Sample transformation walkthrough using the Lookup field

The following example transformation demonstrates how to use the Lookup field. The transformation processes a CSV file and feeds its data into the Avro Input step. The Avro Input step decodes the Avro structure using a lookup field consisting of an atm_id variable mapped to an atm field.

1. Save the following code block in a text file as atm.schema.

   {
     "type": "map",
           "values":{
           "type": "record",
           "name":"ATM",
           "fields": [
                     {"name": "serial_no", "type": "string"},
                     {"name": "location", "type": "string"}
           ]
           }
   }

2. Save the following code block in a text file as simpleexample.csv:

   atm|atms
   atm1|{"atm1": {"serial_no": "zxy555", "location": "Uptown"}, "atm2": {"serial_no": "vvv242", "location": "Downtown"}, "atm4": {"serial_no": "zzz111", "location": "Central"}, "atm6": {"serial_no": "piu786", "location": "Eastside"}, "atm10": {"serial_no": "hbc999", "location": "Westside"}, "atm20": {"serial_no": "mmm456", "location": "Lunar city"}}
   atm2|{"atm1": {"serial_no": "zxy555", "location": "Uptown"}, "atm2": {"serial_no": "vvv242", "location": "Downtown"}, "atm4": {"serial_no": "zzz111", "location": "Central"}, "atm6": {"serial_no": "piu786", "location": "Eastside"}, "atm10": {"serial_no": "hbc999", "location": "Westside"}, "atm20": {"serial_no": "mmm456", "location": "Lunar city"}}
   atm4|{"atm1": {"serial_no": "zxy555", "location": "Uptown"}, "atm2": {"serial_no": "vvv242", "location": "Downtown"}, "atm4": {"serial_no": "zzz111", "location": "Central"}, "atm6": {"serial_no": "piu786", "location": "Eastside"}, "atm10": {"serial_no": "hbc999", "location": "Westside"}, "atm20": {"serial_no": "mmm456", "location": "Lunar city"}}

3. Create a transformation with a CSV File Input step and a hop from the CSV Input step to the Avro Input step.

   

   Avro Input Sample CSV Transform
4. Configure the CSV File Input step as shown below, where the file name is the path to the simpleexample.csv file on your system:

   

   Avro Input Sample CSV File Input

   Note: Make sure that the delimiter is the pipe character.

5. Configure the Avro File Input step tabs as shown below, where the schema is the path to the atm.schema file on your system:

   

   Avro Input Sample Source Config
6. Click Get fields to populate the Avro fields table. Enter the Indexed valuesfield as shown below:

   

   Avro Input Sample Avro Fields Config

   Note: Make sure to select the Pass through fields from previous step option.

7. Enter the following values in the Lookup fields tab:

   

   Avro Input Sample Lookup Fields Config
8. Click Preview to view the data.

   You should see results similar to the results shown below:

   

   Avro Input Sample Preview Data
9. Save your transformation.