question Re: Use NIFI to aggregate data in SQL in Archives of Support Questions (Read Only)

Use NIFI to aggregate data in SQL

hady_ziade — Thu, 22 Sep 2016 21:48:51 GMT

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

Re: Use NIFI to aggregate data in SQL

mburgess — Thu, 22 Sep 2016 21:55:35 GMT

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.

Re: Use NIFI to aggregate data in SQL

hady_ziade — Fri, 23 Sep 2016 13:51:25 GMT

Thanks Matt for your help

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