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 configured database table using a specified dictionary of parameters.
Parameters
Name | Required | Type | Description |
---|---|---|---|
table | yes | symbol | Name of table to retrieve data from |
startTS | yes | timestamp | Inclusive start time of period of interest |
endTS | yes | timestamp | Exclusive end time of period of interest |
filter | no | list[] | List of triadic lists of the form (function;column name;parameter) |
groupBy | no | symbol[] | List of columns to group aggregation result by |
agg | no | symbol[] | List of triples of aggregations or columns to select. e.g. Aggregation dict example:(`c1`avg`price;`c2`sum`size) , basic select example `price`size |
sortCols | no | symbol[] | Columns to sort result data on |
In addition to the above parameters any of the labels defined in the assembly file of the DAPs can be used in the args
dictionary to only select data from DAPs in assemblies matching that label. So for example if the there are DAPs belonging to two assemblies, one with a region
label of canada
another with a region label of europe
, then a dictionary with a region
value of canada
would return only the canada
data. The assembly label arguments are always optional, and will hit all assemblies when not specified.
filter
The filter
parameter is used for applying custom filtering to the query. It's specified via a list of triples where each triple has the form (operator; column name; values).
(("<=";`realFloat;100f);("within";`qual;2 3h))
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"filter\":[[\"<=\",\"valFloat\",100],[\"within\",\"qual\",[0,2]]]}" \
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"filter\":[[\"<=\",\"valFloat\",100],[\"within\",\"qual\",[0,2]]]}" \
The filters are applied in the order they are defined when selecting from the table.
Supported filter functions are:
in within < > like not <= >= =
Nested filters are not currently supported.
groupBy
Specifies the columns which define the by-clause used in the selection of the data. Can be used in conjunction with agg
parameter to perform aggregations across column values.
`sensorID`qual
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"groupBy\":\"sensorID\",\"qual\"]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"groupBy\":\"sensorID\",\"qual\"]}"
agg
The agg
parameter specifies the columns and/or aggregations to return after selecting the data. Argument is specified as a list of symbols, or as a list of lists of symbols. Behaviour differs depending on whether the parameter is specified as a list or list of lists.
If specified as a list of symbols, then those columns are selected and returned from the table across DAPs.
`sensorID`readTS`qual`valFloat
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"agg\":[\"snsorID\",\"readTS\"]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"agg\":[\"snsorID\",\"readTS\"]}"
Alternatively the selection can be specified to aggregate the column with a new name before returning. This is specified as a list of lists where the first element is the new column name, the second element is the aggregation function to use, and the third is the column to aggregate.
Below does the equivalent aggregation to select c1:avg valFloat, c2:min qual from t
. Where the c1
, and c2
are the names of the returned columns are the average of valFloat
and the minimum of qual
column.
(`c1`avg`valFloat;`c2`min`qual)
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"groupBy\":[\"sensorID\"],\"agg\":[[\"c1\",\"avg\",\"valFloat\"]]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"groupBy\":[\"sensorID\"],\"agg\":[[\"c1\",\"avg\",\"valFloat\"]]}"
Supported aggregation functions are:
count first last sum prd min max all any var
avg wsum wavg var dev cov cor svar sdev scov
sortCols
Specifies the columns to sort on before returning the response.
`sensorID`readTS
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"groupBy\":[\"sensorID\"],\"agg\":[[\"c1\",\"avg\",\"valFloat\"]],\"sortCols\":[\"sensorID\"]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"$START\",\"endTS\":\"$END\",\"groupBy\":[\"sensorID\"],\"agg\":[[\"c1\",\"avg\",\"valFloat\"]],\"sortCols\":[\"sensorID\"]}"
Response
See the possible response codes for success or failure information.
The response payload is a list that includes columns to sort on, return columns, by-clause to apply in the aggregator, secondary aggregation, and the table data.
Examples
Assume 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 measurement |
qual | short | Quality of the reading |
updateTS | timestamp | Update timestamp of reading |
For these examples, assume that we have a handle opened to the gateway assigned to h
, a callback function called cb
set to {res::(x;y)}
.
For these examples, assume that an Insights request token has been set in the INSIGHTS_TOKEN
env var.
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:neg[h](`.kxi.getData;args;`cb;()!()) / Make the API call and store the result in "res"
q)5#res[1] / 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
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\"}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\"}"
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:neg[h](`.kxi.getData;args;`cb;()!()) / Make the API call and store the result in "res"
q)5#res[1] / 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
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\"}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\"}"
Here we specify select only a subset of the columns
q)args
table | `trace
startTS| -0Wp
endTS | 0Wp
agg | `sensorID`readTS`valFloat
q)res:neg[h](`.kxi.getData;args;`cb;()!()) / 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
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\",\"agg\":[\"sensorID\",\"readTS\",\"valFloat\"]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\",\"agg\":[\"sensorID\",\"readTS\",\"valFloat\"]}"
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)args
table | `trace
startTS| -0Wp
endTS | 0Wp
filter | (("=";`sensorID;1 3h);("within";`qual;1 3h))
q)res:neg[h] (`.kxi.getData;args;`cb;()!()) / Make the API call and store the result in "res"
q)5#res[1] / 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
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\",\"agg\":[\"sensorID\",\"readTS\",\"valFloat\"],\"filter\":[[\"=\",\"sensorID\",[1,3]],[\"within\",\"qual\",[0,2]]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\",\"agg\":[\"sensorID\",\"readTS\",\"valFloat\"],\"filter\":[[\"=\",\"sensorID\",[1,3]],[\"within\",\"qual\",[0,2]]}"
Again we make use of the sortCols
argument to return the result sorted by qual and valFloat.
q)args
table | `trace
startTS | 2021.05.13D14:05:00.000000000
endTS | 0Wp
sortCols | `qual`valFloat
q)res:neg[h](`.kxi.getData;args;`cb;()!()) / Make the API call and store the result in "res"
q)5#res[1] / 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
curl -X POST "http://${GATEWAY_HOSTNAME}/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\",\"sortCols\":[\"qual\",\"valFloat\"]}"
curl -X POST "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getData" \
--header "Content-Type: application/json" \
--header "Accepted: application/json" \
--header "Authorization: Bearer $INSIGHTS_TOKEN" \
--data "{\"table\":\"trace\",\"startTS\":\"2021.02.09D01.00.00.000000000\",\"sortCols\":[\"qual\",\"valFloat\"]}"