Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
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

avatar
Super 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

avatar
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 🙂

 

avatar
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 

avatar
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  ?

avatar
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