Hello All,
I am trying to execute a SQL query fetched from a file, and bind parameters using the flowfile attributes.
The file looks like the following with the dollar sign specifying desired parameters like NiFi EL.
And the flowfile attributes are value ingested from other sources.
flowfile content (fetched from sqlquery.txt)
SELECT id FROM users WHERE name = ${target_name} AND gender = ${target_gender};flowfile attributes
| target_name | 'Tom' |
| target_gender | 'male' |
And what I am trying to attempt is to execute the SQL:
SELECT id FROM users WHERE name = 'Tom' AND gender = 'male';
My current approach is:
1. Initialize parameters from other sources and store them in the flowfile attributes.
2. Use FetchFile to get the sql query from the file and store it in the flowfile content.
3. Use ReplaceText to replace literal ${target_name} with the string 'Tom'.
4. Use ReplaceText to replace literal ${target_gender} with the string 'male'.
5. ExecuteSQL
It works, but it doesn't seems right when the number of parameters or number of SQL grows.
I'll have to maintain lots of ReplaceText processors or one mapping file for each and every SQL using ReplaceTextWithMapping processor.
What I am attempting:
I am trying to extract the sql query as an attribute to better leverage NiFi expression language.
1. Initialize parameters from other sources and store them in the flowfile attributes.
2. Use FetchFile to get the sql query from the file and store it in the flowfile content.
3. Use ExtractText to extract the sql query
| sqlquery | SELECT id FROM users WHERE name = ${target_name} AND gender = ${target_gender}; |
4. ExecuteSQL with the property:
| SQL select query | ${sqlquery} |
However it execute:
SELECT id FROM users WHERE name = ${target_name} AND gender = ${target_gender}; instead of what I am attempting:
SELECT id FROM users WHERE name = 'Tom' AND gender = 'male';
Note: I have read the related posts I can find, but none of them really resolve my problem.
How can I use variable on query of executeSQL processor on apache NiFi?
NiFi Processor to Dynamically Create SQL Query From FlowFile or JSon
ExecuteSQL dynamic query..
Is there a way to bind variables when using ExecuteSQL with sql query stored in attribute or content?
Or is there a better way to better resolve this use case?
Thank you all in advanced !
