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 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 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.7,
"sepal_width":4.4,
"petal_length":1.5,
"petal_width":0.4,
"species":"Iris-setosa\r"
},
{
"sepal_length":4.4,
"sepal_width":3,
"petal_length":1.3,
"petal_width":0.2,
"species":"Iris-setosa\r"
},
{
"sepal_length":5,
"sepal_width":3.5,
"petal_length":1.6,
"petal_width":0.6,
"species":"Iris-setosa\r"
},
{
"sepal_length":6.7,
"sepal_width":3.1,
"petal_length":4.7,
"petal_width":1.5,
"species":"Iris-versicolor\r"
},
{
"sepal_length":5.7,
"sepal_width":3,
"petal_length":4.2,
"petal_width":1.2,
"species":"Iris-versicolor\r"
}
]
}
]
}
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.
Top comments (0)