SingleStoreDB is a HTAP (Hybrid transactional/analytical processing) database. This means that instead of having separate systems for transactional and analytical/reporting data, we can use one system for both.
SingleStoreDB is the #64 ranked database system on DB-Engines.
The database can run on Linux, or on Docker. There’s a cloud version as well, which is the one I chose to use.
SingleStore is MySQL compatible. It can run as a distributed system, so multiple nodes can be added.
Sign Up:
There’s a free trial for the cloud database. You create an account with your email address. They ask for a company email, but I was able to just use my personal Gmail address.
Getting Started:
Once we’re in to the cloud management screen, we’ll need to create a Workspace Group. A workspace is a container for databases, and these workspaces can be scaled as needed. We stick with the default Standard type (I initially selected the free Starter tier, but that option wouldn’t let me progress with setup). I set the Deployment Type to Non-Production, otherwise I kept the defaults. Once clicking ‘Create Workspace’, it takes a 3-4 minutes for the setup to complete.
The default is for the workspace to go into a suspended state after 20 minutes of activity. We can resume by selecting the workspace in the left pane, go to the Overview tab, and click Resume. It took two minutes or so for the resume to complete.
Once back on the main management screen, We can go to Develop => SQL Editor and run some queries.
Even though a default database was created during workspace creation, we can create other databases in the SQL Editor. A simple
CREATE DATABASE TestDB;
will create a new database. We can highlight the statement and press Control and Return to execute.
Tables:
There are two types of tables that can be created: Columnstore and rowstore. The Columnstore option is the default, and Rowstore is the 2nd option. Columnstore tables are stored to disk, and are intended for analytic work. Rowstore tables are in-memory tables, and are meant for OLTP/Transactional work. Data added to an in-memory rowstore table is still written to a transaction log, so the data can be recreated after a restart.
SingleStore is MySQL compatible, so we can use that syntax to create a simple table:
CREATE TABLE IF NOT EXISTS Customer (
CustomerId int AUTO_INCREMENT NOT NULL PRIMARY KEY,
Name varchar(100) NOT NULL,
PostalCode varchar(10) NOT NULL,
CreateDate datetime NOT NULL DEFAULT NOW()
);
INSERT INTO Customer(Name, PostalCode)
VALUES ('Test customer 1', '10101');
SELECT * FROM Customer;
MySQL doesn’t use schemas, and the object names are case sensitive.
When I inserted this record, the record CustomerID came back as 2251799813685249. It turns out the Auto Increment doesn’t work exactly as you would think. The values can be any number, there are no guarantees that the numbering starts at a certain value, or that the values will be consecutive, among other rules.
To create a Rowstore table, we just have to include ROWSTORE in the create statement:
CREATE ROWSTORE TABLE
There’s lots of options for loading data other than INSERTs. There’s a
LOAD DATA LOCAL INFILE
command to load from files,
CREATE PIPELINE
to load from S3, loading from MySQL backups, among other options.
Wrap Up:
SingleStoreDB is an interesting database system, the idea of combining the transactional data alongside the analytical data seems to solve a lot of problems. I’ve only gone over the very basics with creating simple tables, there’s a lot more to explore here.
Links:
SQL Rob 