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.

how to load CSV file data into oracle database using Apache Nifi

Highlighted

how to load CSV file data into oracle database using Apache Nifi

New Contributor

Hi team,

i have .dat file delimited with '|'.

i am using getFile-> replaceText -> putSQL processors. But in oracle database only nulls are inserting

insert into table_name(CAL_DATE, FROM_CURR, TO_CURR, value1, value2, value3) values('${CAL_DATE}', '${FROM_CURR}', '${TO_CURR}', '${value1}', '${value2}', '${value3}')

File data

header :CAL_DATE|FROM_CURR|TO_CURR|value1|value2|value3

20-12-2016|CAD|SEK|6.991|6.9939|

20-12-2016|GBP|CAD|1.6531|1.6534|

Footer: FOOTER|20161222162314|2

Could anyone help me in inserting this file data into Oracle database.

Regards,

Shashi.

5 REPLIES 5

Re: how to load CSV file data into oracle database using Apache Nifi

Contributor

@Shashidhar Janne

Is that your real data posted? I notice only 5 columns for actual data, and 6 for the header. Or are one of the columns optional?

On your replaceText processor, are you able to look at the data provenance in NiFi and see if they actual values are really being extracted and added as attributes to the flowfile? The same for the putSQL, in the statement are there any values there?

From looking at your use case, your general flow should probably be:

GetFile --> SplitText --> ExtractText --> PutSQL

for the ExtractText you would do something similar to how the CSVtoJSON template works, extracting those delimited values into attributes. The CSVtoJSON template can be found here: https://cwiki.apache.org/confluence/display/NIFI/Example+Dataflow+Templates

Highlighted

Re: how to load CSV file data into oracle database using Apache Nifi

New Contributor

10825-nifi-1.png

10826-nifi-2.png

10827-nifi-3.png

Hi Devin,

Thanks for the reply. hope you are doing good.

6th column contains completly null values, thats why 6 headers and 5 columns data are there.

In data provenance i am able to see the file attributes. Attached screenshots for your reference.

content : insert into QFRM_CCP.QFRM_MDMFXRATES(CAL_DATE, FROM_CURR, TO_CURR, value1, value2, value3) values('', '', '', '', '', '')

only nulls are inserting that to one time.

i am struct here when inserting .dat file into RDBMS. Could anyone assist from here

regards,

Shashi

Highlighted

Re: how to load CSV file data into oracle database using Apache Nifi

Super Guru
@Shashidhar Janne

PutSQL expects a SQL command. Is your replacetext processor creating a SQL command? What is that command? Also, I think you need to remove quotes from your expressions. What is the value of ${CAL_DATE} and other variables?

insert into table_name(CAL_DATE, FROM_CURR, TO_CURR, value1, value2, value3) values(${CAL_DATE}, ${FROM_CURR}, ${TO_CURR}, ${value1}, ${value2}, ${value3})

Highlighted

Re: how to load CSV file data into oracle database using Apache Nifi

New Contributor

Hi Mqureshi,

i am giving insert command in replaceText Processor, In PutSQl there is no option to specify insert query or sql queries.

my insert Query in replacetext processor is

insert into table_name(CAL_DATE,FROM_CURR,TO_CURR,value1, value2, value3) values(${CAL_DATE}, ${FROM_CURR}, ${TO_CURR}, ${value1}, ${value2}, ${value3}).

i removed the quotes, still not able to insert.

the value for cal_date is 05-12-2016

File data

header :CAL_DATE|FROM_CURR|TO_CURR|value1|value2|value3

20-12-2016|CAD|SEK|6.991|6.9939|

20-12-2016|GBP|CAD|1.6531|1.6534|

Footer: FOOTER|20161222162314|2

last column is having null values.

Facing problems in inserting the data.

Regards,

Shashi.

Highlighted

Re: how to load CSV file data into oracle database using Apache Nifi

Super Guru

In your flow you don't list an ExtractText processor, which likely means there is nothing in the flow file attributes named CAL_DATE, FROM_CURR, etc. @Devin Pinkston's answer mentions ExtractText along with an example of how it might work for your use case.

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