Programming ETL

summary

This help page describes the Data****Settings>Programming ETL in Transfer Settings STEP 2.
Programming ETL allows you to execute programs you have written on data retrieved from the source.
Programming ETL allows for flexible conversion processes that cannot be performed with template ETL.

Languages Supported

• Python 3.9
• Ruby 3.2
• Ruby 2.7

constraints

• Only conversion processes can be written in single line units.
  • It is not possible to carry over the results of one line of processing to a subsequent line.
  • Therefore, it is not possible to describe aggregate processing that spans multiple lines. Please understand that we are not responsible for any loss or damage to your property.
• The memory limit for program execution is approximately 1 GB.
• Schema generation when creating or editing transfer settings is designed to time out after 10 minutes have elapsed since execution.
• On the other hand, there is no special timeout setting for programming ETL processing during transfer job execution.
  • A processing time of about 1 minute per line will run without problems.

Setup procedure

Programs written in programming ETL have two roles.

• When creating or editing transfer settings, the schema is used to create the schema.
• When a transfer job is executed, processing is performed on the data acquired from the transfer source.

The following describes the procedure for creating a schema when creating or editing transfer settings.

1. input data generation

Click Generate Input Data.
Data with the template ETL settings reflected will be generated in JSON format in the form of input data for schema generation.
For details, see Format of Input Data for Schema Generation below.

2. source code description

Write the program in the source code.
For details, see the role of each element in the source code below.

3. schema generation

Click Generate Schema.
The program is executed on the input data generated in step 1.
When execution is complete, data in JSON format is displayed in the output data during schema generation.
The schema will also be updated when programming ETL is enabled.

Editing the schema

Edit the updated programming ETL enable schema accordingly.

5. schema storage

Click to preview or confirm****changes.
Whichever you click, the programming ETL settings are saved.
If you want to see the data with the programming ETL executed, click Preview Changes.

Specifications related to programming

Format of input data for schema generation

• Input data is stored in a variable with an array (list) structure called rows.
• Each element of rows corresponds to the data in each row, and each element is a hash (dictionary) structure with columns as keys.

rows :. [
  {<row1_columnA>: <value>, 
   <row1_columnB>: <value>, 
   <row1_columnC>: <value>
  },
  {<row2_columnA>: <value>, 
   <row2_columnB>: <value>, 
   <row2_columnC>: <value>
  },
  {<row3_columnA>: <value>, 
   <row3_columnB>: <value>, 
   <row3_columnC>: <value>
  }
]

Role of each element in the source code

In the following, Python is used as an example.

from etl_base import EtlBase

class Etl(EtlBase):.
  def __init__(self):.
    pass (e.g. skipping a move, passing an examination, ticket to allow entry, etc.)

  def transform_row(self, row):.
    row ["columnA"] = "hoge" # Existing column's value has changed.
    row ["new_column"] = "fuga" # New column is added.
    del row ["columnC"] # Existing column is deleted.
    return row

  def before_action(self):.
    pass (e.g. skipping a move, passing an examination, ticket to allow entry, etc.)

EtlBase and Etl classes

The EtlBase and Etl classes are the necessary elements to perform programming ETL.
Do not delete or rename.

transform_row method

Please describe the process in this method as appropriate.
When the program is executed, each element of the rows is passed to the transform_row method one row at a time.
By default, each element of rows is passed to row. The following description assumes that row data is passed to row.

• Rewriting the value of a key that exists in a row changes the value of the corresponding column.
• Adding a new key to row adds a new column with the key name as the column name.
• Deleting a key that exists in a row deletes the corresponding column.

Note that the schema is created and transferred based on the return value of the transform_row method.
Therefore, RETURN the processed ROW.
If a NULL value is returned, the transfer of the corresponding line is skipped. ( None for Python and nil for Ruby)

before_action method

It is executed only once, before the transform_row method.
Within the before_action method, instance variables can be assigned.
The value can also be used within the transform_row method.
Use this function when you want to perform arbitrary arithmetic operations before sequential processing of rows.

def before_action(self):.
  self.hoge = "hoge"

supplementary information

Manual editing of input data for schema generation

Standard output/Error output