Use NIFI to aggregate data in SQL

[#1]

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 ....

[#2 · Accepted solution]

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.

[#3]

Thanks Matt for your help

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