CSV Files with VARCHAR & NULL

Dremio University
1

Hey,
I am a student and try to figure some things out with/about Dremio.
I have Dremio Version 4.9.0 running on a single node Cluster.
I uploaded some CSV-files with Null values over the UI and curated them on the UI.
I tried to change the column-type but I coudn´t cast them because of the Null Values. Because Dremio sees the Null values as a String and not as a NULL ( I tried it out with TYPEOF(A)). (NULL/Null/null nothing worked)
So I used CONVERT_TO_FLOAT, _INTEGER, TO_DATE, _TIME that worked fine for me except for VARCHAR.
Is there a way Dremio recognize the VARCHAR NULL values? I tried to convert it again with CONVERT_TO(A, ‘VARCHAR’), CONVERT_TO_VARCHAR(A,1,1,0),CAST(A AS VARCHAR), but nothing worked for me. The only solution that works for me is to replace the Null again with NULL: SELECT CASE WHEN “A” = ‘null’ THEN NULL Else “A” END AS “AX”
But is there a way where I don´t have to iterate through the hole column each varchar?
Best regards,
Jonas

2

@Jonas

Dremio does not have DEODE (like Oracle), the equivalent is to use a CASE statement, so if you have some fields that have a value of type string and value as “null” then CASE would be good. If not, in your original CSV, what happens if you find and replace null with an empty cell?

3

Thanks for the fast reply.
OK.
Good idea. When I change my CSV like: a|b||d|e
Then Dremio reads the empty cell like an empty String and not like NULL.