全部产品
Search
文档中心

实时数仓Hologres:CREATE PARTITION TABLE

更新时间:Mar 01, 2024

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只能取唯一值。

  • 若是表有主键,分区键必须是主键的一个子集。

  • 动态分区管理功能仅Hologres V1.3及以上版本支持;在Hologres V2.1.11及以上版本支持Date类型作为分区键时的动态分区管理。请在Hologres管理控制台查看当前实例版本,如果您需要使用对应版本的功能,请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

注意事项

  • 如果单日分区数据小于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

    分区键。

  • 支持将TEXTVARCHAR以及INT类型的数据作为分区键(Partition Key),V1.3.22及以上版本支持将DATE类型设为分区键。

  • 使用示例。

    创建分区表的SQL语句示例如下。

    • 示例1:在public schema下创建不带主键的分区父表和对应的分区子表。

      • V2.1版本起支持的语法:

        BEGIN;
        CREATE TABLE public.hologres_parent (
            a text,
            b int,
            c timestamp,
            d text
        )
        PARTITION BY LIST (a) 
        WITH (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;
      • 所有版本支持的语法:

        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下创建带主键的分区父表和对应的分区子表。

      V2.1版本起支持的语法:

      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) 
      WITH (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;

      所有版本支持的语法:

      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版本开始支持根据用户指定的规则自动增删分区,减少用户管理分区生命周期的负担,系统会根据用户预设的规则定期运行任务,提前创建分区子表和删除过期分区子表。从V1.3.37版本开始支持动态管理冷热数据分层存储,来实现数据自动降冷,降低存储成本,详情请参见数据分层存储

  • 配置动态分区

    • 语法说明

      动态分区管理配置既支持在创建分区表时配置动态分区管理属性,也支持建表后修改动态分区管理属性,语法如下所示。

      • V2.1版本起支持的语法:

        -- 创建分区表时配置动态分区管理属性
        CREATE TABLE [if not exists] [<schema_name>.]<table_name>  ([
          {
           <column_name> <column_type> [ <column_constraints>, [...]]
           | <table_constraints>
           [, ...]
          }
        ])
        PARTITION BY LIST(<column_name>)
        WITH (
           auto_partitioning_enable = 'xxx',
           auto_partitioning_time_unit = 'xxx',
           auto_partitioning_time_zone = 'xxx',
           auto_partitioning_num_precreate = 'xxx',
           auto_partitioning_num_retention = 'xxx'
        );
        
        -- 修改动态分区管理属性
        ALTER TABLE [<schema_name>.]<table_name> SET (
           auto_partitioning_enable = 'xxx',
           auto_partitioning_num_precreate = 'xxx',
           auto_partitioning_num_retention = 'xxx'
        );
        
      • 所有版本支持的语法:

        -- 创建分区表时配置动态分区管理属性
        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');
    • 参数说明

      重要

      使用CREATE TABLE WITH语法配置动态分区时,需要将参数名中的.改为_。例如在Hologres V2.1版本中使用CREATE TABLE WITH语法配置动态分区时,auto_partitioning.enable参数名需改为auto_partitioning_enable

      参数

      是否必选

      说明

      是否可更新

      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。可通过set hg_experimental_auto_part_max_maintained_partitions=<value>;命令来调整保留分区值个数,最大值为8760。

      例如<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'。

      V2.1版本起支持的语法

      1. 创建分区表tbl1

        -- 2.1版本创建分区表,并配置动态分区管理:
        
        CREATE TABLE tbl1 (
            c1 text NOT NULL,
            c2 text 
        )
        PARTITION BY LIST (c2)
        WITH (
           auto_partitioning_enable = 'true',
           auto_partitioning_time_unit = 'DAY',
           auto_partitioning_time_zone = 'Asia/Shanghai',
           auto_partitioning_num_precreate = '3',
           auto_partitioning_num_retention = '2'
        );
        
      2. 待子分区生成后插入数据:

        INSERT INTO tbl1 (c1, c2) VALUES ('Data 1', '20231212');
        INSERT INTO tbl1 (c1, c2) VALUES ('Data 2', '20231213');
        INSERT INTO tbl1 (c1, c2) VALUES ('Data 3', '20231214');
      3. 查询数据:

        SELECT * FROM tbl1 WHERE c2='20231212';

        返回结果如下:

        c1	     c2
        Data 1   20231212

      所有版本支持的语法

      1. 创建分区表tbl1

        -- 创建分区表,并配置动态分区管理:
        BEGIN;
        CREATE TABLE tbl1 (
            c1 text NOT NULL,
            c2 text 
        )
        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;
      2. 待子分区生成后插入数据:

        INSERT INTO tbl1 (c1, c2) VALUES ('Data 1', '20231212');
        INSERT INTO tbl1 (c1, c2) VALUES ('Data 2', '20231213');
        INSERT INTO tbl1 (c1, c2) VALUES ('Data 3', '20231214');
      3. 查询数据:

        SELECT * FROM tbl1 WHERE c2='20231212';

        返回结果如下:

        c1	     c2
        Data 1   20231212

      分区增减的逻辑如下:

      时间

      事件

      结果

      2023-12-12 09:00:00

      执行如上SQL,创建分区表。

      系统创建分区父表:tbl1;系统创建分区子表:tbl1_20231212、tbl1_20231213、tbl1_20231214。

      2023-12-13 00:00:00

      系统自动创建分区子表。

      系统创建分区子表:tbl1_20231215。

      2023-12-14 00:00:00

      系统自动创建分区子表。

      系统创建分区子表:tbl1_20231216。

      2023-12-15 00:00:00

      系统自动创建分区子表,并清理分区子表。

      系统创建分区子表:tbl1_20231217;系统清理分区子表:tbl1_20231212。

      2023-12-16 00:00:00

      系统自动创建分区子表,并清理分区子表。

      系统创建分区子表:tbl1_20231218;系统清理分区子表:tbl1_20231213。

  • 保留指定分区子表

    • 语法说明

      默认情况下,系统将按动态分区配置自动创建和删除分区子表,不在保留范围内的分区子表将被自动删除。但在一些特殊场景中,可能需要保留重要分区的数据(例如在电商场景中,需要保留历年双11数据进行同环比分析),改场景就可以使用保留指定分区子表的功能,通过给表增加keep_alive属性的方式避免需要保留的分区子表被自动清理。

      • V2.1版本起支持的语法:

        -- 增加保留分区
        ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'true');
        
        -- 删除保留属性后,动态分区管理会立即触发过期数据清理
        ALTER TABLE [<schema_name>.]<table_name> SET (keep_alive = 'false');
        
      • 所有版本支持的语法:

        -- 增加保留分区
        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保留,使其不会被动态分区机制自动清理。

        • V2.1版本起支持的语法:

          -- 增加保留分区
          ALTER TABLE pulbic.tbl1_20220111 SET (keep_alive = 'true');
        • 所有版本支持的语法:

          -- 增加保留分区
          call set_table_property('pulbic.tbl1_20220111', 'keep_alive', 'true');
      • 使用如下SQL将tbl1_20220111解除保留,使其会被动态分区机制自动清理。

        • V2.1版本起支持的语法:

          -- 删除保留分区:删除保留属性后,动态分区管理会立即触发过期数据清理
          ALTER TABLE pulbic.tbl1_20220111 SET (keep_alive = 'false');
        • 所有版本支持的语法:

          -- 删除保留分区:删除保留属性后,动态分区管理会立即触发过期数据清理
          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 
    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

    最近一次计划调度时间。

  • 查看创建和清除分区子表日志

    创建和清除分区子表的日志不会出现在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_unitauto_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}此类命名规则,则会被清理,如果不满足则不会被清理。

  • 创建动态分区,指定了auto_partitioning_num_precreate值为3,但是执行完SQL后分区父表创建成功,为什么没有创建三个分区子表?

    对于首次创建动态分区的任务,后台默认每10分钟检查一次,因此分区子表会在10分钟内被创建,请稍后查看。