First steps with SQream DB

This tutorial takes you through a few basic operations in SQream DB.

Preparing for this tutorial

This tutorial assumes you already have a SQream DB cluster running and the SQream command line client installed on the machine you are on.

If you haven’t already:

Run the SQream SQL client like this. It will interactively ask for the password.

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

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

master=> _

You should use a username and password that you have set up or your DBA has given you.

Tip

  • To exit the shell, type \q or Ctrl-d.
  • A new SQream DB cluster contains a database named master. We will start with this database.

Create a new database for playing around in

To create a database, we will use the CREATE DATABASE syntax.

master=> CREATE DATABASE test;
executed

Now, reconnect to the newly created database.

First, exit the client by typing \q and hitting enter.

From the Linux shell, restart the client with the new database name:

$ sqream sql --port=5000 --username=rhendricks -d test
Password:

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

test=> _

The new database name appears in the prompt. This lets you know which database you’re connected to.

Creating your first table

To create a table, we will use the CREATE TABLE syntax, with a table name and some column specifications.

For example,

CREATE TABLE cool_animals (
   id INT NOT NULL,
   name VARCHAR(20),
   weight INT
);

If the table already exists and you want to drop the current table and create a new one, you can add OR REPLACE after the CREATE keyword.

CREATE OR REPLACE TABLE cool_animals (
   id INT NOT NULL,
   name VARCHAR(20),
   weight INT
);

You can ask SQream DB to list the full, verbose CREATE TABLE statement for any table, by using the GET_DDL function, with the table name.

test=> SELECT GET_DDL('cool_animals');
create table "public"."cool_animals" (
"id" int not null,
"name" varchar(20),
"weight" int
);

If you are done with this table, you can use DROP TABLE to remove the table and all of its data.

test=> DROP TABLE cool_animals;

executed

Listing tables

To see the tables in the current database, we will query the catalog

test=> SELECT table_name FROM sqream_catalog.tables;
cool_animals

1 rows

Inserting rows

Inserting rows into a table can be performed with the INSERT statement. The statement includes the table name, an optional list of column names, and column values listed in the same order as the column names:

test=> INSERT INTO cool_animals VALUES (1, 'Dog', 7);

executed

To change the order of values, specify the column order:

test=> INSERT INTO cool_animals(weight, id, name) VALUES (3, 2, 'Possum');

executed

You can use INSERT to insert multiple rows too. Here, you use sets of parentheses separated by commas:

test=> INSERT INTO cool_animals VALUES
      (3, 'Cat', 5) ,
      (4, 'Elephant', 6500) ,
      (5, 'Rhinoceros', 2100);

executed

Note

To load big data sets, use bulk loading methods instead. See our Inserting data guide for more information.

When you leave out columns that have a default value (including default NULL value) the default value is used.

test=> INSERT INTO cool_animals (id) VALUES (6);

executed

test=> INSERT INTO cool_animals (id) VALUES (6);

executed
test=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

Note

Null row values are represented as \N

Queries

For querying, use the SELECT keyword, followed by a list of columns and values to be returned, and the table to get the data from.

test=> SELECT id, name, weight FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

To get all columns without specifying them, use the star operator *:

test=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

To get the number of values in a table without getting the full result set, use COUNT(*):

test=> SELECT COUNT(*) FROM cool_animals;
6

1 row

Filter results by adding a WHERE clause and specifying the filter condition:

test=> SELECT id, name, weight FROM cool_animals WHERE weight > 1000;
4,Elephant            ,6500
5,Rhinoceros          ,2100

2 rows

Sort the results by adding an ORDER BY clause, and specifying ascending (ASC) or descending (DESC) order:

test=> SELECT * FROM cool_animals ORDER BY weight DESC;
4,Elephant            ,6500
5,Rhinoceros          ,2100
1,Dog                 ,7
3,Cat                 ,5
2,Possum              ,3
6,\N,\N

6 rows

Filter null rows by adding a filter IS NOT NULL:

test=> SELECT * FROM cool_animals WHERE weight IS NOT NULL ORDER BY weight DESC;
4,Elephant            ,6500
5,Rhinoceros          ,2100
1,Dog                 ,7
3,Cat                 ,5
2,Possum              ,3

5 rows

Deleting rows

To delete rows in a table selectively, use the DELETE command, with a table name and a WHERE clause to specify which rows are to be deleted:

test=> DELETE FROM cool_animals WHERE weight is null;

executed
master=> SELECT  * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100

5 rows

To delete all rows in a table, use the TRUNCATE command followed by the table name:

test=> TRUNCATE TABLE cool_animals;

executed

Note

While TRUNCATE deletes data from disk immediately, DELETE does not physically remove the deleted rows. For more information on removing the rows from disk, see DELETE.

Saving query results to a CSV or PSV file

The command line client sqream sql can be used to save query results to a CSV or other delimited file format.

$ sqream sql --username=mjordan --database=nba --host=localhost --port=5000 -c "SELECT * FROM nba LIMIT 5" --results-only --delimiter='|' > nba.psv
$ cat nba.psv
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

See the Controlling the output of the client section of the reference for more options.

What’s next?