Querying Databases

Once data is available to you within your kdb Insights Enterprise deployment you will naturally wish to retrieve sections of it for analysis. Within the UI this is facilitated through the explore tab using one of three methods:

• Using a form based Query API
• Using a SQL based query
• Using a Q based query

Each of these methods are outlined below providing you an understanding of the limitations and usage of the various approaches.

Using the Query API

The Query API provides you with the ability to retrieve data from the database without the need to write code. It's form building design gives you the option to return data within specific date/time ranges and apply filters to that data in order to constrain the data you receive to only that which you need. This form appears to users as the following:

Mandatory fields

By default the following sections are mandatory:

• The name of the table that you're querying.
  • Presented to you as a dropdown list of available tables from which you can query
• The output variable name to which your query will be assigned.
  • This is the name you wish to use for the q variable to which this data will be assigned allowing you to complete further analysis in Python or q following the guides here and here respectively.
• The start date which defines the lower bound of your returned data.
  • Presented to you as a datepicker you can define the date and time at which your query will begin.
• The end date which defines the upper bound of your returned data.
  • Presented to you as a datepicker you can define the date and time at which your query will end.

The default requirements are encapsulated in this screenshot showing an example of the datepicker.

Optional fields

In addition to the required fields you can also add filtering conditions to form based queries, these are presently constrained to the following types which are defined in more detail below:

1. filter
2. filter by label
3. sort
4. define aggregation
5. group aggregation by

filter

The filter parameter is used for applying custom filtering to the query. To apply a filter the you must provide three inputs:

In it's basic form it presents to you as follows:

1. The column to be selected

2. The function to be used to apply the filtering logic which is one of:

3. in

4. like
5. not equal
6. within
7. <
8. <=
9. =
10. >=

11. >

12. An input provided by you which defines the item against which the a column will be compared.

filter by label

One of the filters available to you is a filter by label option. This provides the ability to query specific tables in each assembly. To do this, you must select the key value of the label that was assigned to the assembly. Then select the value(s) of the label(s) to query from the tables that correspond to the label.

If no label is selected then all corresponding tables will be queried. This is presented as follows:

sort

This allows you to sort the results of a returned query based on a named column within the table, this presents to you as follows:

define aggregation

This allows you to apply aggregations to specified columns of the table returning the result as an additional column. In it's most basic form this presents itself to you as follows:

The aggregations available to you are as follows:

• count
• last
• sum
• prd (product)
• min
• max
• all
• any
• var (variance)
• avg (average)
• dev (deviation)
• svar (sample variance)
• sdev (standard deviation)

group aggregation by

This allows you to group the results of aggregations based on specific columns. This presents to a user as follows:

Using a SQL based query

The SQL query option provided within the Query Window provides you with the ability to query the tables available within your deployment using SQL syntax. These queries can run in a distributed manner across all available data associated with a table. The following shows how this functionality will be presented to you:

The ANSI SQL implementation used here is based on the kdb Insights SQL API outlined here.

Limitations of kdb Insights SQL query API

The SQL functionality used within the kdb Insights Enterprise provides a subset of an ANSI SQL compliant API the limitations of this are outlined here in depth.

Similarly to the Query form you must specify the output variable to which the return of the SQL statement will be defined in q, this allows for further inspection of the data in Python and q.

Using a Q based query

The Q query option provided within the Query Window provides you with the ability to query the various tiers of their database. Specifically, unlike in the SQL section above, the Q query functionality requires you to target a particular tier of their database for query in order to query data within the time window covered by the RDB, IDB or HDB respectively. The following shows how this functionality will be presented to you: