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.

NIFI load data from CSV to database

NIFI load data from CSV to database

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

Re: NIFI load data from CSV to database

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.

Re: NIFI load data from CSV to database

New Contributor

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

Re: NIFI load data from CSV to database

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

Highlighted

Re: NIFI load data from CSV to database

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?

 

Don't have an account?
Coming from Hortonworks? Activate your account here