Hash, sketch, geospatial and other functions

Every Spark SQL hash, sketch, geospatial and other function on Databasin Sail: whether it is supported, how it differs from Apache Spark, and examples with results.

Last updated October 20, 2026

Reading time 20 min read

This page lists every Spark SQL hash, sketch, geospatial and other function: 35 work on Databasin Sail as in Apache Spark, 4 work with a difference noted below and 39 aren't supported. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

Hash

crc32 · hash · md5 · sha · sha1 · sha2 · xxhash64

crc32

Supported.

crc32(expr): Returns a cyclic redundancy check value of the expr as a bigint.

Since Spark 1.5.0.

SELECT crc32('Spark');
-- 1557323817

hash

Supported.

hash(expr1, expr2, ...): Returns a hash value of the arguments.

Since Spark 2.0.0.

SELECT hash('Spark', array(123), 2);
-- -1321691492

md5

Supported.

md5(expr): Returns an MD5 128-bit checksum as a hex string of expr.

Since Spark 1.5.0.

SELECT md5('Spark');
-- 8cde774d6f7333752ed72cacddb05126

sha

Supported.

sha(expr): Returns a sha1 hash value as a hex string of the expr.

Since Spark 1.5.0.

SELECT sha('Spark');
-- 85f5955f4b27a9a4c2aab6ffe5d7189fc298b92c

sha1

Supported.

sha1(expr): Returns a sha1 hash value as a hex string of the expr.

Since Spark 1.5.0.

SELECT sha1('Spark');
-- 85f5955f4b27a9a4c2aab6ffe5d7189fc298b92c

sha2

Supported.

sha2(expr, bitLength): Returns a checksum of SHA-2 family as a hex string of expr. SHA-224, SHA-256, SHA-384, and SHA-512 are supported. Bit length of 0 is equivalent to 256.

Since Spark 1.5.0.

SELECT sha2('Spark', 256);
-- 529bc3b07127ecb7e53a4dcf1991d9152c24537d919178022b2c42657f79a26b

xxhash64

Supported.

xxhash64(expr1, expr2, ...): Returns a 64-bit hash value of the arguments. Hash seed is 42.

Since Spark 3.0.0.

SELECT xxhash64('Spark', array(123), 2);
-- 5602566077635097486

Misc

aes_decrypt · aes_encrypt · assert_true · bitmap_bit_position · bitmap_bucket_number · bitmap_count · current_catalog · current_database · current_path · current_schema · current_user · input_file_block_length · input_file_block_start · input_file_name · java_method · monotonically_increasing_id · raise_error · reflect · session_user · spark_partition_id · try_aes_decrypt · try_reflect · typeof · user · uuid · version

aes_decrypt

Supported.

aes_decrypt(expr, key[, mode[, padding[, aad]]]): Returns a decrypted value of expr using AES in mode with padding. Key lengths of 16, 24 and 32 bits are supported. Supported combinations of (mode, padding) are ('ECB', 'PKCS'), ('GCM', 'NONE') and ('CBC', 'PKCS'). Optional additional authenticated data (AAD) is only supported for GCM. If provided for encryption, the identical AAD value must be provided for decryption. The default mode is GCM.

expr: The binary value to decrypt.
key: The passphrase to use to decrypt the data.
mode: Specifies which block cipher mode should be used to decrypt messages. Valid modes: ECB, GCM, CBC.
padding: Specifies how to pad messages whose length is not a multiple of the block size. Valid values: PKCS, NONE, DEFAULT. The DEFAULT padding means PKCS for ECB, NONE for GCM and PKCS for CBC.
aad: Optional additional authenticated data. Only supported for GCM mode. This can be any free-form input and must be provided for both encryption and decryption.

Since Spark 3.3.0.

SELECT aes_decrypt(unhex('83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94'), '0000111122223333');
-- Spark

SELECT aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
-- Spark SQL

SELECT aes_decrypt(unbase64('3lmwu+Mw0H3fi5NDvcu9lg=='), '1234567890abcdef', 'ECB', 'PKCS');
-- Spark SQL

SELECT aes_decrypt(unbase64('2NYmDCjgXTbbxGA3/SnJEfFC/JQ7olk2VQWReIAAFKo='), '1234567890abcdef', 'CBC');
-- Apache Spark

SELECT aes_decrypt(unbase64('AAAAAAAAAAAAAAAAAAAAAPSd4mWyMZ5mhvjiAPQJnfg='), 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT');
-- Spark

SELECT aes_decrypt(unbase64('AAAAAAAAAAAAAAAAQiYi+sTLm7KD9UcZ2nlRdYDe/PX4'), 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', 'This is an AAD mixed into the input');
-- Spark

aes_encrypt

Supported.

aes_encrypt(expr, key[, mode[, padding[, iv[, aad]]]]): Returns an encrypted value of expr using AES in given mode with the specified padding. Key lengths of 16, 24 and 32 bits are supported. Supported combinations of (mode, padding) are ('ECB', 'PKCS'), ('GCM', 'NONE') and ('CBC', 'PKCS'). Optional initialization vectors (IVs) are only supported for CBC and GCM modes. These must be 16 bytes for CBC and 12 bytes for GCM. If not provided, a random vector will be generated and prepended to the output. Optional additional authenticated data (AAD) is only supported for GCM. If provided for encryption, the identical AAD value must be provided for decryption. The default mode is GCM.

expr: The binary value to encrypt.
key: The passphrase to use to encrypt the data.
mode: Specifies which block cipher mode should be used to encrypt messages. Valid modes: ECB, GCM, CBC.
padding: Specifies how to pad messages whose length is not a multiple of the block size. Valid values: PKCS, NONE, DEFAULT. The DEFAULT padding means PKCS for ECB, NONE for GCM and PKCS for CBC.
iv: Optional initialization vector. Only supported for CBC and GCM modes. Valid values: None or ''. 16-byte array for CBC mode. 12-byte array for GCM mode.
aad: Optional additional authenticated data. Only supported for GCM mode. This can be any free-form input and must be provided for both encryption and decryption.

Since Spark 3.3.0.

SELECT hex(aes_encrypt('Spark', '0000111122223333'));
-- for example: 83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94

SELECT hex(aes_encrypt('Spark SQL', '0000111122223333', 'GCM'));
-- for example: 6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210

SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
-- for example: 3lmwu+Mw0H3fi5NDvcu9lg==

SELECT base64(aes_encrypt('Apache Spark', '1234567890abcdef', 'CBC', 'DEFAULT'));
-- for example: 2NYmDCjgXTbbxGA3/SnJEfFC/JQ7olk2VQWReIAAFKo=

SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT', unhex('00000000000000000000000000000000')));
-- for example: AAAAAAAAAAAAAAAAAAAAAPSd4mWyMZ5mhvjiAPQJnfg=

SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', unhex('000000000000000000000000'), 'This is an AAD mixed into the input'));
-- for example: AAAAAAAAAAAAAAAAQiYi+sTLm7KD9UcZ2nlRdYDe/PX4

assert_true

Supported.

assert_true(expr [, message]): Throws an exception if expr is not true.

Since Spark 2.0.0.

SELECT assert_true(0 < 1);
-- NULL

bitmap_bit_position

Supported.

bitmap_bit_position(child): Returns the bit position for the given input child expression.

Since Spark 3.5.0.

SELECT bitmap_bit_position(1);
-- 0

SELECT bitmap_bit_position(123);
-- 122

bitmap_bucket_number

Supported.

bitmap_bucket_number(child): Returns the bucket number for the given input child expression.

Since Spark 3.5.0.

SELECT bitmap_bucket_number(123);
-- 1

SELECT bitmap_bucket_number(0);
-- 0

bitmap_count

Supported.

bitmap_count(child): Returns the number of set bits in the child bitmap.

Since Spark 3.5.0.

SELECT bitmap_count(X '1010');
-- 2

SELECT bitmap_count(X 'FFFF');
-- 16

SELECT bitmap_count(X '0');
-- 0

current_catalog

Supported. Returns the session's current catalog (a Databasin catalog name, not spark_catalog).

current_catalog(): Returns the current catalog.

Since Spark 3.1.0.

SELECT current_catalog();
-- spark_catalog

current_database

Supported. Returns the session's current schema in the Databasin catalog.

current_database(): Returns the current database.

Since Spark 1.6.0.

SELECT current_database();
-- default

current_path

Supported. New in Spark 4.2.0. Returns the SQL path with the session's Databasin catalog in place of spark_catalog.

current_path(): Returns the current SQL path (qualified schema names).

SELECT current_path();
-- system.builtin,system.session,spark_catalog.default

current_schema

Supported. Returns the session's current schema in the Databasin catalog.

current_schema(): Returns the current database.

Since Spark 3.4.0.

SELECT current_schema();
-- default

current_user

Supported. Returns the signed-in Databasin user.

current_user(): user name of current execution context.

Since Spark 3.2.0.

SELECT current_user();
-- mockingjay

input_file_block_length

Supported. Works for file reads and Delta tables; not filled on Iceberg tables.

input_file_block_length(): Returns the length of the block being read, or -1 if not available.

Since Spark 2.2.0.

SELECT input_file_block_length();
-- -1

input_file_block_start

Supported. Works for file reads and Delta tables; not filled on Iceberg tables.

input_file_block_start(): Returns the start offset of the block being read, or -1 if not available.

Since Spark 2.2.0.

SELECT input_file_block_start();
-- -1

input_file_name

Supported. Works for file reads and Delta tables; on Iceberg tables only input_file_name is filled. /Files reads show file:///Files/... paths.

input_file_name(): Returns the name of the file being read, or empty string if not available.

Since Spark 1.5.0.

SELECT input_file_name();
--

java_method

Not supported. Calls Java methods by reflection, which Sail does not support.

java_method(class, method[, arg1[, arg2 ..]]): Calls a method with reflection.

Since Spark 2.0.0.

monotonically_increasing_id

Supported. Spark's formula; the partition count of range() can differ, so ids can differ from Spark's.

monotonically_increasing_id(): Returns monotonically increasing 64-bit integers. The generated ID is guaranteed to be monotonically increasing and unique, but not consecutive. The current implementation puts the partition ID in the upper 31 bits, and the lower 33 bits represent the record number within each partition. The assumption is that the data frame has less than 1 billion partitions, and each partition has less than 8 billion records. The function is non-deterministic because its result depends on partition IDs.

Since Spark 1.4.0.

SELECT monotonically_increasing_id();
-- 0

raise_error

Supported, differs from Spark. Raises the error with the given message, but as a plain AnalysisException without Spark's USER_RAISED_EXCEPTION error class / SQLSTATE P0001.

raise_error( expr ): Throws a USER_RAISED_EXCEPTION with expr as message.

Since Spark 3.1.0.

reflect

Not supported. Calls Java methods by reflection, which Sail does not support.

reflect(class, method[, arg1[, arg2 ..]]): Calls a method with reflection.

Since Spark 2.0.0.

session_user

Supported. Returns the signed-in Databasin user.

session_user(): user name of current execution context.

Since Spark 4.0.0.

SELECT session_user();
-- mockingjay

spark_partition_id

Supported.

spark_partition_id(): Returns the current partition id.

Since Spark 1.4.0.

SELECT spark_partition_id();
-- 0

try_aes_decrypt

Supported.

try_aes_decrypt(expr, key[, mode[, padding[, aad]]]): This is a special version of aes_decrypt that performs the same operation, but returns a NULL value instead of raising an error if the decryption cannot be performed.

Since Spark 3.5.0.

SELECT try_aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
-- Spark SQL

SELECT try_aes_decrypt(unhex('----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
-- NULL

try_reflect

Not supported. Calls Java methods by reflection, which Sail does not support.

try_reflect(class, method[, arg1[, arg2 ..]]): This is a special version of reflect that performs the same operation, but returns a NULL value instead of raising an error if the invoke method thrown exception.

Since Spark 4.0.0.

typeof

Supported.

typeof(expr): Return DDL-formatted type string for the data type of the input.

Since Spark 3.0.0.

SELECT typeof(1);
-- int

SELECT typeof(array(1));
-- array<int>

user

Supported. Returns the signed-in Databasin user.

user(): user name of current execution context.

Since Spark 3.4.0.

SELECT user();
-- mockingjay

uuid

Supported.

uuid(): Returns an universally unique identifier (UUID) string. The value is returned as a canonical UUID 36-character string.

Note from Spark: The function is non-deterministic.

Since Spark 2.3.0.

SELECT uuid();
-- for example: 46707d92-02f4-4817-8116-a4c3b23e6266

version

Supported, differs from Spark. Returns the Sail engine version (e.g. 0.7.1), not a Spark version string.

version(): Returns the Spark version. The string contains 2 fields, the first being a release version and the second being a git revision.

Since Spark 3.0.0.

SELECT version();
-- 3.1.0 a6d6ea3efedbad14d99c24143834cd4e2e52fb40

Sketch

approx_top_k_estimate

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

approx_top_k_estimate(state, k): Returns top k items with their frequency. k An optional INTEGER literal greater than 0. If k is not specified, it defaults to 5.

hll_sketch_estimate

Supported.

hll_sketch_estimate(expr): Returns the estimated number of unique values given the binary representation of a Datasketches HllSketch.

Since Spark 3.5.0.

SELECT hll_sketch_estimate(hll_sketch_agg(col)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
-- 3

hll_union

Supported.

hll_union(first, second, allowDifferentLgConfigK): Merges two binary representations of Datasketches HllSketch objects, using a Datasketches Union object. Set allowDifferentLgConfigK to true to allow unions of sketches with different lgConfigK values (defaults to false).

Since Spark 3.5.0.

SELECT hll_sketch_estimate(hll_union(hll_sketch_agg(col1), hll_sketch_agg(col2))) FROM VALUES (1, 4), (1, 4), (2, 5), (2, 5), (3, 6) tab(col1, col2);
-- 6

kll_sketch_get_n_bigint

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_n_bigint(expr): Returns the number of items collected in the sketch.

kll_sketch_get_n_double

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_n_double(expr): Returns the number of items collected in the sketch.

kll_sketch_get_n_float

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_n_float(expr): Returns the number of items collected in the sketch.

kll_sketch_get_quantile_bigint

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_quantile_bigint(left, right): Extracts a single value from the quantiles sketch representing the desired quantile given the input rank. The desired quantile can either be a single value or an array. In the latter case, the function will return an array of results of equal length to the input array.

kll_sketch_get_quantile_double

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_quantile_double(left, right): Extracts a single value from the quantiles sketch representing the desired quantile given the input rank. The desired quantile can either be a single value or an array. In the latter case, the function will return an array of results of equal length to the input array.

kll_sketch_get_quantile_float

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_quantile_float(left, right): Extracts a single value from the quantiles sketch representing the desired quantile given the input rank. The desired quantile can either be a single value or an array. In the latter case, the function will return an array of results of equal length to the input array.

kll_sketch_get_rank_bigint

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_rank_bigint(left, right): Extracts a single value from the quantiles sketch representing the desired rank given the input quantile. The desired rank can either be a single value or an array. In the latter case, the function will return an array of results of equal length to the input array.

kll_sketch_get_rank_double

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_rank_double(left, right): Extracts a single value from the quantiles sketch representing the desired rank given the input quantile. The desired rank can either be a single value or an array. In the latter case, the function will return an array of results of equal length to the input array.

kll_sketch_get_rank_float

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_get_rank_float(left, right): Extracts a single value from the quantiles sketch representing the desired rank given the input quantile. The desired rank can either be a single value or an array. In the latter case, the function will return an array of results of equal length to the input array.

kll_sketch_merge_bigint

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_merge_bigint(left, right): Merges two sketch buffers together into one.

kll_sketch_merge_double

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_merge_double(left, right): Merges two sketch buffers together into one.

kll_sketch_merge_float

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_merge_float(left, right): Merges two sketch buffers together into one.

kll_sketch_to_string_bigint

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_to_string_bigint(expr): Returns human readable summary information about this sketch.

kll_sketch_to_string_double

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_to_string_double(expr): Returns human readable summary information about this sketch.

kll_sketch_to_string_float

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_to_string_float(expr): Returns human readable summary information about this sketch.

theta_difference

Supported. New in Spark 4.1.0.

theta_difference(first, second): Subtracts two binary representations of Datasketches ThetaSketch objects from two input columns using a ThetaSketch AnotB object.

SELECT theta_sketch_estimate(theta_difference(theta_sketch_agg(col1), theta_sketch_agg(col2))) FROM VALUES (5, 4), (1, 4), (2, 5), (2, 5), (3, 1) tab(col1, col2);
-- 2

theta_intersection

Supported. New in Spark 4.1.0.

theta_intersection(first, second): Intersects two binary representations of Datasketches ThetaSketch objects from two input columns using a ThetaSketch Intersect object.

SELECT theta_sketch_estimate(theta_intersection(theta_sketch_agg(col1), theta_sketch_agg(col2))) FROM VALUES (5, 4), (1, 4), (2, 5), (2, 5), (3, 1) tab(col1, col2);
-- 2

theta_sketch_estimate

Supported. New in Spark 4.1.0.

theta_sketch_estimate(expr): Returns the estimated number of unique values given the binary representation of a Datasketches ThetaSketch.

SELECT theta_sketch_estimate(theta_sketch_agg(col)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
-- 3

theta_union

Supported. New in Spark 4.1.0.

theta_union(first, second, lgNomEntries): Merges two binary representations of Datasketches ThetaSketch objects using a ThetaSketch Union object. Users can set lgNomEntries to a value between 4 and 26 to find the union of sketches with different union buffer size values (defaults to 12).

SELECT theta_sketch_estimate(theta_union(theta_sketch_agg(col1), theta_sketch_agg(col2))) FROM VALUES (1, 4), (1, 4), (2, 5), (2, 5), (3, 6) tab(col1, col2);
-- 6