Sail SQL reference

Every Spark SQL statement and built-in function Databasin Sail supports, by category, with the gaps and differences from Apache Spark.

Last updated October 12, 2026

Reading time 9 min read

Databasin Sail speaks Spark SQL: the same dialect Apache Spark and Databricks use. This page lists what works today: statements first, then every supported built-in function by category. If you're coming from Trino, the AI SQL Assistant in the editor knows both dialects and can translate for you.

The short version

Literals, all Spark data types, operators, CASE, joins, window functions, CTEs, set operations and about 500 built-in functions work as they do in Spark. The gaps are a handful of clauses (PIVOT, hints, DISTRIBUTE BY) and JVM-only features.

Queries

Supported in SELECT: FROM, WHERE, GROUP BY (including ROLLUP, CUBE and GROUPING SETS), HAVING, ORDER BY, LIMIT, OFFSET, joins, UNION / INTERSECT / EXCEPT, WITH (common table expressions), VALUES, window functions with OVER, LATERAL VIEW, TABLESAMPLE, and EXPLAIN (which shows Sail's own plan).

Not yet supported: query hints (/*+ … */), CLUSTER BY / DISTRIBUTE BY / SORT BY, PIVOT / UNPIVOT, LATERAL subqueries, TRANSFORM, DESCRIBE QUERY, and lateral column aliases (referring to a column alias later in the same SELECT list).

Writing data

Statement	On Databasin Sail
`INSERT INTO` / `INSERT OVERWRITE`	✅
`CREATE TABLE … USING iceberg` / `CREATE TABLE … AS SELECT`	✅
`CREATE OR REPLACE TABLE … AS SELECT`	✅
`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`	✅ Where your access rules allow it
`INSERT OVERWRITE DIRECTORY`, reading a file path directly	❌ Path-based access is blocked: use catalog tables
`LOAD DATA`, `COPY INTO`	❌

Pipeline catalogs (bronze, silver, gold, staging) are read-only on Sail; write to sandbox or a catalog of your own.

Tables, views and metadata

Statement	On Databasin Sail
`SHOW CATALOGS` / `SCHEMAS` / `TABLES` / `VIEWS` / `COLUMNS`	✅ Filtered by your access rules
`DESCRIBE TABLE`	✅
`<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
`SHOW CREATE TABLE`, `SHOW TBLPROPERTIES`	✅ Iceberg tables
Iceberg metadata tables (`t.snapshots`, `t.files`, …)	✅
`CREATE TEMPORARY VIEW`	✅ Lives for your session
`CREATE VIEW` (in a lake catalog), global temporary views	❌ Use a temporary view or `CREATE TABLE … AS SELECT`

| CREATE FUNCTION, `SHOW #### Aggregate

any, any_value, approx_count_distinct, approx_percentile, array_agg, avg, bit_and, bit_or, bit_xor, bitmap_and_agg, bitmap_construct_agg, bitmap_or_agg, bool_and, bool_or, collect_list, corr, count, count_distinct, count_if, count_min_sketch, covar_pop, covar_samp, every, first, first_value, grouping, grouping_id, histogram_numeric, hll_sketch_agg, hll_union_agg, kurtosis, last, last_value, listagg, listagg_distinct, max, max_by, mean, median, min, min_by, mode, percentile, percentile_approx, percentile_cont, percentile_disc, product, regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy, skewness, some, std, stddev, stddev_pop, stddev_samp, string_agg, string_agg_distinct, sum, sum_distinct, theta_intersection_agg, theta_sketch_agg, theta_union_agg, try_avg, try_sum, var_pop, var_samp, variance

Partly supported: collect_set.

Window

cume_dist, dense_rank, lag, lead, nth_value, ntile, percent_rank, rank, row_number

Generators (explode and friends)

explode, explode_outer, inline, inline_outer, posexplode, posexplode_outer, stack, variant_explode, variant_explode_outer

String

ascii, base64, bit_length, btrim, char, char_length, character_length, chr, concat_ws, contains, decode, elt, encode, endswith, find_in_set, format_number, format_string, initcap, instr, is_valid_utf8, lcase, left, len, length, levenshtein, locate, lower, lpad, ltrim, luhn_check, make_valid_utf8, mask, octet_length, overlay, position, printf, quote, randstr, regexp_count, regexp_extract, regexp_extract_all, regexp_instr, regexp_replace, regexp_substr, repeat, replace, right, rpad, rtrim, sentences, soundex, space, split, split_part, startswith, strpos, substr, substring, substring_index, to_binary, to_char, to_number, to_varchar, translate, trim, try_to_binary, try_to_number, try_validate_utf8, ucase, unbase64, upper, validate_utf8

Date and time

add_days, add_months, add_years, convert_timezone, curdate, current_date, current_timestamp, current_timezone, date_add, date_diff, date_format, date_from_unix_date, date_part, date_sub, date_trunc, dateadd, datediff, datepart, day, dayname, dayofmonth, dayofweek, dayofyear, extract, from_unixtime, from_utc_timestamp, hour, last_day, localtimestamp, make_date, make_dt_interval, make_interval, make_time, make_timestamp, make_timestamp_ltz, make_timestamp_ntz, make_ym_interval, minute, month, monthname, months_between, next_day, now, quarter, second, time_diff, time_trunc, timestamp_add, timestamp_diff, timestamp_micros, timestamp_millis, timestamp_seconds, to_date, to_timestamp, to_timestamp_ltz, to_timestamp_ntz, to_unix_timestamp, to_utc_timestamp, trunc, try_make_interval, try_make_timestamp, try_make_timestamp_ltz, try_make_timestamp_ntz, try_to_timestamp, unix_date, unix_micros, unix_millis, unix_seconds, unix_timestamp, weekday, weekofyear, window, window_time, year

Math

%, *, +, -, /, abs, acos, acosh, asin, asinh, atan, atan2, atanh, bin, bround, cbrt, ceil, ceiling, conv, cos, cosh, cot, csc, degrees, div, e, exp, expm1, factorial, floor, greatest, hex, hypot, least, ln, log, log10, log1p, log2, mod, negate, negative, pi, pmod, positive, pow, power, radians, rand, randn, random, random_poisson, rint, round, sec, sign, signum, sin, sinh, sqrt, tan, tanh, try_add, try_divide, try_mod, try_multiply, try_subtract, unhex, uniform, width_bucket

Array

array, array_append, array_compact, array_contains, array_distinct, array_except, array_insert, array_intersect, array_max, array_min, array_position, array_prepend, array_remove, array_repeat, array_size, array_union, arrays_overlap, arrays_zip, flatten, get, slice, sort_array

Partly supported: array_join, sequence.

Map

create_map, map, map_concat, map_contains_key, map_entries, map_from_arrays, map_from_entries, map_keys, map_values, str_to_map

Struct

named_struct, struct

Higher-order (lambda)

aggregate, array_sort, cardinality, concat, element_at, exists, filter, forall, map_filter, reduce, reverse, size, transform, transform_keys, transform_values, try_element_at, zip_with

JSON

from_json, get_json_object, json_array_length, json_object_keys, json_tuple, schema_of_json, to_json

Variant

is_variant_null, parse_json, schema_of_variant, schema_of_variant_agg, to_variant_object, try_parse_json, try_variant_get, variant_get

XML

from_xml, to_xml, xpath, xpath_boolean, xpath_double, xpath_float, xpath_int, xpath_long, xpath_number, xpath_short, xpath_string

CSV

from_csv, schema_of_csv, to_csv

URL

parse_url, try_parse_url, try_url_decode, url_decode, url_encode

Hash

crc32, hash, md5, sha, sha1, sha2, xxhash64

Conditional

coalesce, ifnull, nanvl, nullif, nullifzero, nvl, nvl2, when, zeroifnull

Predicate and comparison

!, !=, <, <=, <=>, =, ==, >, >=, and, equal_null, ilike, in, isnan, isnotnull, isnull, like, not, or, regexp, regexp_like, rlike

Bitwise

&, <<, >>, >>>, ^, bit_count, bit_get, bitwise_not, getbit, shiftleft, shiftright, shiftrightunsigned, |, ~

Conversion and casting

bigint, binary, boolean, cast, date, decimal, double, float, int, smallint, string, timestamp, tinyint

Geospatial

st_asbinary, st_geogfromwkb, st_geomfromwkb

Miscellaneous

aes_decrypt, aes_encrypt, assert_true, bitmap_bit_position, bitmap_bucket_number, bitmap_count, current_catalog, current_database, current_schema, current_user, hll_sketch_estimate, hll_union, monotonically_increasing_id, raise_error, session_user, spark_partition_id, theta_difference, theta_intersection, theta_sketch_estimate, theta_union, try_aes_decrypt, typeof, user, uuid, version``, SHOW PARTITIONS, ANALYZE TABLE, CACHE TABLE` | ❌ |

Table maintenance (Iceberg)

Statement	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.

Session settings

SET TIME ZONE …, SET timezone = … and SET spark.<option> = … change your session. Settings Databasin manages (catalogs, storage, security) are refused.

Python (PySpark)

Notebook Python cells and Spark Connect clients get the PySpark 4.2 API: DataFrames, spark.sql(…), pyspark.sql.functions and Window. On a shared cluster, user-defined Python functions (Python UDFs, pandas UDFs, Python data sources) are blocked; a single user cluster allows them. Not available: the RDD API, Structured Streaming, MLlib and Scala.

Built-in functions

Every function below works in Spark SQL, and nearly all of them through pyspark.sql.functions too. The list comes from LakeSail's compatibility data for the Sail version Databasin runs, plus functions Databasin added (zip_with, transform_keys, transform_values, map_filter, try_to_date, try_make_interval, Oracle-style decode, and three-argument max_by / min_by).

Aggregate