分区、分桶、明细表
测试用户表
CREATE TABLE user_info(
dt DATE,
id varchar(36) NOT NULL DEFAULT '10',
user_name varchar(36),
age tinyint,
phone varchar(11),
province varchar(10),
city varchar(10),
create_time datetime,
update_time datetime
)ENGINE=OLAP --引擎
DUPLICATE KEY(dt, id, user_name) --排序键
PARTITION BY RANGE(dt)(
PARTITION p20210718 VALUES LESS THAN ("2021-07-18"),
PARTITION p20210719 VALUES LESS THAN ("2021-07-19"),
PARTITION p20210720 VALUES LESS THAN ("2021-07-20"),
PARTITION p20210721 VALUES LESS THAN ("2021-07-21")
)
DISTRIBUTED BY HASH(dt, id) BUCKETS 10 --分桶数
PROPERTIES(
"storage_type" = "COLUMN", --可选#存储方式，旧的支持行存储，默认列存储
"bloom_filter_columns" = "user_name", --可选#BloomFilter索引
"replication_num" = "3", --可选#副本数，默认3
"in_memory" = "false", --可选#是否是内存表
"colocate_with" = "group1", --可选#Colocation Join用的分组，可以本地join
"dynamic_partition.enable" = "true", --打开动态分区
"dynamic_partition.time_unit" = "DAY", --动态分区调度的粒度
"dynamic_partition.start" = "-10", --动态分区开始时间
"dynamic_partition.end" = "1", --动态分区结束时间
"dynamic_partition.prefix" = "p", --动态创建的分区名前缀
"dynamic_partition.buckets" = "10" --动态创建的分区桶数量
);插入测试数据
INSERT INTO user_info VALUES('2021-07-20 12:51:29',"123",'wangshida',18,'19920132232','河北','石家庄','2017-10-31 12:51:29','2017-10-31 12:51:29')
批量导入
curl --location-trusted -u root -H "column_separator:," -T load_user_txt_500w.txt http://127.0.0.1:8030/test库名/event表名/_stream_load
测试订单表
CREATE TABLE order_item(
dt DATE,
user_id varchar(36),
id varchar(36) NOT NULL,
product_count int(11),
price decimal(10,0),
create_time datetime,
update_time datetime
)ENGINE=OLAP --引擎
DUPLICATE KEY(dt, user_id, id) --排序键
PARTITION BY RANGE(dt)(
PARTITION p20210718 VALUES LESS THAN ("2021-07-18"),
PARTITION p20210719 VALUES LESS THAN ("2021-07-19"),
PARTITION p20210720 VALUES LESS THAN ("2021-07-20"),
PARTITION p20210721 VALUES LESS THAN ("2021-07-21")
)
DISTRIBUTED BY HASH(dt, user_id) BUCKETS 10 --分桶数
PROPERTIES(
"storage_type" = "COLUMN", --可选#存储方式，旧的支持行存储，默认列存储
"bloom_filter_columns" = "user_id", --可选#BloomFilter索引
"replication_num" = "3", --可选#副本数，默认3
"in_memory" = "false", --可选#是否是内存表
"colocate_with" = "group1", --可选#Colocation Join用的分组，可以本地join
"dynamic_partition.enable" = "true", --打开动态分区
"dynamic_partition.time_unit" = "DAY", --动态分区调度的粒度
"dynamic_partition.start" = "-10", --动态分区开始时间
"dynamic_partition.end" = "1", --动态分区结束时间
"dynamic_partition.prefix" = "p", --动态创建的分区名前缀
"dynamic_partition.buckets" = "10" --动态创建的分区桶数量
);增加字段
简单模型
ADD COLUMN column_name column_type KEY
聚合模型加value列
ADD COLUMN column_name column_type agg_type
从指定 index 中删除一列
DROP COLUMN column_name FROM rollup_index_name
对指定index列重新排序
ORDER BY (column_name1, column_name2, ...) FROM rollup_index_name PROPERTIES ("key"="value", ...)