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