Getting Started with SingleStore Kai

Prerequisites

• A cluster group running SingleStore version 8.5 or later.

  • Run the following command to check the version:

    SQL

    Copy

    SELECT @@memsql_version;

• A SingleStore Kai-enabled cluster. 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 cluster, perform the following tasks:

1. On the Cloud Portal, select Create New > Cluster.

2. On the Create New Cluster page, enter or select the configuration settings as applicable.

3. Under Settings, enable the MongoDB® Compatible Endpoint API toggle switch.

4. Select Create Cluster.

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

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

• 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 cluster, the API rejects any user authentication requests.

Access the API

A SingleStore Kai-enabled cluster 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.

You can also connect to your MongoDB® instance via AWS PrivateLink. Refer to Connect to MongoDB® using AWS PrivateLink for more information.

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 }