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

OneTick Cloud SQL query requirements

Each OneTick Cloud SQL query must:

  • Specify the database and table, for example, US_COMP_SAMPLE.TRD.
  • Filter by SYMBOL_NAME in the WHERE clause, for example, SYMBOL_NAME = 'CSCO'.
  • Filter by a time window using TIMESTAMP, for example, TIMESTAMP >= '2024-01-02 00:00:00 UTC' AND TIMESTAMP < '2024-01-03 00:00:00 UTC'.

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.

SQL queries

These standalone SQL statements demonstrate additional OneTick Cloud query patterns.

Retrieve US trade data from the US Composite sample:

select * from US_COMP.TRD where SYMBOL_NAME='CSCO' and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York' and TIMESTAMP < '2024-01-04 00:00:00 America/New_York' limit 1000

Retrieve US end-of-day metrics for all exchanges:

select * from US_COMP_DAILY.DAY where SYMBOL_NAME='CSCO' and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York' and TIMESTAMP < '2024-01-04 00:00:00 America/New_York' limit 1000

Retrieve US end-of-day metrics for the composite:

select * from US_COMP_DAILY.DAY where SYMBOL_NAME='CSCO' and TIMESTAMP >= '2024-01-03 00:00:00 America/New_York' and TIMESTAMP < '2024-04-01 00:00:00 America/New_York' and EXCHANGE='' limit 1000

An empty EXCHANGE value selects the composite end-of-day record.

Retrieve US end-of-day composite metrics adjusted for corporate actions:

select CLOSE, VOLUME, CORP_ACTIONS('CLOSE') as ADJ_CLOSE from US_COMP_SAMPLE_DAILY.DAY where SYMBOL_NAME = 'JSPR' AND EXCHANGE = '' and TIMESTAMP >= '2024-01-01 00:00:00.000 America/New_York' and TIMESTAMP < '2024-01-08 00:00:00.000 America/New_York'

CORP_ACTIONS('CLOSE') returns the corporate-action-adjusted closing price as ADJ_CLOSE.

Retrieve UK trades using Bloomberg symbology:

select * from BSYM::LSE_SAMPLE.TRD where SYMBOL_NAME='VOD LN Equity' and TIMESTAMP >= '2024-01-03 00:00:00 UTC' and TIMESTAMP < '2024-01-04 00:00:00 UTC' limit 1000

When SYMBOL_DATE is omitted, OneTick uses the current date to resolve the Bloomberg symbol.

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

The following example deliberately misspells FROM as ffrom to trigger a SQL error. Protected evaluation captures the 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 Cloud.

Next steps