OPAL 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 OPAL Verbs and OPAL Functions 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. |
| array | A special data type used to list other data | Arrays are composed of zero or more Variants in a list, meaning that an array can contain any mix of other data types. Corresponds to the Snowflake ARRAY. |
| 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 |
NoteThe output of dividing by zero is
null. The OPAL parser does not return an error or NaN, but instead returnsnullfor 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 OPAL examples for more on using these operators.
Updated about 2 months ago