Shulou(Shulou.com)06/03 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.