fink sql 读取 kafka 的数据写到 kafka

版本： flink 1.12  平台：streamX

 

-- source
CREATE TABLE `stg_access_its_rt_kafka` (
      `message` STRING COMMENT '表数据',
      `table_name` STRING COMMENT '表名',
      `record_time` STRING COMMENT '时间yyyy-MM-dd HH:mm:ss',
      `ts` TIMESTAMP(3) METADATA FROM 'timestamp'
) WITH (
      'connector' = 'kafka',
      'format' = 'json',
      'topic' = 'its-topic',
      'properties.bootstrap.servers' = 'ip:port',
      'scan.startup.mode' = 'latest-offset',
      'properties.group.id' = 'stg_access_its_rt_kafka'
    );

-- sink
CREATE TABLE `stg_its_vehicle_dynamic_info_rt_kafka` (
        id bigint COMMENT'主键',
        vhc_plate_number	STRING COMMENT'号牌号码',
        vhc_plate_type	STRING COMMENT'号牌种类',
        speed	double COMMENT'速度，单位：km/h',
        fx	STRING COMMENT'方向',
        longi	double COMMENT'经度',
        lati	double COMMENT'纬度',
        gmt_create	STRING COMMENT'GPS生成时间',
        record_time STRING COMMENT'GPS生成时间',
        `dt`  STRING COMMENT 'yyyy-mm-dd',
        `state_time`  STRING COMMENT 'yyyyMMddHHmmss 时间'
) WITH (
      'connector' = 'kafka',
      'format' = 'json',
      'topic' = 'stg_its_vehicle_dynamic_info',
      'properties.bootstrap.servers' = 'ip:port',
      'sink.partitioner'='round-robin'
      );



--vehicle_dynamic_info
INSERT INTO stg_its_vehicle_dynamic_info_rt_kafka
SELECT
    cast(get_value_from_string_json(message,'id') as bigint) as id,
    get_value_from_string_json(message,'vhcPlateNumber') as vhc_plate_number,
    get_value_from_string_json(message,'vhcPlateType') as vhc_plate_type,
    cast(get_value_from_string_json(message,'speed') as double) as speed,
    get_value_from_string_json(message,'fx') as fx,
    cast(get_value_from_string_json(message,'longi') as double) as longi,
    cast(get_value_from_string_json(message,'lati') as double) as lati,
    get_value_from_string_json(message,'gmtCreate') as gmt_create,
    record_time as record_time,
    date_format_local(cast(`ts` as string),'yyyy-MM-dd') as `dt`,
    date_format_local(cast(`ts` as string),'yyyyMMddHHmmss') as `state_time`

from stg_access_its_rt_kafka
where table_name = 'vehicle-dynamic-info';

需要加时间戳的

-- source
`ts` TIMESTAMP(3) METADATA FROM 'timestamp'

-- sink
`dt`  STRING COMMENT 'yyyy-mm-dd',
`state_time`  STRING COMMENT 'yyyyMMddHHmmss 时间'

-- insert into
date_format_local(cast(`ts` as string),'yyyy-MM-dd') as `dt`,
date_format_local(cast(`ts` as string),'yyyyMMddHHmmss') as `state_time`