Transformations > Hierarchy Processor transformation > Hierarchy Processor transformation examples
  

Hierarchy Processor transformation examples

You can use the Hierarchy Processor transformation to complete the following tasks:
Read this section for examples of these use cases.
For more examples, you can watch the following videos on YouTube:

Hierarchical to relational example

You want to flatten hierarchical data and write the data to target files in relational and delimited formats.
A customer order file contains the current customer contact information and the recent orders for those customers. The order file is in hierarchical JSON format and is generated by your company's cloud application.
Using the order file data, you want to create a relational customers table to use for an update on the customer information in the master database. Separately, you want to analyze the orders that have been increasing. You can use the order file to create a separate delimited orders file for the analysis.
Use a Hierarchy Processor transformation in a mapping to transform the data from the hierarchical input to relational and delimited output.
To create and run the mapping, perform the following tasks:
  1. 1. Ensure that you have access to an Amazon S3 V2 Connector for the S3 source and target objects.
  2. 2. Add a Source transformation that reads hierarchical data from the source JSON file.
  3. 3. Configure the following properties for the source object:
  4. Property
    Value
    Connection
    Amazon S3 V2
    Source Format
    JSON
  5. 4. Add a Hierarchy Processor transformation.
  6. The following image shows the data flow:
    The mapping contains a Source transformation that is connected to a Hierarchy Processor transformation that is connected to two Target transformations.
  7. 5. In the Hierarchy Processor transformation, create the OutputCustomers output group to create the relational customers data file.
  8. The following image shows how to add incoming fields, which will create the output group:
    The Hierarchy Processor tab contains a list of incoming fields on the left and output fields on the right. The Add Field dialog box is shown on the right.
  9. 6. Create the OutputOrders output group to create the delimited orders data file.
  10. 7. In the Hierarchy Processor tab, map Incoming Fields to Output Fields.
  11. You can add fields individually or use the following options for struct and array fields:
  12. 8. Add a Target transformation to write the customers data output.
  13. 9. Configure the following properties for the target object:
  14. Property
    Value
    Connection
    Amazon S3 V2
    Formatting Option
    Relational
  15. 10. Add a Target transformation to write the orders data output.
  16. 11. Configure the following properties for the target object:
  17. Property
    Value
    Connection
    Amazon S3 V2
    Formatting Option
    Delimited
  18. 12. Link the OutputCustomers output group to the TargetCustomers Target transformation.
  19. 13. Link the OutputOrders output group to the TargetOrders Target transformation.
  20. 14. Run the mapping.

Relational to hierarchical example

You need to create a purchase order file in hierarchical format using customer sales data from two purchase order tables and the customer address table.
Use the Hierarchy Processor transformation to create purchase orders in hierarchical format.
You will perform the following high-level tasks to create and configure the target file:
The POHeader table contains basic information about the orders placed by customers:
OrderNumber
Comment
OrderDate
ConfirmDate
1
AppD for POD4
2020-10-01 00:00:00.0
2020-10-02 00:00:00.0
2
GoJS for IICS
2020-10-12 00:00:00.0
2020-10-12 00:00:00.0
The Address table contains customer address information for each order:
OrderNumber
AddressType
Name
Street
City
State
Country
Zip
1
ShipTo
Tom
2100 Seaport Blvd
Redwood City
CA
USA
94063
1
BillTo
Tom
2100 Seaport Blvd
Redwood City
CA
USA
94063
2
ShipTo
Bill
1630 S Delaware St
San Mateo
CA
USA
94402
2
BillTo
Bill
PO Box 313
San Mateo
CA
USA
94402
The PODetail table contains details about the customer purchase orders:
OrderNumber
ItemNum
ProductName
Quantity
Price
Comment
ShipDate
PartNum
1
1
AppD Agent for JVM
60
500
JVM agents
2020-10-15 00:00:00.0
1
1
3
ELB agents
10
200
ELB agents
2020-10-15 00:00:00.0
3
1
2
MySQL agents
2
120
MySQL agents
2020-10-16 00:00:00.0
2
1
4
MySQL agents
2
120
MySQL agents
2020-10-01 00:00:00.0
2
1
5
MySQL agents
2
120
MySQL agents
2020-10-01 00:00:00.0
2
2
1
GOJS OEM Edition
2
20000
GOJS Dev
2020-10-19 00:00:00.0
101
2
2
GOJS Professional Service
5
5000
GOJS Dev
2020-10-19 00:00:00.0
102
To use the Hierarchy Processor transformation to create purchase orders in hierarchical format, perform the following tasks:
Design the mapping.
Use the Mapping Designer to perform the following steps:
  1. 1. Add a Hierarchy Processor transformation and change the output data format to Hierarchical.
  2. 2. Add the POHeader, PODetail, and Address tables as source objects.
  3. 3. Connect the source objects to the Hierarchy Processor transformation in the data flow.
  4. 4. In the Hierarchy Processor transformation, add the PurchaseOrder output group and connect the target object in the data flow.
The following image shows the mapping:
The mapping contains three Source transformations that are connected to a Hierarchy Processor transformation that is connected to one Target transformation.
Build the output group and create a struct.
Use the following steps to create the output group with the basic purchase order data and add the ship-to address.
  1. 1. Add all the incoming fields from POHeader to the PurchaseOrder output group.
  2. 2. Add a new output field with the following properties:
  3. Property
    Value
    Child Of
    PurchaseOrder
    Name
    shipToAddress
    Type
    struct
    Struct Name
    address_struct
  4. 3. Add all the incoming fields from Address to the shipToAddress struct in the output group:
  5. The list of incoming fields on the left includes the Address table. The output group on the right contains the empty shipToAddress struct.
  6. 4. Delete the following fields that you do not need in the output group:
  7. 5. Add a filter condition for the PurchaseOrder.shipToAddress struct: :fld.{Address.OrderNumber}=:fld.{PurchaseOrder.OrderNumber} AND :fld.{Address.AddressType}='ShipTo'.
Create an array of structs.
Use the following steps to add the purchase order details in the items array of structs. Configure the data processing strategies to sort by item number, group by part number, and aggregate the incoming quantity and price.
  1. 1. Add a new output field with the following properties:
  2. Property
    Value
    Child Of
    PurchaseOrder
    Name
    Items_arr
    Type
    array
    Array Element Type
    struct
    Element Struct Name
    item_str
  3. 2. Add all the incoming fields from PODetail to the Items_arr array in the output group.
  4. 3. Delete the following field that you do not need in the output group: PurchaseOrder.Items_arr.OrderNumber.
  5. 4. Add a filter condition for the PurchaseOrder.Items_arr array: :fld.{PODetail.OrderNumber}=:fld.{PurchaseOrder.OrderNumber}.
  6. 5. Configure a group by field for the PurchaseOrder.Items_arr array: PODetail.PartNum.
  7. 6. Configure an order by field in ascending order for the PurchaseOrder.Items_arr array: PODetail.ItemNum.
  8. 7. Update the field expression for PODetail.Quantity in the PurchaseOrder.Items_arr array: SUM(:fld.{PODetail.Quantity}) to aggregate quantity.
  9. 8. Update the field expression for PODetail.Price in the PurchaseOrder.Items_arr array: SUM(:fld.{PODetail.Price}) to aggregate price.
The following image shows the data configuration icons and expressions for the Items_arr array in the output group.
The Items_arr output group on the right displays active icons for data sources, filter, group by, and order by. The Quantity and Price fields show the aggregate expressions.
Aggregate the output data.
Use the following steps to aggregate all the items in a particular purchase order, providing the total price.
  1. 1. Add a new output field with the following properties:
  2. Property
    Value
    Child Of
    PurchaseOrder
    Name
    TotalPrice
    Type
    bigint
    Aggregate Options: This field will aggregate values in an output field array
    Enabled
    Output Field
    Items_arr
    The following image shows the aggregate options for the TotalPrice output field:
    Aggregate Options indicates that the Items_arr array output values will be used to aggregate into TotalPrice.
  3. 2. Configure a field expression for PurchaseOrder.TotalPrice: SUM(:fld.{PurchaseOrder.Items_arr.item_str.Quantity}*:fld.{PurchaseOrder.Items_arr.item_str.Price}) to aggregate the total price.
Create an array of structs and join data sources.
Use the following steps to add and configure the same-day items array of structs. Using a filter, a join, and a field expression, you output only the items that were ordered and shipped on the same date.
  1. 1. Add a new output field with the following properties:
  2. Property
    Value
    Child Of
    PurchaseOrder
    Name
    SameDayItems
    Type
    array
    Array Element Type
    struct
    Element Struct Name
    sameday_str
  3. 2. Add all the incoming fields from PODetail to the SameDayItems array in the output group.
  4. 3. Delete the following field that you do not need in the output group: PurchaseOrder.SameDayItems.OrderNumber.
  5. 4. Add POHeader as a data source for PurchaseOrder.SameDayItems array.
  6. 5. Add a join condition for the PurchaseOrder.SameDayItems array with the following properties:
  7. Property
    Value
    Left Group
    POHeader
    Join Type
    Inner
    Right Group
    PODetail
    Join Condition
    :fld.{POHeader.OrderDate}=:fld.{PODetail.ShipDate} AND :fld.{POHeader.OrderNumber}=:fld.{PODetail.OrderNumber}
    The following image shows the data sources and join condition for SameDayItems:
    The Data Configuration dialog shows the selected data sources: POHeader and PODetail. The join condition shows Left Group: POHeader, Join Type: Inner, Right Group: PODetail.
  8. 6. Add a filter condition for the PurchaseOrder.SameDayItems array: :fld.{PODetail.OrderNumber}=:fld.{PurchaseOrder.OrderNumber}.
Run the mapping.
  1. 1. Create a mapping task.
  2. 2. Run the mapping task.
  3. 3. Review your output.
The following JSON shows the PurchaseOrder target output after you run the mapping:
{
"OrderNumber": "1",
"Comment": "AppD for POD4",
"OrderDate": "2018-10-01 00:00:00.0",
"ConfirmDate": "2018-10-02 00:00:00.0",
"address_struct": {
"Name": "Tom",
"Street": "2100 Seaport blvd",
"City": "Redwood City",
"State": "CA",
"Country": "USA",
"Zip": "94063"
},
"Items_arr": [{
"itemNum": "1",
"ProductName": "AppD Agent for JVM",
"Quantity": 60,
"price": 500,
"comment": "JVM agents",
"shipDate": "2018-10-15 00:00:00.0",
"PartNum": "1"
}, {
"itemNum": "2",
"ProductName": "MySQL agents",
"Quantity": 6,
"price": 360,
"comment": "MySQL agents",
"shipDate": "2018-10-15 00:00:00.0",
"PartNum": "2"
}, {
"itemNum": "3",
"ProductName": "ELB agents",
"Quantity": 10,
"price": 200,
"comment": "ELB agents",
"shipDate": "2018-10-16 00:00:00.0",
"PartNum": "3"
}],
"TotalPrice": 34160
} {
"OrderNumber": "2",
"Comment": "GoJS for IICS",
"OrderDate": "2018-10-12 00:00:00.0",
"ConfirmDate": "2018-10-12 00:00:00.0",
"address_struct": {
"Name": "Bill",
"Street": "23rd Ave",
"City": "San Mateo",
"State": "CA",
"Country": "USA",
"Zip": "94401"
},
"Items_arr": [{
"itemNum": "1",
"ProductName": "GOJS OEM Edition",
"Quantity": 2,
"price": 20000,
"comment": "GOJS Dev",
"shipDate": "2018-10-19 00:00:00.0",
"PartNum": "101"
}, {
"itemNum": "2",
"ProductName": "GOJS Prefessional Service",
"Quantity": 5,
"price": 5000,
"comment": "GOJS Dev",
"shipDate": "2018-10-19 00:00:00.0",
"PartNum": "102"
}],
"TotalPrice": 65000
}

Hierarchical to hierarchical example

You want to create a customer order file in hierarchical format using an existing file of hierarchical data.
The existing customer order file, CompanyOrders, contains the names of companies who have placed orders and information about each order, including the price, date, shipping address, and ID numbers of ordered items. The following image shows the structure of the CompanyOrders file: The CompanyOrders file contains a CompanyName string and Orders array of structs. The Orders struct contains OrderPrice, OrderDate, Street, CIty, State, Country, ZipCode, and Items array of structs. The Items struct contains ItemId, ItemName, ItemPrice, and ItemQuantity.
You want to restructure the shipping address into a struct and add a field to calculate the total price of all orders for each company.
You will perform the following tasks to create and configure the target file:
  1. 1. Design the mapping.
  2. 2. Configure the output group.
  3. 3. Create an output field to aggregate the total price.
  4. 4. Create an output struct for the order address.
  5. 5. Run the mapping.
Design the mapping.
Use the Mapping Designer to perform the following steps:
  1. 1. Add the CompanyOrders file as a source object.
  2. 2. Add a Hierarchy Processor transformation to the mapping and connect CompanyOrders as an input source.
  3. 3. In the Hierarchy Processor transformation, set the Data Format to Hierarchical.
  4. An output group is created.
  5. 4. Add a Target transformation to the mapping, and connect the Hierarchy Processor transformation output to this target object.
The following image shows the mapping: A mapping in the Mapping Designer shows the CompanyOrders source connected to the Hierarchy Processor transformation input. The Hierarchy processor transformation is connected to the target transformation.
Configure the output group.
In the Hierarchy Processor transformation, perform the following steps:
  1. 1. Add all the incoming fields from the input to the output group. Set Add to Preserve incoming field.
  2. The following image shows the Add Field dialog: The "Add Field" dialog box shows the incoming field set to Input, Add set to Preserve incoming field, and Output Group or Field set to Output.
  3. 2. Verify that the data source for the output group is set to Input.
Create an output field to aggregate the total price.
Perform the following steps to create a field that calculates the total price of all orders for each company:
  1. 1. Add a new output field with the following properties:
  2. Property
    Value
    Child Of
    Output
    Name
    TotalOrdersPrice
    Type
    double
    Aggregate Options
    Enabled
    Output Field
    Orders
    The following image shows the aggregate options for the TotalOrdersPrice output field:The "New output field" dialog box shows the properties for the TotalOrdersPrice field. The Aggregate Options are highlighted where the "use this field to aggregate values in an output field array" checkbox is selected and the Output Field is set to Orders.
  3. 2. Configure the following field expression for Output.TotalOrdersPrice to aggregate the total price of all orders for a company:
  4. SUM(:fld.{Output.Orders.Orders.OrderPrice})
Create an output struct for the order address.
Perform the following steps to structure the order address in the output:
  1. 1. Add a new output field with the following properties:
  2. Property
    Value
    Child Of
    Output.Orders.Orders
    Name
    OrderAddress
    Type
    struct
    Struct Name
    Address
  3. 2. Add all the incoming fields from Orders to OrderAddress. Set Add to Add primitive single occurring children.
  4. 3. Set the data source for OrderAddress to Use Output.
  5. 4. Delete the following fields from the OrderAddress struct that you do not need:
  6. 5. Delete the following fields from the Orders output group that you do not need:
The following image shows the OrderAddress struct: The Hierarchy Processor transformation output fields shows the final hierarchical structure of the output. The OrderAddress struct is highlighted, where OrderAddress is a struct that contains the string values Street, City, State, Country, and ZipCode.
Run the mapping.