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
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@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.
Re: Not able to ingest the csv data to RDBS Database
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
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
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
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@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
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
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@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
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.