Creating a User Defined Analytics (UDAs)

Introduction

User Defined Analytics (UDAs) can be used to define new APIs callable through the Service Gateway (SG). These analytics can be loaded into the Data Access Processes (DAPs) and the Aggregators using folders referred to as a package. Use the kdb Insights Package CLI documentation to initialize a new package.

Use these UDAs to augment the existing APIs of the kdb Insights system with application logic specific to your business needs. The following documentation provides instructions on organizing UDAs in files, registering UDAs to make them callable using the kdb Insights Service Gateway, and debugging potential issues.

Steps to create a User Defined Analytic (UDA)

Define the UDA Functions

A UDA consists of two functions, a query function and an aggregation function.

1. Create the Query Function

Purpose - Retrieves data from Data Access Processes (DAPs).
Arguments - You can define the arguments for the query function as:
- A list of values
- A dictionary of keys named args
Warning

You must name the dictionary of keys args. Any other name causes the DAP to treat the function as a one parameter function, which ignores the auto-casting of REST parameters.
Response wrapping - Wrap the response with the helper function .kxi.response.ok to indicate successful execution.

Example - Arguments as values

.example.queryFn:{[table;startTS;endTS;columns]
    columns:$[-11h = type columns;enlist columns;columns];
    data:.kxi.selectTable`table`startTS`endTS`agg!(table;startTS;endTS;columns!columns); // Retrieve data within specified time range
    .kxi.response.ok data
    };

Example - Arguments as a dictionary

.example.queryFn:{[args]
    columns:$[-11h = type columns:args`columns;enlist columns;columns];
    filter:enlist (<;`i;100); // Note for partitioned tables, will return first 100 per date
    .kxi.response.ok ?[args`table;filter;0b;columns!columns]
    };

2. Create the Aggregation Function

The aggregation function combines partials received from the DAPs to form the final result, which is then returned to you.

Default aggregation - By default, if no specific aggregation function is defined, the aggregation function uses the kdb+ raze operator to combine the partials.

Warning

When using default aggregation with memory mapped table types such as basic or splayed, ensure the query function includes table as a parameter. This ensures correct routing to a single DAP for requesting data and prevents duplicated results in the response. For more information, refer to the aggregation examples.

Custom aggregation - The aggregation function can be customized based on the specific needs of the UDA. This customization allows the function to perform additional or reduced processing, depending on the requirements.
Arguments - The aggregator argument is a list of partials from the DAPs. The datatype of this argument depends on the output returned from the query function within the UDA.

Example - Aggregation function

The following example demonstrates how to define an aggregation function that returns 100 random records from the list of partials returned by the DAPs. The variable res represents the list of partials.

.example.aggFn:{[res]
    .kxi.response.ok 100?raze res
    };

3. Register the UDA

Before calling a UDA from the Service Gateway, you must register it with the Service Gateway. This registration is done in the code loaded into the DAPs and Aggs using the .kxi.registerUDA function.

`.kxi.registerUDA` Parameters

The .kxi.registerUDA function accepts a dictionary with the following keys:

Key Name	Required	Type	Description
`name`	Yes	Symbol	The name of the UDA when called using the Service Gateway.
`query`	Yes	Symbol	The name of the function that runs on the DAPs and retrieves the `partials` for aggregation.
`aggregation`	No	Symbol	The name of the function that runs on the aggregator and combines the partial results from the DAPs.
`metadata`	No	List	Metadata about the properties of the UDA. It's recommended to use the metadata builders described in the Metadata builders section.

4. Define the Metadata

The .kxi namespace includes a set of meta-building APIs for defining metadata. The following APIs simplify the process of defining metadata entries for UDAs and their functions. For examples of how to use these APIs, refer to the Examples section.

Meta-Building APIs

.kxi.metaDescription - Creates a description entry for a UDA's metadata.
- Parameters:
  - descr: string - The description of the UDA.
.kxi.metaParam - Creates a parameter entry for a UDA's metadata. These parameters should match the inputs of the query function.
- Parameters:
  - param: dictionary - Dictionary containing any subset of the following keys:
  - name: symbol - The name of the parameter.
  - type: short[]|short - The possible types for the parameter.
    Info
    In the case of a REST request, this entry helps automatically cast the input to the correct type. For example, symbols are processed as strings, but specifying the type as `-11h` prompts kdb insights to cast it. If multiple types are specified, auto-casting uses the first type in the list.
  - isReq: Boolean - Indicates whether the parameter is required.
  - default: any - The default value of the parameter if it is not required.
  - description: string - Plain text description of the parameter.
.kxi.metaReturn - Creates a return entry for a UDA's metadata. This entry should match the return type of the aggregation function.
- Parameters:
  - return: dictionary - Dictionary containing any subset of the following keys:
  - type: short|short[] - The possible types for the return value.
  - description: string - Plain text description of the return value.
.kxi.metaMisc - Creates a miscellaneous metadata entry.
- Parameters:
  - misc: dictionary - Dictionary containing any subset of the supported miscellaneous fields.
  - safe: Boolean - Indicates whether the UDA can be safely retried in the event of a failure.

5. Create a complete UDA

This section describes how to combine the previous examples into a single file to run a custom UDA. It is recommended to have the function definitions and the registration in the same file to ensure consistent definitions across DAPs and Aggregators. See (UDA examples)[#uda-example.md] for more information.

Define the schema

Begin by defining the schema for the table processed by the UDA. In the following example, the table readings contains information about sensor usage and the Postal Code of the sensor.

Example schema:

q)readings
sensorID readTS                        val postal
-------------------------------------------------
0        2024.08.12D17:32:37.645609220 100 P0P1K0
1        2024.08.12D17:32:37.645609220 200 K7K2C7
q)meta readings
c       | t f a
--------| -----
sensorID| j
readTS  | p
val     | f
postal  | s

Define the UDA Functions

Define the functions for querying and aggregating data.

Query Function

The usageQuery function computes the count and sum of the val column, grouped by the first three digits of the postal code:

//
// @desc Computes the count of records as well as the sum of `val` column by first 3 digits of postal code.
//
// @param startTS   {timestamp}     Starting timestamp.
// @param endTS     {timestamp}     Ending timestamp.
// @param filter    {list}          Filter to apply to data.
//
// @return         {table}         Average count by postal code.
//
.example.usageQuery:{[startTS;endTS;filter]
    args:`table`startTS`endTS`columns`filter`groupBy!(`readings;startTS;endTS;`sumVal`cntVal!((sum;`val);(count;`val));filter;enlist[`postal]!enlist`postal); / Build arguments
    res:.kxi.selectTable args; / Make selectTable call
    res:0!select sumVal:sum sumVal,cntVal:sum cntVal by postal:`$3#/:string postal from res; / Anonymize data, and perform further processing
    .kxi.response.ok res / Return with OK
    };

Aggregation Function

The usageAgg function consolidates partial results from DAPs and calculates the total usage statistics, including count and average per postal code:

//
// @desc Aggregates partial results from DAPs to produce total usage statistics, count of records, and average per postal code.
//
// @param tbls  {table[]}   List of tables with postal, sumVal, and cntVal columns.
//
// @return      {table}     Table with sum, count, and average by first 3 digits of postal code.
//
.example.usageAgg:{[tbls]
    .kxi.response.ok 0!update avgVal:sumVal%cntVal from select sumVal:sum sumVal, cntVal:sum cntVal by postal from raze tbls
    };

Add Metadata

Define metadata for the UDA to describe its purpose, parameters, and return values:

metadata:.kxi.metaDescription["Returns the average by postal code across the first 100 records of each DAP"], / Description of the UDA
    .kxi.metaParam[`name`type`isReq`description!(`startTS;-12h;1b;"Start timestamp of query")], / Describe `startTS` parameter
    .kxi.metaParam[`name`type`isReq`description!(`endTS;-12h;1b;"End timestamp of query")], / Describe `endTS` parameter
    .kxi.metaParam[`name`type`isReq`default`description!(`filter;0h;0b;();"Filter to apply to readings table")], / Describe `filter` parameter
    .kxi.metaReturn`type`description!(98h;"The summary of total and average usage by first 3 digits of postal code"), / Describe the return of the UDA
    .kxi.metaMisc[enlist[`safe]!enlist 1b]; / Indicates that this UDA is safe to retry in the event of an error

Register the UDA

Register the UDA with the appropriate names for query and aggregation functions, and provide the metadata:

Info

You must call this in both the DAP and the Agg.

.kxi.registerUDA `name`query`aggregation`metadata!(`.example.usageByPostalCode;`.example.usageQuery;`.example.usageAgg;metadata) / Register UDA

Example - Complete UDA

Recommended File Structure

To ensure the UDA functions correctly, organize your files as follows:

da.q
agg.q
uda.q

Info

Both da.q and agg.q must include \l uda.q

Complete UDA

Here is the complete UDA to return statistics about readings usage per postal code area:

//
// @desc Computes the count of records as well as the sum of `val` column by first 3 digits of postal code. .
//
// @param startTS   {timestamp}     Starting timestamp.
// @param endTS     {timestamp}     Starting timestamp.
// @paream filter   {list}          Filter to apply to data.
//
// @return      {table}     Average count by sym
//
.example.usageQuery:{[startTS;endTS;filter]
    args:`table`startTS`endTS`columns`filter`groupBy!(`readings;startTS;endTS;(`sumVal`sum`val;`cntVal`cnt`val);filter;`postal); / Build arguments
    res:.kxi.selectTable args; / Make selectTable call
    res:0!select sumVal:sum sumVal,cntVal:sum cntVal by postal:`$3#/:string postal from res; / Anonymize data, and do further crunchdown
   .kxi.response.ok res / Return with OK
    };

//
// @desc Agg function that consolidates partial results from daps and outputs the statistics total usage, the count of records, and the average per postal code.
//
// @param tbls  {table[]}   List of tables with postal, sumVal, cntVal columns.
//
// @return      {table}     Table with sum, count, average by first 3 digits of postal code.
//
.example.usageAgg:{[tbls]
    .kxi.response.ok 0!update avgVal:sumVal%cntVal from select sumVal:sum sumVal, cntVal:sum cntVal by postal from raze tbls
    };

metadata:.kxi.metaDescription["Returns the average by sym across first 100 records of each DAP"], / Description of the UDA
    .kxi.metaParam[`name`type`isReq`description!(`startTS;-12h;1b;"Start timestamp of query")], / Describe `startTS` parameter
    .kxi.metaParam[`name`type`isReq`description!(`endTS;-12h;1b;"End timestamp of query")], / Describe `startTS` parameter
    .kxi.metaParam[`name`type`isReq`default`description!(`filter;0h;0b;();"Filter to apply to readings table")], / Describe `filter` parameter
    .kxi.metaReturn`type`description!(98h;"The summary of total and average usage by first 3 digits of postal code"), / Describe the return of the UDA
    .kxi.metaMisc[enlist[`safe]!enlist 1b]; / States that this UDA is safe to be retried in the event of an error

.kxi.registerUDA `name`query`aggregation`metadata!(`.example.usageByPostalCode;`.example.usageQuery;`.example.usageAgg;metadata) / Register

Creating a User Defined Analytics (UDAs)

Introduction

Steps to create a User Defined Analytic (UDA)

Define the UDA Functions

1. Create the Query Function

2. Create the Aggregation Function

3. Register the UDA

.kxi.registerUDA Parameters

4. Define the Metadata

Meta-Building APIs

5. Create a complete UDA

Define the schema

Define the UDA Functions

Add Metadata

Register the UDA

Example - Complete UDA

Recommended File Structure

Complete UDA

`.kxi.registerUDA` Parameters