You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@nifi.apache.org by "suman.das@cuddle.ai" <su...@cuddle.ai> on 2017/05/02 15:58:01 UTC

Not able to ingest the csv data to RDBS Database

Hi ,
I have a csv file and i want to ingest the csv file data into the database.

I have used getfile,RelaceText,PutSQL Processors.

It's failing in replaceText Processor.

File Sample :
City,Count
Mumbai,10
Mumbai,10
Pune,10
Pune,10

ReplaceText :

INSERT INTO CITY(city,count) values ('${city}',${count})

Please help.



--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by "suman.das@cuddle.ai" <su...@cuddle.ai>.

Hi ,
I tried with Header Line count 0 in SplitText Processor still getting the
same result. I am trying with the header row.

City,Count
Mumbai,10
Mumbai,10
Pune,10
Pune,10

Do i need to add any processor





--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15641.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by Matt Burgess <ma...@gmail.com>.

Your original example has 5 rows, a header and 4 value rows. Did you
remove the header row for your current testing?  If not, and you use a
Header Line Count of 1, then each flow file probably has two lines in
it, the header and the value row, and ReplaceText is probably matching
the header lines and thus using the hardcoded "values" of City and
Count.  Instead you may want a Header Line Count of zero and
route/remove the header row from the rest of the value row flow files.

Regards,
Matt

On Wed, May 3, 2017 at 10:42 AM, suman.das@cuddle.ai
<su...@cuddle.ai> wrote:
> I am just testing currently with a sample file containing only 4 rows.
>
>
>
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15637.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by "suman.das@cuddle.ai" <su...@cuddle.ai>.

I am just testing currently with a sample file containing only 4 rows.



--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15637.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by Matt Burgess <ma...@apache.org>.

Do all the files look like that, or just the first one? If it is just
the first one, then it is likely due to the header row in your CSV
file. You may want to use RouteOnAttribute after SplitText to get rid
of the header row (using fragment.index = 0), or I think you can use
ReplaceText before SplitText to get rid of the header (but it might
take longer).

If all the files look like that, then the ExtractText is not putting
the right values into the corresponding attributes.

Regards,
Matt

On Wed, May 3, 2017 at 10:20 AM, suman.das@cuddle.ai
<su...@cuddle.ai> wrote:
> Still not able to insert the data in table.
>
> I tested with PutFile instead of PutSql and file contains
> INSERT INTO CITY(city,count) VALUES('city',count)
>
>
>
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15634.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by "suman.das@cuddle.ai" <su...@cuddle.ai>.

SplitText by line 1 and header count 1
Extract Text : City : (.*),.*
                     Count : .*,(.*)
ReplaceText : INSERT INTO CITY(City,Count) values ('${City}',${Count})




--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15635.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by "suman.das@cuddle.ai" <su...@cuddle.ai>.

Still not able to insert the data in table.

I tested with PutFile instead of PutSql and file contains 
INSERT INTO CITY(city,count) VALUES('city',count)



--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15634.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by "suman.das@cuddle.ai" <su...@cuddle.ai>.

Hi,
Thanks for helping . I have modified the flow according to your suggestion.
My flow consists of below processors.

GetFile-->SplitText-->ExtractText-->ReplaceText-->PutSql.

I have only 4 rows in my csv then why the SplitText queue contains huge MB
of data.

Also in PutSql how to specify the table name where the data needs to be
inserted.

The table needs to be present before?

My file

City,Count
Mumbai,10
Mumbai,10
Pune,10
Pune,10





--
View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610p15630.html
Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.

Re: Not able to ingest the csv data to RDBS Database

Posted by Bryan Bende <bb...@gmail.com>.

In order for ReplaceText to reference ${city} and ${count} there need
to be flow file attributes available for these values, and if you
picked up a file from GetFile and went directly to ReplaceText then
they wouldn't be there.

You would likely have to do something like GetFile -> SplitText with
line count 1 -> ExtractText to get city and count in attributes ->
ReplaceText -> PutSQL.

-Bryan

On Tue, May 2, 2017 at 11:58 AM, suman.das@cuddle.ai
<su...@cuddle.ai> wrote:
> Hi ,
> I have a csv file and i want to ingest the csv file data into the database.
>
> I have used getfile,RelaceText,PutSQL Processors.
>
> It's failing in replaceText Processor.
>
> File Sample :
> City,Count
> Mumbai,10
> Mumbai,10
> Pune,10
> Pune,10
>
> ReplaceText :
>
> INSERT INTO CITY(city,count) values ('${city}',${count})
>
> Please help.
>
>
>
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/Not-able-to-ingest-the-csv-data-to-RDBS-Database-tp15610.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.