Data types and operators¶

OPAL is a strongly typed language, with basic and advanced data types. The sections below describe these types, available operators for constructing expressions, and additional syntax details such as comments.

The verb and function references describe the types accepted by each verb or function, required or optional arguments, and if the verb accepts multiple arguments. The usage notation generally follows the notational convention for functions used in the Snowflake documentation.

Passing an argument of an unexpected type generates an error, so be sure to convert values when necessary.

Basic data types¶

Basic type

Examples

Related functions and expressions

bool

true, false, null

bool() creates a boolean value from strings and integers, such as bool("true"). (Also: 1, “1”, “True”, “T”, “t”, or “on”.)

Any expression that returns a boolean can be used to create one, even if it is not a string or integer. Example: myNonzeroFloat != 0 evaluates to true.

duration

5m, 300s, 300000ms

Verbs and functions accept string durations using ns, ms, s, m, h, or d.

For other uses, the duration()family of functions creates durations from nanosecond, millisecond, second, minute, or hour integer values.

Duration values may be added or subtracted, and the result of adding or subtracting two timestamp values is a duration.

float64

123.45, 1.234e-5

float64() creates or converts to a 64-bit float value.

int64

12345

int64() creates or converts to a 64-bit integer value.

string

“abcde”

string() creates or converts to a string value. Allowed special characters are \n, \r, \t, \', \", and \\. Example: 'This string contains a tab (\t) character'

You may escape ' and " with a backslash or by doubling them.

Examples:

• “doublequote (") character”

• ‘That’’s awesome!’

• “I’ve been informed this is called “”coffee.”””

timestamp

1609459200000000000

The from_<time>() family of functions creates timestamps from integer values of nanoseconds, milliseconds, or seconds since 1970-01-01T00:00:00Z (Unix epoch.)

Timestamps are stored internally in nanoseconds, so conversions to other types are based on nanosecond time. Timestamps are displayed in the UI as MM/DD/YY HH:MM:SS.FF3 in your local timezone. Example: 12/31/20 16:00:00.000 (for GMT-8.)

Composite type

Examples

Related functions and expressions

any

A special-purpose type to aid type safety

Used primarily with verbs and functions operating on JSON data. Corresponds to the Snowflake VARIANT, for semi-structured data that may itself contain data of several types.

Important: Some verbs and functions describe inputs or outputs as “any” when they are capable of handling several possible data types. This may not include the any type.

Also, any() is a window or aggregate function that returns type any.

array

array(parse_json("[1, 2, 3]"))

array() converts a value of type any to an array. Frequently used to convert JSON values for operations that require type array.

object

make_object("resident_set_size":rss, "cpu_utilization":pcpu), options(empty_bins:true)

make_object() creates an object from key-value pairs, often as an intermediate step to select a set of fields for further operations.

options

options(empty_bins:true)

options() creates an object of type options, used to provide settings or metadata for certain verbs.

options is similar to object, but the two are not interchangeable. Verbs and functions always require a value of the correct type.

Regex literal

/^DEBUG/

For verbs and functions that accept a regular expression, a pattern to match delimited by / slashes. For more about syntax, see POSIX extended regular expressions.

Most types have a corresponding type_null() function that creates a null value. To pass null as an argument, use the appropriate function to create a value with the correct type. Example: make_col foo:string_null().

Operators¶

OPAL supports many common operators, as well as several additional ones for searching and accessing data within fields. Some have equivalent functions or alternate forms, which may be used interchangeably.

Arithmetic¶

Operator

Operation

+

-

subtraction

*

multiplication

/

division

(, )

group, for precedence

Note

The output of dividing by zero is null. The OPAL parser does not return an error or NaN, but instead returns null for the expression. This is so an unexpected divide by zero doesn’t cause the entire pipeline to fail.

Comparison¶

Operator

Equivalent Function

Operation

=

eq()

equals

!=, <>

ne()

not equal to

<

lt()

less than

>

gt()

greater than

<=

lte()

less than or equal to

>=

gte()

greater than or equal to

Logical¶

Operator

Operation

and

logical AND

or

logical OR

not

logical NOT

Other¶

Operator

Operation

.

nested field access for JSON

[]

subscript for element in an array or JSON

:

name a field or value: make_col intVal: round(floatVal)

~

search within the specified field, or inside JSON

<text>

search for the specified literal text

See Examples for more on using these operators.