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?
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.