Performing Complex Join in Nifi

Question

I am very new to Nifi and trying to perform a SQL against SQL server which has multiple tables being joined and also selecting attributes from each one. How can we perform this in Nifi. I am seeing "Generate Table Fetch" which allows me to mention "Table Name" and its "Where Clause". Not sure, how do we perform complex join if there are multiple tables involved.

Example-

Select 
a.col1,
a.col2,
b.col3,
b.col4,
c.col5,
c.col6
from a left outer join b
on a.colx=b.colx
inner join c
on a.coly = c.coly
where exists (some condition etc)

Thanks, Siddhartha

Example-

Select 
a.col1,
a.col2,
b.col3,
b.col4,
c.col5,
c.col6
from a left outer join b
on a.colx=b.colx
inner join c
on a.coly = c.coly
where exists (some condition etc)

Thanks, Siddhartha

user_7632695 · Answer

Use ExecuteSQL/ExecuteSQLRecord(NiFi-1.8+) processor and keep your query in SQL select query property.

Configure/Enable the dbcp connection pool.

Keep in mind ExecuteSQL processor doesn't store the state:

So if you want to run the processor incrementally then you need to store the state in NiFi (or) externally then pull the state value every time and execute the your query.
Refer to this link for more details regards to storing/fetching state using NiFi.

Performing Complex Join in Nifi

1 Answers