Queries
This page explains the types of queries that can be executed through Data Sources in the kdb Visual Studio Code Extension, including API queries, qSQL queries, SQL queries, and User-Defined Analytics (UDAs).
Each query type maps to functionality available in kdb Insights Enterprise, and the Data Sources provide a form-based workflow for building, parameterizing, and running these queries. Depending on your deployment configuration, you may use one or more of these query types to retrieve or process data.
API queries
This tab provides a method of querying a table using a defined set of parameters. You can define the parameters in the table below through the getData form available in the VSCode extension.
| Property | Description |
|---|---|
| Connection | Select a Connection from the Connection dropdown. |
| Select API | Choose getData from the Select API dropdown. |
| Table | Choose the table you wish to query from the Tables dropdown. |
| Start Time/End Time | Select the Start Time and End Time for the query. |
| Row Limit | Add a limit to the number of queries executed to reduce the number of Out of Memory (OOM) issues or failed queries. |
| Additional Parameters | You can choose from the additional parameters as required. |
Refer to the getData API documentation for more information and a full list of available parameters.
qSQL queries
This tab provides a qSQL query builder that assembles qSQL queries based on a q expression. It is a developer tool that allows running freeform q code against a specific database tier and if required against a specific data access process (DAP).
This function runs a qSQL query:
.com_kx_edi.qsql[args]
Note
Along with the query itself, you must also specify the target database and you can choose to specify the database tier and specific data access process if required.
Refer to the qSQL documentation for more details.
Important
Starting with kdb Insights Enterprise version 1.13, qSQL queries and populating qSQL only work if the Query Environment (QE) is enabled. Ensure you have enabled QEs to use qSQL; they are disabled by default in kdb Insights Enterprise from version 1.13 onwards.
Refer to Query Environments for more details.
SQL queries
This tab allows running freeform SQL queries. Each query is distributed across all available databases. The results are then aggregated and returned as a single dataset.
This function runs a SQL query:
.com_kx_edi.sql[query]
You can run SQL files against any kdb Insights Enterprise connections and, optionally, choose to populate the Scratchpad.
Refer to the SQL documentation for more details.
UDA queries
User-Defined Analytics (UDAs), also known as custom APIs, are essential for developers to leverage the capabilities of kdb when using Insights Enterprise. These UDAs are deployed to Insights through the Data Access Processes (DAPs) and Aggregators (Aggs).
UDAs can be called directly within the VSCode extension through the UDA tab in a data source. This provides a form-based approach to populating the UDA parameters. UDAs can also be called within the kdb Insights Enterprise web interface using Queries, and Views and by running Pipelines.
When interacting with UDAs, parameter fields are shown for configuration. Note the following:
- Required fields are marked with an asterisk (*).
- Optional fields are not required but may be displayed in your results, even if they are empty.
You can add new parameters by clicking Add Parameter. Both optional and distinguished parameters can be added as needed.
A UDA cannot be queried if one or more parameters are invalid
If you attempt to run a UDA with invalid parameters, an error occurs and a pop-up message appears to alert you to the issue.
In some cases, you can successfully query UDAs without any parameters, as seen in the screenshot below.
However, you can still modify the parameter list to add parameters by clicking Add parameter or deleting parameters using the recycle bin icon.
For more information on User-Defined Analytics, refer to the UDAs documentation.