Distribution Policy

{"key": "key1", "op": "op1", "values": ["value1","value2"]}

CREATE DISTRIBUTION POLICY "$policy_name" SET $key1 $op1 ($value1, $value2, ...) AND $key2 $op2 (...) ...;

-- 修改名称为 policy_1 的 DP 规则
ALTER DISTRIBUTION POLICY "policy_1" SET $key $op (...);

-- 将 DP old_dp_name 的名称修改为 new_dp_name
RENAME DISTRIBUTION POLICY "old_dp_name" TO "new_dp_name";

DROP DISTRIBUTION POLICY "$policy_name";

-- 查看已创建的所有 DP
SELECT * FROM information_schema.META_CLUSTER_DPS;

-- 按名称查看指定 DP
SELECT * FROM information_schema.META_CLUSTER_DPS WHERE distribution_policy_name = '$policy_name';

CREATE DATABASE db1 USING distribution policy "$policy_name";

CREATE TABLE t1(a INT) USING distribution policy "$policy_name"; 

CREATE TABLE t1(a INT) PARTITION BY HASH(a) PARTITIONS 4 USING distribution policy "$policy_name";

-- 示例一：将超时的冷数据迁移到挂载了HDD的节点上，需要集群中存在挂载HDD的节点。
CREATE DISTRIBUTION POLICY "policy_x_disk" SET PARTITION_METHOD = "RANGE" AND 
PARTITION_KEY_TO_TIME_TYPE = "predefined:TO_DAYS" AND 
EXPIRE = "1 YEAR" AND
START_TIME = "2024-06-11 00:11:22" AND
END_TIME = "2025-06-11 00:11:22" AND 
STORAGE_TYPE IN ("HDD");

-- 示例二：将超时的冷数据落冷到对象存储上，需要集群开启对象存储层，数据可读写，但存在性能回退与功能限制
CREATE DISTRIBUTION POLICY "policy_x_storage_tier" SET PARTITION_METHOD = "RANGE" AND 
PARTITION_KEY_TO_TIME_TYPE = "predefined:TO_DAYS" AND 
EXPIRE = "1 YEAR" AND
STORAGE_TIER = "OBJECT_STORAGE";

-- 使用预定义函数 UNIX_TIMESTAMP 创建 policy_x1
CREATE DISTRIBUTION POLICY "policy_x1" SET PARTITION_METHOD = "RANGE" AND 
PARTITION_KEY_TO_TIME_TYPE = ("predefined:UNIX_TIMESTAMP") AND 
EXPIRE = "1 MONTH" AND
STORAGE_TYPE IN ("HDD");

-- 创建 RANGE 分区表并绑定 policy_x1
CREATE TABLE t_order_1(
  id bigint NOT NULL, 
  gmt_modified timestamp NOT NULL) 
  PARTITION BY RANGE(unix_timestamp(gmt_modified))(
   PARTITION p1 VALUES LESS THAN(unix_timestamp('2025-11-11')), 
   PARTITION p2 VALUES LESS THAN(unix_timestamp('2025-12-11'))
 ) USING DISTRIBUTION POLICY policy_x1;

-- 使用自定义函数创建 policy_x2
CREATE DISTRIBUTION POLICY "policy_x2" SET PARTITION_METHOD = "RANGE" AND 
PARTITION_KEY_TO_TIME_TYPE = ("year:v/100", "month:v%100") AND 
EXPIRE = "1 MONTH" AND
LEADER_PREFERENCES = (ZONE in ("zone1")) AND
START_TIME = "2025-12-10 00:00:00";

-- 创建 RANGE 分区表并绑定 policy_x2
CREATE TABLE t_order_2(
  id bigint NOT NULL, 
  gmt_modified datetime NOT NULL) 
  PARTITION BY RANGE(YEAR(gmt_modified) * 100 + MONTH(gmt_modified))(
   PARTITION p1 VALUES LESS THAN(202511), 
   PARTITION p2 VALUES LESS THAN(202512),
   PARTITION p3 VALUES LESS THAN(202601)
 ) USING DISTRIBUTION POLICY policy_x2;

自定义函数: year:v/100, month:v%100

分区边界值 v = YEAR(gmt_modified) * 100 + MONTH(gmt_modified)

示例计算:
- p1: v=202511 → year=202511/100=2025, month=202511%100=11
- p2: v=202512 → year=202512/100=2025, month=202512%100=12  
- p3: v=202601 → year=202601/100=2026, month=202601%100=1

-- 创建 policy_x3
CREATE DISTRIBUTION POLICY "policy_x3" SET PARTITION_METHOD = "RANGE COLUMNS" AND 
EXPIRE = "1 YEAR" AND
NODE NOT IN ("node-tdsql3-x-001") AND
START_TIME = "2025-12-10 00:00:00" AND
END_TIME = "2026-12-10 00:00:00";

-- 创建 RANGE COLUMNS 分区表并绑定 policy_x3
CREATE TABLE t_order_3(
  id bigint NOT NULL, 
  gmt_modified datetime NOT NULL) 
  PARTITION BY RANGE COLUMNS(gmt_modified) (
   PARTITION p1 VALUES LESS THAN("2024-12-10 00:00:00"), 
   PARTITION p2 VALUES LESS THAN("2025-12-10 00:00:00"),
   PARTITION p3 VALUES LESS THAN("2026-12-10 00:00:00"),
   PARTITION p4 VALUES LESS THAN("2027-12-10 00:00:00")
 ) USING DISTRIBUTION POLICY policy_x3;

-- 创建 policy_x4，对满足SQL规则命名的分区进行向对象存储的落冷
CREATE DISTRIBUTION POLICY 'policy_x4' SET
  PARTITION_METHOD = 'RANGE' AND
  PARTITION_KEY_TO_TIME_TYPE = ('sql:STR_TO_DATE(CONCAT(BOUND,''01''),''%Y%m%d'')') AND
  EXPIRE = '6 MONTH' AND
  STORAGE_TIER = OBJECT_STORAGE;
  
-- 示例1：创建分区表并绑定 policy_x4
CREATE TABLE t_order_4 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  yyyymm INT NOT NULL,
  data VARCHAR(255) DEFAULT '',
  PRIMARY KEY (id, yyyymm)
) PARTITION BY RANGE (yyyymm) (
  PARTITION p202006 VALUES LESS THAN (202007),
  PARTITION p202106 VALUES LESS THAN (202107),
  PARTITION p209906 VALUES LESS THAN (209907),
  PARTITION p299912 VALUES LESS THAN (299912)
) USING DISTRIBUTION POLICY policy_x4;

-- 示例2：创建子分区表并绑定 policy_x4
CREATE TABLE t_order_4_sub (
  id BIGINT NOT NULL,
  yyyymm INT NOT NULL,
  data VARCHAR(255) DEFAULT '',
  PRIMARY KEY (id, yyyymm)
) PARTITION BY RANGE (yyyymm)
  SUBPARTITION BY HASH(id)
  SUBPARTITIONS 2
(
  PARTITION p202006 VALUES LESS THAN (202007),
  PARTITION p202106 VALUES LESS THAN (202107),
  PARTITION p209906 VALUES LESS THAN (209907),
  PARTITION p299912 VALUES LESS THAN (299912)
) USING DISTRIBUTION POLICY policy_x4;