Nifi Lookup CSV values with SQL NULL values

Question

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!

araujo · Accepted Answer

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é

JimHalfpenny · Answer

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.

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.