SQL query support

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

To facilitate SQL and free-form q queries without impacting resources dedicated to servicing critical production GetData API calls, free-form queries cannot be run against the production databases themselves. The KX Insights Platform isolates them by using sandboxes. A sandbox is a collection of a Resource Coordinator, Aggregator, and Data Access Processes to distribute across which allow free-form queries as well as GetData API calls.

Sandboxes are always represented by a UUIDv4 ID.

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

Permissions

To use SQL across all tiers of an assembly and make sandboxes you require the following roles:

insights.query.data
insights.query.sql

To use qsql against a single RDB, IDB or HDB, you require the following roles:

insights.query.data
insights.query.qsql

SQL and the UI

Sandboxes are integrated into the KX Insights Platform UI and do not require additional setup by users. The lifetime of a sandbox is associated with the lifetime of a UI editor.

The SQL functionality is available in the UI under Data. Select an assembly by name, and then a table. Note that any table in the assembly can be queried despite choosing only a single table.

Explore in the UI

RESTfully using SQL

To utilize SQL via REST, you must create a new sandbox, then submit queries through the Service Gateway, providing the sandbox ID in the URL. When finished with your sandbox, you must tear it down to release the resources that it is using.

You may acquire the hostname and a new token of your install as below,

# Export env vars for host/token
INSIGHTS_HOSTNAME=$(kubectl get ingress --no-headers | awk '{print $3}')
INSIGHTS_TOKEN=$(curl --header "Content-Type: application/x-www-form-urlencoded" -d "grant_type=client_credentials&client_id=redacted&client_secret=redacted" "https://${INSIGHTS_HOSTNAME}/auth/realms/insights/protocol/openid-connect/token" | jq '.access_token' | tr -d '"')

To create a new sandbox, you must make a POST request to the kxi-controller service, and provide it with an assembly name.

To get the name of an assembly, you may use the /servicegateway/kxi/getMeta endpoint.

The following curl request uses jq to get whatever happens to be the first assembly in your system.

ASSEMBLY_NAME=$(curl -X POST --header "Content-Type: application/json" --header "Accept: application/json" --header "Authorization: Bearer $INSIGHTS_TOKEN" "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getMeta" | jq '.payload.assembly[0].assembly' | tr -d '"')

Create a new sandbox:

Distributed SQLTargeted QSQL

Make a POST call to /kxicontroller/sandbox given an assembly name

SANDBOX_ID=$(curl https://${INSIGHTS_HOSTNAME}/kxicontroller/sandbox --header "Content-Type: application/json" --header "Authorization: Bearer $INSIGHTS_TOKEN" -d "{\"name\": \"$ASSEMBLY_NAME\"}" | tr -d '"')

Make a POST call to /kxicontroller/sandbox given an assembly name and database instance

SANDBOX_ID=$(curl https://${INSIGHTS_HOSTNAME}/kxicontroller/sandbox --header "Content-Type: application/json" --header "Authorization: Bearer $INSIGHTS_TOKEN" -d "{\"name\": \"$ASSEMBLY_NAME\", \"instance\": \"rdb\"}" | tr -d '"')

As a sandbox is being created, you may check on the status of the sandbox with /kxicontroller/sandbox/{id}/status.

curl --header "Authorization: Bearer $INSIGHTS_TOKEN"\
    https://${INSIGHTS_HOSTNAME}/kxicontroller/sandbox/${SANDBOX_ID}/status

Once a sandbox has been created, you may submit SQL queries to it:

Distributed SQLTargeted QSQL

Make a POST call to /servicegateway/sandbox/{SANDBOX_ID}/kxi/sql given a query.

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

Results can also be obtained as JSON by requesting Accept: application/json and -o results.json.

Make a POST call to /servicegateway/sandbox/{SANDBOX_ID}/qsql given a query.

curl -X POST\
    --header "Content-Type: application/json"\
    --header "Accept: application/octet-stream"\
    --header "Authorization: Bearer $INSIGHTS_TOKEN"\
    --data '{"query":"select count i from trade"}'\
    -o results.dat\
    "https://${INSIGHTS_HOSTNAME}/servicegateway/sandbox/${SANDBOX_ID}/qsql"

Supported for targeted sandboxes only

This operation is not available against distributed sandboxes

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

-9!read1`:results.dat

Tearing down

UI sessions

Each Explore Tab open in the UI corresponds to a sandbox, and uses resources specific to that UI session. When the corresponding Explore Tab is closed, the sandbox resources will be released and the sandbox torn down.

Monitor sandbox sessions

Sandboxes can become disassociated with a UI session if the browser itself is closed. In this case, or in any other case where sandbox management is required, use the REST API for listing and tearing down sandboxes mentioned in the next section.

RESTfully teardown unused sandboxes

Make a POST request to /kxicontroller/sandbox/{id}/teardown. Sandbox IDs can be found as the UUID in a sandbox pod name, for example 07c1d2d7-4b43-46e4-74be-747abb4e59fa in sandbox-07c1d2d7-4b43-46e4-74be-747abb4e59fa-dap-rdb-0

curl -X POST --header "Authorization: Bearer $INSIGHTS_TOKEN" \
    https://${INSIGHTS_HOSTNAME}/kxicontroller/sandbox/${SANDBOX_ID}/teardown

DELETE

This operation does not use HTTP DELETE, it may move to DELETE in the future, for historical reasons kdb+ supports only POST/GET