Querying Panel

This page describes how to retrieve data from your kdb Insights Enterprise deployment for analysis using the Query panel.

The Query panel is the top left-hand section of the Query window and is used to query your database.

Query window

If this panel is not visible, click View, in the top left-hand corner, and toggle Query Panel to display it.

To create a query:

Open the Query window by clicking + beside Queries in the left-hand menu, or using one of the other methods described here.
Create a query using one of the following methods:
Once your query is created, click Run Query.
The result of your query is assigned to a variable in Python or q based on the language currently selected in the language tab of the scratchpad.
View and analyze your query output in the output panel, at the bottom of the screen.
Each query is assigned a system defined name, displayed in the title bar of the query window.
1. Click Save to save the query with the system defined name. A notification is displayed indicating that the query has been saved.
To rename and save the query:
1. Click the pencil icon beside the query name in the title bar and enter a new name.
2. Click OK.
Saved queries are listed on the left-hand menu under Queries.
Click Clear to clear your query and start a new one.

Basic query

The Basic tab allows you to retrieve data from the database without having to write code. You can use it to return data within specific ranges and apply filters, to that data. The Basic tab is the first tab displayed when you open the Query window. This screen contains:

The following table describes the parameters of the Basic tab.

Parameter

Description

Table Name

The name of the table that you're querying. Select a table from the dropdown list of available tables. This lists tables in deployed databases.

Range

This enables you to specify an initial query condition based on either; ROWS, TIME, or DATE.

Click on the drop-down list to open the Select Range dialog. This gives you predefined options, select one of these or choose the custom option at the end of each list.

ROWS

The Last 100,000 rows are displayed by default.
Custom (Rows)	Select this to specify a custom row limit.

TIME

In the TIME column, select one of the pre-defined time ranges or set a custom time scale.
Custom (Time)	Select this to specify a custom time. When this is selected the Last Minutes parameter is displayed. Specify the number of minutes of data you want queried.

DATE

In the DATE column, select one of the pre-defined date ranges or set a custom date.

Custom (Dates)

Select this to specify a custom date range. When this is selected the 2 additional parameters are displayed.

Start Date The start date which defines the lower bound of your returned data. Use the date picker to define the date and time at which your query begins.

End Date The end date which defines the upper bound of your returned data. Use the date picker to define the date and time at which your query ends.

Output Variable

Specify a name for the variable to which this data is assigned. For a basic query, this defaults to the table name, but you can change it. You can use this variable to complete further analysis in the Scratchpad using Python or q. Select the language you'd like your output variable to be set in using the scratchpad language tab.

Field name	Description
Table Name	The name of the table that you're querying. Select a table from the dropdown list of available tables.
Start Date	The start date which defines the lower bound of your returned data. Use the date picker to define the date and time at which your query begins.
End Date	The end date which defines the upper bound of your returned data. Use the date picker to define the date and time at which your query ends.
Output Variable	This is the name you define for the variable to which this data is assigned. You can use this variable to complete further analysis in the Scratchpad using Python or q.
Select the language you'd like your output variable to be set in using the scratchpad language tab.

Click Clear at any stage to reset the value in this tab.

Query options

You can select from the the following query options by clicking Add Query Option from under Optional Parameters.

	Row Limit
	Filter
	Filter by label
	Select Columns
	Sort
	Define Aggregation
	Group Aggregation By

Row Limit is only available when a time or date value is specified in Range.

Combining Filters

You can apply multiple filters, as well as combine filters of different types except for select columns and define aggregations, which cannot be combined.

Row Limit

To conserve memory usage, for very large datasets, you can set limits on the number of rows returned by your query. By default this is set to the last 100,000 rows.

Select Custom (Rows) from the Range drop-down. From here you can:

Enter a new value in Limit (Rows), under Optional Parameters, to change the number of rows returned.
Click First to have the first rows in your database returned.
Click Last to have the last rows in your database returned.
Remove the row limit filter by clicking the icon beside the filter.

Limit rows

Alternatively, if a time or date range has been specified and you want to change it to a row limit, click Add Query Option under optional parameters and click Row Limit.

Filter

The Filter option is used for applying custom filtering to the query.

Basic filtering

To apply a filter you must specify the following:

Field name	Description
Select Column	The column to be selected.
Select Function	The function to be used to apply the filtering logic which is one of: `in` `like` `not equal` `within` `<` `<=` `=` `>=` `>`
FX Parameter	An input provided by you which defines the item against which the column is compared.

Filter By Label

The Filter By label option allows you to query specific tables.

Basic Filter by Label

To apply a Filter By label you must specify the following:

Field name	Description
Key	Select the key value of the label that was assigned to the database.
Value	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 are queried.

Select Columns

The Select Columns option provides the ability to filter the query results to display selected columns only.

Basic Filter by Label

Click on the Select Columns field, and choose the column(s) from the list displayed.

This list represents the columns in the table selected under Table Name.
Only one Select Columns filter is allowed.
The Define Aggregations filter cannot be combined with a Select Columns filter.

Sort

The Sort option allows you to sort the results of a returned query based on the column chosen in the Select Column field.

Basic sort

Define Aggregation

The Define Aggregation option allows you to apply aggregations to specified columns of the table returning the result as an additional column.

Basic aggregation

To apply an aggregation you must specify the following:

Field name	Description
Set Return Column	The column the results of the aggregation are returned to.
Select Aggregation	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)
Select Column	The column the aggregation is applied to.

The Select Columns filter cannot be combined with a Define Aggregation filter.

Group Aggregation By

The Group Aggregation By option allows you to group the results of aggregations based on the Selected Column.

Basic Group By

Query using SQL

Use the SQL query option, in the Query panel, 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.

Enter your query in the SQL tab as shown below:
Specify the output variable to which the return of the SQL statement is defined. This allows for further inspection of the data in Python and q.
Select the language you'd like your output variable to be set in using the scratchpad language tab.
Click Run Query to run the query.
Review the results.

SQL query API

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

This provides a subset of an ANSI SQL compliant API. The limitations of this are outlined here.

Query using Q

Use the Q query option, in the Query panel, to query your data using q. The Q query functionality requires you to target a particular tier of your database for query to query data within the time window covered by the RDB, IDB or HDB respectively.

Enter your query in the Q window as shown below:
Specify the output variable to which the return of the query is applied. This allows for further inspection of the data in Python and q.
Select the language you'd like your output variable to be set in using the scratchpad language tab.
Click Run Query to run the query.
Review the results.

Query using UDA

User Defined Analytics (UDAs) create custom analytics that are tailored to your specific needs, enabling you to extend the capabilities of kdb Insights beyond its standard functionality. See UDA Overview to learn more about UDAs.

To run UDAs in the Query panel.

Click on the UDA tab, as shown below:

The User Defined Analytic (UDA) drop-down contains a list of UDAs that have already been created and deployed to Insights Enterprise via a package.
Select a UDA from the list. The UDAs parameters are displayed.

The parameters of the UDA are shown, as illustrated in the example below. The displayed parameters vary based on the definitions within the UDA. Required fields are indicated with an asterisk (*). Date fields include date/time selectors to facilitate easy data entry.

When a parameter references a database table, ensure that the database containing the table is deployed before running the query.

A warning is displayed if the UDA, you have selected, cannot be queried for one of the following reasons:
- there is no metadata associated with it or
- it contains required fields with types that are not supported meaning it cannot be queried.
The supported q types for UDA parameters within the web interface are: boolean, byte, short, int, long, real, float, char, symbol, timestamp. Only atomic types and strings are valid parameters when attempting to run a UDA via the Query tab.

A list of symbols is also supported when defined as follows: .kxi.metaParam[nametypeisReqdescription!(`byCols;11 -11h;1b;"Column(s) to count by.")]. See creating a UDA for an example.

Parameters that can't be represented in the web interface, due to conflicting types, are number|symbol, lists, dictionaries, and nested types (that is, 77+t – mapped list of lists of type t). number|symbol, lists, and nested types are also unsupported for querying UDAs via the scratchpad as these cannot be mapped to a single JSON type.

The supported temporal type is an atomic timestamp (-12h), which is of the form yyyy.mm.ddDhh:mm:ss.xxxxxxxxx.
- For example; 2024.11.06D14:48:36.000000000 . This is formatted in q as dateDtimespan which maps to 2024-11-06T14:48:36.000000000 when selecting a specific timestamp on the Query tab date picker.
If invalid parameter types are defined for a UDA, the following error is displayed:
Specify an Output Variable, to which the return of the UDA is applied. This allows for further inspection of the data in Python and q.
Click Run Query to run the query.
The UDA is processed and returns the analytics results the query output tabs.

UDAs can only query tables in their own package

The UDA Query tab currently does not support dictionary parameters when a UDA is created and registered. In order to successfully call the UDA on tables outside of the package that it is defined, you need to define a scope parameter in the UDA metadata so that the service gateway knows which aggregator to route the query through. However, the scope parameter is a dictionary argument and therefore is not supported by the UDA Query tab. See deploying UDAs from the CLI for details on setting the scope parameter when deploying UDAs.

Query output

The results of your query are displayed in the tabs of the output panel at the bottom of the Query window. See here for full details on the results and how they can be displayed.