List of OPAL functions

abs (Abs)

Description

Returns the absolute value of ‘val’.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

abs( val )

Examples

make_col all_positive:abs(@.mixed)

Create a column ‘all_positive’ with the absolute values of column ‘mixed’

Arguments

Argument

Type

Required

Multiple

val

numeric

True

False

any (Any)

Description

Return any value of one column across a group

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

any( expression )

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

any_not_null (Any Not Null)

Description

Return any non-null value of one column across a group. Can still return null if all values in the group are null

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

any_not_null( expression )

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

any_null (Any Null)

Description

Returns a null value of type any. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

any_null()

Examples

make_col positive_or_null:case(x > 0, x, true, any_null())

Create a column ‘positive_or_null’ which is either a positive any, or the null any.

array (To Array)

Description

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

Return type

array

Domain

This is a scalar function (acts on values from each row individually.)

Usage

array( value )

Examples

make_col arr:array(variant)

Make a new column ‘arr’ from casting variant to array.

Arguments

Argument

Type

Required

Multiple

value

any

True

False

array_agg (Array Aggregation)

Description

Returns an array of concatenated input values. The expression must be a valid expression (not a regex or other type). If no ordering is specified, the default ordering is by ‘valid_from’, ascending.

Return type

array

Domain

This is an aggregate function (aggregates rows over a group in statsby.)

Usage

array_agg( expr, [ orderby ] )

Examples

statsby nicknames:array_agg(nickname, order_by(email)), group_by(uid, fullname)

An array containing all nicknames for each individual in the organization; for users with more than one nickname they will be sorted by email address.

Arguments

Argument

Type

Required

Multiple

expr

any

True

False

orderby

ordering

False

False

array_length (Array Length)

Description

returns the number of elements in an array, or null if input is not an array

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

array_length( array )

Arguments

Argument

Type

Required

Multiple

array

array

True

False

array_null (Array Null)

Description

Returns a null value of type array. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

array

Domain

This is a scalar function (acts on values from each row individually.)

Usage

array_null()

Examples

make_col positive_or_null:case(x > 0, x, true, array_null())

Create a column ‘positive_or_null’ which is either a positive array, or the null array.

avg (Average)

Description

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

Return type

numeric

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

avg( value, ... )

Arguments

Argument

Type

Required

Multiple

value

numeric

True

True

bin_end_time (Bin End Time)

Description

Returns the end time of the current bin (exclusive). Can only be used with timechart.

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

bin_end_time()

Examples

timechart 10m, last_to_bin_end:min(bin_end_time() - timestamp), group_by(key)

Create a column named “last_to_bin_end” that contains the difference between the last data point in the bin and the bin’s end time.

bin_start_time (Bin Start Time)

Description

Returns the start time of the current bin (inclusive). Can only be used with timechart.

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

bin_start_time()

Examples

timechart 10m, first_to_bin_start:min(timestamp - bin_start_time()), group_by(key)

Create a column named “first_to_bin_start” that contains the difference between the first data point in the bin and the bin’s start time.

bool (Make Boolean)

Description

Generate a boolean value of the argument value.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

bool( value )

Arguments

Argument

Type

Required

Multiple

value

any

True

False

bool_null (Bool Null)

Description

Returns a null value of type bool. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

bool_null()

Examples

make_col positive_or_null:case(x > 0, x, true, bool_null())

Create a column ‘positive_or_null’ which is either a positive bool, or the null bool.

case (Case)

Description

Return result# if condition# is true. If no condition matches, return NULL. Conditions and results are evaluated in pairs in order of argument.

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

case( condition#, ..., result#, ... )

Examples

filter b=case(a=true, 'foo', a=false, 'bar')

Filter to return rows where, if a is ‘true’, b equals ‘foo’, else if a is false, b equals ‘bar’.

Arguments

Argument

Type

Required

Multiple

condition#

bool

True

True

result#

any

True

True

ceil (Ceil)

Description

Returns ‘val’ rounded up to the given ‘precision’. Precision defaults to 0, meaning the value will be rounded to the nearest integer.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

ceil( val, [ precision ] )

Examples

make_col rounded:ceil(@.temperature, 2)

Return the rounded up value of column temperature with 2 decimals

Arguments

Argument

Type

Required

Multiple

val

numeric

True

False

precision

int64

False

False

coalesce (Coalesce to first Non-Null)

Description

Return the first non-null argument or null if all are null. Arguments must have the same type.

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

coalesce( arg1, arg2, ... )

Examples

make_col foo:coalesce(bar, baz, 0)

Replace the value of column ‘foo’ with ‘bar’ if it isn’t null or ‘baz’ if it isn’t null or 0.

Arguments

Argument

Type

Required

Multiple

arg1

any

True

False

arg2

any

True

True

contains (Contains)

Description

Returns true if string contains expr.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

contains( str, expr )

Examples

filter contains(@.bundle_kind, "kube")

Pass through all bundle kinds that contain the string ‘kube’.

Arguments

Argument

Type

Required

Multiple

str

string

True

False

expr

string

True

False

count (Count Values)

Description

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

Return type

int64

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

count( item )

Arguments

Argument

Type

Required

Multiple

item

any

True

False

count_distinct (Count Distinct Fast)

Description

Estimate the approximate number of distinct values in the input using hyper-log-log.

Aliases: countdistinct(deprecated)

Return type

int64

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

count_distinct( item )

Arguments

Argument

Type

Required

Multiple

item

any

True

False

count_distinct_exact (Count Distinct Exact)

Description

Count the exact number of distinct values in the input using complete enumeration (slower than count_distinct).

Aliases: countdistinctexact(deprecated)

Return type

int64

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

count_distinct_exact( item )

Arguments

Argument

Type

Required

Multiple

item

any

True

False

decode_base64 (Decode Base64)

Description

DecodeBase64 decodes a base64 encoded input

Aliases: decodebase64(deprecated)

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

decode_base64( str, [ urlSafe ] )

Examples

make_col decoded: decode_base64(data)

Decodes the value of the base64 encoded field data

make_col decoded: decode_base64(log, true)

Decodes the value of the URL safe base64 encoded field data

Arguments

Argument

Type

Required

Multiple

str

string

True

False

urlSafe

bool

False

False

decode_uri (Decode URI)

Description

Replace %-encoded escape sequences in a string with unencoded plain text. However, encoded characters in the set #$&+,/:;=?@ are kept encoded. NULL if input contains an invalid encoded sequence.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

decode_uri( str )

Examples

make_col d:decode_uri('%32%5E%33%20%3D%20%38%3B')

Result is ‘2^3 %3D 8%3B’

Arguments

Argument

Type

Required

Multiple

str

string

True

False

decode_uri_component (Decode URI Component)

Description

Replace all %-encoded escape sequences in a string with unencoded plain text. NULL if input contains an invalid encoded sequence.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

decode_uri_component( str )

Examples

make_col dc:decode_uri_component('%32%5E%33%20%3D%20%38%3B')

Result is ‘2^3 = 8;’

Arguments

Argument

Type

Required

Multiple

str

string

True

False

dense_rank (Dense Rank)

Description

Returns the dense rank within an ordered group of values. Default ordering is in ascending time, so the first value has the lowest rank.

Aliases: denserank(deprecated)

Return type

int64

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

dense_rank()

Examples

make_col index:window(dense_rank(), group_by(category_id), order_by(item_cost))

Assigns a dense rank to items, when ordered by cost, grouped by category. The cheapest item in each category will be given the rank 1, items with the same cost within the same category will receive the same rank. Dense means there are no gaps assigned between ranks.

drop_fields (Drop Fields)

Description

Drop one or more fields from an object.

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

drop_fields( column, key, ... )

Examples

make_col smaller:drop_fields(obj, 'key1', 'key2')

Create a column ‘smaller’ based on column ‘obj’ with ‘key1’ and ‘key2’ dropped.

Arguments

Argument

Type

Required

Multiple

column

object

True

False

key

string

True

True

duration (Make Duration (ns))

Description

Convert a number or timestamp, or a time interval, to a duration. Numbers are assumed to be nanoseconds

Return type

duration

Domain

This is a scalar function (acts on values from each row individually.)

Usage

duration( numberOrTimestamp )
duration( start_timestamp, end_timestamp )

Arguments

Argument

Type

Required

Multiple

numberOrTimestamp

timestamp

True

False

Arguments

Argument

Type

Required

Multiple

start_timestamp

timestamp

True

False

end_timestamp

timestamp

True

False

duration_hr (Make Duration (hr))

Description

Convert a number of hours to a duration.

Return type

duration

Domain

This is a scalar function (acts on values from each row individually.)

Usage

duration_hr( hr )

Arguments

Argument

Type

Required

Multiple

hr

int64

True

False

duration_min (Make Duration (min))

Description

Convert a number of minutes to a duration.

Return type

duration

Domain

This is a scalar function (acts on values from each row individually.)

Usage

duration_min( min )

Arguments

Argument

Type

Required

Multiple

min

int64

True

False

duration_ms (Make Duration (ms))

Description

Convert a number of milliseconds to a duration.

Return type

duration

Domain

This is a scalar function (acts on values from each row individually.)

Usage

duration_ms( ms )

Arguments

Argument

Type

Required

Multiple

ms

int64

True

False

duration_null (Duration Null)

Description

Returns a null value of type duration. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

duration

Domain

This is a scalar function (acts on values from each row individually.)

Usage

duration_null()

Examples

make_col positive_or_null:case(x > 0, x, true, duration_null())

Create a column ‘positive_or_null’ which is either a positive duration, or the null duration.

duration_sec (Make Duration (s))

Description

Convert a number of seconds to a duration.

Return type

duration

Domain

This is a scalar function (acts on values from each row individually.)

Usage

duration_sec( sec )

Arguments

Argument

Type

Required

Multiple

sec

int64

True

False

encode_base64 (Encode Base64)

Description

EncodeBase64 encodes the input in the base64 format

Aliases: encodebase64(deprecated)

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

encode_base64( str, [ urlSafe ] )

Examples

make_col encoded:encode_base64(log)

Encodes the value of the field log in the base64 encoding

make_col encoded:encode_base64(log, true)

Encodes the value of the field log in the url safe base64 encoding

Arguments

Argument

Type

Required

Multiple

str

string

True

False

urlSafe

bool

False

False

encode_uri (Encode URI)

Description

Replace certain characters in a string with %-encoded escape sequences. Letters A-Z a-z, digits 0-9, and !#$&’()*+,-./:;=?@ remain unchanged. All others are encoded. NULL if input contains an invalid UTF-8 surrogate sequence.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

encode_uri( str )

Examples

make_col e:encode_uri('2^3 = 8;')

Result is ‘2%5E3%20=%208;’

Arguments

Argument

Type

Required

Multiple

str

string

True

False

encode_uri_component (Encode URI Component)

Description

Replace certain characters in a string with %-encoded escape sequences. Letters A-Z a-z, digits 0-9, and !’()*-. remain unchanged. All others are encoded. NULL if input contains an invalid UTF-8 surrogate sequence.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

encode_uri_component( str )

Examples

make_col ec:encode_uri_component('2^3 = 8;')

Result is ‘2%5E3%20%3D%208%3B’

Arguments

Argument

Type

Required

Multiple

str

string

True

False

ends_with (Ends With)

Description

Returns true if string ends with expr.

Aliases: endswith(deprecated)

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

ends_with( str, expr )

Examples

filter ends_with(@.bundle_kind, "kube")

Pass through all bundle kinds that end with the string ‘kube’.

Arguments

Argument

Type

Required

Multiple

str

string

True

False

expr

string

True

False

eq (=)

Description

Return true if A is equal to B.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

eq( a, b )

Arguments

Argument

Type

Required

Multiple

a

any

True

False

b

any

True

False

exp (Exp)

Description

Returns Euler’s number e raised to the given number.

Return type

float64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

exp( value )

Examples

make_col exp_temperature:exp(@.temperature)

Returns a new column exp_temperature with Euler’s number e being raised to the values of the temperature column.

Arguments

Argument

Type

Required

Multiple

value

numeric

True

False

first (First)

Description

Return the first value of one column across an ordered group. Default ordering is ascending time, so the first value is the earliest

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

first( expression )

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

first_not_null (First Not Null)

Description

Return the first non-null value of one column across an ordered group. Default ordering is ascending time, so the first value is the earliest

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

first_not_null( expression )

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

float64 (Make Float)

Description

Generate a float representation of the argument value.

Return type

float64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

float64( value )

Arguments

Argument

Type

Required

Multiple

value

any

True

False

float64_null (Float64 Null)

Description

Returns a null value of type float64. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

float64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

float64_null()

Examples

make_col positive_or_null:case(x > 0, x, true, float64_null())

Create a column ‘positive_or_null’ which is either a positive float64, or the null float64.

floor (Floor)

Description

Returns ‘val’ rounded down to the given ‘precision’. Precision defaults to 0, meaning the value will be rounded to the nearest integer.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

floor( val, [ precision ] )

Examples

make_col rounded:floor(@.temperature, 2)

Return the rounded down value of column temperature with 2 decimals

Arguments

Argument

Type

Required

Multiple

val

numeric

True

False

precision

int64

False

False

format_time (Format Timestamp)

Description

Format the timestamp value in UTC according to the specified format. Format specifiers are detailed in Snowflake’s time format specifiers documentation

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

format_time( time, format )

Examples

make_col year:format_time(@."Valid From", "YYYY")

Extracts the year from the “Valid From” column

make_col formatted:format_time(@."Valid From", 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')

Format the “Valid From” column according to ISO 8601

make_col formatted:format_time(@."Valid From", 'DY MON DD HH24:MI:SS YYYY')

Format the “Valid From” column similar to ctime’s format

make_col formatted:format_time(@."Valid From", 'MM/DD/YYYY HH12:MI:SS')

Format the “Valid From” column using US’s date format and a 12-hour clock.

Arguments

Argument

Type

Required

Multiple

time

timestamp

True

False

format

string

True

False

frame (Frame)

Description

Specify the relative time frame for a window context. The frame will start from the current row’s “Valid From” time minus “back”, and end at “Valid From” plus “ahead” (both ends are inclusive). For better performance, the window frame boundaries may not be exact and can deviate by at most 1/120th of the total frame size (or 10 seconds, whichever is larger). To make the window boundaries exact, at the cost of slower performance, use the frame_exact() function.

Return type

frame

Domain

This is a scalar function (acts on values from each row individually.)

Usage

frame( back, ahead )

Examples

make_col avg:window(avg(load), group_by(host), order_by(time), frame(back:10m))

Compute the moving average of system load within the past 10 minutes of each event

Arguments

Argument

Type

Required

Multiple

back

expression

True

False

ahead

expression

True

False

frame_exact (Frame Exact)

Description

Specify the relative time frame for a window context. This is the exact version of frame(), where the window frame start and end times are exactly “Valid From” minus “back” and “Valid To” plus “ahead”. Evaluation of exact window frames can be slow when the data volume is large.

Return type

frame

Domain

This is a scalar function (acts on values from each row individually.)

Usage

frame_exact( back, ahead )

Examples

make_col avg:window(avg(load), group_by(host), order_by(time), frame_exact(back:10m))

Compute the moving average of system load within the past 10 minutes of each event

Arguments

Argument

Type

Required

Multiple

back

expression

True

False

ahead

expression

True

False

from_milliseconds (Milliseconds)

Description

Given a numeric value representing milliseconds since epoch, return a timestamp of that point in time.

Aliases: timestamp_ms, milliseconds(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

from_milliseconds( number )

Examples

make_col out_ms:from_milliseconds(in_fld)

Treat in_fld as milliseconds since epoch, and set out_ms to a timestamp representing that time.

Arguments

Argument

Type

Required

Multiple

number

numeric

True

False

from_nanoseconds (Nanoseconds)

Description

Given a numeric value representing nanoseconds since epoch, return a timestamp of that point in time.

Aliases: timestamp_ns, nanoseconds(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

from_nanoseconds( number )

Examples

make_col out_ns:from_nanoseconds(in_fld)

Treat in_fld as nanoseconds since epoch, and set out_ns to a timestamp representing that time.

Arguments

Argument

Type

Required

Multiple

number

numeric

True

False

from_seconds (Seconds)

Description

Given a numeric value representing seconds since epoch, return a timestamp of that point in time.

Aliases: timestamp_s, seconds(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

from_seconds( number )

Examples

make_col out_s:from_seconds(in_fld)

Treat in_fld as seconds since epoch, and set out_s to a timestamp representing that time.

Arguments

Argument

Type

Required

Multiple

number

numeric

True

False

group_by (Group By)

Description

Grouping/partitioning in which to process data

Aliases: groupby(deprecated)

Return type

grouping

Domain

This is a scalar function (acts on values from each row individually.)

Usage

group_by( columnname, ... )

Arguments

Argument

Type

Required

Multiple

columnname

expression

True

True

gt (>)

Description

Return true if A is strictly greater than B.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

gt( a, b )

Arguments

Argument

Type

Required

Multiple

a

any

True

False

b

any

True

False

gte (>=)

Description

Return true if A is greater than or equal to B.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

gte( a, b )

Arguments

Argument

Type

Required

Multiple

a

any

True

False

b

any

True

False

hash (Hash)

Description

Accepts a variable number of arguments of arbitrary types and returns a signed 64-bit hash. Never returns null. Not a cryptographic hash function and should not be used as such.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

hash( args, ... )

Examples

make_col h:hash(@.x, @.y)

Create a column ‘h’ with the result of hashing the columns ‘x’ and ‘y’

Arguments

Argument

Type

Required

Multiple

args

any

True

True

if (If)

Description

Return the second argument if condition# is true, otherwise return third argument. Arguments must have the same general type.

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

if( condition, ontrue, onfalse )

Examples

filter b=if(a=true, 'foo', 'bar')

Filter input to rows where b is equal to ‘foo’ if ‘a’ is true and ‘bar’ otherwise

Arguments

Argument

Type

Required

Multiple

condition

bool

True

False

ontrue

any

True

False

onfalse

any

True

False

if_null (Replace Null)

Description

Return the second argument if the first argument has the null value. Arguments must have the same type.

Aliases: ifnull(deprecated)

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

if_null( arg, replacement )

Examples

make_col foo:if_null(foo, 0)

Replace the value of column ‘foo’ with the value 0 if the value is null.

Arguments

Argument

Type

Required

Multiple

arg

any

True

False

replacement

any

True

False

int64 (Make Integer)

Description

Generate a int representation of the argument value. Float values are rounded to the nearest integer, and timestamps are converted to nanosecond epoch values.

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

int64( value )

Arguments

Argument

Type

Required

Multiple

value

any

True

False

int64_null (Int64 Null)

Description

Returns a null value of type int64. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

int64_null()

Examples

make_col positive_or_null:case(x > 0, x, true, int64_null())

Create a column ‘positive_or_null’ which is either a positive int64, or the null int64.

is_null (Test Null)

Description

Return true if the argument has the null value.

Aliases: isnull(deprecated)

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

is_null( arg )

Arguments

Argument

Type

Required

Multiple

arg

any

True

False

lag (Lag)

Description

Return the lag of one column across an ordered group. Default ordering is ascending time, so the lag value is the most recent prior value

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

lag( expression, lagby )

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

lagby

int64

True

False

last (Last)

Description

Return the last value of one column across an ordered group. Default ordering is ascending time, so the last value is the latest

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

last( expression )

Examples

make_col last_customer:window(last(customer_id), group_by(category))

Find out the customer that appears last within each category.

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

last_not_null (Last Not Null)

Description

Return the last non-null value of one column across an ordered group. Default ordering is ascending time, so the last value is the latest

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

last_not_null( expression )

Examples

make_col last_value:window(last_not_null(value), group_by(kind))

Find out the last non-null within each kind.

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

lead (Lead)

Description

Return the lead of one column across an ordered group. Default ordering is ascending time, so the lead value is the next value

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

lead( expression, leadby )

Arguments

Argument

Type

Required

Multiple

expression

any

True

False

leadby

int64

True

False

left (Left)

Description

Returns a leftmost substring of its input.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

left( value, length )

Examples

make_col leftstring:left(somestring, 4)

Will make a ‘leftstring’ column with the left 4 characters of the text in the ‘somestring’ column.

Arguments

Argument

Type

Required

Multiple

value

string

True

False

length

int64

True

False

like (Like)

Description

Returns true if subject matches pattern (case-sensitive). Within pattern, escape can be included to denote that the character following it be interpreted literally.

The arguments pattern and escape must be string literals. The argument pattern may include wildcards _ (matches exactly one character) and % (matches zero or more characters). If included, escape may only be a single character. If using backslash as the escape character, it must be escaped in the escape clause (see example).

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

like( subject, pattern, [ escape ] )

Examples

filter like(@.bundle_kind, "%kube%")

Pass through all bundle kinds that contain the string ‘kube’.

filter like(@.bundle_kind, "k^%__", "^")

Pass through all bundle kinds that are 4 characters long and begin with ‘k%’.

filter like(@.bundle_kind, "k\\%__", "\\")

Pass through all bundle kinds that are 4 characters long and begin with ‘k%’.

Arguments

Argument

Type

Required

Multiple

subject

string

True

False

pattern

string

True

False

escape

string

False

False

ln (Ln)

Description

Returns natural logarithm of a numeric expression. The value should be greater than 0.

Return type

float64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

ln( value )

Examples

make_col ln_val:ln(@.temperature)

Returns the natural logarithm of column temperature

Arguments

Argument

Type

Required

Multiple

value

numeric

True

False

log (Log)

Description

Returns logarithm of a numeric expression (second argument) with the provided base (first argument). The base should be greater than 0 and not exactly 1.0 and the value should be greater than 0.

Return type

float64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

log( base, value )

Examples

make_col log_val:log(10, @.temperature)

Returns the logarithm of column temperature at base 10

Arguments

Argument

Type

Required

Multiple

base

numeric

True

False

value

numeric

True

False

lower (Lowercase)

Description

Return the input string in lowercase.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

lower( value )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

lpad (Left Pad)

Description

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

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

lpad( str, length, [ pad ] )

Examples

make_col padded_kind:lpad(bundle_kind, 100, '%')

Create a new column ‘padded_kind’ which has a length of 100 characters, with missing characters added as percents on the left.”

Arguments

Argument

Type

Required

Multiple

str

string

True

False

length

int64

True

False

pad

string

False

False

lt (<)

Description

Return true if A is strictly less than B.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

lt( a, b )

Arguments

Argument

Type

Required

Multiple

a

any

True

False

b

any

True

False

lte (<=)

Description

Return true if A is less than or equal to B.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

lte( a, b )

Arguments

Argument

Type

Required

Multiple

a

any

True

False

b

any

True

False

ltrim (Left Trim)

Description

LTrim removes leading characters from a string

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

ltrim( str, [ chars ] )

Examples

make_col trimmed_kind:ltrim(bundle_kind, ' ')

Removes leading spaces from bundle kinds

Arguments

Argument

Type

Required

Multiple

str

string

True

False

chars

string

False

False

make_fields (Make Fields)

Description

Extend an existing object with new fields.

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

make_fields( column, entries, ... )

Examples

make_col larger:make_fields(obj, key1:'value1', key2:'value2')

Create a column ‘larger’ based on column ‘obj’ with two key value pairs added (existing fields with the same name will be replaced).

Arguments

Argument

Type

Required

Multiple

column

object

True

False

entries

expression

True

True

make_object (Make Object)

Description

Turn a sequence of name:value elements into an object.

Aliases: makeobject(deprecated)

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

make_object( [ key, ... ] )

Examples

make_col obj:make_object(label:"speed", value:distance/(endtime-starttime), attime:endtime)

Make a new column ‘obj’ consisting of an object with keys ‘label’, ‘value’, and ‘attime’.

make_col obj:make_object()

Make a new column ‘obj’ consisting of an empty object that is not ‘null’.

Arguments

Argument

Type

Required

Multiple

key

expression

False

True

match_regex (Match RegEx)

Description

Return true if the argument input string matches the argument regular expression. The last parameter specifies optional regex flags:

  • c - Enables case-sensitive matching (default.)

  • i - Enables case-insensitive matching.

  • m - Enables multi-line mode (i.e. meta-characters ^ and $ match the beginning and end of any line of the input string.) By default, multi-line mode is disabled (i.e. ^ and $ match the beginning and end of the entire input string.)

  • s - Enables the POSIX wildcard character . to match \n (newline.) By default, . does not match \n.

For more about syntax, see POSIX extended regular expressions.

Aliases: regex_match(deprecated)

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

match_regex( input_string, pattern, [ flags ] )

Examples

filter match_regex(log, /^debug/, 'i')

Filter field ‘log’ for results matching the specified regular expression, using case-insensitive matching.

Arguments

Argument

Type

Required

Multiple

input_string

string

True

False

pattern

regex

True

False

flags

string

False

False

max (Maximum)

Description

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

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

max( expression, ... )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

True

median (Median)

Description

Return the fast approximate median value of one column.

Return type

float64

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

median( expression )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

False

median_exact (Median Exact)

Description

Return the exact median value of one column.

Aliases: medianexact(deprecated)

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

median_exact( expression )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

False

metric (Metric)

Description

Select the metrics in the rollup verb.

Return type

options

Domain

This is a scalar function (acts on values from each row individually.)

Usage

metric( name, [ filter ], [ label ], [ type ], [ unit ], [ description ], [ rollup ], [ aggregate ] )

Examples

metric("requests_total", return_code >= 400 and return_code <= 599)

Select the metric ‘requests_total’ within this dataset where return_code is between 400 and 599. The dataset must already implement the “metric” interface.

metric("requests_total", label:"Request Rate", type:"cumulativeCounter", unit:"1/s", description:"Number of requests processed per second.", rollup:"rate", aggregate:"sum")

Select the metric ‘requests_total’ for rollup, and overwrite the new metric’s definition with the specified label, type, unit, description, rollup method and aggregate method. The dataset must already implement the “metric” interface.

Arguments

Argument

Type

Required

Multiple

name

string

True

False

filter

bool

False

False

label

expression

False

False

type

expression

False

False

unit

expression

False

False

description

expression

False

False

rollup

expression

False

False

aggregate

expression

False

False

min (Minimum)

Description

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

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

min( expression, ... )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

True

mod (Modulo)

Description

Returns the the remainder when dividend is divided by the divisor.

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

mod( dividend, divisor )

Examples

make_col remaining:mod(@.billed_days, 7)

Create a column ‘remaining’ with the number of billed days that are not counted in weeks

Arguments

Argument

Type

Required

Multiple

dividend

int64

True

False

divisor

int64

True

False

ne (<>)

Description

Return true if A is not equal to B.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

ne( a, b )

Arguments

Argument

Type

Required

Multiple

a

any

True

False

b

any

True

False

numeric_null (Numeric Null)

Description

Returns a null value of type numeric. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

numeric_null()

Examples

make_col positive_or_null:case(x > 0, x, true, numeric_null())

Create a column ‘positive_or_null’ which is either a positive numeric, or the null numeric.

object (To Object)

Description

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

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

object( value )

Examples

make_col obj:object(parse_json(json))

Make a new column ‘obj’ consisting of the JSON objects parsed from the ‘json’ column.

Arguments

Argument

Type

Required

Multiple

value

any

True

False

object_agg (Object Aggregation)

Description

Returns one OBJECT per group. For each (key, value) input pair, the resulting object contains a key:value field. The key column needs to be string. Duplicate keys within a group result in an error, and input tuples with NULL key and/or value are ignored.

Return type

object

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

object_agg( key, value )

Examples

statsby clusterid, oa:object_agg(jobid, status), group_by(clusterid)

For each clusterid, return a JSON object containing the status of each jobid with the attached clusterid.

Arguments

Argument

Type

Required

Multiple

key

string

True

False

value

any

True

False

object_keys (Object Keys)

Description

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

Return type

array

Domain

This is a scalar function (acts on values from each row individually.)

Usage

object_keys( value )

Arguments

Argument

Type

Required

Multiple

value

object

True

False

object_null (Object Null)

Description

Returns a null value of type object. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

object_null()

Examples

make_col positive_or_null:case(x > 0, x, true, object_null())

Create a column ‘positive_or_null’ which is either a positive object, or the null object.

options (Options)

Description

Specify options to change the verb’s behavior

Return type

options

Domain

This is a scalar function (acts on values from each row individually.)

Usage

options( [ keyvalue, ... ] )

Arguments

Argument

Type

Required

Multiple

keyvalue

expression

False

True

order_by (Order By)

Description

Order in which to process data

Aliases: orderby(deprecated)

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

order_by( [ columnname, ... ], [ descending, ... ] )

Arguments

Argument

Type

Required

Multiple

columnname

expression

False

True

descending

bool

False

True

parse_hex (Parse Hex)

Description

Parses a string encoded hex number and returns an int64. Values should be strings, not start with 0x, and only include valid hex characters. NULL will be returned in case of an error

Aliases: parsehex(deprecated)

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

parse_hex( hexstr )

Examples

make_col hexid:parse_hex(hexid)

Will change the hexid column from a hex string to an int64 value.

Arguments

Argument

Type

Required

Multiple

hexstr

string

True

False

parse_ip (parseIp)

Description

When the input is an IPv(4/6) address, returns a JSON object containing the following attributes - family (either “4” or “6”), host (passed host IP), ip_fields (Array of 4 32-bit integers each representing 32 bits from the given IP), ip_type (always “inet”), netmask_prefix_length (Always null). When the address is an IPv4 address, an attribute ipv4 (integer representation of the address) is also added, when it’s an IPv6 address, an attribute hex_ipv6 (integer representation of the address in hexadecimal) is also added.

When the input is an IPv4 subnet mask, along with the above attributes, the following are also returned - ipv4_range_start (integer representation of the least IP address in the given range) , ipv4_range_end (integer representation of the highest IP address in the given range), netmask_prefix_length (length of the subnet mask). When the input is an IPv6 subnet mask, these attributes are prefixed with “hex_ipv6” instead of “ipv4” and the corresponding values are in hexadecimal.

Aliases: parseip(deprecated)

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

parse_ip( arg )

Examples

make_col ip:parse_ip(@.x)

Creates a column named ‘ip’ containing the returned JSON object

Arguments

Argument

Type

Required

Multiple

arg

string

True

False

parse_isotime (Parse ISO8601/RFC3339 Timestamp)

Description

Parse a YYYY-MM-DDTHH:MM:SSZ-formatted string as a timestamp.

Aliases: parseisotime(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

parse_isotime( value )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

parse_json (Parse JSON)

Description

Parse the argument value as a JSON string.

Aliases: parsejson(deprecated)

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

parse_json( value )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

parse_kvs (Parse key=value Pairs)

Description

Returns an object of key=value pairs extracted from an input string.

Aliases: parsekvs(deprecated)

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

parse_kvs( value )

Examples

make_col keyvals:parse_kvs(log)

Make a new object column ‘keyvals’ that contains key=value pairs extracted from string column ‘log’

Arguments

Argument

Type

Required

Multiple

value

string

True

False

parse_url (ParseUrl)

Description

Returns a JSON object consisting of all the components (fragment, host, path, port, query, scheme).

Aliases: parseurl(deprecated)

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

parse_url( arg )

Examples

make_col url:parse_url(@.x)

Creates a column named ‘url’ returning a JSON object that holds the components (fragment, host, path, port, query, scheme) of the input URL

Arguments

Argument

Type

Required

Multiple

arg

string

True

False

path_exists (Path Exists)

Description

Given a column and path, return whether the JSON path exists in that column. Must have a valid column.

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

path_exists( column, path )

Examples

path_exists(A, 'C')

Returns whether JSON path ‘C’ exists in column A.

path_exists(A, 'B.C')

Returns whether JSON path ‘B.C’ exists in column A. A row containing the path may be { B: { C: 1} }.

path_exists(@aaa.B, 'C')

Returns whether JSON path ‘C’ exists in column A of input ‘aaa’

Arguments

Argument

Type

Required

Multiple

column

object

True

False

path

string

True

False

percentile (Percentile)

Description

Returns an approximated value for the specified percentile of the input expression across the group. Percentile needs to be specified in the range of 0 to 1.0.

Return type

numeric

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

percentile( expression, percentile )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

False

percentile

numeric

True

False

percentile_cont (Percentile Cont)

Description

Assuming a continuous distribution, it returns the value for the specified percentile of the input expression across the group. Percentile needs to be specified in the range of 0 to 1.0.

Aliases: percentilecont(deprecated)

Return type

numeric

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

percentile_cont( expression, percentile )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

False

percentile

numeric

True

False

percentile_disc (Percentile Disc)

Description

Assuming a discrete distribution, it returns the value for the specified percentile of the input expression across the group. Percentile needs to be specified in the range of 0 to 1.0.

Aliases: percentiledisc(deprecated)

Return type

numeric

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

percentile_disc( expression, percentile )

Arguments

Argument

Type

Required

Multiple

expression

numeric

True

False

percentile

numeric

True

False

pick_fields (Pick Fields)

Description

Pick one or more fields from an object.

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

pick_fields( column, key, ... )

Examples

make_col smaller:pick_fields(obj, 'key1', 'key2')

Create a column ‘smaller’ based on column ‘obj’ only containing ‘key1’ and ‘key2’ if existing.

Arguments

Argument

Type

Required

Multiple

column

object

True

False

key

string

True

True

pivot_array (Pivot Array)

Description

Converts an array of “key”-“value” pairs into an object with key-value attributes. Note: int64 numbers will be converted to float64 and may lose precision.

Aliases: array_pivot(deprecated)

Return type

object

Domain

This is a scalar function (acts on values from each row individually.)

Usage

pivot_array( array, keyFieldName, valueFieldName )

Examples

pivot_array(array(FIELD.foo), "key", "value")

Converts ‘[{“key”: “k1”, “value”: “v1”} {“key”: “k2”, “value”: “v2”}, …]’ in column values to ‘{“k1”: “v1”, “k2”: “v2”, …}’

Arguments

Argument

Type

Required

Multiple

array

array

True

False

keyFieldName

string

True

False

valueFieldName

string

True

False

position (Position)

Description

Searches for the first occurrence of the second argument (needle) in the first argument (haystack) and, if successful, returns the needle’s position (0-based). Returns -1 if the needle is not found.

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

position( haystack, needle, [ start ] )

Examples

make_col p:position('fuzzy wuzzy', 'uzzy', 5)

Looks for the first occurrence of the needle ‘uzzy’ starting at index 5 (0-based) in the haystack ‘fuzzy wuzzy’. Returns 7.

Arguments

Argument

Type

Required

Multiple

haystack

string

True

False

needle

string

True

False

start

int64

False

False

pow (Pow)

Description

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

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

pow( base, exponent )

Examples

make_col power:pow(@.x, @.y)

Create a column ‘power’ with the result of column ‘base’ to the power of column ‘exponent’

Arguments

Argument

Type

Required

Multiple

base

numeric

True

False

exponent

numeric

True

False

primary_key (Primary Key)

Description

Specify the primary key for some verbs

Aliases: pk, primarykey(deprecated)

Return type

primarykey

Domain

This is a scalar function (acts on values from each row individually.)

Usage

primary_key( columnname, ... )

Arguments

Argument

Type

Required

Multiple

columnname

expression

True

True

query_end_time (Query End Time)

Description

Returns the latest time of the query time window.

Aliases: queryendtime(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

query_end_time()

Examples

make_col is_following:(query_end_time() < some_time_col)

Create a column is_following, which is true if the some_time_col column contains a time later than the query end time.

query_start_time (Query Start Time)

Description

Returns the earliest time of the query time window.

Aliases: querystarttime(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

query_start_time()

Examples

make_col is_previous:(query_start_time() > some_time_col)

Create a column is_previous, which is true if the some_time_col column contains a time earlier than the query start time.

rank (Rank)

Description

Returns the rank within an ordered group of values. Default ordering is in ascending time, so the first value has the lowest rank.

Return type

int64

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

rank()

Examples

make_col index:window(rank(), group_by(category_id), order_by(item_cost))

Assigns a rank to items, when ordered by cost, grouped by category. The cheapest item in each category will be given the rank 1, items with the same cost within the same category will receive the same rank.

replace (Replace)

Description

Replaces all instances of the substring in the input string with a provided value.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

replace( value, substring, replacement )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

substring

string

True

False

replacement

string

True

False

replace_regex (Replace RegEx)

Description

Replaces all instances of a matched regex pattern in the input string with a provided value.

The first parameter specifies the input string, the second parameter specifies the regex pattern, the third parameter specifies the replacement. If the replacement is empty all matched patterns are removed. The fourth parameter specifies which occurrences are to be replaced. If 0 is specified all occurrences are replaced. The fifth parameter specifies optional regex flags:

  • c - Enables case-sensitive matching (default.)

  • i - Enables case-insensitive matching.

  • m - Enables multi-line mode (i.e. meta-characters ^ and $ match the beginning and end of any line of the input string.) By default, multi-line mode is disabled (i.e. ^ and $ match the beginning and end of the entire input string.)

  • s - Enables the POSIX wildcard character . to match \n (newline.) By default, . does not match \n.

For more about syntax, see POSIX extended regular expressions.

Aliases: regex_replace(deprecated)

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

replace_regex( input_string, pattern, replacement, [ occurrence ], [ flags ] )

Examples

make_col date:"2001-31-12"
make_col new_date:replace_regex(date, /^.*([0-9]{4,4})-([0-9]{1,2})-([0-9]{1,2}).*$/,'\\3/\\2/\\1', 0, 's')

Use parenthesis to encapsulate groups and refer to them via the double backslash and their index, starting from 1.

Arguments

Argument

Type

Required

Multiple

input_string

string

True

False

pattern

regex

True

False

replacement

string

True

False

occurrence

int64

False

False

flags

string

False

False

right (Right)

Description

Returns a rightmost substring of its input.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

right( value, length )

Examples

make_col rightstring:right(somestring, 4)

Will make a ‘rightstring’ column with the right 4 characters of the text in the ‘somestring’ column.

Arguments

Argument

Type

Required

Multiple

value

string

True

False

length

int64

True

False

round (Round)

Description

Returns ‘val’ rounded to the given ‘precision’. Precision defaults to 0, meaning the value will be rounded to the nearest integer.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

round( val, [ precision ] )

Examples

make_col rounded:round(@.temperature, 2)

Return the rounded value of column temperature with 2 decimals

Arguments

Argument

Type

Required

Multiple

val

numeric

True

False

precision

int64

False

False

row_end_time (Row End Time)

Description

Returns the time at which the state in the row ended, or null for non-resource datasets.

Aliases: row_endtime(deprecated)

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

row_end_time()

Examples

make_col time_taken:if_null(row_end_time() - row_timestamp(), 0s)

Create a column time_taken that is the duration of the state within the row.

row_number (Row Number)

Description

Return the window index of the row within its groupby, when ordered by the orderby. Row indexes start at 1.

Aliases: rownumber(deprecated)

Return type

int64

Domain

This is a window function (calculates over a group without aggregating rows in window.)

Usage

row_number()

Examples

make_col index:window(row_number(), group_by(category_id), order_by(item_cost))

Assigns an index to items, when ordered by cost, grouped by category. The cheapest item in each category will be given the index 1.

row_timestamp (Row Timestamp)

Description

Returns the timestamp (start time) of the row.

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

row_timestamp()

Examples

make_col time_taken:if_null(row_end_time() - row_timestamp(), 0s)

Create a column time_taken that is the duration of the state within the row.

rpad (Right Pad)

Description

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

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

rpad( str, length, [ pad ] )

Examples

make_col padded_kind:rpad(bundle_kind, 100, '%')

Create a new column ‘padded_kind’ which has a length of 100 characters, with missing characters added as percents on the right.”

Arguments

Argument

Type

Required

Multiple

str

string

True

False

length

int64

True

False

pad

string

False

False

rtrim (Right Trim)

Description

RTrim removes trailing characters from a string

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

rtrim( str, [ chars ] )

Examples

make_col trimmed_kind:rtrim(bundle_kind, ' ')

Removes trailing spaces from bundle kinds

Arguments

Argument

Type

Required

Multiple

str

string

True

False

chars

string

False

False

split (Split)

Description

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

Return type

array

Domain

This is a scalar function (acts on values from each row individually.)

Usage

split( value, separator )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

separator

string

True

False

split_part (Split Part)

Description

Splits a given string at a specified character and returns the requested part. Part is 1-based. If part is a negative value, the parts are counted backward from the end of the string. If any parameter is null, this function returns null.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

split_part( value, delimiter, part )

Examples

split_part("03/04/2021", "/", 2)

Returns “04”.

Arguments

Argument

Type

Required

Multiple

value

string

True

False

delimiter

string

True

False

part

int64

True

False

sqrt (Sqrt)

Description

Returns the square root for a given input and null if input is negative.

Return type

numeric

Domain

This is a scalar function (acts on values from each row individually.)

Usage

sqrt( arg )

Examples

make_col squareroot:sqrt(@.x)

Create a column ‘squareroot’ with the result of the square root of column ‘x’

Arguments

Argument

Type

Required

Multiple

arg

numeric

True

False

starts_with (Starts With)

Description

Returns true if string starts with expr.

Aliases: startswith(deprecated)

Return type

bool

Domain

This is a scalar function (acts on values from each row individually.)

Usage

starts_with( haystack, needle )

Examples

filter starts_with(@.bundle_kind, "kube")

Pass through all bundle kinds that start with the string ‘kube’.

Arguments

Argument

Type

Required

Multiple

haystack

string

True

False

needle

string

True

False

stddev (Standard Deviation)

Description

Calculate the standard deviation across the group.

Return type

numeric

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

stddev( value )

Arguments

Argument

Type

Required

Multiple

value

numeric

True

False

strcat (String Concat)

Description

Return the concatenation of all string arguments.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

strcat( str, ... )

Examples

make_col foo:strcat(colstr1, " ", colstr2)

Make a new column ‘foo’ that is the concatenation of ‘colstr1’, ” “, and ‘colstr2’

Arguments

Argument

Type

Required

Multiple

str

string

True

True

string (Make STRING)

Description

Generate a string representation of the argument value.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

string( value )

Arguments

Argument

Type

Required

Multiple

value

any

True

False

string_agg (String/List Aggregation)

Description

Returns concatenated input values, separated by the delimiter. The expression must be a string column. The delimiter must be a string constant (it may be an empty string).If no ordering is specified, the default ordering is by ‘valid_from’, ascending.

Return type

string

Domain

This is an aggregate function (aggregates rows over a group in statsby.)

Usage

string_agg( expr, delimiter, [ orderby ] )

Examples

statsby nicknames:string_agg(nickname, ", ", order_by(email)), group_by(uid, fullname)

A list of all nicknames for each individual in the organization; for users with more than one nickname they will be sorted by email address.

Arguments

Argument

Type

Required

Multiple

expr

string

True

False

delimiter

string

True

False

orderby

ordering

False

False

string_null (String Null)

Description

Returns a null value of type string. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

string_null()

Examples

make_col positive_or_null:case(x > 0, x, true, string_null())

Create a column ‘positive_or_null’ which is either a positive string, or the null string.

strlen (String Length)

Description

Compute the length of an input string.

Return type

int64

Domain

This is a scalar function (acts on values from each row individually.)

Usage

strlen( value )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

substring (Substring)

Description

Extracts characters from a string, starting at an index. Negative indices count from the end of the string. Positive indices start at 0. Takes an optional length parameter.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

substring( value, start, [ length ] )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

start

int64

True

False

length

int64

False

False

sum (Sum)

Description

Calculate the sum of the argument across the group, or of the scalar arguments if more than one.

Return type

any

Domain

This is a window function (calculates over a group without aggregating rows in window.) This is also an aggregate function (aggregates rows over a group in statsby.)

Usage

sum( item, ... )

Arguments

Argument

Type

Required

Multiple

item

numeric

True

True

timestamp_null (Timestamp Null)

Description

Returns a null value of type timestamp. This is important, because some functions, like case(), return more convenient outputs if all their arguments are of the same type.

Return type

timestamp

Domain

This is a scalar function (acts on values from each row individually.)

Usage

timestamp_null()

Examples

make_col positive_or_null:case(x > 0, x, true, timestamp_null())

Create a column ‘positive_or_null’ which is either a positive timestamp, or the null timestamp.

tokenize (Tokenize)

Description

Splits the string into an array based on separator, which is treated as a set of characters. The default separator is ” “.

Return type

array

Domain

This is a scalar function (acts on values from each row individually.)

Usage

tokenize( value, [ separator ] )

Examples

tokenize("a b c d")

Returns [“a”, “b”, “c”, “d”].

tokenize("hello@example.com", "@.")

Returns [“hello”, “example”, “com”].

Arguments

Argument

Type

Required

Multiple

value

string

True

False

separator

string

False

False

tokenize_part (Tokenize Part)

Description

Tokenizes the input string using the delimiter and returns the requested part. The delimiter is treated as a set of characters. Each character in the delimiter string is a delimiter. The default delimiter is ” “. If the delimiter is empty, and the string is empty, then the function returns NULL. If the delimiter is empty, and the string is non empty, then the whole string will be treated as one token. The part argument is 1-based and the default value is 1. If the part number is out of range, then NULL is returned

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

tokenize_part( value, [ delimiter ], [ part ] )

Examples

tokenize_part("a b c d")

Returns “a”.

tokenize_part("hello@example.com", "@.")

Returns “hello”.

tokenize_part("hello@example.com", "@.", 2)

Returns “example”.

Arguments

Argument

Type

Required

Multiple

value

string

True

False

delimiter

string

False

False

part

int64

False

False

trim (Trim)

Description

Trim removes leading and trailing characters from a string

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

trim( str, [ chars ] )

Examples

make_col trimmed_kind:trim(bundle_kind, ' ')

Removes leading and trailing spaces from bundle kinds

Arguments

Argument

Type

Required

Multiple

str

string

True

False

chars

string

False

False

unpivot_array (Unpivot Array)

Description

Convert an object into an array of “key”-“value” pairs. Note: int64 numbers will be converted to float64 and may lose precision.

Aliases: array_unpivot(deprecated)

Return type

array

Domain

This is a scalar function (acts on values from each row individually.)

Usage

unpivot_array( object, keyFieldName, valueFieldName )

Examples

unpivot_array(object(FIELD.foo), "key", "value")

Converts ‘{“k1”: “v1”, “k2”: “v2”, …}’ to ‘[{“key”: “k1”, “value”: “v1”}, {“key”: “k2”, “value”: “v2”}, …]’

Arguments

Argument

Type

Required

Multiple

object

object

True

False

keyFieldName

string

True

False

valueFieldName

string

True

False

upper (Uppercase)

Description

Return the input string in uppercase.

Return type

string

Domain

This is a scalar function (acts on values from each row individually.)

Usage

upper( value )

Arguments

Argument

Type

Required

Multiple

value

string

True

False

valid_for (Valid For)

Description

Specify the validity period for each event for some verbs

Aliases: validfor(deprecated)

Return type

validfor

Domain

This is a scalar function (acts on values from each row individually.)

Usage

valid_for( expression, ... )

Arguments

Argument

Type

Required

Multiple

expression

expression

True

True

window (Window)

Description

Evaluates its argument in windowed context, partitioned over the given grouping and ordered by the given ordering (by default, input dataset timestamp.) Include ‘frame()’ to evaluate the window function inside a sliding window frame.

For queries, you may omit ‘frame()’ to use the current query time window, although this results in a dataset that cannot be accelerated.

Return type

any

Domain

This is a scalar function (acts on values from each row individually.)

Usage

window( expr, [ groupby ], [ orderby ], [ frame ] )

Examples

make_col name:window(first(name), group_by(section), order_by(time))

Name each row with the first name that appears in the current query time window

make_col avg:window(avg(load), group_by(host), order_by(time), frame(back:10m))

Compute the moving average of system load within the past 10 minutes of each event

Arguments

Argument

Type

Required

Multiple

expr

any

True

False

groupby

grouping

False

False

orderby

ordering

False

False

frame

frame

False

False