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

asked Nov 7 at 16:40

Sidd

173

add a comment |

up vote
1
down vote

favorite

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

asked Nov 7 at 16:40

Sidd

173

add a comment |

up vote
1
down vote

favorite

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

asked Nov 7 at 16:40

Sidd

173

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

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

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 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 |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

1 Answer
1

active

oldest

votes

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 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 |

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 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 |

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

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

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 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

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

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Wsrtjtyk