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.

Hive ORDER BY Issue - Order by converts datetime unexpectedly / incorrectly

Hive ORDER BY Issue - Order by converts datetime unexpectedly / incorrectly

I've come across an ORDER BY issue in Hive that affects my datetime value. (while running queries in both CLI and JDBC)

The data was ingested from Oracle into Hadoop as a raw file using Informatica. I have an external table that points to this raw data. The raw data looks good with the expiration date/time field but the query is outputting bad date values when I use an "order by". Here's an example:

The correct value from the source is 9999-12-31 23:59:59.000000 but "order by" changed it to 1816-03-30 05:56:07.066277

Here's the query:

select equip_code,equip_nbr,start_event_ts,start_event_code,end_event_ts,equip_datetime from dbname.tablename where start_event_code in ('123') order by equip_datetime;

I've noticed this on both HDP 2.3.4 and HDP 2.4.

Thanks for the help!

2 REPLIES 2
Highlighted

Re: Hive ORDER BY Issue - Order by converts datetime unexpectedly / incorrectly

Rising Star

Is this running on a UTC Timezone? In non UTC timezones, that particular overflow error can happen (+ 14 hours is Kiribati, which would overflow).

Re: Hive ORDER BY Issue - Order by converts datetime unexpectedly / incorrectly

@gopal

Thanks for the response - I am running in EDT. I will give it a shot using UTC and see if I still encounter the error.