question Nifi Lookup CSV values with SQL NULL values in Support Questions

Nifi Lookup CSV values with SQL NULL values

code — Tue, 06 Sep 2022 09:30:59 GMT

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!

Re: Nifi Lookup CSV values with SQL NULL values

JimHalfpenny — Tue, 06 Sep 2022 10:44:08 GMT

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.

Re: Nifi Lookup CSV values with SQL NULL values

araujo — Tue, 06 Sep 2022 11:30:54 GMT

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é

Re: Nifi Lookup CSV values with SQL NULL values

JimHalfpenny — Tue, 06 Sep 2022 14:21:03 GMT

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.

Re: Nifi Lookup CSV values with SQL NULL values

code — Wed, 07 Sep 2022 07:34:52 GMT

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

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