CREATE PARTITION TABLE的用法 - 实时数仓Hologres

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只能取唯一值。
若是表有主键，分区键必须是主键的一个子集。

注意事项

如果单日分区数据小于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下创建不带主键的分区父表和对应的分区子表。

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：CREATE PARTITION TABLE