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: Ingesting monthly dump from SQL query into SFTP server as CSV files

NIFI: Ingesting monthly dump from SQL query into SFTP server as CSV files

Hello all,

I am in a situation where I would like to store data as respective monthly CSVs using SQL query into SFTP server.

For instance, my query is :

select fooId, bar from FooBar where query_date>=20180101 and query_date<20180201 (for the month of January 2018)

I would like to store it as 20180101_FooBar.csv on to my SFTP server. Similarly, other files for other months follow the same process with different query_date interval.


Important consideration to make : I have to store the fooId as MD5 Hash string.

How may I automate this flow in NIFI?


Roughly, the flow that I foresee is:

ExecuteSQL(but not sure how to paramterize the counter for query_date) -> ConvertAvroToJson -> EvaluateJsonPath (to extract the fooID ) -> HashContent -> MergeContent


Please advice me on the same, how I may take this forward.

Thanks

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