Summary of Commands¶
Snowflake supports most of the commands and statements defined in SQL:1999.
DDL (Data Definition Language) Commands¶
Account-based and Session-based DDL¶
Commands |
Notes |
---|---|
ALTER ACCOUNT |
Used to set account-level parameters (only by users with ACCOUNTADMIN role). |
ALTER SESSION |
Used to set parameters in the current session. |
SHOW FUNCTIONS |
Displays system-defined functions and user-defined functions, including external functions. |
SHOW PARAMETERS |
Used to view parameters settings for the account or current session. Also can be used to view parameter settings for objects that have parameters. |
SHOW VARIABLES |
Used to view SQL variables in the current session. |
SET , UNSET |
Used to set and unset SQL variables in the current session. |
USE { DATABASE | ROLE | SCHEMA | WAREHOUSE } |
The USE command sets the database, role, schema, or warehouse for the current session. |
Organization Object DDL¶
Commands |
Notes |
---|---|
{ CREATE | DROP | UNDROP } ACCOUNT |
Executed by users with the ORGADMIN role. ALTER ACCOUNT is used to set account-level parameters, and is executed by a user with the ACCOUNTADMIN role. |
Account Object DDL¶
Commands |
Notes |
---|---|
{ ALTER | CREATE | DESC | DROP } APPLICATION |
|
{ ALTER | CREATE | DROP } APPLICATION PACKAGE |
|
{ ALTER | CREATE | DROP } CONNECTION |
|
{ ALTER | CREATE | DESC | DROP | UNDROP | USE } DATABASE |
CREATE also supports cloning existing databases. |
{ ALTER | CREATE } { API | EXTERNAL ACCESS | NOTIFICATION | SECURITY | STORAGE } INTEGRATION |
|
{ DESC | DROP } [ API | EXTERNAL ACCESS | NOTIFICATION | SECURITY | STORAGE ] INTEGRATION |
|
{ ALTER | CREATE | DESC | DROP } NETWORK POLICY |
|
{ ALTER | CREATE | DESC | DROP } RESOURCE MONITOR |
|
{ ALTER | CREATE | DESC | DROP | USE } ROLE |
|
{ ALTER | CREATE | DESC | DROP } SHARE |
|
{ ALTER | CREATE | DESC | DROP } USER |
|
{ ALTER | CREATE | DESC | DROP | USE } WAREHOUSE |
|
COMMENT |
This command can be used with any account object; also, comments be set using CREATE or ALTER. |
{ GRANT | REVOKE } PRIVILEGE |
Privileges are not strictly a first-class object; however, these commands are used for enabling and managing access control for all first-class objects in Snowflake. |
SHOW { APPLICATION PACKAGES | APPLICATIONS | DATABASES | [ API | EXTERNAL ACCESS | NOTIFICATION | SECURITY | STORAGE ] INTEGRATIONS | NETWORK POLICIES | PRIVILEGES | RESOURCE MONITORS | ROLES | SHARES | USERS | WAREHOUSES } |
Database / Schema Object DDL¶
Commands |
Notes |
---|---|
{ ALTER | CREATE | DESC | DROP } ALERT |
Used for creating Snowflake alerts. |
{ ALTER | CREATE | DESC | DROP } EVENT TABLE |
Used for creating and managing event tables. |
CREATE EXTERNAL FUNCTION |
Used for creating external functions. |
{ ALTER | CREATE | DESC | DROP } FILE FORMAT |
CREATE also supports cloning existing file formats. |
{ ALTER | CREATE | DESC | DROP } FUNCTION |
Used for creating UDFs (user-defined functions) and managing UDFs and external functions. |
{ ALTER | CREATE | DESC | DROP } MASKING POLICY |
Used for creating and managing masking policies. |
{ ALTER | CREATE | DESC | DROP | UNDROP } MATERIALIZED VIEW |
Used for creating and managing materialized views. |
{ ALTER | CREATE | DESC | DROP } NETWORK RULE |
|
{ ALTER | CREATE | DESC | DROP } PASSWORD POLICY |
Used for creating and managing password policies. |
{ ALTER | CREATE | DESC | DROP } PIPE |
|
{ ALTER | CREATE | DESC | DROP } PROCEDURE |
Used for creating and managing stored procedures. |
{ ALTER | CREATE | DESC | DROP } ROW ACCESS POLICY |
Used for creating and managing row access policies. |
{ ALTER | CREATE | DESC | DROP } SECRET |
Used for creating and managing secrets. |
{ ALTER | CREATE | DESC | DROP } SEQUENCE |
CREATE also supports cloning existing sequences. |
{ ALTER | CREATE | DESC | DROP } SESSION POLICY |
Used for creating and managing session policies. |
{ ALTER | CREATE | DESC | DROP } STAGE |
CREATE also supports cloning existing named stages (external only). |
{ ALTER | CREATE | DESC | DROP } STREAM |
CREATE also supports cloning existing named streams. |
{ ALTER | CREATE | DESC | DROP | UNDROP | USE } SCHEMA |
CREATE also supports cloning existing schemas. |
{ ALTER | CREATE | DESC | DROP | TRUNCATE | UNDROP } TABLE |
CREATE also supports cloning existing tables, creating populated tables (using CTAS), and creating empty tables (from existing tables). |
{ ALTER | CREATE | DROP | UNDROP } TAG |
DESCRIBE is not supported for the tag object. |
{ ALTER | CREATE | DESC | DROP } TASK |
|
{ ALTER | CREATE | DESC | DROP | UNDROP } VIEW |
|
COMMENT |
This command can be used with any database/schema object; also, comments be set using CREATE or ALTER. |
SHOW { ALERTS | EVENT TABLES | EXTERNAL FUNCTIONS | FILE FORMATS | MASKING POLICIES | MATERIALIZED VIEWS | NETWORK RULES | PASSWORD POLICIES | PIPES | PROCEDURES | ROW ACCESS POLICIES | SCHEMAS | SECRETS | SEQUENCES | SESSION POLICIES | STAGES | STREAMS | TABLES | TAGS | TASKS | USER FUNCTIONS | VIEWS } |
DML (Data Manipulation Language) Commands¶
Commands |
Notes |
---|---|
DELETE , INSERT , MERGE , REPLACE , UPDATE |
Used for general DML. INSERT includes support for multi-table inserts. |
COPY INTO { <table> | <location> } |
Used for loading/unloading data (i.e. bulk import/export). |
GET , LIST , PUT , REMOVE |
These commands do not perform DML; they are used to stage and manage files for data loading and unloading. |
Query Syntax & Operators¶
Category |
Constructs/Operators |
Notes |
---|---|---|
Query Syntax |
||
[ WITH ] |
||
SELECT [ DISTINCT ] |
Required. |
|
[ INTO ] |
For setting Snowflake Scripting variables to values in result columns. |
|
[ FROM ] |
||
[ AT | BEFORE ]
|
For querying using Time Travel. |
|
[ CHANGES ]
|
For querying the change tracking metadata for a table. |
|
[ CONNECT BY ]
|
For joining a table to itself to process hierarchical data in the table. |
|
[ JOIN ]
|
Support for inner, left outer, right outer, and full joins. |
|
[ MATCH_RECOGNIZE ]
|
For finding sequences of rows that match a pattern. |
|
[ PIVOT | UNPIVOT ]
|
||
[ VALUES ]
|
||
[ SAMPLE ] / [ TABLESAMPLE ]
|
TABLESAMPLE is a synonym for SAMPLE. |
|
[ WHERE ] |
||
[ GROUP BY ] |
Columns used in GROUP BY must be in SELECT list. |
|
[ HAVING ]
|
||
[ ORDER BY ] |
||
[ LIMIT ] |
||
Arithmetic Operators |
||
|
||
Comparison Operators |
||
|
||
Logical/Boolean Operators |
||
AND , NOT , OR |
||
Set Operators |
||
INTERSECT [ ALL ] , MINUS [ ALL ] / EXCEPT , UNION [ ALL ] |
||
Subquery Operators |
||
ANY / ALL , [ NOT ] EXISTS , [ NOT ] IN |
||
Predicates |
||
BETWEEN , [ NOT ] EXISTS , [ NOT ] IN , LIKE |
TCL (Transaction Control Language) Commands¶
Commands |
Notes |
---|---|
BEGIN , COMMIT , ROLLBACK |
Used for multi-statement transactions in the current session. |
DESC TRANSACTIONS |
|
SHOW { LOCKS | TRANSACTIONS } |