Skip to content

SQL

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

  1. Only select is supported. This API cannot be used to create tables, insert records, or to drop a table.
  2. 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.
  3. All partitioned tables are assumed to be partitioned by date.
  4. Requests are only distributed among the DAPs connected to the RC that receives the request (hence, multi-RC setups are not fully supported).
  5. 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.
  6. Restricting time with >=, <=, between work, but > and < are faster.
  7. Joins between two select statements that do not contain an aggregation are only supported if the data is co-located in the same DAP and both tables are not partitioned. For example, if table1 lives only in assembly1 and table2 lives only in assembly2, then a query like "(select * from table1) left join (select * from table2) ..." will not work. However, "(select ... from table1 ... group by ...) left join (select ... from table2 ... group by ...)" will.

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 Database configuration).

Note that routing decisions across multiple assemblies is determined by specifying the desired assembly labels in the where clause of the SQL query, or by using the scope.assembly parameter (see Scope) to target an individual assembly.

For example, suppose we had assemblies whose name and labels sections are:

Labels in queries

In a query, labels should be referenced with a label_ prefix. For example, the label class would be referenced as label_class in the query.

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

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

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

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

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

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

Then the following queries target the following assemblies

query scope.assembly assemblies explanation
select ... All
select ... where label_exchange='tsx' tsx-equity, tsx-futures
select ... where label_exchange='nyse' and class='equity' nyse-equity
select ... where label)exchange in ('tsx', 'lse') or class<>'futures' All except nyse-futures
select ... tsx_eq tsx_eq
select ... where label_exchange='tsx' tsx_eq tsx_eq
select ... where label_exchange='tsx' nyse_eq None (failure)

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 label_dev='john_doe' targets only this new assembly.

Parameters

name type description
query string The SQL query to run on a target data access processes. Queries in the database are ANSI SQL compliant with some limitations. See the SQL reference page for full SQL query details.

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

Duplicate virtual label columns

When ALLOW_OLD_LABEL_STYLE is enabled, two copies of label virtual columns will be returned, both the old style of just the label name as well as the label_ prefixed version. See the upgrade considerations

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 the schema detailed below.

column type
sym symbol
time timestamp
price float
size int 
tables:
  trade:
    type: partitioned
    prtnCol: time
    sortColsOrd: [sym]
    sortColsDisk: [sym]
    columns:
      - name: sym
        type: symbol
        attrMem: grouped
        attrDisk: parted
        attrOrd: parted
      - name: time
        type: timestamp
      - name: price
        type: float
      - name: size
        type: int

and spread across the 6 assemblies above.

Basic query

Select all trade data from all assemblies for all time. Note that result includes the virtual columns label_date, label_exchange and label_class.

Gateway URL

The GATEWAY variable below is defined as an IPC connection to the Service Gateway. For example `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace.

GATEWAY (`.kxi.sql; enlist[`query]!enlist"select * from trade"; `; ()!())

The body of the response is a tuple with a header and the payload.

Response Header 
rcvTS     | 2021.01.10D12:00:00.000000000
corr      | 554ed9a1-e138-4948-8aa0-33bf371bbfe1
protocol  | `gw
logCorr   | "554ed9a1-e138-4948-8aa0-33bf371bbfe1"
client    | `:10.1.1.1:5050
api       | `.kxi.sql
ogRcID    | `insights-qe-resource-coordinator-0:5060
retryCount| 0
to        | 2021.01.10D12:01:00.000000000
agg       | `:10.1.1.1:5070
pvVer     | 33
rpID      | 0
refVintage| 7300
rc        | 0h
ac        | 0h
ai        | ""

Inspecting the payload of the response, we see the query result.

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

Gateway URL

The $GATEWAY variable should point at your kdb Insights install. For a microservice install, this will be the hostname of the install using port 8080. For an enterprise install, this is your $INSIGHTS_HOSTNAME with /servicegateway/qe as the URL prefix.

select * from trade

This example uses the above query set as a variable called $QUERY.

curl -X POST "$GATEWAY/kxi/sql" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n --arg query "$QUERY" '{ query: $query }' | jq -cr .)"

The body of the response is an object with a header and the payload.

{ "header": { ... }, "payload": { ... } }
Response Header 
"header": {
    "rcvTS": "2021-01-10T15:20:55.277000000",
    "corr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "protocol": "gw",
    "logCorr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "client": ":10.6.147.140:5050",
    "http": "json",
    "api": ".kxi.sql",
    "ogRcID": "insights-qe-resource-coordinator-0:5060",
    "retryCount": 0,
    "to": "2021-01-10T15:21:55.277000000",
    "agg": ":10.6.152.211:5070",
    "pvVer": 2,
    "rpID": 0,
    "refVintage": 7300,
    "rc": 0,
    "ac": 0,
    "ai": ""
}

Inspecting the payload of the response, we see the query result.

[
    {
        "sym": "MSFT",
        "time": "2021-01-01T15:20:17.381247611",
        "price": 315.7976,
        "size": 1814,
        "date": "2021-01-01",
        "label_exchange": "nyse",
        "label_class": "equity",
    },
    {
        "sym": "AAPL",
        "time": "2021-01-01T15:20:17.381247612",
        "price": 750.7542,
        "size": 8947,
        "date": "2021-01-01",
        "label_exchange": "nyse",
        "label_class": "equity"
    },
    ..
]

Using scope

Setting the scope parameter targets the specified assembly by name.

Gateway URL

The GATEWAY variable below is defined as an IPC connection to the Service Gateway. For example `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace.

GATEWAY (`.kxi.sql; `query`scope!("select * from trade";enlist[`assembly]!enlist`nyse_eq); `; ()!())

The body of the response is a tuple with a header and the payload.

Response Header 
rcvTS     | 2021.01.10D12:00:00.000000000
corr      | 554ed9a1-e138-4948-8aa0-33bf371bbfe1
protocol  | `gw
logCorr   | "554ed9a1-e138-4948-8aa0-33bf371bbfe1"
client    | `:10.1.1.1:5050
api       | `.kxi.sql
ogRcID    | `insights-qe-resource-coordinator-0:5060
retryCount| 0
to        | 2021.01.10D12:01:00.000000000
agg       | `:10.1.1.1:5070
pvVer     | 33
rpID      | 0
refVintage| 7300
rc        | 0h
ac        | 0h
ai        | ""

Inspecting the payload of the response, we see the query result.

date       sym   time                          price size label_exchange label_class
------------------------------------------------------------------------------------
2021.01.01 MSFT  2021.01.01D00:00:14.500000000 22.09 507  nyse           equity
2021.01.01 AAPL  2021.01.01D00:00:24.500000000 22.09 103  nyse           equity
..

Gateway URL

The $GATEWAY variable should point at your kdb Insights install. For a microservice install, this will be the hostname of the install using port 8080. For an enterprise install, this is your $INSIGHTS_HOSTNAME with /servicegateway/qe as the URL prefix.

QUERY='select * from trade'
SCOPE='{ "assembly": "nyse_eq" }'

curl -X POST "$GATEWAY/kxi/sql" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n --arg query "$QUERY" --arg scope "$SCOPE" '{ query: $query, scope: $scope }' | jq -cr .)"

The body of the response is an object with a header and the payload.

{ "header": { ... }, "payload": { ... } }
Response Header 
"header": {
    "rcvTS": "2021-01-10T15:20:55.277000000",
    "corr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "protocol": "gw",
    "logCorr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "client": ":10.6.147.140:5050",
    "http": "json",
    "api": ".kxi.sql",
    "ogRcID": "insights-qe-resource-coordinator-0:5060",
    "retryCount": 0,
    "to": "2021-01-10T15:21:55.277000000",
    "agg": ":10.6.152.211:5070",
    "pvVer": 2,
    "rpID": 0,
    "refVintage": 7300,
    "rc": 0,
    "ac": 0,
    "ai": ""
}

Inspecting the payload of the response, we see the query result.

[
    {
        "sym": "MSFT",
        "time": "2021-01-01T15:20:17.381247611",
        "price": 315.7976,
        "size": 1814,
        "date": "2021-01-01",
        "label_exchange": "nyse",
        "label_class": "equity",
    },
    {
        "sym": "AAPL",
        "time": "2021-01-01T15:20:17.381247612",
        "price": 750.7542,
        "size": 8947,
        "date": "2021-01-01",
        "label_exchange": "nyse",
        "label_class": "equity"
    },
    ..
]

Average price

Select average price, restricting on date and exchange.

Gateway URL

The GATEWAY variable below is defined as an IPC connection to the Service Gateway. For example `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace.

query: "select date,sym,avg(price) from trade ",
  "where (date between '2021.01.01' and '2021.01.07') ",
  "and (label_exchange='nyse') group by date,sym";
GATEWAY (`.kxi.sql; enlist[`query]!enlist query;`;()!())

The body of the response is a tuple with a header and the payload.

Response Header 
rcvTS     | 2021.01.10D12:00:00.000000000
corr      | 554ed9a1-e138-4948-8aa0-33bf371bbfe1
protocol  | `gw
logCorr   | "554ed9a1-e138-4948-8aa0-33bf371bbfe1"
client    | `:10.1.1.1:5050
api       | `.kxi.sql
ogRcID    | `insights-qe-resource-coordinator-0:5060
retryCount| 0
to        | 2021.01.10D12:01:00.000000000
agg       | `:10.1.1.1:5070
pvVer     | 33
rpID      | 0
refVintage| 7300
rc        | 0h
ac        | 0h
ai        | ""

Inspecting the payload of the response, we see the query result.

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

Gateway URL

The $GATEWAY variable should point at your kdb Insights install. For a microservice install, this will be the hostname of the install using port 8080. For an enterprise install, this is your $INSIGHTS_HOSTNAME with /servicegateway/qe as the URL prefix.

Query

select date,sym,avg(price) from trade
    where (date between '2021.01.01' and '2021.01.07') and (label_exchange='nyse')
    group by date,sym

This example uses the above query set as a variable called $QUERY.

curl -X POST "$GATEWAY/kxi/sql" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n --arg query "$QUERY" '{ query: $query }' | jq -cr .)"

The body of the response is an object with a header and the payload.

{ "header": { ... }, "payload": { ... } }
Response Header 
"header": {
    "rcvTS": "2021-01-10T15:20:55.277000000",
    "corr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "protocol": "gw",
    "logCorr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "client": ":10.6.147.140:5050",
    "http": "json",
    "api": ".kxi.sql",
    "ogRcID": "insights-qe-resource-coordinator-0:5060",
    "retryCount": 0,
    "to": "2021-01-10T15:21:55.277000000",
    "agg": ":10.6.152.211:5070",
    "pvVer": 2,
    "rpID": 0,
    "refVintage": 7300,
    "rc": 0,
    "ac": 0,
    "ai": ""
}

Inspecting the payload of the response, we see the query result.

[
    {
        "sym": "MSFT",
        "price": 25.93,
        "date": "2021-01-01"
    },
    {
        "sym": "AAPL",
        "price": 30.12,
        "date": "2021-01-01"
    },
    ..
]

Grouping

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

Gateway URL

The GATEWAY variable below is defined as an IPC connection to the Service Gateway. For example `:insights-qe-gateway:5050 would connect to the query environment gateway within an insights namespace.

query: "select label_exchange,min(price*size) as min_value from trade ",
  "where date = '2021.01.05' and time<'2021-01-01 12:00:00' ",
  "group by label_exchange";
GATEWAY (`.kxi.sql; enlist[`query]!enlist query;`;()!())
The body of the response is a tuple with a header and the payload.

Response Header 
rcvTS     | 2021.01.10D12:00:00.000000000
corr      | 554ed9a1-e138-4948-8aa0-33bf371bbfe1
protocol  | `gw
logCorr   | "554ed9a1-e138-4948-8aa0-33bf371bbfe1"
client    | `:10.1.1.1:5050
api       | `.kxi.sql
ogRcID    | `insights-qe-resource-coordinator-0:5060
retryCount| 0
to        | 2021.01.10D12:01:00.000000000
agg       | `:10.1.1.1:5070
pvVer     | 33
rpID      | 0
refVintage| 7300
rc        | 0h
ac        | 0h
ai        | ""

Inspecting the payload of the response, we see the query result.

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

Gateway URL

The $GATEWAY variable should point at your kdb Insights install. For a microservice install, this will be the hostname of the install using port 8080. For an enterprise install, this is your $INSIGHTS_HOSTNAME with /servicegateway/qe as the URL prefix.

Query

select label_exchange,min(price*size) as min_value from trade
    where date = '2021.01.05' and time<'2021-01-01 12:00:00'
    group by label_exchange

This example uses the above query set as a variable called $QUERY.

curl -X POST "$GATEWAY/kxi/sql" \
    -H "Content-Type: application/json" \
    -H "Accept: application/json" \
    -H "Authorization: Bearer $INSIGHTS_TOKEN" \
    -d "$(jq -n --arg query "$QUERY" '{ query: $query }' | jq -cr .)"

The body of the response is an object with a header and the payload.

{ "header": { ... }, "payload": { ... } }
Response Header 
"header": {
    "rcvTS": "2021-01-10T15:20:55.277000000",
    "corr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "protocol": "gw",
    "logCorr": "cdd6a8f8-31c3-4a4b-9637-db9502204587",
    "client": ":10.6.147.140:5050",
    "http": "json",
    "api": ".kxi.sql",
    "ogRcID": "insights-qe-resource-coordinator-0:5060",
    "retryCount": 0,
    "to": "2021-01-10T15:21:55.277000000",
    "agg": ":10.6.152.211:5070",
    "pvVer": 2,
    "rpID": 0,
    "refVintage": 7300,
    "rc": 0,
    "ac": 0,
    "ai": ""
}

Inspecting the payload of the response, we see the query result.

[
    {
        "label_exchange": tsx,
        "min_value": 213.41
    },
    {
        "label_exchange": nyse,
        "min_value": 329.45
    },
    {
        "label_exchange": lse,
        "min_value": 301.98
    }
]