How to Query OneTick Cloud Market Data in KDB-X

To get real market data into KDB-X (the next generation of kdb+), install the onetickcloud module, load it into the .otc namespace, and call .otc.sql_rest with a SQL query. The result comes back as a native q table, ready for analytics. The module ships with demonstration credentials, so it works out of the box with no OneTick account or signup required.

The data comes from OneTick, the market data and tick analytics platform that is now part of KX. Sample databases cover US, European, Canadian, and UK equities, CME and Eurex derivatives, ICE commodities, global FX, and curated futures. In this post I’ll walk through installing the module, running your first query, and moving to production credentials when you need more than sample data.

What Is the OneTickCloud Module?

The OneTickCloud module is a KDB-X module that queries OneTick Cloud market data over REST and returns results as q tables. It exposes one core function, sql_rest, which takes a SQL statement, authenticates against the OneTick Cloud API using OAuth2, and returns the response directly into your q session. There is no import step and no intermediate files.

Out of the box, the module’s built-in demonstration credentials give you access to OneTick sample data with a 10MB per-query download limit. Production credentials, issued to OneTick subscribers, raise that limit to 1GB or 20GB and unlock the full data catalog.

How Do I Install It?

You need KDB-X with a valid license and kurl, the HTTP client included with KDB-X. Installing zlib is optional but recommended; it enables gzip-compressed responses, which reduces bandwidth and latency. Queries still work without it, the payload is just larger on the wire.

Download the module zip for your platform and extract it into your KDB-X module directory:

unzip -q l64-onetickcloud.zip -d "${QHOME}/mod/kx"

That’s the whole installation.

How Do I Query Market Data?

Load the module and pass a SQL string to sql_rest. This example pulls Vodafone trades from the London Stock Exchange sample database:

// Load the onetickcloud module into the .otc namespace
.otc:use`kx.onetickcloud

// Query LSE sample data for Vodafone trades
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 10";

data:.otc.sql_rest[sql]

data

The result is a native q table:

SYMBOL_NAME TIMESTAMP                     TRADE_ID              PRICE   SIZE
-----------------------------------------------------------------------------
VOD         2024.01.03D07:15:10.133000000 "1075835351045197936" 69.76   40000
VOD         2024.01.03D08:00:06.232000000 "911727684223506"     70      184613
VOD         2024.01.03D08:00:06.233000000 "911727684223588"     70.01   140
...

From here it’s ordinary q. Aggregate it, join it against your own tables, persist it, feed it into your analytics.

sql_rest also accepts a dictionary if you need to control the timezone of the resulting timestamps:

arg:(`sql`timezone)!(sql;"America/New_York");
data:.otc.sql_rest[arg]

Timezone accepts any IANA timezone string and defaults to UTC.

What Sample Data Is Available?

The demonstration credentials cover ten sample markets, each available as tick data, 1-minute bars, and daily bars. Tick databases (for example LSE_SAMPLE, US_COMP_SAMPLE, CME_SAMPLE) cover January 1 to 6, 2024. Bar and daily databases (LSE_SAMPLE_BARS, LSE_SAMPLE_DAILY, and so on) cover January 1 to 31, 2024.

The markets: US Consolidated Equities (28 MICs), European Consolidated Equities (169 MICs across 21 countries), Canadian Consolidated (20 MICs), London Stock Exchange, CME Group, Eurex, ICE US, ICE Europe Commodities, Global FX (around 3,000 pairs), and a global curated futures set covering 278 products. The full breakdown is on the OneTick sample data coverage page.

Keep your queries inside these date ranges. A query outside the sample window returns no rows.

How Do I Use My Own Credentials?

Production credentials can be set two ways, and both override the built-in defaults. Environment variables, read at module load time:

export OTP_CLIENT_ID='your_client_id'
export OTP_CLIENT_SECRET='your_client_secret'

Or at runtime with set_credentials:

.otc.set_credentials[(`client_id`client_secret)!("your_client_id";"your_secret")];

The priority order is built-in defaults, then environment variables, then set_credentials, lowest to highest. Each layer only overrides the fields it sets. You can check what’s active at any time with .otc.get_credentials[].

What Are the Limits and Common Errors?

Demonstration credentials are capped at 10MB of data per query. When you hit the cap, OneTick returns error ERR_00000707AAAAA telling you the maximum allowed units have been consumed. Production credentials raise the limit to 1GB, or 20GB depending on subscription.

Wrong credentials fail at the token step with a clear message: “No token received. Check credentials are correct.” Malformed SQL comes back as a parse error string with the position of the problem.

For production code, wrap calls in protected evaluation so a failed query doesn’t crash your q process:

t:@[.otc.sql_rest; sql; {x}]
$[10h=type t;
    show "Query failed: ",t;
    show t
  ]

More SQL Query Examples

Retrieving 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 From the US Composite Sample 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 From the US Composite Sample 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

Retrieve US End of Day Metrics for the Composite Adjusted for Corporate Actions

select CLOSE, VOLUME, CORP_ACTIONS('CLOSE') as ADJ_CLOSE from US_COMP_SAMPLE_DAILY.DAY where SYMBOL_NAME = 'WMT' AND EXCHANGE = '' and TIMESTAMP >= '2024-02-10 00:00:00.000 UTC' and TIMESTAMP < '2024-03-10 00:00:00.000 UTC'

Retrieve UK Trades by Bloomberg Symbology From the LSE Sample Database

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

FAQ

Do I Need a OneTick Account?

No. The module ships with demonstration credentials that work immediately. You only need OneTick production credentials to go beyond the sample data and the 10MB query limit.

What Do OneTick Cloud SQL Queries Require?

a) Database and Table to be specified. e.g. US_COMP_SAMPLE.TRD

b) Where clause filtered by SYMBOL_NAME. e.g. SYMBOL_NAME = 'CSCO'

c) Where clause filtered by Time window. with TIMESTAMP >= and TIMESTAMP <.

What Format Do Results Come Back In?

Native q tables. No parsing or conversion step.

Can I Query With q Instead of SQL?

The module’s interface is SQL via sql_rest. Once the data is in your session, everything downstream is q.

Where Do I Get Production Credentials?

Production credentials are issued to OneTick subscribers. Contact OneTick or start a cloud free trial.

Get Started

The module download and full documentation, including the complete sql_rest reference and configuration options, are on the KX Developer Center. Install it, run the quickstart query above, and you have real exchange data in a q table in under five minutes.

To learn more about OneTick Coverage visit https://www.onetick.com/market-data-coverage

And for OneTick SQL documentation visit https://sql.docs.sol.onetick.com/intro.html