- 07 Dec 2022
- Print
- DarkLight
- PDF
ETL Templates
- Updated on 07 Dec 2022
- Print
- DarkLight
- PDF
This is a machine-translated version of the original Japanese article.
Please understand that some of the information contained on this page may be inaccurate.
summary
STEP2. This is a help page that explains each item of template ETL that can be set in Advanced Settings.
Column Definition
From the data obtained from the transfer source set in STEP1, filter or add columns, and specify the columns to be transferred.
You can make the following changes to the source column:
- Column name
- data type
- default value
- Date format (for datetime data)
If the source column changes, you can update it to the latest definition by rereading the column definition.
For details, refer to Reloading columns.
Expand JSON Columns
In the column definition, if a JSON type string is stored in the field among the data retrieved from the transfer source, trocco recognizes it as a JSON column and can select the JSON column to expand.
Expand the data stored in JSON as a column by specifying the column name, JSON path, data type, date format, and time zone.
For the expanded column name, enter an arbitrary, unique value.
For details, refer to How to get the value by specifying the element when the JSON of the transfer source column contains an array.
Filter settings
Set the conditions for narrowing down the rows of data with AND conditions or OR conditions.
Only rows that meet the criteria are transferred.
You can specify the target column name and filter value, and select the following conditional expressions:
>
>=
<
<=
==
!=
start_with
end_with
include
IS NULL
IS NOT NULL
Masking settings
Replaces the specified part of the *
specified field with .
Columns that you do not want to transfer as the original data, such as personal information, can be transferred in a masked state.
There are four types of masking, each of which is as follows:
Type | description |
---|---|
Full string | * Replace with all. |
E-mail address | Replace up to the * @ mark with . |
regular expression | Replace the part that matches the * regular expression pattern with . |
Substring | Specify* the start and end positions and replace them with . |
Transfer time column settings
Set whether to add a column to record the transfer time.
It can be used for partitioning and sharding tables depending on the transfer time.
String Regular Expression Substitution
Replaces the part of a particular column that matches the specified regular expression with another string.
This function can be used to format data, such as removing spaces and symbols to correct notation fluctuations.
For details, refer to About string regular expression substitution.
String conversion
Specify the target column name and conversion method to convert a string of type String.
You can normalize full-width alphanumeric characters to half-width and half-width kana to full-width.
For details, refer to About the string conversion function.
Hashing columns
Columns that you do not want to transfer as the original data, such as personal information, are converted to hashed with SHA-256 and transferred.
Please note that it is not strictly encrypted.