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
DROP SAVED QUERY	Drops a saved query
DUMP DATABASE DDL	View the CREATE TABLE statement for an current database
EXECUTE SAVED QUERY	Executes a previously saved query
EXPLAIN	Returns a static query plan, which can be used to debug query plans
EXPORT OPEN SNAPSHOTS	Lists and saves information about all currently open snapshots to a specified file
GET	Transfer data files stored within the database’s internal staging area to a user’s local file system.
GET CHUNK INFO	Retrieves information of specific chunks
GET DDL	View the CREATE TABLE statement for a table
GET EXTENT INFO	Retrieves information of specific extents
GET FUNCTION DDL	View the CREATE FUNCTION statement for a UDF
GET LICENSE INFO	View a user’s license information
GLOBAL GRACEFUL SHUTDOWN	Graceful shutdown of all servers in the cluster
GPU METRICS	Monitor license quota usage by reviewing monthly or daily GPU usage
GET_OPEN_SNAPSHOTS	Lists information about all currently open snapshots
GET TOTAL CHUNKS SIZE	Returns the total size of all data chunks saved in the system
GET VIEW DDL	View the CREATE VIEW statement for a view
HEALTH CHECK MONITORING	Returns system health monitoring logs
LDAP GET ATTR	Enables you to specify the LDAP attributes you want the SQreamDB role catalog table to show
LIST SAVED QUERIES	Lists previously saved query names, one per row
PUT	Transfer data files stored within a user’s local file system to the database’s internal staging area.
RECHUNK	Enables you to merge small data chunks into larger ones
RECOMPILE SAVED QUERY	Recompiles a saved query that has been invalidated due to a schema change
RECOMPILE VIEW	Recreate a view after schema changes
REMOVE	Delete data files stored within the database’s internal staging area.
REMOVE LOCK	Clears locks
REMOVE STATEMENT LOCKS	Clears all locks in the system
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
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
SHOW VERSION	Returns the system version for SQream DB
SWAP TABLE NAMES	Swaps the names of two tables contained within a schema

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