Concerned about Tecton? Watch now.
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()
Returns the absolute value of a number.
Overloads
acos(x)
Calculates the inverse cosine in radians.
Parameters
x:
double
asin(x)
Calculates the inverse sine in radians.
Parameters
x:
double
atan(x)
Calculates the arctangent of a number in radians.
Parameters
x:
double
atan2(y, x)
Calculates the arctangent of y/x in radians, handling quadrant correctly.
Parameters
y:
double
x:
double
bankers_round()
Either:
  • Rounds a number using banker's rounding (round half to even).
  • Rounds a number to specified decimal places using banker's rounding (round half to even).
Overloads
cbrt()
Either:
  • Calculates the cube root of a number.
  • Calculates the cube root of an integer.
Overloads
ceil()
Alias for ceiling.
ceiling()
Returns the smallest integer greater than or equal to the given number.
Overloads
clamp()
Either:
  • Constrains a value between a minimum and maximum range.
  • Constrains an integer value between a minimum and maximum range.
Overloads
cos(x)
Calculates the cosine of an angle in radians.
Parameters
x:
double
cosh(x)
Calculates the hyperbolic cosine of a number.
Parameters
x:
double
degrees(x)
Converts radians to degrees.
Parameters
x:
double
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
floor()
Returns the largest integer less than or equal to the given number.
Overloads
is_finite(x)
Checks if a floating-point number is finite (not NaN or infinite).
Parameters
x:
double
is_infinite(x)
Checks if a floating-point number is infinite.
Parameters
x:
double
is_nan(x)
Checks if a floating-point number is NaN (Not a Number).
Parameters
x:
double
ln(x)
Calculates the natural logarithm of a number.
Parameters
x:
double
log()
Either:
  • Calculates the logarithm of x with the given base.
  • Calculates the base-10 logarithm of a number.
Overloads
log10(x)
Calculates the base-10 logarithm of a number.
Parameters
x:
double
log2(x)
Calculates the base-2 logarithm of a number.
Parameters
x:
double
nan()
Returns a floating point NaN (Not a Number) value.
Parameters
None
negate()
Returns the negative value of a number.
Overloads
pi()
Returns the mathematical constant π (pi).
Parameters
None
pow(x, p)
Raises a number to the power of another number.
Parameters
x:
double
p:
double
power(x, p)
Raises the first number to the power of the second number.
Parameters
x:
double
p:
double
radians(x)
Converts degrees to radians.
Parameters
x:
double
round()
Either:
  • Rounds a floating point number to the nearest integer.
  • Rounds a floating point number to the specified number of decimal places.
Overloads
sign()
Either:
  • Returns the sign of a number (-1, 0, or 1).
  • Returns the sign of an integer (-1, 0, or 1).
Overloads
sin(x)
Calculates the sine of an angle in radians.
Parameters
x:
double
sqrt(x)
Calculates the square root of a number.
Parameters
x:
double
tan(x)
Calculates the tangent of an angle in radians.
Parameters
x:
double
tanh(x)
Calculates the hyperbolic tangent of a number.
Parameters
x:
double
truncate()
Either:
  • Truncates a floating-point number to remove the decimal part.
  • Truncates a floating-point number to have `n` digits after the decimal.
Overloads
%()
Either:
  • Calculates the modulo (remainder) of two integers.
  • Calculates the modulo (remainder) of two floating-point numbers.
Overloads
*()
Either:
  • basic binary * operation
  • num * duration multiplication
Overloads
**(x, y)
basic binary ** operation
Parameters
x:
double
y:
double
+()
Either:
  • number + number addition
  • time + duration addition
  • string or binary concatenation
  • interval + interval addition
  • timestamp + interval addition
  • interval + timestamp addition
Overloads
-()
Either:
  • time - duration subtraction
  • basic binary - operation
  • date(time) subtraction
  • Returns the negative value of a number.
  • interval - interval subtraction
  • timestamp - interval subtraction
Overloads
/()
Either:
  • basic binary float / operation
  • duration / num division
Overloads
//()
floor division
Overloads
between(x, min, ...+1)
Checks if a value is between two bounds (inclusive).
Parameters
x:
$T_ord
min:
$T_ord
max:
$T_ord
distinct_from(x, y)
Checks if two values are distinct, treating NULL values as different from non-NULL values.
Parameters
x:
$T
y:
$T
eq()
Either:
  • eq
  • Compares if two values are equal.
Overloads
gt()
Either:
  • gt
  • Compares if the first value is greater than the second.
Overloads
gte()
Either:
  • gte
  • Compares if the first value is greater than or equal to the second.
Overloads
lt()
Either:
  • lt
  • Compares if the first value is less than the second.
Overloads
lte()
Either:
  • lte
  • Compares if the first value is less than or equal to the second.
Overloads
neq()
Either:
  • neq
  • Compares if two values are not equal.
Overloads
scalar_max()
Returns the maximum of two scalar values.
Overloads
scalar_min()
Returns the minimum of two scalar values.
Overloads
!=(x, y)
basic binary != operation
Parameters
x:
$T
y:
$T
<(x, y)
basic binary < operation
Parameters
x:
$T_ord
y:
$T_ord
<=(x, y)
basic binary <= operation
Parameters
x:
$T_ord
y:
$T_ord
==(x, y)
basic binary == operation
Parameters
x:
$T
y:
$T
>(x, y)
basic binary > operation
Parameters
x:
$T_ord
y:
$T_ord
>=(x, y)
basic binary >= operation
Parameters
x:
$T_ord
y:
$T_ord
bitwise_and()
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
shift:
int64
bitwise_left_shift()
Returns the left shifted value of x.
Overloads
bitwise_logical_shift_right(x, shift, ...+1)
Returns the logical right shift operation on x (treated as bits-bit integer) shifted by shift. shift must not be negative.
Parameters
x:
int64
shift:
int64
bits:
int64
bitwise_not()
Performs bitwise NOT operation (complement) on an integer value.
Overloads
bitwise_or()
Performs bitwise OR operation on two integer values.
Overloads
bitwise_right_shift()
Returns the logical right shifted value of x.
Overloads
bitwise_right_shift_arithmetic()
Returns the arithmetic right shift value of x.
Overloads
bitwise_shift_left(x, shift, ...+1)
Returns the left shift operation on x (treated as bits-bit integer) shifted by shift. shift must not be negative.
Parameters
x:
int64
shift:
int64
bits:
int64
bitwise_xor()
Performs bitwise XOR operation on two integer values.
Overloads
&()
Either:
  • boolean & operation
  • Performs bitwise AND operation on two integer values.
Overloads
|()
Either:
  • boolean | operation
  • Performs bitwise OR operation on two integer values.
Overloads
~()
Either:
  • boolean ~ operation
  • Performs bitwise NOT operation (complement) on an integer value.
Overloads
▲~(x)
▲~(x)
▲~(x)
▲~(x)
▲~(x)
▲~(x)
array_add()
Returns element-wise sum of two arrays.
Overloads
array_all()
Returns true if all elements in a boolean array are true.
Overloads
array_any()
Returns true if any element in a boolean array is true.
Overloads
array_arg_max()
Returns the index of the maximum element in an array.
Overloads
array_arg_min()
Returns the index of the minimum element in an array.
Overloads
array_average()
Calculates the average of numeric values in an array.
Overloads
array_constructor()
Either:
  • Creates an empty array.
  • Creates an array from a variable number of arguments.
Overloads
array_contains()
Alias for contains.
array_contains_element()
Returns true if the array contains the given element.
Overloads
array_cum_sum()
Calculates the cumulative sum of numeric values in an array.
Overloads
array_distinct()
Returns an array with duplicate elements removed, preserving order.
Overloads
array_duplicates()
Returns an array containing only the duplicate elements from the input array.
Overloads
array_except()
Returns elements from the first array that are not present in the second array.
Overloads
array_filter(array, predicate)
Filters an array using a callback function predicate.
Parameters
array:
large_list<item: $T>
predicate:
($T) => bool
array_frequency(x)
Returns a map of elements to their frequency counts in an array.
Parameters
x:
large_list<item: $T>
array_has_duplicates()
Checks if an array contains duplicate elements.
Overloads
array_indexof()
Alias for array_position.
array_intersect()
Returns the intersection of two arrays (elements present in both arrays).
Overloads
array_join()
Either:
  • Joins elements of an array into a string using a separator, replacing nulls with null_replacement.
  • Joins elements of a large array into a string using a separator, replacing nulls.
  • Joins elements of an array into a string using a separator.
  • Joins elements of a large array into a string using a separator.
Overloads
array_length()
Alias for cardinality.
array_max()
Returns the maximum element in an array.
Overloads
array_median()
Calculates the median value of numeric elements in an array.
Overloads
array_min()
Returns the minimum element in an array.
Overloads
array_mode()
Either:
  • Returns the most frequently occurring element in an array.
  • Returns the most frequently occurring element in an array with mode selection option.
Overloads
array_multimode()
Returns all most frequently occurring elements in an array.
Overloads
array_n_unique()
Returns the number of distinct non-null elements in an array.
Overloads
array_normalize()
Computes the L_p normalization of a Vector.
Overloads
array_position()
Either:
  • Returns the 1-based position of the first occurrence of an element in an array.
  • Returns the 1-based position of the ``instance``th value in an array.
Overloads
array_reduce(array, initialState, ...+2)
Applies a reduce function to each element in an array and returns the accumulated value.
Parameters
array:
large_list<item: $T>
initialState:
$U
inputFunction:
($U, $T) => $U
outputFunction:
($U) => $V
array_remove()
Removes all occurrences of a specified element from an array.
Overloads
array_reverse()
Returns the array with elements in reverse order.
Overloads
array_slice()
Either:
  • Extract a sublist from a list
  • Returns a substring starting at the specified position with the given length.
Overloads
array_sort()
Either:
  • Sorts an array in ascending order.
  • Sorts an array using a key extraction function.
Overloads
array_sort_desc()
Either:
  • Sorts an array in descending order using a key extraction function.
  • Sorts an array in descending order.
Overloads
array_stddev()
Calculates the standard deviation of numeric values in an array.
Overloads
array_sum()
Calculates the sum of numeric values in an array.
Overloads
array_to_string()
Alias for array_join.
array_transform(array, function)
Applies a transformation function to each element in an array and returns a new array.
Parameters
array:
large_list<item: $T>
function:
($T) => $U
array_unique()
Alias for array_distinct.
array_value()
arrays_overlap()
Checks if two arrays have any elements in common.
Overloads
arrays_union()
Returns a list of the distinct elements of the common elements
Overloads
cardinality()
Either:
  • Returns the number of elements in a list.
  • Returns the number of entries in a map.
Overloads
contains(x, element)
Checks if a list contains a specific element.
Parameters
x:
large_list<item: $V>
element:
$V
element_at()
Either:
  • Returns the element at the specified index in a list.
  • Retrieves the value associated with a key from a map.
Overloads
find_first()
Returns the first element in an array that matches the given predicate function.
Overloads
find_first_index()
Returns the 1-based index of the first element in an array that matches the given predicate function.
Overloads
flatten()
Flattens a nested array by one level, combining all sub-arrays into a single array.
Overloads
greatest()
Either:
  • Returns the maximum value from a list of float values.
  • Returns the maximum value from a list of integer values.
Overloads
least()
Either:
  • Returns the minimum value from a list of float values.
  • Returns the minimum value from a list of integer values.
Overloads
list_avg()
Alias for array_average.
list_contains()
Alias for contains.
list_count()
Alias for cardinality.
list_distinct()
Alias for array_distinct.
list_has()
Alias for contains.
list_has_any()
Alias for arrays_overlap.
list_intersect()
Alias for array_intersect.
list_max()
Alias for array_max.
list_median()
Alias for array_median.
list_min()
Alias for array_min.
list_mode()
Alias for array_mode.
list_pack()
list_position()
Alias for array_position.
list_slice()
Alias for array_slice.
list_sort()
Alias for array_sort.
list_stddev_pop()
Alias for array_stddev.
list_sum()
Alias for array_sum.
list_unique()
Alias for array_distinct.
list_value()
python_element_at()
Returns the element at the specified Python-style index in a list.
Overloads
python_zip()
Combines multiple lists element-wise into a list of paired structures with Python semantics.
Overloads
remove_nulls()
Removes all null values from an array.
Overloads
shuffle()
Randomly shuffles the elements of an array.
Overloads
slice()
Returns a slice of a list starting at the given position for the specified length.
Overloads
transform()
Applies a transformation function to each element in an array and returns a new array.
Overloads
zip(array1, array2)
Combines two lists element-wise into a list of paired structures.
Parameters
array1:
large_list<item: $T>
array2:
large_list<item: $U>
zip_with(array1, array2, ...+1)
Combines two lists element-wise using a callback function to transform paired elements.
Parameters
array1:
large_list<item: $T>
array2:
large_list<item: $U>
function:
($T, $U) => $R
binary_to_vector()
Returns vector from binary.
Overloads
cosine_similarity_vector()
Returns the cosine similarity between the vectors.
Overloads
dot_product_vector()
Returns the dot product between the vectors.
Overloads
scale_vector()
Returns a scaled vector.
Overloads
vector_to_binary()
Returns binary of vector.
Overloads
boost_regexp_findall(x, y)
Finds all matches of a regular expression pattern in a string and returns them as a list.
Parameters
x:
large_string
y:
large_string
chr(n)
Converts an integer to its corresponding ASCII character.
Parameters
n:
int64
codepoint()
Returns the Unicode code point of the only character of the string.
Overloads
concat()
Either:
  • Concatenates two or more strings together.
  • Concatenates two or more blobs together.
  • Concatenates two or more lists together.
Overloads
ends_with(string, substring)
Checks if a string ends with a specified suffix.
Parameters
string:
large_string
substring:
large_string
hamming_distance()
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
jaccard_similarity(x, y)
Calculates the Jaccard similarity coefficient between two strings based on character sets.
Parameters
x:
large_string
y:
large_string
jaro_winkler_distance(arg1, arg2, ...+1)
Calculates the Jaro-Winkler distance between two strings.
Parameters
arg1:
large_string
arg2:
large_string
arg3:
double
length()
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.
Overloads
levenshtein_distance(x, y)
Calculates the Levenshtein distance between two strings.
Parameters
x:
large_string
y:
large_string
like(string, pattern)
Checks if a string matches a pattern using SQL LIKE syntax with wildcards.
Parameters
string:
large_string
pattern:
large_string
longest_common_subsequence(x, y)
Calculates the longest common subsequence between two strings.
Parameters
x:
large_string
y:
large_string
lower(string)
Converts a string to lowercase.
Parameters
string:
large_string
lpad()
Either:
  • Left pads binary to size bytes with padbinary.
  • Pads a string with a specified padding string on the left to reach a target length.
Overloads
ltrim()
Either:
  • Removes whitespace from the left end of a string.
  • Removes specified characters from the left end of a string.
Overloads
partial_ratio(x, y)
Calculates the partial ratio similarity between two strings using fuzzy matching.
Parameters
x:
large_string
y:
large_string
regexp_extract()
Either:
  • Returns the first substring matched by the regular expression pattern in string.
  • Returns the first substring matched by the regular expression pattern in string and returns the capturing group number group.
Overloads
regexp_extract_all()
Either:
  • Returns the substring(s) matched by the regular expression pattern in string.
  • Returns the substring(s) matched by the regular expression pattern in string and returns the capturing group number group.
Overloads
regexp_full_match(x, y)
Checks if a string matches a regular expression pattern.
Parameters
x:
large_string
y:
large_string
regexp_like(string, pattern)
Checks if a string matches a regular expression pattern.
Parameters
string:
large_string
pattern:
large_string
regexp_replace()
Either:
  • Replaces all matches of a regular expression pattern with a replacement string.
  • Removes all matches of a regular expression pattern from a string.
  • Replaces every instance of the substring matched by the regular expression ``pattern`` in ``string`` using ``function``. The lambda expression ``function`` is invoked for each match with the capturing groups passed as an array. Capturing group numbers start at 1; there is no group for the entire match (if you need this, surround the entire expression with parenthesis).
Overloads
regexp_split(string, pattern)
Splits ``string`` using the regular expression ``pattern`` into a list of strings.
Parameters
string:
large_string
pattern:
large_string
replace()
Either:
  • Removes all occurrences of search from string.
  • Replaces all occurrences of a substring with another string.
Overloads
reverse()
Either:
  • Returns input binary with bytes in reversed order.
  • Reverses the characters in a string.
  • Returns the array in reversed order.
Overloads
rpad()
Either:
  • Right pads binary to size bytes with padbinary.
  • Pads a string with a specified padding string on the right to reach a target length.
Overloads
rtrim()
Either:
  • Removes whitespace from the right end of a string.
  • Removes specified characters from the right end of a string.
Overloads
sequence_matcher_ratio(x, y)
Computes the similarity ratio between two strings using sequence matching.
Parameters
x:
large_string
y:
large_string
split()
Either:
  • Splits a string by a delimiter into a list of strings.
  • Splits a string by a delimiter with a maximum number of splits.
Overloads
split_part(string, delimiter, ...+1)
Splits a string by delimiter and returns the part at the specified index (1-based).
Parameters
string:
large_string
delimiter:
large_string
index:
int64
starts_with(string, substring)
Checks if a string starts with a specified prefix.
Parameters
string:
large_string
substring:
large_string
string_to_bytes_base64()
Alias for from_base64.
string_to_bytes_hex()
Alias for from_hex.
string_to_bytes_utf8()
Alias for to_utf8.
strpos()
Either:
  • Returns the position of the N-th occurrence of a substring within a string.
  • Returns the position of the first occurrence of a substring within a string.
Overloads
strrpos()
Either:
  • Returns the position of the N-th occurrence of a substring from the end of a string.
  • Returns the position of the last occurrence of a substring within a string.
Overloads
substr()
Either:
  • Returns a substring starting at the specified position.
  • Returns a substring starting at the specified position with the given length.
Overloads
token_set_ratio(x, y)
Calculates the token set ratio similarity between two strings using fuzzy matching.
Parameters
x:
large_string
y:
large_string
token_sort_ratio(x, y)
Calculates the token sort ratio similarity between two strings using fuzzy matching.
Parameters
x:
large_string
y:
large_string
trail()
Returns the last N characters of the input string, up to at most the length of string.
Overloads
trim()
Either:
  • Removes whitespace from both ends of a string.
  • Removes specified characters from both ends of a string.
Overloads
unidecode_normalize(x)
Normalizes Unicode characters to their closest ASCII equivalents with whitespace normalization.
Parameters
x:
large_string
unidecode_to_ascii(x)
Converts Unicode characters to their closest ASCII equivalents.
Parameters
x:
large_string
upper(string)
Converts a string to uppercase.
Parameters
string:
large_string
word_stem()
Returns the stem of a word using stemming algorithms.
Overloads
zi_split_part(arg1, arg2, ...+1)
Splits a string by delimiter and returns the part at the specified zero-based index.
Parameters
arg1:
large_string
arg2:
large_string
arg3:
int64
current_date()
Returns the current date.
Parameters
None
date_add()
Either:
  • Adds the specified value of unit to a timestamp.
  • date + duration addition
Overloads
date_trunc(unit, x)
Truncates a timestamp to the specified time unit (e.g., day, month, year).
Parameters
unit:
large_string
x:
timestamp[us, tz=UTC]
day_of_month()
Extracts the day of the month from a timestamp.
Overloads
day_of_week()
Extracts the day of the week from a timestamp.
Overloads
day_of_year()
Extracts the day of the month from a timestamp.
Overloads
format_datetime(timestamp, format)
Formats a datetime using a specified format string.
Parameters
timestamp:
timestamp[us, tz=UTC]
format:
large_string
from_iso8601_timestamp(string)
Parses an ISO 8601 timestamp string into a datetime object.
Parameters
string:
large_string
from_unix_seconds()
Alias for from_unixtime.
from_unixtime()
Either:
  • Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.
  • Returns the UNIX timestamp unixtime as a timestamp with time zone.
Overloads
hour(x)
Extracts the hour from a timestamp.
Parameters
x:
timestamp[us, tz=UTC]
is_federal_holiday()
Returns True if the given timestamp is a federal holiday.
Overloads
last_day_of_month(x)
Returns the last day of the month for a given date.
Parameters
x:
timestamp[us, tz=UTC]
millisecond(x)
Extracts the millisecond from a timestamp.
Parameters
x:
timestamp[us, tz=UTC]
minute(x)
Extracts the minute from a timestamp.
Parameters
x:
timestamp[us, tz=UTC]
month()
Extracts the month from a timestamp.
Overloads
parse_datetime(string, format)
Converts an ISO 8601 string into a datetime.
Parameters
string:
large_string
format:
large_string
quarter(x)
Extracts the quarter of the year from a timestamp.
Parameters
x:
timestamp[us, tz=UTC]
second(x)
Extracts the second from a timestamp.
Parameters
x:
timestamp[us, tz=UTC]
to_iso8601(x)
Converts a datetime to an ISO 8601 string format.
Parameters
x:
timestamp[us, tz=UTC]
to_microseconds()
Either:
  • Converts a duration to microseconds.
  • Constructs a duration with the given number of milliseconds.
Overloads
to_milliseconds()
Either:
  • Converts a duration to milliseconds.
  • Constructs a duration with the given number of milliseconds.
Overloads
to_unixtime(timestamp)
Converts a timestamp to Unix timestamp (seconds since epoch).
Parameters
timestamp:
timestamp[us, tz=UTC]
total_seconds(x)
Returns the length of the input duration in seconds.
Parameters
x:
duration[us]
week()
Extracts the week of the year from a timestamp.
Overloads
week_of_year()
Extracts the week of the year from a timestamp.
Overloads
year()
Extracts the year from a timestamp.
Overloads
year_of_week()
Extracts the year of the ISO week from a date.
Overloads
yow()
Extracts the year of the ISO week from a date.
Overloads
map_contains(x, y)
Checks if a map contains any keys that match a given condition.
Parameters
x:
map<$K, $V>
y:
($k) => bool
map_filter(map, predicate)
Filters the entries of a map using a callback predicate.
Parameters
map:
map<$K, $V>
predicate:
($K, $V) => bool
map_get()
Either:
  • Retrieves the value associated with a key from a map.
  • Retrieves the value associated with a string key from a map with automatic key type casting.
Overloads
map_key_exists(x, y)
Checks if a map contains any keys that match a given condition.
Parameters
x:
map<$K, $V>
y:
$K
map_keys(x)
Returns all keys from a map as a list.
Parameters
x:
map<$K, $V>
map_keys_by_top_n_values(map, n)
Returns the keys of a map ordered by their top N highest values.
Parameters
map:
map<$K, $V>
n:
int64
map_subset()
Returns a subset of a map containing only the specified keys.
Overloads
map_top_n(map, n)
Returns the top N entries from a map ordered by value.
Parameters
map:
map<$K, $V>
n:
int64
map_top_n_keys(map, n)
Returns the top N keys from a map ordered by their values.
Parameters
map:
map<$K, $V>
n:
int64
map_top_n_values(map, n)
Returns the top N values from a map ordered by value.
Parameters
map:
map<$K, $V>
n:
int64
map_values(x)
Returns all values from a map as a list.
Parameters
x:
map<$K, $V>
map_zip_with(map1, map2, ...+1)
Merges two maps by applying a function to each matched key and pair of values.
Parameters
map1:
map<$K, $V1>
map2:
map<$K, $V2>
function:
($K, $V1, $V2) => $V3
beta_cdf(a, b, ...+1)
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
binomial_cdf(numberOfTrials, successProbability, ...+1)
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
cauchy_cdf(median, scale, ...+1)
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
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
f_cdf(df1, df2, ...+1)
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
gamma_cdf(shape, scale, ...+1)
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
inverse_beta_cdf(a, b, ...+1)
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, ...+1)
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, ...+1)
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, ...+1)
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()
Alias for gamma_cdf.
inverse_laplace_cdf(mean, scale, ...+1)
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, ...+1)
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, ...+1)
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, ...+1)
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
normal_cdf(mean, sd, ...+1)
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
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
weibull_cdf(a, b, ...+1)
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
width_bucket(x, bound1, ...+2)
Returns the bucket number for a value in a histogram with uniform bucket widths.
Parameters
x:
double
bound1:
double
bound2:
double
n:
int64
wilson_interval_lower(successes, trials, ...+1)
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
trials:
int64
z:
double
wilson_interval_upper(successes, trials, ...+1)
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
trials:
int64
z:
double
get_json_value()
Either:
  • Extracts a value from JSON using a path expression.
  • Extracts a value from JSON string using a path expression.
Overloads
get_json_value_strict()
Either:
  • Extracts a value from JSON using a path expression. Records errors for invalid JSON.
  • Extracts a value from JSON string using a path expression. Records errors for invalid JSON.
  • Extracts a value from JSON using a path expression. Records errors for invalid JSON.
Overloads
is_json_scalar()
Either:
  • Returns true if the JSON value is a scalar (number, string, true, false, or null).
  • Returns true if the JSON string represents a scalar value.
Overloads
json_array_contains()
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>
json_extract_array()
Extracts an array from JSON string using a path expression.
Overloads
json_extract_scalar()
Either:
  • Extracts a scalar value from JSON using a JSONPath expression.
  • Extracts a scalar value from a JSON string using a JSONPath expression.
Overloads
json_size()
Either:
  • Returns the size of a JSON object or array at the given path, or 0 for scalar values.
  • Returns the size of a JSON value at the given path.
Overloads
jsonify(x)
Converts an arbitrary value into a JSON string
Parameters
x:
$T
from_base64url(string)
Decodes a Base64url-encoded string to binary data.
Parameters
string:
large_string
http_request()
Either:
  • Makes an HTTP request with string body and returns the response as bytes.
  • Makes an HTTP request with binary body and returns the response as bytes.
Overloads
to_base64url(binary)
Encodes binary data to a Base64url string.
Parameters
binary:
large_binary
url_decode(value)
Decodes URL-encoded characters in a string.
Parameters
value:
large_string
url_encode(value)
URL-encodes special characters in a string.
Parameters
value:
large_string
url_extract_fragment(url)
Extracts the fragment portion (after #) from a URL.
Parameters
url:
large_string
url_extract_host(url)
Returns the host from a URL.
Parameters
url:
large_string
url_extract_parameter(url, name)
Extracts the value of a specific query parameter from a URL.
Parameters
url:
large_string
name:
large_string
url_extract_path(url)
Returns the path from a URL.
Parameters
url:
large_string
url_extract_port(url)
Extracts the port number from a URL.
Parameters
url:
large_string
url_extract_protocol(url)
Returns the protocol from a URL.
Parameters
url:
large_string
url_extract_query(url)
Extracts the query string portion (after ?) from a URL.
Parameters
url:
large_string
openai_complete(arg1, arg2, ...+3)
Makes a completion request to OpenAI's chat API and returns the response.
Parameters
arg1:
large_string
arg2:
large_string
arg3:
large_string
arg4:
int64
arg5:
double
sagemaker_predict(x, endpoint, ...+10)
Invokes an AWS SageMaker endpoint for inference with the provided binary input and returns the binary output.
Parameters
x:
large_binary
endpoint:
large_string
content_type:
large_string
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
h3_cell_to_lat_lon_degrees(x)
Calculates the lat-lon in degrees for a given h3 cell.
Parameters
x:
large_string
h3_cell_to_lat_lon_radians(x)
Calculates the lat-lon for a given h3 cell.
Parameters
x:
large_string
h3_lat_lon_to_cell()
Calculates the h3 cell for a given lat-lon.
Overloads
bytes_to_string_base64()
Alias for to_base64.
bytes_to_string_hex()
Alias for to_hex.
bytes_to_string_utf8()
Alias for from_utf8.
crc32(binary)
Calculates the CRC32 checksum of binary data.
Parameters
binary:
large_binary
from_base64(string)
Decodes a Base64-encoded string to binary data.
Parameters
string:
large_string
from_big_endian_32()
Convert a 32-bit big-endian bytes value to an integer.
Overloads
from_big_endian_64()
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
from_snappy()
Decompress snappy-compressed binary data.
Overloads
gunzip()
gunzip the input binary data.
Overloads
hmac_md5(binary, key)
Computes HMAC-MD5 authentication code for data using a secret key.
Parameters
binary:
large_binary
key:
large_binary
hmac_sha1(binary, key)
Computes HMAC-SHA1 authentication code for data using a secret key.
Parameters
binary:
large_binary
key:
large_binary
hmac_sha256(binary, key)
Computes HMAC-SHA256 authentication code for data using a secret key.
Parameters
binary:
large_binary
key:
large_binary
hmac_sha512(binary, key)
Computes HMAC-SHA512 authentication code for data using a secret key.
Parameters
binary:
large_binary
key:
large_binary
md5(binary)
Computes the MD5 hash of binary data.
Parameters
binary:
large_binary
proto_deserialize()
Alias for proto_to_struct.
proto_deserialize_strict()
proto_serialize()
Alias for proto_to_struct.
proto_to_struct(arg1, arg2, ...+2)
Converts protobuf binary data to a structured format.
Parameters
arg1:
large_binary
arg2:
large_string
arg3:
$T
arg4:
large_binary
proto_to_struct_strict(arg1, arg2, ...+2)
Converts protobuf binary data to a structured format, recording parse errors with row attribution.
Parameters
arg1:
large_binary
arg2:
large_string
arg3:
$T
arg4:
large_binary
sha1(binary)
Computes the SHA-1 hash of binary data.
Parameters
binary:
large_binary
sha256(binary)
Computes the SHA-256 hash of binary data.
Parameters
binary:
large_binary
sha512(binary)
Computes the SHA-512 hash of binary data.
Parameters
binary:
large_binary
spooky_hash_v2_32(binary)
Computes a 32-bit SpookyHash V2 hash of binary data.
Parameters
binary:
large_binary
spooky_hash_v2_64(binary)
Computes a 64-bit SpookyHash V2 hash of binary data.
Parameters
binary:
large_binary
struct_to_proto(arg1, arg2, ...+1)
Converts structured data to protobuf binary format.
Parameters
arg1:
large_binary
arg2:
large_string
arg3:
$T
to_base64(binary)
Encodes binary data to a Base64 string.
Parameters
binary:
large_binary
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
xxhash64()
Either:
  • Computes a 64-bit XXHash of binary data with a seed.
  • Computes a 64-bit XXHash of binary data.
Overloads
from_base(string, radix)
Converts a string representation of a number in a given base to an integer.
Parameters
string:
large_string
radix:
int64
from_utf8()
Either:
  • Decodes UTF-8 encoded binary, replacing invalid sequences with the given code point.
  • Decodes UTF-8 encoded binary, replacing invalid sequences with the given string.
  • Decodes UTF-8 encoded binary data into a string.
Overloads
to_base(x, radix)
Converts an integer to its string representation in the specified base.
Parameters
x:
int64
radix:
int64
to_utf8(string)
Encodes a string to UTF-8 binary data.
Parameters
string:
large_string
all_keys_match(x, predicate)
Returns true if all keys in the map match the given predicate.
Parameters
x:
map<$K, $V>
predicate:
($K) => bool
all_match()
Returns true if every element of the array satisfies the predicate.
Overloads
and(x, y)
Performs logical AND operation on two boolean values.
Parameters
x:
bool
y:
bool
any_keys_match(x, predicate)
Returns true if any key in the map matches the given predicate.
Parameters
x:
map<$K, $V>
predicate:
($K) => bool
any_match()
Returns true if any element of the array satisfies the predicate.
Overloads
any_values_match(x, predicate)
Returns true if any value in the map matches the given predicate.
Parameters
x:
map<$K, $V>
predicate:
($V) => bool
arccosh(x)
Calculates the inverse hyperbolic cosine.
Parameters
x:
double
arcsinh(x)
Calculates the inverse hyperbolic sine.
Parameters
x:
double
arctanh(x)
Calculates the inverse hyperbolic tangent.
Parameters
x:
double
avro_deserialize()
Alias for avro_to_struct.
avro_to_struct(arg1, arg2, ...+1)
Converts Avro binary data to a structured format using an Avro schema.
Parameters
arg1:
large_string
arg2:
$T
arg3:
large_binary
bit_count(x, bits)
Counts the number of bits in `x`.
Parameters
x:
int64
bits:
int64
boyer_moore_search(x, y)
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
x:
large_string
y:
large_string
coalesce(x, y)
Returns the first non-null value from a list of arguments.
Parameters
x:
$T
y:
$T...
combinations()
Returns all possible combinations of the given size from an array.
Overloads
cot(x)
Calculates the cotangent of an angle in radians.
Parameters
x:
double
count_matches()
Counts the number of non-overlapping regex matches in a string.
Overloads
date()
Either:
  • Converts a timestamp to a date.
  • Converts a string to a date.
Overloads
date_diff(unit, x1, ...+1)
Returns the difference between two timestamps in the specified unit.
Parameters
unit:
large_string
x1:
timestamp[us, tz=UTC]
x2:
timestamp[us, tz=UTC]
date_format(x, format)
Formats a timestamp as a string using the specified format.
Parameters
x:
timestamp[us, tz=UTC]
format:
large_string
day()
Extracts the day of the month from a date or timestamp.
Overloads
days_in_month()
Returns the number of days in the month of the given date.
Overloads
dow()
Extracts the day of the week from a date or timestamp.
Overloads
doy()
Extracts the day of the year from a date or timestamp.
Overloads
fail()
Raises an error with the specified error message.
Overloads
from_ieee754_32(binary)
Decodes the 32-bit big-endian binary representation of an IEEE 754 floating-point value.
Parameters
binary:
large_binary
from_ieee754_64(binary)
Decodes the 64-bit big-endian binary representation of an IEEE 754 floating-point value.
Parameters
binary:
large_binary
from_iso8601_date(string)
Parses an ISO 8601 date string into a date value.
Parameters
string:
large_string
if_else(arg1, arg2, ...+1)
Returns the second argument if the condition is true, otherwise returns the third argument.
Parameters
arg1:
bool
arg2:
$T
arg3:
$T
ilike(x, y)
Case-insensitive like
Parameters
x:
large_string
y:
large_string
is_business_day()
Returns true if the date falls on a weekday (Monday-Friday).
Overloads
is_null(x)
Checks if the input is null.
Parameters
x:
$T
knuth_morris_pratt_search(x, y)
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
x:
large_string
y:
large_string
len_bytes()
Returns the byte length of a string.
Overloads
log1p(x)
Calculates the natural logarithm of (1 + x), accurate for small x.
Parameters
x:
double
ngrams()
Returns all N-grams (contiguous subsequences of length n) from an array.
Overloads
no_keys_match(x, predicate)
Returns true if no keys in the map match the given predicate.
Parameters
x:
map<$K, $V>
predicate:
($K) => bool
no_values_match(x, predicate)
Returns true if no values in the map match the given predicate.
Parameters
x:
map<$K, $V>
predicate:
($V) => bool
none_match()
Returns true if no element of the array satisfies the predicate.
Overloads
normalize()
Either:
  • Transforms string with NFC normalization form.
  • Transforms string with the specified Unicode normalization form.
Overloads
octet_length()
Either:
  • Returns the byte length of a string.
  • Returns the length of the binary in bytes.
Overloads
prefix()
Alias for starts_with.
python_eq(x, y)
Checks if two values are equal, treating NULL values as equal to other NULL values.
Parameters
x:
$T
y:
$T
python_is_alnum()
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()
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()
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()
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()
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()
Returns true if all characters are whitespace and there is at least one character, matching Python's str.isspace() behavior.
Overloads
python_is_title()
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()
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()
Strips leading Python whitespace (Unicode-aware), matching Python's str.lstrip() with no arguments.
Overloads
python_ne(x, y)
Checks if two values are not equal, treating NULL values as different from non-NULL values.
Parameters
x:
$T
y:
$T
python_range()
Creates a range of integers with Python-like semantics.
Overloads
python_remove_prefix()
Removes the prefix from the string if present, matching Python's str.removeprefix().
Overloads
python_remove_suffix()
Removes the suffix from the string if present, matching Python's str.removesuffix().
Overloads
python_rstrip()
Strips trailing Python whitespace (Unicode-aware), matching Python's str.rstrip() with no arguments.
Overloads
python_string_slice()
Slices a string with exact Python s[start:stop:step] semantics. All integer arguments are nullable (NULL = Python None / unspecified).
Overloads
python_strip()
Strips leading and trailing Python whitespace (Unicode-aware), matching Python's str.strip() with no arguments.
Overloads
rand()
Generates a random float between 0 and 1.
Parameters
None
random()
Either:
  • Generates a pseudo-random float between 0 and 1.
  • Generates a pseudo-random integer within the bounds of the specified type.
Overloads
recover()
Alias for coalesce.
reduce()
Alias for array_reduce.
repeat(element, count)
Returns an array containing the element repeated count times.
Parameters
element:
$E
count:
int32
round_sig_figs(x, y)
Rounds a number to n significant figures.
Parameters
x:
double
y:
int64
secure_random()
Either:
  • Generates a cryptographically secure random float between 0 and 1.
  • Generates a cryptographically secure random number between the specified bounds.
Overloads
sequence()
Either:
  • Generates a sequence of integers from start to stop (inclusive).
  • Generates a sequence of integers from start to stop (inclusive) with a specified step.
Overloads
subscript()
Returns the element at the specified 1-based index in the array.
Overloads
substring()
Alias for substr.
titlecase()
Converts a string to title case.
Overloads
to_days(x)
Constructs a duration with the given number of milliseconds.
Parameters
x:
int64
to_hours(x)
Constructs a duration with the given number of milliseconds.
Parameters
x:
int64
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_minutes(x)
Constructs a duration with the given number of milliseconds.
Parameters
x:
int64
to_seconds(x)
Constructs a duration with the given number of milliseconds.
Parameters
x:
int64
transform_keys(map, function)
Returns a map with keys transformed by the given function.
Parameters
map:
map<$K1, $V>
function:
($K1, $V) => $K2
transform_values(map, function)
Returns a map with values transformed by the given function.
Parameters
map:
map<$K, $V1>
function:
($K, $V1) => $V2
trim_array()
Removes the last n elements from the array.
Overloads
try_(x)
Attempts to execute an expression and handles any errors gracefully.
Parameters
x:
$T
zfill()
Left-pads a string with zeros to the given width.
Overloads
!~~(x, y)
operator alias for not like
Parameters
x:
large_string
y:
large_string
!~~*(x, y)
operator alias for not ilike
Parameters
x:
large_string
y:
large_string
&&()
operator alias for list_has_any
Overloads
<<()
^()
Alias for pow.
^@()
Alias for starts_with.
||()
Alias for concat.
~~(x, y)
operator alias for like
Parameters
x:
large_string
y:
large_string
~~*(x, y)
Case-insensitive like
Parameters
x:
large_string
y:
large_string