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 '@'
Top comments (0)