Query APIs
This page describes how to use query APIs, in the Scratchpad, to interact with the database.
Query APIs are a set of APIs created for you to programmatically interact with the database, in the Scratchpad.
The following functions are supported in both q and Python:
The q version of these APIs are detailed below, with their Python wrappers documented here.
In order to use the Python APIs, the scratchpad sets INSIGHTS_CLIENT_ID and INSIGHTS_CLIENT_SECRET
by default, so the user can set INSIGHTS_URL manually in the scratchpad to establish an authenticated connection
(this URL can be taken from the Insights Instance homepage). Define the following in a python scratchpad
session to ensure all Python wrappers work:
```python
>>> import kxi.query
>>> import os
>>> os.environ['INSIGHTS_URL'] = '<url_from_homepage>'
'https://my-insights.kx.com'
>>> conn = kxi.query.Query()
```
The authenticated connection conn can then be used in conjunction with each of the documented APIs to
run valid queries against an Insights database.
Get Meta
Use .com_kx_edi.getMeta to return the metadata of the databases.
.com_kx_edi.getMeta[]
This returns meta information on APIs defined in the DAPs and aggregation functions in the Aggs for all DAPs and Aggs in the system. Additionally, it returns information on the assemblies, schemas, metadata, and UDAs including their parameters and return values.
Assembly names
If you are using the assembly names from the getMeta[] function for .com_kx_edi.qsql, ensure the "-qe" is removed before invoking .com_kx_edi.qsql.
Returns:
| Type | Description |
|---|---|
| any[] | Metadata of the databases |
Example:
.com_kx_edi.getMeta[]
// List all UDAs
.com_kx_edi.getMeta[][1;`api]
// List all assemblies
.com_kx_edi.getMeta[][1;`assembly]
// List all tables
.com_kx_edi.getMeta[][1;`schema]
For more details, refer to the getMeta documentation.
SQL
Use .com_kx_edi.sql to run an SQL query.
.com_kx_edi.sql[query]
The SQL API allows queries to be distributed/aggregated across multiple DAPs.
Required Parameters:
| Name | Description | Default |
|---|---|---|
| Query | A string that expresses the query you wish to run | Required |
Returns:
| Type | Description |
|---|---|
| any | Result of sql query |
Example:
.com_kx_edi.sql["SELECT * FROM table1 LIMIT 10"]
Refer to the SQL documentation for more details.
QSQL
Use .com_kx_edi.qsql to assemble QSQL queries based on a q expression.
.com_kx_edi.qsql[args]
This is intended to run on a data access process and execute the command sent in from the client.
The args dictionary should be structured as follows:
args.scope {dict} Dictionary argument of the qSQL query containing the assembly and the tier
args.scope.affinity {string} An affinity level for how to route queries; this is hardcoded as "soft" for qSQL queries
args.scope.assembly {string|symbol} The assembly name
args.scope.tier {string} The tier ("rdb", "idb", or "hdb") - omitting this parameter results in a "distributed" query, which queries across all database tiers
args.query {string} A q expression
// We still support the deprecated method as the DAPs currently support this format:
// args.assembly {string|symbol} The assembly name // DEPRECATED; moved to args.scope.assembly
// args.target {string} The tier ("rdb", "idb", or "hdb") // DEPRECATED; moved to args.scope.tier
Required Parameters:
| Name | Description | Default |
|---|---|---|
| args | A dictionary that contains either: the scope (as defined above) and a q query, or the assembly, target, and q query | Required |
Returns:
| Type | Description |
|---|---|
| any | Result of q expression |
Example:
// List all assemblies
q).com_kx_edi.getMeta[][1;`assembly]
assembly kxname tbls
------------------------------------------------------
example-pkg-qe trades-db trades
// The "-qe" must be omitted from the assembly name
q).com_kx_edi.qsql[`scope`query!(`assembly`tier!("example-pkg";"rdb");"select max bid from trades")]
For more information, refer to the QSQL documentation.
Query Builder
Use .com_kx_edi.queryBuilder to construct queries.
.com_kx_edi.queryBuilder[args]
The queryBuilder uses arguments found in GetData API.
The Query Builder is a generic data retrieval API that can be executed against an arbitrary database table. It is intended to run on a Data Access Process and retrieve records from a configured database table using a specified dictionary of parameters, as specified below.
Required Parameters:
| Name | Description | Default |
|---|---|---|
| args | The list of arguments can be found in the getData parameters | Required |
Returns:
| Type | Description |
|---|---|
| table | Result of table query |
Example:
.com_kx_edi.queryBuilder[`table`startTS`endTS!("table1";2024.01.01D; 2024.01.02D)]
UDA Query
Use .com_kx_edi.queryBuilder to query a UDA.
.com_kx_edi.uda[args;name]
Refer to these example UDAs for more details
The UDA API allows you to directly query a UDA against databases within a package.
Required Parameters:
| Name | Description | Default |
|---|---|---|
| args | A dictionary of UDA defined parameters. | Required |
| name | The name of the UDA including namespace in the form of "namespace/udaName" or `namespace.udaName , such as example/daAPI or `.example.daAPI |
Required |
Returns:
| Type | Description |
|---|---|
| any | Result of UDA |
Example:
args:`table`column`multiplier`startTS`endTS!(`table1;`column1;10;2024.11.06D;2024.11.21D);
.com_kx_edi.uda[args;"example/daAPI"]