(mysql和pg) -＞ flink-cdc -＞ kafka -＞ flink-sql -＞ StarRocks

DROP TABLE IF EXISTS ods_mall_activity_20231118;

CREATE TABLE ods_mall_activity_20231118 ( 
    id BIGINT,
    activity_name STRING,
    exchange_end_time BIGINT,
    update_by_name STRING,
    exchange_start_time BIGINT,
    create_time BIGINT,
    `year`  INT,
    end_time BIGINT,
    appointment_start_time BIGINT,
    create_by_name STRING,
    create_by BIGINT,
    update_time BIGINT,
    pick_start_time BIGINT,
    term STRING,
    update_by BIGINT,
    pick_end_time BIGINT
) WITH ( 
 'connector' = 'kafka',
 'topic' = 'ods.mysql.service_geek_coin.mall_activity',
 'properties.bootstrap.servers' = 'devdata1:9092,devdata2:9092,devdata3:9092',
 'properties.group.id' = 'ods_mall_activity_20231115',
 'format' = 'debezium-json',
 'scan.startup.mode' = 'earliest-offset'
);

DROP TABLE IF EXISTS mall_activity;
CREATE TABLE mall_activity (
    id                     bigint,
    create_time            TIMESTAMP,
    create_by              bigint,
    create_by_name         string,
    update_time            TIMESTAMP,
    update_by              bigint,
    update_by_name         string,
    activity_name          string,
    appointment_start_time TIMESTAMP,
    end_time               TIMESTAMP,
    exchange_end_time      TIMESTAMP,
    exchange_start_time    TIMESTAMP,
    pick_end_time          TIMESTAMP,
    pick_start_time        TIMESTAMP,
    term                   string,
    `year`                   int,
    PRIMARY KEY (id) NOT ENFORCED
) WITH (
    'connector' = 'starrocks',
    'jdbc-url' = 'jdbc:mysql://devdata2:9030,devdata3:9030,devdata4:9030',
    'load-url' = 'devdata2:8030,devdata3:8030,devdata4:8030',
    'database-name' = 'service_geek_coin',
    'table-name' = 'mall_activity',
    'username' = 'xxxx',
    'password' = 'xxxx',
    'sink.buffer-flush.max-rows' = '1000000',
    'sink.buffer-flush.max-bytes' = '300000000',
    'sink.buffer-flush.interval-ms' = '1000',
    'sink.parallelism' = '1',
    'sink.properties.partial_update' = 'true',
    'sink.properties.column_separator' = '\x01',
    'sink.properties.row_delimiter' = '\x02'
);
insert into mall_activity
select 
    id,
    TO_TIMESTAMP_LTZ(create_time,3) as create_time,
    create_by,
    create_by_name,
    TO_TIMESTAMP_LTZ(update_time,3) as update_time,
    update_by,
    update_by_name,
    activity_name,
    TO_TIMESTAMP_LTZ(appointment_start_time,3) as appointment_start_time,
    TO_TIMESTAMP_LTZ(end_time,3) as end_time,
    TO_TIMESTAMP_LTZ(exchange_end_time,3) as exchange_end_time,
    TO_TIMESTAMP_LTZ(exchange_start_time,3) as exchange_start_time,
    TO_TIMESTAMP_LTZ(pick_end_time,3) as pick_end_time,
    TO_TIMESTAMP_LTZ(pick_start_time,3) as pick_start_time,
    term,
    `year`
from ods_mall_activity_20231118;