How to ensure the rows moved into SQL?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to ensure the rows moved into SQL?

prabhu Mahendran
Hi All,

Since i need to know how to check all rows in csv moved in SQL Server.

I have download csv files from HTTP.

Just imagine i have 10 files i could move files one after another into SQL Server.

i need to ensure if number of rows in csv moves correctly insert into SQL Server.

For example if file having 100 rows to be insert into SQL Server how to check 100 rows correctly moved into SQL in NiFi.After move that 100 files then row next file moved into SQL.

Is this possible to check number of rows in files equal to sql moved rows without using Script? 
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to ensure the rows moved into SQL?

Andy LoPresto-2
Prabhu,

You can get a row count on the incoming CSV files by routing them through SplitText and using the “fragment.count” value as the total number of (non-header) lines, or by using an ExecuteStreamCommand with the command “wc -l” which counts the number of lines in text. With this knowledge, you can then use a SQL query in ExecuteSQL to check the number of rows inserted in the last time slice or with a special identifier range by checking “max(id)” before the insert begins.  


Andy LoPresto
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4  BACE 3C6E F65B 2F7D EF69

On Jun 12, 2017, at 5:47 AM, prabhu Mahendran <[hidden email]> wrote:

Hi All,

Since i need to know how to check all rows in csv moved in SQL Server.

I have download csv files from HTTP.

Just imagine i have 10 files i could move files one after another into SQL
Server.

i need to ensure if number of rows in csv moves correctly insert into SQL
Server.

For example if file having 100 rows to be insert into SQL Server how to
check 100 rows correctly moved into SQL in NiFi.After move that 100 files
then row next file moved into SQL.

Is this possible to check number of rows in files equal to sql moved rows
without using Script?


signature.asc (859 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to ensure the rows moved into SQL?

prabhu Mahendran

Thanks for your response.

i am having 10 files vary between number of rows.

For example one file has contains 30 lakhs rows and one having 70 lakhs of rows.Since i have using two split text for split files with Line Split Count.hence "fragment.count" not worked in it.

i have used nifi in windows so "wc -l" count not worked.

Is there any other way to ensure number of rows?

On 12-Jun-2017 11:20 PM, "Andy LoPresto" <[hidden email]> wrote:
Prabhu,

You can get a row count on the incoming CSV files by routing them through SplitText and using the “fragment.count” value as the total number of (non-header) lines, or by using an ExecuteStreamCommand with the command “wc -l” which counts the number of lines in text. With this knowledge, you can then use a SQL query in ExecuteSQL to check the number of rows inserted in the last time slice or with a special identifier range by checking “max(id)” before the insert begins.  


Andy LoPresto
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4  BACE 3C6E F65B 2F7D EF69

On Jun 12, 2017, at 5:47 AM, prabhu Mahendran <[hidden email]> wrote:

Hi All,

Since i need to know how to check all rows in csv moved in SQL Server.

I have download csv files from HTTP.

Just imagine i have 10 files i could move files one after another into SQL
Server.

i need to ensure if number of rows in csv moves correctly insert into SQL
Server.

For example if file having 100 rows to be insert into SQL Server how to
check 100 rows correctly moved into SQL in NiFi.After move that 100 files
then row next file moved into SQL.

Is this possible to check number of rows in files equal to sql moved rows
without using Script?

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to ensure the rows moved into SQL?

Koji Kawamura
Hi Prabhu,

In that case, Wait/Notify processors may be helpful. I've written a
blog post before covering the similar use-case using those.
http://ijokarumawak.github.io/nifi/2017/02/02/nifi-notify-batch/

Thanks,
Koji

On Tue, Jun 13, 2017 at 1:34 PM, prabhu Mahendran
<[hidden email]> wrote:

> Thanks for your response.
>
> i am having 10 files vary between number of rows.
>
> For example one file has contains 30 lakhs rows and one having 70 lakhs of
> rows.Since i have using two split text for split files with Line Split
> Count.hence "fragment.count" not worked in it.
>
> i have used nifi in windows so "wc -l" count not worked.
>
> Is there any other way to ensure number of rows?
>
> On 12-Jun-2017 11:20 PM, "Andy LoPresto" <[hidden email]> wrote:
>>
>> Prabhu,
>>
>> You can get a row count on the incoming CSV files by routing them through
>> SplitText and using the “fragment.count” value as the total number of
>> (non-header) lines, or by using an ExecuteStreamCommand with the command “wc
>> -l” which counts the number of lines in text. With this knowledge, you can
>> then use a SQL query in ExecuteSQL to check the number of rows inserted in
>> the last time slice or with a special identifier range by checking “max(id)”
>> before the insert begins.
>>
>>
>> Andy LoPresto
>> [hidden email]
>> [hidden email]
>> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4  BACE 3C6E F65B 2F7D EF69
>>
>> On Jun 12, 2017, at 5:47 AM, prabhu Mahendran <[hidden email]>
>> wrote:
>>
>> Hi All,
>>
>> Since i need to know how to check all rows in csv moved in SQL Server.
>>
>> I have download csv files from HTTP.
>>
>> Just imagine i have 10 files i could move files one after another into SQL
>> Server.
>>
>> i need to ensure if number of rows in csv moves correctly insert into SQL
>> Server.
>>
>> For example if file having 100 rows to be insert into SQL Server how to
>> check 100 rows correctly moved into SQL in NiFi.After move that 100 files
>> then row next file moved into SQL.
>>
>> Is this possible to check number of rows in files equal to sql moved rows
>> without using Script?
>>
>>
>
Loading...