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

CONVERT TO DELTA

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

Executes ConvertToDeltaCommand

DESCRIBE DETAIL

(DESC | DESCRIBE) DETAIL (path | table)

Executes DescribeDeltaDetailCommand

DESCRIBE HISTORY

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

Executes DescribeDeltaHistoryCommand

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)

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