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.

External table when queried from HIVE returns NULL for timestamp column but works well in Impala

Highlighted

External table when queried from HIVE returns NULL for timestamp column but works well in Impala

New Contributor

For Eg: I have value in a pipe delimter textfile created in spark-scala as below

2017-02-16T11:24:29.000Z

External table with timestamp column pointing to above file , when queried returns NULL but same returns 2017-02-16 11:24:29 when queried in Impala.

 

Please share inputs to resolve this.

5 REPLIES 5

Re: External table when queried from HIVE returns NULL for timestamp column but works well in Impala

Guru
Try to run the following Hive query:

ALTER TABLE <table_name> SET SERDEPROPERTIES ("timestamp.formats"="yyyy-MM-dd'T'HH:mm:ss.SSSZ");

Then SELECT the table again should work.

This was introduced as part of: https://issues.apache.org/jira/browse/HIVE-9298

Re: External table when queried from HIVE returns NULL for timestamp column but works well in Impala

New Contributor

If you have time data in the format other than YYYY-MM-DD HH:MM:SS & if you set timestamp as the datatype in HIVE Table, then hive will display NULL when queried.

You can use a simple trick here, Open your .csv data file in Microsoft Excel. Select the entire column, rightclick>Format Cells>Custom>type in the text box the required format (i.e. YYYY-MM-DD HH:MM:SS) and press OK/Apply. Your data will then be displayed in the Hive acceptable format of timestamp and will be loaded appropriately in HIVE. You can check it by querying HIVE.Cheers :)

 

Re: External table when queried from HIVE returns NULL for timestamp column but works well in Impala

New Contributor

Hi, i am facing the same problem. i have lot of data in flat files (HDFS) that can't be open in Excel. Is there any way to do this in Spark or someting else. 

 

 

Thanks 

Re: External table when queried from HIVE returns NULL for timestamp column but works well in Impala

New Contributor

Hi ,

I have huge volume of data it is showing fine in Imapla and when I am trying to Query from Hive it is displaying Null values..Any suggestions  on this  ?

Re: External table when queried from HIVE returns NULL for timestamp column but works well in Impala

New Contributor

Hi,

Below is the solution.

I have checked in hdfs level for the date format and have given the same order in "formats" value as below.

 

My Date Format : 2019-02-02

 

Alter Command :

ALTER TABLE <TN>
SET SERDEPROPERTIES ("timestamp.formats"="yyyy-mm-dd'");

 

Thanks