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 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