SQL/MED介绍

先说说，什么是SQL/MED？

(纯手打)

SQL/MED是SQL语言中管理外部数据的一个扩展标准。MED是英文“Management of External Data”的缩写。这个扩展定义在SQL：2003标准中的“ISO/IEC 9075-9:2003”中。它通过定义一个外部数据包装器和数据连接类型去管理外部数据。PostgreSQL 从9.1版本开始提供对SQL/MED标准的支持，通过SQL/MED可以连接到各种异构数据库或其他 PostgreSQL 数据库。

为什么需要 SQL/MED 呢？

SQL/MED 可以帮助开发人员简化应用架构，减少开发代价。如果没有 SQL/MED，应用程序的架构可能如下图。

PostgreSQL-外部表_数据库

自画图-1

从上图中可以看出，数据库无法互联，应用必须和各种不同的数据库连接，应用与数据库的连接与比较复杂，应用需要与各种不同的数据库交互，开发起来很困难，配置也很复杂。使用 SQL/MED 后，架构就可以简化成如下图所示的形式。

PostgreSQL-外部表_外部表_02

自画图-2

从上图中可以看出，使用 SQL/MED 后，PostgreSQL 通过 SQL/MED 访问其他各种异构数据库或其他 PostgreSQL 数据库，应用程序只需要与这一台 PostgreSQL 数据库交互，极大的简化了应用架构，提高了开发效率。

SQL/MED 相当于一种连接其他数据源的框架和标准。第三方可以根据 PostgreSQL 提供的外部数据源开发各种插件来连接其他数据库。目前，基本上各种常用的数据库或NoSQL产品都有第三方的FDW插件可以使用。

PostgreSQL Foreign_Data_Wrappers：外部数据包装器，缩写为“FDW”，相当于定义外部数据驱动，是一种外部访问接口，可以在PG数据库中创建外部表，用户访问的时候与访问本地表的方法一样，支持增删改查。然而，数据则是存储在外部，外部可以是一个远程的PG数据库或者其他数据库(mysql,oracle等)，又或者是文件等。

Server：外部数据服务器，相当于定义一个外部数据源，需要指定外部数据源的 Foreign Data Wrapper。

User Mapping：用户映射，主要是把外部数据源的用户映射到本地用户，用于控制权限。

Foreign Table：外部表，把外部数据源映射成数据库中的一张外部表。

1.外部数据包装器对象 FDW

创建FDW时需要指定一个函数，该函数定义了PostgreSQL数据库如何从外部数据源取得数据，该函数是使用C语言编写的PostgreSQL扩展函数，返回类型为fdw_handler。在创建时也可以指定一个可选的检验函数和一些参数，检验函数可以检查 User Mapping、Server和 FDW 的参数。创建外部文件包装器的函数如下：

CREATE FUNCTION file_fdw_handler()
RETURNS fdw_handler
AS 'file_fdw' LANGUAGE C STRICT;

CREATE FUNCTION file_fdw_validator(text[],oid)
RETURNS void
AS 'file_fdw' LANGUAGE C STRICT;

CREATE FOREIGN DATA WRAPPER file_fdw
HANDLER file_fdw_handler
VALIDATOR file_fdw_validator;

上面的示例中，第一个SQL创建了一个 handle 函数，第二个 SQL 创建了一个 validator 函数，第三个 SQL 创建了一个“外部数据包装器”，创建时指定了 handle 函数和 validator 函数。

handle 函数有如下3点要求：

  • 必须是用C语言写的扩展函数
  • 不能有参数
  • 必须返回“fdw_handler”类型

validator 函数的要求是：

  • 必须有2个参数
  • 第一个参数类型必须是 text[]，表示要检验的可选参数;
  • 第二个参数类型必须是 oid，指定可选参数的分类，分类为 server / user mapping / FDW / Table;

创建外部数据包装器的完整语法是：

CREATE FOREIGN DATA WRAPPER name
[HANDLER handler_function | NO HANDLER]
[VALIDATOR validator_function | NO VALIDATOR]
[OPTIONS (option 'value' [, ...])]

语法说明如下：

  • name：指定要创建的外部包装器的名称;
  • NO HANDLER：此选项可以创建一个无 handle 函数的外部包装器，但是会导致使用此包装器的外部表只能声明，不能被访问。
  • NO VALIDATOR：此选项可以创建一个无检验函数的外部包装器。无检验函数的外部包装器在创建时不对选项进行检查。
  • OPTIONS  (option 'value' [, ...])：指定一些参数，参数名称必须是唯一的。

示例：

创建外部数据包装器的示例如下：

创建一个无用的外部数据包装器“dummy”，命令如下：

CREATE FOREIGN DATA WRAPPER dummy;

创建一个带选项的外部数据包装器，命令如下：

CREATE FOREIGN DATA SRAPPER mywrapper OPTIONS (dubug 'true');

注意：外部数据包装器必须由超级用户创建，其他用户没有创建权限 。超级用户创建后，其他用户可以使用。

赋权

给其他用户的方法如下：

--让用户 user01 可以使用外部数据包装器 postgres_fdw
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO user01;

实际操作：

在实际操作中，CREATE EXTENSION 命令会自动创建外部数据包装器，如：

create extension file_fdw;
create extension postgres_fdw;

如此这般，外部数据包装器“file_fdw”和“postgresql_fdw”就创建完成了，不需要再运行 CREATE FOREIGN DATA WRAPPER 命令。

2.外部服务器对象

外部服务器对象，即 Server 对象。Server 对象是把 FDW 与连接外部数据源的连接参数关联起来的对象，主要定义如何连接外部数据源。

创建 Server 对象的语法格式如下：

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
  FOREIGN DATA WRAPPER fdw_name
  [ OPTIONS ( option 'value' [, ... ] ) ]

语法说明如下：

  • server_name：外部 Server 的名称;
  • server_type：可选项，指定外部服务器的类型，是否使用此选项与具体的外部数据包装器有关，如果外部数据包装器没有此选项，则不需要定义此选项;
  • server_version：外部服务器的版本，也与具体的外部数据包装器有关;
  • fdw_name：指定此外部服务器的外部数据包装器;
  • OPTIONS (option 'value' [, ...])：这些选项主要用于如何连接外部数据源，如连接外部数据源的IP地址、端口及其他一些参数，也与具体的外部数据包装器有关。

示例[实际操作]：

创建一个指向另一台 PostgreSQL 数据库的外部数据服务器：

CREATE SERVER postgresql_fdw_server FOREIGN DATA WRAPPER postgresql_fdw
  OPTIONS (host '10.0.7.11', dbname 'user01', port '5432');

创建一个指向 MySQL 数据库的外部数据服务器：

CREATE SERVER mysql_fdw_server FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (address '10.0.7.12', port '3306');

赋权

一个用户创建的外部服务器，如果想让另一个用户使用，则需要赋权，命令如下：

GRANT USAGE ON FOREIGN SERVER mysql_fdw_server to user02;

3.用户映射对象

用户映射主要解决 PostgreSQL 用户与外部服务器的用户之间的映射关系。创建用户映射的语法格式如下：

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
  SERVER server_name
  [ OPTIONS ( option 'value' [ ,  ... ]  ) ]

语法说明如下：

  • user_name：代表是本地 PostgreSQL 用户，如果是“CURRENT_USER”或“USER”则代表当前的用户。当声明 PUBLIC 时，一个所谓的公共映射，将被创建完成，当没有特定用户的映射时就会使用该公共映射。
  • server_name：指定一个服务名称，就是前面用 CREATE SERVER 命令创建的名称。
  • [ OPTIONS ( option 'value' [ ,  ... ]  ) ]：该选项通常定义，映射的远程数据源上实际的用户名和密码。选项名称必须是唯一的。具体允许哪些选项，是由外部包装器决定的。

示例[实际操作]：

此示例中，“user01”是本地数据库中的一个用户；“user02”是远程数据库中的一个用户。

CREATE USER MAPPING FOR user01
  SERVER postgresql_fdw_server
  OPTIONS (user 'user02', passwrod 'password02');

4.外部表对象

实际上，SQL/MED 就是把外部数据源中的数据对象映射成一张外部表，然后就可以像访问普通表一样访问“外部表”了。

创建外部表的语法格式如下：

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  column_name data_type [ OPTIONS (option 'value' [, ...] ) ] [ COLLATE [, ...]
  ] )
    SERVER server_name
  [ OPTIONS ( option 'value' [, ...] ) ]

创建外部表的语法与创建本地表的语法类似，定义的列也可以加上一些列约束，可以加的列约束如下：

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr }

示例：

CREATE FOREIGN TABLE ft_test01 (
  id int,
  name text
) SERVER postgresql_fdw_server
OPTIONS (table_name 'test01');

承上启下

PostgreSQL-外部表_外部表_03

5.file_fdw 使用实例

file_fdw 插件为 PostgreSQL 数据库提供了访问外部文件数据的能力。该插件是内置在 PostgreSQL源码的 contrib 中的，使用这个包可以很方便的把外部文本文件映射成一张外部表。当前此类外部表是只读的。

file_fdw 示例1 passwd->

把 Linux 下的 /etc/passwd 文件映射成一张外部表。

create extension file_fdw;
create server file_fdw_server foreign data wrapper file_fdw;
create foreign table passwd (username text,pass text,uid int4,gid int4,gecos text,home text,shell text
) server file_fdw_server
OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');
select * from passwd limit 5;

file_fdw 中各选项的说明如下：

  • filename：指定外部文件名;
  • format：指定文件的格式，与 COPY 命令中的 format 选项相同;
  • header：指定文件是否有行头，与 COPY 命令中的 header 选项相同;
  • delimiter：指定分隔字符，与 COPY 命令中的 delimiter 选项相同;
  • quote：指定字符串的包裹字符，与 COPY 命令中的 quote 选项相同;
  • escape：指定转义字符，与 COPY 命令中的 escape 选项相同;
  • null：指定“空”为表示字符串，与 COPY 命令中的 null 选项相同;
  • encoding：指定文件的字符集编码，与 COPY 命令中的 encoding 选项相同;

实际上，file_fdw 是通过访问 COPY API 来访问外部文本文件的，所以 file_fdw 的选项除 filename 外，都与 COPY 命令相同。目前 file_fdw 还不支持 COPY 命令中的oids、force_quote 和 force_not_null 选项。如果熟悉了 COPY 命令的使用方法，就可以很快熟悉 file_fdw 的使用。

file_fdw 示例2 日志->

将 PostgreSQL 的日志转换成一张外部表。

0.PostgreSQL 日志配置

vim postgresql.conf

# 将 PostgreSQL 日志格式配置成 CSV 格式
log_destination = 'csvlog'
logging_collector = on

# 将 PostgreSQL 日志配置为最多保存一周
log_filename = 'postgresql-%u.log'
log_truncate_on_rotation = on
## 这两步在上个示例已完成。
create extension file_fdw;
create server file_fdw_server foreign data wrapper file_fdw;

##
create user mapping for postgres server file_fdw_server;

1.创建外部表。

创建7张外部表，分别对应星期日至星期六的日志文件 postgresql-0.log 到 postgresql-6.log，命令如下：

CREATE FOREIGN TABLE pglog0 (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_serverity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text
) SERVER file_fdw_server
OPTIONS (filename '/home/pgccc/pgdata/pg_log/ postgresql-0.csv', format 'csv');

...
...
...
...
CREATE FOREIGN TABLE pglog6 (
...
...
...
...
OPTIONS (filename '/home/pgccc/pgdata/pg_log/ postgresql-6.csv', format 'csv');

2.查询日志

select log_time,error_serverity,message from pglog6 limit 5;

6.postgresql_fdw 使用实例

postgresql_fdw 是访问（服务）其他 PostgreSQL 数据库的外部数据包装器，它提供了与原先已有的 dblink 模块相同的功能，但使用 postgresql_fdw 更符合 SQL 标准，在某些场景下比 dblink 有更好的性能表现。

示例：

6.0.在远程数据库中创建测试表 test01

psql -U user01 -d user01
create table test01(id int,name text);
insert into test01 select generate_series(1,10000), 'abcdefghijklmnopqrstuvwxyz';

6.1.在本地数据库中安装 postgresql_fdw 插件

create extension postgresql_fdw;

6.2.创建外部数据服务器

CREATE SERVER postgresql_fdw_server FOREIGN DATA WRAPPER postgresql_fdw
  OPTIONS (host '10.0.7.11', dbname 'user01', port '5432');

6.3.创建用户映射，指定连接远程数据库的用户名和密码

CREATE USER MAPPING FOR CURRENT_USER
  SERVER postgresql_fdw_server
  OPTIONS (user 'user01', passwrod 'password01');

6.4.创建外部表对象

CREATE FOREIGN TABLE ft_test01 (
  id int,
  name text
) SERVER postgresql_fdw_server
OPTIONS (table_name 'test01');

6.5.查询外部表

select * from ft_test01;

6.6.查看外部表执行计划

explain select * from ft_test01 limit 2;

7.oracle_fdw 使用实例

oracle_fdw 是一个 PostgreSQL 的扩展，它也提供了一个外部数据包装器，让你可以轻松高效地访问 Oracle 数据库，包括 WHERE 的下推条件和所需列以及全面的 EXPLAIN 支持。PG可以跨库对 Oracle 数据库中的表进行“增删改查”操作，可以查询 Oracle 中的视图，可以使 PG 中的表和 Oracle 中的表/视图做 JOIN 查询，类似  dblink 的功能。

7.0.创建外部扩展

create extension oracle_fdw;

## 查看创建的外部扩展 EXTENSION
\dx

7.1.创建外部数据源服务

create server oracle_fdw_server foreign data wrapper oracle_fdw
  OPTIONS (dbserver '//192.168.2.165:1521/orcl');

7.2.创建用户映射

指定远程数据库的用户名和密码

create user mapping for postgres server oracle_fdw_server
  options (user 'user01', password 'password01');

7.3.创建外部表

create foreign table "test_01" (id int,name text) server oracle_fdw_server options (table 'test_01');

8.odbc_fdw 使用实例

odbc_fdw 使用开放式数据库连接（ODBC）远程数据库，是专门用来访问SQL-Server数据库中的数据。

示例：

0.修改 ODBC 驱动配置文件 /etc/odbcinst.ini

在此文件中，加入以下内容：

[ODBC Driver 13 for SQL Server]
Description=Micrrosoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.0
UsageCount=1

1.修改 /etc/odbc.ini

为此文件增加一个 SQL Server 数据库的 DSN，为方便以后直接通过其访问 SQL Server

[MSSQL]
Driver=Micrrosoft ODBC Driver 13 for SQL Server
Description=Micrrosoft ODBC Driver 13 for SQL Server
Server=192.168.xxx.xxx
Database=GreenERR_2023

2.安装 odbc_fdw

wget https://github.com/CartoDB/odbc_fdw/archive/0.3.0.tar.gz
make
make install

3.创建扩展-外部数据包装器

create extension odbc_fdw;

4.创建外部数据服务器对象

使用 odbc.ini 中配置的 DSN 创建 server，谁创建归谁。

create server odbc_fdw_server foreign data wrapper odbc_fdw
  options (dsn 'MSSQL');

5.创建外部映射对象

即用户和Server之间的映射关系，命令如下：

create user mapping for binofa server odbc_fdw_server options (odbc_uid 'sa', odbc_pwd 'sa666');

--本文借鉴《PostgreSQL修炼之道 从小工到专家》第2版