Using multiple tables in nifi querydatabasetable custom query

[#1]

Hi, 

I am using a QueryDatabase Table processor to get data from Oracle database to transfer data.

I use custom query like below.

Both table_a and table_b has incremental columns that I can use for maximum-value columns.

My question is, can we track changes from both tables in a single  QueryDatabase processor.

When any column from table_a or table_b changes, I want to transfer below query result to another Oracle database.

Select a.column_1, a.column_2, a.column_3, b.column_1, b.column_2, b.column_3

from table_a a table_b b

where a.id=b.id

Thanks

[#2]

If you have access to the Oracle Db, create a View for your query on that DB. Then use the View name as Db name inside QueryDatabaseTable. 

NOTE: If you have complex query, do use View instead.

I hope this helps.

[#3]

Hi,

The example query you have shown would work id you add a JOIN statement. This would allow you to select all the records where either of the incremental value columns has changed. For example:

select a.column_1, a.column_2, a.column_3, b.column_1, b.column_2, b.column_3

from table_a a 

join table_b b on a.id = b.id

where a.id > 2 or b.id > 1

The trouble with this is you're not really tracking changes and you risk getting duplicate records unless both table_a and table_b are updated at the same time. If they are updated simultaneously then it ought to be sufficient to track the changes in only one table.

If you really want to track changes to tables in a relational database you might want to consider using the CDC processor. There was a series of tutorial posts previously published on how to do this.

https://community.cloudera.com/t5/Community-Articles/Change-Data-Capture-CDC-with-Apache-NiFi-Part-1...

Cheers,

Jim

[#4]

Thanks for your reply,

I checked CDC processor but it is specific to MySQL db. My source db is Oracle.

ID is not the incremental column in the tables. There is a modifiedtime column in both table_a and table_b that shows the updated time of the row. For now I used modifiedtime column in table_a as Maximum-value Columns in QueryDatabase processor, but when modifiedtime column updated on table_b (eg. column_1 table value updated in table_b) I can't track the change in this table. Should I use another QueryDatabase processor and set modifiedtime column in table_b as Maximum-value Columns?