Skip to content

OneTick Cloud KDB-X Module Examples

This page provides examples of querying OneTick and processing the returned q tables.

Prerequisite

Complete the OneTick Quickstart before running these examples. It covers installation, access configuration, and the sample query.

Load the module in your q session:

.otc:use`kx.onetickcloud

The examples use datasets available with the packaged OneTick demonstration credentials.

Where processing occurs

OneTick evaluates the submitted SQL remotely. After .otc.sql_rest returns a q table, q operations run locally in the KDB-X process.

Query trades for a symbol

Pass a SQL string to use the default time zone, UTC:

sql:"select SYMBOL_NAME, TIMESTAMP, TRADE_ID, PRICE, SIZE from LSE_SAMPLE.TRD where SYMBOL_NAME='VOD' and TIMESTAMP >= '2024-01-03 00:00:00 UTC' and TIMESTAMP < '2024-01-04 00:00:00 UTC' limit 100";
trades:.otc.sql_rest[sql];

Inspect the converted q schema:

meta trades

TIMESTAMP is a q timestamp, SYMBOL_NAME is a symbol, and TRADE_ID remains a string because columns whose names end in _ID are not converted to symbols.

Specify a time zone

Pass a dictionary to tell OneTick how to interpret SQL timestamps in a time zone other than UTC:

arg:(`sql`timezone)!(
    "select SYMBOL_NAME, TIMESTAMP, PRICE, SIZE from LSE_SAMPLE.TRD where SYMBOL_NAME='VOD' and TIMESTAMP >= '2024-01-03 08:00:00' and TIMESTAMP < '2024-01-03 09:00:00' limit 100";
    "Europe/London");

trades:.otc.sql_rest[arg];

The timezone value must be an IANA time zone identifier. You can also pass it as a symbol, for example `UTC.

Calculate a field in OneTick SQL

Include OneTick expressions in the submitted SQL. This example asks OneTick to calculate the sum of PRICE and SIZE:

sql:"select TIMESTAMP, PRICE, SIZE, PRICE+SIZE as PS from US_COMP_SAMPLE.TRD where SYMBOL_NAME='AAPL' and TIMESTAMP >= '2024-01-02 00:00:00 UTC' and TIMESTAMP < '2024-01-03 00:00:00 UTC' limit 10";
data:.otc.sql_rest[sql];

The OneTick service determines which SQL syntax, functions, datasets, and fields are available to the configured account. Refer to the OneTick SQL documentation for language details and the OneTick SQL querying examples for additional query patterns.

Aggregate the result in q

Query the returned q table immediately:

sql:"select SYMBOL_NAME, TIMESTAMP, PRICE, SIZE from LSE_SAMPLE.TRD where SYMBOL_NAME='VOD' and TIMESTAMP >= '2024-01-03 00:00:00 UTC' and TIMESTAMP < '2024-01-04 00:00:00 UTC' limit 1000";
trades:.otc.sql_rest[sql];

summary:select trades:count i,volume:sum SIZE,vwap:SIZE wavg PRICE by SYMBOL_NAME from trades;
show summary;

You can join, transform, or persist the table using the same q operations as other in-memory data.

Handle query errors

Use protected evaluation to capture a signaled error as a string without terminating the surrounding evaluation:

sql:"select * ffrom LSE_SAMPLE.TRD";
result:@[.otc.sql_rest;sql;{x}];

$[10h=type result;
    -2 "OneTick query failed: ",result;
    show result];

For help resolving specific errors, refer to Troubleshooting OneTick.

Next steps