Accessing additional columns by using a Reduction Rule

Overview

This guide describes how additional columns can be accessed in Modification Methods by including them in a Reduction Rule. The data of those columns can be retrieved and used in Modification Methods, even if the columns do not exist in the source table and will not be present in the target table. An example for both cases is provided by this guide.

Prerequisites

In this guide, different tables that are located in the tutorial’s sample environment are being accessed.

The prerequisites here 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 and write access to the target table. This is already the case for the sample environment.

Example of acquiring data from different tables

The goal here is to be able to retrieve data from a column that does not exist in the table that is being modified. In this case, the table 'titles' will be masked, so that the 'title' column only contains either 'Senior Employee' or 'Junior Employee', depending on the employee’s salary. Different XDM objects are required to achieve this goal.

Creating the Modification Method

First, a Modification Method is required. This method will check if the salary of each row of the provided table is above 90.000. If so, the title will be changed accordingly. To achieve this, the following apply() method needs to be included in the modification code:

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 its creation, it can be added to a new Modification Set via a new Modification Rule. This Modification Set will later be used by a Task Template.

The Modification Rule needs to be applied to the 'title' column.

Creating the Task Template

A new Table Copy Task Template is required as well. For the Source Connection, the Sample production database Connection may be used. For the Target Connection, the Sample testing database as super user Connection may be used.

Both Connections are used in the tutorial.

Afterward, the Modification Set is added to the Template.

Adding Rules to the Task Template

The next step is to add a Selection Rule to the production schema and a Mapping Rule that maps to the q3 schema. After that, a Reduction Rule is added to the Template. The Reduction Rule will be applied on the 'titles' table in the production schema and contain the following 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 code describes a join of 'titles' with 'employees' and 'salaries'. By doing so and selecting the required columns, the Data Array in the Modification Method will have access to the data of the 'salary' column.

Result of the Modification Method

After creating a Task for the Template and executing it, the Schema Browser can be used to verify that now all entries of the 'title' column of the table 'titles' either contain 'Senior Employee' or 'Junior Employee'.

Example of copying modified data to a table with fewer columns

The goal here is to retrieve data from a column, use that data in a Modification Method and copy the table and the modification results to a target table not containing the column.

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

An example of this would be to have a table containing a 'first_name' column and a 'last_name' column. The last name of each entry would be extracted and merged with the first name to replace both columns with a new column 'name' that contains the full name of each entry.

Different XDM objects are required to achieve this goal.

Creating the Modification Method

First, a Modification Method is required. This method will retrieve both first and last name from the data array, combine them and override the current value of the first name with the result. To achieve this, include the following apply() method to the modification code:

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
  //printf("merged first and last name into " + data[firstNameIndex]);
  return true
}

After its creation, it can be added to a new Modification Set via a new Modification Rule. This Modification Set will later be used by our Task Template.

The Modification Rule needs to be applied to the 'first_name' column.

Creating the Task Template

Now create a new Table Copy Task Template and add the Modification Set.

Adding Rules to the Task Template

In addition to the Selection Rule and Mapping Rule, one more rule needs to be added to the Template as well.

Add another Mapping Rule to override the column name 'first_name' to just 'name'.

Since the 'last_name' column is part of the source table, a reduction rule is not required to include the data in the data array.

Result of the Modification Method

After creating a Task for the Template and executing it, the Schema Browser can be used to verify that the target table does not contain the columns 'first_name' or 'last_name'. Instead, a new column 'name' has appeared, containing the full name of each entry.

Conclusion

These approaches enable users to create more complex modification setups while simultaneously simplifying the necessary steps to take.