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的现状与计划:
- 2011年,PostgreSQL 9.1支持只读方式访问外表(外表查询)
- 2013年,PostgreSQL 9.3支持可写方式访问外表(外表插入/更新/删除)
- ... ...
2.2 概况
- 直至今日,PostgreSQL Global Development Group (PGDG)仅支持:file_fdw 和 postgre_fdw。
- 更多的FDW可以在the PGXN website找到,但因不是官方支持的版本,需小心使用,更详细的说明见WIKI。
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)
4. 参考
- https://wiki.postgresql.org/wiki/Foreign_data_wrappers
- https://wiki.postgresql.org/wiki/SQL/MED
- https://www.pgcon.org/2009/schedule/events/142.en.html
- https://wiki.postgresql.org/wiki/DATALINK
- https://wiki.postgresql.org/wiki/SqlMedConnectionManager
- https://www.iso.org/standard/63476.html
- https://www.postgresql.org/docs/current/unsupported-features-sql-standard.html
- https://gpdb.docs.pivotal.io/6-0/admin_guide/external/g-foreign.html
- https://yq.aliyun.com/articles/713076