In some cases, extra customization and flexibility may be required, which is beyond of scope of simple transforms or switch-case statements. If this happens, you can use custom SQL snippets for columns.
You can use all language construct of SQLite, like string, date and other operations. Additionally, some extra functions implemented by ETL library are available (the list and reference can be found in chapter Extra functions)
{
"columns":
{
"source_column": "source_column",
"source_column_normalized":
{
"sql": "SUBSTR(source_column, 1, 3)"
}
}
}
The list of standard functions can be found here: https://sqlite.org/lang_corefunc.html
Example
Given the following table SourceTable:
Name |
Surname |
Age |
|---|---|---|
Marcin |
Otorowski |
34 |
John |
Smith |
51 |
Kate |
Binks |
25 |
And the following JSON step definition:
{
"id": 1,
"type": "map",
"name": "Example of custom SQL columns",
"source": "SourceTable",
"columns": {
"Name": "Name",
"IsJohn": { "sql": "iif([Name] = \"John\", \"Hello John\", \"Hello, do I know you?\")" },
"Substring": { "sql": "SUBSTR([Name], 1, 3)" },
"Random": { "sql": "random()" }
},
"target": "TargetTable_SqlColumns"
}
The following result table TargetTable_SqlColumns is expected:
Name |
IsJohn |
Substring |
Random |
|---|---|---|---|
Marcin |
Hello John |
Joh |
-6849993107088852193 |
John |
Hello, do I know you? |
Mar |
5566813575801383258 |
Kate |
Hello, do I know you? |
Kat |
5850467925658599088 |
|
Note: Special character " and \ should be escaped in JSON file. This is why in the example above, a \" token is used instead of a single ". |
Arbitrary types, casting and converting
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.