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…

any_null

Returns a null value of type any. This is important,…

array

Convert a datum into an array or NULL if conversion…

array_agg

Returns an array of concatenated input values. The…

array_length

returns the number of elements in an array, or null…

array_null

Returns a null value of type array. This is importan…

avg

Calculate the arithmetic average of the input…

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. This is important…

case

Return a matching result if the given condition

ceil

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

coalesce

Return the first non-null argument or null if all…

contains

Returns true if the str string contains the expr

count

Count the number of non-null items in the group.

count_distinct

Estimate the approximate number of distinct values…

count_distinct_exact

Count the exact number of distinct values in the…

decode_base64

DecodeBase64 decodes a base64 encoded input

decode_uri

Replace %-encoded escape sequences in a string with…

decode_uri_component

Replace all %-encoded escape sequences in a string…

dense_rank

Returns the dense rank within an ordered group of…

deriv

Calculate the average per-second derivative of the…

drop_fields

Drop one or more fields from an object.

duration

Convert a number or timestamp, or a time interval,…

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. This is…

duration_sec

Convert a number of seconds to a duration.

encode_base64

EncodeBase64 encodes the input in the base64 format

encode_uri

Replace certain characters in a string with %-encode…

encode_uri_component

Replace certain characters in a string with %-encode…

ends_with

Returns true if string str ends with string…

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…

first_not_null

Return the first non-null value of one column across…

float64

Generate a float representation of the argument value.

float64_null

Returns a null value of type float64. This is…

floor

Returns ‘val’ rounded down to the given ‘precision’….

format_time

Format the timestamp value in UTC according to the…

frame

Specify the relative time frame for a window…

frame_exact

Specify the relative time frame for a window…

from_milliseconds

Given a numeric value representing milliseconds…

from_nanoseconds

Given a numeric value representing nanoseconds since…

from_seconds

Given a numeric value representing seconds since…

get_field

Given an object, and a computed string value as key…

get_item

Given an array, and a computed index, return the…

group_by

Grouping/partitioning in which to process data. This…

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…

haversine_distance_km

Computes the great circle approximate distance…

if

Return the second argument if condition# is true,…

if_null

Return the second argument if the first argument has…

in

Returns true if the expression matches with any…

int64

Generate a int representation of the argument value….

int64_null

Returns a null value of type int64. This is importan…

is_null

Return true if the argument has the null value.

lag

Return the lag of one column across an ordered…

last

Return the last value of one column across an…

last_not_null

Return the last non-null value of one column across…

lead

Return the lead of one column across an ordered…

left

Returns a leftmost substring of its input.

like

Returns true if subject matches pattern (case-sensit…

ln

Returns natural logarithm of a numeric expression….

log

Returns logarithm of a numeric expression (second…

lower

Return the input string in lowercase.

lpad

Left pads a string with characters from another…

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…

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…

match_regex_all

Returns an array containing all parts of the…

max

Compute the maximum of one column across a group…

median

Return the fast approximate median value of one…

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…

mod

Returns the the remainder when dividend is divided…

ne

Return true if A is not equal to B.

numeric_null

Returns a null value of type numeric. This is…

object

Convert a datum into an object or NULL if conversion…

object_agg

Returns one OBJECT per group. For each (key, value)…

object_keys

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

object_null

Returns a null value of type object. This is…

options

Specify options to change the verb’s behavior

order_by

Specify the ordering when processing data. This is…

parse_csv

Parses an input string as character-separated…

parse_hex

Parses a string encoded hex number and returns an…

parse_ip

When the input is an IPv(4/6) address, returns a…

parse_isotime

Parse a YYYY-MM-DDTHH:MM:SSZ-formatted string as a…

parse_json

Parse the argument value as a JSON string.

parse_kvs

Returns an object of key=value pairs extracted from…

parse_url

Returns a JSON object consisting of all the componen…

path_exists

Given a column and path, return whether the JSON…

percentile

Returns an approximated value for the specified…

percentile_cont

Assuming a continuous distribution, it returns the…

percentile_disc

Assuming a discrete distribution, it returns the…

pick_fields

Pick one or more fields from an object.

pivot_array

Converts an array of “key”-“value” pairs into an…

position

Searches for the first occurrence of the second…

pow

Returns a number ‘base’ raised to the specified…

primary_key

Specify the primary key for some verbs

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…

replace

Replaces all instances of the substring in the input…

replace_regex

Replaces all instances of a matched regex pattern in…

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…

row_number

Return the window index of the row within its…

row_timestamp

Returns the timestamp (start time) of the row.

rpad

Right pads a string with characters from another…

rtrim

rtrim removes trailing characters from a string….

search

Return true if the for text is matched in the…

slice_array

Given an array, returns a possibly smaller array,…

split

Splits the string into an array, based on the…

split_part

Splits a given string at a specified character and…

sqrt

Returns the square root for a given input and null…

starts_with

Returns true if string starts with expr.

stddev

Calculate the standard deviation across the group.

strcat

Return the concatenation of all string arguments.

string

Generate a string representation of the argument…

string_agg

Returns concatenated input values, separated by the…

string_null

Returns a null value of type string. This is…

strlen

Compute the length of an input string.

substring

Extracts characters from a string, starting at an…

sum

Calculate the sum of the argument across the group,…

timestamp_null

Returns a null value of type timestamp. This is…

tokenize

Splits the string into an array based on separator,…

tokenize_part

Tokenizes the input string using the delimiter and…

trim

trim removes leading and trailing characters from…

unpivot_array

Convert an object into an array of “key”-“value”…

upper

Return the input string in uppercase.

valid_for

Specify the validity period for each event for some…

window

Evaluates its argument in windowed context, partitio…

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.