Scalar functions
These functions are available in Telemetry SQL. Signatures, aliases, argument descriptions, and examples below follow the vendored engine fork. Examples that name a table assume matching columns in your own table; result grids illustrate values and may differ from the dashboard or JSON display.
SQL reference · String and regular expression functions · Date and time functions · Array, struct, and map functions · Aggregate functions · Window functions
Math Functions
- abs
- acos
- acosh
- asin
- asinh
- atan
- atan2
- atanh
- cbrt
- ceil
- cos
- cosh
- cot
- degrees
- exp
- factorial
- floor
- gcd
- isnan
- iszero
- lcm
- ln
- log
- log10
- log2
- nanvl
- pi
- pow
- power
- radians
- rand
- random
- round
- signum
- sin
- sinh
- sqrt
- tan
- tanh
- trunc
abs
Returns the absolute value of a number.
abs(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT abs(-5);
+----------+
| abs(-5) |
+----------+
| 5 |
+----------+
acos
Returns the arc cosine or inverse cosine of a number.
acos(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT acos(1);
+----------+
| acos(1) |
+----------+
| 0.0 |
+----------+
acosh
Returns the area hyperbolic cosine or inverse hyperbolic cosine of a number.
acosh(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT acosh(2);
+------------+
| acosh(2) |
+------------+
| 1.31696 |
+------------+
asin
Returns the arc sine or inverse sine of a number.
asin(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT asin(0.5);
+------------+
| asin(0.5) |
+------------+
| 0.5235988 |
+------------+
asinh
Returns the area hyperbolic sine or inverse hyperbolic sine of a number.
asinh(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT asinh(1);
+------------+
| asinh(1) |
+------------+
| 0.8813736 |
+------------+
atan
Returns the arc tangent or inverse tangent of a number.
atan(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECT atan(1);
+-----------+
| atan(1) |
+-----------+
| 0.7853982 |
+-----------+
atan2
Returns the arc tangent or inverse tangent of expression_y / expression_x.
atan2(expression_y, expression_x)
Arguments
- expression_y: First numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
- expression_x: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
Example
SELECT atan2(1, 1);
+------------+
| atan2(1,1) |
+------------+
| 0.7853982 |
+------------+
atanh
Returns the area hyperbolic tangent or inverse hyperbolic tangent of a number.
atanh(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECT atanh(0.5);
+-------------+
| atanh(0.5) |
+-------------+
| 0.5493061 |
+-------------+
cbrt
Returns the cube root of a number.
cbrt(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT cbrt(27);
+-----------+
| cbrt(27) |
+-----------+
| 3.0 |
+-----------+
ceil
Returns the nearest integer greater than or equal to a number.
ceil(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
| 4.0 |
+------------+
cos
Returns the cosine of a number.
cos(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT cos(0);
+--------+
| cos(0) |
+--------+
| 1.0 |
+--------+
cosh
Returns the hyperbolic cosine of a number.
cosh(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT cosh(1);
+-----------+
| cosh(1) |
+-----------+
| 1.5430806 |
+-----------+
cot
Returns the cotangent of a number.
cot(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT cot(1);
+---------+
| cot(1) |
+---------+
| 0.64209 |
+---------+
degrees
Converts radians to degrees.
degrees(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECT degrees(pi());
+------------+
| degrees(0) |
+------------+
| 180.0 |
+------------+
exp
Returns the base-e exponential of a number.
exp(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT exp(1);
+---------+
| exp(1) |
+---------+
| 2.71828 |
+---------+
factorial
Factorial of a non-negative integer. Errors if the argument is negative or the result overflows.
factorial(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT factorial(5);
+---------------+
| factorial(5) |
+---------------+
| 120 |
+---------------+
floor
Returns the nearest integer less than or equal to a number.
floor(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT floor(3.14);
+-------------+
| floor(3.14) |
+-------------+
| 3.0 |
+-------------+
gcd
Returns the greatest common divisor of expression_x and expression_y. Returns 0 if both inputs are zero.
gcd(expression_x, expression_y)
Arguments
- expression_x: First numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
- expression_y: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT gcd(48, 18);
+------------+
| gcd(48,18) |
+------------+
| 6 |
+------------+
isnan
Returns true if a given number is +NaN or -NaN otherwise returns false.
isnan(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT isnan(1);
+----------+
| isnan(1) |
+----------+
| false |
+----------+
iszero
Returns true if a given number is +0.0 or -0.0 otherwise returns false.
iszero(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT iszero(0);
+------------+
| iszero(0) |
+------------+
| true |
+------------+
lcm
Returns the least common multiple of expression_x and expression_y. Returns 0 if either input is zero.
lcm(expression_x, expression_y)
Arguments
- expression_x: First numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
- expression_y: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT lcm(4, 5);
+----------+
| lcm(4,5) |
+----------+
| 20 |
+----------+
ln
Returns the natural logarithm of a number.
ln(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT ln(2.71828);
+-------------+
| ln(2.71828) |
+-------------+
| 1.0 |
+-------------+
log
Returns the base-x logarithm of a number. Can either provide a specified base, or if omitted then takes the base-10 of a number.
log(base, numeric_expression)
log(numeric_expression)
Arguments
- base: Base numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT log(10);
+---------+
| log(10) |
+---------+
| 1.0 |
+---------+
log10
Returns the base-10 logarithm of a number.
log10(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT log10(100);
+-------------+
| log10(100) |
+-------------+
| 2.0 |
+-------------+
log2
Returns the base-2 logarithm of a number.
log2(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT log2(8);
+-----------+
| log2(8) |
+-----------+
| 3.0 |
+-----------+
nanvl
Returns the first argument if it's not NaN. Returns the second argument otherwise.
nanvl(expression_x, expression_y)
Arguments
- expression_x: Numeric expression to return if it's not NaN. Can be a constant, column, or function, and any combination of arithmetic operators.
- expression_y: Numeric expression to return if the first expression is NaN. Can be a constant, column, or function, and any combination of arithmetic operators.
Example
SELECT nanvl(0, 5);
+------------+
| nanvl(0,5) |
+------------+
| 0 |
+------------+
pi
Returns an approximate value of π.
pi()
pow
Alias of power.
power
Returns a base expression raised to the power of an exponent.
power(base, exponent)
Arguments
- base: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
- exponent: Exponent numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT power(2, 3);
+-------------+
| power(2,3) |
+-------------+
| 8 |
+-------------+
Aliases
- pow
radians
Converts degrees to radians.
radians(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT radians(180);
+----------------+
| radians(180) |
+----------------+
| 3.14159265359 |
+----------------+
rand
Alias of random.
random
Returns a random float value in the range [0, 1). The random seed is unique to each row.
random()
Example
SELECT random();
+------------------+
| random() |
+------------------+
| 0.7389238902938 |
+------------------+
Aliases
- rand
round
Rounds a number to the nearest integer.
round(numeric_expression[, decimal_places])
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
- decimal_places: Optional. The number of decimal places to round to. Defaults to 0.
Example
SELECT round(3.14159);
+--------------+
| round(3.14159)|
+--------------+
| 3.0 |
+--------------+
signum
Returns the sign of a number.
Negative numbers return -1.
Zero and positive numbers return 1.
signum(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT signum(-42);
+-------------+
| signum(-42) |
+-------------+
| -1 |
+-------------+
sin
Returns the sine of a number.
sin(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT sin(0);
+----------+
| sin(0) |
+----------+
| 0.0 |
+----------+
sinh
Returns the hyperbolic sine of a number.
sinh(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT sinh(1);
+-----------+
| sinh(1) |
+-----------+
| 1.1752012 |
+-----------+
sqrt
Returns the square root of a number.
sqrt(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
tan
Returns the tangent of a number.
tan(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
SELECT tan(pi()/4);
+--------------+
| tan(PI()/4) |
+--------------+
| 1.0 |
+--------------+
tanh
Returns the hyperbolic tangent of a number.
tanh(numeric_expression)
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECT tanh(20);
+----------+
| tanh(20) |
+----------+
| 1.0 |
+----------+
trunc
Truncates a number to a whole number or truncated to the specified decimal places.
trunc(numeric_expression[, decimal_places])
Arguments
- numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.
- decimal_places: Optional. The number of decimal places to
truncate to. Defaults to 0 (truncate to a whole number). If
decimal_placesis a positive integer, truncates digits to the right of the decimal point. Ifdecimal_placesis a negative integer, replaces digits to the left of the decimal point with0.
Example
SELECT trunc(42.738);
+----------------+
| trunc(42.738) |
+----------------+
| 42 |
+----------------+
Conditional Functions
coalesce
Returns the first of its arguments that is not null. Returns null if all arguments are null. This function is often used to substitute a default value for null values.
coalesce(expression1[, ..., expression_n])
Arguments
- expression1, expression_n: Expression to use if previous expressions are null. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.
Example
select coalesce(null, null, 'datafusion');
+----------------------------------------+
| coalesce(NULL,NULL,Utf8("datafusion")) |
+----------------------------------------+
| datafusion |
+----------------------------------------+
greatest
Returns the greatest value in a list of expressions. Returns null if all expressions are null.
greatest(expression1[, ..., expression_n])
Arguments
- expression1, expression_n: Expressions to compare and return the greatest value.. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.
Example
select greatest(4, 7, 5);
+---------------------------+
| greatest(4,7,5) |
+---------------------------+
| 7 |
+---------------------------+
ifnull
Alias of nvl.
least
Returns the smallest value in a list of expressions. Returns null if all expressions are null.
least(expression1[, ..., expression_n])
Arguments
- expression1, expression_n: Expressions to compare and return the smallest value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.
Example
select least(4, 7, 5);
+---------------------------+
| least(4,7,5) |
+---------------------------+
| 4 |
+---------------------------+
nullif
Returns null if expression1 equals expression2; otherwise it returns expression1.
This can be used to perform the inverse operation of coalesce.
nullif(expression1, expression2)
Arguments
- expression1: Expression to compare and return if equal to expression2. Can be a constant, column, or function, and any combination of operators.
- expression2: Expression to compare to expression1. Can be a constant, column, or function, and any combination of operators.
Example
select nullif('datafusion', 'data');
+-----------------------------------------+
| nullif(Utf8("datafusion"),Utf8("data")) |
+-----------------------------------------+
| datafusion |
+-----------------------------------------+
select nullif('datafusion', 'datafusion');
+-----------------------------------------------+
| nullif(Utf8("datafusion"),Utf8("datafusion")) |
+-----------------------------------------------+
| |
+-----------------------------------------------+
nvl
Returns expression2 if expression1 is NULL otherwise it returns expression1 and expression2 is not evaluated. This function can be used to substitute a default value for NULL values.
nvl(expression1, expression2)
Arguments
- expression1: Expression to return if not null. Can be a constant, column, or function, and any combination of operators.
- expression2: Expression to return if expr1 is null. Can be a constant, column, or function, and any combination of operators.
Example
select nvl(null, 'a');
+---------------------+
| nvl(NULL,Utf8("a")) |
+---------------------+
| a |
+---------------------+\
select nvl('b', 'a');
+--------------------------+
| nvl(Utf8("b"),Utf8("a")) |
+--------------------------+
| b |
+--------------------------+
Aliases
- ifnull
nvl2
Returns expression2 if expression1 is not NULL; otherwise it returns expression3.
nvl2(expression1, expression2, expression3)
Arguments
- expression1: Expression to test for null. Can be a constant, column, or function, and any combination of operators.
- expression2: Expression to return if expr1 is not null. Can be a constant, column, or function, and any combination of operators.
- expression3: Expression to return if expr1 is null. Can be a constant, column, or function, and any combination of operators.
Example
select nvl2(null, 'a', 'b');
+--------------------------------+
| nvl2(NULL,Utf8("a"),Utf8("b")) |
+--------------------------------+
| b |
+--------------------------------+
select nvl2('data', 'a', 'b');
+----------------------------------------+
| nvl2(Utf8("data"),Utf8("a"),Utf8("b")) |
+----------------------------------------+
| a |
+----------------------------------------+
Binary String Functions
decode
Decode binary data from textual representation in string.
decode(expression, format)
Arguments
- expression: Expression containing encoded string data
- format: Same arguments as encode
Related functions:
encode
Encode binary data into a textual representation.
encode(expression, format)
Arguments
- expression: Expression containing string or binary data
- format: Supported formats are:
base64,base64pad,hex
Related functions:
Hashing Functions
digest
Computes the binary hash of an expression using the specified algorithm.
digest(expression, algorithm)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
- algorithm: String expression specifying algorithm to use. Must be one of:
- md5
- sha224
- sha256
- sha384
- sha512
- blake2s
- blake2b
- blake3
Example
select digest('foo', 'sha256');
+------------------------------------------------------------------+
| digest(Utf8("foo"),Utf8("sha256")) |
+------------------------------------------------------------------+
| 2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae |
+------------------------------------------------------------------+
md5
Computes an MD5 128-bit checksum for a string expression.
md5(expression)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select md5('foo');
+----------------------------------+
| md5(Utf8("foo")) |
+----------------------------------+
| acbd18db4cc2f85cedef654fccc4a4d8 |
+----------------------------------+
sha224
Computes the SHA-224 hash of a binary string.
sha224(expression)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select sha224('foo');
+----------------------------------------------------------+
| sha224(Utf8("foo")) |
+----------------------------------------------------------+
| 0808f64e60d58979fcb676c96ec938270dea42445aeefcd3a4e6f8db |
+----------------------------------------------------------+
sha256
Computes the SHA-256 hash of a binary string.
sha256(expression)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select sha256('foo');
+------------------------------------------------------------------+
| sha256(Utf8("foo")) |
+------------------------------------------------------------------+
| 2c26b46b68ffc68ff99b453c1d30413413422d706483bfa0f98a5e886266e7ae |
+------------------------------------------------------------------+
sha384
Computes the SHA-384 hash of a binary string.
sha384(expression)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select sha384('foo');
+--------------------------------------------------------------------------------------------------+
| sha384(Utf8("foo")) |
+--------------------------------------------------------------------------------------------------+
| 98c11ffdfdd540676b1a137cb1a22b2a70350c9a44171d6b1180c6be5cbb2ee3f79d532c8a1dd9ef2e8e08e752a3babb |
+--------------------------------------------------------------------------------------------------+
sha512
Computes the SHA-512 hash of a binary string.
sha512(expression)
Arguments
- expression: String expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
select sha512('foo');
+----------------------------------------------------------------------------------------------------------------------------------+
| sha512(Utf8("foo")) |
+----------------------------------------------------------------------------------------------------------------------------------+
| f7fbba6e0636f890e56fbbf3283e524c6fa3204ae298382d624741d0dc6638326e282c41be5e4254d8820772c5518a2c5a8c0c7f7eda19594a7eb539453e1ed7 |
+----------------------------------------------------------------------------------------------------------------------------------+
Union Functions
Functions to work with the union data type, also know as tagged unions, variant types, enums or sum types. Note: Not related to the SQL UNION operator
union_extract
Returns the value of the given field in the union when selected, or NULL otherwise.
union_extract(union, field_name)
Arguments
- union: Union expression to operate on. Can be a constant, column, or function, and any combination of operators.
- field_name: String expression to operate on. Must be a constant.
Example
❯ select union_column, union_extract(union_column, 'a'), union_extract(union_column, 'b') from table_with_union;
+--------------+----------------------------------+----------------------------------+
| union_column | union_extract(union_column, 'a') | union_extract(union_column, 'b') |
+--------------+----------------------------------+----------------------------------+
| {a=1} | 1 | |
| {b=3.0} | | 3.0 |
| {a=4} | 4 | |
| {b=} | | |
| {a=} | | |
+--------------+----------------------------------+----------------------------------+
union_tag
Returns the name of the currently selected field in the union
union_tag(union_expression)
Arguments
- union: Union expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
❯ select union_column, union_tag(union_column) from table_with_union;
+--------------+-------------------------+
| union_column | union_tag(union_column) |
+--------------+-------------------------+
| {a=1} | a |
| {b=3.0} | b |
| {a=4} | a |
| {b=} | b |
| {a=} | a |
+--------------+-------------------------+
Other Functions
- arrow_cast
- arrow_field
- arrow_metadata
- arrow_try_cast
- arrow_typeof
- cast_to_type
- get_field
- try_cast_to_type
- version
- with_metadata
arrow_cast
Casts a value to a specific Arrow data type.
arrow_cast(expression, datatype)
Arguments
- expression: Expression to cast. The expression can be a constant, column, or function, and any combination of operators.
- datatype: Arrow data type name to cast to, as a string. The format is the same as that returned by
arrow_typeof
Example
select
arrow_cast(-5, 'Int8') as a,
arrow_cast('foo', 'Dictionary(Int32, Utf8)') as b,
arrow_cast('bar', 'LargeUtf8') as c;
+----+-----+-----+
| a | b | c |
+----+-----+-----+
| -5 | foo | bar |
+----+-----+-----+
select
arrow_cast('2023-01-02T12:53:02', 'Timestamp(µs, "+08:00")') as d,
arrow_cast('2023-01-02T12:53:02', 'Timestamp(µs)') as e;
+---------------------------+---------------------+
| d | e |
+---------------------------+---------------------+
| 2023-01-02T12:53:02+08:00 | 2023-01-02T12:53:02 |
+---------------------------+---------------------+
arrow_field
Returns a struct containing the Arrow field information of the expression, including name, data type, nullability, and metadata.
arrow_field(expression)
Arguments
- expression: Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators.
Example
select arrow_field(1);
+-------------------------------------------------------------+
| arrow_field(Int64(1)) |
+-------------------------------------------------------------+
| {name: lit, data_type: Int64, nullable: false, metadata: {}} |
+-------------------------------------------------------------+
select arrow_field(1)['data_type'];
+-----------------------------------+
| arrow_field(Int64(1))[data_type] |
+-----------------------------------+
| Int64 |
+-----------------------------------+
arrow_metadata
Returns the metadata of the input expression. If a key is provided, returns the value for that key. If no key is provided, returns a Map of all metadata.
arrow_metadata(expression[, key])
Arguments
- expression: The expression to retrieve metadata from. Can be a column or other expression.
- key: Optional. The specific metadata key to retrieve.
Example
select arrow_metadata(col) from table;
+----------------------------+
| arrow_metadata(table.col) |
+----------------------------+
| {k: v} |
+----------------------------+
select arrow_metadata(col, 'k') from table;
+-------------------------------+
| arrow_metadata(table.col, 'k')|
+-------------------------------+
| v |
+-------------------------------+
arrow_try_cast
Casts a value to a specific Arrow data type, returning NULL if the cast fails.
arrow_try_cast(expression, datatype)
Arguments
- expression: Expression to cast. The expression can be a constant, column, or function, and any combination of operators.
- datatype: Arrow data type name to cast to, as a string. The format is the same as that returned by
arrow_typeof
Example
select arrow_try_cast('123', 'Int64') as a,
arrow_try_cast('not_a_number', 'Int64') as b;
+-----+------+
| a | b |
+-----+------+
| 123 | NULL |
+-----+------+
arrow_typeof
Returns the name of the underlying Arrow data type of the expression.
arrow_typeof(expression)
Arguments
- expression: Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators.
Example
select arrow_typeof('foo'), arrow_typeof(1);
+---------------------------+------------------------+
| arrow_typeof(Utf8("foo")) | arrow_typeof(Int64(1)) |
+---------------------------+------------------------+
| Utf8 | Int64 |
+---------------------------+------------------------+
cast_to_type
Casts the first argument to the data type of the second argument. Only the type of the second argument is used; its value is ignored.
cast_to_type(expression, reference)
Arguments
- expression: The expression to cast. It can be a constant, column, or function, and any combination of operators.
- reference: Reference expression whose data type determines the target cast type. The value is ignored.
Example
select cast_to_type('42', NULL::INTEGER) as a;
+----+
| a |
+----+
| 42 |
+----+
select cast_to_type(1 + 2, NULL::DOUBLE) as b;
+-----+
| b |
+-----+
| 3.0 |
+-----+
get_field
Returns a field within a map or a struct with the given key.
Supports nested field access by providing multiple field names.
Note: most users invoke get_field indirectly via field access
syntax such as my_struct_col['field_name'] which results in a call to
get_field(my_struct_col, 'field_name').
Nested access like my_struct['a']['b'] is optimized to a single call:
get_field(my_struct, 'a', 'b').
get_field(expression, field_name[, field_name2, ...])
Arguments
- expression: The map or struct to retrieve a field from.
- field_name: The field name(s) to access, in order for nested access. Must evaluate to strings.
Example
-- Access a field from a struct column
WITH test(struct_col) AS (VALUES ({name: 'Alice', age: 30}), ({name: 'Bob', age: 25}))
SELECT struct_col FROM test;
+-----------------------------+
| struct_col |
+-----------------------------+
| {name: Alice, age: 30} |
| {name: Bob, age: 25} |
+-----------------------------+
WITH test(struct_col) AS (VALUES ({name: 'Alice', age: 30}), ({name: 'Bob', age: 25}))
SELECT struct_col['name'] AS name FROM test;
+-------+
| name |
+-------+
| Alice |
| Bob |
+-------+
-- Nested field access with multiple arguments
WITH test(struct_col) AS (VALUES ({outer: {inner_val: 42}}))
SELECT struct_col['outer']['inner_val'] AS result FROM test;
+--------+
| result |
+--------+
| 42 |
+--------+
try_cast_to_type
Casts the first argument to the data type of the second argument, returning NULL if the cast fails. Only the type of the second argument is used; its value is ignored.
try_cast_to_type(expression, reference)
Arguments
- expression: The expression to cast. It can be a constant, column, or function, and any combination of operators.
- reference: Reference expression whose data type determines the target cast type. The value is ignored.
Example
select try_cast_to_type('123', NULL::INTEGER) as a,
try_cast_to_type('not_a_number', NULL::INTEGER) as b;
+-----+------+
| a | b |
+-----+------+
| 123 | NULL |
+-----+------+
version
Returns the version of DataFusion.
version()
Example
select version();
+--------------------------------------------+
| version() |
+--------------------------------------------+
| Apache DataFusion 42.0.0, aarch64 on macos |
+--------------------------------------------+
with_metadata
Attaches Arrow field metadata (key/value pairs) to the input expression. Keys must be non-empty constant strings and values must be constant strings (empty values are allowed). Existing metadata on the input field is preserved; new keys overwrite on collision. This is the inverse of arrow_metadata.
with_metadata(expression, key1, value1[, key2, value2, ...])
Arguments
- expression: The expression whose output Arrow field should be annotated. Values flow through unchanged.
- key: Metadata key. Must be a non-empty constant string literal.
- value: Metadata value. Must be a constant string literal (may be empty).
Example
select arrow_metadata(with_metadata(column1, 'unit', 'ms'), 'unit') from (values (1));
+---------------------------------------------------------------+
| arrow_metadata(with_metadata(column1,Utf8("unit"),Utf8("ms")),Utf8("unit")) |
+---------------------------------------------------------------+
| ms |
+---------------------------------------------------------------+
select arrow_metadata(with_metadata(column1, 'unit', 'ms', 'source', 'sensor')) from (values (1));
+--------------------------+
| {source: sensor, unit: ms} |
+--------------------------+
Attribution
Adapted from the function documentation in Telemetry's vendored Apache DataFusion fork, with Telemetry-specific additions and corrections. Copyright 2019–2026 The Apache Software Foundation. Distributed under the Apache License 2.0; see the Apache notice.