Accessing Additional Columns in Modification

Overview

This guide describes how additional columns can be accessed in Modification Methods.

Additional columns can be made available by including them in a Reduction Rule. The data of these columns can then be retrieved and used inside a Modification Method.

This is possible even if:

the columns do not exist in the table that is modified, or
the columns are not present in the target table.

This guide provides two examples:

using data from another table in a Modification Method,
copying modified data to a target table with fewer columns.

Prerequisites

This guide uses tables from the tutorial’s sample environment.

The prerequisites are:

A connection to the database where the source table is located. For the first example, a connection to the sample environment is recommended.
Read access to the source table.
Write access to the target table.

For the sample environment, these permissions are already available.

Steps

Example 1: Access Data from Another Table

The goal of this example is to retrieve data from a column that does not exist in the table being modified.

In this example, the table titles is masked. The title column should contain either:

Senior Employee, or
Junior Employee.

The value depends on the employee’s salary.

Step 1: Create the Modification Method

Create a Modification Method that checks whether the salary of each row is above 90000.

If the salary is above 90000, the title is changed to Senior Employee. Otherwise, it is changed to Junior Employee.

Use the following apply() method:

def apply() {
    indexOfSalary = ctx.indexOf('salary')
    if (indexOfSalary != -1) {
        salary = data[indexOfSalary]
        if (salary > 90000) {
            data[columnIndex] = "Senior Employee"
        } else {
            data[columnIndex] = "Junior Employee"
        }
    }
    return true
}

After creating the Modification Method, add it to a new Modification Set by using a Modification Rule.

The Modification Rule must be applied to the title column.

Step 2: Create the Task Template

Create a new Table Copy Task Template.

For the connections, the tutorial connections can be used:

Source Connection: Sample production database
Target Connection: Sample testing database as super user

Then add the Modification Set to the Task Template.

Step 3: Add Selection, Mapping, and Reduction Rules

Add the following rules to the Task Template:

A Selection Rule for the production schema.
A Mapping Rule that maps the source schema to the q3 schema.
A Reduction Rule for the titles table in the production schema.

Use the following Reduction Rule Code:

<#--template-->
  SELECT ${columns}, s.salary AS salary
  FROM ${tableName} "T"
  INNER JOIN production.employees e ON (e.emp_no = "T".emp_no)
  INNER JOIN production.salaries s ON (s.emp_no = e.emp_no)
  WHERE s.to_date <= "T".to_date AND s.to_date > "T".from_date
  -- the following line is necessary in a RLP context
  AND ${keys}

This query joins titles with employees and salaries.

By selecting s.salary AS salary, the salary column becomes available in the data array of the Modification Method.

Step 4: Verify the Result

Create and execute a task from the Task Template.

After execution, use the Schema Browser to verify the result.

The title column of the titles table should now contain only:

Senior Employee, or
Junior Employee.

Example 2: Copy Modified Data to a Table with Fewer Columns

The goal of this example is to retrieve data from one column, use it in a Modification Method, and copy the modified result to a target table that does not contain all source columns.

For this example, a database different from the sample environment is recommended.

Example scenario:

The source table contains first_name and last_name.
The target table contains only one column named name.
The Modification Method combines first_name and last_name.
The result is written to the target column name.

Step 1: Create the Modification Method

Create a Modification Method that reads first_name and last_name from the data array.

The method combines both values and writes the result back to the first_name field.

def apply() {
    def lastNameIndex = ctx.indexOf('last_name')
    def lastName = data[lastNameIndex]

    def firstNameIndex = ctx.indexOf('first_name')
    def firstName = data[firstNameIndex]

    def newName = firstName + " " + lastName
    data[firstNameIndex] = newName

    return true
}

After creating the Modification Method, add it to a new Modification Set by using a Modification Rule.

The Modification Rule must be applied to the first_name column.

Step 2: Create the Task Template

Create a new Table Copy Task Template.

Then add the Modification Set to the Task Template.

Step 3: Add the Required Rules

Add the required Selection Rule and Mapping Rule.

In addition, add another Mapping Rule to rename the column:

Source column: first_name
Target column: name

Since the last_name column is part of the source table, no Reduction Rule is required to make it available in the data array.

Step 4: Verify the Result

Create and execute a task from the Task Template.

After execution, use the Schema Browser to verify the target table.

The target table should no longer contain:

first_name
last_name

Instead, it should contain:

name

The name column contains the combined full name.

Benefits

This approach provides the following benefits:

Additional data can be used in Modification Methods without copying it to the target table.
Data from related tables can be included by using Reduction Rules.
Modification logic can use more context than the currently modified column.
Target structures can be simplified, for example by merging multiple source columns into one target column.
Complex transformation scenarios can be implemented without additional processing steps.

Conclusion

Using additional columns in Modification Methods enables more flexible transformation scenarios.

Reduction Rules can provide extra data from related tables, while Mapping Rules can be used to adapt the target structure.

This makes it possible to build more powerful modification setups while keeping the task configuration manageable.