Aug 13: Context layer webinar
Chalk home page
  1. References
  2. Chalk SQL Functions Reference

Chalk SQL Reference

This reference documents the complete set of Chalk SQL functions for federated queries across data sources, offline stores, and the Chalk catalog.

abs(value)
Returns the absolute value of a number.
Examples
SELECT abs(-42);

| abs(-42) |
| -------- |
| 42       |
Overloads
acos(x)
Calculates the inverse cosine in radians.
Parameters
x:
double
The input value.
arccosh(x)
Calculates the inverse hyperbolic cosine.
Parameters
x:
double
The input value.
arcsinh(x)
Calculates the inverse hyperbolic sine.
Parameters
x:
double
The input value.
arctanh(x)
Calculates the inverse hyperbolic tangent.
Parameters
x:
double
The input value.
asin(x)
Calculates the inverse sine in radians.
Parameters
x:
double
The input value.
atan(x)
Calculates the arctangent of a number in radians.
Parameters
x:
double
The input value.
atan2(y, x)
Calculates the arctangent of y/x in radians, handling quadrant correctly.
Parameters
y:
double
The second input value.
x:
double
The input value.
bankers_round(value, ...)
Rounds a number using banker's rounding, optionally to a specified number of decimal places.
Examples
SELECT bankers_round(2.5);

| bankers_round(2.5) |
| ------------------ |
| 2.0                |
SELECT bankers_round(42.125, 2);

| bankers_round(42.125, 2) |
| ------------------------ |
| 42.12                    |
Overloads
cbrt(x)
Calculates the cube root of a number.
Examples
SELECT cbrt(27);

| cbrt(27) |
| -------- |
| 3.0      |
Overloads
ceil(value)
Alias for ceiling.
Examples
SELECT ceil(42.1);

| ceil(42.1) |
| ---------- |
| 43.0       |
ceiling(value)
Returns the smallest integer greater than or equal to the given number.
Examples
SELECT ceiling(42.1);

| ceiling(42.1) |
| ------------- |
| 43.0          |
Overloads
clamp(value, low, high)
Constrains a value to a minimum and maximum range.
Examples
SELECT clamp(12, 0, 10);

| clamp(12, 0, 10) |
| ---------------- |
| 10               |
Overloads
cos(x)
Calculates the cosine of an angle in radians.
Parameters
x:
double
The input value.
cosh(x)
Calculates the hyperbolic cosine of a number.
Parameters
x:
double
The input value.
cot(x)
Calculates the cotangent of an angle in radians.
Parameters
x:
double
The input value.
degrees(radians)
Converts radians to degrees.
Parameters
radians:
double
The angle in radians.
Examples
SELECT degrees(pi());

| degrees(pi()) |
| ------------- |
| 180.0         |
e()
Returns the mathematical constant e (Euler's number).
Parameters
None
exp(x)
Calculates e raised to the power of a number.
Parameters
x:
double
The input value.
floor(value)
Returns the largest integer less than or equal to the given number.
Examples
SELECT floor(42.9);

| floor(42.9) |
| ----------- |
| 42.0        |
Overloads
is_finite(value)
Checks if a floating-point number is finite (not NaN or infinite).
Parameters
value:
double
The input value.
is_infinite(value)
Checks if a floating-point number is infinite.
Parameters
value:
double
The input value.
is_nan(value)
Checks if a floating-point number is NaN (Not a Number).
Parameters
value:
double
The input value.
ln(x)
Calculates the natural logarithm of a number.
Parameters
x:
double
The input value.
log(...)
Calculates the logarithm of a number with a specified or default base.
Examples
SELECT log(10, 100);

| log(10, 100) |
| ------------ |
| 2.0          |
Overloads
log10(x)
Calculates the base-10 logarithm of a number.
Parameters
x:
double
The input value.
log1p(x)
Calculates the natural logarithm of (1 + x), accurate for small x.
Parameters
x:
double
The input value.
log2(x)
Calculates the base-2 logarithm of a number.
Parameters
x:
double
The input value.
nan()
Returns a floating point NaN (Not a Number) value.
Parameters
None
negate(value)
Returns the negative value of a number.
Overloads
pi()
Returns the mathematical constant π (pi).
Parameters
None
pow(base, exponent)
Raises a number to the power of another number.
Parameters
base:
double
The base value.
exponent:
double
The exponent to raise the base to.
Examples
SELECT pow(2.0, 8.0);

| pow(2.0, 8.0) |
| ------------- |
| 256.0         |
power(base, exponent)
Raises the first number to the power of the second number.
Parameters
base:
double
The base value.
exponent:
double
The exponent to raise the base to.
Examples
SELECT power(2.0, 8.0);

| power(2.0, 8.0) |
| --------------- |
| 256.0           |
radians(degrees)
Converts degrees to radians.
Parameters
degrees:
double
The angle in degrees.
Examples
SELECT radians(180.0);

| radians(180.0)    |
| ----------------- |
| 3.141592653589793 |
rand()
Generates a random float between 0 and 1.
Parameters
None
random(n)
Generates a pseudo-random float or integer.
Examples
SELECT random();

| random()                  |
| ------------------------- |
| <random double in [0, 1)> |
Overloads
round(value, ...)
Rounds a floating point number, optionally to a specified number of decimal places.
Examples
SELECT round(42.5);

| round(42.5) |
| ----------- |
| 43.0        |
SELECT round(42.125, 2);

| round(42.125, 2) |
| ---------------- |
| 42.13            |
Overloads
round_sig_figs(value, digits)
Rounds a number to n significant figures.
Parameters
value:
double
The input value.
digits:
int64
The number of significant digits to keep.
secure_random(lower, upper)
Generates a cryptographically secure random float or number.
Overloads
sign(value)
Returns the sign of a number as -1, 0, or 1.
Examples
SELECT sign(-42);

| sign(-42) |
| --------- |
| -1        |
Overloads
sin(x)
Calculates the sine of an angle in radians.
Parameters
x:
double
The input value.
sqrt(x)
Calculates the square root of a number.
Parameters
x:
double
The input value.
Examples
SELECT sqrt(144.0);

| sqrt(144.0) |
| ----------- |
| 12.0        |
tan(x)
Calculates the tangent of an angle in radians.
Parameters
x:
double
The input value.
tanh(x)
Calculates the hyperbolic tangent of a number.
Parameters
x:
double
The input value.
trunc(value)
Alias for truncate.
truncate(value, ...)
Truncates a floating-point number, optionally to a specified number of decimal places.
Examples
SELECT truncate(42.987);

| truncate(42.987) |
| ---------------- |
| 42.0             |
SELECT truncate(42.987, 2);

| truncate(42.987, 2) |
| ------------------- |
| 42.98               |
Overloads
%...
Returns the remainder after dividing the first value by the second.
Examples
SELECT 7 % 3;

| 7 % 3 |
| ----- |
| 1     |
Overloads
x * y
Multiplies numeric values, or scales a duration by a numeric value.
Examples
SELECT 6 * 7;

| 6 * 7 |
| ----- |
| 42    |
Overloads
x ** y
basic binary ** operation
Parameters
x:
double
The input value.
y:
double
The second input value.
x + y
Adds numeric, temporal, or interval values.
Examples
SELECT 2 + 3;

| 2 + 3 |
| ----- |
| 5     |
Overloads
x - y
Subtracts numeric, temporal, or interval values.
Examples
SELECT 7 - 3;

| 7 - 3 |
| ----- |
| 4     |
Overloads
-x
Negates a numeric value.
Examples
SELECT -42;

| -42 |
| --- |
| -42 |
Overloads
x / y
Divides numeric values, or divides a duration by a numeric value.
Examples
SELECT 7.0 / 2.0;

| 7.0 / 2.0 |
| --------- |
| 3.5       |
Overloads
x // y
floor division
Overloads
base ^ exponent
Alias for pow.
Examples
SELECT pow(2.0, 8.0);

| pow(2.0, 8.0) |
| ------------- |
| 256.0         |
and(x, y)
Performs logical AND operation on two boolean values.
Parameters
x:
bool
The input value.
y:
bool
The second input value.
between(value, min, max)
Checks if a value is between two bounds (inclusive).

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Parameters
value:
$T_ord
The input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
min:
$T_ord
The minimum bound, inclusive. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
max:
$T_ord
The maximum bound, inclusive. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
coalesce(value1, value2)
Returns the first non-null value from a list of arguments.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
value1:
$T
The first value. T means matching inputs and outputs use the same type.
value2:
$T...
The second value. T means matching inputs and outputs use the same type.
Examples
SELECT coalesce(NULL, 'fallback');

| coalesce(NULL, 'fallback') |
| -------------------------- |
| fallback                   |
distinct_from(x, y)
Checks if two values are distinct, treating NULL values as different from non-NULL values.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
x:
$T
The input value. T means matching inputs and outputs use the same type.
y:
$T
The second input value. T means matching inputs and outputs use the same type.
eq(x, y)
Returns whether two values are equal.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT eq(1, 1);

| eq(1, 1) |
| -------- |
| true     |
Overloads
gt(x, y)
Returns whether the first value is greater than the second.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT gt(2, 1);

| gt(2, 1) |
| -------- |
| true     |
Overloads
gte(x, y)
Returns whether the first value is greater than or equal to the second.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT gte(2, 2);

| gte(2, 2) |
| --------- |
| true      |
Overloads
if_else(condition, true_value, false_value)
Returns the second argument if the condition is true, otherwise returns the third argument.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
condition:
bool
The boolean condition to evaluate.
true_value:
$T
The value returned when the condition is true. T means matching inputs and outputs use the same type.
false_value:
$T
The value returned when the condition is false. T means matching inputs and outputs use the same type.
is_null(value)
Checks if the input is null.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
value:
$T
The input value. T means matching inputs and outputs use the same type.
lt(x, y)
Returns whether the first value is less than the second.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT lt(1, 2);

| lt(1, 2) |
| -------- |
| true     |
Overloads
lte(x, y)
Returns whether the first value is less than or equal to the second.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT lte(2, 2);

| lte(2, 2) |
| --------- |
| true      |
Overloads
neq(x, y)
Returns whether two values are not equal.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT neq(1, 2);

| neq(1, 2) |
| --------- |
| true      |
Overloads
nullif(value1, value2)
Returns NULL if both arguments compare equal; otherwise returns the first argument.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
value1:
$T
The first value. T means matching inputs and outputs use the same type.
value2:
$T
The second value. T means matching inputs and outputs use the same type.
python_eq(x, y)
Checks if two values are equal, treating NULL values as equal to other NULL values.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
x:
$T
The input value. T means matching inputs and outputs use the same type.
y:
$T
The second input value. T means matching inputs and outputs use the same type.
python_ne(x, y)
Checks if two values are not equal, treating NULL values as different from non-NULL values.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
x:
$T
The input value. T means matching inputs and outputs use the same type.
y:
$T
The second input value. T means matching inputs and outputs use the same type.
scalar_max(x, y)
Returns the maximum of two scalar values.
Overloads
scalar_min(x, y)
Returns the minimum of two scalar values.
Overloads
x != y
basic binary != operation

Type parameters. T means matching inputs and outputs use the same type.

Parameters
x:
$T
The input value. T means matching inputs and outputs use the same type.
y:
$T
The second input value. T means matching inputs and outputs use the same type.
x < y
basic binary < operation

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Parameters
x:
$T_ord
The input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
y:
$T_ord
The second input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
x <= y
basic binary <= operation

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Parameters
x:
$T_ord
The input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
y:
$T_ord
The second input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
x == y
basic binary == operation

Type parameters. T means matching inputs and outputs use the same type.

Parameters
x:
$T
The input value. T means matching inputs and outputs use the same type.
y:
$T
The second input value. T means matching inputs and outputs use the same type.
x > y
basic binary > operation

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Parameters
x:
$T_ord
The input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
y:
$T_ord
The second input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
x >= y
basic binary >= operation

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Parameters
x:
$T_ord
The input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
y:
$T_ord
The second input value. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.
bit_count(x, bits)
Counts the number of bits in `x`.
Parameters
x:
int64
The input value.
bits:
int64
The bit width to operate within.
bitwise_and(x, y)
Performs bitwise AND operation on two integer values.
Overloads
bitwise_arithmetic_shift_right(x, shift)
Returns the arithmetic right shift operation on x in 2’s complement representation. shift must not be negative.
Parameters
x:
int64
The input value.
shift:
int64
bitwise_left_shift(x, shift)
Returns the left shifted value of x.
Overloads
bitwise_logical_shift_right(x, shift, bits)
Returns the logical right shift operation on x (treated as bits-bit integer) shifted by shift. shift must not be negative.
Parameters
x:
int64
The input value.
shift:
int64
bits:
int64
The bit width to operate within.
bitwise_not(x)
Performs bitwise NOT operation (complement) on an integer value.
Overloads
bitwise_or(x, y)
Performs bitwise OR operation on two integer values.
Overloads
bitwise_right_shift(x, shift)
Returns the logical right shifted value of x.
Overloads
bitwise_right_shift_arithmetic(x, shift)
Returns the arithmetic right shift value of x.
Overloads
bitwise_shift_left(x, shift, bits)
Returns the left shift operation on x (treated as bits-bit integer) shifted by shift. shift must not be negative.
Parameters
x:
int64
The input value.
shift:
int64
bits:
int64
The bit width to operate within.
bitwise_xor(x, y)
Performs bitwise XOR operation on two integer values.
Overloads
x & y
Applies boolean AND or bitwise AND to two values.
Examples
SELECT TRUE & FALSE;

| TRUE & FALSE |
| ------------ |
| false        |
SELECT 6 & 3;

| 6 & 3 |
| ----- |
| 2     |
Overloads
arg1 << arg2
x | y
Applies boolean OR or bitwise OR to two values.
Examples
SELECT TRUE | FALSE;

| TRUE \| FALSE |
| ------------- |
| true          |
SELECT 4 | 1;

| 4 \| 1 |
| ------ |
| 5      |
Overloads
~x
Applies boolean NOT or bitwise NOT to a value.
Examples
SELECT ~TRUE;

| ~TRUE |
| ----- |
| false |
Overloads
▲~x
▲~x
▲~x
▲~x
▲~x
▲~x
all_match(array, predicate)
Returns true if every element of the array satisfies the predicate.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
any_match(array, predicate)
Returns true if any element of the array satisfies the predicate.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_add(array1, array2)
Returns element-wise sum of two arrays.
Overloads
array_all(array)
Returns true if all elements in a boolean array are true.
Overloads
array_any(array)
Returns true if any element in a boolean array is true.
Overloads
array_append(array, element)
Adds an element to the end of an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_apply(x, y)
Alias for transform.
array_arg_max(array)
Returns the index of the maximum element in an array.
Overloads
array_arg_min(array)
Returns the index of the minimum element in an array.
Overloads
array_average(array)
Calculates the average of numeric values in an array.
Overloads
array_compact(array)
Removes all null values from an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_constructor(values)
Creates an array from zero or more values.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT array_constructor(1, 2, 3);

| array_constructor(1, 2, 3) |
| -------------------------- |
| [1, 2, 3]                  |
Overloads
array_contains(array, element)
Alias for contains.
array_contains_element(array, element)
Returns true if the array contains the given element.
Overloads
array_cum_sum(array)
Calculates the cumulative sum of numeric values in an array.
Overloads
array_distinct(array)
Returns an array with duplicate elements removed, preserving order.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_duplicates(array)
Returns an array containing only the duplicate elements from the input array.
Overloads
array_except(array1, array2)
Returns elements from the first array that are not present in the second array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_filter(array, predicate)
Filters an array using a callback function predicate.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
array:
large_list<item: $T>
The input array or list. T means matching inputs and outputs use the same type.
predicate:
($T) => bool
T means matching inputs and outputs use the same type.
array_frequency(array)
Returns a map of elements to their frequency counts in an array.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
array:
large_list<item: $T>
The input array or list. T means matching inputs and outputs use the same type.
array_has_duplicates(array)
Checks if an array contains duplicate elements.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_indexof(array, element)
Alias for array_position.
array_intersect(array1, array2)
Returns the intersection of two arrays (elements present in both arrays).

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_join(array, delimiter, ...)
Joins array elements into a string using a separator and optional null replacement.

Type parameters. K is the key type of the input map.

Examples
SELECT array_join(array_constructor('a', 'b', 'c'), ',');

| array_join(array_constructor('a', 'b', 'c'), ',') |
| ------------------------------------------------- |
| a,b,c                                             |
Overloads
array_length(array)
Alias for cardinality.
array_max(array)
Returns the maximum element in an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_median(array)
Calculates the median value of numeric elements in an array.
Overloads
array_min(array)
Returns the minimum element in an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_mode(array, ...)
Returns the most frequently occurring element in an array.
Examples
SELECT array_mode(array_constructor(1, 2, 2, 3));

| array_mode(array_constructor(1, 2, 2, 3)) |
| ----------------------------------------- |
| 2                                         |
Overloads
array_multimode(array)
Returns all most frequently occurring elements in an array.
Overloads
array_n_unique(array)
Returns the number of distinct non-null elements in an array.
Overloads
array_normalize(array, p)
Computes the L_p normalization of a Vector.
Overloads
array_position(array, element, ...)
Returns the 1-based position of an element in an array.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT array_position(array_constructor('a', 'b', 'a'), 'a');

| array_position(array_constructor('a', 'b', 'a'), 'a') |
| ----------------------------------------------------- |
| 1                                                     |
Overloads
array_reduce(array, initialState, ...+2)
Applies a reduce function to each element in an array and returns the accumulated value.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type. V is the value type of the input map.

Parameters
array:
large_list<item: $T>
The input array or list. T means matching inputs and outputs use the same type.
initialState:
$U
U is a second generic value type.
inputFunction:
($U, $T) => $U
T means matching inputs and outputs use the same type. U is a second generic value type.
outputFunction:
($U) => $V
V is the value type of the input map. U is a second generic value type.
array_reduce_while(array, initial_state, function)
Reduces an array with Python-style loop control. Lambda returns (new_state, control_code, result), where control_code is 0=normal, 1=return, 2=continue, 3=break. Returns row(final_state, control_code, result|null).

Type parameters. T means matching inputs and outputs use the same type. S is the callback state type. R is the callback result type.

Parameters
array:
large_list<item: $T>
The input array or list. T means matching inputs and outputs use the same type.
initial_state:
$S
The initial accumulator state. S is the callback state type.
function:
($S, $T) => struct<state: $S, control: int64, result: $R>
The callback function applied to the elements. T means matching inputs and outputs use the same type. R is the callback result type. S is the callback state type.
array_remove(array, element)
Removes all occurrences of a specified element from an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
array_reverse(array)
Returns the array with elements in reverse order.
Overloads
array_slice(...)
Returns a slice from an array, string, or binary value.

Type parameters. E is the element type of the input array or list.

Examples
SELECT array_slice(array_constructor(1, 2, 3, 4), 2, 3);

| array_slice(array_constructor(1, 2, 3, 4), 2, 3) |
| ------------------------------------------------ |
| [2, 3, 4]                                        |
Overloads
array_sort(array, ...)
Sorts an array in ascending order, optionally using a key extraction function.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.

Examples
SELECT array_sort(array_constructor(3, 1, 2));

| array_sort(array_constructor(3, 1, 2)) |
| -------------------------------------- |
| [1, 2, 3]                              |
Overloads
array_sort_desc(array, ...)
Sorts an array in descending order, optionally using a key extraction function.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.

Examples
SELECT array_sort_desc(array_constructor(3, 1, 2));

| array_sort_desc(array_constructor(3, 1, 2)) |
| ------------------------------------------- |
| [3, 2, 1]                                   |
Overloads
array_stddev(array, sample)
Calculates the standard deviation of numeric values in an array.
Overloads
array_sum(array)
Calculates the sum of numeric values in an array.
Overloads
array_to_string(array, delimiter)
Alias for array_join.
array_transform(array, function)
Applies a transformation function to each element in an array and returns a new array.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.

Parameters
array:
large_list<item: $T>
The input array or list. T means matching inputs and outputs use the same type.
function:
($T) => $U
The callback function applied to the elements. T means matching inputs and outputs use the same type. U is a second generic value type.
array_unique(x)
Alias for array_distinct.
array_value(values)
arrays_overlap(array1, array2)
Checks if two arrays have any elements in common.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
arrays_union(array1, array2)
Returns a list of the distinct elements of the common elements

Type parameters. T means matching inputs and outputs use the same type.

Overloads
cardinality(...)
Returns the number of elements in an array or entries in a map.

Type parameters. V is the value type of the input map. K is the key type of the input map.

Examples
SELECT cardinality(array_constructor(1, 2, 3));

| cardinality(array_constructor(1, 2, 3)) |
| --------------------------------------- |
| 3                                       |
Overloads
combinations(array, n)
Returns all possible combinations of the given size from an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
contains(array, element)
Checks if a list contains a specific element.

Type parameters. V is the value type of the input map.

Parameters
array:
large_list<item: $V>
The input array or list. V is the value type of the input map.
element:
$V
The element to search for. V is the value type of the input map.
element_at(...)
Returns an element from an array or map.

Type parameters. E is the element type of the input array or list. K is the key type of the input map. V is the value type of the input map.

Examples
SELECT element_at(array_constructor('a', 'b'), 2);

| element_at(array_constructor('a', 'b'), 2) |
| ------------------------------------------ |
| b                                          |
Overloads
find_first(array, ...)
Returns the first element in an array that matches the given predicate function.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
find_first_index(array, ...)
Returns the 1-based index of the first element in an array that matches the given predicate function.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
flatten(array)
Flattens a nested array by one level, combining all sub-arrays into a single array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
greatest(value1, value2)
Returns the largest value from a list of values.

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Examples
SELECT greatest(3.0, 7.0, 5.0);

| greatest(3.0, 7.0, 5.0) |
| ----------------------- |
| 7.0                     |
SELECT greatest(3, 7, 5);

| greatest(3, 7, 5) |
| ----------------- |
| 7                 |
Overloads
least(value1, value2)
Returns the smallest value from a list of values.

Type parameters. T_ord can be any orderable type, such as a number, string, date, timestamp, or boolean.

Examples
SELECT least(3.0, 7.0, 5.0);

| least(3.0, 7.0, 5.0) |
| -------------------- |
| 3.0                  |
SELECT least(3, 7, 5);

| least(3, 7, 5) |
| -------------- |
| 3              |
Overloads
list_apply(x, y)
Alias for transform.
list_avg(x)
Alias for array_average.
list_concat(...)
Alias for concat.
list_contains(array, element)
Alias for contains.
list_count(array)
Alias for cardinality.
list_distinct(x)
Alias for array_distinct.
list_extract(...)
Either:
  • Returns the element at the specified index in a list.
  • Retrieves the value associated with a key from a map.

Type parameters. E is the element type of the input array or list. K is the key type of the input map. V is the value type of the input map.

Overloads
list_filter(list, predicate)
Filters a list using a callback function predicate.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
list_has(array, element)
Alias for contains.
list_has_any(array1, array2)
Alias for arrays_overlap.
list_intersect(array1, array2)
Alias for array_intersect.
list_max(x)
Alias for array_max.
list_median(array)
Alias for array_median.
list_min(x)
Alias for array_min.
list_mode(array, ...)
Alias for array_mode.
Examples
SELECT array_mode(array_constructor(1, 2, 2, 3));

| array_mode(array_constructor(1, 2, 2, 3)) |
| ----------------------------------------- |
| 2                                         |
list_pack(values)
list_position(array, element)
Alias for array_position.
list_slice(...)
Alias for array_slice.
list_sort(x, ...)
Alias for array_sort.
list_stddev_pop(array, sample)
Alias for array_stddev.
list_sum(x)
Alias for array_sum.
list_transform(x, y)
Alias for transform.
list_unique(x)
Alias for array_distinct.
list_value(values)
ngrams(array, n)
Returns all N-grams (contiguous subsequences of length n) from an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
none_match(array, predicate)
Returns true if no element of the array satisfies the predicate.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
python_element_at(array, index)
Returns the element at the specified Python-style index in a list.

Type parameters. E is the element type of the input array or list.

Overloads
python_range(...)
Creates a range of integers with Python-like semantics.
Overloads
python_zip(list1, list2, ...)
Combines multiple lists element-wise into a list of paired structures with Python semantics.

Type parameters. E is the element type of the input array or list.

Overloads
reduce(array, initialState, ...+2)
Alias for array_reduce.
remove_nulls(array)
Removes all null values from an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
repeat(element, count)
Returns an array containing the element repeated count times.

Type parameters. E is the element type of the input array or list.

Parameters
element:
$E
The element to search for. E is the element type of the input array or list.
count:
int32
The number of values to return or repeat.
sequence(start, stop, ...)
Generates a sequence of integers with an optional step.
Examples
SELECT sequence(1, 5);

| sequence(1, 5)  |
| --------------- |
| [1, 2, 3, 4, 5] |
Overloads
shuffle(array)
Randomly shuffles the elements of an array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
slice(array, start, length)
Returns a slice of a list starting at the given position for the specified length.

Type parameters. E is the element type of the input array or list.

Overloads
subscript(array, index)
Returns the element at the specified 1-based index in the array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
transform(array, function)
Applies a transformation function to each element in an array and returns a new array.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.

Overloads
trim_array(array, n)
Removes the last n elements from the array.

Type parameters. T means matching inputs and outputs use the same type.

Overloads
zip(array1, array2)
Combines two lists element-wise into a list of paired structures.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.

Parameters
array1:
large_list<item: $T>
The first input array. T means matching inputs and outputs use the same type.
array2:
large_list<item: $U>
The second input array. U is a second generic value type.
zip_with(array1, array2, function)
Combines two lists element-wise using a callback function to transform paired elements.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type. R is the callback result type.

Parameters
array1:
large_list<item: $T>
The first input array. T means matching inputs and outputs use the same type.
array2:
large_list<item: $U>
The second input array. U is a second generic value type.
function:
($T, $U) => $R
The callback function applied to the elements. T means matching inputs and outputs use the same type. U is a second generic value type. R is the callback result type.
array1 && array2
operator alias for list_has_any

Type parameters. E is the element type of the input array or list.

Overloads
binary_to_vector(binary, precision)
Returns vector from binary.
Overloads
cosine_similarity_vector(vector1, vector2)
Returns the cosine similarity between the vectors.
Overloads
dot_product_vector(vector1, vector2)
Returns the dot product between the vectors.
Overloads
scale_vector(vector, factor)
Returns a scaled vector.
Overloads
vector_to_binary(vector, precision)
Returns binary of vector.
Overloads
boost_regexp_findall(string, pattern)
Finds all matches of a regular expression pattern in a string and returns them as a list.
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
boyer_moore_search(string, substring)
Searches for the first occurrence of a pattern in a string using the Boyer-Moore algorithm. Returns the 0-based byte offset of the first match, or -1 if not found.
Parameters
string:
large_string
The input string.
substring:
large_string
The substring to search for.
chr(n)
Converts an integer to its corresponding ASCII character.
Parameters
n:
int64
The Unicode code point to convert to a character.
codepoint(string)
Returns the Unicode code point of the only character of the string.
Overloads
concat(...)
Concatenates two or more strings or lists.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT concat('feature', '_', 'store');

| concat('feature', '_', 'store') |
| ------------------------------- |
| feature_store                   |
Overloads
count_matches(string, pattern)
Counts the number of non-overlapping regex matches in a string.
Overloads
ends_with(string, substring)
Checks if a string ends with a specified suffix.
Parameters
string:
large_string
The input string.
substring:
large_string
The substring to search for.
Examples
SELECT ends_with('feature_store', 'store');

| ends_with('feature_store', 'store') |
| ----------------------------------- |
| true                                |
hamming_distance(string1, string2)
Returns the Hamming distance of string1 and string2, i.e. the number of positions at which the corresponding characters are different. Note that the two strings must have the same length.
Overloads
ilike(string, pattern)
Case-insensitive like
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
jaccard_similarity(string1, string2)
Calculates the Jaccard similarity coefficient between two strings based on character sets.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
jaro_winkler_distance(string1, string2, prefix_weight)
Calculates the Jaro-Winkler distance between two strings.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
prefix_weight:
double
The Jaro-Winkler prefix scaling factor.
knuth_morris_pratt_search(string, substring)
Searches for the first occurrence of a pattern in a string using the Knuth-Morris-Pratt algorithm. Returns the 0-based byte offset of the first match, or -1 if not found.
Parameters
string:
large_string
The input string.
substring:
large_string
The substring to search for.
len_bytes(string)
Returns the byte length of a string.
Overloads
length(...)
Returns the length of a string, binary, array, or map.

Type parameters. V is the value type of the input map.

Examples
SELECT length('chalk');

| length('chalk') |
| --------------- |
| 5               |
SELECT length(array_constructor(1, 2, 3));

| length(array_constructor(1, 2, 3)) |
| ---------------------------------- |
| 3                                  |
Overloads
levenshtein_distance(string1, string2)
Calculates the Levenshtein distance between two strings.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
like(string, pattern)
Checks if a string matches a pattern using SQL LIKE syntax with wildcards.
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
longest_common_subsequence(string1, string2)
Calculates the longest common subsequence between two strings.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
lower(string)
Converts a string to lowercase.
Parameters
string:
large_string
The input string.
Examples
SELECT lower('Chalk');

| lower('Chalk') |
| -------------- |
| chalk          |
lpad(...)
Left pads a string or binary value to a target length.
Examples
SELECT lpad('42', 5, '0');

| lpad('42', 5, '0') |
| ------------------ |
| 00042              |
Overloads
ltrim(string, ...)
Removes whitespace or specified characters from the left side of a string.
Examples
SELECT ltrim('  chalk');

| ltrim('  chalk') |
| ---------------- |
| chalk            |
Overloads
minijinja_render(template, context)
Render a Jinja2 template using the provided JSON context via minijinja.
Overloads
normalize(string, ...)
Normalizes a string using a Unicode normalization form.
Examples
SELECT normalize('chalk');

| normalize('chalk') |
| ------------------ |
| chalk              |
Overloads
octet_length(...)
Returns the byte length of a string or binary value.
Examples
SELECT octet_length('chalk');

| octet_length('chalk') |
| --------------------- |
| 5                     |
Overloads
partial_ratio(string1, string2)
Calculates the partial ratio similarity between two strings using fuzzy matching.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
prefix(x, y)
Alias for starts_with.
Examples
SELECT starts_with('feature_store', 'feature');

| starts_with('feature_store', 'feature') |
| --------------------------------------- |
| true                                    |
python_is_alnum(string)
Returns true if all characters in the string are alphanumeric and there is at least one character, matching Python's str.isalnum() behavior.
Overloads
python_is_alpha(string)
Returns true if all characters in the string are alphabetic and there is at least one character, matching Python's str.isalpha() behavior.
Overloads
python_is_digit(string)
Returns true if all characters are digit characters and there is at least one character, matching Python's str.isdigit() behavior.
Overloads
python_is_lower(string)
Returns true if all cased characters are lowercase and there is at least one cased character, matching Python's str.islower() behavior.
Overloads
python_is_numeric(string)
Returns true if all characters are numeric characters and there is at least one character, matching Python's str.isnumeric() behavior.
Overloads
python_is_space(string)
Returns true if all characters are whitespace and there is at least one character, matching Python's str.isspace() behavior.
Overloads
python_is_title(string)
Returns true if the string is titlecased and there is at least one cased character, matching Python's str.istitle() behavior.
Overloads
python_is_upper(string)
Returns true if all cased characters are uppercase and there is at least one cased character, matching Python's str.isupper() behavior.
Overloads
python_lstrip(string)
Strips leading Python whitespace (Unicode-aware), matching Python's str.lstrip() with no arguments.
Overloads
python_remove_prefix(string, prefix)
Removes the prefix from the string if present, matching Python's str.removeprefix().
Overloads
python_remove_suffix(string, suffix)
Removes the suffix from the string if present, matching Python's str.removesuffix().
Overloads
python_rstrip(string)
Strips trailing Python whitespace (Unicode-aware), matching Python's str.rstrip() with no arguments.
Overloads
python_string_slice(string, start, ...+2)
Slices a string with exact Python s[start:stop:step] semantics. All integer arguments are nullable (NULL = Python None / unspecified).
Overloads
python_strip(string)
Strips leading and trailing Python whitespace (Unicode-aware), matching Python's str.strip() with no arguments.
Overloads
regexp_extract(string, pattern, ...)
Returns the first substring matched by a regular expression.
Examples
SELECT regexp_extract('user_123', 'user_([0-9]+)', 1);

| regexp_extract('user_123', 'user_([0-9]+)', 1) |
| ---------------------------------------------- |
| 123                                            |
Overloads
regexp_extract_all(string, pattern, ...)
Returns all substrings matched by a regular expression.
Examples
SELECT regexp_extract_all('a1 b2 c3', '[a-z][0-9]');

| regexp_extract_all('a1 b2 c3', '[a-z][0-9]') |
| -------------------------------------------- |
| [a1, b2, c3]                                 |
Overloads
regexp_full_match(string, pattern)
Checks if a string matches a regular expression pattern.
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
regexp_like(string, pattern)
Checks if a string matches a regular expression pattern.
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
Examples
SELECT regexp_like('user_123', '^user_[0-9]+$');

| regexp_like('user_123', '^user_[0-9]+$') |
| ---------------------------------------- |
| true                                     |
regexp_matches(x, y)
Alias for regexp_like.
Examples
SELECT regexp_like('user_123', '^user_[0-9]+$');

| regexp_like('user_123', '^user_[0-9]+$') |
| ---------------------------------------- |
| true                                     |
regexp_replace(string, pattern, ...)
Replaces or removes substrings matched by a regular expression.
Examples
SELECT regexp_replace('user_123', '[0-9]+', '456');

| regexp_replace('user_123', '[0-9]+', '456') |
| ------------------------------------------- |
| user_456                                    |
Overloads
regexp_split(string, pattern)
Splits ``string`` using the regular expression ``pattern`` into a list of strings.
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
regexp_split_to_array(x, y)
Alias for regexp_split.
replace(string, search, ...)
Replaces or removes occurrences of a substring.
Examples
SELECT replace('hello chalk', 'hello', 'hi');

| replace('hello chalk', 'hello', 'hi') |
| ------------------------------------- |
| hi chalk                              |
Overloads
reverse(...)
Reverses a string, binary value, or array.

Type parameters. T means matching inputs and outputs use the same type.

Examples
SELECT reverse('chalk');

| reverse('chalk') |
| ---------------- |
| klahc            |
Overloads
rpad(...)
Right pads a string or binary value to a target length.
Examples
SELECT rpad('42', 5, '0');

| rpad('42', 5, '0') |
| ------------------ |
| 42000              |
Overloads
rtrim(string, ...)
Removes whitespace or specified characters from the right side of a string.
Examples
SELECT rtrim('chalk  ');

| rtrim('chalk  ') |
| ---------------- |
| chalk            |
Overloads
sequence_matcher_ratio(string1, string2)
Computes the similarity ratio between two strings using sequence matching.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
split(string, delimiter, ...)
Splits a string by a delimiter, optionally limiting the number of splits.
Examples
SELECT split('a,b,c', ',');

| split('a,b,c', ',') |
| ------------------- |
| [a, b, c]           |
Overloads
split_part(string, delimiter, index)
Splits a string by delimiter and returns the part at the specified index (1-based).
Parameters
string:
large_string
The input string.
delimiter:
large_string
The delimiter used to split or join the string.
index:
int64
The index to read.
Examples
SELECT split_part('features.user.id', '.', 2);

| split_part('features.user.id', '.', 2) |
| -------------------------------------- |
| user                                   |
starts_with(string, substring)
Checks if a string starts with a specified prefix.
Parameters
string:
large_string
The input string.
substring:
large_string
The substring to search for.
Examples
SELECT starts_with('feature_store', 'feature');

| starts_with('feature_store', 'feature') |
| --------------------------------------- |
| true                                    |
string_split(...)
Alias for split.
string_split_regex(x, y)
Alias for regexp_split.
string_to_array(...)
Alias for split.
string_to_bytes_base64(x)
Alias for from_base64.
string_to_bytes_hex(x)
Alias for from_hex.
string_to_bytes_utf8(x)
Alias for to_utf8.
strpos(string, substring, ...)
Returns the position of a substring within a string.
Examples
SELECT strpos('chalk data', 'data');

| strpos('chalk data', 'data') |
| ---------------------------- |
| 7                            |
Overloads
strrpos(string, substring, ...)
Returns the position of the last matching substring within a string.
Examples
SELECT strrpos('chalk data data', 'data');

| strrpos('chalk data data', 'data') |
| ---------------------------------- |
| 12                                 |
Overloads
substr(string, start, ...)
Returns a substring starting at the specified position, optionally with a specified length.
Examples
SELECT substr('feature_store', 9);

| substr('feature_store', 9) |
| -------------------------- |
| store                      |
SELECT substr('feature_store', 1, 7);

| substr('feature_store', 1, 7) |
| ----------------------------- |
| feature                       |
Overloads
substring(...)
Alias for substr.
Examples
SELECT substr('feature_store', 9);

| substr('feature_store', 9) |
| -------------------------- |
| store                      |
SELECT substr('feature_store', 1, 7);

| substr('feature_store', 1, 7) |
| ----------------------------- |
| feature                       |
titlecase(string)
Converts a string to title case.
Overloads
token_set_ratio(string1, string2)
Calculates the token set ratio similarity between two strings using fuzzy matching.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
token_sort_ratio(string1, string2)
Calculates the token sort ratio similarity between two strings using fuzzy matching.
Parameters
string1:
large_string
The first input string.
string2:
large_string
The second input string.
trail(string, N)
Returns the last N characters of the input string, up to at most the length of string.
Overloads
trim(string, ...)
Removes whitespace or specified characters from both ends of a string.
Examples
SELECT trim('  chalk  ');

| trim('  chalk  ') |
| ----------------- |
| chalk             |
SELECT trim('xychalkyx', 'xy');

| trim('xychalkyx', 'xy') |
| ----------------------- |
| chalk                   |
Overloads
unidecode_normalize(string)
Normalizes Unicode characters to their closest ASCII equivalents with whitespace normalization.
Parameters
string:
large_string
The input string.
unidecode_to_ascii(string)
Converts Unicode characters to their closest ASCII equivalents.
Parameters
string:
large_string
The input string.
upper(string)
Converts a string to uppercase.
Parameters
string:
large_string
The input string.
Examples
SELECT upper('chalk');

| upper('chalk') |
| -------------- |
| CHALK          |
word_stem(word, ...)
Returns the stem of a word using stemming algorithms.
Overloads
zfill(string, width)
Left-pads a string with zeros to the given width.
Overloads
zi_split_part(string, delimiter, index)
Splits a string by delimiter and returns the part at the specified zero-based index.
Parameters
string:
large_string
The input string.
delimiter:
large_string
The delimiter used to split or join the string.
index:
int64
The index to read.
string !~~ pattern
operator alias for not like
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
string !~~* pattern
operator alias for not ilike
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
x ^@ y
Alias for starts_with.
Examples
SELECT starts_with('feature_store', 'feature');

| starts_with('feature_store', 'feature') |
| --------------------------------------- |
| true                                    |
||...
Alias for concat.
string ~~ pattern
operator alias for like
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
string ~~* pattern
Case-insensitive like
Parameters
string:
large_string
The input string.
pattern:
large_string
The pattern to match.
current_date()
Returns the current date.
Parameters
None
date(...)
Converts a timestamp or string to a date.
Examples
SELECT date('2024-01-02');

| date('2024-01-02') |
| ------------------ |
| 2024-01-02         |
Overloads
date_add(...)
Adds a duration or time-unit interval to a date or timestamp.
Examples
SELECT date_add('day', 7, TIMESTAMP '2024-01-02 00:00:00');

| date_add('day', 7, TIMESTAMP '2024-01-02 00:00:00') |
| --------------------------------------------------- |
| 2024-01-09 00:00:00                                 |
Overloads
date_diff(unit, timestamp1, timestamp2)
Returns the difference between two timestamps in the specified unit.
Parameters
unit:
large_string
The time unit, such as 'second', 'day', or 'month'.
timestamp1:
timestamp[us, tz=UTC]
The first timestamp.
timestamp2:
timestamp[us, tz=UTC]
The second timestamp.
date_format(timestamp, format)
Formats a timestamp as a string using the specified format.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
format:
large_string
The format string.
date_trunc(unit, x)
Truncates a timestamp to the specified time unit (e.g., day, month, year).
Parameters
unit:
large_string
The time unit, such as 'second', 'day', or 'month'.
x:
timestamp[us, tz=UTC]
The input value.
day(date)
Extracts the day of the month from a date or timestamp.
Overloads
day_of_month(date)
Extracts the day of the month from a timestamp.
Overloads
day_of_week(date)
Extracts the day of the week from a timestamp.
Overloads
day_of_year(date)
Extracts the day of the month from a timestamp.
Overloads
days_in_month(date)
Returns the number of days in the month of the given date.
Overloads
dow(date)
Extracts the day of the week from a date or timestamp.
Overloads
doy(date)
Extracts the day of the year from a date or timestamp.
Overloads
format_datetime(timestamp, format)
Formats a datetime using a specified format string.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
format:
large_string
The format string.
format_datetime_in_timezone(timestamp, format, timezone)
Formats a UTC timestamp as a string using a Joda format pattern in the given IANA timezone.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
format:
large_string
The format string.
timezone:
large_string
The IANA timezone name, such as 'America/New_York'.
from_iso8601_date(string)
Parses an ISO 8601 date string into a date value.
Parameters
string:
large_string
The input string.
from_iso8601_timestamp(string)
Parses an ISO 8601 timestamp string into a datetime object.
Parameters
string:
large_string
The input string.
from_unix_seconds(unixtime)
Alias for from_unixtime.
from_unixtime(unixtime, ...)
Converts a UNIX timestamp to a timestamp, optionally with a time zone offset.
Examples
SELECT from_unixtime(1700000000);

| from_unixtime(1700000000) |
| ------------------------- |
| 2023-11-14 22:13:20 UTC   |
Overloads
hour(timestamp)
Extracts the hour from a timestamp.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
hour_in_timezone(timestamp, timezone)
Extracts the hour from a timestamp on a given timezone.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
timezone:
large_string
The IANA timezone name, such as 'America/New_York'.
is_business_day(date)
Returns true if the date falls on a weekday (Monday-Friday).
Overloads
is_federal_holiday(date)
Returns True if the given timestamp is a federal holiday.
Overloads
last_day_of_month(date)
Returns the last day of the month for a given date.
Parameters
date:
timestamp[us, tz=UTC]
The input date or timestamp.
make_date(year, month, day)
Constructs a date from year, month, and day fields.
Parameters
year:
int64
month:
int64
day:
int64
millisecond(timestamp)
Extracts the millisecond from a timestamp.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
minute(timestamp)
Extracts the minute from a timestamp.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
month(date)
Extracts the month from a timestamp.
Overloads
parse_datetime(string, format)
Converts an ISO 8601 string into a datetime.
Parameters
string:
large_string
The input string.
format:
large_string
The format string.
quarter(timestamp)
Extracts the quarter of the year from a timestamp.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
second(timestamp)
Extracts the second from a timestamp.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
to_days(days)
Constructs a duration with the given number of days.
Overloads
to_hours(hours)
Constructs a duration with the given number of hours.
Overloads
to_iso8601(timestamp)
Converts a datetime to an ISO 8601 string format.
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
Examples
SELECT to_iso8601(TIMESTAMP '2024-01-02 03:04:05');

| to_iso8601(TIMESTAMP '2024-01-02 03:04:05') |
| ------------------------------------------- |
| 2024-01-02T03:04:05.000000Z                 |
to_microseconds(...)
Converts a duration to microseconds or constructs a duration from microseconds.
Overloads
to_milliseconds(...)
Converts a duration to milliseconds or constructs a duration from milliseconds.
Overloads
to_minutes(minutes)
Constructs a duration with the given number of minutes.
Overloads
to_seconds(seconds)
Constructs a duration with the given number of seconds.
Overloads
to_unix_nanos(timestamp)
Converts a timestamp or date to nanoseconds since the Unix epoch (UTC). Throws if the value falls outside the int64 ns range (~1677 to ~2262).
Overloads
to_unixtime(timestamp)
Converts a timestamp to Unix timestamp (seconds since epoch).
Parameters
timestamp:
timestamp[us, tz=UTC]
The input timestamp.
total_seconds(duration)
Returns the length of the input duration in seconds.
Parameters
duration:
duration[us]
The input duration.
week(date)
Extracts the week of the year from a timestamp.
Overloads
week_of_year(date)
Extracts the week of the year from a timestamp.
Overloads
year(date)
Extracts the year from a timestamp.
Overloads
year_of_week(date)
Extracts the year of the ISO week from a date.
Overloads
yow(date)
Extracts the year of the ISO week from a date.
Overloads
all_keys_match(map, key)
Returns true if all keys in the map match the given predicate.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($K) => bool
The map key to read or update. K is the key type of the input map.
any_keys_match(map, key)
Returns true if any key in the map matches the given predicate.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($K) => bool
The map key to read or update. K is the key type of the input map.
any_values_match(map, key)
Returns true if any value in the map matches the given predicate.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($V) => bool
The map key to read or update. V is the value type of the input map.
map_contains(map, key)
Checks if a map contains any keys that match a given condition.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($k) => bool
The map key to read or update.
map_filter(map, key)
Filters the entries of a map using a callback predicate.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($K, $V) => bool
The map key to read or update. K is the key type of the input map. V is the value type of the input map.
map_from_arrays(keys, values)
Creates a map from separate arrays of keys and values.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Overloads
map_get(map, key)
Retrieves a value from a map by key.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Overloads
map_key_exists(map, key)
Checks if a map contains any keys that match a given condition.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
$K
The map key to read or update. K is the key type of the input map.
map_keys(map)
Returns all keys from a map as a list.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
map_keys_by_top_n_values(map, key)
Returns the keys of a map ordered by their top N highest values.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
int64
The map key to read or update.
map_subset(map, key)
Returns a subset of a map containing only the specified keys.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Overloads
map_top_n(map, key)
Returns the top N entries from a map ordered by value.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
int64
The map key to read or update.
map_top_n_keys(map, key)
Returns the top N keys from a map ordered by their values.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
int64
The map key to read or update.
map_top_n_values(map, key)
Returns the top N values from a map ordered by value.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
int64
The map key to read or update.
map_values(map)
Returns all values from a map as a list.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
map_zip_with(map, key, function)
Merges two maps by applying a function to each matched key and pair of values.

Type parameters. K is the key type of the input map.

Parameters
map:
map<$K, $V1>
The input map. K is the key type of the input map.
key:
map<$K, $V2>
The map key to read or update. K is the key type of the input map.
function:
($K, $V1, $V2) => $V3
The callback function applied to the elements. K is the key type of the input map.
no_keys_match(map, key)
Returns true if no keys in the map match the given predicate.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($K) => bool
The map key to read or update. K is the key type of the input map.
no_values_match(map, key)
Returns true if no values in the map match the given predicate.

Type parameters. K is the key type of the input map. V is the value type of the input map.

Parameters
map:
map<$K, $V>
The input map. K is the key type of the input map. V is the value type of the input map.
key:
($V) => bool
The map key to read or update. V is the value type of the input map.
transform_keys(map, key)
Returns a map with keys transformed by the given function.

Type parameters. V is the value type of the input map.

Parameters
map:
map<$K1, $V>
The input map. V is the value type of the input map.
key:
($K1, $V) => $K2
The map key to read or update. V is the value type of the input map.
transform_values(map, key)
Returns a map with values transformed by the given function.

Type parameters. K is the key type of the input map.

Parameters
map:
map<$K, $V1>
The input map. K is the key type of the input map.
key:
($K, $V1) => $V2
The map key to read or update. K is the key type of the input map.
beta_cdf(a, b, value)
Compute the Beta cdf with given a, b parameters: P(N < value; a, b). The a, b parameters must be positive real numbers and value must be a real value (all of type DOUBLE). The value must lie on the interval [0, 1].
Parameters
a:
double
b:
double
value:
double
The input value.
binomial_cdf(numberOfTrials, successProbability, value)
Compute the Binomial cdf with given numberOfTrials and successProbability (for a single trial): P(N < value). The successProbability must be real value in [0, 1], numberOfTrials and value must be positive integers with numberOfTrials greater or equal to value.
Parameters
numberOfTrials:
int64
successProbability:
double
value:
int64
The input value.
cauchy_cdf(median, scale, value)
Compute the Cauchy cdf with given parameters median and scale (gamma): P(N; median, scale). The scale parameter must be a positive double. The value parameter must be a double on the interval [0, 1].
Parameters
median:
double
scale:
double
value:
double
The input value.
chi_squared_cdf(df, value)
Compute the Chi-square cdf with given df (degrees of freedom) parameter: P(N < value; df). The df parameter must be a positive real number, and value must be a non-negative real value (both of type DOUBLE).
Parameters
df:
double
value:
double
The input value.
f_cdf(df1, df2, value)
Compute the F cdf with given df1 (numerator degrees of freedom) and df2 (denominator degrees of freedom) parameters: P(N < value; df1, df2). The numerator and denominator df parameters must be positive real numbers. The value must be a non-negative real number.
Parameters
df1:
double
df2:
double
value:
double
The input value.
gamma_cdf(shape, scale, value)
Compute the Gamma cdf with given shape and scale parameters: P(N < value; shape, scale). The shape and scale parameters must be positive real numbers. The value must be a non-negative real number.
Parameters
shape:
double
scale:
double
value:
double
The input value.
inverse_beta_cdf(a, b, p)
Compute the inverse of the Beta cdf with given a, b parameters for the cumulative probability (p): P(N < n). The a, b parameters must be positive double values. The probability p must lie on the interval [0, 1].
Parameters
a:
double
b:
double
p:
double
inverse_binomial_cdf(numberOfTrials, successProbability, p)
Compute the inverse of the Binomial cdf with given numberOfTrials and successProbability (of a single trial) the cumulative probability (p): P(N <= n). The successProbability and p must be real values in [0, 1] and the numberOfTrials must be a positive integer.
Parameters
numberOfTrials:
int64
successProbability:
double
p:
double
inverse_cauchy_cdf(median, scale, p)
Compute the inverse of the Cauchy cdf with given parameters median and scale (gamma) for the probability p. The scale parameter must be a positive double. The probability p must be a double on the interval [0, 1].
Parameters
median:
double
scale:
double
p:
double
inverse_chi_squared_cdf(df, p)
Compute the inverse of the Chi-square cdf with given df (degrees of freedom) parameter for the cumulative probability (p): P(N < n). The df parameter must be positive real values. The probability p must lie on the interval [0, 1].
Parameters
df:
double
p:
double
inverse_f_cdf(df1, df2, p)
Compute the inverse of the Fisher F cdf with a given df1 (numerator degrees of freedom) and df2 (denominator degrees of freedom) parameters for the cumulative probability (p): P(N < n). The numerator and denominator df parameters must be positive real numbers. The probability p must lie on the interval [0, 1].
Parameters
df1:
double
df2:
double
p:
double
inverse_gamma_cdf(shape, scale, p)
Alias for gamma_cdf.
inverse_laplace_cdf(mean, scale, p)
Compute the inverse of the Laplace cdf with given mean and scale parameters for the cumulative probability (p): P(N < n). The mean must be a real value and the scale must be a positive real value (both of type DOUBLE). The probability p must lie on the interval [0, 1].
Parameters
mean:
double
scale:
double
p:
double
inverse_normal_cdf(mean, sd, p)
Compute the inverse of the Normal cdf with given mean and standard deviation (sd) for the cumulative probability (p): P(N < n). The mean must be a real value and the standard deviation must be a real and positive value (both of type DOUBLE). The probability p must lie on the interval (0, 1).
Parameters
mean:
double
sd:
double
p:
double
inverse_poisson_cdf(lambda, p)
Compute the inverse of the Poisson cdf with given lambda (mean) parameter for the cumulative probability (p). It returns the value of n so that: P(N <= n; lambda) = p. The lambda parameter must be a positive real number (of type DOUBLE). The probability p must lie on the interval [0, 1).
Parameters
lambda:
double
p:
double
inverse_weibull_cdf(a, b, p)
Compute the inverse of the Weibull cdf with given parameters a, b for the probability p. The a, b parameters must be positive double values. The probability p must be a double on the interval [0, 1].
Parameters
a:
double
b:
double
p:
double
laplace_cdf(mean, scale, value)
Compute the Laplace cdf with given mean and scale parameters: P(N < value; mean, scale). The mean and value must be real values and the scale parameter must be a positive value (all of type DOUBLE).
Parameters
mean:
double
scale:
double
value:
double
The input value.
normal_cdf(mean, sd, value)
Compute the Normal cdf with given mean and standard deviation (sd): P(N < value; mean, sd). The mean and value must be real values and the standard deviation must be a real and positive value (all of type DOUBLE).
Parameters
mean:
double
sd:
double
value:
double
The input value.
poisson_cdf(lambda, value)
Compute the Poisson cdf with given lambda (mean) parameter: P(N <= value; lambda). The lambda parameter must be a positive real number (of type DOUBLE) and value must be a non-negative integer.
Parameters
lambda:
double
value:
int32
The input value.
weibull_cdf(a, b, value)
Compute the Weibull cdf with given parameters a, b: P(N <= value). The a and b parameters must be positive doubles and value must also be a double.
Parameters
a:
double
b:
double
value:
double
The input value.
width_bucket(x, bound1, ...+2)
Returns the bucket number for a value in a histogram with uniform bucket widths.
Parameters
x:
double
The input value.
bound1:
double
bound2:
double
n:
int64
The number of equal-width buckets.
wilson_interval_lower(successes, trials, z)
Returns the lower bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score z.
Parameters
successes:
int64
The number of successes.
trials:
int64
The number of trials.
z:
double
The z-score for the confidence level.
wilson_interval_upper(successes, trials, z)
Returns the upper bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score z.
Parameters
successes:
int64
The number of successes.
trials:
int64
The number of trials.
z:
double
The z-score for the confidence level.
cast_to_json(value)
Converts an arbitrary json-compatible value into json.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
value:
$T
The input value. T means matching inputs and outputs use the same type.
get_json_value(json, json_path)
Extracts a value from JSON or a JSON string using a path expression.
Overloads
get_json_value_strict(json, json_path, ...)
Extracts a value from JSON or a JSON string using a path expression and records invalid JSON errors.
Overloads
is_json_scalar(json)
Returns whether a JSON value or JSON string represents a scalar.
Overloads
json_array_contains(json, value)
Checks if a value exists in a JSON array.
Overloads
json_array_length(json)
Returns the number of elements in a JSON array.
Parameters
json:
extension<arrow.json>
The input JSON value or JSON string.
json_extract_array(json, json_path)
Extracts an array from JSON string using a path expression.
Overloads
json_extract_scalar(json, json_path)
Extracts a scalar value from JSON or a JSON string using a JSONPath expression.
Overloads
json_format(json)
Encodes a JSON value into a string
Parameters
json:
extension<arrow.json>
The input JSON value or JSON string.
json_is_null(json, json_path)
Returns true when a JSON path resolves to a JSON null value.
Overloads
json_parse(string)
Converts an arbitrary value into a JSON string
Parameters
string:
large_string
The input string.
json_size(json, value)
Returns the size of a JSON object or array at a path.
Overloads
json_stringify(json)
Encodes a JSON value into a string. This is an alias for `json_format`.
Parameters
json:
extension<arrow.json>
The input JSON value or JSON string.
jsonify(value)
Converts an arbitrary value into a JSON string

Type parameters. T means matching inputs and outputs use the same type.

Parameters
value:
$T
The input value. T means matching inputs and outputs use the same type.
orjson_json_handle(json, on_null, ...+6)
Dispatches a JSON value to the lazy callback matching its runtime JSON type.

Type parameters. R is the callback result type.

Overloads
orjson_json_is_valid(json)
Returns true when the input is syntactically valid JSON according to orjson.loads.
Overloads
python_json_array_get(json, index)
Extracts an element from a Python JSON list using Python integer indexing, returning null for non-lists and out-of-range indices.
Overloads
python_json_extract_bool(json, ...)
Either:
  • Extracts a Python JSON boolean, returning null for non-booleans.
  • Extracts a Python JSON boolean after following object keys, returning null for non-booleans.
Overloads
python_json_extract_dict(json, ...)
Either:
  • Extracts a Python JSON dict, returning null for non-dicts.
  • Extracts a Python JSON dict after following object keys, returning null for non-dicts.
Overloads
python_json_extract_float(json, ...)
Either:
  • Extracts a Python JSON float candidate, returning null for non-numbers.
  • Extracts a Python JSON float candidate after following object keys, returning null for non-numbers.
Overloads
python_json_extract_int(json, ...)
Either:
  • Extracts a Python JSON integer, returning null for non-integers.
  • Extracts a Python JSON integer after following object keys, returning null for non-integers.
Overloads
python_json_extract_list(json, ...)
Either:
  • Extracts a Python JSON list, returning null for non-lists.
  • Extracts a Python JSON list after following object keys, returning null for non-lists.
Overloads
python_json_extract_string(json, ...)
Either:
  • Extracts a Python JSON string, returning null for non-strings.
  • Extracts a Python JSON string after following object keys, returning null for non-strings.
Overloads
python_json_handle(json, on_null, ...+6)
Dispatches a JSON value to the lazy callback matching its runtime JSON type.

Type parameters. R is the callback result type.

Overloads
python_json_is_valid(json)
Returns true when the input is syntactically valid JSON.
Overloads
python_json_object_get(json, key, keys)
Extracts a nested field from a Python JSON object using string keys, returning null for non-objects and missing keys.
Overloads
python_json_size(json)
Returns the top-level size of a Python JSON object or array, or 0 for scalar values.
Overloads
python_json_value(json)
Parses a Python JSON value, returning null for invalid JSON.
Overloads
regorus_eval(policy, query, ...+2)
Evaluate a Rego query against a policy via the regorus interpreter. Arguments are (policy, query, input_json, data_json); the result is the regorus QueryResults serialized as a JSON string.
Overloads
from_base64url(string)
Decodes a Base64url-encoded string to binary data.
Parameters
string:
large_string
The input string.
to_base64url(binary)
Encodes binary data to a Base64url string.
Parameters
binary:
large_binary
The input binary value.
url_decode(value)
Decodes URL-encoded characters in a string.
Parameters
value:
large_string
The input value.
url_encode(value)
URL-encodes special characters in a string.
Parameters
value:
large_string
The input value.
url_extract_fragment(url)
Extracts the fragment portion (after #) from a URL.
Parameters
url:
large_string
The URL to request.
url_extract_host(url)
Returns the host from a URL.
Parameters
url:
large_string
The URL to request.
url_extract_parameter(url, name)
Extracts the value of a specific query parameter from a URL.
Parameters
url:
large_string
The URL to request.
name:
large_string
url_extract_path(url)
Returns the path from a URL.
Parameters
url:
large_string
The URL to request.
url_extract_port(url)
Extracts the port number from a URL.
Parameters
url:
large_string
The URL to request.
url_extract_protocol(url)
Returns the protocol from a URL.
Parameters
url:
large_string
The URL to request.
url_extract_query(url)
Extracts the query string portion (after ?) from a URL.
Parameters
url:
large_string
The URL to request.
fetch_file(uri)
Downloads a file from object storage (gs://, s3://, abfs://) and returns the raw bytes. Returns null on error.
Parameters
uri:
large_string
fetch_file_raw(uri)
Downloads a file from object storage (gs://, s3://, abfs://) and returns a struct with data, success, error, and metadata.
Parameters
uri:
large_string
http_request(url, method, ...+5)
Makes an HTTP request and returns the response.
Overloads
openai_complete(prompt)
Makes a completion request to OpenAI's chat API and returns the response.
Parameters
prompt:
large_string
model:
large_string
 = NULL
api_server:
large_string
 = NULL
api_key:
large_string
 = NULL
max_tokens:
int64
 = NULL
temperature:
double
 = NULL
service_tier:
large_string
 = NULL
response_format:
large_string
 = NULL
chat_template_kwargs:
large_string
 = NULL
openai_complete_raw(prompt)
Makes a completion request to OpenAI's chat API and returns the raw JSON response string.
Parameters
prompt:
large_string
model:
large_string
 = NULL
api_server:
large_string
 = NULL
api_key:
large_string
 = NULL
max_tokens:
int64
 = NULL
temperature:
double
 = NULL
service_tier:
large_string
 = NULL
response_format:
large_string
 = NULL
chat_template_kwargs:
large_string
 = NULL
sagemaker_predict(body)
Invokes an AWS SageMaker endpoint for inference with the provided binary input and returns the binary output.
Parameters
body:
large_binary
The request body.
endpoint:
large_string
The name of the model endpoint to invoke.
content_type:
large_string
The request content type.
target_model:
large_string
target_variant:
large_string
aws_access_key_id_override:
large_string
aws_secret_access_key_override:
large_string
aws_session_token_override:
large_string
aws_role_arn_override:
large_string
aws_region_override:
large_string
aws_profile_name_override:
large_string
inference_component:
large_string
vertex_predict(body)
Invokes a Vertex AI endpoint for inference with binary input, returns binary output.
Parameters
body:
large_binary
The request body.
endpoint:
large_string
The name of the model endpoint to invoke.
content_type:
large_string
 = NULL
The request content type.
gcp_credentials_override:
large_string
 = NULL
dedicated_endpoint_dns:
large_string
 = NULL
method:
large_string
 = NULL
The HTTP method, such as 'GET' or 'POST'.
api_host:
large_string
 = NULL
geometry_as_geojson(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
geometry_from_geojson(geojson)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geojson:
large_string
The geometry as a GeoJSON string.
geometry_nearest_points(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_area(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_astext(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_boundary(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_buffer(geometry, distance)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
distance:
double
The buffer distance.
st_centroid(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_contains(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_convexhull(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_crosses(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_difference(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_dimension(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_disjoint(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_distance(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_endpoint(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_envelope(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_envelopeaspts(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_equals(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_exteriorring(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_geometries(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_geometryfromtext(wkt)
Geospatial (Presto-flavored Velox geometry function).
Parameters
wkt:
large_string
The geometry in WKT format.
st_geometryn(geometry, index)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
index:
int32
The index to read.
st_geometrytype(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_interiorringn(geometry, index)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
index:
int32
The index to read.
st_interiorrings(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_intersection(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_intersects(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_isclosed(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_isempty(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_isring(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_issimple(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_isvalid(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_length(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_linefromtext(wkt)
Geospatial (Presto-flavored Velox geometry function).
Parameters
wkt:
large_string
The geometry in WKT format.
st_linestring(points)
Geospatial (Presto-flavored Velox geometry function).
Parameters
points:
large_list<item: large_binary>
The array of point geometries.
st_multipoint(points)
Geospatial (Presto-flavored Velox geometry function).
Parameters
points:
large_list<item: large_binary>
The array of point geometries.
st_numgeometries(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_numinteriorring(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_numpoints(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_overlaps(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_point(x, y)
Geospatial (Presto-flavored Velox geometry function).
Parameters
x:
double
The input value.
y:
double
The second input value.
st_pointn(geometry, index)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
index:
int32
The index to read.
st_points(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_polygon(wkt)
Geospatial (Presto-flavored Velox geometry function).
Parameters
wkt:
large_string
The geometry in WKT format.
st_relate(geometry1, geometry2, relation)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
relation:
large_string
The DE-9IM intersection matrix pattern.
st_startpoint(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_symdifference(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_touches(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_union(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_within(geometry1, geometry2)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry1:
large_binary
The first input geometry, as WKB.
geometry2:
large_binary
The second input geometry, as WKB.
st_x(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_xmax(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_xmin(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_y(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_ymax(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
st_ymin(geometry)
Geospatial (Presto-flavored Velox geometry function).
Parameters
geometry:
large_binary
The input geometry, as WKB.
h3_cell_to_lat_lon_degrees(cell)
Calculates the lat-lon in degrees for a given h3 cell.
Parameters
cell:
large_string
The H3 cell identifier.
h3_cell_to_lat_lon_radians(cell)
Calculates the lat-lon for a given h3 cell.
Parameters
cell:
large_string
The H3 cell identifier.
h3_lat_lon_to_cell(lat, lon, resolution)
Calculates the h3 cell for a given lat-lon.
Overloads
avro_deserialize(schema, result_type, binary)
Alias for avro_to_struct.
avro_to_struct(schema, result_type, binary)
Converts Avro binary data to a structured format using an Avro schema.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
schema:
large_string
The schema definition.
result_type:
$T
A (possibly null) struct value whose type defines the result shape. T means matching inputs and outputs use the same type.
binary:
large_binary
The input binary value.
bytes_to_string_base64(x)
Alias for to_base64.
bytes_to_string_hex(x)
Alias for to_hex.
bytes_to_string_utf8(x)
Alias for from_utf8.
from_base64(string)
Decodes a Base64-encoded string to binary data.
Parameters
string:
large_string
The input string.
from_big_endian_32(varbinary)
Convert a 32-bit big-endian bytes value to an integer.
Overloads
from_big_endian_64(varbinary)
Convert a 64-bit big-endian bytes value to an integer.
Overloads
from_hex(string)
Converts a hexadecimal string to binary data.
Parameters
string:
large_string
The input string.
from_ieee754_32(binary)
Decodes the 32-bit big-endian binary representation of an IEEE 754 floating-point value.
Parameters
binary:
large_binary
The input binary value.
from_ieee754_64(binary)
Decodes the 64-bit big-endian binary representation of an IEEE 754 floating-point value.
Parameters
binary:
large_binary
The input binary value.
from_snappy(binary)
Decompress snappy-compressed binary data.
Overloads
gunzip(binary)
gunzip the input binary data.
Overloads
proto_deserialize(descriptor, message_name, ...+2)
Alias for proto_to_struct.
proto_deserialize_strict(descriptor, message_name, ...+2)
proto_serialize(descriptor, message_name, ...+2)
Alias for proto_to_struct.
proto_to_struct(descriptor, message_name, ...+2)
Converts protobuf binary data to a structured format.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
descriptor:
large_binary
The serialized protobuf FileDescriptorSet.
message_name:
large_string
The fully-qualified protobuf message name.
result_type:
$T
A (possibly null) struct value whose type defines the result shape. T means matching inputs and outputs use the same type.
binary:
large_binary
The input binary value.
proto_to_struct_strict(descriptor, message_name, ...+2)
Converts protobuf binary data to a structured format, recording parse errors with row attribution.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
descriptor:
large_binary
The serialized protobuf FileDescriptorSet.
message_name:
large_string
The fully-qualified protobuf message name.
result_type:
$T
A (possibly null) struct value whose type defines the result shape. T means matching inputs and outputs use the same type.
binary:
large_binary
The input binary value.
python_bytes_decode(binary)
Decodes UTF-8 encoded binary data using Python's strict bytes.decode() semantics.
Parameters
binary:
large_binary
The input binary value.
struct_to_proto(descriptor, message_name, value)
Converts structured data to protobuf binary format.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
descriptor:
large_binary
The serialized protobuf FileDescriptorSet.
message_name:
large_string
The fully-qualified protobuf message name.
value:
$T
The input value. T means matching inputs and outputs use the same type.
to_base64(binary)
Encodes binary data to a Base64 string.
Parameters
binary:
large_binary
The input binary value.
to_big_endian_32(integer)
Encodes an integer into a 32-bit big-endian binary representation.
Parameters
integer:
int32
to_big_endian_64(bigint)
Encodes a bigint into a 64-bit big-endian binary representation.
Parameters
bigint:
int64
to_hex(binary)
Converts binary data to its hexadecimal string representation.
Parameters
binary:
large_binary
The input binary value.
to_ieee754_32(real)
Encodes a float as a 32-bit big-endian binary in IEEE 754 format.
Parameters
real:
float
to_ieee754_64(double)
Encodes a double as a 64-bit big-endian binary in IEEE 754 format.
Parameters
double:
double
to_snappy(binary)
Snappy-compress binary data.
Overloads
crc32(binary)
Calculates the CRC32 checksum of binary data.
Parameters
binary:
large_binary
The input binary value.
hmac_md5(binary, key)
Computes HMAC-MD5 authentication code for data using a secret key.
Parameters
binary:
large_binary
The input binary value.
key:
large_binary
The map key to read or update.
hmac_sha1(binary, key)
Computes HMAC-SHA1 authentication code for data using a secret key.
Parameters
binary:
large_binary
The input binary value.
key:
large_binary
The map key to read or update.
hmac_sha256(binary, key)
Computes HMAC-SHA256 authentication code for data using a secret key.
Parameters
binary:
large_binary
The input binary value.
key:
large_binary
The map key to read or update.
hmac_sha512(binary, key)
Computes HMAC-SHA512 authentication code for data using a secret key.
Parameters
binary:
large_binary
The input binary value.
key:
large_binary
The map key to read or update.
md5(binary)
Computes the MD5 hash of binary data.
Parameters
binary:
large_binary
The input binary value.
sha1(binary)
Computes the SHA-1 hash of binary data.
Parameters
binary:
large_binary
The input binary value.
sha256(binary)
Computes the SHA-256 hash of binary data.
Parameters
binary:
large_binary
The input binary value.
sha512(binary)
Computes the SHA-512 hash of binary data.
Parameters
binary:
large_binary
The input binary value.
spooky_hash_v2_32(binary)
Computes a 32-bit SpookyHash V2 hash of binary data.
Parameters
binary:
large_binary
The input binary value.
spooky_hash_v2_64(binary)
Computes a 64-bit SpookyHash V2 hash of binary data.
Parameters
binary:
large_binary
The input binary value.
xxhash64(binary, ...)
Computes a 64-bit XXHash of binary data, optionally with a seed.
Overloads
from_base(string, radix)
Converts a string representation of a number in a given base to an integer.
Parameters
string:
large_string
The input string.
radix:
int64
The base (radix) to convert to.
from_utf8(binary, ...)
Decodes UTF-8 encoded binary data, optionally replacing invalid sequences.
Overloads
to_base(value, radix)
Converts an integer to its string representation in the specified base.
Parameters
value:
int64
The integer to convert.
radix:
int64
The base (radix) to convert to.
to_utf8(string)
Encodes a string to UTF-8 binary data.
Parameters
string:
large_string
The input string.
datasource_query(datasource_name, query)
Runs a SQL query directly against a registered datasource and returns the datasource result as a table. Use this when the SQL should be interpreted by the datasource dialect instead of the ChalkSQL dialect.
Parameters
datasource_name:
string
The name of the registered datasource to query.
query:
string
The SQL query to execute against the datasource.
Examples
SELECT * FROM datasource_query('my_postgres', 'SELECT id, email FROM public.users LIMIT 10');
base64(binary)
Alias for to_base64.
bedrock_embed(text)
Computes an Amazon Titan text embedding for the input string via AWS Bedrock and returns the embedding as a float vector.
Parameters
text:
large_string
The input text.
model_id:
large_string
The model identifier to invoke.
dimensions:
int64
 = NULL
The embedding dimension count.
normalize:
bool
 = NULL
aws_access_key_id_override:
large_string
 = NULL
aws_secret_access_key_override:
large_string
 = NULL
aws_session_token_override:
large_string
 = NULL
aws_role_arn_override:
large_string
 = NULL
aws_region_override:
large_string
 = NULL
aws_profile_name_override:
large_string
 = NULL
bedrock_predict(body)
Invokes an AWS Bedrock model via InvokeModel with the provided binary request body and returns the raw binary response body.
Parameters
body:
large_binary
The request body.
model_id:
large_string
The model identifier to invoke.
content_type:
large_string
 = NULL
The request content type.
accept:
large_string
 = NULL
The response MIME type to request.
aws_access_key_id_override:
large_string
 = NULL
aws_secret_access_key_override:
large_string
 = NULL
aws_session_token_override:
large_string
 = NULL
aws_role_arn_override:
large_string
 = NULL
aws_region_override:
large_string
 = NULL
aws_profile_name_override:
large_string
 = NULL
cpc_sketch_cardinality(sketch)
Reads the estimated distinct count off a CPC sketch, rounded to the nearest integer.
Overloads
cpc_sketch_of(value)
Builds a single-element CPC sketch, sized and seeded to match the sketches produced by approx_count_distinct.
Overloads
cpc_sketch_union(sketch, sketches)
Unions CPC sketches into a single sketch. Null arguments are skipped; all-null input yields null.
Overloads
duration_us_from_microseconds(microseconds)
Constructs a duration from a microsecond count.
Parameters
microseconds:
int64
The number of microseconds.
editdist3(string1, string2)
encode(string)
Alias for to_utf8.
fail(message)
Raises an error with the specified error message.
Overloads
instr(x, y)
Alias for strpos.
Examples
SELECT strpos('chalk data', 'data');

| strpos('chalk data', 'data') |
| ---------------------------- |
| 7                            |
jaro_similarity(a, b)
Calculates the Jaro similarity between two strings.
Parameters
a:
large_string
b:
large_string
jaro_winkler_similarity(a, b, ...)
Calculates the Jaro-Winkler similarity between two strings.
Overloads
len(...)
Either:
  • Returns the length of the binary in bytes.
  • Returns the length of a string in characters.
  • Returns the number of elements in a list.

Type parameters. V is the value type of the input map.

Examples
SELECT length('chalk');

| length('chalk') |
| --------------- |
| 5               |
Overloads
levenshtein(string1, string2)
onnx_run(state, observation, model_path)
Runs one step of a recurrent ONNX model: new_state = graph(state, observation).
Overloads
openai_embed(input)
Makes an embedding request to OpenAI's embeddings API and returns the response.
Parameters
input:
large_string
The input value.
model:
large_string
 = NULL
api_server:
large_string
 = NULL
api_key:
large_string
 = NULL
dimensions:
int64
 = NULL
The embedding dimension count.
python_reduce(array, initialState, inputFunction)
Applies a reduce function to each element in an array and returns the accumulated value.

Type parameters. T means matching inputs and outputs use the same type. U is a second generic value type.

Parameters
array:
large_list<item: $T>
The input array or list. T means matching inputs and outputs use the same type.
initialState:
$U
U is a second generic value type.
inputFunction:
($U, $T) => $U
T means matching inputs and outputs use the same type. U is a second generic value type.
range(...)
Alias for python_range.
recover(x, y)
Alias for coalesce.
str_split(...)
Alias for split.
str_split_regex(x, y)
Alias for regexp_split.
suffix(x, y)
Alias for ends_with.
Examples
SELECT ends_with('feature_store', 'store');

| ends_with('feature_store', 'store') |
| ----------------------------------- |
| true                                |
try_(value)
Attempts to execute an expression and handles any errors gracefully.

Type parameters. T means matching inputs and outputs use the same type.

Parameters
value:
$T
The input value. T means matching inputs and outputs use the same type.
uuid_string(namespace, name)
Generates a UUID string: a random version 4 UUID with no arguments, or the deterministic version 5 UUID of a name within a namespace UUID with two arguments.
Examples
SELECT uuid_string();

SELECT uuid_string('fe971b24-9572-4005-b22f-351e9c09274d', 'chalk');
Overloads
a <=> b
Cosine distance (1 - cosine similarity) between two vectors.
Overloads