question Using multiple tables in nifi querydatabasetable custom query in Support Questions

Using multiple tables in nifi querydatabasetable custom query

data_tr — Tue, 06 Sep 2022 17:02:00 GMT

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

Re: Using multiple tables in nifi querydatabasetable custom query

rafy — Tue, 06 Sep 2022 20:20:04 GMT

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.

Re: Using multiple tables in nifi querydatabasetable custom query

JimHalfpenny — Tue, 06 Sep 2022 20:43:49 GMT

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-of-3/ta-p/246623

Cheers,

Jim

Re: Using multiple tables in nifi querydatabasetable custom query

data_tr — Thu, 08 Sep 2022 11:08:26 GMT

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?