You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by obaidul karim <ob...@gmail.com> on 2016/01/23 06:04:03 UTC
JDBC External Table File Target
Hi,
I developing a nifi processor for netezza.
As per by experience the best way to load data to netezza is using netezza
JDBC external table.
I want to run below command to load a file within NiFi processor.
insert into MYTABLE
select * FROM EXTERNAL '<local file system filename>'
USING (<JDBC Options>) ;
My question is which file to usefor <local file system filename> ?
- Is it the flow files ? If yes then how can I get full path of a flowfile ?
- or can I directly load the file in a spool directory ?
Thanks for your help in advance.
-Obaid
Re: JDBC External Table File Target
Posted by obaidul karim <ob...@gmail.com>.
Hi All,
Seems this mail is dead.
However, as the main issue has been resolved(JDBC external table load), I
can say the issue is resolved.
I would consider Matthew's response as solution.
I am initiating another mail for my other issue (synchronous run of all
processes.)
Thanks all :).
-Obaid
On Mon, Feb 1, 2016 at 1:21 AM, obaidul karim <ob...@gmail.com> wrote:
> Hi Matthew ,
>
> I didn't read your reply(just now reading :)), however, I have implemented
> in the same way as you described :)).
>
> In fact, I followed steps in FetchFile processor to read files directly
> from an input list generated by ListFile processor.
> My flow looks like below:
>
> Flow: An Input Folder > [1]ListFile(Standard Processor) > [2]NzLoad(My
> Custom processor) > [3]NzSql (My Custom processor)
>
> Now need your help on below:
> 1. As I am reading from ListFile, it should be thread safe (a single file
> should not be processed by more than 1 threads on NzLoad), right ?
> 2. How can I make the full flow synchronous. I want it like below:
> a. [1]ListFile should list <=N files for each run and then stop until [2]
> & [3]c completes
> b. [2]NzLoad will process all the <=N files(using multiple thread) and
> only then then trigger [3]NzSql
> c. [3]NzSql will complete it processing and then trigger [1]ListFile and
> so on.
>
> Please share me if you have any alternative idea.
>
> Regards,
> Obaid
>
> On Fri, Jan 29, 2016 at 4:22 AM, Matthew Burgess <ma...@gmail.com>
> wrote:
>
>> I think an approach that could work is the following:
>>
>> 1) Start with a ListFile processor to locate the filenames of the files
>> you want to load into netezza
>>
>> 2) Send to a ReplaceText processor, where you replace the entire contents
>> of the flow file with:
>> insert into MYTABLE
>> select * FROM EXTERNAL '${filename}'
>> USING (<JDBC Options>) ;
>>
>>
>> Note I’m using Expression Language to refer to the “filename” attribute.
>> Each incoming flow file will have that attribute set to one of the files
>> found by the ListFile processor.
>>
>>
>>
>> 3) Send to a PutSQL processor. Your SQL statement(s) are complete at this
>> point so you don’t need arguments, parameters, etc.
>>
>> I haven’t tried this yet but it seems like it should work. If you give it
>> a try, please let us know how it works for you :)
>>
>> Regards,
>> Matt
>>
>>
>> On 1/28/16, 3:04 PM, "Joe Percivall" <jo...@yahoo.com> wrote:
>>
>> >Hello Obaid,
>> >
>> >Sorry no one has gotten back to you sooner, many of the developers are
>> working diligently to get 0.5.0 done.
>> >
>> >I don't know too much about loading SQL tables but when you say "local
>> file system filename", is this a file that exits on the target system? If
>> so you may just be able to set that command because my understanding is
>> that using ExecuteSQL you have an input query which gets sent to the target
>> server. Then the target server then runs that command.
>> >
>> >Can anyone else that has more experience with SQL and loading tables
>> chime in?
>> >
>> >
>> >Joe
>> >- - - - - -
>> >Joseph Percivall
>> >linkedin.com/in/Percivall
>> >e: joepercivall@yahoo.com
>> >
>> >
>> >
>> >On Saturday, January 23, 2016 12:31 AM, obaidul karim <
>> obaidcuet@gmail.com> wrote:
>> >
>> >
>> >
>> >Hi,
>> >
>> >I developing a nifi processor for netezza.
>> >As per by experience the best way to load data to netezza is using
>> netezza JDBC external table.
>> >
>> >I want to run below command to load a file within NiFi processor.
>> >
>> >insert into MYTABLE
>> >select * FROM EXTERNAL '<local file system filename>'
>> >USING (<JDBC Options>) ;
>> >
>> >My question is which file to usefor <local file system filename> ?
>> >- Is it the flow files ? If yes then how can I get full path of a
>> flowfile ?
>> >- or can I directly load the file in a spool directory ?
>> >
>> >
>> >Thanks for your help in advance.
>> >
>> >-Obaid
>>
>>
>
Re: JDBC External Table File Target
Posted by obaidul karim <ob...@gmail.com>.
Hi Matthew ,
I didn't read your reply(just now reading :)), however, I have implemented
in the same way as you described :)).
In fact, I followed steps in FetchFile processor to read files directly
from an input list generated by ListFile processor.
My flow looks like below:
Flow: An Input Folder > [1]ListFile(Standard Processor) > [2]NzLoad(My
Custom processor) > [3]NzSql (My Custom processor)
Now need your help on below:
1. As I am reading from ListFile, it should be thread safe (a single file
should not be processed by more than 1 threads on NzLoad), right ?
2. How can I make the full flow synchronous. I want it like below:
a. [1]ListFile should list <=N files for each run and then stop until [2] &
[3]c completes
b. [2]NzLoad will process all the <=N files(using multiple thread) and only
then then trigger [3]NzSql
c. [3]NzSql will complete it processing and then trigger [1]ListFile and so
on.
Please share me if you have any alternative idea.
Regards,
Obaid
On Fri, Jan 29, 2016 at 4:22 AM, Matthew Burgess <ma...@gmail.com>
wrote:
> I think an approach that could work is the following:
>
> 1) Start with a ListFile processor to locate the filenames of the files
> you want to load into netezza
>
> 2) Send to a ReplaceText processor, where you replace the entire contents
> of the flow file with:
> insert into MYTABLE
> select * FROM EXTERNAL '${filename}'
> USING (<JDBC Options>) ;
>
>
> Note I’m using Expression Language to refer to the “filename” attribute.
> Each incoming flow file will have that attribute set to one of the files
> found by the ListFile processor.
>
>
>
> 3) Send to a PutSQL processor. Your SQL statement(s) are complete at this
> point so you don’t need arguments, parameters, etc.
>
> I haven’t tried this yet but it seems like it should work. If you give it
> a try, please let us know how it works for you :)
>
> Regards,
> Matt
>
>
> On 1/28/16, 3:04 PM, "Joe Percivall" <jo...@yahoo.com> wrote:
>
> >Hello Obaid,
> >
> >Sorry no one has gotten back to you sooner, many of the developers are
> working diligently to get 0.5.0 done.
> >
> >I don't know too much about loading SQL tables but when you say "local
> file system filename", is this a file that exits on the target system? If
> so you may just be able to set that command because my understanding is
> that using ExecuteSQL you have an input query which gets sent to the target
> server. Then the target server then runs that command.
> >
> >Can anyone else that has more experience with SQL and loading tables
> chime in?
> >
> >
> >Joe
> >- - - - - -
> >Joseph Percivall
> >linkedin.com/in/Percivall
> >e: joepercivall@yahoo.com
> >
> >
> >
> >On Saturday, January 23, 2016 12:31 AM, obaidul karim <
> obaidcuet@gmail.com> wrote:
> >
> >
> >
> >Hi,
> >
> >I developing a nifi processor for netezza.
> >As per by experience the best way to load data to netezza is using
> netezza JDBC external table.
> >
> >I want to run below command to load a file within NiFi processor.
> >
> >insert into MYTABLE
> >select * FROM EXTERNAL '<local file system filename>'
> >USING (<JDBC Options>) ;
> >
> >My question is which file to usefor <local file system filename> ?
> >- Is it the flow files ? If yes then how can I get full path of a
> flowfile ?
> >- or can I directly load the file in a spool directory ?
> >
> >
> >Thanks for your help in advance.
> >
> >-Obaid
>
>
Re: JDBC External Table File Target
Posted by Matthew Burgess <ma...@gmail.com>.
I think an approach that could work is the following:
1) Start with a ListFile processor to locate the filenames of the files you want to load into netezza
2) Send to a ReplaceText processor, where you replace the entire contents of the flow file with:
insert into MYTABLE
select * FROM EXTERNAL '${filename}'
USING (<JDBC Options>) ;
Note I’m using Expression Language to refer to the “filename” attribute. Each incoming flow file will have that attribute set to one of the files found by the ListFile processor.
3) Send to a PutSQL processor. Your SQL statement(s) are complete at this point so you don’t need arguments, parameters, etc.
I haven’t tried this yet but it seems like it should work. If you give it a try, please let us know how it works for you :)
Regards,
Matt
On 1/28/16, 3:04 PM, "Joe Percivall" <jo...@yahoo.com> wrote:
>Hello Obaid,
>
>Sorry no one has gotten back to you sooner, many of the developers are working diligently to get 0.5.0 done.
>
>I don't know too much about loading SQL tables but when you say "local file system filename", is this a file that exits on the target system? If so you may just be able to set that command because my understanding is that using ExecuteSQL you have an input query which gets sent to the target server. Then the target server then runs that command.
>
>Can anyone else that has more experience with SQL and loading tables chime in?
>
>
>Joe
>- - - - - -
>Joseph Percivall
>linkedin.com/in/Percivall
>e: joepercivall@yahoo.com
>
>
>
>On Saturday, January 23, 2016 12:31 AM, obaidul karim <ob...@gmail.com> wrote:
>
>
>
>Hi,
>
>I developing a nifi processor for netezza.
>As per by experience the best way to load data to netezza is using netezza JDBC external table.
>
>I want to run below command to load a file within NiFi processor.
>
>insert into MYTABLE
>select * FROM EXTERNAL '<local file system filename>'
>USING (<JDBC Options>) ;
>
>My question is which file to usefor <local file system filename> ?
>- Is it the flow files ? If yes then how can I get full path of a flowfile ?
>- or can I directly load the file in a spool directory ?
>
>
>Thanks for your help in advance.
>
>-Obaid
Re: JDBC External Table File Target
Posted by Joe Percivall <jo...@yahoo.com>.
Hello Obaid,
Sorry no one has gotten back to you sooner, many of the developers are working diligently to get 0.5.0 done.
I don't know too much about loading SQL tables but when you say "local file system filename", is this a file that exits on the target system? If so you may just be able to set that command because my understanding is that using ExecuteSQL you have an input query which gets sent to the target server. Then the target server then runs that command.
Can anyone else that has more experience with SQL and loading tables chime in?
Joe
- - - - - -
Joseph Percivall
linkedin.com/in/Percivall
e: joepercivall@yahoo.com
On Saturday, January 23, 2016 12:31 AM, obaidul karim <ob...@gmail.com> wrote:
Hi,
I developing a nifi processor for netezza.
As per by experience the best way to load data to netezza is using netezza JDBC external table.
I want to run below command to load a file within NiFi processor.
insert into MYTABLE
select * FROM EXTERNAL '<local file system filename>'
USING (<JDBC Options>) ;
My question is which file to usefor <local file system filename> ?
- Is it the flow files ? If yes then how can I get full path of a flowfile ?
- or can I directly load the file in a spool directory ?
Thanks for your help in advance.
-Obaid