3

Would it be possible to route flow files according to the result of an SQL query which returns a single row result? For example, if the result is '1' the flow file will be processed; otherwise, it will be ignored.

Solution

The following approach worked best for me.

  1. Use ExecuteSQL processor in order to run filtering SQL query. The query was written to produce either a single record (match) or an empty record set (no match) in a way suggested by Shu.
  2. Connect ExecuteSQL to RouteOnAttribute processor in order to filter out unmatched flow files using the following value of routing property value ${executesql.row.count:replaceNull(0):gt(0)}

Notice, that the original content of a flow file will be lost after applying ExecuteSQL. It's not an issue in my case, because I do filtering before processing flow file content and my SQL query is based entirely on the flow file attributes and not on its content. Though in a more general scenario, when the flow file content is modified by the incoming part of the flow, one should save file content somewhere (e.g. file system) and restore it after the filtering part has applied.

depthofreality
  • 579
  • 6
  • 14

1 Answers1

2

You can add where clause in your sql query where <field_name> = 1 then we are only going to have output a flowfile when the result value =1.

(or)

Checking the data in NiFi:

We are going to have AVRO format data as the result of SQL query so you can use

option1:ConvertAvroToJson Processor:

Convert the AVRO data into JSON format then extract the value from the json content as attribute using EvaluateJsonPath processor.

Then use RouteOnAttribute processor add new property using NiFi expression language equals function compare the value and route the flowfile to matched relation.

Refer to this link more details regards to EvaluateJsonpath and RouteOnAttribute processor configs.

option2: Using QueryRecord processor:

By using QueryRecord processor we can run SQL queries on the content of the flowfile

Add new property to the processor as

select * from FLOWFILE where <filed_name> =1

Feed the property relation to the other processor

Refer to this link for more details regarding QueryRecord processor usage.

notNull
  • 30,258
  • 4
  • 35
  • 50