Performing Complex Join in Nifi
up vote
1
down vote
favorite
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
apache-nifi
asked Nov 7 at 16:40
Sidd
173
add a comment |
up vote
1
down vote
favorite
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
apache-nifi
asked Nov 7 at 16:40
Sidd
173
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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
apache-nifi
asked Nov 7 at 16:40
Sidd
173
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
apache-nifi
apache-nifi
asked Nov 7 at 16:40
Sidd
173
asked Nov 7 at 16:40
Sidd
173
asked Nov 7 at 16:40
Sidd
173
asked Nov 7 at 16:40
Sidd
173
asked Nov 7 at 16:40
Sidd
173
173
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Nov 7 at 17:03
Shu
3,8612418
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
@Sidd, if you are already haverun_time_business_dateattribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..
– Shu
Nov 8 at 18:42
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
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.
answered Nov 7 at 17:03
Shu
3,8612418
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
@Sidd, if you are already haverun_time_business_dateattribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..
– Shu
Nov 8 at 18:42
add a comment |
up vote
1
down vote
accepted
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.
answered Nov 7 at 17:03
Shu
3,8612418
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
@Sidd, if you are already haverun_time_business_dateattribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..
– Shu
Nov 8 at 18:42
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
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.
answered Nov 7 at 17:03
Shu
3,8612418
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.
answered Nov 7 at 17:03
Shu
3,8612418
answered Nov 7 at 17:03
Shu
3,8612418
answered Nov 7 at 17:03
Shu
3,8612418
answered Nov 7 at 17:03
Shu
3,8612418
3,8612418
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
@Sidd, if you are already haverun_time_business_dateattribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..
– Shu
Nov 8 at 18:42
add a comment |
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
@Sidd, if you are already haverun_time_business_dateattribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..
– Shu
Nov 8 at 18:42
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
Thanks for the response. This worked fine for me. If i had to use a parameterized date in my sql statement, how to pass the value dynamically. For example my where condition looks like "where business_date=$run_time_business_date"
– Sidd
Nov 8 at 18:16
@Sidd, if you are already have
run_time_business_date attribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..– Shu
Nov 8 at 18:42
@Sidd, if you are already have
run_time_business_date attribute associated with the flowfile then you need to use "where business_date=${run_time_business_date}" to get value of the attribute..– Shu
Nov 8 at 18:42
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53193955%2fperforming-complex-join-in-nifi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
