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:

Get Meta
SQL
QSQL
QueryBuilder
UDA

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:

text

Copy

```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.

Copy

.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.

Warning

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:

Copy

.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.

Copy

.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:

Copy

.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.

Copy

.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:

text

Copy

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:

Copy

// 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.

Copy

 .com_kx_edi.queryBuilder[args]

Note

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:

Copy

.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.

Copy

.com_kx_edi.uda[args;name]

Note

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:

Copy

args:`table`column`multiplier`startTS`endTS!(`table1;`column1;10;2024.11.06D;2024.11.21D);
.com_kx_edi.uda[args;"example/daAPI"]

Further eading

Scratchpad