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

社群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标準主要有如下不同：

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 參考

文法上擴充了HANDLER和VALIDATOR從句

标準裡定義的LIBRARY和LANGUAGE從句并未實作

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 參考

絕大部分相容 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。
文法上，，和基本與社群PostgreSQL一緻，隻是額外增加mpp_execute屬性，用于mpp query時，決定通過哪個主機擷取外部資料，預設為‘master’。mpp_execute屬性的優先級為：FOREIGN TABLE > FOREIGN SERVER > FOREIGN DATA WARPPER

AnalyticDB for PostgreSQL 6 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 參考

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 新特性解析 - FDWAnalyticDB for PostgreSQL 6 新特性解析 - FDW1. SQL/MED2. FDW for PostgreSQL3. FDW in ADBPG v6.04. 參考

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

繼續閱讀

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離