Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive Insert Query

Solved Go to solution
Highlighted

Hive Insert Query

Explorer

Hi,

Need help on below query.

We have a scenario where for any column defined with datatype as string in Hadoop, NULL value is loaded as blank. However, any column other than string datatype (INT,DOUBLE etc) NULL values are loaded as NULL in Hadoop. 

 

Column name          Data Type

service1end               string

service1start              string

service2end               string

service2start              string

firstlinemaintcost    double

 

 

 

Is this a default behavior of Hadoop/hive?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Hive Insert Query

Explorer

@Gomathinayagam 

 

Thanks for your prompt response & clarification !. 

View solution in original post

2 REPLIES 2
Highlighted

Re: Hive Insert Query

Cloudera Employee

@ARVINDR ,

 

Yes, this is a default behaviour in Hive. When an empty value ( '' ) is passed for STRING datatype, it is taken as it is. 

 

If you would like the null values for STRING to be treated as NULL instead of just empty, you can set this property - serialization.null.format for the table of your choice. For example, you can do this on a new table or to an existing table by running a statement like the below, 

 

New Table:

CREATE TABLE null_test_1
(service1end string,
service1start string,
service2end string,
service2start string,
firstlinemaintcost double)
TBLPROPERTIES (
'serialization.null.format'='');

Existing Table:

alter table null_test set tblproperties ('serialization.null.format'='');

 Once done, the empty values inserted for the columns with STRING datatype will be shown as NULL. 

 

Hope this helps!

Re: Hive Insert Query

Explorer

@Gomathinayagam 

 

Thanks for your prompt response & clarification !. 

View solution in original post

Don't have an account?
Coming from Hortonworks? Activate your account here