> ## Documentation Index > Fetch the complete documentation index at: https://docs.ocient.com/llms.txt > Use this file to discover all available pages before exploring further. # Transform Data in Data Pipelines export const Unix = "Unix®"; export const Ocient = "Ocient®"; export const Java = "Java®"; export const IEEE = "IEEE®"; export const Groovy = "Apache® Groovy™"; 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 JSON Selectors](/data-formats-for-data-pipelines#supported-json-selectors) * [Understand Source Fields and Data Formats](/data-types-for-data-pipelines#source-fields) ## 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](/data-pipelines#create-pipeline-function) using {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](/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](/point-constructors#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 <>(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<>`. | CHAR | TUPLE of type | CSV:
`TUPLE<>($37) as int_decimal_timestamp_binary_tuple_col`
`TUPLE<>($4,$8,$1) as int_decimal_timestamp_binary_tuple_col_multi_input`
JSON:
`TUPLE<>($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](/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](/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](/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](/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](/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](/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](/formatting-functions#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 )](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html).

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](/formatting-functions#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 )](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html).

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](/formatting-functions). 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 )](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html).

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 length 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-functions-and-operators#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](/array-functions-and-operators#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](/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** ```sql SQL theme={null} 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](/special-data-pipeline-transformation-functions). | | **Reference Function Expression**
UPCASE = UPCASE(x) -> CHAR
tuple = tuple(T1,...,Tn) -> tuple\<\> | 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](/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](/data-pipelines) and [Data Types for User-Defined Data Pipeline Functions](/data-types-for-user-defined-data-pipeline-functions). ## Related Links [Formatting Functions](/formatting-functions) [Data Types for Data Pipelines](/data-types-for-data-pipelines) [Data Formats for Data Pipelines](/data-formats-for-data-pipelines) [Load Metadata and File-Based Partitioned Data in Data Pipelines](/load-metadata-and-file-based-partitioned-data-in-data-pipelines) [Data Pipeline Load of JSON Data from Kafka](/data-pipeline-load-of-json-data-from-kafka) [Data Pipeline Load of Parquet Data from S3](/data-pipeline-load-of-parquet-data-from-s3) [Data Pipeline Load of CSV Data from S3](/data-pipeline-load-of-csv-data-from-s3)