Sqream SQL CLI Reference

SQream DB comes with a built-in client for executing SQL statements either interactively or from the command-line.

This page serves as a reference for the options and parameters. Learn more about using SQream DB SQL with the CLI by visiting the first_steps tutorial.

Installing Sqream SQL 

If you have a SQream DB installation on your server, sqream sql can be found in the bin directory of your SQream DB installation, under the name sqream.

Note

If you installed SQream DB via Docker, the command is named sqream-client sql, and can be found in the same location as the console.

Changed in version 2020.1: As of version 2020.1, ClientCmd has been renamed to sqream sql.

To run sqream sql on any other Linux host:

Download the sqream sql tarball package from the Client Drivers page.
Untar the package: tar xf sqream-sql-v2020.1.1_stable.x86_64.tar.gz

Start the client:

$ cd sqream-sql-v2020.1.1_stable.x86_64
$ ./sqream sql --port=5000 --username=jdoe --databasename=master
Password:

Interactive client mode
To quit, use ^D or \q.

master=> _

Troubleshooting Sqream SQL Installation 

Upon running sqream sql for the first time, you may get an error error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory.

Solving this error requires installing the ncruses or libtinfo libraries, depending on your operating system.

Ubuntu:
1. Install libtinfo:
  
  $ sudo apt-get install -y libtinfo
2. Depending on your Ubuntu version, you may need to create a symbolic link to the newer libtinfo that was installed.
  
  For example, if libtinfo was installed as /lib/x86_64-linux-gnu/libtinfo.so.6.2:
  
  $ sudo ln -s /lib/x86_64-linux-gnu/libtinfo.so.6.2 /lib/x86_64-linux-gnu/libtinfo.so.5
CentOS / RHEL:
1. Install ncurses:
  
  $ sudo yum install -y ncurses-libs
2. Depending on your RHEL version, you may need to create a symbolic link to the newer libtinfo that was installed.
  
  For example, if libtinfo was installed as /usr/lib64/libtinfo.so.6:
  
  $ sudo ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5

When writing long statements and queries, it may be beneficial to use line-breaks. The prompt for a multi-line statement will change from => to ., to alert users to the change. The statement will not execute until a semicolon is used.

$ sqream sql --port=5000 --username=mjordan -d master
Password:

Interactive client mode
To quit, use ^D or \q.

master=> SELECT "Age",
. AVG("Salary")
. FROM NBA
. GROUP BY 1
. ORDER BY 2 ASC
. LIMIT 5
. ;
38,1840041
19,1930440
23,2034746
21,2067379
36,2238119
5 rows
time: 0.009320s

Executing Batch Scripts (`-f`)

To run an SQL script, use the -f <filename> argument.

For example,

$ sqream sql --port=5000 --username=jdoe -d master -f sql_script.sql --results-only

Tip

Output can be saved to a file by using redirection (>).

Executing Commands Immediately (`-c`)

To run a statement from the console, use the -c <statement> argument.

For example,

$ sqream sql --port=5000 --username=jdoe -d nba -c "SELECT TOP 5 * FROM nba"
Avery Bradley           ,Boston Celtics        ,0,PG,25,6-2 ,180,Texas                ,7730337
Jae Crowder             ,Boston Celtics        ,99,SF,25,6-6 ,235,Marquette            ,6796117
John Holland            ,Boston Celtics        ,30,SG,27,6-5 ,205,Boston University    ,\N
R.J. Hunter             ,Boston Celtics        ,28,SG,22,6-5 ,185,Georgia State        ,1148640
Jonas Jerebko           ,Boston Celtics        ,8,PF,29,6-10,231,\N,5000000
5 rows
time: 0.202618s

Tip

Remove the timing and row count by passing the --results-only parameter

Examples 

Starting a Regular Interactive Shell 

Connect to local server 127.0.0.1 on port 5000, to the default built-in database, master:

$ sqream sql --port=5000 --username=mjordan -d master
Password:

Interactive client mode
To quit, use ^D or \q.

master=>_

Connect to local server 127.0.0.1 via the built-in load balancer on port 3108, to the default built-in database, master:

$ sqream sql --port=3105 --clustered --username=mjordan -d master
Password:

Interactive client mode
To quit, use ^D or \q.

master=>_

Executing Statements in an Interactive Shell 

Note that all SQL commands end with a semicolon.

Creating a new database and switching over to it without reconnecting:

$ sqream sql --port=3105 --clustered --username=oldmcd -d master
Password:

Interactive client mode
To quit, use ^D or \q.

master=> create database farm;
executed
time: 0.003811s
master=> \c farm
farm=>

farm=> create table animals(id int not null, name text(30) not null, is_angry bool not null);
executed
time: 0.011940s

farm=> insert into animals values(1,'goat',false);
executed
time: 0.000405s

farm=> insert into animals values(4,'bull',true) ;
executed
time: 0.049338s

farm=> select * from animals;
1,goat                          ,0
4,bull                          ,1
2 rows
time: 0.029299s

Executing SQL Statements from the Command Line 

$ sqream sql --port=3105 --clustered --username=oldmcd -d farm -c "SELECT * FROM animals WHERE is_angry = true"
4,bull                          ,1
1 row
time: 0.095941s

Controlling the Client Output 

Two parameters control the dispay of results from the client:

--results-only - removes row counts and timing information
--delimiter - changes the record delimiter

Exporting SQL Query Results to CSV 

Using the --results-only flag removes the row counts and timing.

$ sqream sql --port=3105 --clustered --username=oldmcd -d farm -c "SELECT * FROM animals" --results-only > file.csv
$ cat file.csv
1,goat                          ,0
2,sow                           ,0
3,chicken                       ,0
4,bull                          ,1

Changing a CSV to a TSV 

The --delimiter parameter accepts any printable character.

Tip

To insert a tab, use Ctrl-V followed by Tab ↹ in Bash.

$ sqream sql --port=3105 --clustered --username=oldmcd -d farm -c "SELECT * FROM animals" --delimiter '  ' > file.tsv
$ cat file.tsv
1  goat                             0
2  sow                              0
3  chicken                          0
4  bull                             1

Executing a Series of Statements From a File 

Assuming a file containing SQL statements (separated by semicolons):

$ cat some_queries.sql
   CREATE TABLE calm_farm_animals
  ( id INT IDENTITY(0, 1), name TEXT(30)
  );

INSERT INTO calm_farm_animals (name)
  SELECT name FROM   animals WHERE  is_angry = false;

$ sqream sql --port=3105 --clustered --username=oldmcd -d farm -f some_queries.sql
executed
time: 0.018289s
executed
time: 0.090697s

Connecting Using Environment Variables 

You can save connection parameters as environment variables:

$ export SQREAM_USER=sqream;
$ export SQREAM_DATABASE=farm;
$ sqream sql --port=3105 --clustered --username=$SQREAM_USER -d $SQREAM_DATABASE

Connecting to a Specific Queue 

When using the dynamic workload manager - connect to etl queue instead of using the default sqream queue.

$ sqream sql --port=3105 --clustered --username=mjordan -d master --service=etl
Password:

Interactive client mode
To quit, use ^D or \q.

master=>_

Operations and Flag References 

Command Line Arguments 

Sqream SQL supports the following command line arguments:

Argument	Default	Description
`-c` or `--command`	None	Changes the mode of operation to single-command, non-interactive. Use this argument to run a statement and immediately exit.
`-f` or `--file`	None	Changes the mode of operation to multi-command, non-interactive. Use this argument to run a sequence of statements from an external file and immediately exit.
`--host`	`127.0.0.1`	Address of the SQream DB worker.
`--port`	`5000`	Sets the connection port.
`--databasename` or `-d`	None	Specifies the database name for queries and statements in this session.
`--username`	None	Username to connect to the specified database.
`--password`	None	Specify the password using the command line argument. If not specified, the client will prompt the user for the password.
`--clustered`	False	When used, the client connects to the load balancer, usually on port `3108`. If not set, the client assumes the connection is to a standalone SQream DB worker.
`--service`	`sqream`	Service name (queue) that statements will file into.
`--results-only`	False	Outputs results only, without timing information and row counts
`--no-history`	False	When set, prevents command history from being saved in `~/.sqream/clientcmdhist`
`--delimiter`	`,`	Specifies the field separator. By default, `sqream sql` outputs valid CSVs. Change the delimiter to modify the output to another delimited format (e.g. TSV, PSV). See the section supported record delimiters below for more information.

Tip

Run $ sqream sql --help to see a full list of arguments

Supported Record Delimiters 

The supported record delimiters are printable ASCII values (32-126).

Recommended delimiters for use are: ,, |, tab character.
The following characters are not supported: \, N, -, :, ", \n, \r, ., lower-case latin letters, digits (0-9)

Meta-Commands 

Meta-commands in Sqream SQL start with a backslash (\)