Doris表创建动态分区

Doris创建动态分区表

动态分区仅仅支持range分区

DDL建表语句如下：

CREATE TABLE dwd.`dwd_cdc_t_fin_trade_record_detail` (
 `trade_id` varchar(200) NULL COMMENT "流水号",
 `merchant_code` varchar(200) NOT NULL COMMENT "",
 `order_no_business` varchar(800) NULL COMMENT "订单业务字段(包材费时存包材号，仓储费时存批次号,操作费存冻结记录id)",
 `trade_date` date NULL COMMENT "变动时间",
 `trade_type` int(11) NULL COMMENT "交易类型,1-线下充值,2-操作费,3-仓储费,4-包材,5调账,6线上充值,7贴标服务费,8-退仓存储费",
 `cost_name` varchar(200) NULL COMMENT "费用名称",
 `merchant_name` varchar(400) NULL COMMENT "卖家名称",
 `source` varchar(200) NULL COMMENT "来源",
 `trade_amount` decimal(20, 2) NULL COMMENT "变动金额(充值未正，扣费为负)",
 `currency_code` varchar(40) NULL COMMENT "",
 `packing_material_name` varchar(1020) NOT NULL COMMENT "包材名称",
 `is_active` int(11) NULL COMMENT "数据是否已被逻辑删除,0是已删除，1是未删除",
 `etl_time` datetime NULL COMMENT "跑批日期"
) ENGINE=OLAP
DUPLICATE KEY(`trade_id`,`merchant_code`)
COMMENT "商户余额变动流水表"
PARTITION BY RANGE(`trade_date`)()
DISTRIBUTED BY HASH(`merchant_code`) BUCKETS 3
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "4",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "24",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.start_day_of_month" = "1",
"in_memory" = "false",
"storage_format" = "V2"
);

PARTITION BY RANGE(trade_date)() 根据trade_date字段做分区
“dynamic_partition.enable” = “true” 开启动态分区
“dynamic_partition.start” = “-2147483648”, 系统会根据今天的时间去建分区，如果这里设置为0，则历史的数据（trade_date<今天的日期）不会被插入到表中。想做历史数据的迁移，可以将其设置为负值。
doris默认会建立未来分区

Doris历史分区不够怎么办

新增历史分区

TABLE ods.ods_esc_system_oct_buyer_one_sale_goods SET ("dynamic_partition.enable"

ALTER TABLE ods.ods_esc_system_oct_buyer_one_sale_goods ADD PARTITION p202108 VALUES[('2020-01-01 00:00:00'), ('2021-09-01 00:00:00'));

ALTER TABLE ods.ods_esc_system_oct_buyer_one_sale_goods ADD PARTITION p202109 VALUES[('2021-09-01 00:00:00'), ('2021-10-01 00:00:00'));

ALTER TABLE ods.ods_esc_system_oct_buyer_one_sale_goods SET ("dynamic_partition.enable" = "true")

然后可以查看分区信息

show partitions from ods_esc_system_oct_buyer_one_sale_goods

查看分区表信息

SHOW DYNAMIC PARTITION TABLES;

分区数据做替换

当某个分区数据需要做修改时，可以通过临时分区做替换，临时分区过后会自动删除

– 1.创建临时分区


ALTER TABLE dwd.`dwd_cdc_t_fin_trade_record_detail` 
ADD TEMPORARY PARTITION today 
VALUES [("$[yyyy-MM-dd]"), ("2022-02-01"))
;

– 2.数据插入临时分区

insert into dolphinscheduler.`demo01` TEMPORARY PARTITION(today) (
create_date,col1,col2
)
SELECT create_date,col1,col2
FROM dolphinscheduler.demo02 
WHERE create_date >= 20220101 and create_date < 20220201
;

– 3.临时分区替换正式分区，替换完成后临时分区自动删除

ALTER TABLE dolphinscheduler.`demo01` 
REPLACE PARTITION (p_202201) 
WITH TEMPORARY PARTITION (today)
;

– 4.查询临时分区 SQL

SELECT * 
from dolphinscheduler.`demo01`
TEMPORARY PARTITION today
;

List分区，不支持动态分区

DDL建表语句如下：

CREATE TABLE ods.`ods_esc_buyer_goods_ebay_compatibility_model` (
    `id` varchar(128) NOT NULL COMMENT 'id',
    `create_time` datetime NOT NULL COMMENT 'create_time',
    `category_id` varchar(200) NOT NULL COMMENT 'ebayid',
    `site` varchar(1020) NOT NULL COMMENT '站点',
    `model_id` varchar(128) DEFAULT NULL COMMENT '车型id',
    `esc_category_id` varchar(128) DEFAULT NULL COMMENT 'esc品类id',
    `version` varchar(128) DEFAULT NULL COMMENT '版本号',
    `update_type` int(1) DEFAULT NULL COMMENT '更新类型',
    `update_time` datetime DEFAULT NULL COMMENT 'update_time',
    `deleted` tinyint(1) DEFAULT '0' COMMENT '删除标记',
    `is_deleted_flg` int(11) DEFAULT "0" COMMENT '是否被系統物理刪 0：未被物理删，1：系统已删除',
    `ts_ms` bigint(20) NULL DEFAULT "0" COMMENT '数据拉取时间',
    INDEX idx_site (`site`) USING BITMAP COMMENT '站点索引',
    INDEX idx_category_id (`category_id`) USING BITMAP COMMENT '品类索引',
    INDEX idx_model_id (`model_id`) USING BITMAP COMMENT '车型id索引'
) ENGINE=OLAP
UNIQUE KEY (id,create_time,category_id,site)
COMMENT '品类车型关联表'
PARTITION BY List ( category_id,site )()
DISTRIBUTED BY HASH(id) BUCKETS 10
PROPERTIES (
"function_column.sequence_type" = "bigint",
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);

也可以指定List

PARTITION BY LIST(`id`, `city`)
(
        PARTITION `p1_city` VALUES IN (("1", "Beijing"), ("1", "Shanghai")),
        PARTITION `p2_city` VALUES IN (("2", "Beijing"), ("2", "Shanghai")),
        PARTITION `p3_city` VALUES IN (("3", "Beijing"), ("3", "Shanghai"))
)