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 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\"]}"