Skip to content

Data Access APIs

APIs that run under Data Access processes

.kxi. getData generic data retrieval qsql execute a qSQL query ping whether Data Access reaches a target

Each API function takes a single dictionary argument args.

.kxi.getData

Generic data retrieval

.kxi.getData args

Where args is

table name of table to retrieve data from (symbol) startTS inclusive start time of period of interest (timestamp) endTS exclusive end time of period of interest (timestamp) filter qSQL Where phrase defining filter (string) columns columns to return in response (symbol vector) opts dictionary of additional options (dictionary)

and opts is a dictionary:

tsCol       timestamp column to apply startTS/endTS to; 
            defaults to table’s partition column (symbol)
sortCols    columns to sort response payload on (symbol vector)

returns a list:

  1. columns to sort on
  2. columns to select
  3. table with the requested filters and options applied

For the examples, assume

  • a handle opened and assigned to h
  • a partitioned table trace in all data-access processes with the following schema:
sensorID   int         sensor identifier 
readTS     timestamp   timestamp of reading the column 
                       that determines HDB partition 
valFloat   float       reading measurement 
qual       short       quality of the reading 
updateTS   timestamp   update timestamp of reading 

A basic get call with only the table and time range specified.

q)args
table  | `trace
startTS| -0Wp
endTS  | 0Wp 
q)/ Make the API call and store the result in "res"
q)res:h (`.da.execute;`.kxi.getData;hdr;args) 
q)5#res[2] / Display only the first 5 records
sensorID readTS                        valFloat qual updateTS
----------------------------------------------------------------------------------
0        2021.02.09D00:00:00.000000000 283.0992 1    2021.02.10D19:02:25.332557500
0        2021.02.09D01:00:00.000000000 906.6764 5    2021.02.10D19:02:25.332557500
0        2021.02.09D02:00:00.000000000 546.6978 4    2021.02.10D19:02:25.332557500
0        2021.02.09D03:00:00.000000000 164.0931 4    2021.02.10D19:02:25.332557500
0        2021.02.09D04:00:00.000000000 663.1649 4    2021.02.10D19:02:25.332557500

Refine the time range by specifying startTS.

q)args
table  | `trace
startTS| 2021.02.09D01:00:00.000000000
endTS  | 0Wp
q)res:h (`.da.execute;`.kxi.getData;hdr;args) 
q)5#res[2] / Display only the first 5 records
sensorID readTS                        valFloat qual updateTS
----------------------------------------------------------------------------------
0        2021.02.09D01:00:00.000000000 906.6764 5    2021.02.10D19:02:25.332557500
0        2021.02.09D02:00:00.000000000 546.6978 4    2021.02.10D19:02:25.332557500
0        2021.02.09D03:00:00.000000000 164.0931 4    2021.02.10D19:02:25.332557500
0        2021.02.09D04:00:00.000000000 663.1649 4    2021.02.10D19:02:25.332557500
0        2021.02.09D05:00:00.000000000 537.0982 4    2021.02.10D19:02:25.332557500

Select only a subset of the columns.

q)args
table  | `trace
startTS| -0Wp
endTS  | 0Wp
columns| `sensorID`readTS`valFloat
q)res:h (`.da.execute;`.kxi.getData;hdr;args) 
q)5#res[2] / Display only the first 5 records
sensorID readTS                        valFloat
-----------------------------------------------
0        2021.05.12D00:00:00.000000000 278.1034
0        2021.05.12D01:00:00.000000000 256.3526
0        2021.05.12D02:00:00.000000000 73.38756
0        2021.05.12D03:00:00.000000000 911.9714
0        2021.05.12D04:00:00.000000000 171.8527

Use the filter argument to ask for sensor IDs equal to 10i, and qual values between 1 and 3h.

q)args
table  | `trace
startTS| -0Wp
endTS  | 0Wp
filter | "sensorID=10i,qual within 1 3h"
q)res:h (`.da.execute;`.kxi.getData;hdr;args) 
q)5#res[2] / Display only the first 5 records
sensorID readTS                        valFloat qual updateTS
----------------------------------------------------------------------------------
10       2021.05.12D03:00:00.000000000 781.8174 3    2021.05.13D14:01:56.802276100
10       2021.05.12D04:00:00.000000000 951.9392 2    2021.05.13D14:01:56.802276100
10       2021.05.12D07:00:00.000000000 971.0995 3    2021.05.13D14:01:56.802276100
10       2021.05.12D13:00:00.000000000 103.5567 2    2021.05.13D14:01:56.802276100
10       2021.05.12D14:00:00.000000000 297.4721 1    2021.05.13D14:01:56.802276100

Use the options dictionary to apply the timestamp filter to the updateTS column.

q)args
table  | `trace
startTS| 2021.05.13D14:05:00.000000000
endTS  | 0Wp
opts   | (,`tsCol)!,`updateTS
q)res:h (`.da.execute;`.kxi.getData;hdr;args)
q)5#res[2] / Display only the first 5 records
sensorID readTS                        valFloat qual updateTS
----------------------------------------------------------------------------------
0        2021.05.12D00:00:00.000000000 458.3568 5    2021.05.13D14:05:06.802253100
0        2021.05.12D01:00:00.000000000 919.25   3    2021.05.13D14:05:06.802253100
0        2021.05.12D02:00:00.000000000 465.7557 1    2021.05.13D14:05:06.802253100
0        2021.05.12D03:00:00.000000000 609.1758 2    2021.05.13D14:05:06.802253100
0        2021.05.12D04:00:00.000000000 349.6804 1    2021.05.13D14:05:06.802253100

Use of the options dictionary to sort the result by qual and valFloat.

q)args
table  | `trace
startTS| 2021.05.13D14:05:00.000000000
endTS  | 0Wp
opts   | (,`sortCols)!,`qual`valFloat
q)res:h (`.da.execute;`.kxi.getData;hdr;args)
q)5#res[2] / Display only the first 5 records
sensorID readTS                        valFloat   qual updateTS
------------------------------------------------------------------------------------
99       2021.05.12D08:00:00.000000000 0.00825664 0    2021.05.13D14:11:56.802232800
39       2021.05.12D12:00:00.000000000 0.09356276 0    2021.05.13D14:12:36.802253700
70       2021.05.12D02:00:00.000000000 0.1157809  0    2021.05.13D14:17:56.802235900
1        2021.05.12D01:00:00.000000000 0.197002   0    2021.05.13D14:17:46.804990700
33       2021.05.12D12:00:00.000000000 0.2034092  0    2021.05.13D14:13:16.803428200

.kxi.ping

Whether a data-access process reaches a target

.kxi.ping args

Where args is an empty dictionary, returns a boolean atom.

q)res:h (`.da.execute; `.kxi.ping;hdr;()!())
q)res
1b

.kxi.qsql

Execute a qSQL query

.kxi.qsql args

Where args is

query qSQL query (string)

returns a list:

  1. a dictionary of the response code rc and application code ac
  2. the result of executing the query

Application codes:

INPUT       query is not a string
TYPE        executing query signals a type error
            e.g "where id=`a", when id is an int column
LENGTH      executing query signals a length error
            e.g "where id= 1 2", when column doesn't have a count of 2

For the examples assume table t was created in a target process as

t:([]id:til 10)

A simple select from the target table t for the id of 4. Response and application codes are both OK.

q)qry:"select from t where id=4"
q)res: h (`.da.execute;`.kxi.qsql; hdr; (enlist`query)!enlist qry)
q)res 0
rc| 0
ac| 0
q)res 1
id
--
4

A badly formatted query with a Where phrase that checks equality between a long column and a character, resulting in a type error: response payload is null and has a header with a response code of APP_DB and an application code of TYPE.

q)qry:"select from t where id=`a"
q)res: h (`.da.execute;`.kxi.qsql; hdr; (enlist`query)!enlist qry)
q)res 0
rc| 6
ac| 11
q)(::)~res 1
1b