Skip to content

KDB.AI q API

This page contains the references for KDB.AI's q API. For example usage, see the Quickstart Guide.

Introduction

KDB.AI q API allows KDB.AI Server users to achieve the following:

  • Load and use existing kdb+ datasets in KDB.AI.
  • Apply KDB.AI functionalities to external kdb+ datasets.

KDB.AI is a high-performance vector database designed to manage real-time, unstructured data and power AI applications with advanced search, recommendation, and personalization features.

Kdb+ is a high-performance time-series database widely used in industries like finance for managing large datasets, particularly time-sensitive data. It is built to handle vast amounts of structured data efficiently. The q programming language is an integral part of kdb+ as its query language, designed for working with complex, high-speed analytics on large datasets. Q provides a concise syntax and is optimized for the operations typical in kdb+, such as querying and manipulating time-series data.

Pre-requisites

Before you start, make sure you have the following:

  • Access to KDB.AI Server.
  • Knowledge of writing q code using an environment of choice (command line, IDE, notebooks).

Version

getVersion

Retrieve version info from server and compatible client min/max version.

Example

gw(`getVersion;`)

Error handling:

Description Message Troubleshooting
Success. version info is returned. serverVersionclientMinVersion`clientMaxVersion!("1.4.0";"1.4.0";"latest") N/A
Error: KDBAI is not available. Cannot write to handle ... Check your connection and if your server is running.

Database

Create, delete, and retrieve databases.

In KDB.AI, a database is a collection of tables which stores related data.

Key principles for database management

To simplify database design/management and prevent naming conflicts, follow the principles below:

  • Unique database names: Each database must have a unique name and can contain multiple tables.
  • Unique table names within a database: Tables within a database must have unique names, but different databases can contain tables with the same name. This is similar to the concept of namespaces.
  • Cascade deletion: When deleting a database, all child entities (tables) will also be deleted.
  • Default database: You don't need to create a database to create tables. If you create a table without specifying a database, it will be placed in a default, undeletable database.

createDatabase

Create a database.

Input parameters:

Name Type Description Required
database symbol Name of the database to create. Yes

Database name rules

  • Max length is 128 characters
  • Must contain only alphanumeric characters and underscore
  • Must start with an alpha character

Example

`gw set hopen 8082;
gw(`createDatabase;enlist[`database]!enlist `myDatabaseName)

Error handling:

Description Message Troubleshooting
Success: Database is created and returned successresulterror!(1b;(name!`tableName);()) N/A
Fail: Database name is not unique successresult`error!0b;();"database {name} already exists" A database with the given name already exists. Create a database with another name.
Fail: Database name is not a valid name successresult`error!0b;();"database name is invalid" Provide a valid symbol for the database name.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

getDatabase

Retrieve database with a given name.

Input parameters:

Name Type Description Required
database symbol Name of the database to be retrieved Yes

Example

`gw set hopen 8082;
gw(`getDatabase;enlist[`database]!enlist `myDatabaseName)

Error handling:

Description Message Troubleshooting
Success: Database with given name is found successresulterror!1b;database`tables!{databasename};{list of table metadata (see at get table)};() N/A
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

listDatabases

Retrieve list of databases in ascending order.

Example

`gw set hopen 8082;
gw(`listDatabases;`)

Error handling:

Description Message Troubleshooting
Success: Returns list of database names and default db included successresult`error!1b;[get database names];() N/A
Error: Databases cannot be listed because KDBAI is not available Cannot write to handle... Check your connection and if your server is running.

deleteDatabase

Delete database with a given name and all associated tables.

Input parameters:

Name Type Description Required
database symbol Name of the database to be deleted. Yes

Example

`gw set hopen 8082;
gw(`deleteDatabase;enlist[`database]!enlist `myDatabaseName)

Error handling:

Description Message Troubleshooting
Success: Database with given name has been deleted successresult`error!1b;();() N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Default database cannot be deleted, (see database API section intro) successresult`error!0b;();"Default database cannot be deleted" Remove the tables from the database individually as required.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

Table

Create, delete, update, and retrieve tables.

createTable

Create a table.

Input parameters:

Name Type Description Required
database symbol Name of the database. Yes
table symbol Name of the table to create. Yes
externalDataReferences string list Should contain the keys:
- path (path to the existing kdb+ table mounted in our Docker container)
- provider (set to kx)

WARNING: the name of the table should match the name of the target table in the existing kdb+ database.
No
schema list of dictionary Schema details for the table. Yes - if externalDataReferences is not specified
indexes list of dictionary List of index definitions No
embeddingConfigurations dictionary Should be keyed by embedding column name No

Table name rules

  • Max length is 128 characters
  • Must contain only alphanumeric characters and underscore
  • Must start with an alpha character

createTable example

// Open connection
`gw set hopen 8082;

// Create table partitioned on date with flat index on the embeddings column
indexes:(enlist `flat;enlist `embeddings;enlist `flat;enlist `dims`metric!(25;`CS));
params:(!) . flip ((`database;`default);
                   (`table;`myTable);
                   (`schema;flip `name`type!(`date`tag`val`embeddings;`d`s`j`E));
                   (`partitionColumn;`date);
                   (`indexes;flip `name`column`type`params!indexes));
gw(`createTable;params);

// Create table from external table partitioned on date with flat index on the embeddings column
ref:enlist `path`provider`type!("/db";`kx);
indexes:enlist `name`column`type`params!(`flat_index;`embeddings;`flat;enlist[`dims]!enlist 384);
params:(!) . flip ((`database;`default);
                   (`table;`myTableExternal);
                   (`externalDataReferences;ref);
                   (`indexes;indexes));
gw(`createTable;params);

schema

Attributes:

Name Type Description Required
name symbol Column name Yes
type symbol Column type using kdb type symbols. Small for atoms and caps for vectors, Example: ‘i' for integer and 'I’ for integer list. Yes

Schema example

schema: (`name`type!(`id;`i);`name`type!(`isValid;`b);`name`type!(`embeddings;`E);`name`type!(`sparse_col;`)

indexes

Attributes:

Name Type Description Required
name symbol Index name Yes
type symbol Index type, for example: Flat, qFlat, HNSW, ivf, ivfpq, qHNSW Yes
column symbol kdb+ column name to apply index Yes
params dictionary Index parameters containing index-specific attributes for Flat, qFlat, HNSW, ivf, ivfpq, qHNSW Yes

Indexes example

flatIndex: `name`column`type`params!(`flat_index;`embeddings;`flat;enlist[`dims]!enlist 25)
hnswFast: `name`column`type`params!(`hnsw_fast;`embeddings;`hnsw;`dims`M`efConstruction!(25;8;8))
sparseIndex: `name`column`type`params!(`sparse_index;`sparse_column;`bm25;`k`b!(1.25;0.75))
indexes: (flat_index;hnsw_fast;sparse_index)
flat

Index-specific attributes (params) for type = flat

Attribute Description Type Required Default
dims Dimension of vector space int Yes N/A
metric Distance metric symbol No `L2
qFlat

Index-specific attributes (params) for type = qFlat

Attribute Description Type Required Default
dims Dimension of vector space int Yes N/A
metric Distance metric symbol No `L2
hnsw

Index-specific attributes (params) for type = hnsw

Attribute Description Type Required Default
dims Dimension of vector space int Yes N/A
M Graph valency int No 8
efConstruction Search depth at construction int No 8
metric Distance metric symbol No `L2
qHnsw

Index-specific attributes (params) for type = qHnsw

Attribute Description Type Required Default
dims Dimension of vector space int Yes N/A
M Graph valency int No 8
efConstruction Search depth at construction int No 8
metric Distance metric symbol No `L2
ivf

Index-specific attributes (params) for type = ivf

Attribute Description Type Required Default
nclusters Number of clusters long No 8
metric Distance metric symbol No `L2
ivfpq

Index-specific attributes (params) for type = ivfpq

Attribute Description Type Required Default
nclusters Number of clusters long No 8
nbits Number of bits to quantize long No 8
nsplits Number of vectors to split long No 8
metric Distance metric symbol No `L2

externalDataReferences

Attributes:

Name Type Description Required
path string Path to external table, for instance the existing kdb+ table mounted in our Docker container. Yes
provider symbol Provider of external table, for example kx. Yes

externalDataReferences example

Launch the KDB.AI Server container with the -v flag to mount an existing kdb+ DB in the container, for example:

docker run -it -e KDB_LICENSE_B64     \
               -v $PWD/taq/db:/db:ro  \   <= mount a local ./taq/db under /db in the container as read-only
                -p 8082:8082          \
                kdbai-db:1.4.0
Then:

gw(`createTable;`database`table`externalDataReferences!(`;`tq;enlist `path`provider!("/db";`kx)))
WARNING: The name of the table (tq) should match the name of the target table in the external kdb+ database.

Error handling:

Description Message Troubleshooting
Success: Table is created and returned successresult`error!1b;table_dictionary;" N/A
Fail: Table name is not unique successresult`error!0b;();"Table with given name already exists" Specify a different table name as it appears a table with this name already exists.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Use a valid symbol for the table name.
Fail: Any of the input parameters are of wrong type successresult`error!0b;();"invalid arguments types: " ... Provide the correct type of input parameters required.
Fail: Any of the input parameters are missing successresult`error!0b;();"missing arguments: " ... Provide required input parameters.
Fail: Any of the input parameters are invalid successresult`error!0b;();"invalid arguments: " ... Provide known or valid input parameters.
Fail: Schema individual attributes are not valid successresult`error!0b;();"invalid table attributes: " ... Provide valid attributes in the schema.
Fail: Schema individual types are not valid successresult`error!0b;();"invalid column types: " ... Provide valid column types in the schema.
Fail: Index individual parameters are not valid successresult`error!0b;();"invalid index parameters:" ... Double check the parameters of one of the specified indexes.
Fail: Database does not exist successresult`error!0b;();"Database does not exist" Double check the database name.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

getTable

Retrieve a table from a database with a given name.

Input parameters:

Name Type Description Required
database symbol Name of the database where the table is. Yes
table symbol Name of the table to be retrieved Yes

Example

// Open connection
`gw set hopen 8082;
// Get table details
gw(`getTable;`database`table!`default`myTable);

Error handling:

Description Message Troubleshooting
Success: Table with given name is found successresult`error!(1b;table meta dictionary;"") N/A
Fail; Database name is not valid successresult`error!0b;();"Database name is invalid" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

listTables

Retrieve a list of tables from a database with a given name.

Input parameters:

Name Type Description Required
database symbol Name of the database where the table is. If you don't provide a database name, the default database is used. Yes

Example

// Open connection
`gw set hopen 8082;
// Get tables details
gw(`listTables;enlist[`database]!enlist`default)

Error handling:

Description Message Troubleshooting
Success: Tables found successresulterror!(1b;database`tables!(dbname;tables name list);"") N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

deleteTable

Delete a table with a given name and all associated indexes.

Input parameters:

Name Type Description Required
database symbol Name of the database where the table is. Yes
table symbol Name of the table to be deleted. Yes

Example

// Open connection
`gw set hopen 8082;
// Get table details
gw(`deleteTable;`database`table!`default`myTable);

Error handling:

Description Message Troubleshooting
Success: Table with given name has been deleted successresulterror!(1b;database`tables!(dbname;tables name list);"") N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

Index

Retrieve and list indexes.

getIndex

Retrieve an index from a table.

Input parameters:

Name Type Description Required
database symbol Name of the database where the table is. If not provided, the default database is used. Yes
table symbol Name of the table where the index to be retrieved is. Yes
name symbol Name of the index to be retrieved Yes

Example

gw:hopen 8082
gw(`getIndex;`database`table`name!`default`test`trade_flat_index)

Error handling:

Description Message Troubleshooting
Success: Index with given name is found and returned successresult`error!1b;dictionary of details;() N/A
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Fail: Index name is not valid successresult`error!0b;();"Index name is invalid" Provide a valid symbol for the index name.
Fail: Index with given name is not found successresult`error!0b;();"Index name is not found" Double check the supplied value for the index.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

listIndexes

List all indexes for a table.

Input parameters:

Name Type Description Required
database symbol Name of the database where the table is. If not provided, the default database is used. No
table symbol Name of the table where the indexes to be retrieved are. No

Example

`gw set hopen 8082
gw(`listIndex;`database`table!`default`myTable)

Error handling:

Description Message Troubleshooting
Success: Indexes found and returned successresult`error!1b;dictionary of details;() N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

updateIndexes

Build one or more indexes.

Allows to build and attach indexes to external kdb+ tables.

Input parameters:

Name Type Description Required
database symbol The name of the database. If a database name is not provided,the default database is used. Yes
table symbol The name of the table. Yes
indexes symbol list List of index names to build. Yes
parts symbol list Partitions list to build index in case of partition database. If not given, then indexes will be built on all partitions. Yes

Example

ref:enlist `path`provider`type!("/tmp/kx/remote";`kx;`HDB);
indexes:enlist `name`column`type`params!(`flat_index;`embeddings;`flat;enlist[`dims]!enlist 384);

p:`database`table`externalDataReferences`indexes`partitionColumn!(`default;`SEC;ref;indexes;`date);
gw(`createTable;p);
gw(`updateIndexes;`database`table`indexes!(`default;`SEC;enlist `flat_index))

Error handling:

Description Message Troubleshooting
Success: Index(es) with given name(s) updated successfully None N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Fail: Operation called on a table managed by kdbai KDBAIException: feature not supported: build index is only allowed on reference database
Fail: Index name is not valid ValueError: Index name is invalid
Fail: Index with given name is not found KDBAIException: index not found: invalid
Fail: Update Operation is not valid ValueError: Update Operation is not valid
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

Data

Insert, query, and search data.

insertData

Add rows to a table.

Input parameters:

Name Type Description Required
database symbol The name of the database where the table is located. If a database name is not provided,the default database is used. Yes
table symbol The name of the table where the data will be inserted. Yes
payload table Data to insert. No - not required when using external database.

Example

// Open connection
`gw set hopen 8082;

// Insert Data
gw(`insertData;`database`table`payload!(`default;`myTable;data)

Error handling:

Description Message Troubleshooting
Success. Data inserted successfully. {successresultèrrorMsg!(1b;rows_inserted!10;"")}) N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Fail: Index name is not valid successresult`error!0b;();"Index name is invalid" Provide a valid symbol for the index name.
Fail: Index with given name is not found successresult`error!0b;();"Index name is not found" Double check the supplied value for the index.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

trainData

Train data.

Input parameters:

Name Type Description Required
database symbol The name of the database where the table is located. Yes
table symbol The name of the table. Yes
payload table Data to insert. Yes

Example

// Open connection
`gw set hopen 8082;

// Train
gw(`trainData;`database`table`payload!(`default;`myTable;data)

Error handling:

Description Message Troubleshooting
Success: Index(es) with given name(s) updated successfully 1b N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Fail: Index name is not valid successresult`error!0b;();"Index name is invalid" Provide a valid symbol for the index name.
Fail: Index with given name is not found successresult`error!0b;();"Index name is not found" Double check the supplied value for the index.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

query

Query data from a table.

Input parameters:

Name Type Description Required
database symbol The name of the database where the table you are querying data from is located. Yes
table symbol The name of the table you are querying data from. Yes
filter list of tuples List of filter conditions, parse tree style. No
sortColumns list of symbols The columns by which to sort the results. No
groupBy list of symbols The column values by which to group the results. No
aggs dictionary Aggregation rules. Dictionary structure:
- Key → new column name
- Value → old column name or parse tree style aggregation rule
No
limit integer Number of rows to return. No

Example

// Open connection
`gw set hopen 8082;

// Query
gw(`query;args:`database`table!`default`myTable)

Error handling:

Description Message Troubleshooting
Success: Successful query table N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.

Perform a similarity search.

Input parameters:

Name Type Description Required
database symbol Database name Yes
table symbol Table name. Yes
type symbol Specify the type of search (tss or otherwise). No
vectors dictionary Indexes to query with query vectors. Yes
n long Number of neighbors to return. Yes
indexParams dictionary Weights required for multi index search. No
options dictionary Use this dictionary:
- to rename the distance column with distanceColumn=newname
- to not return metadata columns with indexOnly=1b
- to return TSS matched patterns with returnMatches=1b
- to force a TSS search on a partitioned tables with failing partitions with force=1b
Yes
filter list of tuples List of filter conditions, parse tree style. No
sortColumns list of symbols The columns by which to sort the results. No
groupBy list of symbols The column values by which to group the results. No
aggs dictionary Aggregation rules. No

Example

// Open connection
`gw set hopen 8082;

// Query
gw(`search;args:`database`table`vectors`n!(`default;`myTable;enlist[`indexName]!enlist enlist 384?1e;25))

options

Attribute Description Type Required Default
distanceColumn Rename distance column to this. symbol No None
indexOnly Return only index information boolean No None

indexParams

indexParams is a dictionary where key is index name and value is a dictionary with the arguments below .

Attribute Description Type Required Default
weight Weight for each index. float Required for multi index input. None

Important! For multi index searches, you have to allocate a weight to each index. The sum of all weights must be equal to 1.

Error handling:

Description Message Troubleshooting
Success: Successful query list of tables N/A
Fail: Database name is not valid successresult`error!0b;();"database {name} does not exist" A database with the given name does not appear to exist. Check the specified value.
Fail: Database with given name is not found successresult`error!0b;();"database {name} does not exist" Check the name of the database you are searching for as it does not seem to exist.
Fail: Table name is not valid successresult`error!0b;();"Table name is invalid" Provide a valid symbol for the table name.
Fail: Table with given name is not found successresult`error!0b;();"Table name is not found". A table with the given name doens't exist. Double check the supplied value.
Error: KDBAI is not available Cannot write to handle ... Check your connection and if your server is running.