Skip to main content
data_tr
data_tr
Silver ⭐️
September 6, 2022
Question

Using multiple tables in nifi querydatabasetable custom query

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

 

 

3 replies

rafy
rafy
Gold ⭐️
September 6, 2022

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.

Translate
    JimHalfpenny
    JimHalfpenny
    Silver ⭐️⭐️⭐️
    September 6, 2022

    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

    Translate
      data_tr
      data_trAuthor
      Silver ⭐️
      September 8, 2022

      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?

      Translate
      Terms & ConditionsAccessibility statement