One Minute NiFi Tip: Calcite SQL Notes

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 '@'