Differentiate Null and Blank cell in an IN-DB dataset

[vaibhavmarathey]

Hello

I have an IN-DB dataset on HIVE (hdfs), in this certain cell are Blank as well Null. In an IN-DB workflow I am able to idenntify NULL cells but having trouble filtering out blank cell. 

Any pointers on solving this issue shall be great help. 

Thanks

Vaibhav

[apathetichell]

i believe '' (or empty value) is empty in Hive syntax so: where "column" =''  should work.

note -I had a recent situation in a Databricks query where I needed to drop the column name quotes to get it to run. And yes I do have field names quoted checked.

[PanPP]

Hi @vaibhavmarathey,

In Designer, you can also utilize the formulas below to filter these out. 

IsEmpty([FieldName]) would identify the empty/blank values columns.

IsNull([FieldName]) would identify the null values in a column.

Hope this helps, if it does please mark it as a solution and like the post.

[apathetichell]

@PanPP  those don't work In-DB. Formula In-DB uses the native SQL format of the back end DB. Fields are (usually) denoted in quotes.

[PanPP]

You can utilize the data stream out tool to utilize a normal formula tool with the expressions that were mentioned.

[apathetichell]

@PanPP - that's fine on 1000 records. or 10000 records. Have you tried to datastream out 10,000,000 or 100,000,000 or 1,000,000,000 records?

[maygross]

Have you ever figured this out for the in DB filter tool? im currently having the same issue

[apathetichell]

are you using filter-in-db? What kind of SQL are you using? That would dicate your solution.