nifi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From A_Nad <anadka...@seic.com>
Subject Re: Database Session AutoCommit in PUTSQL (Nifi 1.9.1) not working
Date Thu, 28 Feb 2019 09:54:57 GMT
Hi Denes,

Thanks for your inputs. Yes, I observed what you are mentioning. Flowfiles
get committed to oracle database if Batch size is set to 1 irrespective of
the Autocommit property being false.

*Use case scenario* - I have a parent table(incremental parent_id) and a
child table(parent_id referencing the parent table) on oracle database. My
use case is that I need to load data into both parent and child table
together so that when i run the below validation query, I always get the
ora_rowscn value to match for both parent and child tables. 

I verified this by loading 2 test tables using PUTSQL processor. I created
two source test tables(id,name) and inserted same 10 records into both the
source tables and made them load into another set of 2 target test
tables(id,name). To load the 2 target tables together in one PUTSQL, i
introduced the funnel just before PUTSQL so that I can use one PUTSQL with
batchsize of 2 and Database Autocommit property set as 'false'.
My flow layout is 
*Pipeline1*:-GenerateTableFetch(table t1,partition size 10)
->ReplaceText(creates insert statement)->UpdateAttribute(adds 'priority'
attribute as 1 to the flowfile)
                                                                                         
                 
v
                                                                                         
                 
v
                                                                       
*Funnel*(PriorityAttributePrioritizer) -->PutSQL
                                                                                         
                 
^
                                                                                         
                 
^
*Pipeline2*:-GenerateTableFetch(table t2,partition size 10)
->ReplaceText(creates insert statement) ->UpdateAttribute(Added 'priority'
attribute as 2 to the flowfile)

I tested this flow multiple times.
*Result *- Data gets loaded into both target1 an target2 tables every time.
However, when I run the below query with ora_rowscn to check the commit
boundary on the 2 tables

/select a.idd,b.idd,a.ora_rowscn,b.ora_rowscn,case when a.ora_rowscn =
b.ora_rowscn then 'matches' else 'doomed' end as flg
from test_table_tgt a, test_table_tgt1 b
where a.idd = b.idd(+)/

I find that out 10 times that i run the dataflow, 6 times the ora_rowscn in
both the target table matches, but rest it does not match.

Ideal result should have been that all 10 times the ora_rowscn values in
both the tables should have matched. But this is not the case. Does this
mean that there is a bug in PUTSQL and that even after setting the database
autocommit property to false, data does not commit as expected? Please let
me know if you see some thing odd in my test and how can i achieve my use
case scenario

Thanks,
A_Nad




--
Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/

Mime
View raw message