Sample transformation walkthrough using the Lookup field

Pentaho Data Integration

Version

9.3.x

Audience

anonymous

Part Number

MK-95PDIA003-15

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.

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"}
        ]
        }
}

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"}}

Create a transformation with a CSV File Input step and a hop from the CSV Input step to the Avro Input step.
Configure the CSV File Input step as shown below, where the file name is the path to the simpleexample.csv file on your system:
Note: Make sure that the delimiter is the pipe character.
Configure the Avro File Input step tabs as shown below, where the schema is the path to the atm.schema file on your system:
Click Get fields to populate the Avro fields table. Enter the Indexed values field as shown below:
Note: Make sure to select the Pass through fields from previous step option.
Enter the following values in the Lookup fields tab:
Click Preview to view the data.
You should see results similar to the results shown below:
Save your transformation.