SQL reference for Databricks Runtime 7.x and above

This is a SQL command reference for users on clusters running Databricks Runtime 7.x and above in the Databricks Data Science & Engineering workspace and Databricks Machine Learning environment.

Note

• For Databricks Runtime 5.5 LTS and 6.x SQL commands, see SQL reference for Databricks Runtime 5.5 LTS and 6.x.
• For the Databricks Runtime language reference, see SQL reference for Databricks SQL.

General reference

This general reference for Databricks SQL describes data types, functions, identifiers, literals, and semantics:

• Data types
• SQL data type rules
• Datetime patterns
• Functions
• Lambda functions
• Window functions
• Identifiers
• Literals
• Names
• NULL semantics
• JSON path expressions
• ANSI compliance
• Apache Hive compatibility
• Partitions
• Principals
• Privileges and securable objects
• Reserved words

DDL statements

You use data definition statements to create or modify the structure of database objects in a database:

• ALTER DATABASE
• ALTER TABLE
• ALTER VIEW
• COMMENT ON
• CREATE DATABASE
• CREATE FUNCTION (External)
• CREATE FUNCTION (SQL)
• CREATE TABLE
• CREATE VIEW
• DROP DATABASE
• DROP FUNCTION
• DROP TABLE
• DROP VIEW
• MSCK REPAIR TABLE
• TRUNCATE TABLE
• USE DATABASE

DML statements

You use data manipulation statements to add, change, or delete data:

• INSERT INTO
• INSERT OVERWRITE DIRECTORY
• INSERT OVERWRITE DIRECTORY with Hive format
• LOAD DATA

Data retrieval statements

You use the SELECT statement to retrieve rows from one or more tables according to the specified clauses. The full syntax and brief description of supported clauses are explained in SELECT. The SQL statements related to SELECT are also included in this section.

Spark also provides the ability to generate logical and physical plan for a query using the EXPLAIN statement.

• SELECT
• EXPLAIN

Delta Lake statements

You use Delta Lake SQL statements to manage tables stored in Delta Lake format:

• CACHE SELECT
• CREATE TABLE CLONE
• CONVERT TO DELTA
• COPY INTO
• CREATE BLOOM FILTER INDEX (Delta Lake on Databricks)
• DELETE FROM
• DESCRIBE HISTORY (Delta Lake on Databricks)
• DROP BLOOM FILTER INDEX (Delta Lake on Databricks)
• FSCK REPAIR TABLE
• GENERATE
• MERGE INTO
• OPTIMIZE (Delta Lake on Databricks)
• RESTORE (Delta Lake on Databricks)
• UPDATE
• VACUUM

For details on using Delta Lake statements, see Delta Lake and Delta Engine guide.

Auxiliary statements

You use auxiliary statements to collect statistics, manage caching for Apache Spark cache, explore metadata, set configurations, and manage resources:

Analyze statement

• ANALYZE TABLE

Apache Spark Cache statements

• CACHE TABLE
• CLEAR CACHE
• REFRESH
• REFRESH FUNCTION
• REFRESH TABLE
• UNCACHE TABLE

Describe statements

• DESCRIBE DATABASE
• DESCRIBE FUNCTION
• DESCRIBE QUERY
• DESCRIBE TABLE

Show statements

• SHOW COLUMNS
• SHOW CREATE TABLE
• SHOW DATABASES
• SHOW FUNCTIONS
• SHOW GROUPS
• SHOW PARTITIONS
• SHOW TABLE EXTENDED
• SHOW TABLES
• SHOW TBLPROPERTIES
• SHOW USERS
• SHOW VIEWS

Configuration management

• RESET
• SET
• SET TIME ZONE

Resource management

• ADD ARCHIVE
• ADD FILE
• ADD JAR
• LIST ARCHIVE
• LIST FILE
• LIST JAR

Security statements

You use security SQL statements to manage access to data:

• ALTER GROUP
• CREATE GROUP
• DENY
• DROP GROUP
• GRANT
• MSCK REPAIR PRIVILEGES
• REVOKE
• SHOW GRANT

For details using these statements, see Data object privileges.