- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Nifi Lookup CSV values with SQL NULL values
- Labels:
-
Apache NiFi
Created 09-06-2022 02:30 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Created on 09-06-2022 04:30 AM - edited 09-06-2022 04:30 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created 09-06-2022 03:44 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created on 09-06-2022 04:30 AM - edited 09-06-2022 04:30 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created 09-06-2022 07:21 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created 09-07-2022 12:34 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@JimHalfpenny and @araujo thanks both of you for your help!
The solution of creating a view with some kind of placeholders worked for me!
