Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

NIFI load data from CSV to database

avatar
New Contributor

72421-replacetext.png

72423-putdatabaserecord.png

Hello guys,

I'm trying to load/insert data from a csv file to database (oracle).
GetFile --> UpdateAttribute --> ReplaceText --> PutDatabaseRecord

I'm new with nifi, any help is appreciated here. Template attached.

Thank you in advance.

72418-updateattribute.png

4 REPLIES 4

avatar
Master Guru

PutDatabaseRecord allows you to put multiple records from one flow file into a database at a time, without requiring the user to convert to SQL (you can use PutSQL for the latter, but it is less efficient). In your case you just need GetFile -> PutDatabaseRecord. Your CSVReader will have the schema for the data, which will indicate the types of the fields to PutDatabaseRecord. It will use that to insert the fields appropriately into the prepared statement and execute the whole flow file as a single batch.

avatar

Hi @Matt Burgess

I am trying to insert few sample records (.csv) to Teradata using NiFi. My current workflow is same as you suggested

GetFile -> PutDatabaseRecord

but I am getting the error as below. Please advise. Appreciate your help!

3.png

Please find the configurations for the 2 processors:

4.png 6.png 7.png


This is how data looks like:

ACCT_ID,ACCT_NAME
1,A
2,B

Table definition:

create table bigdata_dl.acct(
ACCT_ID VARCHAR(30),
ACCT_NAME VARCHAR(30)
);


5.png

avatar
Master Guru

Since your CSV headers match the column names exactly, try setting Translate Field Names to false.

avatar
Explorer

@mburgess 

I have a csv file which might have corrupt data as well.

How can I still insert the correct data and collect the corrupted data in seperate flow?