Getting Started

The Getting Started page describes the following:

Before Installing SQream

Before installing SQream, do the following:

  • Set up your local machine according to SQream’s recommended pre-installation configurations.

  • Verify you have an NVIDIA-capable server, either on-premise or on supported cloud platforms:

    • Red Hat Enterprise Linux v7.x

    • CentOS v7.x

    • Ubuntu 18.04

    • Amazon Linux

  • Verify that you have the following:

    • An NVIDIA GPU - SQream recommends using a Tesla GPU.

    • An SSH connection to your server.

    • SUDO permissions for installation and configuration purposes.

    • A SQream license - Contact support@sqream.com or your SQream account manager for your license key.

For more information, see the following:

Installing SQream

The Installing SQream section includes the following SQream installation methods:

Using the SQream SQL Command Line Interface

SQream includes a built-in client for executing SQL statements either interactively or using the Command Line Interface (CLI). The SQream SQL CLI Reference page provides a reference for the options and parameters.

For more information, see SQream SQL CLI Reference.

Performing Basic SQream Operations

After installing SQream you can perform the operations described in the Performing Basic SQream Operations section:

Running the SQream SQL Client

The following example shows how to run the SQream SQL client:

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

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

master=> _

Running the SQream SQL client prompts you to provide your password. Use the username and password that you have set up, or your DBA has provided.

Tip

  • You can exit the shell by typing \q or Ctrl-d.

  • A new SQream cluster contains a database named master, which is the database used in the examples on this page.

Creating a New Database

To create a new database:

  1. Write a CREATE DATABASE statement.

    The following is an example of creating a new database:

    master=> CREATE DATABASE test;
executed

  2. Reconnect to the newly created database.

    1. Exit the client by typing \q and pressing Enter.

    2. 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 name of the new database that you are connected to is displayed in the prompt.

Creating Your First Table

The Creating Your First Table section describes the following:

Creating a Table

The CREATE TABLE syntax is used to create your first table. This table includes a table name and column specifications, as shown in the following example:

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

For more information on creating a table, see CREATE TABLE.

Replacing a Table

You can drop an existing table and create a new one by adding the OR REPLACE parameter after the CREATE keyword, as shown in the following example:

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

Listing a CREATE TABLE Statement

You can list the full, verbose CREATE TABLE statement for a table by using the GET DDL function with the table name as shown in the following example:

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

Note

  • SQream DB identifier names such as table names and column names are not case sensitive. SQream DB lowercases all identifiers bu default. If you want to maintain case, enclose the identifiers with double-quotes.

  • SQream DB places all tables in the public schema, unless another schema is created and specified as part of the table name.

For information on listing a CREATE TABLE statement, see GET_DDL.

Dropping a Table

When you have finished working with your table, you can drop the table to remove it table and its content, as shown in the following example:

test=> DROP TABLE cool_animals;

executed

For more information on dropping tables, see DROP TABLE.

Listing Tables

To see the tables in the current database you can query the catalog, as shown in the following example:

test=> SELECT table_name FROM sqream_catalog.tables;
cool_animals

1 rows

Inserting Rows

The Inserting Rows section describes the following:

Inserting Basic Rows

You can insert basic rows into a table using the INSERT statement. The inserted statement includes the table name, an optional list of column names, and column values listed in the same order as the column names, as shown in the following example:

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

executed

Changing Value Order

You can change the order of values by specifying the column order, as shown in the following example:

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

executed

Inserting Multiple Rows

You can insert multiple rows using the INSERT statement by using sets of parentheses separated by commas, as shown in the following example:

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

executed

Note

You can load large data sets using bulk loading methods instead. For more information, see Inserting Data Overview.

Omitting Columns

Omitting columns that have a default values (including default NULL values) uses the default value, as shown in the following example:

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

For more information on inserting rows, see INSERT.

For more information on default values, see default value.

Running Queries

The Running Queries section describes the following:

Running Basic Queries

You can run a basic query using the SELECT keyword, followed by a list of columns and values to be returned, and the table to get the data from, as shown in the following example:

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

For more information on the SELECT keyword, see SELECT.

To Output All Columns

You can output all columns without specifying them using the star operator *, as shown in the following example:

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

Outputting Shorthand Table Values

You can output the number of values in a table without getting the full result set by using the COUNT statement:

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

1 row

Filtering Results

You can filter results by adding a WHERE clause and specifying the filter condition, as shown in the following example:

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

2 rows

Sorting Results

You can sort results by adding an ORDER BY clause and specifying ascending (ASC) or descending (DESC) order, as shown in the following example:

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

Filtering Null Rows

You can filter null rows by adding an IS NOT NULL filter, as shown in the following example:

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

For more information, see the following:

  • Outputting the number of values in a table without getting the full result set - COUNT(*).

  • Filtering results - WHERE

  • Sorting results - ORDER BY

  • Filtering rows - IS NOT NULL

Deleting Rows

The Deleting Rows section describes the following:

Deleting Selected Rows

You can delete rows in a table selectively using the DELETE command. You must include a table name and WHERE clause to specify the rows to delete, as shown in the following example:

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

Deleting All Rows

You can delete all rows in a table using the TRUNCATE command followed by the table name, as shown in the following example:

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, see the following:

Saving Query Results to a CSV or PSV File

You can save query results to a CSV or PSV file using the sqream sql command from a CLI client. This saves your query results to the selected delimited file format, as shown in the following example:

$ 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

For more output options, see Controlling the Client Output.

What’s next?