3

I'm casting a datefeild into time stamp in hive as from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as timestamp) as column_name, when I query it through impala time is displaying in GMT format. Not only the column I created every timestamp field is converting into GMT format.

How to make it display the time as is ? or how can I convert it into est ?

please help with sql query or with the spark/scala code, I tried with this How to convert a Date String from UTC to Specific TimeZone in HIVE? it dint help me.

Community
  • 1
  • 1
Rob
  • 162
  • 3
  • 13

2 Answers2

1

It is not Impala that converts the time to UTC+00:00 but Hive, but only when saving timestamps into Parquet. This is a bug in Hive: HIVE-12767. When reading timestamps from Parquet files, Hive does the reverse adjustment to get the correct value, but Impala does not.

According to the TIMESTAMP Data Type page in Impala's documentation, -convert_legacy_hive_parquet_utc_timestamps=true makes Impala do the same reverse adjustment that Hive does, but using it has a performance overhead.

If you write timestamps with Impala and read them back with Hive, Hive notices that the data was written by Impala thus it requires no adjustment, so timestamps written by Impala show up correctly in Hive.

Zoltan
  • 2,928
  • 11
  • 25
  • I enabled it in my spark code in the hive context, it dint work. Thank you – Rob Jan 26 '17 at 20:41
  • `convert_legacy_hive_parquet_utc_timestamps` is an Impala option, it's not something you can enable in Spark or Hive. – Zoltan Jan 28 '17 at 09:18
  • I got what you mean Zoltan, I'm using hive context to load the data into table and at the end I'm invalidating the metadata in impala to reflect the updates because our users query the data in imapala. I have no possibility to enable the parameter that you gave me. – Rob Jan 30 '17 at 19:42
0

did you try this?

SELECT  from_utc_timestamp(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 'GMT-4')  as column_name;

it should convert your date to EST time zone. Also, considerate the issue regarding to parquet date formats if you will read data instead of use the time system

Impala timestamps don't match Hive - a timezone issue?

Regaards!

Community
  • 1
  • 1
hlagos
  • 7,690
  • 3
  • 23
  • 41
  • I tried this I dint work, I know it will work on read but not on write. Thank you – Rob Jan 26 '17 at 20:39