Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

Kai Data Storage Model

Column Name	Data Type	Description
`_id`	BSON NOT NULL	The `_id` field required in all MongoDB® documents.
`_more`	BSON NOT NULL	Contains all the fields in the document, except the `_id` field.
`$_id`	PERSISTED LONGBLOB	Used to implement SingleStore unique indexes on MongoDB® collections, similar to BSON unique indexes. This column contains the `_id` field normalized using the `BSON_NORMALIZE` or `BSON_NORMALIZE_NO_ARRAY` function, which transform the BSON data into a byte stream that preserves the BSON comparison when compared byte-by-byte.

For example, run the following command on a Kai-enabled cluster to create a collection named exampleCollection:

MongoDB

db.exampleCollection.insertMany( [
        { _id: 1, Code: "xv1f", Qty: 45 },
        { _id: 2, Code: "nm3w", Qty: 30 },
        { _id: 3, Code: "qoma", Qty: 20 },
        { _id: 4, Code: "hr3k", Qty: 15 } ] )

This exampleCollection is stored in SingleStore as:

MongoDB

db.runCommand({sql:"SHOW CREATE TABLE dbTest.exampleCollection"}).cursor.firstBatch[0]["Create Table"]

CREATE TABLE `exampleCollection` (
  `_id` bson NOT NULL,
  `_more` bson NOT NULL COMMENT 'KAI_MORE' ,
  `$_id` as BSON_NORMALIZE_NO_ARRAY(`_id`) PERSISTED longblob COMMENT 'KAI_AUTO' ,
  SHARD KEY `__SHARDKEY` (`$_id`),
  UNIQUE KEY `__PRIMARY` (`$_id`) USING HASH,
  SORT KEY `__UNORDERED` ()
) COMMENT 'KAI_CID:2QZxxxx1xxxxxxxx' AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES'

The COMMENT clauses shown in this CREATE TABLE SQL statement, for example, COMMENT 'KAI_AUTO', COMMENT 'KAI_MORE', or COMMENT 'KAI_CID:2QZxxxx1xxxxxxxx', are reserved for internal use. Using these COMMENT clauses in a CREATE TABLE statement manually may lead to unexpected behavior.

When an index is created using the Kai API, SingleStore generates a computed column with the comment KAI_AUTO to indicate that this column was created using the Kai API and stores this information in the table metadata. This information is used during query generation for MongoDB® queries. SingleStore recommends creating MongoDB® collections and indexes using the SingleStore Kai endpoint (mongodb://) if you intend to query these collections using the Kai API.

View the BSON Data

To view the BSON data, SingleStore recommends the following:

Use the Kai Shell or other supported MongoDB® tools, such as MongoDB® Compass.
Cast the columns to JSON using the following SQL command:
SQL
```
SELECT _id :> JSON , _more :> JSON FROM <table_name>;
```

Example

To view the documents in a collection named exampleCollection using the Kai Shell, run the following command:

MongoDB

db.exampleCollection.find()

[ { _id: 4, Code: 'hr3k', Qty: 15 },
  { _id: 3, Code: 'qoma', Qty: 20 },
  { _id: 1, Code: 'xv1f', Qty: 45 },
  { _id: 2, Code: 'nm3w', Qty: 30 } ]

To view the documents in exampleCollection using SQL commands, run the following command in the SQL command-line:

SQL

SELECT _id :> JSON AS _id, _more :> JSON AS _more FROM exampleCollection;

+-----+--------------------------+
| _id | _more                    |
+-----+--------------------------+
| 4   | {"Code":"hr3k","Qty":15} |
| 3   | {"Code":"qoma","Qty":20} |
| 1   | {"Code":"xv1f","Qty":45} |
| 2   | {"Code":"nm3w","Qty":30} |
+-----+--------------------------+

Kai Data Storage Model

On this page

View the BSON Data

Example

Was this article helpful?

On this page

Was this article helpful?