AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据仓库AnalyticDB 云原生数据仓库AnalyticDB PostgreSQL版 操作指南 数据湖分析 使用OSS Foreign Table进行数据湖分析 AnalyticDB PostgreSQL 7.0版使用分区外表

AnalyticDB PostgreSQL 7.0版使用分区外表

更新时间：
我的收藏

AnalyticDB PostgreSQL 7.0版新增分区外表功能。当查询语句的WHERE条件命中分区列时，分区表可以有效减少数据拉取量，从而提升查询的执行效率。

与6.0版分区外表的区别

  • 6.0版仅支持LIST分区，7.0版除了LIST分区外，还支持RANGE和HASH两种分区。

  • 相比较6.0版的分区外表语法，7.0版语法更加简洁直观。

创建OSS Server和创建OSS User Mapping

使用OSS FDW前您需要提前创建OSS Server以及OSS User Mapping。

创建分区外表的父表

AnalyticDB PostgreSQL 7.0版在创建分区外表前，需要创建一个指定分区类型的分区空表作为分区外表的父表，分区表创建语法如下：

CREATE TABLE <table_name> ( <column1> <data_type>, <column2> <data_type>, ...)
...
PARTITION BY { RANGE | LIST | HASH } ( <column_name> | <expression>);

更多关于分区表的介绍，请参见表分区定义。

例如创建一个名为ossfdw_parttable的分区表，分区键为pt，示例如下：

  • RANGE分区

    CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    retion TEXT
)
DISTRIBUTED BY (key)
PARTITION BY RANGE(pt);

  • HASH分区

    CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    retion TEXT
)
DISTRIBUTED BY (key)
PARTITION BY HASH(pt);

  • LIST分区

    CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    retion TEXT
)
DISTRIBUTED BY (key)
PARTITION BY LIST(pt);

添加分区外表

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
{ FOR VALUES partition_bound_spec | DEFAULT }
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

  • 其中column_constraint语法如下：

    [ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED }

  • 其中table_constraint语法如下：

    [ CONSTRAINT constraint_name ]
CHECK ( expression ) [ NO INHERIT ]

  • 其中partition_bound_spec语法如下：

    IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

对分区父表添加分区外表操作示例如下：

  • RANGE分区

    CREATE FOREIGN TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES FROM ('2023-02-01') TO ('2023-03-01')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/', format 'csv', DELIMITER '|');

  • HASH分区

    CREATE FOREIGN TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES WITH (modulus 8, remainder 0)
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES WITH (modulus 8, remainder 0)
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/', format 'csv', DELIMITER '|');

  • LIST分区

    CREATE FOREIGN TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-01')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-02')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/', format 'csv', DELIMITER '|');

添加二级分区外表

二级分区外表与一级分区外表语法一致，需保证最末级分区为外表定义，即创建父表和一级分区时使用内部分区表语法，二级分区使用外部分区表语法。

内部分区表语法请参见表分区定义，外部分区表语法请参见添加分区外表。

此处以创建LIST分区表为例，示例如下：

  1. 创建LIST分区父表。

    CREATE TABLE ossfdw_parttable(
    key TEXT,
    value BIGINT,
    pt TEXT,
    region TEXT
)
DISTRIBUTED BY (key)
PARTITION BY LIST(pt);

  2. 创建一级分区。

    CREATE TABLE ossfdw_parttable_pt_202301
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-01')
 PARTITION BY LIST(region);

CREATE TABLE ossfdw_parttable_pt_202302
 PARTITION OF ossfdw_parttable
 FOR VALUES IN ('2023-02')
 PARTITION BY LIST(region);

  3. 创建二级分区。

    CREATE FOREIGN TABLE ossfdw_parttable_pt_202301_hangzhou
 PARTITION OF ossfdw_parttable_pt_202301
 FOR VALUES IN ('hangzhou')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/hangzhou/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202301_beijing
 PARTITION OF ossfdw_parttable_pt_202301
 FOR VALUES IN ('beijing')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202301/beijing/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302_hangzhou
 PARTITION OF ossfdw_parttable_pt_202302
 FOR VALUES IN ('hangzhou')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/hangzhou/', format 'csv', DELIMITER '|');

CREATE FOREIGN TABLE ossfdw_parttable_pt_202302_beijing
 PARTITION OF ossfdw_parttable_pt_202302
 FOR VALUES IN ('beijing')
 SERVER oss_serv
 OPTIONS (log_errors 'true', segment_reject_limit '10', dir 'ossfdw_parttable/202302/beijing/', format 'csv', DELIMITER '|');

修改分区

ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER FOREIGN TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema

其中action参数可以是以下动作之一：

    ADD [ COLUMN ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] column_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])
    ADD table_constraint [ NOT VALID ]
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    SET WITHOUT OIDS
    INHERIT parent_table
    NO INHERIT parent_table
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
    OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ])

例如需要关闭分区外表的错误日志，操作方式如下：

  1. 在psql上执行\d+命令查看分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202302_beijing

    分区外表信息如下，其中log_errors的参数值为on：

                           Foreign table "public.ossfdw_parttable_pt_202302_beijing"
 Column |  Type  | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+-------------+----------+--------------+-------------
 key    | text   |           |          |         |             | extended |              |
 value  | bigint |           |          |         |             | plain    |              |
 pt     | text   |           |          |         |             | extended |              |
 region | text   |           |          |         |             | extended |              |
Partition of: ossfdw_parttable_pt_202302 FOR VALUES IN ('beijing')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text) AND (region IS NOT NULL) AND (region = 'beijing'::text))
Server: oss_serv
FDW options: (log_errors 'on', segment_reject_limit '10', dir 'ossfdw_parttable/202302/beijing/', format 'csv', delimiter '|')

  2. 修改log_errors参数为off。

    ALTER FOREIGN TABLE ossfdw_parttable_pt_202302_beijing OPTIONS(SET log_errors 'off');

  3. 在psql上再次执行\d+命令查看修改后的分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202302_beijing

    修改后分区外表信息如下，其中log_errors的参数值已变更为off：

                           Foreign table "public.ossfdw_parttable_pt_202302_beijing"
 Column |  Type  | Collation | Nullable | Default | FDW options | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+-------------+----------+--------------+-------------
 key    | text   |           |          |         |             | extended |              |
 value  | bigint |           |          |         |             | plain    |              |
 pt     | text   |           |          |         |             | extended |              |
 region | text   |           |          |         |             | extended |              |
Partition of: ossfdw_parttable_pt_202302 FOR VALUES IN ('beijing')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text) AND (region IS NOT NULL) AND (region = 'beijing'::text))
Server: oss_serv
FDW options: (log_errors 'off', segment_reject_limit '10', dir 'ossfdw_parttable/202302/beijing/', format 'csv', delimiter '|')

分离分区外表

分离分区外表的语法与分离分区表的语法一致。您可以将父表下的指定分区子表解除绑定，分离为独立表。语法如下：

ALTER TABLE <table_name1>
DETACH PARTITION <table_name2>;

参数说明：

参数

说明

table_name1

父表。

table_name2

需要独立的分区子表。

例如，分区表ossfdw_parttable_pt_202302下有两个分区子表，需要将ossfdw_parttable_pt_202302_beijing分离为独立表，示例如下：

  1. 在psql上执行\d+命令查看分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202302

    表结构如下，可以看到有两个分区子表beijing和hangzhou。

                      Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing'),
            ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

  2. 将ossfdw_parttable_pt_202302_beijing从ossfdw_parttable_pt_202302中分离。

    ALTER TABLE ossfdw_parttable_pt_202302 DETACH PARTITION ossfdw_parttable_pt_202302_beijing;

  3. 在psql上再次执行\d+命令查看分离后分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202302

    表结构如下，可以看到仅有一个分区子表hangzhou。

                      Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

添加已有表为分区外表

添加已有表为分区外表的语法与添加已有表为分区表的语法一致。您可以将独立表设置为分区表的分区子表或默认分区。语法如下：

ALTER TABLE <table_name1>
ATTACH PARTITION <table_name2>
{ FOR VALUES <partition_bound> | DEFAULT };

例如，将独立表ossfdw_parttable_pt_202302_beijing设置为分区表ossfdw_parttable_pt_202302的分区子表。示例如下：

  1. 在psql上执行\d+命令查看分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202302

    表结构如下，目前仅有一个分区子表hangzhou。

                      Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

  2. 将ossfdw_parttable_pt_202302_beijing 设置为ossfdw_parttable_pt_202302 的分区子表，VALUES为beijing。

    ALTER TABLE ossfdw_parttable_pt_202302 ATTACH PARTITION ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing');

  3. 在psql上再次执行\d+命令查看添加分区子表后分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202302

    表结构如下，可以看到有两个分区子表beijing和hangzhou。

                      Partitioned table "public.ossfdw_parttable_pt_202302"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-02')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-02'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing'),
            ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

删除分区外表

您可以通过DROP FOREIGN TABLE语法删除指定分区外表。语法如下：

DROP FOREIGN TABLE <table_name>;

例如，删除分区子表ossfdw_parttable_pt_202301_hangzhou，示例如下：

  1. 在psql上执行\d+命令查看分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202301

    表结构如下，可以看到有两个分区子表beijing和hangzhou。

                      Partitioned table "public.ossfdw_parttable_pt_202301"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-01')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-01'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202302_beijing FOR VALUES IN ('beijing'),
            ossfdw_parttable_pt_202302_hangzhou FOR VALUES IN ('hangzhou')
Distributed by: (key)
Access method: heap

  2. 删除分区子表ossfdw_parttable_pt_202301_hangzhou。

    DROP FOREIGN TABLE ossfdw_parttable_pt_202301_hangzhou;

  3. 在psql上再次执行\d+命令查看删除分区子表后分区外表的详细信息。

    \d+ ossfdw_parttable_pt_202301

    表结构如下，可以看到仅有一个分区子表beijing。

                      Partitioned table "public.ossfdw_parttable_pt_202301"
 Column |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------+-----------+----------+---------+----------+--------------+-------------
 key    | text   |           |          |         | extended |              |
 value  | bigint |           |          |         | plain    |              |
 pt     | text   |           |          |         | extended |              |
 region | text   |           |          |         | extended |              |
Partition of: ossfdw_parttable FOR VALUES IN ('2023-01')
Partition constraint: ((pt IS NOT NULL) AND (pt = '2023-01'::text))
Partition key: LIST (region)
Partitions: ossfdw_parttable_pt_202301_beijing FOR VALUES IN ('beijing')
Distributed by: (key)
Access method: heap
上一篇：使用OSS Foreign Table分区表下一篇：使用OSS Foreign Table导入和导出数据
文档推荐