Generator Functions¶

This page lists all generator functions available in Spark SQL.

---

collations¶

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

Examples:

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

Since: 4.0.0

explode¶

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.

Arguments:

• expr - An array or map expression whose elements are separated into rows.

Examples:

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

Since: 1.0.0

explode_outer¶

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.

Examples:

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

Since: 3.4.0

inline¶

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

Examples:

> 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

Since: 3.4.0

inline_outer¶

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

Examples:

> 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

Since: 3.4.0

posexplode¶

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.

Arguments:

• expr - An array or map expression whose elements are separated into rows, each paired with its position.

Examples:

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

Since: 2.0.0

posexplode_outer¶

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.

Arguments:

• expr - An array or map expression whose elements are separated into rows, each paired with its position.

Examples:

> SELECT posexplode_outer(array(10,20));
 0  10
 1  20
> SELECT posexplode_outer(collection => array(10,20));
 0  10
 1  20

Since: 2.0.0

sql_keywords¶

sql_keywords() - Get Spark SQL keywords

Examples:

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

Since: 3.5.0

stack¶

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

Arguments:

• n - The number of rows to separate the expressions into. Must be a positive integer literal.
• exprN - The expressions to separate into rows. Their values are laid out row-major across the n output rows.

Examples:

> SELECT stack(2, 1, 2, 3);
 1  2
 3  NULL

Since: 2.0.0