Transform Data in Data Pipelines | Ocient Documentation

Data pipeline functionality supports data transformation during loading to efficiently convert and clean source data for your chosen table schema. You can use two broad classes of data transformation: source field references or SQL transformation functions.

Source Field References

When you load data using a data pipeline, you reference extracted data using a special syntax named a source field reference. These expressions are either a named reference such as $my_field or a numeric field reference such as $1.

In structured data formats like JSON, more complex source field references allow you to transform the structured data into the required form. For example, you can extract an array of values from an array of nested objects with an expression such as $data.items[].price.

To learn more about transforming data with source field references, see:

Supported Transformation Functions in Data Pipelines

Data pipeline loading supports numerous SQL functions for use in transforming data during loading. You can use these transformation functions in the INTO ... SELECT portion of a pipeline. Transformation functions generally match the syntax and behavior of the function you use when you query in Ocient®. The behavior might differ from querying behavior in two ways:

Some additional functions exist that provide behavior that does not exist in queries.
Some existing functions have extended behavior during loading that provides important capabilities that are not in queries.

The types of transformation functions supported in loading include:

Scalar data conversion and casting of data types
Special loading transformation functions
Date and time transformations
Character data transformations
Binary data transformations
Array data transformations
Logical operations transformations
Network type functions
Other data transformations
Custom user-defined Pipeline Functions using Apache® Groovy™

Scalar Transformation Functions and Casting

Scalar transformation functions include constructors, scalar data conversions, and data-casting functions.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`T[](X)`	Casts a string representation of an array of values or the result of a JSON array extraction expression to the corresponding array with data type `T` where `T` is a SQL data type. Multi-dimensional arrays are supported. To indicate an N-dimensional array cast, use N sets of square brackets in the casting function. All transformation functions can only apply to an array as a whole. To apply functions on an element-wise basis, see `TRANSFORM` in Special Data Pipeline Transformation Functions.	CHAR	ARRAY[]	CSV: `DECIMAL(8,4)[][] ($21) as decimal_2d_array_col` JSON: `CHAR[]($a.b.names[]) as names` `DECIMAL(8,2)[][]($a[].b.c[].prices) as prices` `CHAR[][][]($names[][][]) as names`
`BIGINT(X)`	Converts the specified type to a 64-bit signed integer.	BIGINT CHAR	BIGINT	CSV: `BIGINT($22) as int64_col` JSON: `BIGINT($a.b.c) as int64_col`
`BINARY(X)`	Translates a string representation of bytes and converts them to real bytes. The string representation expects hexadecimal representations that convert to the matching bytes with this function. Hexadecimal data is not case-sensitive. Example source data: `a891`, `F1D890`	CHAR	BINARY or VARBINARY	CSV: `BINARY($30) as varbinary_col` JSON: `BINARY($a.b.c) as varbinary_col`
`BOOLEAN(X)`	Handles textual representation of Booleans and converts them to its Boolean equivalent. You can represent `true` as any of these values: `“t”, “true”, “yes”, “1”, “y”, “on”` `false` representation as any of these values: `“f”, “false”, “no”, “0”, “n”, “off”`	CHAR	BOOL	CSV: `BOOLEAN($23) as bool_col` JSON: `BOOLEAN($a.b.c) as bool_col`
`BYTE(X)`	Supports characters and number or byte array representations. Interprets character data as the ASCII numeric representation of a byte. If you specify a byte array, the Ocient System uses the first element of the array. When the system converts text data to a byte, the function throws an error if the value is `> 127` or `< -128`. Example: `'101'` -> `101` `'-23'` -> `-23`	CHAR	BYTE	CSV: `BYTE($24) as byte_col` JSON: `BYTE($a.b.c) as byte_col`
`CHAR(X)`	Converts the specified input type to its string representation.	CHAR BINARY FLOAT DOUBLE INT BYTE SMALLINT DATE BOOLEAN UUID ARRAY	CHAR or VARCHAR	CSV: `CHAR($25) as varchar_col` `CHAR($26) as char_col` JSON: `CHAR($a.b.c) as varchar_col` `CHAR($a.b.c) as char_col` `CHAR($array_field[]) as char_col`
`DECIMAL(X,P,S)`	`X` is input data to convert to a decimal. `P` is the precision of the decimal (number of significant digits) and `S` is the scale (number of digits after the decimal point). You can omit `P` and `S` or only `S`. In that case, the Ocient System defaults `P` to 31 and `S` to 16. Example: `DECIMAL(30.0,4,2) -> 30.00`	CHAR	DECIMAL	CSV: `DECIMAL($27, 8, 4) as decimal_col` JSON: `DECIMAL($a.b.c, 8, 4) as decimal_col`
`DOUBLE(X)`	Converts the specified type to its double representation.	BINARY CHAR	DOUBLE	CSV: `DOUBLE($28) as double_col` JSON: `DOUBLE($a.b.c) as double_col`
`FLOAT(X)`	Converts the specified type to its float representation.	BINARY CHAR	FLOAT	CSV: `FLOAT($29) as float_col` JSON: `FLOAT($a.b.c) as float_col`
`HASH(X,N)`	Translates a string representation of bytes and converts them to bytes. The string representation expects hexadecimal representations that convert to the matching bytes with this function. Hexadecimal data is not case-sensitive. `X` is the source data, such as: `a891`, `F1D890` `N` is the fixed number of bytes in the binary array.	CHAR	HASH	CSV: `HASH($23, 9) as fixed_hash_binary_col` JSON: `HASH($a.b.c, 9) as fixed_hash_binary_col`
`INT(X)`	Converts the specified type to a 32-bit signed integer.	DECIMAL CHAR SMALLINT	INT	CSV: `INT($31) as int32_col` JSON: `INT($a.b.c) as int32_col`
`IP(X)`	Converts the specified string to an IPv6 address equivalent. `X` is the source data, such as: `2001:0db8:1234:5678:abcd:ef01:2345:6789` `2001:db8:0:0:0:0:0:2` `192.0.2.1`	CHAR	IP	CSV: `IP($32) as ip_col` JSON: `IP($a.b.c) as ip_col`
`IPV4(X)`	Converts the specified string to an IPv4 address equivalent. `X` is the source data, such as: `192.0.2.1`	CHAR	IPV4	CSV: `IPV4($32) as ipv4_col` JSON: `IPV4($a.b.c) as ipv4_col`
`SMALLINT(X)`	Converts the specified value to its 16-bit signed integer representation.	DECIMAL CHAR BYTE	SMALLINT	CSV: `SMALLINT($33) as int16_col` JSON: `SMALLINT($a.b.c) as int16_col`
`ST_POINT(Long, Lat)`	Accepts a longitude and latitude coordinate for a geospatial point. Arguments must be of type `double` or string values that can convert to `double`. For WKT representation, see ST_POINTFROMTEXT.	Both DOUBLE Both CHAR	`ST_POINT`	CSV: `ST_POINT($1, $2) as st_point_col` JSON: `ST_POINT($a.b.c, $d.e.f) as st_point_col`
`ST_POINTFROMTEXT(X)`	Accepts the WKT text representation for `ST_POINT` with `X` and `Y` coordinates only. Example WKT representation: `'POINT(-40.5318 11.8532)'`	CHAR	`ST_POINT`	`st_pointfromtext($1)` `st_pointfromtext($a.b.c)`
`TINYINT(X)`	Converts the specified value to an 8-bit signed integer.	CHAR	TINYINT	CSV: `TINYINT($34) as int8_col` JSON: `TINYINT($a.b.c) as int8_col`
`TUPLE <<T,[T]>>(X,[Y])`	Creates a tuple from the specified types. TUPLE supports arrays of tuples, but does not support tuples within tuples. The arguments to the TUPLE casting function can be either a single source value or must match the number of types defined in the TUPLE. If you specify a single value, the Ocient System infers individual elements of the tuple using the TUPLE format `(value1,value2,value3)`. Example source data: `(123,12.75)` is valid text data for a `TUPLE<<INT,DECIMAL(7,2)>>`.	CHAR	TUPLE of type	CSV: `TUPLE<<INT,DECIMAL(7,2),TIMESTAMP,BINARY(7)>>($37) as int_decimal_timestamp_binary_tuple_col` `TUPLE<<INT,DECIMAL(7,2),TIMESTAMP,BINARY(7)>>($4,$8,$1) as int_decimal_timestamp_binary_tuple_col_multi_input` JSON: `TUPLE<<INT,DECIMAL(7,2),TIMESTAMP,BINARY(7)>>($a, $b, $c)) as int_decimal_timestamp_binary_tuple_col`
`UUID(X)`	Creates a unique identifier from the specified value. Example source data: `'237e9877-e79b-12d4-a765-321741963000'`	CHAR	UUID	CSV: `UUID($34) as uuid_col` JSON: `UUID($a.b.c) as uuid_col`

Special Data Pipeline Transformation Functions

Special data pipeline transformation functions include functions that only exist in loading, such as the METADATA function to load special data during pipeline execution.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`EXPLODE_OUTER(array)`	Expands the contents of the specified one-dimensional array. By default, the array can contain a maximum of 8,191 elements. For supported options, see Special Data Pipeline Transformation Functions.	ARRAY	Elements of the array in their own row.	`EXPLODE_OUTER($"data.response.responses[].own") AS own`
`FILTER(T[], (T) -> Boolean)`	Filters elements in an array based on the logic in a lambda expression. The first argument is an expression that evaluates to an array that contains the elements to filter. The second argument is a lambda expression (or function reference) that returns `true` if an element should be retained. The second argument must be of the form `(x T) -> BOOL`. This returns a boolean value for a specified element `x`. The type `T` of the element `x` is specified in the lambda function. Elements in the first argument array are included in the resulting array when the lambda function returns `true` and excluded otherwise. For supported options and examples, see Special Data Pipeline Transformation Functions.	ARRAY Lambda Function `(x T) -> BOOL` or Function Reference	The elements of the array that matched the filter criteria function.	`FILTER($data.names, (name CHAR) -> length(name) > 5)`
`LOOKUP(lookup_source_name, value, join_column_name, return_column_name)`	Look up and load data in an external data source. For details, see Load Data from External Sources in Data Pipelines .	Varies based on the `value` argument.	Varies based on the returned data in the `return_column_name` argument.	`LOOKUP('sqlite_db', $2, 'firstname', 'id')`
`METADATA(key)`	Extracts the metadata value for the specified key from available metadata for the pipeline. For supported key values, see Load Metadata and File-Based Partitioned Data in Data Pipelines.	CHAR	Varies based on the metadata key.	`METADATA('filename') → '/path/to/file/orders_1830374.json'`
`PARSE_DELIMITED_ARRAY(text, rank, array_element_delimiter, bracket_spec)`	Converts a string of text data representing an array into a `CHAR[]`. For example, with data in the form `[1;2;3]`, executing `PARSE_DELIMITED_ARRAY('[1;2;3]', 1, ';', '[]')` returns a `CHAR[]` with the value `['1','2','3']`.	`text` is CHAR data that represents the text to convert to an array. `rank` is an optional argument that represents the numeric rank of the output array. The default value is 1. You can only specify rank as a literal value because you cannot retrieve it dynamically from a source field selector. `array_element_delimiter` is an optional argument that contains the control character for delimiting array elements. For example, `','` indicates that a comma separates each element in the `text` character data. The default value definition comes from the `ARRAY_ELEMENT_DELIMITER` value of the data pipeline. `bracket_spec` is an optional argument that is the pair of control characters for the open and close array brackets. For example, `'[]'` indicates that each rank of the array is enclosed in square brackets. The default definition comes from the `OPEN_ARRAY` and `CLOSE_ARRAY` characters of the data pipeline. Valid options are: `''`, `'[]'`, `'{}'`, `'()'`, and `'<>'`.	CHAR[]	`PARSE_DELIMITED_ARRAY($text_array)` `PARSE_DELIMITED_ARRAY($text_array, 1, ';', '[]')` `PARSE_DELIMITED_ARRAY($two_dim_text_array, 2, ',', '[]')`
`TRANSFORM(T[], (T) -> U)`	Transforms an array based on the logic in a lambda expression. The first argument is an expression that evaluates to an array that contains the elements to transform. The second argument is a lambda expression (or function reference) that defines how to transform each element. `TRANSFORM` returns an array of elements of the same length as the array argument of the type returned by the lambda function. The second argument must be of the form `(x T) -> U` . This returns a value of type `U` for a specified element `x`. The type `T` of the element `x` is specified in the lambda function. The second argument can also be a function reference for a single argument function. For supported options and examples, see Special Data Pipeline Transformation Functions.	ARRAY Lambda Function `(x) -> U` or Function Reference	Elements of the array transformed to the return value and type of the anonymous function.	`TRANSFORM($data.names[], (el CHAR) -> LOWER(el)) as lower_case_name_array` `TRANSFORM($data.names[], LOWER) as lower_case_name_array`
`WIDTH_BUCKET(X,min,max,N)`	Creates `N` equal-width buckets in the range `[min,max)` as a histogram. If `X` is less than `min`, then the function returns 0. If `X` is greater than or equal to the value of `max`, the function returns `N+1` buckets. If `X` is less than the value of `max` and greater than or equal to the value of `min`, the function returns the number of the bucket corresponding to where the input value belongs in the histogram. The values are inclusive on the lower bound and exclusive on the upper bound, so all buckets are the same width. Requesting 100 buckets retrieves 102 possible buckets, bucket 0 through 101, where 0 captures all values below the minimum and 101 captures all values at the maximum and beyond.	`X` is the value for bucket determination. `min` is the minimum value for the histogram. `max` is the maximum value for the histogram. `N` is the number of buckets in the histogram.	Integer	`WIDTH_BUCKET('$2', 1, 5, 2) AS output`
`ZIP_WITH(array1, ..., array2, func)`	Merges two or more input arrays, element-wise, into a single array using the trailing combining function. If one array is shorter than the others, the system appends `NULL` values at the end to match the length of the longest array before applying. Returns an `array` with elements that result from applying the function `func` to each pair of elements from `array1` and `array2`. If the arrays are not the same length, the system pads the shorter array with `NULL` values. `func` must be of the form `(x T, y U) -> V`, where `x` is the element from `array1` and `y` is the element from `array2`. Types `T` and `U` are specified in the lambda function. For supported options and examples, see Special Data Pipeline Transformation Functions.	ARRAY ARRAY Lambda Function `(x T, y U) -> V` or Function Reference	An array of length `max(length(array1), length(array2))` containing elements of type `V` as returned by the lambda function.	`ZIP_WITH($line_items[].product_name, $line_items[].sku, (name CHAR, sku CHAR) -> concat(name, '[', sku, ']'))`

Date and Time Transformation Functions

Date and time transformation functions provide utilities to parse, convert, or transform DATE, TIME, and TIMESTAMP values in pipelines.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`DATE(X)`	If the input type is a `CHAR`, the `DATE` function parses the string into a `DATE` type according to the format `YYYY[-/]MM[-/]DD...`. The format is in this order: 4-digit year dash or backlash 2-digit month a dash or backslash 2-digit day any number of trailing characters If the input type is a `TIMESTAMP`, the function parses the date portion of the timestamp (in UTC) into a `DATE` type.	CHAR TIMESTAMP	DATE	CSV: `DATE($24) as date_col` `DATE(TIMESTAMP($24)) as date_col` JSON: `DATE($a.b.c) as date_col` `DATE(TIMESTAMP($a.b.c)) as date_col`
`TO_DATE(X,F,FT)`	Converts the specified value to a DATE. `X` — The value to convert. `F` — (optional) The format string for the interpretation of the textual representation of `X`. `FT` — (optional) The format type. There are two format types: `ocient` and `java`. If you use the `ocient` type, the function uses the Ocient-based formatting function to parse the date. For details, see TO_DATE. If you use the `java` type, the function uses the `DateTimeFormatter` in Java® to parse the timestamp. For details, see DateTimeFormatter (Java Platform SE 8 ). The default is the `ocient` format type with format string `YYYY-MM-DD`. If you specify the `java` format type, you must specify a valid `DateTimeFormatter` format string, as there is no default.	CHAR	DATE	`to_date($3, 'YYYY/MM/DD')` `to_date($a.b.c, 'DD.MM.YYYY')`
`INT_TO_DATE(arg)`	Creates a `DATE` type from an `INT` type representing the number of days after the Unix® epoch (January 1, 1970).	INT	DATE	`INT_TO_DATE($1) AS col_date_1`
`TIME(X)`	If the input type is a `CHAR`, the `TIME` function parses the string into a `TIME` according to the format `HH:MI[:SS[.NS]]`. The format is a 2-digit hour between 00 and 23, a colon, a 2-digit minute, an optional colon and 2-digit second, and an optional period and 1-9 digits for fractional seconds. The value can end optionally in `Z` to denote UTC (Zulu). The value can also end optionally in `±TZH[:TZM]` to denote the timezone offset. This format is a plus or minus sign, a 2-digit hour, and an optional colon and 2-digit minute. The system resolves all values to UTC, so `Z`, `+00:00`, and `-00` are equivalent. If the input type is a `BIGINT`, the function parses the number with millisecond precision. If the input type is a `TIMESTAMP`, the function parses the date portion of the timestamp (in UTC) into a `TIME` type.	CHAR BIGINT TIMESTAMP	TIME	CSV: `TIME($34) as time_col` `TIME(BIGINT($34)) as time_col` JSON: `TIME($a.b.c) as time_col` `TIME(BIGINT($a.b.c) as time_col`
`TO_TIME(X,F,FT)`	Converts the specified value to a TIME value. `X` — The value to convert. `F` — (optional) The format string for the interpretation of the textual representation of `X`. `FT` — (optional) The format type. There are two format types: `ocient` and `java`. If you use the `ocient` type, the function uses the Ocient-based formatting function to parse the time. For details, see TO_TIMESTAMP. If you use the `java` type, the function uses the `DateTimeFormatter` in Java to parse the timestamp. For details, see DateTimeFormatter (Java Platform SE 8 ). The default is the `ocient` format type with format string `HH:MI:SS`. If you specify the `java` format type, you must specify a valid `DateTimeFormatter` format string, as there is no default.	CHAR	TIME	`to_time($n, 'HH:MI:SS')` `to_time($n, 'HHMISS')`
`SECONDS_TO_TIME(arg)`	Converts the specified value to the `TIME` type, treating the argument with second precision.	BIGINT	TIME	`SECONDS_TO_TIME($1) AS col_time`
`MILLIS_TO_TIME(arg)`	Converts the specified value to the `TIME` type, treating the argument with millisecond precision. Alias for `TIME(X)` where `X` is the `BIGINT` type.	BIGINT	TIME	`MILLIS_TO_TIME($1) AS col_time`
`MICROS_TO_TIME(arg)`	Converts the specified value to the `TIME` type, treating the argument with microsecond precision.	BIGINT	TIME	`MICROS_TO_TIME($1) AS col_time`
`NANOS_TO_TIME(X)`	Converts the specified value to a TIME value. If the argument has the `BIGINT` type, the function treats the number with nanosecond precision.	BIGINT	TIME	CSV: `NANOS_TO_TIME($34) as time_col` `NANOS_TO_TIME(BIGINT($34)) as time_col` JSON: `NANOS_TO_TIME($a.b.c) as time_col` `NANOS_TO_TIME(BIGINT($a.b.c) as time_col`
`TIMESTAMP(X)`	If the input type is a `CHAR`, the `TIMESTAMP` function parses the string into a `TIMESTAMP` according to the format `YYYY[-/]MM[-/]DD[T ]HH:MI[:SS[.NS]]`. For the format, see the `DATE` and `TIME` functions. The input can have either the letter `T` or a space separating the date and time portions. The input can also end in `Z` to denote UTC or `±TZH[:TZM]` to denote the time zone offset. If the input type is a `BIGINT`, the function parses the number with millisecond precision. If the input type is a `DATE`, the function converts the date into a `TIMESTAMP` with the value of the start of the day in UTC.	CHAR BIGINT DATE	TIMESTAMP	CSV: `TIMESTAMP($35) as timestamp_col` JSON: `TIMESTAMP($a.b.c) as timestamp_col` `TIMESTAMP('2023-03-01 18:22:58.826') -> 2023-03-01T18:22:58.826` `TIMESTAMP(bigint(1651681788123456789)) -> 2022-05-04T16:29:48.123`
`TO_TIMESTAMP(X,F,FT)`	Converts the specified value to a TIMESTAMP. `X` — The value to convert. `F` — (optional) The format string for the interpretation of the textual representation of `X`. `FT` — (optional) The format type. There are two format types: `ocient` and `java`. If you use the `ocient` type, the function uses the Ocient-based formatting function to parse the timestamp. For details, see TO_TIMESTAMP. If you use the `java` type, the function uses the `DateTimeFormatter` in Java to parse the timestamp. For details, see DateTimeFormatter (Java Platform SE 8 ). The default is the `ocient` format type with format string `YYYY-MM-DD HH:MI:SS`. If you specify the `java` format type, you must specify a valid `DateTimeFormatter` format string, as there is no default.	CHAR	TIMESTAMP	`to_timestamp($n, 'YYYY-MM-DD HH:MI:SS.US')` `to_timestamp($n, 'DD.MM.YYYY HH:MI:SS.NS')` `to_timestamp($n, 'uuuu-mm-dd HH:mm:ss.SSSSSS', 'java')`
`SECONDS_TO_TIMESTAMP(X)`	Converts the specified value to a TIMESTAMP. This function creates a TIMESTAMP from a BIGINT that represents the number of nanoseconds after the Unix epoch (January 1, 1970).	BIGINT	TIMESTAMP	CSV: `SECONDS_TO_TIMESTAMP($35) as timestamp_col` JSON: `SECONDS_TO_TIMESTAMP($a.b.c) as timestamp_col` `NANOS_TO_TIMESTAMP(bigint(1651681788123456789)) -> 2022-05-04T16:29:48`
`MILLIS_TO_TIMESTAMP(X)`	Converts the specified value to a TIMESTAMP. This function creates a TIMESTAMP from a BIGINT that represents the number of milliseconds after the Unix epoch (January 1, 1970). Alias for `TIMESTAMP(X)` where `X` is the `BIGINT` type.	BIGINT	TIMESTAMP	`MILLIS_TO_TIMESTAMP($35) as timestamp_col`
`MICROS_TO_TIMESTAMP(X)`	Converts the specified value to a TIMESTAMP. This function creates a TIMESTAMP from a BIGINT that represents the number of microseconds after the Unix epoch (January 1, 1970).	BIGINT	TIMESTAMP	`MICROS_TO_TIMESTAMP($35) as timestamp_col`
`NANOS_TO_TIMESTAMP(X)`	Converts the specified value to a TIMESTAMP. This function creates a TIMESTAMP from a BIGINT that represents the number of nanoseconds after the Unix epoch (January 1, 1970).	BIGINT	TIMESTAMP	CSV: `NANOS_TO_TIMESTAMP($35) as timestamp_col` JSON: `NANOS_TO_TIMESTAMP($a.b.c) as timestamp_col` `NANOS_TO_TIMESTAMP(bigint(1651681788123456789)) -> 2022-05-04T16:29:48.123456789`
`NOW()`	Returns the current TIMESTAMP.		TIMESTAMP	`now() as col_load_timestamp`

For the TO_TIMESTAMP function, the Ocient formatter currently does not support variable-length sections of timestamps (i.e., the data might have a different number of fractions of seconds across records).

For example, assume this source data.

2024-01-01 12:00:00.123456

2024-01-01 12:00:00.1234

Then, you must specify the format_type as java and enter the DateTimeFormatter format string to parse the timestamps.

Character Data Transformation Functions

Character data transformation functions operate on VARCHAR data to transform a string of data.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`CONCAT(arg1, arg2[, ...])`	Returns a string that is the result of concatenating the arguments provided. Accepts two or more arguments to concatenate. If one of the arguments to `CONCAT` is `NULL`, then `CONCAT` returns `NULL`. ℹ️ `CONCAT` does not support `HASH` or `BINARY` arguments in loading.	`arg1`: CHAR `arg2`: CHAR `arg3`… `argN` (optional) CHAR	CHAR	`CONCAT($1, $2)` `CONCAT(TRIM($first_name), '_', TRIM($last_name))`
`SUBSTRING(X, P, L)`	Extract a substring from `X` beginning at the position offset `P` with a length `L`. `X` is the source data in CHAR or BINARY data type. `P` is the numeric byte position in the record that starts at index 1, which indicates the position to begin reading. `L` is the length of data to return starting from position `P`. For `VARCHAR` data, by default, the Ocient System treats each byte in a source field reference (e.g., `$your_field`) as an ASCII-encoded character, not multi-byte character data. To handle multi-byte character data such as UTF-8 in a source field, you must cast `X` as `CHAR($your_field)` or use another function that returns `CHAR` data. `P` and `L` represent the character offset and the number of characters to retrieve from the source data.	`X`: CHAR or BINARY `P`: INT `L`: INT	Return type matches the type of `X`: * CHAR

BINARY | substring($order.postcode, 1, 5) as col_postcode
| | TRIM(arg1[, arg2])
BTRIM(arg1[, arg2]) | Trims the leading and trailing space characters (or optionally, characters specified in the arg2 argument) from the arg1 argument. | arg1: CHAR
arg2 ( optional) must be a CHAR of length 1. | CHAR | TRIM('__testTrimFunction__', '_') -> 'testTrimFunction'
BTRIM(' testBtrimFunction ') -> 'testBtrimFunction' | | REPLACE(arg1, arg2, arg3) | The arg1 argument is the input string for the replace operation.
The arg2 argument is the pattern to replace or the target string.
The arg3 argument is the pattern for the replacement or the replacement string.
This function replaces occurrences of arg2 in arg1 with arg3. | arg1: CHAR
arg2: CHAR
arg3: CHAR | CHAR | REPLACE('testReplaceFunctionXYZ', 'X', 'Z') -> 'testReplaceFunctionZYZ' | | LENGTH(arg1)
CHAR_LENGTH(arg1)
CHARACTER_LENGTH(arg1) | These functions perform the same operation of returning the character length of the input string.
arg1 is the input string.
⚠️ When you use these functions with the SUBSTRING, RIGHT, or LEFT function, you might experience unintended results for multi-byte characters, such as Arabic in UTF-8 if you do not cast the field reference to CHAR. | CHAR | INT | LENGTH('hello world') -> 11 | | UPPER(arg1)
UCASE(arg1) | Converts the characters in the input string to all uppercase characters. | CHAR | CHAR | UPPER('Hello there') -> 'HELLO THERE' | | LOWER(arg1)
LCASE(arg1) | Converts the characters in the input string to all lowercase characters | CHAR | CHAR | LOWER('Obi Wan Kenobi') -> 'obi wan kenobi' | | LOCATE(arg1, arg2[, arg3])
POSITION(arg1, arg2[, arg3]) | Returns the index position of the first occurrence of the character value arg1 in character value arg2.
Optionally, you can also include arg3 to offset the search by the specified number of spaces. | arg1: CHAR
arg2: CHAR
arg3: LONG | INT | LOCATE('F', 'testLocateFunction') -> 11
POSITION('on', 'testPositionFunction', 12) -> 19 | | LEFT(arg1, arg2) | Returns the number of characters in the arg1 string equal to the arg2 integer value. If the integer is negative, the function returns all characters except the last number of characters as specified by the arg2 value.
This function treats source field references (e.g., $your_field) as single-byte ASCII characters by default, similar to the SUBSTRING function. | arg1: CHAR
arg2: LONG | CHAR | LEFT('testLeftFunction', 4) -> 'test'
LEFT('testLeftFunction', -4) -> 'testLeftFunc' | | RIGHT(arg1, arg2) | Returns the number of trailing characters in the arg1 string equal to the arg2 integer value. If the integer is negative, the function returns all characters except the first number of characters as specified by the arg2 value.
This function treats source field references (e.g., $your_field) as single-byte ASCII characters by default, similar to the SUBSTRING function. | arg1: CHAR
arg2: LONG | CHAR | RIGHT('green tea', 3) -> 'tea'
RIGHT('coffee beans', -7) -> 'beans | | TO_CHAR(value, format) | Converts the binary value specified by value into a character value by performing an encode or decoding using the format value specified by format.
| value — BINARY(N) or VARBINARY

format — CHAR with these supported values:

'hex' — Encodes into a hexadecimal string with no 0x prefix and lowercase letters.

'base64' — Encodes into a base64 string with padding. This encoding is not URL-safe and does not include line breaks.

'char' — Decodes into a string with the specified CHAR encoding, which can be values such as: 'utf-8', 'utf-16le', 'utf-16be', or iso-8859-1. | CHAR | TO_CHAR(BINARY('0x6f6369656e74'), 'hex') -> '6f6369656e74'

TO_CHAR(BINARY('0x6f6369656e74'), 'utf-8') -> 'ocient' | | HEX(value) | Alias for TO_CHAR(value, 'hex') | value — BINARY(N) or VARBINARY | CHAR | HEX(BINARY('0x6f6369656e74')) -> '6f6369656e74' | | TO_HASH(value, length, format) | Converts the string value specified by value into a fixed length binary value with lengh specified by length by encoding or decoding using the format value specified by format. | value — CHAR

length — INT

format — CHAR with these supported values:

'hex' — Decodes from a hexadecimal string with optional 0x prefix and case-insensitivity.

'base64' — Decodes from a base64 string with optional padding. This decoding is not URL-safe and does not expect line breaks.

char — Encodes into a string with the specified CHAR encoding, which can be values such as: 'utf-8', 'utf-16le', 'utf-16be', or iso-8859-1. | HASH(arg2) | TO_HASH('6f6369656e74', 6, 'hex') -> 0x6f6369656e74

TO_HASH('ocient', 6, 'utf-8') -> 0x6f6369656e74
| | TO_VARBINARY(value, format) | Converts the string value specified by value into a variable length binary value by encoding or decoding using the format value specified by format. | See TO_HASH description. | VARBINARY | TO_VARBINARY('6f6369656e74', 'hex') -> 0x6f6369656e74

TO_VARBINARY('ocient', 'utf-8') -> 0x6f6369656e74
| | UNHEX(value) | Alias for TO_VARBINARY(value, 'hex') | value: CHAR | VARBINARY | UNHEX('6f6369656e74') -> 0x6f6369656e74 |

When you use the SUBSTRING, RIGHT, and LEFT functions with Delimited or JSON formatted data sources, the Ocient System does not treat a source field reference (e.g., $your_field) as multi-byte character data. Each byte is treated as a single-byte ASCII character. To handle multi-byte character data like UTF-8 in a source field, you must cast the source field using CHAR($your_field) or use another function that returns CHAR data.

When you use the LENGTH, CHAR_LENGTH, and CHARACTER_LENGTH functions with the SUBSTRING, RIGHT, and LEFT functions, you might experience unintended results for multi-byte characters, such as Arabic in UTF-8 if you do not cast the source field reference to CHAR.

Binary Data Transformation Functions

Binary data transformation functions operate on bytes provided when you use the BINARY format type in a pipeline. These functions are typically used in processing mainframe data outputs.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`FLOAT(bytes [, endianness])`	With a byte array in the `BINARY` format, parse bytes in standard IEEE® 754 `binary32` (single-precision) format.	`bytes` is a 4-byte array extracted from BINARY data. `endianness` (optional) must be a literal string. Valid values include `'big'` and `'little'`. The default value is the `ENDIANNESS` option in the pipeline definition.	FLOAT	BINARY: `FLOAT($"[5, 4]") as float_col1` `FLOAT($"[5, 4]", 'little') as float_col2`
`DOUBLE(bytes [, endianness])`	With a byte array in the `BINARY` format, parse bytes in standard IEEE 754 `binary64` (double-precision) format.	`bytes` is an 8-byte array extracted from BINARY data. `endianness` (optional) must be a literal string. Valid values include `'big'` and `'little'`. The default value is the `ENDIANNESS` option in the pipeline definition.	DOUBLE	BINARY: `DOUBLE(FLOAT($"[5, 8]")) as double_col1` `DOUBLE($"[5, 8]", 'big') as double_col2`
`TINYINT(bytes [[, signed], endianness])`	Converts a 1-byte array that represents a signed or unsigned number to the `TINYINT` format. ⚠️ If you do not specify the correct number of bytes or if an unsigned transform overflows a signed target column, the function throws an error.	`bytes` is a single-byte array extracted from BINARY data. `signed` is a string literal that indicates if the bytes represent a signed or unsigned `BINARY` format. Valid values include `'signed'` and `'unsigned'`. The default value is `'signed'`. `endianness` is a string literal that indicates whether the bytes are in the big or little endian format. Valid values include `‘big'` and `'little'`. The default value is the `ENDIANNESS` option in the pipeline definition.	TINYINT	BINARY: `TINYINT($"[9, 1]") as tiny_1` `TINYINT($"[9, 1]", 'unsigned') as tiny_2` `TINYINT($"[9, 1]", 'signed', 'little') as tiny_3`
`SMALLINT(bytes [[, signed], endianness])`	Converts a 2-byte array that represents a signed or unsigned number to the `SMALLINT` format. ⚠️ The function throws an error if you do not specify the correct number of bytes or if an unsigned transform overflows a signed target column.	`bytes` is a 2-byte array extracted from BINARY data. `signed` is a string literal that indicates if the bytes represent a signed or unsigned `BINARY` format. Valid values include `'signed'` and `'unsigned'`. The default value is `'signed'`. `endianness` is a string literal that indicates whether the bytes are big or little in endian format. Valid values include `‘big'` and `'little'`. The default value is the `ENDIANNESS` option in the pipeline definition.	SMALLINT	BINARY: `SMALLINT($"[9, 2]") as small_1` `SMALLINT($"[9, 2]", 'unsigned') as small_2` `SMALLINT($"[9, 2]", 'signed', 'little') as small_3`
`INT(bytes [[, signed], endianness])`	Converts a 4-byte array that represents a signed or unsigned number to the `INT` format. ⚠️ The function throws an error if you do not specify the correct number of bytes or if an unsigned transform overflows a signed target column.	`bytes` is a 4-byte array extracted from BINARY data. `signed` is a string literal that indicates if the bytes represent a signed or unsigned `BINARY` format. Valid values include `'signed'` and `'unsigned'`. The default value is `'signed'`. `endianness` is a string literal that indicates whether the bytes are big or little endian format. Valid values include `‘big'` and `'little'`. The default value is the `ENDIANNESS` option in the pipeline definition.	INT	BINARY: `INT($"[9, 4]") as int_1` `INT($"[9, 4]", 'unsigned') as int_2` `INT($"[9, 4]", 'signed', 'little') as int_3`
`BIGINT(bytes [[, signed], endianness])`	Converts an 8-byte array that represents a signed or unsigned number to the `BIGINT` format. ⚠️ The function throws an error if you do not specify the correct number of bytes or if an unsigned transform overflows a signed target column.	`bytes` is an 8-byte array extracted from BINARY data. `signed` is a string literal that indicates if the bytes represent a signed or unsigned binary format. Valid values include `'signed'` and `'unsigned'`. The default value is `'signed'`. `endianness` is a string literal that indicates whether the bytes are big or little endian format. Valid values include `‘big'` and `'little'`. The default value is the `ENDIANNESS` option in the pipeline definition.	BIGINT	BINARY: `BIGINT($"[9, 8]") as bigint_1` `BIGINT($"[9, 8]", 'unsigned') as bigint_2` `BIGINT($"[9, 8]", 'signed', 'little') as bigint_3`
`DECIMAL(bytes, format, precision, scale [, sign_format])`	Convert a byte array that represents packed decimal or zoned decimal BINARY data to the `DECIMAL` format. With this transformation, you can indicate the format of the BINARY data as either `'zoned'` or `'packed'`.	`bytes` is an array of bytes extracted from BINARY data. `format` is a string literal that indicates the data format. Valid values include `'packed'` (meaning the array contains decimal data in each nibble for two digits per byte) and `'zoned'` (meaning the array contains decimal data in each nibble for one digit per byte). `precision` is the total number of significant digits. `scale` is the number of digits after the decimal point. `sign_format` is a string literal that indicates how the transformation should extract sign information from the bytes. Valid values include `'leading'` (means that the system stores the sign in the upper nibble of the first byte for both `'zoned'` and `'packed'` data formats), `'leading_separate'` (means the system stores the sign as a separate new first byte from the digit data), `'trailing'` (means that the system stores the sign in the upper nibble of the last byte when the data format is set to `'zoned'` and in the lower nibble of the last byte when set to `'packed'`), or `'trailing_separate'` (means the system stores the sign as a separate new last byte from the digit data). For `'leading'` and `'trailing'` sign information, accepted sign values are `0x7`, `0xB`, and `0xD` for negative signs and `0xA`, `0xC`, `0xE`, and `0xF` for positive. The accepted sign values for `'leading_separate'` and `'trailing_separate'` sign information are `0x2D` for `-` negative signs and `0x2B` for `+` positive. The default value for `sign_format` is `'trailing'` for both `format` data formats.	DECIMAL	BINARY: `DECIMAL($"[1, 10]", 'packed', 4, 2) as revenue` `DECIMAL($"[1, 10]", 'zoned', 4, 2, 'leading') as revenue`

Array Data Transformation Functions

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`ARRAY_CAP(A,N)`	Restrict the length of an array to a maximum number of elements.	`A` is an array of any type. `N` is the maximum number of elements for the length of the specified array.	Array of any type	`ARRAY_CAP(A, 1000)`
`ARRAY_COMPACT(array)`	Removes NULL values from the array.	`array` is an array that contains any type of elements. The array can be single or multi-dimensional.	Array of any type	`ARRAY_COMPACT($ints[])`
`ARRAY_CONTAINS(array, value)`	Returns `true` if the array contains the specified value. If the specified value is NULL, then this transform function returns NULL.	`array` is an array that contains any type of elements. The array can be single or multi-dimensional. `value` is an integer.	Boolean or NULL	`ARRAY_CONTAINS($ints[], 1)`
`ARRAY_DISTINCT(array)`	Return all unique values in an array. For details, see ARRAY_DISTINCT.	`array` is an array that contains any type of elements. The array can be single or multi-dimensional.	Array of any type	`ARRAY_DISTINCT($ints[])`
`ARRAY_LENGTH(array,dim)`	Returns the length of the array for the specified dimension `dim`, which starts at index 1. If you specify a dimension that is greater than the size of the array, the function returns NULL.	`array` is an array that contains any type of elements. The array can be single or multi-dimensional. `dim` is an integer.	Integer	With `$arr` containing `[[1,2,3], [4,5,6]]`, `ARRAY_LENGTH($arr,2)` returns `3` for the second dimension of the array.
`ARRAY_SORT(array)` `ARRAY_SORT(array,function)`	`ARRAY_SORT(array)` sorts and returns the input array based on the natural ordering of its elements. If one of the array elements is NULL, then this transform function sorts the NULL values to the end of the array. `ARRAY_SORT(array,function)` sorts and returns the input array based on the results of the specified Lambda function. The function should have two arguments representing two elements of the array. This function should return a negative integer, 0, or a positive integer if the first element is less than, equal to, or greater than the second element, respectively. For details, see ARRAY_SORT.	For the first syntax, `array` is an array of elements with these types: `BIGINT`, `BOOLEAN`, `BYTE`, `DATE`, `DOUBLE`, `FLOAT`, `INT`, `SMALLINT`, `UUID`, or `VARCHAR`. For the second syntax, `array` is an array that contains any type of elements. The array can be single or multi-dimensional. The `function` argument is a Lambda function with the format `(x T, y T) -> INT` or another SQL reference function.	Array of any type	`ARRAY_SORT($"1d"[], (x int, y int) -> CASE WHEN x = y THEN 0 WHEN INT(COALESCE(x, 1000)) < INT(COALESCE(y, 1000)) THEN -1 ELSE 1 END)` sorts the array in `1d` column using the Lambda function with a CASE statement. The CASE statement returns 0 when x and y are equal, returns -1 when the integer cast of the non-NULL value x is less than the corresponding value for y, and returns 1 otherwise.
`ELEMENT_AT(array,I)`	Returns the element value of the array or tuple at the specified index. You can specify a negative index value as long as the absolute value of the index does not exceed the length of the array or tuple. If the index is out of bounds, the function returns NULL.	`array` is an array or tuple. `I` is a 1-based index, specified as an integer.	Any type.	`$a` accesses the array `[1,2,3]`. `ELEMENT_AT($a,1)` returns `1`. `ELEMENT_AT($a,-1)` returns `3`. `ELEMENT_AT($a,-2)` returns `2`. `ELEMENT_AT($a,-4)` returns NULL.
`FLATTEN(array)`	Transforms an `N`-dimensional array into an `N-1`-dimensional array. If one of the inner array elements is NULL, this transform function returns NULL.	`array` is a multi-dimensional array, for example `T[][]`, that contains any type of elements.	Array of any type that can be single or multi-dimensional.	`FLATTEN($ints[][])`
`REDUCE(array, start_value, merge_function, finish_function)`	Applies a merge function to a starting value and all elements in the array, and then reduces the array to a single value. Optionally, specify a finish function for the returned single value. The function applies the merge function to each element of the array with the start value to return a final value.	`array` is an array of any type and can be a single or multi-dimensional array. `start_value` is the starting value of any type. `merge_function` is a Lambda function or any reference function. This Lambda function has the form `(x U, y T) -> U`. `finish_function` is a Lambda function or any reference function. This Lambda function has the form `(x U) -> V`.	This function returns the value of type `V`, which can be any type, from the finish function. If you do not specify the finish function, this function returns the type `U`, which can be any type, from the merge function.	Assuming `ints[]` is `INT[]` of `[1, 2, 3]`, `REDUCE($ints[], 0, (acc int, x int) -> acc + x)` returns the value `6`. Assuming `ints[]` is `INT[]` of `[1, 2, 3]`, `REDUCE($ints[], 0, (acc int, x int) -> acc + x, BOOLEAN)` returns the `true` value. In this case, the finish function is the BOOLEAN reference function with the input value being the resulting value from the merge function: `BOOLEAN(6)`.
`TO_ARRAY_LENGTH(V,N)`	Create the specified number of copies of any JSON object in an array.	`V` is any JSON object to copy into the result array. `N` is the number of copies to make in the result array.	Array of any type	`TO_ARRAY_LENGTH(V, 3)`

Logical Operations Transformation Functions

These transformation functions execute logical operations on the data in the data pipeline.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`IF(X,T,F)`	Returns `T` if the expression `X` evaluates to `true`, or the function returns `F` if `X` evaluates to `false`. `IF` and `IFF` are function aliases.	Any type	Any type	`IF('$2', '1', '2') AS output`
`CASE` `WHEN expression1 THEN result1` `WHEN expression2 THEN result2` `...` `ELSE result` `END`	Returns the `result` value based on whether an expression is `true`. If none of the expressions evaluate to `true`, then the function returns the `result` value after the `ELSE` keyword.	Any type	Any type	`CASE WHEN $x THEN 'True was input in'` `ELSE 'False was input in' END AS toutput`
`NULL_IF(X, Y)`	Returns the `NULL` value if the two input arguments are equal. If the arguments are not equal, the function returns `X`.	Any type	Any type	`INT(NULL_IF($32, ''))` `INT(NULL_IF($a.b.c, ''))`

Network Type Functions

Network type functions are also supported in loading. For example, SUBNET(IP($9),13) AS ipv6_subnet_form1 in the CREATE PIPELINE SELECT SQL statement converts the input data in the column to an IPv6-mapped IPv4 address and then computes the prefix of size 13 for that address. For the supported network type functions, see Network Type Functions.

Other Data Transformation Functions

Other data transformation functions include the extraction of values from the JSON format and miscellaneous functions such as COALESCE, which are commonly used when working with data pipelines.

Name	Description	Supported Input Type	Return Type	Example SELECT Expression
`COALESCE(arg1, arg2)`	Returns `arg1` if it is non-NULL; otherwise, the function returns `arg2`. `arg1` and `arg2` can be any data type, but their types must match.	Any type	Matches `arg1` and `arg2` type	`COALESCE($1, $2)` `COALESCE(int($1), int($2))` `COALESCE(bigint($1), 13)` `COALESCE(substring(char($1), 1, 2), TRIM($2))`
`JSON_EXTRACT_PATH_TEXT(X, D)`	Extracts a value from a JSON string.	`X` is the JSON string for extracting a value in `CHAR` type. `D` is the key of the value to extract in `CHAR` type.	CHAR	`JSON_EXTRACT_PATH_TEXT(JSON_UNESCAPE($real_field_key), 'b') AS col_char`
`MAP_KEYS(X)`	Returns the keys in the specified JSON string.	`X` is the JSON string.	ARRAY OF STRINGS	Input JSON string `a`: `'{"name":"John", "age":30, "car":null}'` `MAP_KEYS($a)` Output: `["age", "car", "name"]`
`MAP_VALUES(X)`	Returns the values in the specified JSON string.	`X` is the JSON string.	ARRAY OF STRINGS	Input JSON string `a`: `'{"name":"John", "age":30, "car":null}'` `MAP_VALUES($a)` Output: `["John", "30", null]`

DEFAULT Keyword

You can use the DEFAULT keyword to include the configured default value of a column in transformation functions.

Example

COALESCE($my_field, DEFAULT) as my_column

Lambda Functions

Lambda functions are a transformative tool in data pipelines, enabling declarative and highly flexible data transformations. By supporting per-element operations on arrays and nested data structures, lambda functions streamline data ingestion and reduce the need for post-processing. Their adoption enables you to handle complex, real-time data engineering scenarios natively.

Lambda functions enable you to apply custom logic to each element of an array during ingestion. They support advanced operations like filtering and aggregation. You can embed transformation logic directly in the pipeline definition. This embedding reduces the need for additional processing steps after the load.

Expression	Description
Lambda Function Expression (T1,…,Tn) -> U	A lambda function is a temporary function that takes 0 or more named arguments as a parenthesis-enclosed list and returns an expression. The expression can reference objects defined within the closure of the expression, which includes locally named arguments that are locally defined within an enclosing scope, field references, literals, etc. For examples, see Special Data Pipeline Transformation Functions.
Reference Function Expression UPCASE = UPCASE(x) -> CHAR tuple = tuple(T1,…,Tn) -> tuple<<T1,…,Tn>>	A reference function points to an existing function in the Ocient function list. Specifying the function name allows you to use that function in contexts where you would otherwise need to create a full lambda function. For examples, see Special Data Pipeline Transformation Functions.

Expression

Description

Lambda Function Expression
(T1,…,Tn) -> U

A lambda function is a temporary function that takes 0 or more named arguments as a parenthesis-enclosed list and returns an expression. The expression can reference objects defined within the closure of the expression, which includes locally named arguments that are locally defined within an enclosing scope, field references, literals, etc.
For examples, see Special Data Pipeline Transformation Functions.

Reference Function Expression
UPCASE = UPCASE(x) -> CHAR
tuple = tuple(T1,…,Tn) -> tuple<<T1,…,Tn>>

A reference function points to an existing function in the Ocient function list. Specifying the function name allows you to use that function in contexts where you would otherwise need to create a full lambda function.
For examples, see Special Data Pipeline Transformation Functions.

User-Defined Data Pipeline Functions

You can create a user-defined data pipeline function using the CREATE PIPELINE FUNCTION SQL statement and Groovy. After you create data pipeline functions, you can execute them in data pipelines like any other function to transform records.

For more details, see CREATE PIPELINE FUNCTION and Data Types for User-Defined Data Pipeline Functions.

Formatting Functions

Data Types for Data Pipelines

Data Formats for Data Pipelines

Load Metadata and File-Based Partitioned Data in Data Pipelines

Data Pipeline Load of JSON Data from Kafka

Data Pipeline Load of Parquet Data from S3

Data Pipeline Load of CSV Data from S3