SQL reference

Lakehouse runtimeDownload PDF (5.6 MB)

Statements and the permission each needs#

Statement Permission
SELECT, WITH ... SELECT, time travel (VERSION AS OF, TIMESTAMP AS OF) Select on each table
SELECT from a metadata table (db.t.snapshots) Select on the base table
SHOW TABLES IN db, SHOW VIEWS IN db Select on the database
SHOW DATABASES, DESCRIBE DATABASE List on the warehouse
DESCRIBE [EXTENDED \| FORMATTED] db.t, SHOW TBLPROPERTIES, SHOW PARTITIONS, SHOW COLUMNS, SHOW CREATE TABLE, ANALYZE TABLE, REFRESH TABLE Select on the table
INSERT INTO Insert on the target, Select on the sources
INSERT OVERWRITE, UPDATE, DELETE, MERGE INTO, TRUNCATE TABLE Update on the target, Select on the sources
CREATE TABLE, CREATE TABLE ... AS SELECT, CREATE VIEW CreateTable on the database, Select on the sources
CREATE TEMPORARY VIEW Select on the sources
DROP TABLE, DROP VIEW DropTable
ALTER TABLE, COMMENT ON, REPLACE TABLE ... AS SELECT AlterTable, Select on the sources
CREATE DATABASE CreateDatabase on the warehouse
DROP DATABASE DropDatabase on the warehouse
ALTER DATABASE, SET key = value All on the warehouse
CALL system.<procedure>(...) AlterTable on the table (see below)

A permission named All at a level covers every action at that level. An explicit table-level deny always wins.

Queries#

sql
SELECT [DISTINCT] expr [AS alias], ...
FROM database.table [AS alias]
  [[INNER | LEFT | RIGHT | FULL | CROSS] JOIN database.table ON condition]
  [LEFT SEMI JOIN ... | LEFT ANTI JOIN ...]
[WHERE condition]
[GROUP BY expr, ... | GROUP BY ALL]
[HAVING condition]
[ORDER BY expr [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
[LIMIT n]

Prefix with WITH name AS (SELECT ...), ... for common table expressions. Set operators UNION [ALL], INTERSECT and EXCEPT combine queries. PIVOT, LATERAL VIEW explode(...), TABLESAMPLE and GROUPING SETS, ROLLUP and CUBE are supported:

sql
SELECT * FROM (
  SELECT channel, status, order_total FROM sales.orders
) PIVOT (SUM(order_total) FOR status IN ('COMPLETE', 'RETURNED'))

Time travel#

sql
SELECT ... FROM db.t TIMESTAMP AS OF '2026-09-30 12:00:00'
SELECT ... FROM db.t VERSION AS OF 4821763650912831907
SELECT ... FROM db.t VERSION AS OF 'tag_name'

VERSION AS OF takes a snapshot id or the name of a branch or tag. FOR SYSTEM_TIME AS OF and FOR SYSTEM_VERSION AS OF are not accepted by the Lakehouse; use the forms above.

Creating and changing tables#

sql
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] db.t (col type [COMMENT 'text'], ...)
  USING iceberg
  [PARTITIONED BY (col | years(col) | months(col) | days(col) | hours(col)
                   | bucket(n, col) | truncate(n, col), ...)]
  [COMMENT 'text']
  [TBLPROPERTIES ('key' = 'value', ...)]

CREATE TABLE db.t USING iceberg [PARTITIONED BY (...)] AS SELECT ...
REPLACE TABLE db.t USING iceberg AS SELECT ...

ALTER TABLE db.t ADD COLUMN col type [COMMENT 'text'] [FIRST | AFTER other]
ALTER TABLE db.t DROP COLUMN col
ALTER TABLE db.t RENAME COLUMN col TO new_name
ALTER TABLE db.t ALTER COLUMN col TYPE wider_type
ALTER TABLE db.t ALTER COLUMN col {SET | DROP} NOT NULL
ALTER TABLE db.t ADD | DROP PARTITION FIELD transform
ALTER TABLE db.t REPLACE PARTITION FIELD old_transform WITH new_transform
ALTER TABLE db.t WRITE ORDERED BY col [ASC | DESC], ...
ALTER TABLE db.t SET TBLPROPERTIES ('key' = 'value')
ALTER TABLE db.t UNSET TBLPROPERTIES ('key')
ALTER TABLE db.t RENAME TO db.new_name
COMMENT ON TABLE db.t IS 'text'
DROP TABLE [IF EXISTS] db.t

Changing data#

sql
INSERT INTO db.t [(col, ...)] VALUES (...), (...)
INSERT INTO db.t SELECT ...
INSERT OVERWRITE db.t SELECT ...
UPDATE db.t SET col = expr [, ...] [WHERE condition]
DELETE FROM db.t [WHERE condition]
TRUNCATE TABLE db.t

MERGE INTO db.target AS t USING (db.source | (SELECT ...)) AS s ON condition
  WHEN MATCHED [AND condition] THEN UPDATE SET col = expr [, ...] | UPDATE SET * | DELETE
  WHEN NOT MATCHED [AND condition] THEN INSERT (cols) VALUES (...) | INSERT *

Databases and information#

sql
CREATE DATABASE [IF NOT EXISTS] db
DROP DATABASE [IF EXISTS] db
SHOW DATABASES
SHOW TABLES IN db
DESCRIBE [EXTENDED | FORMATTED] db.t
SHOW CREATE TABLE db.t
SHOW TBLPROPERTIES db.t
SHOW PARTITIONS db.t
SHOW COLUMNS IN db.t

A database must be empty before you can drop it.

Table procedures#

Iceberg procedures run with CALL. Name arguments with =>.

Procedure Purpose
rewrite_data_files(table => 't') Compact small files; add strategy, sort_order or where
rewrite_manifests('db.t') Merge manifest files
expire_snapshots(table => 't', older_than => ts, retain_last => n) Delete old snapshots
remove_orphan_files(table => 't') Delete files that no snapshot references
rollback_to_snapshot('db.t', snapshot_id) Move the table back to a snapshot
rollback_to_timestamp('db.t', ts) Move the table back to a time
set_current_snapshot('db.t', snapshot_id) Make a snapshot current
cherrypick_snapshot('db.t', snapshot_id) Apply one snapshot's changes
ancestors_of('db.t') List a snapshot's ancestors (needs only Select)

Blocked or restricted statements#

What you ran What you see
USE database 'Use' keyword not allowed. Use fully qualified table names.
A table without its database, for example FROM orders Use fully qualified table names.
A statement that cannot be parsed for authorization Access denied: Unable to parse query for authorization.
A statement that names no table, such as SELECT 1 or EXPLAIN ... Access denied: Unable to determine operations for authorization. Query a table instead.
A database that does not exist Following namespaces specified in the query does not exist: followed by the names
DROP DATABASE audit The system audit database cannot be dropped.
Changing audit.logs Audit logs can only be queried. Modifications are not permitted.
Reading audit.logs without the administrator role Access to audit.logs table is restricted to Admin group members.
The word namespace as a separate word anywhere in the text No resource named 'namespace'.
Metadata tables of a table with data policies Metadata tables of <table> are …: the table has data policies … Query the table itself instead.
A table whose policy is Block raw reads Table access blocked by protected policy: <database>.<table>
A masked column in a filter, grouping, ordering or join Masked columns cannot be used in <clause> clauses

One statement per run

Send one statement at a time. When a text contains a SELECT, only the first statement is run and the rest is ignored, without an error.

Behaviour of SQL in the Lakehouse#

  • ANSI mode. An invalid cast, an arithmetic overflow or a division by zero fails the query. Use try_cast(x AS INT), try_divide(a, b) or a / nullif(b, 0) to get NULL instead.
  • Names are case-insensitive. Sales.Orders and sales.orders are the same table; Iceberg stores the names as written.
  • Overwrites are dynamic. INSERT OVERWRITE replaces only the partitions present in the new rows.
  • SET lasts for the session. A value set with SET key = value stays until the session stops.
  • Temporary views. CREATE TEMPORARY VIEW name AS SELECT ... creates a view that exists only until the session ends and can be queried without a database.
  • No keyboard shortcut to run. Use the Run query button.