All OPAL functions

Opal functions compute values for individual columns.

Function	Description
abs	Returns the absolute value of ‘val’.
any	Return any value of one column across a group
any_not_null	Return any non-null value of one column across a group.
append_item	Returns an array containing all elements from the source array as well as the new element.
array	Convert a datum into an array or NULL if conversion is impossible
array_agg	Returns an array of concatenated input values.
array_agg_distinct	Returns an array of distinct input values.
array_contains	Tests if an array contains a value.
array_distinct	Returns a new array that contains only the distinct elements from the input array after removing duplicate elements.
array_length	returns the number of elements in an array, or null if input is not an array
array_max	Return the maximum value in an array.
array_min	Return the minimum value in an array.
array_null	Returns a null value of type array.
array_to_string	Cast all values in an array to string and concatenate them, optionally adding a separator between each value.
array_union_agg	Returns an array containing the multiset union of input arrays.
arrays_overlap	Compares whether two arrays have at least one element in common.
asc	Specify the ordering as ascending when processing data.
avg	Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.
bin_end_time	Returns the end time of the current bin (exclusive).
bin_size	Returns the size of the time bin that’s being aggregated.
bin_start_time	Returns the start time of the current bin (inclusive).
bool	Generate a boolean value of the argument value.
bool_null	Returns a null value of type bool.
case	Return a matching result if any of the multiple given conditions are true.
ceil	Returns ‘val’ rounded up to the given ‘precision’.
coalesce	Return the first non-null argument or null if all are null.
concat_arrays	Returns a concatenation of N arrays array1, array2, arrayN.
concat_strings	Return the concatenation of all string arguments.
contains	Returns true if the str string contains the expr string (case-sensitive).
count	Count the number of non-null items in the group.
count_distinct	Estimate the approximate number of distinct values in the input using hyper-log-log.
count_distinct_exact	Count the exact number of distinct values in the input using complete enumeration.
decode_base64	DecodeBase64 decodes a base64 encoded input
decode_uri	Replace %-encoded escape sequences in a string with unencoded plain text.
decode_uri_component	Replace all %-encoded escape sequences in a string with unencoded plain text.
delta	Calculates the value difference of the argument in each time bin for each group.
delta_monotonic	Calculates the amount of difference in a column in each time bin for each group.
dense_rank	Returns the dense rank within an ordered group of values.
deriv	Calculate the average per-second derivative of the argument across the group.
desc	Specify the ordering as descending when processing data.
detect_browser	Identify the browser used for a web request from its User-Agent header.
drop_fields	Drop one or more fields from an object.
duration	Convert one argument as a number of nanoseconds or a timestamp, or two arguments as start or stop timestamps, to a duration.
duration_hr	The input is a number representing a number of hours, the output is that quantity as type duration.
duration_min	The input is a number representing a number of minutes, the output is that quantity as type duration.
duration_ms	The input is a number representing a number of milliseconds, the output is that quantity as type duration.
duration_null	Returns a null value of type duration.
duration_sec	The input is a number representing a number of seconds, the output is that quantity as type duration.
editdistance	Returns the Levenshtein distance between str1 and str2.
embed_sql_params	Embed parameters into the given prepared SQL statement by replacing occurrences of the placeholder character ‘?
encode_base64	EncodeBase64 encodes the input in the base64 format
encode_uri	Replace certain characters in a string with %-encoded escape sequences.
encode_uri_component	Replace certain characters in a string with %-encoded escape sequences.
ends_with	Returns true if string str ends with string expr.
eq	Return true if A is equal to B.
exp	Returns Euler’s number e raised to the given number.
first	Return the first value of one column across an ordered group.
first_not_null	Return the first non-null value of one column across an ordered group.
float64	Generate a float representation of the argument value.
float64_null	Returns a null value of type float64.
floor	Returns ‘val’ rounded down to the given ‘precision’.
format_time	Format a timestamp column according to the specified format string.
frame	Specify the approximate relative time frame for a window context.
frame_exact	Specify the exact relative time frame for a window context.
from_milliseconds	Given a numeric value representing milliseconds since epoch, return a timestamp of that point in time.
from_nanoseconds	Given a numeric value representing nanoseconds since epoch, return a timestamp of that point in time.
from_seconds	Given a numeric value representing seconds since epoch, return a timestamp of that point in time.
get_field	Given an object, and a computed string value as key (which can be a column value or some string concatenation, for example), look
get_item	Given an array and a computed index, return the item at that index in the array.
get_jmespath	Process an object or array expression using a query written in the JMESPath query language.
get_regex	Returns a string that matches the given regular expression if a match exists.
get_regex_all	Returns an array containing all parts of the argument string that match the given regular expression (which may be empty if
group_by	Grouping/partitioning in which to process data.
gt	Return true if A is strictly greater than B.
gte	Return true if A is greater than or equal to B.
hash	Accepts a variable number of arguments of arbitrary types and returns a signed 64-bit hash of the values.
haversine_distance_km	Computes the great circle approximate distance between two latitude/longitude coordinates, using the haversine formula.
if	Test if a condition is true or false.
if_null	Return the second argument if the first argument has the null value.
in	Returns true if the expression matches with any value in the given set.
index_of_item	Given an array and a value, return the index of the first occurrence of that value within that array, or NULL if the value isn’t
insert_item	Returns an array containing all elements from the source array as well as the new element.
int64	Generate an integer representation of the argument value.
int64_null	Returns a null value of type int64.
int64_to_ipv4	Converts integer-encoded IPv4 addresses to dotted-quad notation.
int_div	Returns the integer quotient of the dividend divided by the divisor and truncates the remainder.
intersect_arrays	Returns an array containing the matching elements inside the two input arrays.
ipv4	Returns an IPv4 address representation of the argument value.
ipv4_address_in_network	Test whether an IPv4 address is in a given subnet.
ipv4_network_int64	Converts IPv4 network addresses to integer representation.
ipv4_to_int64	Given an IPv4 address string in dotted-quad format, convert the address to an integer representing the address.
is_null	Return true if the argument has the null value.
lag	Return the value of one column in a previous row across an ordered group.
lag_not_null	Return the non-null value of one column in a previous row across an ordered group.
last	Return the last value of one column across an ordered group.
last_not_null	Return the last non-null value of one column across an ordered group.
lead	Return the value of one column in a following row across an ordered group.
lead_not_null	Return the non-null value of one column in a following row across an ordered group.
left	Returns a leftmost substring of its input.
like	Returns true if subject matches pattern (case-sensitive).
ln	Returns natural logarithm of a numeric expression.
log	Returns logarithm of a numeric expression (second argument) with the provided base (first argument).
lower	Return the input string in lowercase.
lpad	Left pads a string with characters from another string, default pad string is whitespace
lt	Return true if A is strictly less than B.
lte	Return true if A is less than or equal to B.
ltrim	ltrim removes leading characters from a string.
m	Select a metric for the align verb.
m_tdigest	Select a metric of type tdigest for the align verb.
make_array	Returns an array constructed from zero, one or more input arguments.
make_array_range	Returns an array that contains a list of consecutive integers ranging from start to end.
make_fields	Extend an existing object with new fields.
make_object	Turn a sequence of name:value elements into an object.
match_regex	Return true if the argument input string or object (converted to a string) matches the argument regular expression.
max	Returns the maximum value of a group of inputs.
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
merge_objects	Merge one or more objects into a single object.
metric	Select the metrics in the rollup verb.
min	Returns the minimum value of a group of inputs.
mod	Returns the remainder when dividend is divided by the divisor.
ne	Return true if A is not equal to B.
now	Returns the current time for which the query is executed as timestamp.
nullsfirst	Specify the ordering as nulls first when processing data.
nullslast	Specify the ordering as nulls last when processing data.
numeric_null	Returns a null value of type numeric.
object	Convert a datum into an object or NULL if conversion is impossible
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
object_keys	Get an array of top-level keys from an object type field (field names from JSON objects).
object_null	Returns a null value of type object.
on	on is a special function used to specify the join condition for some join verbs.
options	Specify options to change the verb’s behavior
order_by	Specify the ordering when processing data.
parse_csv	Parses an input string as character-separated values, where the default separator is a comma (comma-separated values.
parse_duration	Given a string describing the duration between two points in time, convert it to a duration.
parse_hex	Parses a string encoded hex number and returns an int64.
parse_ip	Parse an IPv4 or IPv6 network address into relevant attributes.
parse_isotime	Parse an ISO8601 (YYYY-MM-DDTHH:MM:SSZ) formatted string as a timestamp.
parse_json	Parse the argument value as a JSON string.
parse_kvs	Returns an object of key=value pairs extracted from an input string.
parse_timestamp	Parse a string value, whose format is specified by the format argument, as a timestamp.
parse_url	Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme).
path_exists	Given a column and path, return whether the JSON path exists in that column.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, return the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, return the value for the specified percentile of the input expression across the group.
pick_fields	Pick one or more fields from an object.
pivot_array	Converts an array of “key”-“value” pairs into an object with key-value attributes.
position	Searches for the first occurrence of the second argument (needle) in the first argument (haystack).
pow	Returns a number ‘base’ raised to the specified power ‘exponent’.
prepend_item	Returns an array containing all elements from the source array as well as the new element.
primary_key	Specify the primary key for some verbs
prom_quantile	Calculates an approximate percentile value of the distribution in a histogram metric generated by a Prometheus data source.
query_end_time	Returns the latest time of the query time window.
query_start_time	Returns the earliest time of the query time window.
rank	Returns the rank within an ordered group of values.
rate	Calculate the average per-second rate of the argument across the group.
regex	Coerce a string literal to a regular expression.
replace	Replaces all instances of the substring in the input string with a provided value.
replace_regex	Replaces all instances of a matched regex pattern in the input string with a provided value.
right	Returns a rightmost substring of its input.
round	Returns ‘val’ rounded to the given ‘precision’.
row_end_time	Returns the value of the “Valid To” column, or null if the verb input dataset has no “Valid To” column.
row_number	Return the window index of the row within its groupby, when ordered by the orderby.
row_timestamp	Returns the value of the timestamp column for each row.
rpad	Right pads a string with characters from another string, default pad string is whitespace
rtrim	rtrim removes trailing characters from a string.
same	Return true if A is the same as B.
search	Return true if the for text is matched in the input string in (case-insensitive).
slice_array	Given an array, returns a possibly smaller array, starting at the given from index, and including items up to *but not
sort_array	Return an array with the elements of the input array in sorted order.
split	Splits the string into an array, based on the separator.
split_part	Splits a given string at a specified delimiter character and returns the requested part.
sqrt	Returns the square root for a given input and null if input is negative.
starts_with	Returns true if string starts with expr.
stddev	Calculate the standard deviation across the group.
string	Generate a string representation of the argument value.
string_agg	Returns concatenated input values, separated by the delimiter.
string_agg_distinct	Returns concatenated input values, separated by the delimiter.
string_null	Returns a null value of type string.
strlen	Compute the length of an input string.
substring	Extracts characters from a string, starting at an index.
sum	Calculate the sum of the argument across the group, or of the scalar arguments if more than one.
tdigest_agg	Generate a single t-digest state out of an arbitrary number of numeric values.
tdigest_combine	Combine (merge) multiple t-digest values together This function is purely an aggregate function.
tdigest_null	Returns a null value of type tdigest.
tdigest_quantile	Calculates a quantile from a TDigest state.
timestamp_null	Returns a null value of type timestamp.
to_days	Convert a duration to a number of days.
to_hours	Convert a duration to a number of hours.
to_milliseconds	Convert a duration to a number of milliseconds.
to_minutes	Convert a duration to a number of minutes.
to_nanoseconds	Convert a duration to a number of nanoseconds.
to_seconds	Convert a duration to a number of seconds.
to_weeks	Convert a duration to a number of weeks.
tokenize	Splits the string into an array based on separator, which is treated as a set of characters.
tokenize_part	Tokenizes the input string using the delimiter and returns the requested part.
topk_agg	Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.
trim	trim removes leading and trailing characters from a string.
uniform	Generates a uniformly-distributed pseudo-random number in the inclusive range [min, max].
unpivot_array	Convert an object into an array of “key”-“value” pairs.
upper	Return the input string in uppercase.
valid_for	Specify the validity period for each event for some verbs
variant_null	Returns a null value of type variant.
variant_type_name	Resolves the data type of a variant column so that column may be typed for better performance.
width_bucket	Construct equi-width histogram by dividing up the range between min and max with num_buckets buckets, and compute an integer
window	Evaluate OPAL within time windows.
zipf	Returns a Zipf-distributed integer, for N elements and characteristic exponent s.

Aliases

Aliases work as alternative names for their main function. Users of certain other query languages may be more comfortable with these alternative names. Alias names on this list can always be used whenever the main name can be used.

Alias	Function
timestamp_ms	from_milliseconds
timestamp_ns	from_nanoseconds
timestamp_s	from_seconds
pk	primary_key
row_start_time	row_timestamp