SQL

The SQL API allows queries to be distributed/aggregated across multiple DAPs. It supports a subset of the SQL operations in KXCE defined here, with the following additional constraints:

Only select is supported. This API cannot be used to create tables, insert records, or to drop a table.
Tables referenced in the query must be defined in the assembly configuration. While not strictly enforced, tables defined in more than one assembly should have the same schema. If they do not, some queries may not work correctly, unless only the common columns are referenced within the query.
All tables are assumed to be partitioned by date.
Requests are only distributed among the DAPs connected to the RC that receives the request (hence, multi-RC setups are not fully supported).
Some arithmetic operations are currently not/partially supported:
- round - always rounds to the nearest integer and not to a specified decimal point.
- floor and ceiling - not supported.
- like - not supported on symbol columns.
- order by - not supported.
- limit - not supported.
Restricting time with >=, <=, between work, but > and < are faster.

Note that, by default, this API is only enabled in sandboxes. To enable it in non-sandboxes, set the KXI_ALLOWED_SBX_APIS in the RCs and DAPs in which you want to enable it (see Service Gateway Configuration and Data Access Configuration).

Note that routing decisions across multiple assemblies is determined by specifying the desired assembly labels in the where clause of the SQL query. For example, suppose we had assemblies whose labels sections are:

# Assembly tsx-equity.
labels:
  exchange: tsx
  class: equity

# Assembly tsx-futures.
labels:
  exchange: tsx
  class: futures

# Assembly nyse-equity.
labels:
  exchange: nyse
  class: equity

# Assembly nyse-futures.
labels:
  exchange: nyse
  class: futures

# Assembly lse-equity.
labels:
  exchange: lse
  class: equity

# Assembly lse-futures.
labels:
  exchange: lse
  class: futures

The following queries target the following assemblies

Query	Assemblies	Explanation
`select ...`	All	No where clause defaults to all.
`select ... where exchange='tsx'`	`tsx-equity`, `tsx-futures`	One exchange, all classes.
`select ... where exchange='nyse' and class='equity'`	`nyse-equity`	Specify both exchange and class.
`select ... where exchange in ('tsx', 'lse') or class<>'futures'`	All except `nyse-futures`	Logically equivalent to `not(exchange='nyse' and class='futures')`.

Labels can be used to target as many or as few assemblies as required. Note also that labels need not be consistent across assemblies, allowing the creation of new assemblies with different labels within a running system. E.g. if we deploy the following assembly to the above set:

# New assembly with different labels.
labels:
  dev: john_doe

Then a query of the form select ... where dev='john_doe' targets only this new assembly.

Parameter:

Name	Type	Description
query	string	The SQL string to run on a target data access processes

Response:

The response is a table result with columns dependent on the SQL query passed into the API. Note that the response includes virtual columns (date and any labels) if explicitly specified, or if executing a query of the form select * from ....

Application Codes

AC	Description
ERR	General (unexpected) error.
TYPE	A SQL statement results in a type error.
LENGTH	A SQL length error.

Examples:

For these examples, assume we have a table trade with schema

Column	Type
sym	symbol
time	timestamp
price	float
size	int

and spread across the 6 assemblies above.

q

// Setup.
h:hopen`:gw_host:1234 / Hopen handle to gateway
cb:{res::(x;y)} / Define callback

Select all trade data from all assemblies for all time. Note that result includes the virtual columns date, exchange and class.

q

q)neg[h](`.kxi.sql;enlist[`query]!enlist"select * from trade";`cb;(0#`)!())
q)res 1

date       exchange class   sym   time                          price size
--------------------------------------------------------------------------
2021.01.01 nyse     equity  MSFT  2021.01.01D00:00:14.500000000 22.09 507
2021.01.01 nyse     equity  AAPL  2021.01.01D00:00:24.500000000 22.09 103
2021.01.01 lsx      equity  VOD   2021.01.01D00:01:34.500000000 22.10 107
2021.01.01 nyse     futures XYZH5 2021.01.01D00:01:54.500000000 22.10 647
2021.01.01 tsx      equity  BDRBF 2021.01.01D00:02:39.500000000 22.10 631
..

Select average price, restricting on date and exchange.

q

q)neg[h](`.kxi.sql;enlist[`query]!enlist"select date,sym,avg(price) from trade where (date between '2021.01.01' and '2021.01.07') and (exchange='nyse') group by date,sym";`cb;(0#`)!())
q)res 1

date       sym       price
------------------------------
2021.01.01 MSFT      25.93
2021.01.01 AAPL      30.12
2021.01.01 F         19.21
2021.01.02 AAPL      33.84
2021.01.02 T         11.74
..

Select minimum trade value by date for given time grouped by exchange.

q

q)neg[h](`.kxi.sql;enlist[`query]!enlist"select exchange,min(price*size) as min_value from trade where date='2021.01.05' and time<'2021-01-01 12:00:00' group by exchange";`cb;(0#`)!())
q)res 1

exchange min_value
------------------
tsx      213.41
nyse     329.45
lse      301.98