AnalyticDB for PostgreSQL 6 新特性解析 - FDW

  1. 云栖社区>
  2. 博客>
  3. 正文

AnalyticDB for PostgreSQL 6 新特性解析 - FDW

游客xq2fyplh2yvrc 2019-11-15 14:11:52 浏览332
展开阅读全文

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

image.png

2.3 核心

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

2.4 不同

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

  1. CREATE FOREIGN DATA WRAPPER
    CREATE_FOREIGN_DATA_WRAPPER
  • 语法上扩展了HANDLER和VALIDATOR从句
  • 标准里定义的LIBRARY和LANGUAGE从句并未实现
  1. CREATE FOREIGN TABLE
    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 TABLECREATE SERVER 和 CREATE FOREIGN DATA WRAPPER 基本与社区PostgreSQL一致,只是额外增加mpp_execute属性,用于mpp query时,决定通过哪个主机获取外部数据,默认为‘master’。mpp_execute属性的优先级为:FOREIGN TABLE > FOREIGN SERVER > FOREIGN DATA WARPPER
    mpp_execute

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. 参考

网友评论

登录后评论
0/500
评论
游客xq2fyplh2yvrc
+ 关注