DocumentationArchitectureBlogAboutSpeak to an engineer
Hotdata
Reference›Conditional & utility functions

Conditional & utility functions

Reference for conditional & utility functions in HotSQL. Names, signatures, and examples match the engine exactly.

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]
> 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.
> 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.
> 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]
> 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.
> 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.
> select cast_to_type('42', NULL::INTEGER) as a;
+----+
| a  |
+----+
| 42 |
+----+

> select cast_to_type(1 + 2, NULL::DOUBLE) as b;
+-----+
| b   |
+-----+
| 3.0 |
+-----+

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.

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.
> -- Access a field from a struct column
> create table 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}        |
+-----------------------------+
> select struct_col['name'] as name from test;
+-------+
| name  |
+-------+
| Alice |
| Bob   |
+-------+

> -- Nested field access with multiple arguments
> create table test(struct_col) as values
    ({outer: {inner_val: 42}});
> select struct_col['outer']['inner_val'] as result from test;
+--------+
| result |
+--------+
| 42     |
+--------+

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.
> select greatest(4, 7, 5);
+---------------------------+
| greatest(4,7,5)           |
+---------------------------+
| 7                         |
+---------------------------+

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

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.
> select nvl(null, 'a');
+---------------------+
| nvl(NULL,Utf8("a")) |
+---------------------+
| a                   |
+---------------------+\
> select nvl('b', 'a');
+--------------------------+
| nvl(Utf8("b"),Utf8("a")) |
+--------------------------+
| b                        |
+--------------------------+

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

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.
> 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 engine version string.

version()

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).
> 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} |
+--------------------------+