Using a map in a Data Integration Job

Create a JSON structure based on a sample document to use as input for your mapping.

1. Expand the Hierarchical Mapper node and right-click Structure, then click New > Structure.
2. In the wizard that opens, select Import a structure definition and click Next.
3. Select JSON Sample Document and click Next.
4. Select the file to use:
   • Select an existing resource in your workspace.
   • Import a local file.
   • Enter the URL to the file.
5. Click Next.
6. Select a folder and enter a name for your structure, then click Next.
7. Click Finish.

Results

Your structure is created and opens in the editor. In the Document tab, you can see your sample data. The different elements are highlighted when you click them in the structure.

Create a simple Job with a tFileInputRaw, a tHMap and a tFileOutputDelimited.

1. In the Integration perspective, right-click the Job Designs node and click Create Standard Job.
2. Enter a name, purpose and description for your Job, then click Finish.
3. Add the following components to your design workspace and link them with Row > Main connections:
   • A tFileInputRaw
   • A tHMap
   • A tFileOutputDelimited
   Your Job should look like this:

   

4. Double-click the tFileInputRaw to open its properties and click the ... button to the left of the Filename field to select your input JSON file.
5. Double-click the tFileOutputDelimited and enter the path to the output file in the File Name field.
6. Optional: Edit other properties as needed.
7. Click the ... button to the right of Edit schema to create the schema for your output file.
8. In the Input schema, click the + button to add the following columns:
   • firstName
   • lastName
   • age
   • streetAddress
   • city
   • state
   • postalCode
   • homePhoneNumber
   • mobilePhoneNumber
   • workPhoneNumber
9. Click the button to copy the columns to the Output schema, then click OK.

   

10. Click OK to close the schema editor.
11. Double-click the tHMap and follow the wizard to generate the map.
    1. In the first step, select Select an existing hierarchical mapper structure and click Next.
    2. Select the structure you created in Creating the input structure for your Data Integration Job and click Next, then click Next again.
    3. In the second step, select Generate hierarchical mapper structure based on the schema and click Next.
    4. Click Finish.

Configure the map generated in the previous procedure.

1. Drag the input customers element on the output row element.

   The firstName, lastName and age elements are automatically mapped, and a SimpleLoop function with a reference to the input customers element is added to the Loop tab of the output row element.

   This looping expression indicates that the map should produce one row in the output for each iteration of the customers element in the input. For more information, see Loop expressions.

   

2. Drag and drop the input streetAddress, city, state and postalCode elements on the corresponding output elements.
3. Drag and drop an AgConcat expression on the output homePhoneNumber element.
4. Drag the input number element on the AgConcat function.
   This is required because there are several number elements for each customers, and you need to create a single output from them.
   A looping expression using the input phoneNumber loop is generated.
5. Drag and drop an Equal function on the Filter argument of the SimpleLoop function.
6. Drag the input type element on the First Value argument.
7. Drag and drop a Constant function on the Second Value argument, then double-click it, enter home in the Value field and click OK.
   This filter allows you to specify that the homePhoneNumber element should return the value of the input number element in the iterations where the value of type is equal to home.

   

8. Right-click the AgConcat function and click Copy to copy the entire expression.
9. Right-click the output mobilePhoneNumber and click Paste.
   The AgConcat expression is copied to the Value tab of the mobilePhoneNumber element.
10. Right-click AgConcat and click Expand All to see the whole expression, then double-click the Constant function and change the value to mobile.
11. Repeat the previous steps to copy the AgConcat expression to the output workPhoneNumber element and change the value of the Constant expression to work.
12. Save your map.

Results

Your elements are now mapped.

You can use the Test Run option to test the output, and go back to your Job to run it. The output should look like this with the sample input data:

John,Smith,25,21 2nd Street,New York,NY,10021,202-555-0109,202-555-0181,
Jane,Doe,31,19 4th Street,New York,NY,10021,202-555-0178,202-555-0123,202-555-0163