Get data from two different databases and merge them

Question

I have a task in Apache NiFi

Table 1

ID date client_name attr_client

Table 2

ID date address city active

Table 3 (result table)

ID date client_name attr_client address city

I need to make a query to one database (one connection), then a query to another database (second connection) and combine data (on id and date field) into one file and save it to db. Is this possible?

I know about ExecuteSQL processor and can extract data, but I also should wait two files and then join them. Also I read about LookupRecord but it can't join data on two or more columns?

• daggett: i would take table1 and execute inserts into a target db, then table2 and execute updates against the target db, then do the same with table3. of cause some fields should be nullable in your target db or defaulted to some values. or do all that against the staging table and then after you inserted all data into a staging table - as a last step move it from staging into a real table.

Answer 1

Take a look at LookupRecord and DatabaseRecordLookupService, you can use the latter on a single column (like id) to enrich the data (like a join on a single column). If you need to filter after that you can use QueryRecord to match the date with a SQL query, or perhaps first another LookupRecord for date then the QueryRecord to filter the results as needed.