NiFi Quick Tip on SQL

You sometimes have to cast, as fields aren't what you think they are. I have some temperatures that are stored as string, yeah I know let's yell at who did that. Maybe it was some lazy developer (Me?~??~?~?!!!). Let's just cast to a type that makes sense for math and comparisons. CAST is my friend.

SELECT *

FROM FLOWFILE

WHERE CAST(temperaturef as FLOAT) > 60

Apache NiFi (and lots of other awesome projects) use Apache Calcite for queries. So if you need some SQL help, always look here: https://calcite.apache.org/docs/reference.html

You can also include variables in your QueryRecord queries.

SELECT *

FROM FLOWFILE

WHERE CAST(temperaturef as FLOAT) >= (CAST(${predictedTemperature} as FLOAT) - 5)

There are wildcard characters that you may need to watch.

Underscore has special meaning. Also there often column names that are reserved words. I got a lot of columns coming from IoT often with names like timestamp, start, end and other ones used by SQL. Just put a start around it.

Watch those wildcards.

select * from flowfile where internal = false
and name not like '@_@_%' ESCAPE '@'