AnalyticDB for PostgreSQL 6 新特性解析 - FDW

2019-11-15 1269

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

阿里云百炼推荐规格 ADB PostgreSQL，4核16GB 100GB 1个月

云原生数据仓库AnalyticDB MySQL版，基础版 8ACU 100GB 1个月

云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB

简介： ADBPG v6.0版本新特性 -- FDW

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

随着AnalyticDB for PostgreSQL 6（下文简称 ADBPG v6）发布公测，ADBPG v6内核进入PostgreSQL 9.4时代，而PG内核的升级给ADBPG带来更多新的特性，本文简要解析Foreign Data Wrapper（FDW）。

1. SQL/MED

在正式说明FDW之前，我们需要了解FDW的由来，想要了解FDW，那就不得不说SQL/MED。

SQL/MED ("SQL Management of External Data"，SQL标准ISO/IEC 9075-9)，是最早在2001年提出的用于集成管理外部数据的SQL标准ISO/IEC 9075-9:2001
最新发布版本为ISO/IEC 9075-9:2016
主要包含两部分主题：
- Foreign Table a transparent access method for external data
- DATALINK a special SQL type intended to store URLs in database
Foreign Data Wrapper 是SQL/MED标准的一部分，定义访问外部数据源的接口，见下图：

2. FDW for PostgreSQL

2.1 演进

2008年，PostgreSQL为适配SQL/MED标准，提出设计草案：，并于PostgreSQL 8.4版本开始逐步实现。
2009年，PostgreSQL用户大会时，Peter Eisentraut的演讲说明当时PostgreSQL支持SQL/MED的现状与计划：

1570864400648_da44fb11_979e_416f_ac35_c54733f20825
1570955253672_b811996a_521c_475a_8519_2cf26558665b

2011年，PostgreSQL 9.1支持只读方式访问外表（外表查询）
2013年，PostgreSQL 9.3支持可写方式访问外表（外表插入/更新/删除）
... ...

2.2 概况

直至今日，PostgreSQL Global Development Group (PGDG)仅支持：file_fdw 和 postgre_fdw。
更多的FDW可以在the PGXN website找到，但因不是官方支持的版本，需小心使用，更详细的说明见WIKI。

社区PostgreSQL对于SQL/MED标准的支持情况见下图：

2.3 核心

大体来说，PostgreSQL外表功能的核心实现可以简要概括如下：

以 _USER MAPPING _用户（CREATE USER MAPPING）
通过 _FOREIGN DATA WRAPPER _方式（CREATE FOREIGN DATA WRAPPER）
操作在 _FOREIGN SERVER_（CREATE SERVER）上
定义的 _FOREIGN TABLE_（CREATE FOREIGN TABLE）

2.4 不同

在实现外表功能的语法上，PostgreSQL与ISO/IEC 9075-9标准主要有如下不同：

CREATE FOREIGN DATA WRAPPER

语法上扩展了HANDLER和VALIDATOR从句

标准里定义的LIBRARY和LANGUAGE从句并未实现

CREATE FOREIGN TABLE

绝大部分兼容 ISO/IEC 9075-9 (SQL/MED)标准

允许字段空值约束

允许无字段创建外表

字段允许设定默认值也是PostgreSQL的一个功能扩展

3. FDW in ADBPG v6.0

3.1 概况

ADBPG v6中的FDW与社区PostgreSQL基本一致，可以像在PostgreSQL中一样在ADBPG v6中使用FDW功能。需要注意的是：postgres_fdw只能通过master而不能直接通过segment直接访问ADBPG。
语法上，CREATE FOREIGN TABLE，CREATE SERVER 和 CREATE FOREIGN DATA WRAPPER 基本与社区PostgreSQL一致，只是额外增加mpp_execute属性，用于mpp query时，决定通过哪个主机获取外部数据，默认为‘master’。mpp_execute属性的优先级为：FOREIGN TABLE > FOREIGN SERVER > FOREIGN DATA WARPPER

3.2 示例

多说无益，以postgres_fdw为例演示FDW在ADBPG v6.0中的使用。

编译postgres_fdw（当前默认不编译）。
在保证网络可服务的前提下，在10.101.194.174上搭建集群A，在100.81.152.195上搭建集群B，配置如下：

集群A：10.101.194.174

postgres=# select * from gp_segment_configuration ;
 dbid | content | role | preferred_role | mode | status | port  |     hostname     |     address      |                           datadir
------+---------+------+----------------+------+--------+-------+------------------+------------------+--------------------------------------------------------------
    1 |      -1 | p    | p              | n    | u      | 19600 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/qddir/demoDataDir-1
    2 |       0 | p    | p              | s    | u      | 19650 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast1/demoDataDir0
    5 |       0 | m    | m              | s    | u      | 19653 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast_mirror1/demoDataDir0
    3 |       1 | p    | p              | s    | u      | 19651 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast2/demoDataDir1
    6 |       1 | m    | m              | s    | u      | 19654 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast_mirror2/demoDataDir1
    4 |       2 | p    | p              | s    | u      | 19652 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast3/demoDataDir2
    7 |       2 | m    | m              | s    | u      | 19655 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/dbfast_mirror3/demoDataDir2
    8 |      -1 | m    | m              | s    | u      | 19610 | rs1l13368.et2sqa | rs1l13368.et2sqa | /home/const/workspace_adbpg/demo/standby
(8 rows)

集群B：100.81.152.195

postgres=# select * from gp_segment_configuration ;
 dbid | content | role | preferred_role | mode | status | port  |     hostname      |      address      |                        datadir
------+---------+------+----------------+------+--------+-------+-------------------+-------------------+--------------------------------------------------------
    1 |      -1 | p    | p              | n    | u      | 15432 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/qddir/demoDataDir-1
    2 |       0 | p    | p              | s    | u      | 18000 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast1/demoDataDir0
    5 |       0 | m    | m              | s    | u      | 18003 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast_mirror1/demoDataDir0
    3 |       1 | p    | p              | s    | u      | 18001 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast2/demoDataDir1
    6 |       1 | m    | m              | s    | u      | 18004 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast_mirror2/demoDataDir1
    4 |       2 | p    | p              | s    | u      | 18002 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast3/demoDataDir2
    7 |       2 | m    | m              | s    | u      | 18005 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/dbfast_mirror3/demoDataDir2
    8 |      -1 | m    | m              | s    | u      | 16432 | e18b01547.et15sqa | e18b01547.et15sqa | /home/const/workspace/demo/standby
(8 rows)

以集群B（100.81.152.195）作为外部数据源，配置pg_hba.conf文件后reload。

local       all     all                             trust
host        all     all         ::1/128             trust
host        all     all         127.0.0.1/32        trust
host        all     all         0.0.0.0/0           md5

设置集群B const用户的登陆密码（1234）。

postgres=# \password const
Enter new password:
Enter it again:

在集群B（100.81.152.195）的public schema下分别创建表x，y。

postgres=# create table x(id int, value float8, des text) distributed by (id);
CREATE TABLE
postgres=# create table y(id int, value float8, des text) distributed by (id);
CREATE TABLE
postgres=# insert into x select r, r*random(), md5((r*random())::text) from generate_series(1,1000000)r;
INSERT 0 1000000
postgres=# insert into y select r, r*random(), md5((r*random())::text) from generate_series(1,1000)r;
INSERT 0 1000

准备在集群A（10.101.194.174）中创建外表 foreign_x 和 foreign_y分别对应集群B中的表x和y。

-- 创建postgres_fdw
CREATE EXTENSION postgres_fdw;

-- 创建foreign server
create server foreign_server_195
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS(host '100.81.152.195', port '15432', dbname 'postgres');

-- 创建user mapping
CREATE USER MAPPING FOR current_user
    SERVER foreign_server_195
    OPTIONS (user 'const', password '1234');

-- 创建foreign table foreign_x
CREATE FOREIGN TABLE foreign_x
(
    id int,
    value float8,
    des text
)
SERVER foreign_server_195
OPTIONS (schema_name 'public', table_name 'x');

-- 创建foreign table foreign_y
CREATE FOREIGN TABLE foreign_y
(
    id int,
    value float8,
    des text
)
SERVER foreign_server_195
OPTIONS (schema_name 'public', table_name 'y');

-- 查看foreign server
postgres=# \des+
                                                                     List of foreign servers
        Name        | Owner | Foreign-data wrapper | Access privileges | Type | Version |                       FDW Options                        | Description
--------------------+-------+----------------------+-------------------+------+---------+----------------------------------------------------------+-------------
 foreign_server_195 | const | postgres_fdw         |                   |      |         | (host '100.81.152.195', port '15432', dbname 'postgres') |
(1 row)

-- 查看foreign table
postgres=# \det+
                                     List of foreign tables
 Schema |   Table   |       Server       |              FDW Options               | Description
--------+-----------+--------------------+----------------------------------------+-------------
 public | foreign_x | foreign_server_195 | (schema_name 'public', table_name 'x') |
 public | foreign_y | foreign_server_195 | (schema_name 'public', table_name 'y') |
(2 rows)

-- 查询foreign_x
postgres=# select count(1) from foreign_x;
 count
--------
 1000000
(1 row)

-- 设置mpp_execute = all segments
postgres=# alter foreign table foreign_x options (add mpp_execute 'all segments');
ALTER FOREIGN TABLE
postgres=# \det+
                                     List of foreign tables
 Schema |   Table   |       Server       |              FDW Options               | Description
--------+-----------+--------------------+----------------------------------------+-------------
 public | foreign_x | foreign_server_195 | (schema_name 'public', table_name 'x', mpp_execute 'all segments') |
 public | foreign_y | foreign_server_195 | (schema_name 'public', table_name 'y') |
(2 rows)

-- 再次查询foreign_x (3 segments)
postgres=# select count(1) from foreign_x;
 count
--------
 3000000
(1 row)

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

1. SQL/MED

2. FDW for PostgreSQL

2.1 演进

2.2 概况

2.3 核心

2.4 不同

3. FDW in ADBPG v6.0

3.1 概况

3.2 示例

4. 参考

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

1. SQL/MED

2. FDW for PostgreSQL

2.1 演进

2.2 概况

2.3 核心

2.4 不同

3. FDW in ADBPG v6.0

3.1 概况

3.2 示例

4. 参考

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像