perform join on csv and sql table having 1 common column and store result into sql table.

[#1]

I have a csv file (BUAndAmt.csv)

GlCode,Rate 60050,38.54 16501,1500.12 105601,538.4 and a

sql master table select * from [Sample].[dbo].[MasterData]

GlCode Amt

60050    5

16501    8

105601 2.

I want to perform join operation and do calculation on rate and amt column and store the calculated result into sql table. 

I am able to fetch csv with getFile processor and fetch sql table. aslo able to merge it. but how to join these flow files

Could you please help me on this

[#2]

@kanchanDesai Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our NiFi experts @cotopaul and @MattWho  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

[#3]

Hi @kanchanDesai ,

You have couple of options :

1 - You can just dump the data from CSV into its own table in the target DB (or staging) , and then dump the data from the sql source into the same target DB in case its different from the source. Once you have both sources in SQL you can join both tables by GlCode  and do the need calculation and store the result in the destination table.

2- If you want to do the join and calculation in Nifi , then you can take advantage of the ForkEnrichment\JoinEnrichment processors to join data together. If you are using SQL Strategy to join then you can do your calculation there as well and get the desired result that will feed into the destination table, otherwise you can use UpdateRecord, QueryRecord or JoltTransformationJSON to do the calculation. For more information on the Fork\Join Enrichment processors see : https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.20.0/org.apach...

If that helps please accept solution.

Thanks

[#4]

@kanchanDesai Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.  Thanks.