NIFI load data from CSV to database

[#1]

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.

[#2]

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.

[#3]

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)
);

[#4]

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

[#5]

@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?