Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Nifi Lookup CSV values with SQL NULL values

avatar
Explorer

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!

1 ACCEPTED SOLUTION

avatar
Super Guru

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é

 

 

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

View solution in original post

4 REPLIES 4

avatar
Contributor

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.

 

JimHalfpenny_0-1662460934921.png

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. 

avatar
Super Guru

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é

 

 

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

avatar
Contributor

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.

avatar
Explorer

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

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