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 SQL 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
• Data type precedence
• Datetime patterns
• Functions
• Lambda functions
• Window functions
• Identifiers
• Literals
• NULL semantics
• ANSI compliance

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
• 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
• INSERT INTO
• INSERT OVERWRITE DIRECTORY
• INSERT OVERWRITE DIRECTORY with Hive format
• INSERT OVERWRITE
• 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 (Delta Lake on Databricks)
• CLONE (Delta Lake on Databricks)
• CONVERT TO DELTA (Delta Lake on Databricks)
• COPY INTO (Delta Lake on Databricks)
• CREATE BLOOM FILTER INDEX (Delta Lake on Databricks)
• DELETE FROM (Delta Lake on Databricks)
• DESCRIBE HISTORY (Delta Lake on Databricks)
• DROP BLOOM FILTER INDEX (Delta Lake on Databricks)
• FSCK REPAIR TABLE (Delta Lake on Databricks)
• GENERATE (Delta Lake on Databricks)
• MERGE INTO (Delta Lake on Databricks)
• OPTIMIZE (Delta Lake on Databricks)
• RESTORE (Delta Lake on Databricks)
• UPDATE (Delta Lake on Databricks)
• 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, explore metadata, set configurations, and manage resources:

Analyze statement

• ANALYZE TABLE

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
• REVOKE
• SHOW GRANT

For details using these statements, see Data object privileges.