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