Skip to content

Delta SQL

Delta Lake registers custom SQL statements (using DeltaSparkSessionExtension that injects DeltaSqlParser with DeltaSqlAstBuilder).

The SQL statements support table identifiers of the format delta.`path` (with backticks), e.g. delta.`/tmp/delta/t1` while path is between single quotes, e.g. '/tmp/delta/t1'.

The SQL statements can also refer to tables that are registered in a catalog (metastore).

ALTER TABLE ADD CONSTRAINT

ALTER TABLE table
ADD CONSTRAINT name
CHECK (expr+)

Creates an AlterTableAddConstraint

ALTER TABLE DROP CONSTRAINT

ALTER TABLE table
DROP CONSTRAINT (IF EXISTS)? name

Creates a AlterTableDropConstraint

CLONE

cloneTableHeader SHALLOW CLONE source temporalClause?
  (TBLPROPERTIES tableProps)?
  (LOCATION location)?

cloneTableHeader
    : createTableHeader
    | replaceTableHeader
    ;

createTableHeader
    : CREATE TABLE (IF NOT EXISTS)? table
    ;

replaceTableHeader
    : (CREATE OR)? REPLACE TABLE table
    ;

temporalClause
    : FOR? (SYSTEM_VERSION | VERSION) AS OF version=(INTEGER_VALUE | STRING)
    | FOR? (SYSTEM_TIME | TIMESTAMP) AS OF timestamp=STRING
    ;

Creates a CloneTableStatement

CONVERT TO DELTA

CONVERT TO DELTA table
  (NO STATISTICS)?
  (PARTITIONED BY (colTypeList))?

Creates a ConvertToDeltaCommand

DESCRIBE DETAIL

(DESC | DESCRIBE) DETAIL (path | table)

Executes DescribeDeltaDetailCommand

DESCRIBE HISTORY

(DESC | DESCRIBE) HISTORY (path | table)
  (LIMIT limit)?

Creates a DescribeDeltaHistory

GENERATE

GENERATE modeName FOR TABLE table

Executes DeltaGenerateCommand

OPTIMIZE

OPTIMIZE (path | table)
  (WHERE partitionPredicate)?
  (zorderSpec)?

zorderSpec
    : ZORDER BY '(' interleave (, interleave)* ')'
    | ZORDER BY interleave (, interleave)*
    ;

Executes OptimizeTableCommand on a delta table (identified by a directory path or a table name)

Parsed by DeltaSqlAstBuilder that creates an OptimizeTableCommand

RESTORE

RESTORE TABLE? table
TO? temporalClause

temporalClause
    : FOR? (SYSTEM_VERSION | VERSION) AS OF version
    | FOR? (SYSTEM_TIME | TIMESTAMP) AS OF timestamp
    ;

Creates a RestoreTableStatement

VACUUM

VACUUM (path | table)
  (RETAIN number HOURS)? (DRY RUN)?

Executes VacuumTableCommand