|
<< Click to Display Table of Contents >> ETL > 12.6 > Implementation Guide > Tutorial and implementation guide > Steps > Mapping Transformed value |
The value can be transformed using a built-in transform. The following transformation are available:
Transform name |
Behavior |
Lowercase |
Returns lowercase variant of the value. This transformation by default preserves the original data type. |
Uppercase |
Returns uppercase variant of the value. This transformation by default preserves the original data type. |
IsNullOrEmpty |
Returns 0 is the value is not null or empty or 1 otherwise. This transformation maps by default to a boolean value. |
IsNotNullOrEmpty |
Returns 1 is the value is not null or empty or 0 otherwise. This transformation maps by default to a boolean value. |
Length |
Returns the length of the string value. This transformation maps by default to a 32-bit integer value. |
MD5 |
Hashes the given column with MD5 hash. Returns 128-bit (16 bytes) hexadecimal string of 32 characters. |
SHA1 |
Hashes the given column with SHA1 hash. Returns 160-bit (20 bytes) hexadecimal string of 40 characters. |
There are three ways of defining transformation:
Simple definition:
{
"columns":
{
"target_column_name": "source_column_name-><type>"
}
}
Object definition:
{
"columns":
{
"target_column_name":
{
"name": "source_column_name",
"transform": "<type>"
}
}
}
Full object definition
{
"columns":
{
"target_column_name":
{
"name": "source_column_name",
"transform":
{
"type": "<type>"
}
}
}
}
Where <type> is one of the supported value: Lowercase, Uppercase, IsNullOrEmpty, IsNotNullOrEmpty or Length. Transformation names are case-insensitive. All three ways of defining a transformation are functionally and technically equal. We recommend using the simplified string notation, as it is the least verbose of all three.
Given the following table SourceTable:
Name |
Surname |
Age |
|---|---|---|
Marcin |
Otorowski |
34 |
John |
<null> |
51 |
Kate |
Binks |
25 |
And the following JSON step definition:
{
"id": 1,
"type": "map",
"name": "Example of transform-columns",
"source": "SourceTable",
"columns": {
"Name": "Name",
"Name_Uppercase": "Name->uppercase",
"Surname_IsProvided": "Surname->isnotnullorempty",
"Surname_Length": "Surname->length"
},
"target": "TargetTable_TransformedColumns"
}
The following result table TargetTable_TransformedColumns is expected:
Name |
Name_Uppercase |
Surname_IsProvided |
Surname_Length |
|---|---|---|---|
Marcin |
MARCIN |
1 (bit) |
9 |
John |
JOHN |
0 (bit) |
0 |
Kate |
KATE |
1 (bit) |
5 |
Using the type property lets you define a custom cast or custom type length. This common property is available for all column types, see more information in chapter Arbitrary column types.
It is possible to define a custom value returned, if the source column does not exist in the table. For more information about the usage of fallback values, see the Fallback columns chapter.