Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Access Policy transformation
  6. Aggregator transformation
  7. B2B transformation
  8. Cleanse transformation
  9. Data Masking transformation
  10. Data Services transformation
  11. Deduplicate transformation
  12. Expression transformation
  13. Filter transformation
  14. Hierarchy Builder transformation
  15. Hierarchy Parser transformation
  16. Hierarchy Processor transformation
  17. Input transformation
  18. Java transformation
  19. Java transformation API reference
  20. Joiner transformation
  21. Labeler transformation
  22. Lookup transformation
  23. Machine Learning transformation
  24. Mapplet transformation
  25. Normalizer transformation
  26. Output transformation
  27. Parse transformation
  28. Python transformation
  29. Rank transformation
  30. Router transformation
  31. Rule Specification transformation
  32. Sequence transformation
  33. Sorter transformation
  34. SQL transformation
  35. Structure Parser transformation
  36. Transaction Control transformation
  37. Union transformation
  38. Velocity transformation
  39. Verifier transformation
  40. Web Services transformation

Transformations

Transformations

Joiner transformation example

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

0 COMMENTS

We’d like to hear from you!