SQL Query Support

This page shows how to run SQL queries on data in kdb Insights.

kdb Insights allows users to query a unified view of data using SQL. Data within all tiers of an assembly can be queried at once.

For a detailed overview of SQL constraints and advanced features, see the service gateway SQL API.

RESTfully using SQL

SQL queries are made by submitting POST requests with a JSON query, assembly, and target database name to the /servicegateway/kxi/sql endpoint in insights.

You can download query results in JSON or binary format as follows:

JSONbinarybinary, in a structured text format

    curl -X POST\
        --header "Content-Type: application/json"\
        --header "Accept: application/json"\
        --data '{"query":"SELECT size FROM trade"}'\
        -o results.dat\
        "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/sql"

The SQL query results are saved as IPC binary results and may be compressed. You can inspect the downloaded file with q:

-9!read1`:results.dat / octet-stream

    curl -X POST\
        --header "Content-Type: application/json"\
        --header "Accept: application/octet-stream"\
        --data '{"query":"SELECT size FROM trade"}'\
        -o results.dat\
        "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/sql"

The SQL query results are saved as IPC binary results and may be compressed. You can inspect the downloaded file with q:

-9!read1`:results.dat / octet-stream

    curl -X POST\
        --header "Content-Type: application/json"\
        --header "Accept: application/struct-text"\
        --data '{"query":"SELECT size FROM trade"}'\
        -o results.dat\
        "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/sql"

The SQL query results are saved as IPC binary results and may be compressed. You can inspect the downloaded file with q:

.j.k "c"$read1`:results.dat / struct-text

RESTfully using qSQL

qSQL queries are made by submitting POST requests with a JSON query, assembly, and target database name to the /servicegateway/kxi/qsql endpoint in insights.

You can download query results in JSON or binary format as follows:

JSONbinary

    curl -X POST\
        --header "Content-Type: application/json"\
        --header "Accept: application/json"\
        --data '{"query":"select size from trade", "target": "rb", "assembly": "dev-assembly"}'\
        -o results.json\
        "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/qsql"

    curl -X POST\
        --header "Content-Type: application/json"\
        --header "Accept: application/octet-stream"\
        --data '{"query":"select size from trade", "target": "rb", "assembly": "dev-assembly"}'\
        -o results.json\
        "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/qsql"

The qSQL query results are saved as IPC binary results and may be compressed. You can inspect the downloaded file with q:

-9!read1`:results.dat