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