SQL Query Support
This page shows how to run SQL queries on data in kdb Insights Enterprise.
kdb Insights Enterprise allows users to query a unified view of data using SQL. Data within all tiers of a package can be queried at once.
To facilitate SQL and free-form q queries without impacting resources dedicated to servicing critical production API calls, the kdb Insights Enterprise isolates them by using query environments.
A query environment is a collection Data Access Processes that are launched concurrently when deploying a package with the qe setting, and requests to them are routed through an isolated resource coordinator and gateway, as to not impact the primary set of databases.
For a detailed overview of SQL constraints and advanced features, see the service gateway SQL API.
Sandboxes
Log statements within a database may refer to themselves as sandboxes. This is synonymous with them being inside query environments.
Permissions
To use SQL, you require the following roles:
insights.query.data
insights.query.sql
To use qSQL, you require the following roles:
insights.query.data
insights.query.qsql
Query Environment
When manually authoring a package, to enable query environments, set the following:
router/router.yaml:
qe:
enabled: true
size: 1
The size setting allows you to control the amount of databases in a query environment, separate from the daps.instances size used in the package.
All requests pertaining to query environments should be sent to /servicegateway/qe/* as opposed to /servicegateway/*.
For example, /servicegateway/qe/meta may be used to get the replica counts of the query environment databases, while /servicegateway/meta would return details of the production databases.
SQL and the Web Interface
The SQL functionality is available in the Web Interface under Query, then the SQL tab.
Refer to Query in the Web Interface for details.
qSQL and the Web Interface
In kdb Insights Enterprise web interface the you can Query using qSQL against any packages that have been launched with Query Environment(s) enabled.
The SQL functionality is available in the web interface under Query, then the q tab.
If you wish to list what Query Environment(s) are available, you may do so with GET /servicegateway/kxi/meta and jq to print the results:
curl --header "Authorization: Bearer $INSIGHTS_TOKEN" "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/meta" | jq .payload.dap
[
{
"assembly": "insights-demo-qe",
"instance": "hb",
"startTS": "",
"endTS": ""
},
{
"assembly": "insights-demo-qe",
"instance": "rb",
"startTS": "1707-09-22T00:12:43.145224194",
"endTS": "2292-04-10T23:47:16.854775807"
},
{
"assembly": "insights-demo-qe",
"instance": "ib",
"startTS": "",
"endTS": ""
}
]
Querying with empty time values
Empty startTS and endTS indicate that particular tier does not yet have data
RESTfully using SQL
SQL queries are made by submitting POST requests with a JSON query, package, and target database name to the /servicegateway/qe/kxi/sql endpoint in kdb Insights Enterprise.
You can download query results in JSON or binary format as follows:
curl -X POST\
--header "Content-Type: application/json"\
--header "Accept: application/json"\
--header "Authorization: Bearer $INSIGHTS_TOKEN"\
--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"\
--header "Authorization: Bearer $INSIGHTS_TOKEN"\
--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"\
--header "Authorization: Bearer $INSIGHTS_TOKEN"\
--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, package, and target database name to the /servicegateway/qe/kxi/qsql endpoint in kdb Insights Enterprise.
You can download query results in JSON or binary format as follows:
curl -X POST\
--header "Content-Type: application/json"\
--header "Accept: application/json"\
--header "Authorization: Bearer $INSIGHTS_TOKEN"\
--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"\
--header "Authorization: Bearer $INSIGHTS_TOKEN"\
--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