Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

Solved Go to solution

Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

New Contributor

62492-3.png

I have a simple CSV file and the content of the file is as follows:

1,QWER
2,TYUI
3,ASDF
4,GHJK
5,ZXCV 

I want to move the content of this file into a MYSQL table, hence i have created a the following flow (refer to 1.png)

I have configured the PutDatabaseRecord processor as (refer 2.png )

Also, the CSVReader looks like (refer 3.png)

62490-2.png

62489-1.png

I am getting error refer 4.png

62491-4.png

Can you help me configure my CSVReader I guess its because of it I am not able to push my csv records in to mysql table
Any help is appreciated.

Reference link:
https://community.hortonworks.com/questions/102559/please-suggest-me-stepshow-i-can-insert-csv-filei...

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

Guru

@Kunal Gaikwad

I used PutDatabaseRecord with a CSVReader, but have a slightly different flow that uses an UpdateAttribute processor to set the schema. So the flow is GetFile-->UpdateAttribute-->PutDatabaseRecord. Here it is as an attachment:

csv-to-mysql.xml

UpdateAttribute adds the attribute "schema.name" with the value of "test".

CSVReader:

62504-csvreader.png

AvroSchemaRegistry:

62505-avroschemaregistry.png

PutDatabaseRecord:

62507-putdbrecord.png

Here are the results in my database:

62511-mysql-db.png

5 REPLIES 5

Re: Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

Guru

@Kunal Gaikwad

I used PutDatabaseRecord with a CSVReader, but have a slightly different flow that uses an UpdateAttribute processor to set the schema. So the flow is GetFile-->UpdateAttribute-->PutDatabaseRecord. Here it is as an attachment:

csv-to-mysql.xml

UpdateAttribute adds the attribute "schema.name" with the value of "test".

CSVReader:

62504-csvreader.png

AvroSchemaRegistry:

62505-avroschemaregistry.png

PutDatabaseRecord:

62507-putdbrecord.png

Here are the results in my database:

62511-mysql-db.png

Re: Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

New Contributor

@AndrewLim

Worked like a charm. Looking at your response, I figured that I never defined schema.name and hence the errors! Thank you for the prompt response!

Re: Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

Guru

@Kunal Gaikwad

Great! Glad you got your flow working.

Re: Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

New Contributor

Hi @Andrew Lim @Kunal Gaikwad

I have tried the workflow (csv-to-mysql.xml) that you've attached. My use case is to insert few rows into teradata instead of mysql.
I've kept everything same as yours, just that the table name in Teradata (bigdata_dl.acct), DBCP Connection Pool for Teradata and datatype in Teradata is different.


But, I am getting the below error:

org.apache.nifi.schema.acess.SchemaNotFoundException:Unable to find schema with name 'bigdata_dl.acct'

error.jpeg

I've created the table in teradata before running the workflow:

2.png

This is how the UpdateAttribute processor looks like:

1.jpeg

This is how CSVReader looks like:

3.jpeg

This is how AvroSchemaRegistry looks like:

4.jpeg

if I provide the "test" field in the AvroSchemaRegistry as below (to match the datatype in Teradata as it doesn't supports long or string) it shows me State as Invalid stating not a valid Avro Schema: Illegal character in: varcher(30)

{ "type": "record",

"name": "TestRecord",

"fields" : [

{"name": "ACCT_ID", "type": "varchar(30)"},

{"name": "ACCT_NAME", "type": "varchar(30)"}

]

}

This is how PutDatabaseRecord looks like:

Please advise. Appreciate your help! Happy Halloween :-)

Re: Loading a simple CSV file using nifi(PutDatabaseRecord and CSVReader) causing error

@nifi_is_awesome

FYI, It is better to start a new question, referencing this question, rather than adding a new question via an answer here.