Getting Started with SingleStore Kai

Prerequisites

• A workspace group running SingleStore version 8.5.

  • Run the following command to check the version:

    SQL

    Copy

    SELECT @@memsql_version;

• A SingleStore Kai-enabled workspace. Refer to Enable the API for more information.

• Any MongoDB® client that uses the MongoDB® wire protocol or standard MongoDB® driver that supports the loadBalanced mode.

Enable the API

To enable the API for your SingleStore Helios workspace, perform the following tasks:

1. On the Cloud Portal, select Create Workspace Group. You can also create a new workspace in an existing workspace group.

2. On the Create Workspace Group page, enter or select the configuration settings, and then select Next.

3. On the Create Workspace page, under Advanced Settings, enable the SingleStore Kai toggle switch.

   

4. Select Create Workspace.

The API is now enabled for your workspace. You can only enable or disable the API endpoint while creating a workspace.

Note: The following global compatibility configuration variables are automatically set when the API is enabled for a workspace:

• json_compatibility_level: '8.0'

• data_conversion_compatibility_level: '8.0'

• regexp_format: 'advanced'

If any of these variables cannot be set to the values specified above for a workspace, the API rejects any user authentication requests.

Access the API

A SingleStore Kai-enabled workspace has two different endpoints:

• mongodb://

• mysql://

For example:

• Copy

  mongodb://<user>:<password>@svc-XXXX.svc.singlestore.com:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true

• Copy

  mysql://svc-XXXX.svc.singlestore.com:3306

Connect to the mongodb:// endpoint to run MongoDB® queries. This endpoint supports SASL/PLAIN authentication mechanism.

Clients/applications must connect to the API in load-balanced mode. If a client/application connects to the API without specifying loadBalanced=true in the connection string, the API rejects the authentication request with an explanatory error message.

GRANT EXECUTE ON cluster.* TO joe;

mongosh "mongodb://<username>@<host>:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true"

mongosh "mongodb://admin@svc-XXXX.svc.singlestore.com:27017/?authMechanism=PLAIN&tls=true&loadBalanced=true"

Examples

The following examples perform CRUD operations using the API endpoint.

Create a Database

The following command creates a database named dbExample.

use dbExample
show dbs

mongo               0.00 B
dbExample           0.00 B
information_schema  0.00 B

Create a Collection and Insert Documents

Run the following command to create a collection named exampleCollection and add multiple documents to this collection:

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 } ] )

{ acknowledged: true, insertedIds: { '0': 1, '1': 2, '2': 3, '3': 4 } }

Read Values from a Collection

The following command returns all the documents stored in exampleCollection:

db.exampleCollection.find()

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

Update a Collection

The following example updates the first document in exampleCollection where Code equals "xv1f":

db.exampleCollection.updateOne(
        { Code: "xv1f" },
        { $set: { Qty: 40 } }
    )

{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}

db.exampleCollection.find()

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

The following example updates all the documents in exampleCollection where Qty is less than or equal to 25.

db.exampleCollection.updateMany(
        { Qty: {$lte: 25 } },
        { $set: { Comment: "Restock required" },  $inc: { Qty: -10 } }
    )

{
  acknowledged: true,
  insertedId: null,
  matchedCount: 2,
  modifiedCount: 2,
  upsertedCount: 0
}

db.exampleCollection.find()

[
  { _id: 1, Code: 'xv1f', Qty: 40 },
  { _id: 2, Code: 'nm3w', Qty: 30 },
  { _id: 3, Code: 'qoma', Qty: 10, Comment: 'Restock required' },
  { _id: 4, Code: 'hr3k', Qty: 5, Comment: 'Restock required' }
]

Delete a Document

The following command deletes all the documents from exampleCollection that match the specified condition:

db.exampleCollection.deleteMany(
        { Comment: "Restock required", Qty: { $lt: 10 } }
    )

{ acknowledged: true, deletedCount: 1 }