A DatumHue app runs SQL through DataHandle:query. The query is lazy — it plans and runs on first read, then caches.
Querying
The query is rooted on the handle it's called on, bound as the table data:
lua
local top = sales:query("SELECT region, SUM(amount) AS total FROM data GROUP BY region")
Pass values for ? placeholders as a second array argument; each ? binds left to right:
lua
local big = sales:query("SELECT * FROM data WHERE amount > ?", { 1000 })
A Dir or File handle in the params binds the next ? to a location inside its grant, so a query can read a granted file directly. Only granted locations are reachable — there is no path to the wider filesystem:
lua
local rows = data_handle:query("SELECT * FROM ? WHERE ok", { my_file })
Write results into a granted location with COPY ... TO ?:
lua
data_handle:query("COPY data TO ? STORED AS PARQUET", { out_dir })
Queries never modify your source data. The only writes are into a location you've granted for writing — COPY ... TO ?, or a table you create over a granted directory. They run under a memory budget (2 GiB natively, 1 GiB in the browser) and spill to disk where one is available.
Functions 232
The functions a query can call, by group.
Analytical Functions 5
first_value
window
first_value(expression)
Returns value evaluated at the row that is the first row of the window frame.
expression
Expression to operate on
Example
-- Example usage of the first_value window function:SELECT department,
employee_id,
salary,
first_value(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary
FROM employees;
+-------------+-------------+--------+------------+
| department | employee_id | salary | top_salary |
+-------------+-------------+--------+------------+
| Sales | 1 | 70000 | 70000 |
| Sales | 2 | 50000 | 70000 |
| Sales | 3 | 30000 | 70000 |
| Engineering | 4 | 90000 | 90000 |
| Engineering | 5 | 80000 | 90000 |
+-------------+-------------+--------+------------+
lag
window
lag(expression, offset, default)
Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value).
expression
Expression to operate on
offset
Integer. Specifies how many rows back the value of expression should be retrieved. Defaults to 1.
default
The default value if the offset is not within the partition. Must be of the same type as expression.
Example
-- Example usage of the lag window function:SELECT employee_id,
salary,
lag(salary, 1, 0) OVER (ORDER BY employee_id) AS prev_salary
FROM employees;
+-------------+--------+-------------+
| employee_id | salary | prev_salary |
+-------------+--------+-------------+
| 1 | 30000 | 0 |
| 2 | 50000 | 30000 |
| 3 | 70000 | 50000 |
| 4 | 60000 | 70000 |
+-------------+--------+-------------+
last_value
window
last_value(expression)
Returns value evaluated at the row that is the last row of the window frame.
expression
Expression to operate on
Example
-- SQL example of last_value:SELECT department,
employee_id,
salary,
last_value(salary) OVER (PARTITION BY department ORDER BY salary) AS running_last_salary
FROM employees;
+-------------+-------------+--------+---------------------+
| department | employee_id | salary | running_last_salary |
+-------------+-------------+--------+---------------------+
| Sales | 1 | 30000 | 30000 |
| Sales | 2 | 50000 | 50000 |
| Sales | 3 | 70000 | 70000 |
| Engineering | 4 | 40000 | 40000 |
| Engineering | 5 | 60000 | 60000 |
+-------------+-------------+--------+---------------------+
lead
window
lead(expression, offset, default)
Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default (which must be of the same type as value).
expression
Expression to operate on
offset
Integer. Specifies how many rows forward the value of expression should be retrieved. Defaults to 1.
default
The default value if the offset is not within the partition. Must be of the same type as expression.
Example
-- Example usage of lead window function:SELECT
employee_id,
department,
salary,
lead(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;
+-------------+-------------+--------+--------------+
| employee_id | department | salary | next_salary |
+-------------+-------------+--------+--------------+
| 1 | Sales | 30000 | 50000 |
| 2 | Sales | 50000 | 70000 |
| 3 | Sales | 70000 | 0 |
| 4 | Engineering | 40000 | 60000 |
| 5 | Engineering | 60000 | 0 |
+-------------+-------------+--------+--------------+
nth_value
window
nth_value(expression, n)
Returns the value evaluated at the nth row of the window frame (counting from 1). Returns NULL if no such row exists.
expression
The column from which to retrieve the nth value.
n
Integer. Specifies the row number (starting from 1) in the window frame.
Example
-- Sample employees table:CREATE TABLEemployees (id INT, salary INT);
INSERT INTOemployees (id, salary) VALUES
(1, 30000),
(2, 40000),
(3, 50000),
(4, 60000),
(5, 70000);
-- Example usage of nth_value:SELECTnth_value(salary, 2) OVER (
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS nth_value
FROM employees;
+-----------+
| nth_value |
+-----------+
| 40000 |
| 40000 |
| 40000 |
| 40000 |
| 40000 |
+-----------+
Approximate Functions 4
approx_distinct
aggregate
approx_distinct(expression)
Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
approx_percentile_cont(percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
Returns the approximate percentile of input values using the t-digest algorithm.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
percentile
Percentile to compute. Must be a float value between 0 and 1 (inclusive).
centroids
Number of centroids to use in the t-digest algorithm. Default is 100. A higher number results in more accurate approximation but requires more memory.
Example
> SELECTapprox_percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+------------------------------------------------------------------+
| approx_percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) |
+------------------------------------------------------------------+
| 65.0 |
+------------------------------------------------------------------+
> SELECTapprox_percentile_cont(0.75, 100) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+-----------------------------------------------------------------------+
| approx_percentile_cont(0.75, 100) WITHIN GROUP (ORDER BY column_name) |
+-----------------------------------------------------------------------+
| 65.0 |
+-----------------------------------------------------------------------+
> SELECTapprox_percentile_cont(column_name, 0.75) FROM table_name;
+-----------------------------------------------+
| approx_percentile_cont(column_name, 0.75) |
+-----------------------------------------------+
| 65.0 |
+-----------------------------------------------+
> SELECTapprox_percentile_cont(column_name, 0.75, 100) FROM table_name;
+----------------------------------------------------------+
| approx_percentile_cont(column_name, 0.75, 100) |
+----------------------------------------------------------+
| 65.0 |
+----------------------------------------------------------+
approx_percentile_cont_with_weight
aggregate
approx_percentile_cont_with_weight(weight, percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
Returns the weighted approximate percentile of input values using the t-digest algorithm.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
weight
Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators.
percentile
Percentile to compute. Must be a float value between 0 and 1 (inclusive).
centroids
Number of centroids to use in the t-digest algorithm. Default is 100. A higher number results in more accurate approximation but requires more memory.
Example
> SELECTapprox_percentile_cont_with_weight(weight_column, 0.90) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+---------------------------------------------------------------------------------------------+
| approx_percentile_cont_with_weight(weight_column, 0.90) WITHIN GROUP (ORDER BY column_name) |
+---------------------------------------------------------------------------------------------+
| 78.5 |
+---------------------------------------------------------------------------------------------+
> SELECTapprox_percentile_cont_with_weight(weight_column, 0.90, 100) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+--------------------------------------------------------------------------------------------------+
| approx_percentile_cont_with_weight(weight_column, 0.90, 100) WITHIN GROUP (ORDER BY column_name) |
+--------------------------------------------------------------------------------------------------+
| 78.5 |
+--------------------------------------------------------------------------------------------------+
> SELECTapprox_percentile_cont_with_weight(column_name, weight_column, 0.90) FROM table_name;
+--------------------------------------------------+
| approx_percentile_cont_with_weight(column_name, weight_column, 0.90) |
+--------------------------------------------------+
| 78.5 |
+--------------------------------------------------+
Array Functions 52
array_add
scalar
array_add(array1, array2)
Returns the element-wise sum of two numeric arrays of equal length, computed as array1[i] + array2[i] per position. NULL is propagated per element: if either input element at position i is NULL, the corresponding output element is NULL (positions are preserved). Returns NULL if either entire input array is NULL. Errors if the per-row lengths differ. Returns an empty array if both inputs are empty.
array1
Array expression. Can be a constant, column, or function, and any combination of array operators.
array2
Array expression. Can be a constant, column, or function, and any combination of array operators.
Also called list_append, array_push_back, list_push_back.
array_avg
scalar
array_avg(array)
Returns the arithmetic mean (sum divided by count) of the elements of the input array. NULL elements are skipped (per SQL aggregate convention) and excluded from the count. Returns NULL if the input row is NULL, every element is NULL, or the array is empty.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns the L2-normalized vector for the input numeric array, computed as array[i] / sqrt(sum(array[i]^2)) per element. Returns NULL if the input is NULL, contains NULL elements, or has zero magnitude (all elements are zero). Returns an empty array for an empty input array.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns the position of the first occurrence of the specified element in the array, or NULL if not found. Comparisons are done using IS DISTINCT FROM semantics, so NULL is considered to match NULL.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
element
Element to search for in the array.
index
Index at which to start searching (1-indexed). Defaults to searching from the start
Also called list_prepend, array_push_front, list_push_front.
array_product
scalar
array_product(array)
Returns the product of the elements in the input numeric array. NULL elements inside the array are skipped (matching SQL aggregate convention). Returns NULL if the input is NULL, every element is NULL, or the array is empty. The result is always returned as Float64.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Removes the first element from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If element evaluates to NULL, the result is NULL rather than removing NULL entries.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Removes all elements from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If element evaluates to NULL, the result is NULL rather than removing NULL entries.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Removes the first max elements from the array equal to the given value. NULL elements already in the array are preserved when removing a non-NULL value. If element evaluates to NULL, the result is NULL rather than removing NULL entries.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns a new array with each element of the input array multiplied by a scalar value, computed as array[i] * scalar. Returns NULL if the input row is NULL or the scalar is NULL. If a NULL element appears in the input array at position i, the result element at position i is NULL. Returns an empty array for an empty input array.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
scalar
Numeric scalar to multiply each element by. Can be a constant or column expression.
Returns the element-wise difference of two numeric arrays of equal length, computed as array1[i] - array2[i] per position. NULL is propagated per element: if either input element at position i is NULL, the corresponding output element is NULL (positions are preserved). Returns NULL if either entire input array is NULL. Errors if the per-row lengths differ. Returns an empty array if both inputs are empty.
array1
Array expression. Can be a constant, column, or function, and any combination of array operators.
array2
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns the sum of the elements of the input array, computed as array[0] + array[1] + .... NULL elements are skipped (per SQL aggregate convention). Returns NULL if the input row is NULL, every element is NULL, or the array is empty.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns an array of structs created by combining the elements of each input array at the same index. If the arrays have different lengths, shorter arrays are padded with NULLs.
Returns the cosine distance between two input arrays of equal length. The cosine distance is defined as 1 - cosine_similarity, i.e. 1 - dot(a,b) / (||a|| * ||b||). Returns NULL if either array is NULL or contains only zeros.
array1
Array expression. Can be a constant, column, or function, and any combination of array operators.
array2
Array expression. Can be a constant, column, or function, and any combination of array operators.
Converts an array of arrays to a flat array. - Applies to any depth of nested arrays - Does not change arrays that are already flat The flattened array contains all the elements from all source arrays.
array
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns the inner product (dot product) of two input arrays of equal length, computed as sum(array1[i] * array2[i]). Returns NULL if either array is NULL or contains NULL elements. Returns 0.0 for two empty arrays.
array1
Array expression. Can be a constant, column, or function, and any combination of array operators.
array2
Array expression. Can be a constant, column, or function, and any combination of array operators.
Returns an array between start and stop with step. The range start..end contains all values with start <= x < end. It is empty if start >= end. Step cannot be 0.
start
Start of the range. Ints, timestamps, dates or string types that can be coerced to Date32 are supported.
end
End of the range (not included). Type must be the same as start.
step
Increase by step (cannot be 0). Steps less than a day are supported only for timestamp ranges.
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.
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.
greatest
scalar
greatest(expression1[, ..., expression_n])
Returns the greatest value in a list of expressions. Returns null if all expressions are null.
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.
Returns the smallest value in a list of expressions. Returns null if all expressions are null.
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.
Returns null if expression1 equals expression2; otherwise it returns expression1. This can be used to perform the inverse operation of coalesce.
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
scalar
nvl(expression1, expression2)
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.
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.
Returns an array created from the expression elements. If ordering is required, elements are inserted in the specified order. This aggregation function can only mix DISTINCT and ORDER BY if the ordering expression is exactly the same as the argument expression.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECTarray_agg(column_name ORDER BY other_column) FROM table_name;
+-----------------------------------------------+
| array_agg(column_name ORDER BY other_column) |
+-----------------------------------------------+
| [element1, element2, element3] |
+-----------------------------------------------+
> SELECTarray_agg(DISTINCT column_name ORDER BY column_name) FROM table_name;
+--------------------------------------------------------+
| array_agg(DISTINCT column_name ORDER BY column_name) |
+--------------------------------------------------------+
| [element1, element2, element3] |
+--------------------------------------------------------+
avg
aggregate
avg(expression)
Returns the average of numeric values in the specified column.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
Returns the first element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECTfirst_value(column_name ORDER BY other_column) FROM table_name;
+-----------------------------------------------+
| first_value(column_name ORDER BY other_column)|
+-----------------------------------------------+
| first_element |
+-----------------------------------------------+
grouping
aggregate
grouping(expression)
Returns 1 if the data is aggregated across the specified column, or 0 if it is not aggregated in the result set.
expression
Expression to evaluate whether data is aggregated across the specified column. Can be a constant, column, or function.
Example
> SELECT column_name, GROUPING(column_name) AS group_column
FROM table_name
GROUP BY GROUPING SETS ((column_name), ());
+-------------+-------------+
| column_name | group_column |
+-------------+-------------+
| value1 | 0 |
| value2 | 0 |
| NULL | 1 |
+-------------+-------------+
last_value
aggregate
last_value(expression [ORDER BY expression])
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
Example
> SELECTlast_value(column_name ORDER BY other_column) FROM table_name;
+-----------------------------------------------+
| last_value(column_name ORDER BY other_column) |
+-----------------------------------------------+
| last_element |
+-----------------------------------------------+
max
aggregate
max(expression)
Returns the maximum value in the specified column.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
percentile_cont(percentile) WITHIN GROUP (ORDER BY expression)
Returns the exact percentile of input values, interpolating between values if needed.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
percentile
Percentile to compute. Must be a float value between 0 and 1 (inclusive).
Example
> SELECTpercentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+----------------------------------------------------------+
| percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) |
+----------------------------------------------------------+
| 45.5 |
+----------------------------------------------------------+
> SELECTpercentile_cont(column_name, 0.75) FROM table_name;
+---------------------------------------+
| percentile_cont(column_name, 0.75) |
+---------------------------------------+
| 45.5 |
+---------------------------------------+
Also called quantile_cont.
string_agg
aggregate
string_agg([DISTINCT] expression, delimiter [ORDER BY expression])
Concatenates the values of string expressions and places separator values between them. If ordering is required, strings are concatenated in the specified order. This aggregation function can only mix DISTINCT and ORDER BY if the ordering expression is exactly the same as the first argument expression.
expression
The string expression to concatenate. Can be a column or any valid string expression.
delimiter
A literal string used as a separator between the concatenated values.
Example
> SELECTstring_agg(name, ', ') AS names_list
FROM employee;
+--------------------------+
| names_list |
+--------------------------+
| Alice, Bob, Bob, Charlie |
+--------------------------+
> SELECTstring_agg(name, ', 'ORDER BY name DESC) AS names_list
FROM employee;
+--------------------------+
| names_list |
+--------------------------+
| Charlie, Bob, Bob, Alice |
+--------------------------+
> SELECTstring_agg(DISTINCT name, ', 'ORDER BY name DESC) AS names_list
FROM employee;
+--------------------------+
| names_list |
+--------------------------+
| Charlie, Bob, Alice |
+--------------------------+
sum
aggregate
sum(expression)
Returns the sum of all values in the specified column.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
Returns a map with the specified key-value pairs. The make_map function creates a map from two lists: one for keys and one for values. Each key must be unique and non-null.
key
For map: Expression to be used for key. Can be a constant, column, function, or any combination of arithmetic or string operators.
For make_map: The list of keys to be used in the map. Each key must be unique and non-null.
value
For map: Expression to be used for value. Can be a constant, column, function, or any combination of arithmetic or string operators.
For make_map: The list of values to be mapped to the corresponding keys.
Returns a list containing the value for the given key or an empty list if the key is not present in the map.
map
Map expression. Can be a constant, column, or function, and any combination of map operators.
key
Key to extract from the map. Can be a constant, column, or function, any combination of arithmetic or string operators, or a named expression of the previously listed.
Truncates a number to a whole number or truncated to the specified decimal places.
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_places is a positive integer, truncates digits to the
right of the decimal point. If decimal_places is a negative
integer, replaces digits to the left of the decimal point with 0.
Expression to cast. The expression can be a constant, column, or function, and any combination of operators.
datatype
data type name to cast to, as a string. The format is the same as that returned by [arrow_typeof]
Example
> selectarrow_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 |
+----+-----+-----+
> selectarrow_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
scalar
arrow_field(expression)
Returns a struct containing the Arrow field information of the expression, including name, data type, nullability, and metadata.
expression
Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators.
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.
expression
The expression to retrieve metadata from. Can be a column or other expression.
key
Optional. The specific metadata key to retrieve.
Example
> selectarrow_metadata(col) from table;
+----------------------------+
| arrow_metadata(table.col) |
+----------------------------+
| {k: v} |
+----------------------------+
> selectarrow_metadata(col, 'k') from table;
+-------------------------------+
| arrow_metadata(table.col, 'k')|
+-------------------------------+
| v |
+-------------------------------+
arrow_try_cast
scalar
arrow_try_cast(expression, datatype)
Casts a value to a specific data type, returning NULL if the cast fails.
expression
Expression to cast. The expression can be a constant, column, or function, and any combination of operators.
datatype
data type name to cast to, as a string. The format is the same as that returned by [arrow_typeof]
Example
> selectarrow_try_cast('123', 'Int64') as a,
arrow_try_cast('not_a_number', 'Int64') as b;
+-----+------+
| a | b |
+-----+------+
| 123 | NULL |
+-----+------+
arrow_typeof
scalar
arrow_typeof(expression)
Returns the name of the underlying data type of the expression.
expression
Expression to evaluate. The expression can be a constant, column, or function, and any combination of operators.
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.
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
> selectcast_to_type('42', NULL::INTEGER) as a;
+----+
| a |
+----+
| 42 |
+----+
> selectcast_to_type(1 + 2, NULL::DOUBLE) as b;
+-----+
| b |
+-----+
| 3.0 |
+-----+
file_row_index
scalar
file_row_index()
Returns the zero-based row offset within the source file that produced the current row. The value is scoped to one file, so rows from different files in the same scan can have the same row index. This function is intended to be rewritten at file-scan time. If the input file is not known (for example, if this function is evaluated outside a file scan, or was not pushed down into one), direct evaluation returns an error.
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').
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
> create tabletest( 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 tabletest(struct_col) as values
({outer: {inner_val: 42}});
> select struct_col['outer']['inner_val'] as result from test;
+--------+
| result |
+--------+
| 42 |
+--------+
input_file_name
scalar
input_file_name()
Returns the path of the input file that produced the current row. Note: file paths/URIs may be sensitive metadata depending on your environment. This function is intended to be rewritten at file-scan time (when the file is known). If the input file is not known (for example, if this function is evaluated outside a file scan, or was not pushed down into one), direct evaluation returns an error.
Example
SELECTinput_file_name() FROM t;
try_cast_to_type
scalar
try_cast_to_type(expression, reference)
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.
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
> selecttry_cast_to_type('123', NULL::INTEGER) as a,
try_cast_to_type('not_a_number', NULL::INTEGER) as b;
+-----+------+
| a | b |
+-----+------+
| 123 | NULL |
+-----+------+
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.
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).
Relative rank of the current row: (number of rows preceding or peer with the current row) / (total rows).
Example
-- Example usage of the cume_dist window function:SELECT salary,
cume_dist() OVER (ORDER BY salary) AS cume_dist
FROM employees;
+--------+-----------+
| salary | cume_dist |
+--------+-----------+
| 30000 | 0.33 |
| 50000 | 0.67 |
| 70000 | 1.00 |
+--------+-----------+
dense_rank
window
dense_rank()
Returns the rank of the current row without gaps. This function ranks rows in a dense manner, meaning consecutive ranks are assigned even for identical values.
Example
-- Example usage of the dense_rank window function:SELECT department,
salary,
dense_rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
+-------------+--------+------------+
| department | salary | dense_rank |
+-------------+--------+------------+
| Sales | 70000 | 1 |
| Sales | 50000 | 2 |
| Sales | 50000 | 2 |
| Sales | 30000 | 3 |
| Engineering | 90000 | 1 |
| Engineering | 80000 | 2 |
+-------------+--------+------------+
ntile
window
ntile(expression)
Integer ranging from 1 to the argument value, dividing the partition as equally as possible
expression
An integer describing the number groups the partition should be split into
Returns the percentage rank of the current row within its partition. The value ranges from 0 to 1 and is computed as (rank - 1) / (total_rows - 1).
Example
-- Example usage of the percent_rank window function:SELECT employee_id,
salary,
percent_rank() OVER (ORDER BY salary) AS percent_rank
FROM employees;
+-------------+--------+---------------+
| employee_id | salary | percent_rank |
+-------------+--------+---------------+
| 1 | 30000 | 0.00 |
| 2 | 50000 | 0.50 |
| 3 | 70000 | 1.00 |
+-------------+--------+---------------+
rank
window
rank()
Returns the rank of the current row within its partition, allowing gaps between ranks. This function provides a ranking similar to row_number, but skips ranks for identical values.
Example
-- Example usage of the rank window function:SELECT department,
salary,
rank() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
+-------------+--------+------+
| department | salary | rank |
+-------------+--------+------+
| Sales | 70000 | 1 |
| Sales | 50000 | 2 |
| Sales | 50000 | 2 |
| Sales | 30000 | 4 |
| Engineering | 90000 | 1 |
| Engineering | 80000 | 2 |
+-------------+--------+------+
row_number
window
row_number()
Number of the current row within its partition, counting from 1.
Example
-- Example usage of the row_number window function:SELECT department,
salary,
row_number() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
+-------------+--------+---------+
| department | salary | row_num |
+-------------+--------+---------+
| Sales | 70000 | 1 |
| Sales | 50000 | 2 |
| Sales | 50000 | 3 |
| Sales | 30000 | 4 |
| Engineering | 90000 | 1 |
| Engineering | 80000 | 2 |
+-------------+--------+---------+
Regular Expression Functions 5
regexp_count
scalar
regexp_count(str, regexp[, start[, flags]])
Returns the number of matches that a regular expression has in a string.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
regexp
Regular expression to operate on. Can be a constant, column, or function, and any combination of operators.
start
Optional start position (the first position is 1) to search for the regular expression. Can be a constant, column, or function.
flags
Optional regular expression flags that control the behavior of the regular expression. Refer to the flags reference above for supported flags.
Returns the slope of the linear regression line for non-null pairs in aggregate columns. Given input column Y and X: regr_slope(Y, X) returns the slope (k in Y = k*X + b) using minimal RSS fitting.
expression_y
Dependent variable expression to operate on. Can be a constant, column, or function, and any combination of operators.
expression_x
Independent variable expression to operate on. Can be a constant, column, or function, and any combination of operators.
Trims the specified trim string from the start and end of a string. If no trim string is provided, all spaces are removed from the start and end of the input string.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
trim_str
String expression to operate on. Can be a constant, column, or function, and any combination of operators. Default is a space.
Trims the specified trim string from the beginning of a string. If no trim string is provided, spaces are removed from the start of the input string.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
trim_str
String expression to trim from the beginning of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is a space.
Replaces all occurrences of a specified substring in a string with a new substring.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
substr
Substring expression to replace in the input string. Substring expression to operate on. Can be a constant, column, or function, and any combination of operators.
replacement
Replacement substring expression to operate on. Can be a constant, column, or function, and any combination of operators.
Trims the specified trim string from the end of a string. If no trim string is provided, all spaces are removed from the end of the input string.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
trim_str
String expression to trim from the end of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Default is a space.
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
substr
Substring to test for.
strpos
scalar
strpos(str, substr)
Returns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
substr
Substring expression to search for.
Also written:
position(substr in origstr)
Also called instr, position.
substr
scalar
substr(str, start_pos[, length])
Extracts a substring of a specified number of characters from a specific starting position in a string.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
start_pos
Character position to start the substring at. The first character in the string has a position of 1. If the start position is less than 1, it is treated as if it is before the start of the string and the (absolute) number of characters before position 1 is subtracted from length (if given). For example, substr('abc', -3, 6) returns 'ab'.
length
Number of characters to extract. If not specified, returns the rest of the string after the start position.
Also written:
substring(str from start_pos for length)
Also called substring.
substr_index
scalar
substr_index(str, delim, count)
Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
delim
The string to find in str to split str.
count
The number of times to search for the delimiter. Can be either a positive or negative number.
Performs character-wise substitution based on a mapping.
str
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
from
The characters to be replaced.
to
The characters to replace them with. Each character in from that is found in str is replaced by the character at the same index in to. Any characters in from that don't have a corresponding character in to are removed. If a character appears more than once in from, the first occurrence determines the mapping.
Returns a struct using the specified name and input expressions pairs. For information on comparing and ordering struct values (including NULL handling), see Comparison and Ordering.
expression_n_name
Name of the column field. Must be a constant string.
expression_n_input
Expression to include in the output struct. Can be a constant, column, or function, and any combination of arithmetic or string operators.
Example
> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
> selectnamed_struct('field_a', a, 'field_b', b) from t;
+-------------------------------------------------------+
| named_struct(Utf8("field_a"),t.a,Utf8("field_b"),t.b) |
+-------------------------------------------------------+
| {field_a: 1, field_b: 2} |
| {field_a: 3, field_b: 4} |
+-------------------------------------------------------+
struct
scalar
struct(expression1[, ..., expression_n])
Returns a struct using the specified input expressions optionally named. Fields in the returned struct use the optional name or the cN naming convention. For example: c0, c1, c2, etc. For information on comparing and ordering struct values (including NULL handling), see Comparison and Ordering.
expression1, expression_n
Expression to include in the output struct. Can be a constant, column, or function, any combination of arithmetic or string operators.
Example
> select * from t;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
| 3 | 4 |
+---+---+
-- use default names `c0`, `c1`
> selectstruct(a, b) from t;
+-----------------+
| struct(t.a,t.b) |
+-----------------+
| {c0: 1, c1: 2} |
| {c0: 3, c1: 4} |
+-----------------+
-- name the first field `field_a`selectstruct(a as field_a, b) from t;
+--------------------------------------------------+
| named_struct(Utf8("field_a"),t.a,Utf8("c1"),t.b) |
+--------------------------------------------------+
| {field_a: 1, c1: 2} |
| {field_a: 3, c1: 4} |
+--------------------------------------------------+
Also called row.
Time and Date Functions 19
current_date
scalar
current_date()
Returns the current date in the session time zone. The current_date() return value is determined at query time and will return the same date, no matter when in the query plan the function executes.
Also called today.
current_time
scalar
current_time()
Returns the current time in the session time zone. The current_time() return value is determined at query time and will return the same time, no matter when in the query plan the function executes. The time zone can be a value like +00:00, 'Europe/London' etc.
Calculates time intervals and returns the start of the interval nearest to the specified timestamp. Use date_bin to downsample time series data by grouping rows into time-based "bins" or "windows" and applying an aggregate or selector function to each window. For example, if you "bin" or "window" data into 15 minute intervals, an input timestamp of 2023-01-01T18:18:18Z will be updated to the start time of the 15 minute bin it is in: 2023-01-01T18:15:00Z.
interval
Bin interval.
expression
Time expression to operate on. Can be a constant, column, or function.
origin_timestamp
Optional. Starting point used to determine bin boundaries. If not specified defaults 1970-01-01T00:00:00Z (the UNIX epoch in UTC). The following intervals are supported:
nanoseconds
microseconds
milliseconds
seconds
minutes
hours
days
weeks
months
years
century
Example
-- Bin the timestamp into 1 day intervals
> SELECTdate_bin(interval'1 day', time) as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z') t(time);
+---------------------+
| bin |
+---------------------+
| 2023-01-01T00:00:00 |
| 2023-01-03T00:00:00 |
+---------------------+
2 row(s) fetched.
-- Bin the timestamp into 1 day intervals starting at 3AM on 2023-01-01
> SELECTdate_bin(interval'1 day', time, '2023-01-01T03:00:00') as bin
FROM VALUES ('2023-01-01T18:18:18Z'), ('2023-01-03T19:00:03Z') t(time);
+---------------------+
| bin |
+---------------------+
| 2023-01-01T03:00:00 |
| 2023-01-03T03:00:00 |
+---------------------+
2 row(s) fetched.
-- Bin the time into 15 minute intervals starting at 1 min
> SELECTdate_bin(interval'15 minutes', time, TIME '00:01:00') as bin
FROM VALUES (TIME '02:18:18'), (TIME '19:00:03') t(time);
+----------+
| bin |
+----------+
| 02:16:00 |
| 18:46:00 |
+----------+
2 row(s) fetched.
date_part
scalar
date_part(part, expression)
Returns the specified part of the date as an integer.
part
Part of the date to return. The following date parts are supported:
year
isoyear (ISO 8601 week-numbering year)
quarter (emits value in inclusive range [1, 4] based on which quartile of the year the date is in)
month
week (week of the year)
day (day of the month)
hour
minute
second
millisecond
microsecond
nanosecond
dow (day of the week where Sunday is 0)
doy (day of the year)
epoch (seconds since Unix epoch for timestamps/dates, total seconds for intervals)
isodow (ISO 8601 day of the week where Monday is 1 and Sunday is 7)
expression
Time expression to operate on. Can be a constant, column, or function.
Converts an integer to RFC3339 timestamp format (YYYY-MM-DDT00:00:00.000000000Z). Integers and unsigned integers are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z) return the corresponding timestamp.
expression
The expression to operate on. Can be a constant, column, or function, and any combination of operators.
timezone
Optional timezone to use when converting the integer to a timestamp. If not provided, the default timezone is UTC.
Returns the current timestamp in the system configured timezone (None by default). The now() return value is determined at query time and will return the same timestamp, no matter when in the query plan the function executes.
Also called current_timestamp.
to_char
scalar
to_char(expression, format)
Returns a string representation of a date, time, timestamp or duration based on a strftime format. Unlike the PostgreSQL equivalent of this function numerical formatting is not supported.
expression
Expression to operate on. Can be a constant, column, or function that results in a date, time, timestamp or duration.
format
A strftime format string to use to convert the expression.
Converts a value to a date (YYYY-MM-DD). Supports strings, numeric and timestamp types as input. Strings are parsed as YYYY-MM-DD (e.g. '2023-07-20') if no strftime formats are provided. Integers and doubles are interpreted as days since the unix epoch (1970-01-01T00:00:00Z). Returns the corresponding date. Note: to_date returns Date32, which represents its values as the number of days since unix epoch(1970-01-01) stored as signed 32 bit value. The largest supported date value is 9999-12-31.
expression
String expression to operate on. Can be a constant, column, or function, and any combination of operators.
format_n
Optional strftime format strings to use to parse the expression. Formats will be tried in the order
they appear with the first successful one being returned. If none of the formats successfully parse the expression
an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.
Converts a timestamp with a timezone to a timestamp without a timezone (with no offset or timezone information). This function handles daylight saving time changes.
expression
Time expression to operate on. Can be a constant, column, or function.
Example
> SELECTto_local_time('2024-04-01T00:00:20Z'::timestamp);
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20 |
+---------------------------------------------+
> SELECTto_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels');
+---------------------------------------------+
| to_local_time(Utf8("2024-04-01T00:00:20Z")) |
+---------------------------------------------+
| 2024-04-01T00:00:20 |
+---------------------------------------------+
> SELECT
time,
arrow_typeof(time) as type,
to_local_time(time) as to_local_time,
arrow_typeof(to_local_time(time)) as to_local_time_type
FROM (
SELECT'2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels'AS time
);
+---------------------------+----------------------------------+---------------------+--------------------+
| time | type | to_local_time | to_local_time_type |
+---------------------------+----------------------------------+---------------------+--------------------+
| 2024-04-01T00:00:20+02:00 | Timestamp(ns, "Europe/Brussels") | 2024-04-01T00:00:20 | Timestamp(ns) |
+---------------------------+----------------------------------+---------------------+--------------------+
# combine `to_local_time()` with `date_bin()` to bin on boundaries in the timezone rather
# than UTC boundaries
> SELECTdate_bin(interval'1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AS date_bin;
+---------------------+
| date_bin |
+---------------------+
| 2024-04-01T00:00:00 |
+---------------------+
> SELECTdate_bin(interval'1 day', to_local_time('2024-04-01T00:00:20Z'::timestamp AT TIME ZONE 'Europe/Brussels')) AT TIME ZONE 'Europe/Brussels'AS date_bin_with_timezone;
+---------------------------+
| date_bin_with_timezone |
+---------------------------+
| 2024-04-01T00:00:00+02:00 |
+---------------------------+
to_time
scalar
to_time(expression[, format1, ..., format_n])
Converts a value to a time (HH:MM:SS.nnnnnnnnn). Supports strings and timestamps as input. Strings are parsed as HH:MM:SS, HH:MM:SS.nnnnnnnnn, or HH:MM if no strftime formats are provided. Timestamps will have the time portion extracted. Returns the corresponding time. Note: to_time returns Time64(Nanosecond), which represents the time of day in nanoseconds since midnight.
expression
String or Timestamp expression to operate on. Can be a constant, column, or function, and any combination of operators.
format_n
Optional strftime format strings to use to parse the expression. Formats will be tried in the order
they appear with the first successful one being returned. If none of the formats successfully parse the expression
an error will be returned.
Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000000<TZ>) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no strftime formats are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z). Note: to_timestamp returns Timestamp(ns, TimeZone) where the time zone is the session time zone. The supported range for integer input is between-9223372037 and 9223372036. Supported range for string input is between 1677-09-21T00:12:44.0 and 2262-04-11T23:47:16.0. Please use to_timestamp_seconds for the input outside of supported bounds. The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc.
expression
Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
format_n
Optional strftime format strings to use to parse the expression.
Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully
parse the expression an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.
Note: parsing of named timezones (e.g. 'America/New_York') using %Z is
only supported at the end of the string preceded by a space.
Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000000<TZ>) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no strftime formats are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as microseconds since the unix epoch (1970-01-01T00:00:00Z). The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc.
expression
Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
format_n
Optional strftime format strings to use to parse the expression.
Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully
parse the expression an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.
Note: parsing of named timezones (e.g. 'America/New_York') using %Z is
only supported at the end of the string preceded by a space.
Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000<TZ>) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no strftime formats are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as milliseconds since the unix epoch (1970-01-01T00:00:00Z). The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc.
expression
Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
format_n
Optional strftime format strings to use to parse the expression.
Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully
parse the expression an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.
Note: parsing of named timezones (e.g. 'America/New_York') using %Z is
only supported at the end of the string preceded by a space.
Converts a value to a timestamp (YYYY-MM-DDT00:00:00.000000000<TZ>) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no strftime formats are provided. Strings that parse without a time zone are treated as if they are in the session time zone. Integers, unsigned integers, and doubles are interpreted as nanoseconds since the unix epoch (1970-01-01T00:00:00Z). The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc.
expression
Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
format_n
Optional strftime format strings to use to parse the expression.
Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully
parse the expression an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.
Note: parsing of named timezones (e.g. 'America/New_York') using %Z is
only supported at the end of the string preceded by a space.
Converts a value to a timestamp (YYYY-MM-DDT00:00:00<TZ>) in the session time zone. Supports strings, integer, unsigned integer, and double types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no strftime formats are provided. Strings that parse without a time zone are treated as if they are in the session time zone, or UTC if no session time zone is set. Integers, unsigned integers, and doubles are interpreted as seconds since the unix epoch (1970-01-01T00:00:00Z). The session time zone can be set using the statement SET TIMEZONE = 'desired time zone'. The time zone can be a value like +00:00, 'Europe/London' etc.
expression
Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
format_n
Optional strftime format strings to use to parse the expression.
Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully
parse the expression an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.
Note: parsing of named timezones (e.g. 'America/New_York') using %Z is
only supported at the end of the string preceded by a space.
Converts a value to seconds since the unix epoch (1970-01-01T00:00:00). Supports strings, dates, timestamps, integer, unsigned integer, and float types as input. Strings are parsed as RFC3339 (e.g. '2023-07-20T05:44:00') if no strftime formats are provided. Integers, unsigned integers, and floats are interpreted as seconds since the unix epoch (1970-01-01T00:00:00).
expression
Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
format_n
Optional strftime format strings to use to parse the expression. Formats will be tried in the order they appear with the first successful one being returned. If none of the formats successfully parse the expression an error will be returned. NULL formats are skipped. If every format is NULL the result is NULL.