Skip to content

Ingest Data

This section covers how to insert randomly generated data into your KDB.AI database.

An optional list of metadata columns is included for each document; this enhances filtering during querying and searching.

Real-world applications often involve substantially higher-dimensional vectors than our example.

Generate data

To begin the data ingestion process, first establish a connection with the table and then prepare the data for insertion.

import numpy as np 
import pandas as pd 

# Connect with the KDB.AI table
documents = session.table('documents') 

# Generate data
n_rows = 2000

data = pd.DataFrame({
    'id': np.arange(n_rows, dtype='int16'),
    'tag': np.random.choice([True, False], n_rows),
    'author': [f'author{i}' for i in range(n_rows)],
    'length': np.random.randint(0, 1000, n_rows, dtype='int32'),
    'content': [f'document{i}' for i in range(n_rows)],
    'createdDate': pd.date_range(start='2020-01-01', periods=n_rows, freq='1D'),
    'embeddings': [np.random.rand(12).astype('float32') for _ in range(n_rows)]

JSON data can be generated in any language. Below .j.j from q is used to generate example data. The output of .j.j is used as a standard for how table data should be formatted.

n: 2000;
// Note: for simplicity we do not randomize content, createdDate, or embeddings
rows:.j.j flip `id`tag`author`length`content`createdDate`embeddings!(n?0h;n?0b;n?`bob`joe`jill;n?1000;n#enlist;n#.z.p;n#enlist "e"$til 12);

An example preview of this data, where n is '2', would be:

    "id": 21212,
    "tag": true,
    "author": "jill",
    "length": 68,
    "content": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789",
    "createdDate": "2023-10-11T00:00:00.000000000",
    "embeddings": [0,1,2,3,4,5,6,7,8,9,10,11]
    "id": 19376,
    "tag": false,
    "author": "joe",
    "length": 626,
    "content": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789",
    "createdDate": "2023-10-11T00:00:00.000000000",
    "embeddings": [0,1,2,3,4,5,6,7,8,9,10,11]

For the insert, only include fields that exist in the target table. In the case of metadata columns with null values, KDB.AI handles the insert operation gracefully. However, this approach is not applicable for vector columns, as nulls are not accepted in those cases.

Ensure precise alignment of data types with the table schema to avoid exceptions.

JSON row format

JSON supports the following types:

  • String
  • Number
  • Object
  • Array
  • Boolean
  • Null

JSON row data must be formatted as one of the above types based on the databases column schema.

For example, a table with a boolean column requires the row data to a literal boolean of true or false. Parsing the value from the string true or false, or from the number 0 and 1 is not supported.

For the following database types, the corresponding JSON formats must be used:

Database type ID Database type name JSON data format Description Example
-1h boolean Boolean A Boolean literal of true or false true
-2h guid String A 36 character UUIDv4 formatted String "77579e36-71e7-d395-5551-5a4221e86e2b"
-4h byte String A 2 character hex string ff
-5h short Number A 16 bit number 32767
-6h int Number A 32 bit number 2147483647
-7h long Number A 64 bit number 4611686018427387904
-8h real Number A 32 bit floating point number 3.14159265
-9h float Number A 64 bit floating point number 3.14159265
-10h char String A string of a single character "a"
-11h symbol String A string representing a symbol "BTC"
-12h timestamp String A ISO date-time without an offset in format 'yyyy-MM-dd'T'HH:mm:ss' with optional 0-9 decimals for nanoseconds "2023-01-01T00:00:00.000000000"
-13h month String A string representing a month in format MM-dd "2023-10"
-14h date String A string representing an ISO Date without an offset in format yyyy-MM-dd "2023-10-01"
-15h datetime String A ISO date-time without an offset in format 'yyyy-MM-dd'T'HH:mm:ss' with optional 0-3 decimals for milliseconds "2023-01-01T00:00:00.000"
-16h timespan String A duration of time with units in nanoseconds 'd'D'HH:mm:ss' with optional 0-9 decimals for nanoseconds "0D00:00:00.000000005"
-17h minute String A duration of time in HH:mm "22:59"
-18h second String A duration of time in HH:mm:ss "22:59:13"
-19h time String A duration fo time in HH:mm:ss with optional 0-9 decimals for nanosecond precision "22:59:13.000000000"
1h booleans Array<Boolean> Array of boolean values [true, false]
2h guids Array<String> Array of guid values ["09c4f826-b3f2-e699-c7ff-5195d89a0925", "c0b9ec94-87b6-b0e9-3427-312d62aaec9c"]
4h bytes Array<String> Array of byte values ["ff", "0e"]
5h shorts Array<Number> Array of short values [32767, -32767]
6h ints Array<Number> Array of int values [2147483647, -2147483647]
7h longs Array<Number> Array of long values [4611686018427387904, -4611686018427387904]
8h reals Array<Number> Array of real values [1.1, 1.2]
9h floats Array<Number> Array of float values [1.1, 1.2]
10h string String A string of text "abcdef"
11h symbols Array<String> Array of symbol values ["BTC","MSFT"]
12h timestamps Array<String> Array of timestamp values ["2023-01-01T00:00:00.000000000", "2023-01-02T00:00:00.000000000"]
13h months Array<String> Array of month values ["2023-10", "2023-11"]
14h dates Array<String> Array of date values ["2023-10-01", "2023-10-02"]
15h datetimes Array<String> Array of datetime values ["2023-01-01T00:00:00.000", "2023-01-02T00:00:00.000"]
16h timespans Array<String> Array of timespan values ["0D00:00:00.000000005", "0D00:00:00.000000006"]
17h minutes Array<String> Array of minute values ["22:59", "23:00"]
18h seconds Array<String> Array of second values ["22:59:13", "22:59:14"]
19h times Array<String> Array of time values ["22:59:13.000000000", "2023-01-01T00:00:00.001"]

If null is provided for a value, it is replaced by its relevant empty string or largest negative number.

JSON null is not supported within arrays. If you require a data point to be numerically null, set the value to the largest negative number appropriate.

For columns containing multiple data types, no conversion takes place. All numbers are set as float. This results in suboptimal data storage and compression.


Now you can populate your table with data.

Populate the documents table with the above dataframe.


Populate the documents table with a curl http request.

Save the following to a local file named insert.json:

  "table": "documents",
  "rows": [
      "id": 21212,
      "tag": true,
      "author": "jill",
      "length": 68,
      "content": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789",
      "createdDate": "2023-10-11T00:00:00.000000000",
      "embeddings": [0,1,2,3,4,5,6,7,8,9,10,11]
      "id": 19376,
      "tag": false,
      "author": "joe",
      "length": 626,
      "content": "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789",
      "createdDate": "2023-10-11T00:00:00.000000000",
      "embeddings": [0,1,2,3,4,5,6,7,8,9,10,11]

Ensure that the @ sign is included before the filename, otherwise, the file becomes uri-encoded.

curl -H 'Content-Type: application/json' -d @insert.json localhost:8082/api/v1/insert

If the supplied embeddings are not the same dimension as the table, the operation fails.

When working with text, chunking refers to how source material is broken up prior to passing to a model for the creation of embeddings. For example, you can choose between:

  • Processing an entire document and generate a single embedding
  • Separating the document into paragraphs or sentences and have embeddings at that level.

Typically, you would chunk sentences, which results in multiple rows per document.

The KDB.AI Cloud client has a maximum insert size limit of 10MB.

Batch inserts

KX recommends inserting data in batches, by passing a dataframe to the insert method. This can significantly improve performance in some cases. For large dataframes, you can split the df. The fewer batches sent, the more efficient the inserts are.

from tqdm import tqdm 
n = 200  # batch size

for i in tqdm(range(0, data.shape[0], n)):

Insert data with caution, the current version of KDB.AI does not support individual record updates or deletions.

Note that batch and chunk are not synonymous:

  • Chunking is determined by the decision taken on how the source text is broken down for embedding purposes.
  • Batching is determined by the total size of your dataframe and the number of rows that can be inserted into KDB.AI at a time.

Tip: Chunk as appropriate, but pack as much data into each batch you insert as possible.

Train index

The IVF and IVFPQ indexes need training before using them for similarity searching. When creating a new table with either of these two indexes, it's mandatory to include the additional parameter trainingVectors within the vectorIndex attribute of the schema. This trainingVectors parameter specifies the quantity of vectors required for training.

The train function operates in a manner similar to the insert function, with the key distinction being that it caches the inserted data until the specified threshold, set with trainingVectors, is reached. Once this threshold is met, the training of the index occurs, followed by the insertion of the data.

# Following a similar schema but with the index set as ivfpq
schema = {'columns': [
        {'name': 'id', 'pytype': 'int16'},
        {'name': 'tag', 'pytype': 'bool'},
        {'name': 'author', 'pytype': 'str'},
        {'name': 'length', 'pytype': 'int32'},
        {'name': 'content', 'pytype': 'str'},
        {'name': 'createdDate', 'pytype': 'datetime64[ns]'},
        {'name': 'embeddings',
            'vectorIndex': {'type': 'ivfpq', 
                            'metric': 'L2', 
                            'trainingVectors': 1000}}]}

documentsIVFPQ = session.create_table('documentsIVFPQ', schema)   

# Add a subset of the data

# Return: '500 records cached for training, threshold is 1000'

# Add the rest of the training data that surpass the training threshold

# Return: 'Index training complete, 100 extra rows discarded'

Training data is provided identically to an insert, except that the URL is /api/v1/train.

To train an index using the same data from the insert example, insert.json, update the name of the table and run:

# Schema for the new table 
  "type": "splayed",
  "columns": [
    {"name": "id", "type": "short"},
    {"name": "tag", "type": "boolean"},
    {"name": "author", "type": "char"},
    {"name": "length", "type": "int"},
    {"name": "content", "type": "char"},
    {"name": "createdDate", "type": "timestamp"},
      "name": "embeddings",
      "type": "reals",
      "vectorIndex": {
        "type": "ivfpq",
        "metric": "L2",
        "nsplits": 4,
        "trainingVectors": 4

curl -H 'Content-Type: application/json' -d @trainSchema.json localhost:8082/api/v1/config/table/documentsIVFPQ

curl -H 'Content-Type: application/json' -d @insert.json localhost:8082/api/v1/train

Training must occur before data insertion. Vectors used for training are not included in the index and they won't be available for search operations.

Next steps

Now that you have data in your table, you can query it.