First steps with SQream DB¶
This tutorial takes you through a few basic operations in SQream DB.
In this topic:
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
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?
- Explore all of SQream DB’s SQL Syntax
- See the full SQream SQL CLI reference
- Connect a third party tool to SQream DB and start analyzing data