建表语句
CREATE TABLE IF NOT EXISTS base_site_test(
site_id INT DEFAULT '10',
city_code INT,
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
UNIQUE KEY(site_id, city_code)
DISTRIBUTED BY HASH(site_id) BUCKETS 3;导入测试数据
INSERT INTO base_site_test
VALUES("20","10","wangshida",1),
("21","10","xiaohong",2),
("22","10","xiaoming",15)查看表的副本分布，下面是分区表
ADMIN SHOW REPLICA DISTRIBUTION FROM base_site_test;
ADMIN SHOW REPLICA DISTRIBUTION FROM base_site_test PARTITION(p1, p2);
查看表的rollup信息
desc base_site_test all;
其它，分区分桶表
CREATE TABLE site_access(
event_day DATE,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day)(
PARTITION p20200321 VALUES LESS THAN ("2020-03-22"),
PARTITION p20200322 VALUES LESS THAN ("2020-03-23"),
PARTITION p20200323 VALUES LESS THAN ("2020-03-24"),
PARTITION p20200324 VALUES LESS THAN ("2020-03-25")
)
DISTRIBUTED BY HASH(event_day, site_id) BUCKETS 32
PROPERTIES(
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-3",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "32"
);