tencent cloud

CREATE TABLE clickhouse_sink_table (
   `id` INT,
   `name` STRING
) WITH (
   -- Specify the parameters for database connection.
   'connector' = 'clickhouse',                       -- Specify the connector to use. Here, it should be `clickHouse`.
   'url' = 'clickhouse://172.28.28.160:8123',        -- Specify the cluster address, which can be viewed on the ClickHouse cluster page.
   -- You don't need to specify it if the ClickHouse cluster is not configured with an account and password.
   --'username' = 'root',                            -- The ClickHouse cluster username.
   --'password' = 'root',                            -- The ClickHouse cluster password.
   'database-name' = 'db',                           -- The database to write data to.
   'table-name' = 'table',                           -- The table to write data to.
   'sink.batch-size' = '1000'                        -- The number of data records that trigger a batch write.
);

CREATE TABLE clickhouse_upsert_sink_table (
   `id` INT,
   `name` STRING,
  PRIMARY KEY (`id`) NOT ENFORCED -- Define the primary key here if the database table to monitor has it defined.
) WITH (
   -- Specify the parameters for database connection.
   'connector' = 'clickhouse',                     -- Specify the connector to use. Here, it should be `clickHouse`.
   'url' = 'clickhouse://172.28.28.160:8123',      -- Specify the cluster address, which can be viewed on the ClickHouse cluster page.
   -- You don't need to specify it if the ClickHouse cluster is not configured with an account and password.
   --'username' = 'root',                          -- The ClickHouse cluster username.
   --'password' = 'root',                          -- The ClickHouse cluster password.
   'database-name' = 'db',                         -- The database to write data to.
   'table-name' = 'table',                         -- The table to write data to.
   'table.collapsing.field' = 'Sign',              -- The name of the CollapsingMergeTree type column field.
   'sink.batch-size' = '1000'                      -- The number of data records that trigger a batch write.
);

CREATE TABLE `clickhouse_batch_source` (
    `when`     TIMESTAMP,
    `userid`   BIGINT,
    `bytes`    FLOAT
) WITH (
    'connector' = 'clickhouse',
    'url' = 'clickhouse://172.28.1.21:8123',
    'database-name' = 'dts',
    'table-name' = 'download_dist'
--    'scan.by-part.enabled' = 'false', -- Whether to read the ClickHouse table by part. To enable this, you must first stop the backend merges and TTL-based data deletions of the table using the commands 'STOP MERGES' and 'STOP TTL MERGES' on all nodes; otherwise, an error will occur in data reading.
--    'scan.part.modification-time.lower-bound' = '2021-09-24 16:00:00', -- The minimum `modification_time` (inclusive) for filtering ClickHouse table parts, in the format of `yyyy-MM-dd HH:mm:ss`.
--     'scan.part.modification-time.upper-bound' = '2021-09-17 19:16:26', -- The maximum `modification_time` (exclusive) for filtering ClickHouse table parts, in the format of `yyyy-MM-dd HH:mm:ss`.
--    'local.read-write' = 'false', -- Whether to read the local table. Default value: false.
--    'table.local-nodes' = '172.28.1.24:8123,172.28.1.226:8123,172.28.1.109:8123,172.28.1.36:8123' -- The list of local nodes (the HTTP port number is required). Note that only one replica node address can be configured for each shard; otherwise, duplicate data may be read.
);

CREATE TABLE `clickhouse_dimension` (
    `userid` BIGINT,
    `comment` STRING
) WITH (
    'connector' = 'clickhouse',
    'url' = 'clickhouse://172.28.1.21:8123',
    'database-name' = 'dimension',
    'table-name' = 'download_dist',
    'lookup.cache.max-rows' = '500', -- The maximum number of data records allowed in the Lookup Cache.
    'lookup.cache.ttl' = '10min', -- The maximum cache time of each record.
    'lookup.max-retries' = '10' -- The maximum number of retries upon database query failure.
);

CREATE TABLE datagen_source_table ( 
    id INT, 
    name STRING 
) WITH ( 
   'connector' = 'datagen',
   'rows-per-second'='1'  -- The number of data records generated per second.
);
CREATE TABLE clickhouse_sink_table (
   `id` INT,
   `name` STRING
) WITH (
   -- Specify the parameters for database connection.
   'connector' = 'clickhouse',                       -- Specify the use of the ClickHouse connector.
   'url' = 'clickhouse://172.28.28.160:8123',        -- Specify the cluster address, which can be viewed on the ClickHouse cluster page.
   -- You don't need to specify it if the ClickHouse cluster is not configured with an account and password.
   --'username' = 'root',                            -- The ClickHouse cluster username.
   --'password' = 'root',                            -- The ClickHouse cluster password.
   'database-name' = 'db',                           -- The database to write data to.
   'table-name' = 'table',                           -- The table to write data to.
   'sink.batch-size' = '1000'                        -- The number of data records that trigger a batch write.
);
insert into clickhouse_sink_table select * from datagen_source_table;

CREATE TABLE testdb.testtable on cluster default_cluster (`id` Int32,`name` Nullable(String),`age` Nullable(Int32),`weight` Nullable(Float64),`Sign` Int8) ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/{layer}-{shard}/testdb/testtable', '{replica}', Sign) ORDER BY id SETTINGS  replicated_deduplication_window = 0;

CREATE TABLE testdb.testtable on cluster default_cluster (`id` Int32,`name` Nullable(String),`age` Nullable(Int32),`weight` Nullable(Float64),`Sign` Int8) ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/{layer}-{shard}/testdb/testtable', '{replica}', Sign) ORDER BY id ;

-- Creating a database
CREATE DATABASE test ON cluster default_cluster;

-- Creating a local table
CREATE TABLE test.datagen ON cluster default_cluster (`id` Int32,`name` Nullable(String),`age` Nullable(Int32),`weight` Nullable(Float64),`Sign` Int8) ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/{layer}-{shard}/test/datagen', '{replica}', Sign) ORDER BY id SETTINGS replicated_deduplication_window = 0;

-- Creating a distributed table based on a local table
CREATE TABLE test.datagen_all ON CLUSTER default_cluster AS test.datagen ENGINE = Distributed(default_cluster, test, datagen, id);

-- Creating a database
CREATE DATABASE test ON cluster default_cluster;

-- Creating a local table
CREATE TABLE test.datagen ON cluster default_cluster (`id` Int32,`name` Nullable(String),`age` Nullable(Int32),`weight` Nullable(Float64) ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/test/datagen', '{replica}') ORDER BY id SETTINGS replicated_deduplication_window = 0;

-- Creating a distributed table based on a local table
CREATE TABLE test.datagen_all ON CLUSTER default_cluster AS test.datagen ENGINE = Distributed(default_cluster, test, datagen, id);