[flink-1.11] 读 kafka 写 hive，partition-time 抽取值不准确

[nashcen]

Kafka 表 定义如下：
CREATE TABLE `dc_ods`.`ods_dcpoints_prod_kafka_source` (
  `assetSpecId` STRING,
  `dcnum` STRING,
  `monitorType` STRING,
  `tagNo` STRING,
  `value` STRING,
*  `updateTime` BIGINT,
  `eventTime` AS TO_TIMESTAMP(FROM_UNIXTIME(`updateTime` / 1000,'yyyy-MM-dd
HH:mm:ss')),*
  WATERMARK FOR `eventTime` AS `eventTime` - INTERVAL '5' SECOND
) WITH (
  'connector' = 'kafka',
  'topic' = 'ods_dcpoints_prod',
  'properties.bootstrap.servers' = 'prod-bigdata-03:9092',
  'scan.startup.mode' = 'earliest-offset',
  'format' = 'json',
  'json.fail-on-missing-field' = 'false',
  'json.ignore-parse-errors' = 'true'
)

从已经插入到 hive的数据来看，有一条update_time=1600218000292,对应event_time的值，为
2020-09-16 01:00:00.0
<http://apache-flink.147419.n8.nabble.com/file/t817/1600935127%281%29.png>

但是从 hive 查询 FROM_UNIXTIME(cast(1600218000292/1000 as int),'yyyy-MM-dd
HH:mm:ss') 的值，为
2020-09-16 09:00:00
<http://apache-flink.147419.n8.nabble.com/file/t817/1600935330%281%29.png>

两者对不上，这是什么原因？






--
Sent from: http://apache-flink.147419.n8.nabble.com/

[Rui Li]

这应该是时区差异导致的，flink的FROM_UNIXTIME用的是UTC时区，hive的FROM_UNIXTIME用的是系统时区。

On Thu, Sep 24, 2020 at 4:16 PM nashcen <[hidden email]> wrote:

> Kafka 表 定义如下：
> CREATE TABLE `dc_ods`.`ods_dcpoints_prod_kafka_source` (
>   `assetSpecId` STRING,
>   `dcnum` STRING,
>   `monitorType` STRING,
>   `tagNo` STRING,
>   `value` STRING,
> *  `updateTime` BIGINT,
>   `eventTime` AS TO_TIMESTAMP(FROM_UNIXTIME(`updateTime` / 1000,'yyyy-MM-dd
> HH:mm:ss')),*
>   WATERMARK FOR `eventTime` AS `eventTime` - INTERVAL '5' SECOND
> ) WITH (
>   'connector' = 'kafka',
>   'topic' = 'ods_dcpoints_prod',
>   'properties.bootstrap.servers' = 'prod-bigdata-03:9092',
>   'scan.startup.mode' = 'earliest-offset',
>   'format' = 'json',
>   'json.fail-on-missing-field' = 'false',
>   'json.ignore-parse-errors' = 'true'
> )
>
> 从已经插入到 hive的数据来看，有一条update_time=1600218000292,对应event_time的值，为
> 2020-09-16 01:00:00.0
> <http://apache-flink.147419.n8.nabble.com/file/t817/1600935127%281%29.png>
>
>
> 但是从 hive 查询 FROM_UNIXTIME(cast(1600218000292/1000 as int),'yyyy-MM-dd
> HH:mm:ss') 的值，为
> 2020-09-16 09:00:00
> <http://apache-flink.147419.n8.nabble.com/file/t817/1600935330%281%29.png>
>
>
> 两者对不上，这是什么原因？
>
>
>
>
>
>
> --
> Sent from: http://apache-flink.147419.n8.nabble.com/
>

--
Best regards!
Rui Li

[Rui Li]

不好意思，我本地试了一下flink的FROM_UNIXTIME也是用的系统时区。你贴一下hive表的DDL和INSERT语句，我去试一下吧

On Fri, Sep 25, 2020 at 1:58 PM Rui Li <[hidden email]> wrote:

> 这应该是时区差异导致的，flink的FROM_UNIXTIME用的是UTC时区，hive的FROM_UNIXTIME用的是系统时区。
>
> On Thu, Sep 24, 2020 at 4:16 PM nashcen <[hidden email]> wrote:
>
>> Kafka 表 定义如下：
>> CREATE TABLE `dc_ods`.`ods_dcpoints_prod_kafka_source` (
>>   `assetSpecId` STRING,
>>   `dcnum` STRING,
>>   `monitorType` STRING,
>>   `tagNo` STRING,
>>   `value` STRING,
>> *  `updateTime` BIGINT,
>>   `eventTime` AS TO_TIMESTAMP(FROM_UNIXTIME(`updateTime` /
>> 1000,'yyyy-MM-dd
>> HH:mm:ss')),*
>>   WATERMARK FOR `eventTime` AS `eventTime` - INTERVAL '5' SECOND
>> ) WITH (
>>   'connector' = 'kafka',
>>   'topic' = 'ods_dcpoints_prod',
>>   'properties.bootstrap.servers' = 'prod-bigdata-03:9092',
>>   'scan.startup.mode' = 'earliest-offset',
>>   'format' = 'json',
>>   'json.fail-on-missing-field' = 'false',
>>   'json.ignore-parse-errors' = 'true'
>> )
>>
>> 从已经插入到 hive的数据来看，有一条update_time=1600218000292,对应event_time的值，为
>> 2020-09-16 01:00:00.0
>> <http://apache-flink.147419.n8.nabble.com/file/t817/1600935127%281%29.png>
>>
>>
>> 但是从 hive 查询 FROM_UNIXTIME(cast(1600218000292/1000 as int),'yyyy-MM-dd
>> HH:mm:ss') 的值，为
>> 2020-09-16 09:00:00
>> <http://apache-flink.147419.n8.nabble.com/file/t817/1600935330%281%29.png>
>>
>>
>> 两者对不上，这是什么原因？
>>
>>
>>
>>
>>
>>
>> --
>> Sent from: http://apache-flink.147419.n8.nabble.com/
>>
>
>
> --
> Best regards!
> Rui Li
>

--
Best regards!
Rui Li