Data Types and Operators

OPAL consists of 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.)

Advanced Data Types

Composite type	Examples	Related functions and expressions
variant	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. NOTE - any is deprecated and replaced with variant.
ipv4	ipv4('127.0.0.1'), ipv4(int_column), ipv4_addr ~ 192.168.0.0/16	ipv4() is used to store an IPv4 address. Use the ipv4() function to convert a value of type any, int64, or string to an IPv4 address type.
object	make_object( [ options ] [ , key_value ] ... )	make_object() creates an object from key-value pairs, often as an intermediate step to select a set of fields for further operations.
tdigest_agg	statsby state:tdigest_agg(float_column), group_by(...)	tdigest_agg() aggregates multiple numeric values into a single t-digest state (represented as a JSON object). Other functions can be leveraged to manipulate and use tdigest states, namely: tdigest_combine(): merges multiple tdigest states into one. tdigest_quantile(): calculate a quantile from a tdigest state in a fast albeit approximated way.
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
+	addition
-	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 expression. Applies string() conversion automatically if necessary.
!~	can be used to negate the match. *!~ x means none of the fields matches x.
<text>	search for the specified literal text

See Examples for more on using these operators.