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

[#1]

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.

[#2]

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

[#3]

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 🙂

[#4]

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 

[#5]

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  ?

[#6]

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