ETL Templates
  • 07 Dec 2022
  • PDF

ETL Templates

  • PDF

Article summary

Note

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.

columndefinition.png

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.

jsoncolumn.png

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

filter.png

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:

Typedescription
Full string* Replace with all.
E-mail addressReplace up to the * @ mark with .
regular expressionReplace the part that matches the * regular expression pattern with .
SubstringSpecify* the start and end positions and replace them with .

masking.png

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.


Was this article helpful?

What's Next