Skip to content

GetData

The getData API is a generic data retrieval API that can be executed against an arbitrary database table. It is intended to run on a data access process and retrieve records from a database table using specified parameters.

Parameter:

Name Type Description
table symbol Name of table to retrieve data from
startTS timestamp Inclusive start time of period of interest
endTS timestamp Inclusive end time of period of interest
filter string q-sql where clause defining filter
columns symbol[] Columns to return in response
opts dictionary Dictionary of additional options

Opts dictionary:

Name Type Description
tsCol symbol Timestamp column to apply startTS/endTS to. Default is the partition column of table
sortCols symbol[] Columns to sort response payload on

Response:

The response is a list that includes columns to sort on, columns to select, and the table with the requested filters and options applied.

Application Codes

AC Description
INPUT
TYPE
LENGTH

Examples:

For these examples, assume that we have a handle opened assigned to h and that there is a partitioned table called trace in all data access processes with the following schema:

Name Type Description
sensorID int Sensor identifier
readTS timestamp Timestamp of reading, and the column which determines HDB partition
valFloat float Reading measurment
qual short Quality of the reading
updateTS timestamp Update timestamp of reading

In this example we do a basic get call with only the table and time range specified.

q)args
table  | `trace
startTS| -0Wp
endTS  | 0Wp 
q)res:h (`.da.execute;`.kxi.getData;hdr;args) / Make the API call and store the result in "res"
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

In this example we refine the time range by specifying a startTS.

q)args
table  | `trace
startTS| 2021.02.09D01:00:00.000000000
endTS  | 0Wp
q)res:h (`.da.execute;`.kxi.getData;hdr;args) / Make the API call and store the result in "res"
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

Here we specify 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) / Make the API call and store the result in "res"
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
``

Example using the filter argument. Here we ask for sensorIDs equal to 10i, as well as filtering for qual values between 1 and 3h. 

```q
q)args
table  | `trace
startTS| -0Wp
endTS  | 0Wp
filter | "sensorID=10i,qual within 1 3h"
q)res:h (`.da.execute;`.kxi.getData;hdr;args) / Make the API call and store the result in "res"
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
``

In this example we use the options dictionary to specify that the timestamp filter should apply to the updateTS column instead. 

```q
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) / Make the API call and store the result in "res"
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

Again we make use of the options dictionary, but this time to return the result sorted 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) / Make the API call and store the result in "res"
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