Transform dataset using JavaScript or Python

Overview

Scripting transformations are code transformations executed at the different steps in the ETL process, such as before extract/load, for each row, after extract row, etc.

The typical use cases are:

• Change a data set on the fly, including fields, values, and structure.
• Programmatically set global and flow variables.
• Modify source query.

You can use JavaScipt (default) or Python in the scripting transformation.

Scripting transformations

To configure one of the following scripting transformations, go to:

• Transformation/ MAPPING/ Additional Transformations/ Before Prepare Source Query: Scripting transformation executed before the source query is merged with the flow variables.
• Transformation/ MAPPING/ Additional Transformations/ Before Extract: Scripting transformation executed before extract.
• Transformation/ MAPPING/ Additional Transformations/ For each row: Scripting transformation executed for each extracted row. The alternative to the scripting transformation For each row is per-field Mapping.
• Transformation/ MAPPING/ Additional Transformations/ After Extract: Scripting transformation executed after extract.
• Transformation/ MAPPING/ Additional Transformations/ After Load: Scripting transformation executed after load.

Before Prepare Source Query

This transformation is executed right before the Source query is merged with the Flow variables, so it is a perfect place for setting the values of the Flow variables.

It can also be used to programmatically modify the Source query itself. 

Change the Source query programmatically

source.setSql('select * from table where id > {VAR_NAME});

Set the value of the Flow variable

scenario.getVariable('VAR_NAME').setValue(value);

Before Extract

This transformation is executed before the extract. It can be used to set global and Flow variables, logging, or for any other reason.

For Each Row

This transformation is executed for each row of the extracted data set. Use it to modify field values on the fly. The alternative to the scripting transformation For each row is per-field Mapping.

After Extract

This transformation is executed after the extract is finished. It can be used to create a completely new data set using the existing data set as a source. For example, you can create a highly-nested data set from the source, which later will be used to create a complex XML document. In order for this transformation to work, streaming must be disabled (Parameters/ Stream Data/ Disable).

After Load

This transformation is executed in the last step of the extract-transform-load after the data was successfully loaded into the destination. It can be used to set global and Flow variables, logging, clean up, or for any other reason.

You can use all 4 transformations together.

Available objects

The following objects can be referenced by name from JavaScript or Python code:

Most commonly used methods

The following methods of the DataSet object are typically used when modifying a data set.

Example

The example below demonstrates how to traverse the original data set and create a brand new one, which will then be used to create a destination file. The After extract transformation is used for this. 

var stageDataSet = new com.toolsverse.etl.common.DataSet();

var rows = dataSet.getRecordCount();

for (row = 0; row < rows; row++) {
    var record = dataSet.getRecord(row);

    stageDataSet.addField("Name.FirstName", dataSet.getFieldValue(record, "firstName"));
    stageDataSet.addField("Name.LastName", dataSet.getFieldValue(record, "lastName"));

    stageDataSet.addField("Age", dataSet.getFieldValue(record, "age"));
    stageDataSet.addField("City", dataSet.getFieldValue(record, "city"));
    stageDataSet.addField("State", dataSet.getFieldValue(record, "state"));
    stageDataSet.addField("ZipCode", dataSet.getFieldValue(record, "postalCode"));
    stageDataSet.addField("Addr1", dataSet.getFieldValue(record, "streetAddress"));

    var phones = dataSet.getFieldValue(record, "phoneNumbers");

    if (phones != null && phones.getRecordCount() > 0) {
        var phonesCount = phones.getRecordCount();

        for (phoneRow = 0; phoneRow < phonesCount; phoneRow++) {
           stageDataSet.addField("Phone.Type", 
              dataSet.getFieldValue(record, "phoneNumbers.phoneNumbers_type", '.', phoneRow));
           stageDataSet.addField("Phone.Num", 
              dataSet.getFieldValue(record, "phoneNumbers.phoneNumbers", '.', phoneRow));             
        }  
    }  
}  

dataSet.setFields(stageDataSet.getFields());

dataSet.setData(stageDataSet.getData());