Querying kdb Insights

The functionality outlined below provides a breakdown of kxi.Query, which you can use to query data stored in kdb Insights or kdb Insights Enterprise deployments via SQL or API calls.

kxi.query.Query Querying interface for kdb Insights Enterprise

Querying get_data query kdb Insights Enterprise using a formatted API sql query kdb Insights Enterprise using SQL get_meta retrieve metadata information relating to a deployed database fetch_udas query the database for User Defined Analytics (UDAs) and attach them as additional attributes

..

`kxi.query.Query`

Create an authenticated connection to a kdb Insights or kdb Insights Enterprise instance.

Parameters:

name	type	description	default
host	str	The host against which a query instance is to be established if provided as a `str`	Value set in environment variable `INSIGHTS_URL`.
client_id	str	The client ID associated with the user attempting to establish a connection if provided as a `str`	Value set in environment variable `INSIGHTS_CLIENT_ID`
client_secret	str	The client secret ID associated with the user attempting to establish a connection if provided as a `str`	Value set in environment variable `INSIGHTS_CLIENT_SECRET`
realm	str	The realm to use for OAuth authentication	Value set in environment variable `INSIGHTS_REALM`, using `insights` if this is unset. Use `admin-cli` for administrator auth, or `insights` for clients.
usage	str	Is this client targeted at a kdb Insights (`'MICROSERVICES'`) or kdb Insights Enterprise (`'ENTERPRISE'`) instance	`'ENTERPRISE'`
data_format	str	The format of the returned data from subsequent query calls.	By default this is set to `'application/octet-stream'` which will allow the data to be parsed by PyKX as a q variable. If PyKX is not available `'application/json'` should be used to return the payload as a JSON structure
timeout	int	The timeout in seconds for requests when attempting to retrieve a token.	20
username	str	Used for Insights admin authentication using the LegacyApplicationClient	N/A
password	str	Used for Insights admin authentication using the LegacyApplicationClient	N/A

Example:

Authenticate for query against an instance of kdb Insights Enterprise using defined environment variables and get an access token.

>>> import kxi.query
>>> import os
>>> os.environ['INSIGHTS_URL']
'https://my-insights.kx.com'
>>> os.environ['INSIGHTS_CLIENT_ID']
'my-client-id'
>>> os.environ['INSIGHTS_CLIENT_SECRET']
'my-client-secret'
>>> conn = kxi.query.Query()

Connect to an instance of kdb Insights using an environment variable for the service gateway URL.

>>> import kxi.query
>>> import os
>>> os.environ['INSIGHTS_URL']
'http://localhost:8080'
>>> conn = kxi.query.Query(usage='MICROSERVICES')

Authenticate for query against an instance of kdb Insights Enterprise through specification of client details. These can be retrieved from Keycloak using the documentation outlined here.

>>> import kxi.query
>>> conn = kxi.query.Query('https://my-deploy.kx.com', client_id='my-client-id', client_secret='my-client-secret')

Connect to an instance of kdb Insights through specification of client details.

>>> import kxi.query
>>> conn = kxi.query.Query('http://localhost:8080', usage='MICROSERVICES')

Authenticate for query against an instance of kdb Insights Enterprise including definition of return data format.

>>> import kxi.query
>>> conn = kxi.query.Query(data_format='application/json')

`get_data`

Run a getData API call against kdb Insights or kdb Insights Enterprise.

Parameters:

name	type	description	default
table	str	The name of the table from which to retrieve data	N/A
start_time	str	The earliest time data is to be queried from	15 minutes prior to the time that the get_data call is made
end_time	str	The latest time data is to be queried from	The time that the get_data function is called
input_timezone	str	The timezones of start_time and end_time	UTC
output_timezone	str	The timezone of output timestamp columns	UTC
filter	list	A list of triadic Python lists defining filter conditions in the format `[['function', 'column name', 'parameter'], ... ]`	`None`
group_by	list[str]	A list of Python `str` items defining the columns to group aggregations by	`None`
aggregations	list[str]	A list of Python `str` items or a list of lists of `str` items defining. The columns to be returned from the table across DAPs if defined as a list of `str`. If provided as a list of 3-element Python lists as follows `[['assignname', 'agg', 'column'], ...]` then `assignName` defines the name to be assigned to the return of the aggregation, `agg` defines the aggregation function to be used and `column` defines the column on which that aggregation is applied.	`None`
fill	str	This defines how to handle the presence of null values. This should be supplied as a single `str` item with value `forward` to define the use of the last non-null value in a column or `zero` to replace all null values with the 0 value of that column.	`None`
temporality	str	This determines the way that the `start_time` and `end_time` arguments are interpreted. The two support input types are `'slice'` and `'snapshot'`.	`None`
slice	list[str]	This should be defined as a Python list of 'times' which denote the time range to grab between each date in the `start_time` and `end_time` range when a temporality of `'slice'` is used.	`None`
sort_columns	list[str]	This should be a Python list of `str` type objects denoting the columns which are to be sorted on.	`None`
labels	dict[str, str]	labels defined in the assembly file of the DAPs can be used to only select data from DAPs in assemblies matching that label.

Returns:

type	description
`pykx.Table` or `json` output	The data returned from the `get_data` call

Examples:

Connect to a running instance of kdb Insights Enterprise and query using get_data with default values querying a named table.

>>> import kxi.query
>>> conn = kxi.query.Query()
>>> conn.get_data('my_table')

Query the instance providing arguments to various get_data calls.

>>> conn.get_data('my_table', group_by = ['sensorID', 'qual'])
>>> conn.get_data('my_table', filter = [['within', 'qual', [0, 2]]])
>>> conn.get_data('my_table', start_time='2000.05.26',end_time='2000.05.27')

Connect to a running instance of kdb Insights Enterprise and query using get_data returning result as json payload.

>>> import kxi.query
>>> conn = kxi.query.Query('https://my-deploy.kx.com',
...                        client_id='my-client-id',
...                        client_secret='my-client-secret',
...                        data_format='application/json')
>>> conn.get_data('my_table')

Connect to a running instance of kdb Insights and query using get_data returning result as json payload.

>>> import kxi.query
>>> conn = kxi.query.Query('http://localhost:8080',
...                        usage='MICROSERVICES')
>>> conn.get_data('my_table')

`sql`

Run a SQL API call against a kdb Insights or kdb Insights Enterprise query environment.

Parameters:

name	type	description	default
query	str	The SQL query which is to be run against the query environment	N/A

Returns:

type	description
`pykx.Table` or `json` output	The data returned from the `sql` call

Examples:

Connect to a running instance of kdb Insights Enterprise and query a named table using sql.

>>> import kxi.query
>>> conn = kxi.query.Query()
>>> conn.sql('SELECT * FROM my_table')

Connect to a running instance of kdb Insights Enterprise and query using sql returning result as json payload.

>>> import kxi.query
>>> conn = kxi.query.Query('https://my-deploy.kx.com',
...                        client_id='my-client-id',
...                        client_secret='my-client-secret',
...                        data_format='application/json')
>>> conn.sql('SELECT * FROM my_table')

Connect to a running instance of kdb Insights and query using sql returning result as json payload.

>>> import kxi.query
>>> conn = kxi.query.Query('http://localhost:8080',
...                        usage='MICROSERVICES',
...                        data_format='application/json')
>>> conn.sql('SELECT * FROM my_table')

`get_meta`

Run a getMeta API call against kdb Insights or kdb Insights Enterprise.

Returns:

type	description
`pykx.Dictionary` or `json` output	A dictionary containing metadata information associated with the various processes and schemas which describe a kdb Insights Enterprise Database

Examples:

Connect to a running instance of kdb Insights Enterprise and query using get_meta to retrieve information about the database, schemas and assemblies deployed.

>>> import kxi.query
>>> conn = kxi.query.Query()
>>> conn.get_meta()

Connect to a running instance of kdb Insights Enterprise and query using get_meta returning result as json payload.

>>> import kxi.query
>>> conn = kxi.query.Query('https://my-deploy.kx.com',
...                        client_id='my-client-id',
...                        client_secret='my-client-secret',
...                        data_format='application/json')
>>> conn.get_meta()

Connect to a running instance of kdb Insights and query using get_meta to retrieve information about the database, schemas and assemblies deployed.

>>> import kxi.query
>>> conn = kxi.query.Query('http://localhost:8080',
...                        usage='MICROSERVICES',
...                        data_format='application/json')
>>> conn.get_meta()

`fetch_udas`

Query kdb Insights or kdb Insights Enterprise for UDAs and attach them as additional attributes on the Query object.

Examples:

Connect to a running instance of kdb Insights Enterprise and call a UDA.

>>> import kxi.query
>>> conn = kxi.query.Query('https://my-deploy.kx.com',
...                        client_id='my-client-id',
...                        client_secret='my-client-secret')
>>> conn.fetch_custom_apis()
>>> params = {
...     "table": "taxi",
...     "column": "fare",
...     "multiplier": 10
... }
>>> conn.example_daAPI(json=params)

Connect to a running instance of kdb Insights and call a UDA.

>>> import kxi.query
>>> conn = kxi.query.Query('http://localhost:8080',
...                        usage='MICROSERVICES',
...                        data_format='application/json')
>>> conn.fetch_custom_apis()
>>> params = {
...     "table": "taxi",
...     "column": "fare",
...     "multiplier": 10
... }
>>> conn.example_daAPI(json=params)