CODEWITHSUNDEEP
hivebigdatahiveql
jun41D
jun41D

Reputation: 3

Hive replacing a value with null value in hive

I have a hive table where instead of NULL, it represents null record as string with value 'N/A', Is there any query that will convert that 'N/A' into NULL value datatype in hive.

Upvotes: 0

Views: 727

Answers (2)

Housheng-MSFT
Housheng-MSFT

Reputation: 772

The "CASE WHEN" syntax may be helpful. Create hive table and import data.

hive> create table mytable(
    name string,
    mycol string
 )

 row format delimited fields terminated by "\t";
 
 hive> load data local inpath '/opt/tempdata/mytable.txt' into table mytable;

sql syntax:

 hive> select 
 name,
 case mycol when 'N/A' then NULL else mycol end as mycol
 from mytable

Hope it helps you.

Upvotes: 0

Koushik Roy
Koushik Roy

Reputation: 7407

You can try if or case when or decode like below -

select 
case when mycol='N/A' then NULL else mycol end as mycol,
if(mycol='N/A', NULL,mycol) as mycol_if
from mytable

Upvotes: 1

Related Questions