Sail SQL reference
The Spark SQL statements Databasin Sail supports, from MERGE and views to Iceberg and Delta maintenance, with an index of every built-in function by category.
Databasin Sail speaks Spark SQL, the dialect Apache Spark and Databricks use. Statements follow Spark 4.0; newer functions are checked against Spark 4.2. This page covers the statements Sail supports and links to every built-in function, grouped by category.
Spark compatibility lists where Sail behaves differently from Apache Spark.
Queries
Supported in SELECT: FROM, WHERE, GROUP BY (including ROLLUP, CUBE, GROUPING SETS and GROUP BY ALL), HAVING, ORDER BY (including ORDER BY ALL), LIMIT, OFFSET, joins, UNION / INTERSECT / EXCEPT, WITH (common table expressions), VALUES, window functions with OVER, PIVOT, UNPIVOT, LATERAL VIEW, LATERAL subqueries, query hints (/*+ … */), TABLESAMPLE, CLUSTER BY / DISTRIBUTE BY / SORT BY, lateral column aliases, DESCRIBE QUERY, and EXPLAIN (which shows Sail's own plan, not Spark's).
Not yet supported: TRANSFORM (Hive scripts) and pipe syntax.
Writing data
| Statement | On Databasin Sail |
|---|---|
INSERT INTO / INSERT OVERWRITE |
✅ Including column lists, static partitions and dynamic partition overwrite |
CREATE TABLE / CREATE TABLE … AS SELECT |
✅ Takes the catalog's format when USING is left out; a different format is refused |
CREATE OR REPLACE TABLE, CREATE TABLE LIKE |
✅ |
MERGE INTO |
✅ Reports rows changed |
UPDATE |
✅ Reports rows changed; small delete files on Iceberg v2 tables |
DELETE FROM |
✅ Reports rows changed |
TRUNCATE TABLE |
✅ Iceberg tables |
DROP TABLE [PURGE] |
✅ Where your access rules allow it |
INSERT OVERWRITE DIRECTORY, reading a file path directly |
❌ Path-based access is blocked: use catalog tables (or /Files in notebooks) |
LOAD DATA |
❌ As in Spark, not for Iceberg or Delta tables |
Writes follow your access rules in every catalog, the pipeline catalogs (bronze, silver, gold, staging) included. By default only admins can write to pipeline catalogs; use sandbox for your own tables. Live catalogs are read-only.
Tables, views and metadata
| Statement | On Databasin Sail |
|---|---|
SHOW CATALOGS / SCHEMAS / TABLES / VIEWS / COLUMNS |
✅ Filtered by your access rules |
DESCRIBE TABLE, DESCRIBE QUERY, DESCRIBE a column |
✅ |
<catalog>.information_schema.* |
✅ schemata, tables, columns, views |
USE catalog.schema, USE catalog |
✅ |
ALTER TABLE |
✅ Iceberg: columns (add, drop, rename, widen type, comment, position), rename table, partition fields, WRITE ORDERED BY, identifier fields |
COMMENT ON TABLE |
✅ |
SHOW CREATE TABLE, SHOW TBLPROPERTIES |
✅ Iceberg tables |
Iceberg metadata tables (t.snapshots, t.files, …) |
✅ |
CREATE TEMPORARY VIEW |
✅ Lives for your session |
CREATE VIEW, ALTER VIEW … AS, DROP VIEW (lake catalogs) |
✅ Persistent views; a view runs with the reader's access, and views created on Sail run on Sail only |
CREATE GLOBAL TEMPORARY VIEW |
✅ Visible only to you, not to other users |
CREATE TEMPORARY FUNCTION (SQL) |
✅ Lives for your session |
CACHE TABLE, UNCACHE TABLE, REFRESH |
✅ Answered as Spark does; no data is kept in memory |
CREATE FUNCTION (persistent), SHOW PARTITIONS, ANALYZE TABLE |
❌ (ANALYZE TABLE isn't available for Iceberg or Delta tables in Spark either) |
Table maintenance
| Statement (Iceberg) | What it does |
|---|---|
OPTIMIZE t [WHERE …] |
Compacts small files on the cluster's workers, in the table's sort order. |
VACUUM t [RETAIN n HOURS] [DRY RUN] |
Removes unreferenced files. Keeps 7 days by default, never under 24 hours. |
CALL <catalog>.system.expire_snapshots(…) |
Expires old snapshots (follows the table's properties; 5 days when unset). |
CALL <catalog>.system.remove_orphan_files(…) |
Removes files no snapshot references (never newer than 24 hours). |
CALL <catalog>.system.rewrite_manifests(…) |
Rewrites manifests for faster planning. |
CALL <catalog>.system.rewrite_data_files(…) |
Compacts data files. |
CALL <catalog>.system.rollback_to_snapshot(…), rollback_to_timestamp(…), set_current_snapshot(…) |
Moves the table back to an earlier state. |
On Delta tables: OPTIMIZE (bin-packing), VACUUM, RESTORE, DESCRIBE HISTORY, DESCRIBE DETAIL, SHOW TBLPROPERTIES and ALTER TABLE for columns.
Session settings
SET TIME ZONE …, SET timezone = …, SET spark.<option> = …, RESET, ${var} substitution, session variables and EXECUTE IMMEDIATE work as in Spark. Settings Databasin manages (catalogs, storage, connection and engine runtime settings) are refused, and so are ${env:…} and ${system:…}.
Built-in functions
Spark 4.2 has 574 built-in functions. On Sail, 490 work as they do in Apache Spark, 23 work with a difference that's noted on the function, and 61 aren't supported. Each page lists every function in its category with its status, usage, and examples whose results on Sail match Spark's.
| Page | Functions | What's in it |
|---|---|---|
| Aggregate functions | 86 | count, sum, avg, percentiles, statistics, collect_list, sketches |
| Window, generator and table functions | 20 | row_number, rank, lag, lead, explode, stack, range |
| String functions | 79 | concat_ws, substring, regexp_replace, split, format_string, URLs |
| Date and time functions | 82 | date_trunc, date_add, to_timestamp, date_format, time zones |
| Math and bitwise functions | 81 | round, ceil, log, pow, try_divide, bit operations |
| Array, map and struct functions | 61 | array_contains, transform, filter, map_keys, named_struct |
| JSON, XML, CSV and Variant functions | 38 | from_json, get_json_object, to_json, xpath, parse_json |
| Conditional, comparison and conversion functions | 49 | coalesce, CASE, nvl, like, rlike, cast |
| Hash, sketch, geospatial and other functions | 78 | md5, sha2, uuid, current_user, HLL and theta sketches |
Supported functions also work from PySpark through pyspark.sql.functions, wherever PySpark has a wrapper for them.
Not supported
- Calling Java methods:
java_method,reflect,try_reflect. - Avro and Protobuf functions:
from_avro,to_avro,schema_of_avro,from_protobuf,to_protobuf. Avro files can still be read. - KLL, tuple and
approx_top_ksketches (added in Spark 4.1 and 4.2). HLL and theta sketches work. - Other newer functions:
measure(Spark 4.2), andst_srid,st_setsridandpython_worker_logs(Spark 4.1).
Collations: UTF8_BINARY and UTF8_LCASE work in comparisons, IN, LIKE, joins, ORDER BY and window functions. Grouping and set operations on UTF8_LCASE, and other (ICU) collations, aren't supported yet.
Function names, usage text and examples on the function pages are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation.