ClickHouse 实战 - 劫持项目明细数据 DDL

建表

在每台节点上执行

clickhouse-client -m -h node15 --port 8000  --password 9036011d8fa79028f99488c2de6fdfb09d66beb5e4b8ca5178ee9ff9179ed6a8

clickhouse-client -m -h node16 --port 9000  --password 9036011d8fa79028f99488c2de6fdfb09d66beb5e4b8ca5178ee9ff9179ed6a8

clickhouse-client -m -h node110 --port 9000  --password 9036011d8fa79028f99488c2de6fdfb09d66beb5e4b8ca5178ee9ff9179ed6a8

clickhouse-client -m -h node111 --port 9000  --password 9036011d8fa79028f99488c2de6fdfb09d66beb5e4b8ca5178ee9ff9179ed6a8
CREATE  TABLE dsp_cloak_conversion_dwi (
 repeats Int32 DEFAULT  CAST(0  AS Int32),
 rowkey Nullable(String),
 id Nullable(String),
 campaignid Int32 DEFAULT  CAST(0  AS Int32),
 campaignname Nullable(String),
 landingpageid Int32 DEFAULT  CAST(0  AS Int32),
 offerid Int32 DEFAULT  CAST(0  AS Int32),
 cloakdetail Nullable(String),
 countrycode Nullable(String),
 ip Nullable(String),
 statdate Nullable(String),
 payout Float32 DEFAULT  CAST(0.  AS Float32),
 param1 Nullable(String),
 param2 Nullable(String),
 param3 Nullable(String),
 param4 Nullable(String),
 param5 Nullable(String),
 repeated Nullable(String),
 l_time DateTime,
 b_date Date,
 b_time DateTime
) ENGINE = MergeTree PARTITION  BY (b_date, b_time) ORDER  BY (b_date, b_time) SETTINGS index_granularity = 8192; 

CREATE  TABLE dsp_cloak_conversion_dwi_all AS dsp_cloak_conversion_dwi ENGINE = Distributed(bip_ck_cluster, default, dsp_cloak_conversion_dwi, rand());

CREATE  TABLE dsp_cloak_conversion_dwi_for_select (
 repeats Int32 DEFAULT  CAST(0  AS Int32),
 rowkey Nullable(String),
 id Nullable(String),
 campaignid Int32 DEFAULT  CAST(0  AS Int32),
 campaignname Nullable(String),
 landingpageid Int32 DEFAULT  CAST(0  AS Int32),
 offerid Int32 DEFAULT  CAST(0  AS Int32),
 cloakdetail Nullable(String),
 countrycode Nullable(String),
 ip Nullable(String),
 statdate Nullable(String),
 payout Float32 DEFAULT  CAST(0.  AS Float32),
 param1 Nullable(String),
 param2 Nullable(String),
 param3 Nullable(String),
 param4 Nullable(String),
 param5 Nullable(String),
 repeated Nullable(String),
 l_time DateTime,
 b_date Date,
 b_time DateTime
) ENGINE = MergeTree PARTITION  BY (b_date, b_time) ORDER  BY (b_date, b_time) SETTINGS index_granularity = 8192; 

CREATE  TABLE dsp_cloak_conversion_dwi_for_select_all AS dsp_cloak_conversion_dwi_for_select ENGINE = Distributed(bip_ck_cluster, default, dsp_cloak_conversion_dwi_for_select, rand());
CREATE  TABLE dsp_cloak_request_dwi (
 repeats Int32 DEFAULT  CAST(0  AS Int32),
 rowkey Nullable(String),
 id Nullable(String),
 campaignid Int32 DEFAULT  CAST(0  AS Int32),
 campaignname Nullable(String),
 landingpageid Int32 DEFAULT  CAST(0  AS Int32),
 offerid Int32 DEFAULT  CAST(0  AS Int32),
 cloakdetail Nullable(String),
 countrycode Nullable(String),
 ip Nullable(String),
 statdate Nullable(String),
 payout Float32 DEFAULT  CAST(0.  AS Float32),
 param1 Nullable(String),
 param2 Nullable(String),
 param3 Nullable(String),
 param4 Nullable(String),
 param5 Nullable(String),
 repeated Nullable(String),
 l_time DateTime,
 b_date Date,
 b_time DateTime
) ENGINE = MergeTree PARTITION  BY (b_date, b_time) ORDER  BY (b_date, b_time) SETTINGS index_granularity = 8192; 

CREATE  TABLE dsp_cloak_request_dwi_all AS dsp_cloak_request_dwi ENGINE = Distributed(bip_ck_cluster, default, dsp_cloak_request_dwi, rand());

CREATE  TABLE dsp_cloak_request_dwi_for_select (
 repeats Int32 DEFAULT  CAST(0  AS Int32),
 rowkey Nullable(String),
 id Nullable(String),
 campaignid Int32 DEFAULT  CAST(0  AS Int32),
 campaignname Nullable(String),
 landingpageid Int32 DEFAULT  CAST(0  AS Int32),
 offerid Int32 DEFAULT  CAST(0  AS Int32),
 cloakdetail Nullable(String),
 countrycode Nullable(String),
 ip Nullable(String),
 statdate Nullable(String),
 payout Float32 DEFAULT  CAST(0.  AS Float32),
 param1 Nullable(String),
 param2 Nullable(String),
 param3 Nullable(String),
 param4 Nullable(String),
 param5 Nullable(String),
 repeated Nullable(String),
 l_time DateTime,
 b_date Date,
 b_time DateTime
) ENGINE = MergeTree PARTITION  BY (b_date, b_time) ORDER  BY (b_date, b_time) SETTINGS index_granularity = 8192; 

CREATE  TABLE dsp_cloak_request_dwi_for_select_all AS dsp_cloak_request_dwi_for_select ENGINE = Distributed(bip_ck_cluster, default, dsp_cloak_request_dwi_for_select, rand());