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
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!
Is this running on a UTC Timezone? In non UTC timezones, that particular overflow error can happen (+ 14 hours is Kiribati, which would overflow).