[jira] [Commented] (IMPALA-3933) Backward compatibility between Hive and Impala TIMESTAMP set of timezone rules across these different libraries

["Csaba Ringhofer (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IMPALA-3933?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16537373#comment-16537373 ] 

Csaba Ringhofer commented on IMPALA-3933:
-----------------------------------------

[~attilaj] [~zi]
I tried to reproduce this issue hoping that it is already resolved by IMPALA-3307 - it turned out that it is now reproducible not just with convert_legacy_hive_parquet_utc_timestamps, but also with from_utc_timestamp:

select from_utc_timestamp('1890-09-30 00:00:00', 'Europe/Budapest') 
result: 1890-09-30 01:16:20
select from_utc_timestamp('1890-10-01 00:00:00', 'Europe/Budapest')
result: 1890-10-01 01:00:00    

Note that the issue is not reproducible with "CET", so "CET" and "Europe/Budapest" are actually different.
It may be related that CET was introduced in Hungary in 1890 (according to https://en.wikipedia.org/wiki/Central_European_Time). I tried Europe/Belgrade, where CET was introduced in 1884, and the skew starts there accordingly.
Meanwhile in Hive the result is UTC+1 even before 1890. There may be a difference between how IANA and Hive's timezone db works, and the new timezone db of Impala and glibc may works similarly in this regard (both are IANA based).

What makes this issue a bit worrying is that it breaks ordering:
select from_utc_timestamp('1890-09-30 22:40:00', 'Europe/Budapest') 
result: 1890-09-30 23:56:20
select from_utc_timestamp('1890-09-30 22:45:00', 'Europe/Budapest');
result: 1890-09-30 23:45:00

This may lead to scenarios where Hive thinks that some data is ordered, but it turns out to be unordered after Impala converts it to local time.

> Backward compatibility between Hive and Impala TIMESTAMP set of timezone rules across these different libraries
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: IMPALA-3933
>                 URL: https://issues.apache.org/jira/browse/IMPALA-3933
>             Project: IMPALA
>          Issue Type: New Feature
>          Components: Backend
>    Affects Versions: impala 2.3
>            Reporter: Adriano Simone
>            Priority: Minor
>
> How the TIMESTAMP skew with convert_legacy_hive_parquet_utc_timestamps=true
> Enabling --convert_legacy_hive_parquet_utc_timestamps=true seems to cause data skew (improper converting) upon the reading for dates earlier than 1900 (not sure about the exact date).
> The following example was run on a server which is in CEST timezone, thus the time difference is GMT+1 for dates before 1900 (I'm not sure, I haven't checked the exact starting date of DST computation), and GMT+2 when summer daylight saving time was applied.
> create table itst (col1 int, myts timestamp) stored as parquet;
> From impala:
> {code:java}
> insert into itst values (1,'2016-04-15 12:34:45');
> insert into itst values (2,'1949-04-15 12:34:45');
> insert into itst values (3,'1753-04-15 12:34:45');
> insert into itst values (4,'1752-04-15 12:34:45');
> {code}
> from hive
> {code:java}
> insert into itst values (5,'2016-04-15 12:34:45');
> insert into itst values (6,'1949-04-15 12:34:45');
> insert into itst values (7,'1753-04-15 12:34:45');
> insert into itst values (8,'1752-04-15 12:34:45');
> {code}
> From impala
> {code:java}
> select * from itst order by col1;
> {code}
> Result:
> {code:java}
> Query: select * from itst
> +------+---------------------+
> | col1 | myts                |
> +------+---------------------+
> | 1    | 2016-04-15 12:34:45 |
> | 2    | 1949-04-15 12:34:45 |
> | 3    | 1753-04-15 12:34:45 |
> | 4    | 1752-04-15 12:34:45 |
> | 5    | 2016-04-15 10:34:45 |
> | 6    | 1949-04-15 10:34:45 |
> | 7    | 1753-04-15 11:34:45 |
> | 8    | 1752-04-15 11:34:45 |
> +------+---------------------+
> {code}
> The timestamps are looking good, the DST differences can be seen (hive inserted it in local time, but impala shows it in UTC)
> From impala after setting the command line argument "--convert_legacy_hive_parquet_utc_timestamps=true"
> {code:java}
> select * from itst order by col1;
> {code}
> The result in this case:
> {code:java}
> Query: select * from itst order by col1
> +------+---------------------+
> | col1 | myts                |
> +------+---------------------+
> | 1    | 2016-04-15 12:34:45 |
> | 2    | 1949-04-15 12:34:45 |
> | 3    | 1753-04-15 12:34:45 |
> | 4    | 1752-04-15 12:34:45 |
> | 5    | 2016-04-15 12:34:45 |
> | 6    | 1949-04-15 12:34:45 |
> | 7    | 1753-04-15 12:51:05 |
> | 8    | 1752-04-15 12:51:05 |
> +------+---------------------+
> {code}
> It seems that instead of 11:34:45 it is showing 12:51:05.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org