函数列表
以下函数,hive-1.2 和 hive 3.1 中,相同的参数,返回的结果不同。在迁移过程中,容易导致数据结果不正确。
1. from_unixtime
hive-1.2 中返回当前时区对应的时间。hive-3.1 返回 UTC 时区的时间,结果和 hive-1.2 不一致。
1.1 from_unixtime(int)
1.1.1 hive-1.2
hive> select from_unixtime(1);
OK
1970-01-01 08:00:01
1.1.2 hive-3.1.3
把一个整数作为参数,返回 UTC 时区的时间,结果和 hive-1.2 不一致。
hive> select from_unixtime(1);
OK
1970-01-01 00:00:01
1.2 from_unixtime(tinyint)
1.2.1 hive-1.2
hive> select from_unixtime(cast(1 as tinyint));
OK
1970-01-01 08:00:01
1.2.2 hive-3.1.3
hive> select from_unixtime(cast(1 as tinyint));
OK
1970-01-01 00:00:01
1.3 from_unixtime(smallint)
1.3.1 hive-1.2
hive> select from_unixtime(cast(1 as smallint));
OK
1970-01-01 08:00:01
1.3.2 hive-3.1.3
hive> select from_unixtime(cast(1 as smallint));
OK
1970-01-01 00:00:01
1.4 from_unixtime(bigint)
1.4.1 hive-1.2
hive> select from_unixtime(cast (1 as bigint));
OK
1970-01-01 08:00:01
1.4.2 hive-3.1.3
hive> select from_unixtime(cast (1 as bigint));
OK
1970-01-01 00:00:01
1.5 from_unixtime(bigint unixtime, text format)
1.5.1 hive-1.2
hive> select from_unixtime(1,'yyyy-MM-dd HH:mm:ss');
OK
1970-01-01 08:00:01
1.5.2 hive-3.1
hive> select from_unixtime(1,'yyyy-MM-dd HH:mm:ss');
OK
1970-01-01 00:00:01
2. unix_timestamp
2.1 无参数 unix_timestamp()
hive-1.2 和 hive-3.1 执行结果一样。
2.2 有参数的 unix_timestamp 方法,调用 to_unix_timestamp,所以和 to_unix_timestamp 结果相同
如 select unix_timestamp('1970-01-01 00:00:01');
的结果等于 select to_unix_timestamp('1970-01-01 00:00:01');
3. to_unix_timestamp
3.1 to_unix_timestamp(string);
3.1.1 hive-1.2
hive> select to_unix_timestamp('1970-01-01 00:00:01.674');
OK
-28799
3.1.2 hive-3.1
hive> select to_unix_timestamp('1970-01-01 00:00:01.674');
OK
1
3.2 to_unix_timestamp(date);
3.2.1 hive-1.2
hive> select to_unix_timestamp(cast ('1970-01-01' as date));
OK
-28800
3.2.2 hive-3.1
hive> select to_unix_timestamp(cast ('1970-01-01' as date));
OK
0
3.3 to_unix_timestamp(timestamp);
3.3.1 hive-1.2
hive> select to_unix_timestamp(cast ('1970-01-01 00:00:01' as timestamp));
OK
-28799
3.3.2 hive-3.1
hive> select to_unix_timestamp(cast ('1970-01-01 00:00:01' as timestamp));
OK
1
Time taken: 0.172 seconds, Fetched: 1 row(s)
3.3.3 hive-1.2 精确到毫秒
如果 hive-1.2 精确到毫秒,则结果和精确到秒的数据有一定差别,相差一秒。
hive> select to_unix_timestamp(cast ('1970-01-01 00:00:01.679' as timestamp));
OK
-28798
3.3.4 hive-3.1 精确到毫秒
可以发现精确到毫秒和精确到秒一样。精确到毫秒时,转换时,使用 toEpochSecond 只返回精确到秒的数据。
hive> select to_unix_timestamp(cast ('1970-01-01 00:00:01.679' as timestamp));
OK
1
Time taken: 0.172 seconds, Fetched: 1 row(s)
3.4 to_unix_timestamp(string date, string format);
3.4.1 hive-1.2
hive> select to_unix_timestamp('1970-01-01','yyyy-MM-dd');
OK
-28800
3.4.1 hive-3.1
hive> select to_unix_timestamp('1970-01-01','yyyy-MM-dd');
OK
0
3.5 to_unix_timestamp(string timestamp, string format);
3.5.1 hive-1.2
select to_unix_timestamp('1970-01-01 00:00:01.674','yyyy-MM-dd HH:mm:ss');
OK
-28799
3.5.1 hive-3.1
hive> select to_unix_timestamp('1970-01-01 00:00:01.674','yyyy-MM-dd HH:mm:ss');
OK
1
3.6 to_unix_timestamp(string timestamp_with_local_zone, string format);
hive-1.2 和 hive-3.1 结果一致
3.6.1 hive-1.2
hive> select to_unix_timestamp('01/Jan/1970:00:00:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z');
OK
-28799
3.6.1 hive-3.1
hive> select to_unix_timestamp('01/Jan/1970:00:00:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z');
OK
-28799
4 timpstamp
4.1 timpstamp无参数
select timestamp();
的结果在 hive-1.2 和 hive-3.1 一致。返回结果不一致是因为执行时间的差异。
4.2 timestamp(int)
以下参数控制认为整数参数是 seconds 还是 milliseconds。
<property>
<name>hive.int.timestamp.conversion.in.seconds</name>
<value>false</value>
<description>
Boolean/tinyint/smallint/int/bigint value is interpreted as milliseconds during the timestamp conversion.
Set this flag to true to interpret the value as seconds to be consistent with float/double.
</description>
</property>
4.2.1 hive-1.2
hive> select timestamp(1);
OK
1970-01-01 08:00:00.001
4.2.2 hive-3.1
hive> select timestamp(1);
OK
1970-01-01 00:00:00.001
4.3 timestamp(tinyint)
4.3.1 hive-1.2
hive> select timestamp(cast(1 as tinyint));
OK
1970-01-01 08:00:00.001
4.3.2 hive-3.1
hive> select timestamp(cast(1 as tinyint));
OK
1970-01-01 00:00:00.001
4.4 timestamp(smallint)
4.4.1 hive-1.2
hive> select timestamp(cast(1 as smallint));
OK
1970-01-01 08:00:00.001
4.4.2 hive-3.1
hive> select timestamp(cast(1 as smallint));
OK
1970-01-01 00:00:00.001
4.5 timestamp(bigint)
4.5.1 hive-1.2
hive> select timestamp(cast(1 as bigint));
OK
1970-01-01 08:00:00.001
4.5.2 hive-3.1
hive> select timestamp(cast(1 as bigint));
OK
1970-01-01 00:00:00.001
4.6 timestamp(boolean true)
4.6.1 hive-1.2
hive> select timestamp(true);
OK
1970-01-01 08:00:00.001
4.6.2 hive-3.1
hive> select timestamp(true);
OK
1970-01-01 00:00:00.001
4.7 timestamp(boolean false)
4.7.1 hive-1.2
hive> select timestamp(false);
OK
1970-01-01 08:00:00
4.7.2 hive-3.1
hive> select timestamp(false);
OK
1970-01-01 00:00:00
4.8 timestamp(float)
4.8.1 hive-1.2
hive> select timestamp(cast(1 as float));
OK
1970-01-01 08:00:01
4.8.2 hive-3.1
hive> select timestamp(cast(1 as float));
OK
1970-01-01 00:00:01
4.9 timestamp(double)
4.9.1 hive-1.2
hive> select timestamp(cast(1 as double));
OK
1970-01-01 08:00:01
4.9.2 hive-3.1
hive> select timestamp(cast(1 as double));
OK
1970-01-01 00:00:01
4.10 timestamp(decimal)
4.10.1 hive-1.2
hive> select timestamp(cast(1 as decimal));
OK
1970-01-01 08:00:01
4.10.2 hive-3.1
hive> select timestamp(cast(1 as decimal));
OK
1970-01-01 00:00:01
5. 其他函数
5.1 date 函数(把timestamp 对应的 string 转为 date 类型)
hive-1.2
hive> select cast('1970-01-01 00:00:01.674' as date);
OK
NULL
hive-3.1
hive> select cast('1970-01-01 00:00:01.674' as date);
OK
1970-01-01
5.2 date_format hive-3.1 和 hive-1.2 应该一致
hive> select date_format('2023-04-16 05:00:00','"yyyy-MM-dd HH:mm:ss"') ;
OK
"2023-04-16 05:00:00"
hive> select date_format('2023-04-16 05:00:00','"yyyy-MM-dd HH:mm:ss z"') ;
OK
"2023-04-16 05:00:00 CST"
2. 原因分析
在 hive-1.2 版本中,不同的时区下执行这个SQL,输出的结果不一致。为了解决固定参数输出结果不一致,并且结果比较会出错的问题。HIVE-12192 进行了修复,它总是返回对应的 UTC 时间。对于一个数值,它总是返回固定的结果。
HIVE-12192 详细解释了修改的原因。从原理来说,这是 hive-1.2 的一个 bug,在 hive-3.1 修复了此 bug,导致了结果和 hive-1.2 不一致。
- 结果比较出错的解释:如 a 和 b 是两个数并且 a>b,在一个时区执行
select from_unixtime(a);
的结果比在一个时区执行select from_unixtime(b);
小。
3. 应对策略
3.1 修改现有SQL,使用 current_timestamp()
返回当前时间
3.2 修改源代码,退回 hive-1.2 的版本
和社区不兼容,每次升级都需要修改。
HIVE-12192 对应的 commit
如 from_unixtime 方法在此次提交里,仅修改了两行代码,如下图所示