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
, orHDB
.
- symbol - One of
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
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 definedapp*
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)
.
- list - A well formed response conforming to
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)
.
- list - A well formed response conforming to
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 definedapp*
keys only.result
- any - API payload.
-
Return
- list - A well formed response conforming to
(header;result)
.
- list - A well formed response conforming to
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)
.
- list - A well formed response conforming to
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 definedapp*
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)
.
- list - A well formed response conforming to
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 toapi
).cb
- symbol - Callback function to invoke on API responses.opts
- dict|dict[] - Optional header augmentations (parallel toapi
).
-
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[]