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:

LookUpTemplate

For example, consider that there are two tables,

Source table: lookup_source

id	Name	age	emailid	phone
8	Carla Adams	24	[e-mail]	107-555-0138
12	James Aguilar	40	[e-mail]	1 (11) 500 555-0198
13	Robert Ahlering	40	[e-mail]	678-555-0175
15	Kim Akers	22	[e-mail]	440-555-0166
16	Lili Alameda	30	[e-mail]	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	[e-mail]	107-555-0138	5/16/2005 16:33
12	James Aguilar	40	[e-mail]	1 (11) 500 555-0198	8/1/2003 0:00
13	Robert Ahlering	40	[e-mail]	678-555-0175	9/1/2003 0:00
15	Kim Akers	22	[e-mail]	440-555-0166	9/1/2001 0:00
16	Lili Alameda	30	[e-mail]	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.