How to lookup between SQL data?
Using Lookup Record Processor, a specific field can be extracted from a record and looks up value for those fields in a LookupService.
If the result is returned by the LookupService, then that result is optionally added to the record using Result RecordPath property in the Lookup record processor.
Use Lookup record processor to map SQL records as follows:
Sample:
For example, consider that there are two tables,
Source table: lookup_source
id |
Name |
age |
emailid |
phone |
8 | Carla Adams | 24 | 107-555-0138 | |
12 | James Aguilar | 40 | 1 (11) 500 555-0198 | |
13 | Robert Ahlering | 40 | 678-555-0175 | |
15 | Kim Akers | 22 | 440-555-0166 | |
16 | Lili Alameda | 30 | 1 (11) 500 555-0150 |
Lookup table: lookup_table
id |
purchasedate |
8 | 5/16/2005 16:33 |
12 | 8/1/2003 0:00 |
13 | 9/1/2003 0:00 |
15 | 9/1/2001 0:00 |
16 | 9/1/2002 0:00 |
And the table to be used as target is,
Target table: lookup_target
Id |
Name |
age |
emailid |
phone |
purchasedate |
8 | Carla Adams | 24 | 107-555-0138 | 5/16/2005 16:33 | |
12 | James Aguilar | 40 | 1 (11) 500 555-0198 | 8/1/2003 0:00 | |
13 | Robert Ahlering | 40 | 678-555-0175 | 9/1/2003 0:00 | |
15 | Kim Akers | 22 | 440-555-0166 | 9/1/2001 0:00 | |
16 | Lili Alameda | 30 | 1 (11) 500 555-0150 | 9/1/2002 0:00 |
Now, you need to merge those two tables based on Id like the following screenshot,
Overview
Step 1: Drag and drop ExecuteSQL processor into the canvas. This processor is used to get data from the lookup_source table. Double click the processor and configure as follows,
Step 2: Configure MicrosoftSQLServer controller service with the required details, used to obtain connection from source table.
Step 3: Add UpdateAttribute processor and configure as below,
Here table schema is assigned to schema.name property.
Step 4: Add LookupRecord processor and configure as follows,
It is used to extract a record from incoming SQL data and lookup key value (ID here) with SQLLookupService controller and return the result as flowfile.
The result type is based on the Record Writer property, here SQLRecordSetWriter is used to write the results into lookup_target table.
A property named key should be added for mentioning the column to be used as lookup key in lookup record processor.
Step 4: Configure AvroSchemaRegistry controller service, used to add schema for target table.
Create a property named table-schema and add value as,
{ "type" : "record", "name" : "record", "fields" : [ { "name" : "id", "type" : "long", "doc" : "Type inferred from '8'" }, { "name" : "name", "type" : "string" }, { "name" : "age", "type" : "long" }, { "name" : "emailid", "type" : "string" }, { "name" : "phone", "type" : "string" }, { "name" : "purchasedate", "type" : "string" } ] } |
Step 5: Configure AvroReader controller service, used to read avro data retrieved from ExecuteSQL processor.
Step 6: Configure SQLLookupService controller service, used to lookup source data retrieved with lookup table and return mapped lookup key columns from lookup table.
Database connection service: Used to obtain connection for lookup table.
Table name: Name of the table to be used as lookup table.
Lookup Value Columns: The columns to be retrieved from lookup table.
Lookup Key Column: The column to be used as lookup key for mapping with source data.
Step 7: Configure SQLRecordSetWriter controller service, used to write the data retrieved as SQL queries.
Table Name: Mention the table name to be used as target table.
Schema Registry: AvroSchemaRegistry controller service specifies the schema for the output data.