Can I use dynamic variable in NiFi expression language?

[#1]

I am trying to write NiFi expression for routeonattribute processor as below:

${db.table.schema:toLower():in(${IncludeSchemaList}):and(${db.table.name:toLower():in(${ExcludeTableList}):not()})}

IncludeSchemaList and ExcludeTableList are provided in variables and added onto the flowfile as an attribute with updateattribute processor. The above In clause is treating the variables as literals and not working.

If I hard code the variables it looks like this:

${db.table.schema:toLower():in("dbo","hilton","archive","audit", "util"):and(${db.table.name:toLower():in("couponcodes","batch_job_execution_context","batch_step_execution"):not()})}

I hope someone knows how to solve for this. I would like to parameterize the schemanames to include and tables names to exclude in variables.

Thanks.

[#2 · Accepted solution]

@Prachi Sharma I think you should try something like this:

${anyDelineatedValue("${IncludeSchemaList}", ","):equals("${db.table.schema}"):and(anyDelineatedValue("${ExcludeTableList}", ","):equals("${db.table.schema}"):not())}

while having

IncludeSchemaList=dbo,hilton,archive,audit,util
ExcludeTableList=couponcodes,batch_job_execution_context,batch_step_execution

HTH

*** If you found this answer addressed your question, please take a moment to login and click the "accept" link on the answer.

[#3]

Thanks, I appreciate the quick response. I had to tweak the approach a bit, but this worked great!

I used two routeonattribute processor as the not() was posing issues.

First apply schema filter allows matches to flow through using below:

${anyDelineatedValue("${IncludeSchemaList}", ","):equalsIgnoreCase("${db.table.schema}")}

Then I apply table filter and only allow unmatched to flow through, using below:

${anyDelineatedValue("${ExcludeTableList}", ","):equalsIgnoreCase("${db.table.name}")}