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.

Use NIFI to aggregate data in SQL

Solved Go to solution
Highlighted

Use NIFI to aggregate data in SQL

New Contributor

Hi , i have a file that we use as configuration file contains a key value pair in the following format ParamName : ParamValue .

i need to create a nifi workflow to read from this Parameter file and use its values as attributes to use it to construct a sql query . that will be used to aggregate data .

for example : Offset : 2

insert into table1 select ....... from table2 where date_time > timestampadd(day,${Offset},getdate())

its very important for us to use a configuration file instead of hardcode these values in each workflow since we will have many workflows reading from the same file .

am not able to create such workflow in NIFI , any suggestions would be appreciated ....

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Use NIFI to aggregate data in SQL

Super Guru

There are a couple of options:

1) If you want one SQL query per parameter, you can use ListFile/FetchFile (or GetFile if you want to repeatedly get the config file) to retrieve the configuration file, then SplitText to split each line (so one parameter per flow file), then ExtractText to get the name and value of the parameter, then ReplaceText to build a SQL query using Expression Language and the name of the parameter (which will fill in the value), such as the example statement you have above.

2) If you want to build a single statement with possibly multiple parameters, you could use ExecuteScript (if you are comfortable writing code in Groovy, Jython, JRuby, JavaScript, or Lua) to read in the configuration file, split the lines to build a map of parameter names to values, then write out a SQL statement with the names and/or values as you have done above.

View solution in original post

2 REPLIES 2
Highlighted

Re: Use NIFI to aggregate data in SQL

Super Guru

There are a couple of options:

1) If you want one SQL query per parameter, you can use ListFile/FetchFile (or GetFile if you want to repeatedly get the config file) to retrieve the configuration file, then SplitText to split each line (so one parameter per flow file), then ExtractText to get the name and value of the parameter, then ReplaceText to build a SQL query using Expression Language and the name of the parameter (which will fill in the value), such as the example statement you have above.

2) If you want to build a single statement with possibly multiple parameters, you could use ExecuteScript (if you are comfortable writing code in Groovy, Jython, JRuby, JavaScript, or Lua) to read in the configuration file, split the lines to build a map of parameter names to values, then write out a SQL statement with the names and/or values as you have done above.

View solution in original post

Highlighted

Re: Use NIFI to aggregate data in SQL

New Contributor

Thanks Matt for your help

do you have any similar example for using ExecuteScript with any of the code you mentioned ?

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