SQL Statements

The SQL Statements page describes the following commands:

SQream supports commands from ANSI SQL.

Data Definition Commands (DDL)

The following table shows the Data Definition commands:

Command

Usage

ADD COLUMN

Add a new column to a table

ALTER DEFAULT SCHEMA

Change the default schema for a role

ALTER TABLE

Change the schema of a table

CLUSTER BY

Change clustering keys in a table

CREATE DATABASE

Create a new database

CREATE FOREIGN TABLE

Create a new foreign table in the database

CREATE FUNCTION

Create a new user defined function in the database

CREATE SCHEMA

Create a new schema in the database

CREATE TABLE

Create a new table in the database

CREATE TABLE AS

Create a new table in the database using results from a select query

CREATE VIEW

Create a new view in the database

DROP CLUSTERING KEY

Drops all clustering keys in a table

DROP COLUMN

Drop a column from a table

DROP DATABASE

Drop a database and all of its objects

DROP FUNCTION

Drop a function

DROP SCHEMA

Drop a schema

DROP TABLE

Drop a table and its contents from a database

DROP VIEW

Drop a view

RENAME COLUMN

Rename a column

RENAME TABLE

Rename a table

RENAME SCHEMA

Rename a schema

Data Manipulation Commands (DML)

The following table shows the Data Manipulation commands:

Command

Usage

CREATE TABLE AS

Create a new table in the database using results from a select query

DELETE

Delete specific rows from a table

COPY FROM

Bulk load CSV data into an existing table

COPY TO

Export a select query or entire table to CSV files

INSERT

Insert rows into a table

SELECT

Select rows and column from a table

TRUNCATE

Delete all rows from a table

UPDATE

Modify the value of certain columns in existing rows without creating a table

VALUES

Return rows containing literal values

Utility Commands

The following table shows the Utility commands:

Command

Usage

GET TOTAL CHUNKS SIZE

Returns the total size of all data chunks saved in the system

DROP SAVED QUERY

Drops a saved query

EXECUTE SAVED QUERY

Executes a previously saved query

EXPLAIN

Returns a static query plan, which can be used to debug query plans

LIST SAVED QUERIES

Lists previously saved query names, one per row.

RECOMPILE SAVED QUERY

Recompiles a saved query that has been invalidated due to a schema change

SELECT GET LICENSE INFO

View a user’s license information

SELECT GET DDL

View the CREATE TABLE statement for a table

SELECT GET FUNCTION DDL

View the CREATE FUNCTION statement for a UDF

SELECT GET TOTAL CHUNKS SIZE

Shows the total size of all data chunks saved in the system in both compressed and uncompressed formats

SELECT GET VIEW DDL

View the CREATE VIEW statement for a view

SELECT DATA READ METRICS

Monitor license quota usage by reviewing monthly or daily data read usage

SELECT GPU METRICS

Monitor license quota usage by reviewing monthly or daily GPU usage

SELECT HEALTH CHECK MONITORING

Returns system health monitoring logs

SELECT RECOMPILE VIEW

Recreate a view after schema changes

SELECT DUMP DATABASE DDL

View the CREATE TABLE statement for an current database

SHOW CONNECTIONS

Returns a list of active sessions on the current worker

SHOW LOCKS

Returns a list of locks from across the cluster

SHOW NODE INFO

Returns a snapshot of the current query plan, similar to EXPLAIN ANALYZE from other databases

SHOW SAVED QUERY

Returns a single row result containing the saved query string

SHOW SERVER STATUS

Returns a list of active sessions across the cluster

SHOW VERSION

Returns the system version for SQream DB

SHUTDOWN SERVER

Sets your server to finish compiling all active queries before shutting down according to a user-defined time value

STOP STATEMENT

Stops or aborts an active statement

Workload Management

The following table shows the Workload Management commands:

Command

Usage

SUBSCRIBE SERVICE

Add a SQream DB worker to a service queue

UNSUBSCRIBE SERVICE

Remove a SQream DB worker from a service queue

SHOW SUBSCRIBED INSTANCES

Return a list of service queues and workers

Access Control Commands

The following table shows the Access Control commands:

Command

Usage

ALTER DEFAULT PERMISSIONS

Applies a change to defaults in the current schema

ALTER ROLE

Applies a change to an existing role

CREATE ROLE

Creates a roles, which lets a database administrator control permissions on tables and databases

DROP ROLE

Removes roles

GET ALL ROLES DATABASE DDL

Returns the definition of all role databases in DDL format

GET_ROLE_PERMISSIONS

Returns all permissions granted to a role in table format

GET_ROLE_GLOBAL_DDL

Returns the definition of a global role in DDL format

GET_ALL_ROLES_GLOBAL_DDL

Returns the definition of all global roles in DDL format

GET_ROLE_DATABASE_DDL

Returns the definition of a role’s database in DDL format

GET STATEMENT PERMISSIONS

Returns a list of permissions required to run a statement or query

GRANT

Grant permissions to a role

GRANT USAGE ON SERVICE TO ALL ROLES

Grant service usage permissions

REVOKE

Revoke permissions from a role

RENAME ROLE

Rename a role