SQL Statements

The SQL Statements page describes the following commands:

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

CLUSTER BY

Change clustering keys in 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 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

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

EXECUTE SAVED QUERY

Executes a previously saved query

EXPLAIN

Returns a static query plan, which can be used to debug query plans

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

Returns a list of active sessions across the cluster

SHOW VERSION

Returns the system version for SQream DB

STOP STATEMENT

Stops or aborts an active statement

Workload Management

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

The following table shows the 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_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_ROLE_DATABASE_DDL

Returns the definition of a database role in DDL format

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