Querying Best Practices

This page provides best practices for querying data from kdb Insights.

Querying data with negative infinity partitions

Columns that contain null timestamp values are not suitable for use as partition columns. Data that is saved and partitioned that contain partitioned timestamps such as -0Wp and therefore a date partition of-0Wd (negative infinity) is not returned by in the payload of any API request.

// Checking directly on the HDB process
q) min date
-0Wd

// Running an API call to retrieve min date from the database. This filters out any partitioned data where the partitioned column is `-0Wd`.

q)last GATEWAY(`.kxi.sql;enlist[`query]!enlist"select min(date) as minDate from trade";`;()!())
minDate
----------
2025.04.25

Querying reference data in long running UDA

If a UDA that accesses reference data runs for a long time, in the order of an EOI interval or more, it may be necessary to set KXI_SM_EOI_RETAIN_REF. By default, the Storage Manager only stores the latest reference data in the most recent IDB partition, and deletes the old reference data at the end of any EOI operation. If while this operation is happening a DAP is running a UDA that uses reference data then the query may hit a "No file found" issue as it tries to read in the old data that has been deleted.

To prevent this, the KXI_SM_EOI_RETAIN_REF environment variable can be set so that Storage Manager retains a number of older versions of the reference data. The value should be set to something higher than the number of EOI intervals the system's longest running reference data request will run.

For example, say a system has an EOI interval of 10 minutes, and we have UDA that accesses reference data which can run for ~30 minutes. In this case we should set the KXI_SM_EOI_RETAIN_REF to 3 or 4 to be safe and ensure the reference data is readable by the query.