Quick tip: Using SingleStoreDB's Data API

Abstract

SingleStoreDB provides a Data API that can be used to execute SQL statements over an HTTP connection. This short article will show examples of how to use this Data API.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use Iris Demo Group as our Workspace Group Name and iris-demo as our Workspace Name. We'll make a note of our password and host name.

Test the Data API

The Data API is enabled by default, and we can test it quite quickly from the command line, as follows:

curl https://<host>/ping

We'll replace the <host> with the value from our SingleStoreDB Cloud account.

The result should be:

pong

Create a database

Let's now create a database:

curl -u "admin:<password>" -H "Content-Type: application/json" --data '{"sql" : "CREATE DATABASE IF NOT EXISTS iris_demo"}' https://<host>/api/v2/exec

We'll replace the <password> and <host> with the values from our SingleStoreDB Cloud account.

The result should be:

{"lastInsertId":0,"rowsAffected":1}

Create a table

Let's now create a table:

curl -u "admin:<password>" -H "Content-Type: application/json" --data '{"sql" : "CREATE TABLE IF NOT EXISTS iris (sepal_length FLOAT, sepal_width FLOAT, petal_length FLOAT, petal_width FLOAT, species VARCHAR(20))", "database" : "iris_demo"}' https://<host>/api/v2/exec

The result should be:

{"lastInsertId":0,"rowsAffected":0}

Load data into table

We'll now load some data into our database using a MySQL client.

First, we'll download a CSV file containing the Iris flower data set.

Next, we'll launch the client:

mysql --local-infile -u admin -h <host> -P 3306 --default-auth=mysql_native_password -p

The <host> being replaced with the value obtained from SingleStoreDB Cloud.

Finally, we'll load the CSV file data into our database table, as follows:

USE iris_demo;

LOAD DATA LOCAL INFILE '/path/to/iris.csv'
INTO TABLE iris
IGNORE 1 LINES
COLUMNS TERMINATED BY ',';

We would replace /path/to/ with the actual path to where the CSV file was located.

Perform a query

We can then perform queries, such as:

curl -u "admin:<password>" -H "Content-Type: application/json" --data '{"sql" : "SELECT * FROM iris LIMIT 5", "database" : "iris_demo"}' https://<host>/api/v2/query/rows

The result should be similar to the following:

{
  "results": [
    {
      "rows": [
        {
          "sepal_length": 5.5,
          "sepal_width": 4.2,
          "petal_length": 1.4,
          "petal_width": 0.2,
          "species": "Iris-setosa"
        },
        {
          "sepal_length": 6.4,
          "sepal_width": 3.2,
          "petal_length": 4.5,
          "petal_width": 1.5,
          "species": "Iris-versicolor"
        },
        {
          "sepal_length": 5.6,
          "sepal_width": 2.5,
          "petal_length": 3.9,
          "petal_width": 1.1,
          "species": "Iris-versicolor"
        },
        {
          "sepal_length": 5.5,
          "sepal_width": 2.5,
          "petal_length": 4,
          "petal_width": 1.3,
          "species": "Iris-versicolor"
        },
        {
          "sepal_length": 6.4,
          "sepal_width": 2.7,
          "petal_length": 5.3,
          "petal_width": 1.9,
          "species": "Iris-virginica"
        }
      ]
    }
  ]
}

Summary

The SingleStoreDB Data API provides another way that we can work with SingleStoreDB. This method could be useful for certain types of applications and integrations. This is another example of the flexibility that SingleStoreDB provides.