Preface
Transformations
- Active and passive transformations
- Transformation types
- Licensed transformations
- Incoming fields
  - Field name conflicts
    - Creating a field name conflict resolution
  - Field rules
- Data object preview
- Variable fields
- Transformation caches
- Expression macros
- File lists
- Configuration for multibyte hierarchical data
Source transformation
- Source object
- File sources
- Database sources
- Web service sources
- Partitions
  - Partitioning rules and guidelines
  - Partitioning examples
- Reading hierarchical data in advanced mode
- Configuration for multibyte hierarchical data
- Source fields
  - Editing native data types in complex file sources
  - Editing transformation data types
Target transformation
- Target object
  - Target file creation on advanced clusters
- File targets
- Database targets
- Web service targets
  - Web service operations for targets
  - Field mapping for web service targets
- Partitions
- Writing hierarchical data in advanced mode
- Configuration for multibyte hierarchical data
- Target fields
- Target transformation field mappings
- Configuring a Target transformation
Access Policy transformation
- Access Policy transformation configuration
- Access Policy transformation example
B2B transformation
- B2B Incoming Fields
- B2B settings
- Output fields
- Field mapping
- Advanced settings
Aggregator transformation
- Group by fields
- Sorted data
- Aggregate fields
- Advanced properties
- Hierarchical data in advanced mode
- Aggregator transformation example
Cleanse transformation
- Cleanse transformation configuration
  - Cleanse asset considerations
  - Synchronizing data quality assets
- Cleanse transformation field mappings
- Cleanse transformation output fields
- Advanced properties
Data Masking transformation
- Masking techniques
- Configuration properties for masking techniques
- Credit card masking
- Email masking
  - Advanced email masking
- IP address masking
- Key masking
- Phone number masking
- Random masking
- Social Insurance number masking
- Social Security number masking
- Custom substitution masking
- Dependent masking
  - Dependent masking parameters
- Substitution masking
- URL address masking
- Mask rule parameter
- Creating a Data Masking transformation
- Consistent masked output
  - Rules and guidelines
  - Example
- Data Masking transformation example
Data Services transformation
- Dynamic service name
- Status tracing messages
- Data Services properties
- Data Services transformation input fields
- Data Services transformation output fields
- Data Services transformation field mapping
Deduplicate transformation
- Deduplication and consolidation operations
- Identity population data
- Groups in duplicate analysis
  - Example: Selecting a group key column
- Deduplicate transformation configuration
- Deduplicate transformation field mappings
- Metadata fields on the Deduplicate transformation
- Link scores and driver scores
- Deduplicate transformation output fields
- Advanced properties
Expression transformation
- Expression fields
- Expression editor
- Transformation language components for expressions
- Expression syntax
- String and numeric literals
- Adding comments to expressions
- Reserved words
- Window functions
  - Frame
  - Partition and order keys
- Example: Use a window to calculate expiration dates
- Example: Use a window to flag GPS pings
- Example: Run an aggregate function on a window
- Advanced properties
- Hierarchical data in advanced mode
Filter transformation
- Filter conditions
- Advanced properties
- Hierarchical data in advanced mode
Hierarchy Builder transformation
- Configure output settings
  - Rules and guidelines for hierarchical schemas
  - Single output example
- Join and map fields for data conversion
  - Joining incoming data
  - Mapping relational fields to hierarchy fields
- Configure advanced properties
- Configuration for multibyte hierarchical data
- Hierarchy Builder transformation example
Hierarchy Parser transformation
- Using a Hierarchy Parser transformation
- Hierarchy Parser rules and guidelines
- Choosing a sample or schema file
- Hierarchical schemas
  - Rules and guidelines for hierarchical schemas
  - Creating a hierarchical schema
- Input settings
  - Selecting a hierarchical schema
  - Creating a hierarchical schema from sample
- Input field selection
- Field mapping
  - Selecting the elements to convert
- Output fields
- Selecting an output group
- Configuration for multibyte hierarchical data
- Hierarchy Parser transformation example
Hierarchy Processor transformation
- Hierarchy Processor transformation overview
- Processing relational output
- Processing hierarchical output
- Processing flattened output
Input transformation
- Input fields
Java transformation
- Defining a Java transformation
- Classpath configuration
- Java transformation fields
- Configuring Java transformation properties
- Developing the Java code
- Compiling the code
  - Viewing the full class code
- Troubleshooting a Java transformation
  - Finding the source of compilation errors
  - Identifying the error type
- Java transformation example
Java transformation API reference
- failSession
- generateRow
- getInRowType
- incrementErrorCount
- invokeJExpression
- isNull
- logError
- logInfo
- setNull
- setOutRowType
Joiner transformation
- Join condition
- Join type
- Advanced properties
- Hierarchical data in advanced mode
- Creating a Joiner transformation
- Joiner transformation example
Labeler transformation
- Labeler transformation configuration
- Labeler transformation field mappings
- Labeler transformation output fields
Lookup transformation
- Lookup object
  - Lookup object properties
    - Multiple match policy restrictions
  - Custom queries
- Lookup condition
- Lookup return fields
- Advanced properties
- Lookup SQL overrides
  - Guidelines for overriding the lookup query
- Lookup source filter
- Dynamic lookup cache
- Persistent lookup cache
  - Rebuilding the lookup cache
- Unconnected lookups
  - Configuring an unconnected Lookup transformation
  - Calling an unconnected lookup from another transformation
- Connected Lookup example
- Dynamic Lookup example
- Unconnected Lookup example
Machine Learning transformation
- Deploying the model as a REST endpoint
- Accessing the machine learning model
- Mapping fields to the request schema
  - Mapping hierarchical fields
  - Request mapping options
- Viewing response fields
- Configuring bulk requests
  - Bulk request options
- Configuring an API proxy
- Troubleshooting
- Error handling
- Machine Learning transformation example
Mapplet transformation
- Mapplet transformation configuration
- Selecting a mapplet
- Mapplet transformation field mappings
- Mapplet parameters
- Mapplet transformation output fields
- Mapplet transformation names
- Synchronizing a mapplet
Normalizer transformation
- Normalized fields
- Normalizer field mapping
  - Normalizer field mapping options
- Advanced properties
- Target configuration for Normalizer transformations
- Normalizer field rule for parameterized sources
- Mapping example with a Normalizer and Aggregator
Output transformation
- Output fields
- Field mapping
Parse transformation
- Parse transformation configuration
- Parse transformation field mappings
- Parse transformation output fields
- Advanced properties
Python transformation
- Install and configure Python
- Python transformation fields
- Active and passive Python transformations
- Resource files
- Developing the Python code
  - Creating Python code snippets
  - Referencing a resource file
- Example: Add an ID column to nonpartitioned data
- Example: Use partitions to find the highest salary
- Example: Operationalize a pre-trained model
Rank transformation
- Ranking string values
- Rank caches
- Defining a Rank transformation
- Rank transformation fields
- Defining rank properties
- Defining rank groups
- Advanced properties
- Hierarchical data in advanced mode
- Rank transformation example
Router transformation
- Working with groups
  - Guidelines for connecting output groups
- Group filter conditions
  - Configuring a group filter condition
- Advanced properties
- Hierarchical data in advanced mode
- Router transformation examples
Rule Specification transformation
- Rule Specification transformation configuration
- Rule Specification transformation field mappings
- Rule Specification transformation output fields
- Advanced properties
Sequence Generator transformation
- Sequence Generator transformation uses
- Sequence Generator output fields
- Sequence Generator properties
  - Disabling incoming fields
- Hierarchical data in advanced mode
- Sequence Generator transformation rules and guidelines
- Sequence Generator transformation example
Sorter transformation
- Sort conditions
- Sorter caches
- Advanced properties
- Hierarchical data in advanced mode
- Sorter transformation example
SQL transformation
- Stored procedure or function processing
- Connected or unconnected SQL transformation for stored procedure processing
- Unconnected SQL transformations
- Query processing
- SQL transformation configuration
Structure Parser transformation
- Processing input from a Hadoop Files source
- Processing input from a flat file source
  - Configuring the flat file source
  - Configuring the Structure Parser transformation to access flat files
- Structure Parser field mapping
- Output fields
- Advanced properties
- Structure Parser transformation configuration
- Rules and guidelines for the Structure Parser transformation
- Structure Parser transformation example
Transaction Control transformation
- Transaction control condition
- Using Transaction Control transformations in mappings
  - Sample transaction control mappings with multiple targets
- Guidelines for using Transaction Control transformations in mappings
- Advanced properties
Union transformation
- Comparison to Joiner transformation
- Planning to use a Union transformation
- Input groups
- Output fields
- Field mappings
- Advanced properties
- Union Transformation example
Velocity transformation
- Velocity transformation input format
  - Source configuration for file sources
- Velocity template
- Testing the template
- Velocity transformation output
  - Target configuration for file targets
- Velocity transformation parsers
- Examples
  - XML conversion example
  - JSON conversion example
Verifier transformation
- Address Reference Data
- Verifier transformation configuration
- Verifier transformation field mappings
  - Understanding input and output mappings
- Verifier transformation output fields
- Advanced properties
Web Services transformation
- Create a Web Services consumer connection
- Define a business service
- Configure the Web Services transformation
- Web Services transformation example
- Configuration for multibyte hierarchical data

Transformations

Back Next

Joiner transformation example

You're a marketing manager for an online retailer, and you want to merge order data with product and customer data from different Amazon S3 sources to understand what customers are purchasing. Use Joiner transformations to join the data from your sources.

You have three source data tables in an Amazon S3 bucket: orders, order_items, and customer_data. The following image shows a mapping that joins the data from these sources:

The Mapping Designer shows a mapping with three Source transformations, two Joiner transformations, and a Target transformation.

The mapping contains the following elements:

Source transformation for orders: The orders data table includes fields for the order number, date, price, and ID of the customer for each online order.
The following table shows a portion of orders:

order_id
order_date
customer_id
order_price

1005
2023-01-20
789
78.25

1006
2023-01-24
268
150.09

1007
2023-02-07
268
30.20
Source transformation for order_items: The order_items data table includes details about the items in each order, including the quantity and price.
The following table shows a portion of order_items:

order_id
item_id
qty
price

1005
5063
2
34.99

1006
2389
3
19.99

1006
5063
1
34.99

1007
9871
2
10.99

In the Source transformation, you rename the field
order_id
to
items_order_id
to avoid a field name conflict when you join order_items with orders.
Source transformation for customer_data: The customer_data table includes fields for information that the customers provide, including their name, date of birth, and phone number.
The following table shows a portion of customer_data:

c_id
c_name
c_dob

789
Kelcy Almeida
1969-07-20

268
Chidi Donalds
1972-12-07
Joiner transformation for orders and order_items: The first Joiner transformation performs a normal join between orders and order_items. The orders Source transformation is the master group and the order_items Source transformation is the detail group so that order information is added to each item ordered.
The Joiner transformation uses the following join condition to match the data by the order ID:
order_id = items_order_id
.
Joiner transformation for customer_data: The second Joiner transformation performs a detail outer join between customer_data and the output from the first Joiner transformation. The transformation uses the customer_data Source transformation as the master group since it is the smaller data set.
The second Joiner transformation uses the following join condition to match the data by the customer ID:
customer_id = c_id
.
Target transformation: The Target transformation writes the data to a new file in Amazon S3. You can configure the incoming fields to exclude the duplicate fields that result from the joins.
The following table shows a portion of the output data:

order_id
order_date
order_price
item_id
qty
price
c_id
c_name
c_dob

1005
2023-01-20
78.25
5063
2
34.99
789
Kelcy Almeida
1969-07-20

1006
2023-01-24
150.09
2389
3
19.99
268
Chidi Donalds
1972-12-07

1006
2023-01-24
150.09
5063
1
34.99
268
Chidi Donalds
1972-12-07

1007
2023-02-07
30.20
9871
2
10.99
268
Chidi Donalds
1972-12-07

order_id	order_date	customer_id	order_price
1005	2023-01-20	789	78.25
1006	2023-01-24	268	150.09
1007	2023-02-07	268	30.20

order_id	item_id	qty	price
1005	5063	2	34.99
1006	2389	3	19.99
1006	5063	1	34.99
1007	9871	2	10.99

c_id	c_name	c_dob
789	Kelcy Almeida	1969-07-20
268	Chidi Donalds	1972-12-07

order_id	order_date	order_price	item_id	qty	price	c_id	c_name	c_dob
1005	2023-01-20	78.25	5063	2	34.99	789	Kelcy Almeida	1969-07-20
1006	2023-01-24	150.09	2389	3	19.99	268	Chidi Donalds	1972-12-07
1006	2023-01-24	150.09	5063	1	34.99	268	Chidi Donalds	1972-12-07
1007	2023-02-07	30.20	9871	2	10.99	268	Chidi Donalds	1972-12-07