Nifi Lookup CSV values with SQL NULL values

[#1]

Hi, I need to compare several csv columns with values from my db. To do so I use the LookupRecord with concat various csv columns and the corresponding concat of sql columns in the DatabaseRecordLookupService. This works fine as long as I do not have any NULL values in my db.

If there is a NULL value the Lookup Processor always routes those datasets to unmatched, as an empty string in the csv does not match the NULL values from the db.

What needs to be written in the corresponding csv column so that it matches the db NULL value?

I already tried to insert 'null', 'NULL' and 0 in the csv but it never matched the db value.

Thanks for your help!

[#2]

The CSV reader has a property name Null String that allows you to set the value that will appear in a CSV field to represent null.

Try setting this when you read your CSV records. I've not tested whether this passes null to a SQL query, but using a literal value like "null" in your CSV data will be interpreted as a string. 

[#3 · Accepted solution]

Hi, @code ,

I don't think this is actually possible. Even if there was a way to enter a literal value of NULL for the lookup value, the controller service is probably comparing the lookup value with an "equals" operation (e.g. mytable.mykey = lookup_value) and in relational databases the comparison NULL = NULL is always evaluated to FALSE. (the only way to compare values with a NULL is to use the operator IS).

What you can try to do is to create a view on top of that table that converts NULLs to some string that you can use to compare to lookup values in NiFi. Then you can use the view name in the controller service configuration instead of the table name.

For example:

CREATE VIEW v_mytable AS
SELECT
  NVL(mykey, '<NULL>') as key_without_nulls, *
FROM mytable

Be aware of potential performance implications of this, since this could prevent existing table indexes from being used for lookups.

Cheers,

André

[#4]

Agreed, I did a quick test and null values in records return null when the lookup is done. As André says, querying column_value = null does not match a null value in the column as an IS NULL statement would. Ideally you should not have a key column which contains nulls or duplicate rows since this is going to unpredictable results.

[#5]

@JimHalfpenny and @araujo thanks both of you for your help!

The solution of creating a view with some kind of placeholders worked for me!