Reply
New Contributor
Posts: 1
Registered: ‎03-21-2017

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

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.

Cloudera Employee
Posts: 279
Registered: ‎03-23-2015

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

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
Ro1
New Contributor
Posts: 1
Registered: ‎02-20-2018

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

[ Edited ]

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 :)

 

New Contributor
Posts: 2
Registered: ‎09-28-2017

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

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 

Announcements