tencent cloud

CREATE TABLE `jdbc_source_table` (
    `id` INT,
    `name` STRING
) WITH (
    -- Specify the options for database connection.
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.1.28.93:3306/CDB?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai', -- Replace it with your MySQL database URL.
    'table-name' = 'my-table',  -- The name of the table to connect.
    ‌'username' = 'admin',      -- The username (with the INSERT permission required) for database access.
    'password' = 'MyPa$$w0rd',  -- The password for database access.
    'scan.partition.column' = 'id',   -- The name of the partitioned scan column.
    'scan.partition.num' = '2',       -- The number of partitions.
    'scan.partition.lower-bound' = '0', -- The minimum value of the first partition.
    'scan.partition.upper-bound' = '100', -- The maximum value of the last partition.
    'scan.fetch-size' = '1' -- The number of rows that should be fetched from the database when reading per round trip.
);

CREATE TABLE `jdbc_dim_table` (
    `id` INT,
    `name` STRING
) WITH (
    -- Specify the options for database connection.
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.1.28.93:3306/CDB?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai', -- Replace it with your MySQL database URL.
    'table-name' = 'my-table',  -- The name of the table to connect.
    ‌'username' = 'admin',      -- The username (with the INSERT permission required) for database access.
    'password' = 'MyPa$$w0rd',  -- The password for database access.
    'lookup.cache.max-rows' = '100',   -- The maximum number of rows of lookup cache.
    'lookup.cache.ttl' = '5000'       -- The max time to live for each row in the Lookup Cache.
);

CREATE TABLE `jdbc_sink_table` (
    `id` INT,
    `name` STRING
) WITH (
    -- Specify the options for database connection.
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.1.28.93:3306/CDB?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai', -- Replace it with your MySQL database URL.
    'table-name' = 'my-table',  -- The name of JDBC table to connect.
    'username' = 'admin',       -- The username (with the INSERT permission required) for database access.
    'password' = 'MyPa$$w0rd',  -- The password for database access.
    'sink.buffer-flush.max-rows' = '200',  -- The maximum size of buffered records before flush.
    'sink.buffer-flush.interval' = '2s'    -- The flush interval.
);

CREATE TABLE `jdbc_upsert_sink_table` (
    `id` INT PRIMARY KEY NOT ENFORCED,
    `name` STRING
) WITH (
    -- Specify the options for database connection.
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.1.28.93:3306/CDB?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai', -- Replace it with your MySQL database URL.
    'table-name' = 'my-upsert-table', -- The name of JDBC table to connect.
    'username' = 'admin',             -- The username (with the INSERT permission required) for database access.
    'password' = 'MyPa$$w0rd',        -- The password for database access.
    'sink.buffer-flush.max-rows' = '200',  -- The maximum size of buffered records before flush.
    'sink.buffer-flush.interval' = '2s'    -- The flush interval.
);

CREATE TABLE `jdbc_source_table` (
    `id` INT,
    `name` STRING
) WITH (
    -- Specify the options for database connection.
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.1.28.93:3306/CDB?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai', -- Replace it with your MySQL database URL.
    'table-name' = 'my-table',  -- The name of the table to connect.
    ‌'username' = 'admin',      -- The username (with the INSERT permission required) for database access.
    'password' = 'MyPa$$w0rd',  -- The password for database access.
    'scan.partition.column' = 'id',   -- The name of the partitioned scan column.
    'scan.partition.num' = '2',       -- The number of partitions.
    'scan.partition.lower-bound' = '0', -- The minimum value of the first partition.
    'scan.partition.upper-bound' = '100', -- The maximum value of the last partition.
    'scan.fetch-size' = '1' -- The number of rows that should be fetched from the database when reading per round trip.
);
CREATE TABLE `jdbc_upsert_sink_table` (
    `id` INT PRIMARY KEY NOT ENFORCED,
    `name` STRING
) WITH (
    -- Specify the options for database connection.
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://10.1.28.93:3306/CDB?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai', -- Replace it with your MySQL database URL.
    'table-name' = 'my-upsert-table', -- The name of JDBC table to connect.
    'username' = 'admin',             -- The username (with the INSERT permission required) for database access.
    'password' = 'MyPa$$w0rd',        -- The password for database access.
    'sink.buffer-flush.max-rows' = '200',  -- The maximum size of buffered records before flush.
    'sink.buffer-flush.interval' = '2s'    -- The flush interval.
);
insert into jdbc_upsert_sink_table select * from jdbc_source_table;