Skip to content

UDA Creation Quickstart

This page provides a quickstart guide to creating a UDA that counts the number of records in a table within a specified timestamp range for each unique combination of values across a given set of columns. To help validate the UDA, you can replicate the query using the getData API.

The UDA creation process consists of the following steps:

  1. Prerequisites: Ensure you meet all the necessary prerequisites.

  2. Develop the UDA: Develop the UDA ensuring your code is modular, efficient, and well-documented. Validate it with different datasets to ensure it performs as expected in various scenarios, using the test results to refine and optimize the UDA for performance and accuracy.

  3. Add the UDA to a package: Arrange all necessary files, including the UDA code, dependencies, metadata, and registration in a structured manner in a package and specify required configuration settings, such as environment variables, to ensure the UDA functions correctly after deployment.

  4. Test on a staging environment: Deploy the UDA in a staging environment to test it integrates well with other components.

  5. Deploy to Production: Deploy the package to your production environment.

Refer to the UDA Configuration guide for more details.

By the end of this quickstart the UDA described here will be queryable on your database

The UDA counts the number of records in a table within a timestamp for each unique combination of values in a specified set of columns.

// Query function.
// Custom count by UDA.

// @desc Counts number of entries by specified columns.
// @param table     {symbol}            Table name.
// @param startTS   {timestamp}         Start time (inclusive).
// @param endTS     {timestamp}         End time (exclusive).
// @param byCols    {symbol|symbol[]}   Column(s) to count by.
// @return          {dict|table}        The `byCols` dictionary and table payload. 
.exampleuda.countByQuery:{[table;startTS;endTS;byCols]
    bc:bc!bc:(),byCols; / Convert byCols into dictionary for select table
    agg:enlist[`x]!enlist(count;`i); / Create aggregation to get count of records
    res:.kxi.selectTable`table`startTS`endTS`groupBy`agg!(table;startTS;endTS;bc;agg)); / Select data
    .kxi.response.ok(byCols;res) / Return both the table result and the by clause for easy aggregation
    }

// Aggregation function.
// @desc Takes partial results and sums the counts by the groupby dictionary returned from DAPs.
// @param res   {list[][]}  Partial result from all DAPs. This includes `byCols` and payload.
.exampleuda.countByAgg:{[res]
    bc:first first res; / Get the groupBy columns
    t:raze last each res; / Get table data from each DAP and convert to one table
    res:?[t;();bc;enlist[`cnt]!enl(sum;`x)] / Functional form of 'select sum x by {byCols} from t'
    .kxi.response.ok res / Return result with success codes
    }


// Metadata.
metadata:.kxi.metaDescription["Custom UDA - does a count by."],
    .kxi.metaParam[`name`type`isReq`description!(`table;-11h;1b;"Table name.")],
    .kxi.metaParam[`name`type`isReq`description!(`byCols;11 -11h;1b;"Column(s) to count by.")],
    .kxi.metaParam[`name`type`isReq`description!(`startTS;-12h;1b;"Start time (inclusive).")],
    .kxi.metaParam[`name`type`isReq`description!(`endTS;-12h;1b;"End time (exclusive).")],
    .kxi.metaReturn`type`description!(98h;"Count by specified columns.");

// Registration.
.kxi.registerUDA `name`query`aggregation`metadata!(`.exampleuda.countBy;`.exampleuda.countByQuery;`.exampleuda.countByAgg;metadata);

1. Prerequisites

Before trying to create this UDA ensure the following prerequisites are met:

  • You have access to a kdb Insights Enterprise instance.

  • The kxi CLI is installed and configured on your system.

  • You are familiar with how packaging works in kdb Insights Enterprise. A solid understanding of packaging is essential for understanding how UDAs are packaged and deployed. Refer to the Packaging Overview documentation for more information.

  • You have the insights-demo package deployed. Refer to the database creation and subway pipeline guides for details.

2. Develop

In this section, you define the UDA code, test it using the subway table with the direction_id and route_long_name columns, and run it against the current date.

  1. Ensure the insights-demo package is deployed.

  2. Open a code editor that can access a specific DAP process while testing your UDA:

    You can use any of the following:

    1. Open a Query Window

    2. Select the q tab in the Query and Load Data panel

    3. Select insights-demo as the Database

    4. Select idb as the Instance.

    1. Ensure a connection to your kdb Insights Enterprise is connected. Refer to kdb Insights Enterprise Connections

    2. Create a new q file

    3. Select the appropriate connection from the Connection dropdown

    4. Set the Target to one of the insights-demo-idb DAP processes

  3. Define the query function:

    1. Start by reading the raw data from a single DAP and aggregating it into the count of rows per unique combination of selected columns:

      byCols: `direction_id`route_long_name; / set the columns to groupby
bc:bc!bc:(),byCols; / Convert byCols into dictionary for select table
agg:enlist[`x]!enlist(count;`i); / Create aggregation to get count of records
.kxi.selectTable`table`startTS`endTS`groupBy`agg!(`subway;.z.D;.z.P;bc;agg) / Select data from the subway table with today's date
      • The code utilizes the helper function .kxi.selectTable to collect the required data from the specified table and time range. We recommend this method when querying the data rather than qSQL as this handles late data and supports Row-level Entitlements.

    2. Execute the query

    3. Check the code returns the correct number of rows per unique combination of values in the selected columns and table.

    4. Test the code with different columns.

  4. Convert the query code into a function and call the function to test that it returns the same data as in the previous step:

    .exampleuda.countByQuery:{[table;startTS;endTS;byCols]
    bc:bc!bc:(),byCols; / Convert byCols into dictionary for select table
    agg:enlist[`x]!enlist(count;`i); / Create aggregation to get count of records
    res:.kxi.selectTable`table`startTS`endTS`groupBy`agg!(table;startTS;endTS;bc;agg); / Select data
    .kxi.response.ok(byCols;res) / Return both the table result and the by clause for easy aggregation
    };

//execute the query function
.exampleuda.countByQuery[`subway;.z.D;.z.P;`direction_id`route_long_name]

    Points of interest

    • The previously hard coded values have been added as parameters in the function call.

    • The response line utilizes .kxi.response.ok has been addedwhich includes the data and any parameters that need to be passed to the aggregation function.

    1. Execute the query.

      This returns the data in the standard API response format including a header and results. This is helpful if there are errors as it provides the response codes.

    2. Once you have confirmed there are not errors you can update the last line temporarily to only display the data, as follows:

      .exampleuda.countByQuery[`subway;.z.D;.z.P;`direction_id`route_long_name][1][1]

When building your own query functions refer to the testing page for further details on testing and debugging your code.

  1. Define the aggregation:

    You can use the qSQL API against the Scratchpad to test your aggregation function as this allows you to execute the query function against all the DAPs and override the aggregation with your own code.

    1. Switch from targeting a specific DAP to the Scratchpad:

      Use the Scratchpad panel

      Change the Execution Target to Scratchpad

    2. Type the following code into the Scratchpad:

      ///Define a string and set it to the query function and its execution
query: ".exampleuda.countByQuery:{[table;startTS;endTS;byCols]
    bc:bc!bc:(),byCols; / Convert byCols into dictionary for select table
    agg:enlist[`x]!enlist(count;`i); / Create aggregation to get count of records
    res:.kxi.selectTable`table`startTS`endTS`groupBy`agg!(table;startTS;endTS;bc;agg); / Select data
    /commented out as qSQL does not support response headers
    /.kxi.response.ok(byCols;res) / Return both the table result and the by clause for easy aggregation
    (byCols;res)
    };
    .exampleuda.countByQuery[`subway;.z.D-1;.z.P;`direction_id`route_long_name]";

///Define the aggregation function
countByAgg:{[partials]
    bc:first first partials; / Get the groupBy columns
    t:raze last each partials; / Get table data from each DAP and convert to one table
    ?[t;();bc!bc;enlist[`cnt]!enlist(sum;`x)] / Functional form of parse'select sum x by {byCols} from t'
    };

// run a distributed qSQL API call on a specific database with the DAP and AGG function
.com_kx_edi.qsql[`query`agg!(query;countByAgg)]

        === "Query Window"

            1. Open a Query Window

            1. Either:

                - Go to the Scratchpad Panel and type in the following making sure you update the time range if necessary:

                    ```q
                    args:`table`startTS`endTS`groupBy`agg!(`subway;.z.P-10000D;.z.P;enlist `direction_id,`stop_name;enlist `cnt`count`direction_id)
                    .com_kx_edi.uda[args;"kxi/getData"]
                    ```

                - Select the **Basic** tab

                    1. Set the time range to the current date

                    1. Set the table to `subway`

                    1. Set `byCols` to `["direction_id","stop_name"]`

        === "VS Code"

            Create a new [q file](https://code.kx.com/vscode/guides/source-files.html) and run this q code:

            ```q
            args:`table`startTS`endTS`groupBy`agg!(`subway;.z.P-10000D;.z.P;enlist `direction_id,`stop_name;enlist `cnt`count`direction_id)
            .com_kx_edi.uda[args;"kxi/getData"]
            ```

  1. Update the package to include dedicated RCs and Aggregators:

    kxi package add --to insights-demo rc
kxi package add --to insights-demo agg

    Refer to the Dedicated resources guide for more details on why you would include dedicated resources.

  2. Ensure users are able to call UDAs you need to ensure they have the appropriate permissions and entitlements, if they are enforced. Refer to Authentication and Data Entitlements documentation for instructions on granting users permissions and entitlements to query the database using UDAs.

To test the UDA on a staging environment you can use the kxi pm load command to update the running package with the changes.

This allows you to iterate quickly without requiring a redeployment of the package.

  1. We recommend you checkpoint the updated package to ensure it has a higher version than the one currently running.

    The example below increases the patch number:

    kxi package checkpoint insights-demo --bump patch

  2. Push the updated package to kdb Insights Enterprise

    kxi pm push insights-demo

  3. Deploy the UDA. The deployment method depends on whether the insights-demo database is deployed:

    • If the database is not deployed you can deploy it using kxi pm deploy:

      kxi pm deploy insights-demo

    • If the database is already deployed use kxi pm load to update the running database with the changes.

      kxi pm load insights-demo -d insights-demo

      Important

      • To use kxi pm load you must enable dynamic package loading following the Configuration requirements guide.

      • If the package version has not been increased kxi pm load will not load the updated code into the processes.

  1. Confirm the UDA is available using one of the methods below:

    It may take several minutes for the updates to become available.

    1. Open the Query Window in the web interface

    2. Either:

      1. Go to the Scratchpad Panel and type in:

        .com_kx_edi.getMeta[][1][`api]

        You should see exampleuda.countBy in the list returned

      2. Select the UDA tab in the Query & Load Data panel

        You should see exampleuda.countBy in the UDAs dropown

    Using an $INSIGHTS_TOKEN you can call the following command:

    curl -X POST --header "Content-Type: application/json"\
    --header "Accepted: application/struct-text"\
    --header "Authorization: Bearer $INSIGHTS_TOKEN"\
    "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getMeta"

    You should see exampleuda.countBy in the data returned.

    1. Connect to the kdb Insights Enterprise deployment

    2. Either:

      • Create a new q file and run this q code:

        .com_kx_edi.getMeta[][1][`api]

        You should see exampleuda.countBy in the UDAs dropdown

      • Click on the meta node underneath the connection node:

        Look for exampleuda.countBy in the text.

    If the UDA is not available after several minutes

    • Verify that the package has been successfully. Use the kxi pm list command or check the database details in the web interface.

    • If the deployment fails, review error messages and logs to identify the cause. Common issues include missing dependencies, incorrect configurations, or permission errors.

    • If necessary, roll back the deployment to the last known stable state to avoid disruptions in production.

    • Identify and troubleshoot issues, correct any errors, and attempt the deployment again. Refer to the Troubleshooting guide for more details.

  2. Once the UDA available, you can query it as follows to test that it produces the expected results:

    1. Open a Query Window

    2. Either:

      • Go to the Scratchpad Panel and type in:

        args:`table`startTS`endTS`byCols!(`subway;.z.P-10000D;.z.P;`direction_id)
.com_kx_edi.uda[args;"uda/countBy"]

      • Select the UDA tab in the Query & Load Data panel

        1. Choose the UDA from the dropdown

        2. Select a time range within the current date

        3. In the table field, type in subway

        4. In the byCols field, type in direction_id,stop_name

        5. Click Run

    Refer to Query using UDA for more details on using the Query & Load Data panel.

    1. Choose the appropriate startTS, endTS and columns values based on what you wish to return

      Using an $INSIGHTS_TOKEN you can call the following command:

      startTS=$(date -u '+%Y.%m.%dD%H:00:00')
endTS=$(date -u '+%Y.%m.%dD%H:%M%:%S')
columns="direction_id,stop_name"
curl -X POST --header "Content-Type: application/json"\
    --header "Accepted: application/struct-text"\
    --header "Authorization: Bearer $INSIGHTS_TOKEN"\
    --data "{\"startTS\": \"$startTS\", \"endTS\": \"$endTS\", \"byCols\": \"$columns\"}"\
    "https://${INSIGHTS_HOSTNAME}/servicegateway/exampleuda/countBy"

      Refer to REST for more details from using the UDAs through RSET.

      Refer to the example UDAs documentation for more RESTful UDA query examples.

    Either:

    • Create a new q file and run this q code:

      args:`table`startTS`endTS`byCols!(`subway;.z.P-10000D;.z.P;`direction_id)
.com_kx_edi.uda[args;"uda/countBy"]

    • Use a Datasource file

      1. Choose the UDA tab

      2. Select a time range within the current date

      3. In the tables field, type in subway

      4. In the byCols field, type in direction_id,stop_name

      5. Click Run

    Refer to VS Code documentation for more details from using the UDAs with the kdb VS Code Extension.

Refer to the Authentication documentation for instructions on granting users permissions to query the database using UDAs.

If entitlements are enforced refer to the Entitlements documentation for instructions on giving users entitlements to query the insights-demo data.

    !!!note "The the query function defined in the previous step has been modified as follows:"

        - The query function and its execution have been assigned to a string variable to be passed to the `qSQL` call

        - The `.kxi.response.ok` line has been commented out as `qSQL` does not support response headers.

    !!!note "Aggregation details"

        - The group by column information is extracted from the first `partial`

        - The `raze` operator is used to combine the `partials` into one table of data, before the group by is applied again to aggregate the `partials` into one set of results.

        - The last line sums the counts from each partial together

        - There is a single parameter defined for the aggregation function which is a set of `partials` returned from the query function

1. Expand the time range, if necessary, to ensure more than one tier is targeted with the query function

1. Click **Run**.

1. Check the code returns the correct data.

!!! info
    In this quickstart we are testing both the query and aggregation functions before we deploy the package. When developing your aggregation function, if you prefer, you can deploy the query function and then execute it directly, rather than passing the function definition into the `qSQL` call.

1. Check this code works as expected for different combinations of columns in different tables before proceeding.


    ???info "To assist with validation of this quickstart there is a `getData` equivalent to this UDA"



        === "Query Window"

            1. Open a Query Window

            1. Either:

                - Go to the Scratchpad Panel and type in the following making sure you update the time range if necessary:

                    ```q
                    args:`table`startTS`endTS`groupBy`agg!(`subway;.z.P-10000D;.z.P;enlist `direction_id,`stop_name;enlist `cnt`count`direction_id)
                    .com_kx_edi.uda[args;"kxi/getData"]
                    ```

                - Select the **Basic** tab

                    1. Set the time range to the current date

                    1. Set the table to `subway`

                    1. Set `byCols` to `["direction_id","stop_name"]`

        === "VS Code"

            Create a new [q file](https://code.kx.com/vscode/guides/source-files.html) and run this q code:

            ```q
            args:`table`startTS`endTS`groupBy`agg!(`subway;.z.P-10000D;.z.P;enlist `direction_id,`stop_name;enlist `cnt`count`direction_id)
            .com_kx_edi.uda[args;"kxi/getData"]
            ```

  1. Convert the aggregation code into a function:

    ///Define the aggregation
.exampleuda.countByAgg:{[partials]
bc:first first partials; / Get the groupBy columns
t:raze last each partials; / Get table data from each DAP and convert to one table
?[t;();bc!bc;enlist[`cnt]!enlist(sum;`x)]; / Functional form of parse'select sum x by {byCols} from t'
.kxi.response.ok res / Return result with success codes
};

    Points of interest

    • Added a namespace to the function name

    • Added the response line which utilizes .kxi.response.ok returning a table of data.

If you wish to add log entries to your UDA refer to Adding logging to UDAs for more information.

3. Add to a package

Now that you have defined the functions, you need to add them to the package along with all the necessary information to register the UDA and ensure it can be queried.

Note

This quickstart adds the UDA directly to the insights-demo package. If you wish your UDA to be stored in a code-only package, refer to Loading code-only packages into a database for more details.

  1. Pull the insights-demo from kdb Insights Enterprise to ensure it is accessible to your kdb Insights CLI.

    Pull insights-demo using the kxi pm pull command:

    kxi pm pull insights-demo

    Export insights-demo locally:

    1. Export the package as a .kxi file.

    2. Use the kxi package unpack command to extract the .kxi file into a package folder structure.

      kxi package unpack insights-demo-1.0.0.kxi

    The code above assumes the package version is 1.0.0

    The package folder structure looks like this:

    └──insights-demo
    ├── databases
    │   └── insights-demo
    │       ├── shards
    │       │   └── insights-demo-shard.yaml
    │       └── tables
    ├── init.q
    ├── manifest.yaml

  2. Create a new folder src in the package folder.

Add the query function

  1. Add a da.q file in the src folder to hold the query function definition.

    The package folder structure now looks like this:

    └──insights-demo
    ├── databases
    │   └── insights-demo
    │       ├── shards
    │       │   └── insights-demo-shard.yaml
    │       └── tables
    ├── init.q
    ├── manifest.yaml
    └── src
        └── da.q

  2. Add the following code to the da.q file:

    // @desc Counts number of entries by specified columns.
// @param table     {symbol}            Table name.
// @param startTS   {timestamp}         Start time (inclusive).
// @param endTS     {timestamp}         End time (exclusive).
// @param byCols    {symbol|symbol[]}   Column(s) to count by.
// @return          {dict|table}        The `byCols` dictionary and table payload. 
.exampleuda.countByQuery:{[table;startTS;endTS;byCols]
    bc:bc!bc:(),byCols; / Convert byCols into dictionary for select table
    agg:enlist[`x]!enlist(count;`i); / Create aggregation to get count of records
    res:.kxi.selectTable`table`startTS`endTS`groupBy`agg!(table;startTS;endTS;bc;agg); / Select data
    .kxi.response.ok(byCols;res) / Return both the table result and the by clause for easy aggregation
    }

Metadata

Define metadata for the UDA to describe its purpose, parameters, and return values. This is used by getMeta once the UDA is registered in the next step.

Add the metadata to the da.q file:

// Define metadata.
metadata:.kxi.metaDescription["Custom UDA - does a count by."],
    .kxi.metaParam[`name`type`isReq`description!(`table;-11h;1b;"Table name.")],
    .kxi.metaParam[`name`type`isReq`description!(`byCols;11 -11h;1b;"Column(s) to count by.")],
    .kxi.metaParam[`name`type`isReq`description!(`startTS;-12h;1b;"Start time (inclusive).")],
    .kxi.metaParam[`name`type`isReq`description`default!(`endTS;-12h;0b;"End time (exclusive).";.z.P)],
    .kxi.metaReturn`type`description!(98h;"Count by specified columns.");

  • Each parameter is defined using .kxi.metaParam. Adding each parameter to the metadata is preferable to using a dictionary as it ensures those querying the UDA can identify the parameters required.

  • endTS has been set as an optional parameter and defaulted to the current time. This is done by setting param.isReq to 0b and adding the default argument which is set to the current timestamp.

  • The data returned from this UDA is a table therefore the return type is set to 98h

  • In this example byCols can be a symbol or list of symbols, showing that type can accept multiple values.

  • The Resource Coordinator routes only to DAPs and Aggregators that have the UDA registered.

    Note

    When dedicator aggregators are used, you no longer need to add scope as a mandatory field and set the assembly key to the package name.

  • To ensure the routing to the appropriate partitions table and startTS distinguished parameters have been set as mandatory fields in the metadata.

Refer to Metadata builders for more details.

Registration function

Register the UDA with the appropriate names for query and aggregation functions, and provide the metadata to ensure the UDA is included in any calls to getMeta and can be queried through the Service Gateway.

Add the registration to the da.q file:

.kxi.registerUDA `name`query`aggregation`metadata!(`.exampleuda.countBy;`.exampleuda.countByQuery;`.exampleuda.countByAgg;metadata);

  • This registration is done using the .kxi.registerUDA function.

  • The query and aggregation functions along with the UDA itself have been defined as part of a namespace as this is best practice.

  • If your UDA does not include an aggregation function and you chose to use the raze operator, the aggregation parameter can be omitted as follows:

    .kxi.registerUDA `name`query`metadata!(`.exampleuda.countBy;`.exampleuda.countByQuery;metadata);

Add the aggregation function

  1. Add an agg.q file in the src folder to hold the aggregation function definition.

    The package folder structure now looks like this:

    └──insights-demo
    ├── databases
    │   └── insights-demo
    │       ├── shards
    │       │   └── insights-demo-shard.yaml
    │       └── tables
    ├── init.q
    ├── manifest.yaml
    └── src
        ├── da.q
        └── agg.q

  2. Add the following code to the agg.q file:

    // @desc Counts number of entries by specified columns.
// @param table     {symbol}            Table name.
// @param startTS   {timestamp}         Start time (inclusive).
// @param endTS     {timestamp}         End time (exclusive).
// @param byCols    {symbol|symbol[]}   Column(s) to count by.
// @return          {dict|table}        The `byCols` dictionary and table payload. 
.exampleuda.countByQuery:{[table;startTS;endTS;byCols]
    bc:bc!bc:(),byCols; / Convert byCols into dictionary for select table
    agg:enlist[`x]!enlist(count;`i); / Create aggregation to get count of records
    res:.kxi.selectTable`table`startTS`endTS`groupBy`agg!(table;startTS;endTS;bc;agg); / Select data
    .kxi.response.ok(byCols;res) / Return both the table result and the by clause for easy aggregation
    }

// Define metadata.
metadata:.kxi.metaDescription["Custom UDA - does a count by."],
    .kxi.metaParam[`name`type`isReq`description!(`table;-11h;1b;"Table name.")],
    .kxi.metaParam[`name`type`isReq`description!(`byCols;11 -11h;1b;"Column(s) to count by.")],
    .kxi.metaParam[`name`type`isReq`description!(`startTS;-12h;1b;"Start time (inclusive).")],
    .kxi.metaParam[`name`type`isReq`description`default!(`endTS;-12h;0b;"End time (exclusive).";.z.P)],
    .kxi.metaReturn`type`description!(98h;"Count by specified columns.");

.kxi.registerUDA `name`query`aggregation`metadata!(`.exampleuda.countBy;`.exampleuda.countByQuery;`.exampleuda.countByAgg;metadata);

Points of interest

  • The metadata declaration and registerAPI function must be included in both files as they ensure the process registers its function with the corresponding Resource Coordinator for use by the system.

  • Having the query and aggregation functions in different files ensures each process only loads the function is needs to register.

Refer to UDA registration for more details.

Update the package

  1. Update the package to load the UDA, by including entrypoints:

    To ensure that the UDA is loaded by the Data Access and Aggregator processes specify entrypoints labeled data-access and aggregator in the package containing the UDA using the kxi package add command.

    kxi package add --to insights-demo entrypoint --name aggregator --path src/uda.q
kxi package add --to insights-demo entrypoint --name data-access --path src/uda.q

    Once the commands are executed the following entrypoints section is included in the package manifest file:

    This updates the entrypoints section in the manifest.yaml file of the package as follows:

    entrypoints:
    default: init.q
    data-access: src/da.q
    aggregator: src/agg.q

Note

The entrypoints only reference the files that include code a specific process needs to load.

  1. Update the package to include dedicated RCs and Aggregators:

    kxi package add --to insights-demo rc
kxi package add --to insights-demo agg

    Refer to the Dedicated resources guide for more details on why you would include dedicated resources.

  2. Ensure users are able to call UDAs you need to ensure they have the appropriate permissions and entitlements, if they are enforced. Refer to Authentication and Data Entitlements documentation for instructions on granting users permissions and entitlements to query the database using UDAs.

4. Test on a staging environment

To test the UDA on a staging environment you can use the kxi pm load command to update the running package with the changes.

This allows you to iterate quickly without requiring a redeployment of the package.

  1. We recommend you checkpoint the updated package to ensure it has a higher version than the one currently running.

    The example below increases the patch number:

    kxi package checkpoint insights-demo --bump patch

  2. Push the updated package to kdb Insights Enterprise

    kxi pm push insights-demo

  3. Deploy the UDA. The deployment method depends on whether the insights-demo database is deployed:

    • If the database is not deployed you can deploy it using kxi pm deploy:

      kxi pm deploy insights-demo

    • If the database is already deployed use kxi pm load to update the running database with the changes.

      kxi pm load insights-demo -d insights-demo

      Important

      • To use kxi pm load you must enable dynamic package loading following the Configuration requirements guide.

      • If the package version has not been increased kxi pm load will not load the updated code into the processes.

  4. Confirm the UDA is available using one of the methods below:

    It may take several minutes for the updates to become available.

    1. Open the Query Window in the web interface

    2. Either:

      1. Go to the Scratchpad Panel and type in:

        .com_kx_edi.getMeta[][1][`api]

        You should see exampleuda.countBy in the list returned

      2. Select the UDA tab in the Query & Load Data panel

        You should see exampleuda.countBy in the UDAs dropown

    Using an $INSIGHTS_TOKEN you can call the following command:

    curl -X POST --header "Content-Type: application/json"\
    --header "Accepted: application/struct-text"\
    --header "Authorization: Bearer $INSIGHTS_TOKEN"\
    "https://${INSIGHTS_HOSTNAME}/servicegateway/kxi/getMeta"

    You should see exampleuda.countBy in the data returned.

    1. Connect to the kdb Insights Enterprise deployment

    2. Either:

      • Create a new q file and run this q code:

        .com_kx_edi.getMeta[][1][`api]

        You should see exampleuda.countBy in the UDAs dropdown

      • Click on the meta node underneath the connection node:

        Look for exampleuda.countBy in the text.

    If the UDA is not available after several minutes

    • Verify that the package has been successfully. Use the kxi pm list command or check the database details in the web interface.

    • If the deployment fails, review error messages and logs to identify the cause. Common issues include missing dependencies, incorrect configurations, or permission errors.

    • If necessary, roll back the deployment to the last known stable state to avoid disruptions in production.

    • Identify and troubleshoot issues, correct any errors, and attempt the deployment again. Refer to the Troubleshooting guide for more details.

  5. Once the UDA available, you can query it as follows to test that it produces the expected results:

    1. Open a Query Window

    2. Either:

      • Go to the Scratchpad Panel and type in:

        args:`table`startTS`endTS`byCols!(`subway;.z.P-10000D;.z.P;`direction_id)
.com_kx_edi.uda[args;"uda/countBy"]

      • Select the UDA tab in the Query & Load Data panel

        1. Choose the UDA from the dropdown

        2. Select a time range within the current date

        3. In the table field, type in subway

        4. In the byCols field, type in direction_id,stop_name

        5. Click Run

    Refer to Query using UDA for more details on using the Query & Load Data panel.

    1. Choose the appropriate startTS, endTS and columns values based on what you wish to return

      Using an $INSIGHTS_TOKEN you can call the following command:

      startTS=$(date -u '+%Y.%m.%dD%H:00:00')
endTS=$(date -u '+%Y.%m.%dD%H:%M%:%S')
columns="direction_id,stop_name"
curl -X POST --header "Content-Type: application/json"\
    --header "Accepted: application/struct-text"\
    --header "Authorization: Bearer $INSIGHTS_TOKEN"\
    --data "{\"startTS\": \"$startTS\", \"endTS\": \"$endTS\", \"byCols\": \"$columns\"}"\
    "https://${INSIGHTS_HOSTNAME}/servicegateway/exampleuda/countBy"

      Refer to REST for more details from using the UDAs through RSET.

      Refer to the example UDAs documentation for more RESTful UDA query examples.

    Either:

    • Create a new q file and run this q code:

      args:`table`startTS`endTS`byCols!(`subway;.z.P-10000D;.z.P;`direction_id)
.com_kx_edi.uda[args;"uda/countBy"]

    • Use a Datasource file

      1. Choose the UDA tab

      2. Select a time range within the current date

      3. In the tables field, type in subway

      4. In the byCols field, type in direction_id,stop_name

      5. Click Run

    Refer to VS Code documentation for more details from using the UDAs with the kdb VS Code Extension.

If there are any issues with calling the UDA refer to the Troubleshooting guide for more details.

5. Deploy to production

Once you are happy with the UDA you can deploy the updated package to production as above.

Refer to the Authentication documentation for instructions on granting users permissions to query the database using UDAs.

If entitlements are enforced refer to the Entitlements documentation for instructions on giving users entitlements to query the insights-demo data.

Next steps