This tutorial uses ClientCmd, the SQream DB command line client, to introduce key concepts and tasks, including:

Note: ClientCmd, the SQL client, is distributed with SQream DB, so no additional software is needed.

Before you begin

Make sure you have an active SQream DB installation.

The rest of this guide assumes you’re running SQream DB listening on local port 5000.

1. Copy the CSV file to the SQream DB server

Stage the file in a readable directory, like /temp.

$ mkdir -p /temp/ && cd $_
$ curl -O https://sqream.github.io/sqream-db-quickstart/customers.csv

2. Log into SQream DB

Open a terminal window and start ClientCmd at the command prompt:

$ ClientCmd --username=<username> --password=<password> --database=<database>

Where:

Note: If connecting to a server not on localhost:5000, specify them using the --host and --port arguments. For example, $ ClientCmd --host=192.168.0.11 --port=5112 ....

You should see our interactive client and the connected database name.

$ ClientCmd --username=sqream --password=sqream --database=master
Interactive client mode
To quit, use ^D or \q.

master=>

To exit the interactive client, use \q or Ctrl+D. If you make a mistake, use Ctrl+C to escape the current statement and clean the shell.

Find the SQream DB version

master=> select show_version();
v2.13
1 row
time: 0.073726s

3. Create a new database for testing

CREATE DATABASE test;

Note: Each SQL statement must be terminated with a semicolon - ;

Now reconnect the client to this database. Use the meta-command \c test. You can also re-start the client with the new --database=test argument.

If you successfully connected to the database, you will see the test=> prompt in place of the previous one.

4. Create a table

CREATE OR REPLACE TABLE customers ( 
   id         int IDENTITY (1,1), 
   first_name varchar(40) , 
   last_name  varchar(40) , 
   email      varchar(100) , 
   gender     varchar(6) , 
   country    varchar(40) , 
   balance DOUBLE );

Note:

5. Copy data into the table

Let’s peek at the CSV file we’ll be loading.

$ head -n5 /temp/customers.csv
first_name,last_name,email,gender,country,balance
Dollie,Deackes,ddeackes0@godaddy.com,Female,Indonesia,1962.55
Rhonda,Seiler,rseiler1@ezinearticles.com,Female,Greece,1911.38
Rolland,Friel,rfriel2@w3.org,Male,Indonesia,1594.77
Ruy,Sprey,rsprey3@webs.com,Male,Pakistan,1226.00

Before we load:

 COPY customers (first_name,last_name,email,gender,country,balance) FROM '/temp/customers.csv' WITH offset 2;

6. Query the loaded data

Count table entries

test=> SELECT count(*) from customers;
3000
1 row
time: 0.061949s

See all data

Peek at the records in the table

test=> SELECT top 5 * from customers;
3001,Dollie                                  ,Deackes                                 ,ddeackes0@godaddy.com                                                                               ,Female,Indonesia                               ,1962.5500
3002,Rhonda                                  ,Seiler                                  ,rseiler1@ezinearticles.com                                                                          ,Female,Greece                                  ,1911.3800
3003,Rolland                                 ,Friel                                   ,rfriel2@w3.org                                                                                      ,Male  ,Indonesia                               ,1594.7700
3004,Ruy                                     ,Sprey                                   ,rsprey3@webs.com                                                                                    ,Male  ,Pakistan                                ,1226.0000
3005,Celestyna                               ,Duffie                                  ,cduffie4@apple.com                                                                                  ,Female,China                                   ,710.9800
5 rows
time: 0.233690s

Query based on filters

Return list of customer emails who have academic e-mails, with the LIKE syntax

test=> select email from customers where email like '%.edu';
bedmondsong@uiuc.edu
dglabachj@stanford.edu
awells14@uiuc.edu
mschaffel2i@umich.edu
anarup2z@unc.edu
bcargenven38@msu.edu
bmaric3n@columbia.edu
npeckitt3t@ucla.edu
tpiper46@arizona.edu
pstock54@msu.edu
thupe58@stanford.edu
-- [...]
156 rows
time: 0.116133s

Aggregate and summarize

Find countries with highest average account balances

test=> SELECT TOP 10 country,
.               AVG(balance) AS avg_balance
. FROM   customers
. GROUP  BY 1
. ORDER  BY 2 DESC;
South Sudan                             ,2002.0400
Kyrgyzstan                              ,1992.3400
Moldova                                 ,1947.5800
Liechtenstein                           ,1941.2800
Svalbard and Jan Mayen                  ,1899.9100
Malawi                                  ,1868.6750
Mayotte                                 ,1799.4500
Bhutan                                  ,1789.3400
Rwanda                                  ,1763.7200
Guinea                                  ,1679.3900
10 rows
time: 0.289319s

Note:

7. Monitor the SQream DB instance

List open SQream DB connections

test=> SELECT show_connections();
192.168.0.186       ,13,2018-08-24 16:36:11 ,31,2018-08-24 16:36:11 ,  SELECT show_connections();                              
192.168.0.186       ,6,2018-08-24 16:34:38 ,24,2018-08-24 16:34:38 ,   COPY customers (first_name,last_name,email,gender,country,balance) FROM '/temp/customers.csv' WITH offset 2;
3 rows
time: 0.089976s

Column order:

Show server status

test=> SELECT show_server_status();
sqream,0,192.168.0.186,5000,test,sqream,192.168.0.186,39,SELECT show_server_status();,24-08-2018 16:45:06,Execute,24-08-2018 16:45:06
1 row
time: 0.118321s

Column order:

8. Clean-up

Once you’re done with your test, drop the test database.

  1. Switch to the master database from the client: \c master
  2. Drop the test database: 
    DROP DATABASE test;

    You can now leave the client with \q or Ctrl+D.

Summary and takeaways

Querying

SQream DB has a built in interactive client shell which allows running statements and queries.

Data loading

Data load is performed in two steps:

  1. Create a table with a schema that represents the files to be loaded into SQream DB
  2. Copy the data with the COPY command to the target table

Things to notice:

What’s next?