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

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

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