测试环境10.0.12.14/15/17客诉表tbl_market_activity_log_info_ulsp.txt
--14/15/17三张表
CREATE TABLE IF NOT EXISTS `upsf_ods_upb`.`tbl_market_activity_log_info_ulsp`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = Distributed(up_ck_cluster, '', `upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`, rand());
CREATE TABLE IF NOT EXISTS `_cw_distributed_db`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = Distributed(up_ck_cluster, '', `upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`, rand());
--10.0.12.14shard1shard3
CREATE TABLE IF NOT EXISTS `shard1`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica1')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
CREATE TABLE IF NOT EXISTS `shard3`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/3/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica2')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
--10.0.12.15shard1shard2
CREATE TABLE IF NOT EXISTS `shard1`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica2')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
CREATE TABLE IF NOT EXISTS `shard2`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/2/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica1')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
--10.0.12.17shard2shard3
CREATE TABLE IF NOT EXISTS `shard2`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/2/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica2')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;
CREATE TABLE IF NOT EXISTS `shard3`.`upsf_ods_upb_tbl_market_activity_log_info_ulsp_replica`
(
`transDt` String,
`discountId` String,
`cardNo` String,
`mobile` String,
`userId` String,
`logId` String,
`transChnl` Nullable(String),
`mchntCd` Nullable(String),
`orderAt` Nullable(String),
`icTmn` Nullable(String),
`activityNm` Nullable(String),
`factor` Nullable(String),
`factorName` Nullable(String),
`ruleTp` Nullable(String),
`resultCd` Nullable(String),
`hostname` Nullable(String),
`message` Nullable(String),
`id` Nullable(String),
`collectiontime` Nullable(String),
`dataset` Nullable(String),
`path` Nullable(String),
`rownumber` Nullable(String),
`ip` Nullable(String),
`streamtype` Nullable(String),
`date` Nullable(String),
`processtype` Nullable(String),
`parsingtime` Nullable(String),
`version` Nullable(String),
`topic` Nullable(String),
`record` Nullable(String),
`_cw_insert_time` DateTime MATERIALIZED now(),
`_cw_uuid` UUID MATERIALIZED generateUUIDv4()
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/3/upsf_ods_upb/tbl_market_activity_log_info_ulsp', 'replica1')
PARTITION BY subString(transDt,1,10)
ORDER BY (transDt,discountId,cardNo,mobile,userId,logId)
SETTINGS index_granularity = 8192;