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

any_null

Returns a null value of type any.

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_length

returns the number of elements in an array, or null if input is not 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.

avg

Calculate the arithmetic average of the input expression across the group.

bin_end_time

Returns the end time of the current bin (exclusive).

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 the given condition is true.

ceil

Returns ‘val’ rounded up to the given ‘precision’.

coalesce

Return the first non-null argument or null if all are null.

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 (slower than count_distinct).

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.

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.

detect_browser

Given the contents of a web request User-Agent header string, determine which

drop_fields

Drop one or more fields from an object.

duration

Convert a number or timestamp, or a time interval, to a duration.

duration_hr

Convert a number of hours to a duration.

duration_min

Convert a number of minutes to a duration.

duration_ms

Convert a number of milliseconds to a duration.

duration_null

Returns a null value of type duration.

duration_sec

Convert a number of seconds to a duration.

editdistance

Returns the Levenshtein distance between str1 and str2.

embed_sql_params

Embed parameters into the given prepared SQL statement by replacing occurrences

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 the timestamp value in UTC according to the specified format.

frame

Specify the relative time frame for a window context.

frame_exact

Specify the 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

get_item

Given an array, and a computed index, return the item at that index in the

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.

haversine_distance_km

Computes the great circle approximate distance between two latitude/longitude

if

Return the second argument if condition# is true, otherwise return third argument.

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

int64

Generate a int representation of the argument value.

int64_null

Returns a null value of type int64.

int64_to_ipv4

Given an integer that represents an IPv4 address in big-endian (network) byte order, return the corresponding dotted-quad

int_div

Returns the integer quotient of the dividend divided by the divisor and truncates the remainder.

ipv4_address_in_network

Given an IPv4 address string in dotted-quad format on the left, and a subnet

ipv4_network_int64

Given an IPv4 address string in dotted-quad format, possibly with a slash-masksize trailing component, convert the address to an

ipv4_to_int64

Given an IPv4 address string in dotted-quad format, convert the address to an integer representing the address in big-endian

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

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

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.

make_array

Returns an array constructed from zero, one or more input arguments.

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.

match_regex_all

Returns an array containing all parts of the argument string that match the

max

Compute the maximum of one column across a group (with one argument) or the scalar greatest value of its arguments (with more than

median

Return the fast approximate median value of one column.

median_exact

Return the exact median value of one column.

metric

Select the metrics in the rollup verb.

min

Compute the minimum of one column across a group (with one argument) or the scalar least value of its arguments (with more than

mod

Returns the remainder when dividend is divided by the divisor.

ne

Return true if A is not equal to B.

numeric_null

Returns a null value of type numeric.

object

Convert a datum into an object or NULL if conversion is impossible

object_agg

Returns one OBJECT per group.

object_keys

Get array of object keys (field names from object).

object_null

Returns a null value of type object.

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

parse_hex

Parses a string encoded hex number and returns an int64.

parse_ip

When the input is an IPv(4/6) address, returns a JSON object containing the following attributes - family (either “4” or “6”)

parse_isotime

Parse a 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_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, it returns the value for the specified percentile of the input expression across the group.

percentile_disc

Assuming a discrete distribution, it returns 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) and, if successful, returns the

pow

Returns a number ‘base’ raised to the specified power ‘exponent’.

primary_key

Specify the primary key for some verbs

prom_quantile

prom_quantile calculates an approximate percentile value based on the

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 time at which the state in the row ended, or null for non-resource datasets.

row_number

Return the window index of the row within its groupby, when ordered by the orderby.

row_timestamp

Returns the timestamp (start time) of the row.

rpad

Right pads a string with characters from another string, default pad string is whitespace

rtrim

rtrim removes trailing characters from a string.

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, constructed by starting at the

split

Splits the string into an array, based on the separator.

split_part

Splits a given string at a specified 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_concat

Return the concatenation of all string arguments.

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.

timestamp_null

Returns a null value of type timestamp.

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.

trim

trim removes leading and trailing characters from a string.

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_type_name

Given a variant value (typically, something you get from looking up a path or element in a JSON object or array,) determine what

width_bucket

Construct equi-width histogram by dividing up the range between min and max

window

Evaluates its argument in windowed context, partitioned over the given grouping and ordered by the given ordering (by default

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.