Helper Functions

When writing custom UDAs (user defined analytics), the following helper functions are available in the DAPs to retrieve data.

Data model

Unlike a typical kdb+ database, table data is not stored in the base table name. Data may be distributed across an on-disk table and multiple in-memory tables (see Late data). It is highly recommended to use the following helper functions.

`.kxi.selectTable`

This a general-purpose function to retrieve data for the specified table. It creates a single synthesized view of the table, removing the need to know how the data is distributed across multiple internal tables. This is the recommended method for accessing data.

Parameters
- args - dict - Dictionary containing the following (any other input type triggers an error):
  - table - symbol - Table name.
  - startTS - timestamp - Optional: Start time (inclusive) for selecting data. Default: -0Wp. This can be an empty list for reference tables.
  - endTS - timestamp - Optional: End time (exclusive) for selecting data. Default: 0Wp. This can be an empty list for reference tables.
  - filter - list - Optional: Functional where clause, for example, a list of the parse tree. Default: (), indicating no filter is applied.
  - groupBy - dict/boolean - Optional: Functional by clause. Default: 0b, indicating no by clause.
  - agg - dict - Optional: Functional aggregation/select clause. Default: All columns of the table are included.
Return
- table - Result of the select query.

Warning

.kxi.selectTable accepts a subset of the parameters used in .kxi.getData. The keys filter, groupBy, and agg have the same semantic meaning, but there are subtle differences in the syntax. Refer to the documentation for the precise syntax details.

Warning

In previous versions, .kxi.selectTable accepted six parameters. The latest version now requires a single dictionary. Additionally, there is a minor change in how the end time parameter endTS functions. Previously, the endTS parameter was inclusive, in the latest version the endTS parameter is exclusive. If you are migrating to the new API and want to maintain identical behavior, you must add one nanosecond to the endTS value.

Examples

args:`table`startTS`endTS`filter`groupBy`agg!(`trade;2024.04.10D;2024.04.15D;((=;`sym;enlist`AAPL);(>;`price;100));enlist[`sym]!enlist`sym;`avgPrice`avgSize!((avg;`price);(max;`size)))
.kxi.selectTable args / Complex select on timeseries data
.kxi.selectTable enlist[`table]!enlist`trade / Select using defaults

Advanced

.kxi.selectTable is the recommended method for retrieving data. However, advanced users may choose to access internal tables individually for greater control.

Info

It is not recommended to use the following functions without a deep understanding of the data model used in kdb Insights. For more information, refer to the Late data documentation.

`.kxi.getDapType`

Returns the type of the data access process being code is executing in. One of RDB, IDB, or HDB.

Parameters
- None
Return
- symbol - One of RDB, IDB, or HDB.

Example

q).kxi.getDapType[]
`IDB

`.kxi.getTableAccessors` or `.kxi.getTableRefAccessors`

Returns a list of table accessors or table reference accessors based on the system configuration and the DAP type the function is executing on. The accessors in the response are ordered from the oldest data to the latest.

For example, a .kxi.getTableAccessors call returns:

q).kxi.getTableAccessors`myPartitionedTable
`.kxi.getTableBase`.kxi.getTableBuffer`.kxi.getTableOverflow

Data returned by .kxi.getTableBase is older (based on time ingested) than the data returned by .kxi.getTableOverflow. The order is important for keyed tables, where newer data must be upserted onto older data to ensure the latest in-effect records are returned.

Parameters
- tn - symbol - Table name.
Return
- symbol[] - List of functions to return or references to data.

Example `.kxi.getTableAccessors`

q) .kxi.getDapType[]
`HDB
q)tn:`myPartitionedTable
q).kxi.getTableAccessors tn
`.kxi.getBaseTable`.kxi.getBufferTable`.kxi.getOverflowTable

Example `.kxi.getTableRefAccessors`

q) .kxi.getDapType[]
`HDB
q)tn:`myPartitionedTable
q).kxi.getTableRefAccessors tn
`.kxi.getBaseTableRef`.kxi.getBufferTableRef`.kxi.getOverflowTableRef

`.kxi.getTableBase` or `kxi.getTableBaseRef`

Retrieves a reference to the main portion of the table.

Table Type	Applicable DAPs	What's returned
splayed or basic	RDB, IDB, HDB	Returns on-disk table portion of the data written down as of the last EOI.
splayed_mem	RDB, IDB, HDB	Returns full in-memory table.
partitioned	RDB, IDB, HDB	Returns the main portion of the data. In RDB this is in-memory, and for IDB/HDB this is a partitioned table on-disk.

Warning

For IDB and HDB, you must call the function from the global namespace because the data it returns may reside on disk.

Parameters
- tn - symbol - Table name.
Return for .kxi.getTableBase
- table - Table data.
Return for .kxi.getTableBaseRef
- symbol - Variable name to access table.

Example

For a partitioned table in IDB:

q).kxi.getTableBase`myTable
int x y
-------
0   0 2
0   1 3

q).kxi.getTableBaseRef`myTable
`..myTable

`.kxi.getTableBuffer` or `.kxi.getTableBufferRef`

Retrieves the portion of the table held in memory that Storage Manager writes to disk during the next EOX event. Use this function for splayed and basic tables, and for partitioned tables in IDB or HDB only when late data is enabled.

Table Type	Applicable DAPs	What's returned
splayed or basic	RDB, IDB, HDB	Returns the in-memory portion of data that is written to disk during the next EOI.
splayed_mem	None	Not applicable
partitioned	Late data IDB or HDB	Returns the in-memory table that is written to disk during the next EOX event. For IDB, this occurs during EOI, and for HDB, it occurs during EOD.

Parameters
- tn - symbol - Table name.
Return for .kxi.getTableBuffer
- table - Main in-memory portion of the table.
Return for .kxi.getTableBufferRef
- symbol - Variable name of the table holding buffer data.

Example `.kxi.getTableBuffer`

q).kxi.getTableBuffer`myTable
x y
---
1 3
2 4

Example `.kxi.getTableBufferRef`

q).kxi.getTableBufferRef`myTable
`.da.i.buffer.myTable
q).da.i.buffer.myTable
x y
---
1 3
2 4

`.kxi.getTableOverflow` or `.kxi.getTableOverflowRef`

Retrieves a reference to the portion of the table held in memory that was ingested after the start of an EOX event. This table is generally empty except between the start of an EOX event (_prtnEnd) and the end of that event (_reload).

Table Type	Applicable DAPs	What's returned
splayed or basic	RDB, IDB, HDB	Returns any data received between the start and end of an EOI event. This table is typically empty except during an ongoing EOI.
splayed_mem	None	Not applicable
partitioned	Late data IDB or HDB	Returns any data received between the start and end of an EOX event (EOI for IDB, and EOD for HDB). This table is typically empty except during the EOX event.

Parameters
- tn - symbol - Table name.
Return for .kxi.getTableOverflow
- table - Delta portion of the table.
Return for .kxi.getTableOverflowRef
- symbol - Variable name of the table holding overflow data.

Example `.kxi.getTableOverflow`

q).kxi.getTableOverflow`myTable
x y
---
1 3
2 4

Example `.kxi.getTableOverflow`

q).kxi.getTableOverflowRef`myTable
`.da.i.overflow.myTable
q).da.i.overflow.myTable
x y
---
1 3
2 4

`.kxi.getTables`

Retrieves the list of tables available in the assembly. This is a DAP level function only.

Return
- symbol[] - Table list.

Example

q).kxi.getTables[]
`trade`quote`exchange`instrument

`.kxi.getSchema`

Retrieves the schema for the given table name. This is a DAP level function only.

Parameters
- tbl - symbol - Table name.
Return
- table - Table schema.

Example

q).kxi.getSchema[`exchange]
column    | typ description oldName attrMem attrIDisk attrDisk isSerialized foreign anymap backfill encrypt vectorIndex
----------| -----------------------------------------------------------------------------------------------------------
time      | -16 ""                                             0                    0               0       ::
sym       | -11 ""                                             0                    0               0       ::
exchangeID| -7  ""                  g       p                  0                    0               0       ::
country   | -11 ""                                             0                    0               0       ::
openHour  | -19 ""                                             0                    0               0       ::
closeHour | -19 ""                                             0                    0               0       ::
currency  | 0   ""                                             0                    0               0       ::
timezoneID| -11 ""                  g                          0                    0               0       ::

`.kxi.getTableProperties`

Retrieves the list of associated properties for the given table name. Refer to Schemas for a full list of available properties. This function operates at the DAP level only.

Parameters
- tbl - symbol - Table name.
- props - symbol|symbol[] - Table properties.
Return
- dictionary - Dictionary of requested properties to table property values.

The list of valid properties are:

property	return type	description
`description`	string	Table description.
`typ`	symbol	Table type.
`pkCols`	symbol[]	Primary key columns.
`prtnCol`	symbol	Column the data is partitioned on.
`sortColsMem`	symbol[]	Columns the data is sorted on in memory (e.g. RDB).
`sortColsIDisk`	symbol[]	Columns the data is sorted on intraday disk (e.g. IDB).
`sortColsDisk`	symbol[]	Columns the data is sorted on on-disk (e.g. HDB).
`isSplayed`	boolean	Indicates if the table is splayed.
`isPartitioned`	boolean	Indicates if the table is partitioned.
`isSharded`	boolean	Indicates if the table is sharded.
`columns`	table	Table columns (see below).
`oldName`	symbol	Name of a previous version of this table.
`shards`	int	Number of assemblies over which the table is sharded.
`slices`	int	Slice count.
`hashCol`	symbol	Column to hash for sharding.
`partitions`	int	Partition count.
`blockSize`	int	Controls how frequently data is written to disk.
`isMD`	boolean	Indicates if the table is reference data.
`isDelta`	boolean	Indicates if the table has an in-memory component.

Example

q).kxi.getTableProperties[`exchange;`description`typ]
description| "Money can be exchanged for goods and services"
typ        | `basic

Invalid property values result in an error.

q).kxi.getTableProperties[`exchange;`typ`notValid]
invalid table property: ,`notValid error

`.kxi.getHeader`

Retrieves the header of the request. This function operates at the DAP and Aggregator levels.

Return
- dict - Dictionary of header keys.

Example

q).kxi.getHeader[]
logCorr| "testCorr"
timeout| 60000
appID  | `test
aggFn  | `.custom.aggFn

// Further example within a custom UDA
// Assuming that user has added the below app* options to their request
appAudit:1b;
appEmployee:`bob;

myAuditFunction:{[args]
    auditChk args;
    / Some logic
    }

auditChk:{[args]
    hdr:.kxi.getHeader[];
    if[hdr`appAudit;
        .kxi.publish[`auditData;`api`dap`user`args!(hdr`api;.z.h;hdr`appEmployee;args)]]
    }

`.kxi.response.make`

Returns the result of an API call in the format of (hdr;result), where the header contains valid RC/AC codes.

Parameters
- header - dict - API query header, with user defined app* keys only.
- status - list - API query status of the form (.kxi.response.rc.{code};.kxi.response.ac.{code};"application information").
- result - any - API payload.
Return
- list - A well formed response conforming to (header;result).

Example

myFunction:{
    / Some logic
    .kxi.response.make[`appTeam`appTest!(`EQ;"Q1 beta");(.kxrrc.OK;.kxrac.OK;"");res]
    }

`.kxi.response.ok`

Returns the result of a successful API call in the format (hdr;result), where the header contains valid RC/AC codes.

Parameters
- result - any - API payload.
Return
- list - A well formed response conforming to (header;result).

Example

myFunction:{
    / Some logic
    .kxi.response.ok res
    }

`.kxi.response.hok`

Returns the result of a successful API call in the format (hdr;result), where the header contains valid RC/AC codes and user defined header values are maintained.

Parameters
- header - dict - API query header, with user defined app* keys only.
- result - any - API payload.
Return
- list - A well formed response conforming to (header;result).

Example

myFunction:{
    / Some logic
    .kxi.response.hok[`appTeam`appTest!(`EQ;"Q1 beta");res]
    }

`.kxi.response.error`

Returns the result of a failed API call in the format (hdr;result), where the header contains valid RC/AC codes.

Parameters
- ac - short - AC code indicating the nature of the API failure.
- ai - string - Error message to include in the response header.
- result - any - API payload.
Return
- list - A well formed response conforming to (header;result).

Example

myFunction:{
    / Some logic
    if[not tbl in tables[];
        :.kxi.response.error[.kxi.response.ac.TABLE;"Table not found in DAP";res]];
    / Some more logic
    }

`.kxi.response.herror`

Returns the result of a failed API call in the format (hdr;result), where the header contains valid RC/AC codes and user defined header values are maintained.

Parameters
- header - dict - API query header, with user defined app* keys only.
- ac - short - AC code indicating the nature of the API failure.
- ai - string - Error message to include in the response header.
- result - any - API payload.
Return
- list - A well formed response conforming to (header;result).

Example

myFunction:{
    / Some logic
    if[not tbl in tables[];
        :.kxi.response.herror[`appTeam`appTest!(`EQ;"Q1 beta");.kxi.response.ac.TABLE;"Table not found in DAP";res]];
    / Some more logic
    }

`.kxi.context.set`

Saves a context value that can be retrieved when resuming a request upon receiving the response from a sub-request (refer to .kxi.response.callAPI).

Parameters
- name - symbol|symbol[] - Names of context entries.
- value - any - Corresponding context values.

Example

.kxi.context.set[`x;10]
.kxi.context.set[`y`z;("abc";([]c:1 2))]

`.kxi.context.get`

Retrieves a value previously saved to the context. This is useful when resuming a request after receiving the response from a sub-request (refer to .kxi.response.callAPI).

Parameters
- name - symbol|symbol[] - Names of context entries.
Return
- any - Corresponding context values.

Example

.kxi.context.get`x // 10
.kxi.context.get`y`z // ("abc";([]c:1 2))

`.kxi.context.contains`

Checks for the existence of a context value. Use this in combination with .kxi.context.get to ensure that expected values are present in the context.

Parameters
- name - symbol|symbol[] - Names of context entries.
Return

boolean - True if the context value exists, false otherwise

Example

.kxi.context.contains`x // 1b
.kxi.context.contains`a // 0b
.kxi.context.contains`y`a`z // 101b

`.kxi.response.callAPI`

Calls another API from within a UDA. The response is deferred until the result from the called API is returned. This is an aggregator only helper function.

Parameters
- api - symbol|symbol[] -APIs to call. These must be registered APIs available to the gateway. Registered APIs can be viewed using .kxi.getMeta. The response is deferred until the result is returned.
- args - dict|dict[] - API args (parallel to api).
- cb - symbol - Callback function to invoke on API responses.
- opts - dict|dict[] - Optional header augmentations (parallel to api).
Return
- (dict;list) - Deferred response header, request payloads, and callback.

opts within deferred requests

Within a deferred request, only the following fields are permissible in the opts field: aggFn, cast, version, and any field prefixed with app.

Defer in aggregator example

The example below shows how to use .kxi.response.callAPI to defer within a UDA until enough data is available for an aggregation calculation:

// @desc Aggregates trade data, but defers if not enough responses.
// @param data  {list[]}                Partial responses from `.kxi.getData`.
// @return      {dictionary;table|list} Response header and payload or sub-request if deferring.
aggMinTrade:{[data]
    t:.sgagg.getData data; / Execute normal getData aggregation
    hdr:first t; / Response header
    tbl:last t; / Table data

    if[.kxi.response.rc.OK<>first[t]`rc; / If the response is no good
        :.kxi.response.error[.kxi.response.ac.ERR;"Failed to aggregate getData: ",hdr`ai;()]]; / Fail

    if[100<count tbl; / If we have enough data
        :.kxi.response.ok tbl]; / Succeed

    //
    // Not enough data.
    //
    .kxi.context.set[`prevData;tbl]; / Store existing values for later
    .kxi.response.callAPI[`.kxi.getData;`table`startTS`agg!(`trade;1+max tbl`time;cols tbl);`.resume.aggMinTrade;()!()] / Defer
    }

// @desc Resume function for `aggMinTrade`.
// @param data  {list[]}                Partial responses from `.kxi.getData`.
// @return      {dictionary;table|list} Response header and payload or sub-request if re-deferring.
.resume.aggMinTrade:{[data]
    t:.sgagg.getData data; / Execute normal getData aggregation
    hdr:first t; / Response header
    tbl:last t; / Table data

    if[.kxi.response.rc.OK<>first[t]`rc; / If the response is no good
        :.kxi.response.error[.kxi.response.ac.ERR;"Failed to aggregate getData: ",hdr`ai;()]]; / Fail

    if[not .kxi.context.contains`prevData;
        :.kxi.response.error[.kxi.response.ac.ERR;"Unexpected: prevData missing from context";()]]; / Sanity check
    prevData:.kxi.context.get`prevData; / Recover previous data

    if[100<count[prevData]+count tbl; / Now if we have enough data
        :.kxi.response.ok prevData,tbl] / Succeed

    //
    // Still not enough data.
    //
    prevData,:tbl; / Accumulate
    .kxi.context.set[`prevData;prevData]; / Re-save for later
    .kxi.response.callAPI[`.kxi.getData;`table`startTS`agg!(`trade;1+max tbl`time;cols tbl);`resume.aggMinTrade;()!()] / Redefer
    }

// We can then register our aggregations functions thusly:
.sgagg.registerAggFn[`.custom.aggMinTrade;
    .kxi.metaDescription["Defers on receipt of trade data to ensure there is enough data to run aggregation"],
    .kxi.metaParam[`name`type`descriptions!(`trade;0h;"Trade data from DAPs")],
    .kxi.metaReturn`type`description!(98h;"Minimum result of trade data");
    `$()]

`.kxi.pe.enable` / `.kxi.pe.disable`

These functions enable or disable protected evaluation within DAPs or the Aggregator. They do not take arguments. Enabling or disabling protected evaluation is useful when building and debugging issues in a user defined analytic (UDA).

Protected evaluation is enabled by default. It is recommended to disable it only in test processes outside of production environments.

Example

Consider the following query function, which causes a type error:

.custom.countBy:{[table;startTS;endTS;byCols]
    1+`
    }

Although the error is evident in the code, use this function for demonstration purposes only. After calling this API, the DAP logs provide the following output:

2024-08-12 15:45:30.146 [hdb] DEBUG KXCTX {23329698-9642-4654-b65e-81bfefe16b05} Starting context, corr=23329698-9642-4654-b65e-81bfefe16b05
2024-08-12 15:45:30.146 [hdb] DEBUG KXCTX {23329698-9642-4654-b65e-81bfefe16b05} Setting context to no-save, reason='requests not saved in DAP'
2024-08-12 15:45:30.146 [hdb] DEBUG DA {23329698-9642-4654-b65e-81bfefe16b05} Executing .custom.countBy
2024-08-12 15:45:30.146 [hdb] ERROR SAPI {23329698-9642-4654-b65e-81bfefe16b05} Error (type) encountered executing .custom.countBy, rc=6 ac=11 ai=Unexpected error (type) encountered executing .custom.countBy
2024-08-12 15:45:30.146 [hdb] DEBUG DA {23329698-9642-4654-b65e-81bfefe16b05} Completed .custom.countBy, rc=6 ac=11 ai=Unexpected error (type) encountered executing .custom.countBy
2024-08-12 15:45:30.146 [hdb] DEBUG DA {23329698-9642-4654-b65e-81bfefe16b05} Sending response to aggregator, agg=:10.244.0.9:5070
2024-08-12 15:45:30.147 [hdb] DEBUG KXCTX {23329698-9642-4654-b65e-81bfefe16b05} Ending context, no-save

The log indicates a type error, which helps identify that the issue is related to data types. To pinpoint the exact location of the failure, you can enhance debugging by adding log statements to the code. Alternatively, if you attach to the process, kdb can show where the process is failing.

To attach to the process and enable more detailed debugging, run the following commands:

q).kxi.pe.disable[] / This turns off protected evaluation
q)\e 1 / Set kdb to catch errors

After running the same query while connected to the process, the following detailed debugging output is displayed:

q)2024-08-12 15:51:51.154 [hdb] DEBUG KXCTX {ba62e0b9-f290-4abd-be3d-dcc91cb6116d} Starting context, corr=ba62e0b9-f290-4abd-be3d-dcc91cb6116d
2024-08-12 15:51:51.154 [hdb] DEBUG KXCTX {ba62e0b9-f290-4abd-be3d-dcc91cb6116d} Setting context to no-save, reason='requests not saved in DAP'
2024-08-12 15:51:51.154 [hdb] DEBUG DA {ba62e0b9-f290-4abd-be3d-dcc91cb6116d} Executing .custom.countBy
type error
.custom.countBy[0]   {[table;startTS;endTS;byCols]1+`}
                                                   ^
q))table / See what current table argument is
`trade
q))startTS / See what current startTS argument is
-0Wp

Based on the output you can see specifically where the function is failing. Additionally, you can inspect the current values of the arguments. For larger functions, you can also run through the code line by line to identify the underlying issue.

To revert to the old behavior, run the following:

.kxi.pe.enable[]

Helper Functions

.kxi.selectTable

Examples

Advanced

.kxi.getDapType

Example

.kxi.getTableAccessors or .kxi.getTableRefAccessors

Example .kxi.getTableAccessors

Example .kxi.getTableRefAccessors

.kxi.getTableBase or kxi.getTableBaseRef

Example

.kxi.getTableBuffer or .kxi.getTableBufferRef

Example .kxi.getTableBuffer

Example .kxi.getTableBufferRef

.kxi.getTableOverflow or .kxi.getTableOverflowRef

Example .kxi.getTableOverflow

Example .kxi.getTableOverflow

.kxi.getTables

Example

.kxi.getSchema

Example

.kxi.getTableProperties

Example

.kxi.getHeader

Example

.kxi.response.make

Example

.kxi.response.ok

Example

.kxi.response.hok

Example

.kxi.response.error

Example

.kxi.response.herror

Example

.kxi.context.set

Example

.kxi.context.get

Example

.kxi.context.contains

Example

.kxi.response.callAPI

Defer in aggregator example

.kxi.pe.enable / .kxi.pe.disable

Example

`.kxi.selectTable`

`.kxi.getDapType`

`.kxi.getTableAccessors` or `.kxi.getTableRefAccessors`

Example `.kxi.getTableAccessors`

Example `.kxi.getTableRefAccessors`

`.kxi.getTableBase` or `kxi.getTableBaseRef`

`.kxi.getTableBuffer` or `.kxi.getTableBufferRef`

Example `.kxi.getTableBuffer`

Example `.kxi.getTableBufferRef`

`.kxi.getTableOverflow` or `.kxi.getTableOverflowRef`

Example `.kxi.getTableOverflow`

Example `.kxi.getTableOverflow`

`.kxi.getTables`

`.kxi.getSchema`

`.kxi.getTableProperties`

`.kxi.getHeader`

`.kxi.response.make`

`.kxi.response.ok`

`.kxi.response.hok`

`.kxi.response.error`

`.kxi.response.herror`

`.kxi.context.set`

`.kxi.context.get`

`.kxi.context.contains`

`.kxi.response.callAPI`

`.kxi.pe.enable` / `.kxi.pe.disable`