Analysis of inconsistency between Timestamp type date in 0014-Hive and display in Impala
Warm Tip: to see the high-definition no-code picture, please open it with your mobile phone and click the picture to enlarge.
1. Problem description
The Timestamp type field stored in the Hive table does not display a date that is consistent with the date queried in Impala.
two。 Problem recurrence
1. Create a simple test table
two。 Insert a piece of test data into the table
Insert into date_test4 values (1 Magnum 1503751615 Magnum Magnum 2017-08-26 08 purl 46 purl 55')
Get the current system time stored in the table:
3. The query time through Hive is shown as follows
Select id,create_date_str,from_unixtime (create_date) from date_test4
4. The query time through Impala is shown as follows
Select id,create_date_str,cast (create_date as timestamp) from date_test4
You can see that the time seen through the Hive query is not consistent with the time seen through the Impala query
3. Analysis of problems
From_unixtime of 3.1Hive
Description of from_unixtime function on Hive official website:
Return TypeName (Signature) Descriptionstringfrom_unixtime (bigint unixtime, string format) Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00".
In Hive, the TIMESTAMP timestamp is converted into a string in the date format of the current time zone through the from_unixtime function. The default format is "yyyy-MM-dd HH:mm:ss", so Hive can correctly convert the stored timestamp to the time of the current time zone when querying.
TIMESTAMP of 3.2Impala
By default, Impala does not use the local time zone to store timestamps to avoid unnecessary problems caused by unexpected time zone problems, and timestamps are stored and interpreted using UTC. For more information, please refer to the official documentation:
Http://impala.apache.org/docs/build/html/topics/impala\_timestamp.html#timestamp
4. Solution method
Use the from_utc_timestamp function of Impala to specify the time zone for time conversion, as shown in the following example:
Select id,create_date_str, cast (create_date as timestamp), from_utc_timestamp (cast (create_date as timestamp), 'EDT') from date_test4
The time after the specified time zone is the same as the time displayed in the original Hive. For more information on the time zone, please see the following address:
Http://zh.thetimenow.com/time-zones-abbreviations.php
Drunken whips are famous horses, and teenagers are so pompous! Lingnan Huan Xisha, under the vomiting liquor store! The best friend refuses to let go, the flower of data play!
Warm Tip: to see the high-definition no-code picture, please open it with your mobile phone and click the picture to enlarge.