Window, generator and table functions

This page lists every Spark SQL window, generator and table-valued function: 19 work on Databasin Sail as in Apache Spark and 1 isn'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.

Window

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

cume_dist

Supported.

cume_dist(): Computes the position of a value relative to all values in the partition.

Since Spark 2.0.0.

SELECT a, b, cume_dist() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 0.6666666666666666
-- A1 | 1 | 0.6666666666666666
-- A1 | 2 | 1.0
-- A2 | 3 | 1.0

dense_rank

Supported.

dense_rank(): Computes the rank of a value in a group of values. The result is one plus the previously assigned rank value. Unlike the function rank, dense_rank will not produce gaps in the ranking sequence.

• children: this is to base the rank on; a change in the value of one the children will trigger a change in rank. This is an internal parameter and will be assigned by the Analyser.

Since Spark 2.0.0.

SELECT a, b, dense_rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 1
-- A1 | 1 | 1
-- A1 | 2 | 2
-- A2 | 3 | 1

lag

Supported.

lag(input[, offset[, default]]): Returns the value of input at the offsetth row before the current row in the window. The default value of offset is 1 and the default value of default is null. If the value of input at the offsetth row is null, null is returned. If there is no such offset row (e.g., when the offset is 1, the first row of the window does not have any previous row), default is returned.

• input: a string expression to evaluate offset rows before the current row.
• offset: an int expression which is rows to jump back in the partition.
• default: a string expression which is to use when the offset row does not exist.

Since Spark 2.0.0.

SELECT a, b, lag(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | NULL
-- A1 | 1 | 1
-- A1 | 2 | 1
-- A2 | 3 | NULL

lead

Supported.

lead(input[, offset[, default]]): Returns the value of input at the offsetth row after the current row in the window. The default value of offset is 1 and the default value of default is null. If the value of input at the offsetth row is null, null is returned. If there is no such an offset row (e.g., when the offset is 1, the last row of the window does not have any subsequent row), default is returned.

• input: a string expression to evaluate offset rows after the current row.
• offset: an int expression which is rows to jump ahead in the partition.
• default: a string expression which is to use when the offset is larger than the window. The default value is null.

Since Spark 2.0.0.

SELECT a, b, lead(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 1
-- A1 | 1 | 2
-- A1 | 2 | NULL
-- A2 | 3 | NULL

nth_value

Supported.

nth_value(input[, offset]): Returns the value of input at the row that is the offsetth row from beginning of the window frame. Offset starts at 1. If ignoreNulls=true, we will skip nulls when finding the offsetth row. Otherwise, every row counts for the offset. If there is no such an offsetth row (e.g., when the offset is 10, size of the window frame is less than 10), null is returned.

• input: the target column or expression that the function operates on.
• offset: a positive int literal to indicate the offset in the window frame. It starts with 1.
• ignoreNulls: an optional specification that indicates the NthValue should skip null values in the determination of which row to use.

Since Spark 3.1.0.

SELECT a, b, nth_value(b, 2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 1
-- A1 | 1 | 1
-- A1 | 2 | 1
-- A2 | 3 | NULL

ntile

Supported.

ntile(n): Divides the rows for each window partition into n buckets ranging from 1 to at most n.

• buckets: an int expression which is number of buckets to divide the rows in. Default value is 1.

Since Spark 2.0.0.

SELECT a, b, ntile(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 1
-- A1 | 1 | 1
-- A1 | 2 | 2
-- A2 | 3 | 1

percent_rank

Supported.

percent_rank(): Computes the percentage ranking of a value in a group of values.

• children: this is to base the rank on; a change in the value of one the children will trigger a change in rank. This is an internal parameter and will be assigned by the Analyser.

Since Spark 2.0.0.

SELECT a, b, percent_rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 0.0
-- A1 | 1 | 0.0
-- A1 | 2 | 1.0
-- A2 | 3 | 0.0

rank

Supported.

rank(): Computes the rank of a value in a group of values. The result is one plus the number of rows preceding or equal to the current row in the ordering of the partition. The values will produce gaps in the sequence.

• children: this is to base the rank on; a change in the value of one the children will trigger a change in rank. This is an internal parameter and will be assigned by the Analyser.

Since Spark 2.0.0.

SELECT a, b, rank(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 1
-- A1 | 1 | 1
-- A1 | 2 | 3
-- A2 | 3 | 1

row_number

Supported.

row_number(): Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition.

Since Spark 2.0.0.

SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
-- A1 | 1 | 1
-- A1 | 1 | 2
-- A1 | 2 | 3
-- A2 | 3 | 1

Generator

collations · explode · explode_outer · inline · inline_outer · posexplode · posexplode_outer · sql_keywords · stack

collations

Supported. Lists collations; UTF8_LCASE grouping / set operations and ICU collations are refused.

collations(): Get all of the Spark SQL string collations

Since Spark 4.0.0.

SELECT * FROM collations() WHERE NAME = 'UTF8_BINARY';
-- SYSTEM  BUILTIN  UTF8_BINARY NULL  NULL  ACCENT_SENSITIVE  CASE_SENSITIVE  NO_PAD  NULL

explode

Supported.

explode(expr): Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns. Unless specified otherwise, uses the default column name col for elements of the array or key and value for the elements of the map.

Since Spark 1.0.0.

SELECT explode(array(10, 20));
-- 10
-- 20

SELECT explode(collection => array(10, 20));
-- 10
-- 20

explode_outer

Supported.

explode_outer(expr): Separates the elements of array expr into multiple rows, or the elements of map expr into multiple rows and columns. Unless specified otherwise, uses the default column name col for elements of the array or key and value for the elements of the map.

Since Spark 1.0.0.

SELECT explode_outer(array(10, 20));
-- 10
-- 20

SELECT explode_outer(collection => array(10, 20));
-- 10
-- 20

inline

Supported.

inline(expr): Explodes an array of structs into a table. Uses column names col1, col2, etc. by default unless specified otherwise.

Since Spark 3.4.0.

SELECT * FROM inline(array(struct(1, 'a'), struct(2, 'b')));
-- 1 | a
-- 2 | b

SELECT * FROM inline(input => array(struct(1, 'a'), struct(2, 'b')));
-- 1 | a
-- 2 | b

inline_outer

Supported.

inline_outer(expr): Explodes an array of structs into a table. Uses column names col1, col2, etc. by default unless specified otherwise.

Since Spark 2.0.0.

SELECT * FROM inline_outer(array(struct(1, 'a'), struct(2, 'b')));
-- 1 | a
-- 2 | b

SELECT * FROM inline_outer(input => array(struct(1, 'a'), struct(2, 'b')));
-- 1 | a
-- 2 | b

posexplode

Supported.

posexplode(expr): Separates the elements of array expr into multiple rows with positions, or the elements of map expr into multiple rows and columns with positions. Unless specified otherwise, uses the column name pos for position, col for elements of the array or key and value for elements of the map.

Since Spark 2.0.0.

SELECT posexplode(array(10,20));
-- 0 | 10
-- 1 | 20

SELECT posexplode(collection => array(10,20));
-- 0 | 10
-- 1 | 20

posexplode_outer

Supported.

posexplode_outer(expr): Separates the elements of array expr into multiple rows with positions, or the elements of map expr into multiple rows and columns with positions. Unless specified otherwise, uses the column name pos for position, col for elements of the array or key and value for elements of the map.

Since Spark 2.0.0.

SELECT * FROM posexplode_outer(array(10,20));
-- 0 | 10
-- 1 | 20

SELECT * FROM posexplode_outer(collection => array(10,20));
-- 0 | 10
-- 1 | 20

sql_keywords

Supported.

sql_keywords(): Get Spark SQL keywords

Since Spark 3.5.0.

SELECT * FROM sql_keywords() LIMIT 2;
-- ADD  false
-- AFTER  false

stack

Supported.

stack(n, expr1, ..., exprk): Separates expr1, ..., exprk into n rows. Uses column names col0, col1, etc. by default unless specified otherwise.

Since Spark 2.0.0.

SELECT stack(2, 1, 2, 3);
-- 1 | 2
-- 3 | NULL

Table-valued

python_worker_logs · range

python_worker_logs

Not supported. New in Spark 4.1.0. Not implemented.

python_worker_logs(): Returns a table of logs collected from Python workers.

range

Supported.

range(start[, end[, step[, numSlices]]]) / range(end): Returns a table of values within a specified range.

• start: An optional BIGINT literal defaulted to 0, marking the first value generated.
• end: A BIGINT literal marking endpoint (exclusive) of the number generation.
• step: An optional BIGINT literal defaulted to 1, specifying the increment used when generating values.
• numParts: An optional INTEGER literal specifying how the production of rows is spread across partitions.

Since Spark 2.0.0.

SELECT * FROM range(1);
-- +---+
-- | id|
-- +---+
-- |  0|
-- +---+

SELECT * FROM range(0, 2);
-- +---+
-- |id |
-- +---+
-- |0  |
-- |1  |
-- +---+

SELECT * FROM range(0, 4, 2);
-- +---+
-- |id |
-- +---+
-- |0  |
-- |2  |
-- +---+

Function names, usage text, argument descriptions and examples 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. Sail status and notes are from Databasin.

Where to go next