SQL statements

SQream DB supports commands from ANSI SQL.

Data Definition commands (DDL)

DDL 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

CREATE DATABASE

Create a new database

CREATE EXTERNAL TABLE

Create a new external table in the database (deprecated)

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

Data manipulation commands (DML)

DML 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

VALUES

Return rows containing literal values

Utility commands

Utility commands

Command

Usage

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

Saved queries

See more about Saved queries

Saved queries

Command

Usage

SELECT DROP_SAVED_QUERY

Drop a saved query

SELECT EXECUTE_SAVED_QUERY

Executes a saved query

SELECT LIST_SAVED_QUERIES

Returns a list of saved queries

SELECT RECOMPILE_SAVED_QUERY

Recompiles a query that has been invalidated by a schema change

SELECT SAVE_QUERY

Compiles and saves a query for re-use and sharing

SELECT SHOW_SAVED_QUERY

Shows query text for a saved query

Monitoring

Monitoring statements allow a database administrator to execute actions in the system, such as aborting a query or get information about system processes.

Command

Usage

EXPLAIN

Returns a static query plan for a statement

SHOW_CONNECTIONS

Returns a list of jobs and statements on the current worker

SHOW_LOCKS

Returns any existing locks in the database

SHOW_NODE_INFO

Returns a query plan for an actively running statement with timing information

SHOW_SERVER_STATUS

Shows running statements across the cluster

SHOW_VERSION

Returns the version of SQream DB

STOP_STATEMENT

Stops a query (or statement) if it is currently running

Workload management

Command

Usage

SUBSCRIBE_SERVICE

Add a SQream DB worker to a service queue

UNSUBSCRIBE_SERVICE

Remove a SQream DB worker to a service queue

SHOW_SUBSCRIBED_INSTANCES

Return a list of service queues and workers

Access control commands

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_STATEMENT_PERMISSIONS

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

GRANT

Grant permissions to a role

REVOKE

Revoke permissions from a role

RENAME ROLE

Rename a role