CREATE PARTITION TABLE语句用于创建分区表。本文为您介绍CREATE PARTITION TABLE的用法。

命令说明

Partition Table，也叫分区表。父表按分区键（Partition Key）的值划分为不同的子表，子表对外可见。下文无特殊说明的父表和子表皆指分区父表和子表。

分区表在使用时，需要提前创建子表。create partition table命令被用于创建分区表。也可以使用动态分区表管理功能自动创建分区子表。

分区表的不同分区子表采用不同的文件存储，查询时带上分区条件，指定所需查询的分区，避免全表扫描，快速定位存储文件，提高处理效率。通常将事实表按照日期划分为不同的分区表。分区表的任何一个子表在元数据存储上等同于一个非分区表，因此分区多会造成一定程度的元数据膨胀，以及小文件碎片。

如果您的数据来源于数据库，不建议使用分区表。过多的分区表会引起额外的IO资源浪费，为改善此问题并实现索引过滤加速查询的效果，您可以将常用分区条件作为segment_key。

使用限制

  • Hologres暂不支持插入数据至分区表父表，只支持插入数据至具体的分区表子表。
    说明 实时计算Flink版支持实时写入数据至Hologres的分区表父表，详情请参见实时写入数据至Hologres的分区结果表。
  • 一个分区规则只能创建一个分区表。
  • PARTITION BY类型仅支持LIST分区，切分PARTITION BY LIST只能取唯一值。
  • 分区父表和子表必须要在同一个Schema。
  • 若是表有主键，分区键必须是主键的一个子集。
  • 动态分区管理功能仅Hologres V1.3及以上版本支持，并且暂不支持Date类型作为分区键时的动态分区管理。请在Hologres管理控制台查看当前实例版本，如果您的实例是V1.3以下版本，请您提交工单或加入在线支持钉钉群申请升级实例。

注意事项

  • 如果单日分区数据小于1亿条，不建议使用日作为分区条件，或创建分区表。分区表太小，查询加速效果不明显，可以选择较大粒度的分区。
  • 如果您需要经常对某分区数据进行整体替换，包括执行truncate操作或者drop操作，建议使用分区表。针对该场景，执行truncate或者drop效果更好，可以避免大范围的删除操作。

创建分区表

  • 命令格式。
    创建分区表的命令格式如下。
    --创建分区父表语句
CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
PARTITION BY LIST(<column_name>);

--创建分区子表语句
CREATE TABLE [if not exists] [<schema_name>.]<table_name> PARTITION OF <parent_table>
  FOR VALUES IN (<string_literal>);
  • 参数说明。
    创建分区表的参数说明如下。
    参数 说明
    if not exists 如果已经存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表关系已存在。
    schema_name 表所在的schema名称，若是在同一个schema创建父表和子表，可以不需要指定schema名称。若是需要跨schema创建父表和子表，需要指定schema名称。
    table_name 需要创建的分区父表或分区子表的名称。
    column_name 新表中要创建的字段名。
    column_type 字段的数据类型。
    column_constraints 列约束的名称。
    table_constraints 表约束的名称。
    parent_table 子表对应的父表名称。
    string_literal 分区键。
  • 支持将TEXT、VARCHAR以及INT类型的数据作为分区键（Partition Key），V1.3.22及以上版本支持将DATE类型设为分区键。
  • 使用示例。
    创建分区表的SQL语句示例如下。
    • 示例1：在public schema下创建不带主键的分区父表和对应的分区子表。
      BEGIN;
CREATE TABLE public.hologres_parent(
  a text, 
  b int, 
  c timestamp, 
  d text
) 
  PARTITION BY LIST(a);
CALL set_table_property('public.hologres_parent', 'orientation', 'column');           
CREATE TABLE public.hologres_child1 PARTITION OF public.hologres_parent FOR VALUES IN('v1');
CREATE TABLE public.hologres_child2 PARTITION OF public.hologres_parent FOR VALUES IN('v2');
CREATE TABLE public.hologres_child3 PARTITION OF public.hologres_parent FOR VALUES IN('v3');
COMMIT;
    • 示例2：在public schema下创建带主键的分区父表和对应的分区子表。
      BEGIN;
CREATE TABLE public.hologres_parent_2(
  a text , 
  b int, 
  c timestamp, 
  d text,
  ds text,
  primary key(ds,b)
  )
  PARTITION BY LIST(ds);
CALL set_table_property('public.hologres_parent_2', 'orientation', 'column');
CREATE TABLE public.holo_child_1 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201215');
CREATE TABLE public.holo_child_2 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201216');
CREATE TABLE public.holo_child_3 PARTITION OF public.hologres_parent_2 FOR VALUES IN('20201217');
COMMIT;

查看所有分区子表

您可以通过如下两种方法查看当前分区父表下所有的分区子表：
  • 通过HoloWeb可视化查看，HoloWeb会展示分区父表下面的所有分区子表。
  • 通过执行如下命令语句，查看当前分区父表下所有的分区子表。其中，您可以将parent_table_name修改为实际的父表名称。
    SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

分区子表和父表约束一览表

分区子表在绑定父表时，其约束关系如下表所示。其中：
  • 与父表保持一致：即对应的属性分区子表必须和父表保持一致，若不一致，在分区子表绑定（ATTACH）父表时会报错，需要重新创建分区子表。
  • 不要求与父表一致：即对应的属性分区子表可以与父表不一致，如果子表没有显式指定属性，则会继承父表的属性，若是子表显式指定属性，则会保留子表的属性。
  • 索引列必须包含父表的索引列：即分区子表的索引列必须包含父表的索引列，还能显式再指定其他列。
分类 表属性 描述 create table partition of 时是否继承父表属性 ATTACH时与父表的约束关系
表属性 orientation 表存储格式。 继承 与父表保持一致。
table_group Table Group属性包含Shard Count。 继承 与父表保持一致。
time_to_live_in_seconds 表数据生命周期。 继承 不要求与父表一致。
  • 子表属性未赋值，继承父表属性。
  • 子表属性已赋值，保留子表属性。
索引 primary key 主键。 继承 与父表保持一致。
distribution_key 分布键。 继承 与父表保持一致。
clustering_key 聚簇索引。 继承 与父表保持一致。
event_time_column 分段键。 继承 与父表保持一致。
bitmap_columns 比特编码。 继承 索引列必须包含父表的索引列。
dictionary_encoding_columns 字段编码。 继承 索引列必须包含父表的索引列。
binlog_level 是否开启Binlog。 继承 与父表保持一致。
proxima_vectors 向量检索索引。 继承 索引列必须包含父表的索引列。
列约束 nullable 非空约束。 继承 与父表保持一致。
default value 默认值。 继承 与父表保持一致。

动态分区管理

从Hologres V1.3版本开始支持根据用户指定的规则自动增删分区，减少用户管理分区生命周期的负担，系统会根据用户预设的规则定期运行任务，提前创建分区子表和删除过期分区子表。

  • 配置动态分区
    • 语法说明
      动态分区管理配置既支持在创建分区表时配置动态分区管理属性，也支持建表后修改动态分区管理属性，语法如下所示。
      -- 创建分区表时配置动态分区管理属性
BEGIN;
CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
  {
   <column_name> <column_type> [ <column_constraints>, [...]]
   | <table_constraints>
   [, ...]
  }
])
PARTITION BY LIST(<column_name>);
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_unit', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_zone', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
COMMIT;

-- 修改动态分区管理属性
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.enable', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_unit', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.time_zone', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_precreate', 'xxx');
CALL set_table_property('[<schema_name>.]<table_name>', 'auto_partitioning.num_retention', 'xxx');
    • 参数说明
      参数 是否必选 说明 是否可更新
      auto_partitioning.enable 否 是否启用动态分区管理，取值如下。
      • true：启用动态分区管理。
      • （默认）false：关闭动态分区管理。
      		 是
      auto_partitioning.time_unit 是 动态分区的时间单位，取值如下。
      • HOUR
      • DAY
      • MONTH
      • QUARTER
      • YEAR
      例如配置为DAY则系统将按天进行分区的预创建、删除。       		否
      auto_partitioning.time_zone 否 动态分区时区设置，配置后将按照对应时区时间点进行动态分区管理。您可以使用如下SQL查看可选的时区和offset等。返回结果中的name列即为timezone值，例如Asia/Shanghai。
      SELECT * FROM pg_timezone_names;
      默认值为当前连接的时区。      		 否
      auto_partitioning.num_precreate 否 预创建分区数量，有如下取值。
      • 0: 不进行预创建。
      • [1, 512]：以当前时间点为基准创建分区，建议该值需要大于等于2，默认值为4。
      例如time_unit = DAY，num_precreate = 3 ：如果当前时间为2022-01-10将创建2022-01-10、2022-01-11、2022-01-12三个分区。       		是
      auto_partitioning.num_retention 否 保留历史分区数量，有如下取值。
      • 0：不保留历史分区。
      • （默认）-1：不清理历史分区。
      • 正数 ： 保留N个历史分区，最大值为512。
      例如<time_unit = DAY，num_retention = 3> ：如果当前时间为2022-01-10, 将保留 2022-01-09、2022-01-08 、2022-01-07三个分区；早于2022-01-07的历史分区将被删除。       		是
    • 分区表名生成规则
      自动分区表时间单位time_unit可以配置为day、month、quarter、year，自动分区将使用分区父表名加上时间后缀作为新创建分区的表名；分区表名的生成格式形如：{parent_table}_{time_suffix}， 其中时间后缀将依据自动分区的调度时间和时间单位对应的格式模板生成。具体的对应关系如下。
      time_unit 时间后缀格式 样例 执行时间
      hour YYYYMMDDHH24 2022030117。 每个整点的开始，例如2022年3月1日 01:00:01执行任务。
      day YYYYMMDD 20220301。 每天00:00:01开始，例如2022年3月1日 00:00:01。
      quarter YYYYQ 20221、20222、20223、20224分别表示2022年的四个季度。 每天00:00:01开始，例如2022年3月1日 00:00:01。
      year YYYY 2022、2023分别表示2022年、2023年的分区。 每天00:00:01开始，例如2022年3月1日 00:00:01。
    • 使用样例
      如下SQL将以天为时间单位，预先创建未来3天的分区，保留近2天的历史分区，并将时区设置为'Asia/Shanghai'。
      -- 创建分区表，并配置动态分区管理：
BEGIN;
CREATE TABLE tbl1 (
    c1 text NOT NULL,
    c2 int
)
PARTITION BY LIST (c2);
CALL set_table_property ('tbl1', 'auto_partitioning.enable', 'true');
CALL set_table_property ('tbl1', 'auto_partitioning.time_unit', 'DAY');
CALL set_table_property ('tbl1', 'auto_partitioning.time_zone', 'Asia/Shanghai');
CALL set_table_property ('tbl1', 'auto_partitioning.num_precreate', '3');
CALL set_table_property ('tbl1', 'auto_partitioning.num_retention', '2');
COMMIT;
      执行结果如下。
      时间 事件 结果
      2022-01-10 09:00:00 执行如上SQL，创建分区表。 系统创建分区父表：tbl1；系统创建分区子表：tbl1_20220110、tbl1_20220111、tbl1_20220112。
      2022-01-11 00:00:00 系统自动创建分区子表。 系统创建分区子表：tbl1_20220113。
      2022-01-12 00:00:00 系统自动创建分区子表。 系统创建分区子表：tbl1_20220114。
      2022-01-13 00:00:00 系统自动创建分区子表，并清理分区子表。 系统创建分区子表：tbl1_20220115；系统清理分区子表：tbl1_20220110。
      2022-01-14 00:00:00 系统自动创建分区子表，并清理分区子表。 系统创建分区子表：tbl1_20220116；系统清理分区子表：tbl1_20220111。
  • 保留指定分区子表
    • 语法说明
      默认情况下，系统将按动态分区配置自动创建和删除分区子表，不在保留范围内的分区子表将被自动删除。但在一些特殊场景中，可能需要保留重要分区的数据（例如在电商场景中，需要保留历年双11数据进行同环比分析），改场景就可以使用保留指定分区子表的功能，通过给表增加keep_alive属性的方式避免需要保留的分区子表被自动清理。
      -- 增加保留分区
call set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'true');

-- 删除保留分区：删除保留属性后，动态分区管理会立即触发过期数据清理
call set_table_property('[<schema_name>.]<table_name>', 'keep_alive', 'false');
      schema_name为需要保留的分区子表所在的Schema名称；table_name为需要保留的分区子表的表名称。
    • 使用示例
      • 使用如下SQL将tbl1_20220111保留，使其不会被动态分区机制自动清理。
        -- 增加保留分区
call set_table_property('pulbic.tbl1_20220111', 'keep_alive', 'true');
      • 使用如下SQL将tbl1_20220111解除保留，使其会被动态分区机制自动清理。
        -- 删除保留分区：删除保留属性后，动态分区管理会立即触发过期数据清理
call set_table_property('pulbic.tbl1_20220111', 'keep_alive', 'false');
  • 查看配置动态分区配置和调度情况
    可以使用如下SQL查询当前数据库中配置了分区表和对应的动态分区策略参数。
    SELECT
    nsp_name AS schema_name,
    tbl_name AS table_name,
    enable,
    time_unit,
    time_zone,
    num_precreate,
    num_retention,
    b.usename AS create_user,
    cret_time,
    schd_time,
    options
FROM
    hologres.hg_partitioning_config AS a
    LEFT JOIN pg_user AS b ON a.cret_user = b.usesysid;
    查询结果样例如下。查看配置动态分区样例结果列的含义如下。
    列名称 说明
    schema_name Schema名称。
    table_name 表名称。
    ENABLE 是否启用动态分区管理。
    time_unit 动态分区的时间单位。
    time_zone 动态分区时区设置。
    num_precreate 预创建分区数量。
    num_retention 保留历史分区数量。
    cret_time 创建时间。
    schd_time 最近一次计划调度时间。
    options 其他。
  • 查看创建和清除分区子表日志
    创建和清除分区子表的日志不会出现在Query Log中，您可以使用如下SQL查询创建和清除分区子表日志。
    SELECT
    relname,
    relowner,
    schdtime,
    trigtime,
    status,
    message,
    precreate,
    discard
FROM
    hologres.hg_partitioning_log;
    查询结果样例如下。查看和清除分区子表日志样例结果列的含义如下。
    列名 说明
    relname schema.table即{Schema名称}.{表名称}。
    relowner 表的Owner。
    schdtime 计划调度时间。
    trigtime 实际触发时间。
    status 状态。
    message 备注。
    precreate 创建的分区子表名。
    discard 清理的分区子表名。

常见问题

  • 对于存量分区表如何开启动态分区？
    对于存量分区表您可以使用如下SQL打开动态分区。
    重要 由于auto_partitioning.time_unit和auto_partitioning.time_zone为动态分区功能的核心配置，所以升级后仅允许设置一次，设置后不能再更改。 
    -- SQL样例
BEGIN;
CALL set_table_property('auto_part_old', 'auto_partitioning.enable', 'true');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_unit', 'HOUR');
CALL set_table_property('auto_part_old', 'auto_partitioning.time_zone', 'PRC');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_precreate', '4');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_retention', '-1');
CALL set_table_property('auto_part_old', 'auto_partitioning.num_hot', '-1');
COMMIT;
  • 对于存量表开启动态分区后，历史存在的分区子表是否会收到自动清理逻辑的影响？

    系统根据分区子表的名称进行分区子表清理，若分区子表的名称满足{parent_table}_{time_suffix}此类命名规则，则会被清理，如果不满足则不会被清理。