PostgreSQL的外部数据封装器fdw用法

下面我会详细讲解“PostgreSQL的外部数据封装器（Foreign Data Wrapper，简称FDW）用法”的完整攻略。

什么是FDW

FDW是PostgreSQL提供的一种机制，可以将外部数据源（如其他数据库、文件等）封装成PostgreSQL的表，从而可以在PostgreSQL中查询和操作这些外部数据源，实现了数据的统一管理和处理。

目前常用的FDW有多种，如mysql_fdw、oracle_fdw、hadoop_fdw等。在本攻略中，我们将以mysql_fdw为例进行讲解。

使用FDW

安装

在开始使用FDW之前，我们需要先安装相应的FDW插件。以mysql_fdw为例，可以使用以下命令进行安装：

sudo apt-get install postgresql-<version>-mysql-fdw

其中，\<version>为PostgreSQL的版本号，例如9.6、10等。

配置外部数据源

在使用FDW之前，我们需要先配置外部数据源。以MySQL为例，假设我们有一个MySQL数据库，其地址为192.168.0.1，端口为3306，数据库名为test，用户名为user，密码为password，我们需要使用以下命令在PostgreSQL中创建该MySQL数据库的连接：

CREATE SERVER mysql_test FOREIGN DATA WRAPPER mysql_fdw OPTIONS(host '192.168.0.1', port '3306', dbname 'test', username 'user', password 'password');

该命令创建了一个名为mysql_test的服务器对象，使用mysql_fdw插件连接了远程MySQL数据库，供后续使用。

创建外部表

在配置完外部数据源之后，我们可以使用以下命令在PostgreSQL中创建外部表：

CREATE FOREIGN TABLE mysql_table (
  col1     INTEGER,
  col2     TEXT,
  col3     NUMERIC
)
SERVER mysql_test
OPTIONS ( table_name 'mysql_table' );

该命令创建了一个名为mysql_table的外部表，列名和数据类型分别与MySQL表对应。其中，SERVER关键字指定了使用哪个服务器对象（即连接哪个外部数据源），OPTIONS关键字指定了该外部表对应的MySQL表名。

查询数据

创建外部表之后，我们可以在PostgreSQL中查询该外部表对应的MySQL表的数据，例如：

SELECT * FROM mysql_table;

该命令将会返回mysql_table表中的所有记录。

示例1

下面，我们以MySQL中的employees数据库为例，演示如何使用mysql_fdw将其封装到PostgreSQL中，并对其中的employees表进行查询操作。

首先，我们需要安装mysql_fdw插件：

sudo apt-get install postgresql-<version>-mysql-fdw

然后，我们需要在PostgreSQL中创建连接：

CREATE SERVER mysql_employees FOREIGN DATA WRAPPER mysql_fdw OPTIONS(host 'localhost', port '3306', dbname 'employees', username 'user', password 'password');

注意，这里将MySQL安装在了本地（localhost），端口号为3306，数据库名为employees，用户名为user，密码为password，需要根据实际情况进行修改。

创建连接之后，我们可以使用如下命令创建外部表：

CREATE FOREIGN TABLE employees (
  emp_no      INTEGER,
  birth_date  DATE,
  first_name  TEXT,
  last_name   TEXT,
  gender      CHAR(1),
  hire_date   DATE
)
SERVER mysql_employees
OPTIONS ( table_name 'employees' );

该命令创建了一个名为employees的外部表，对应了MySQL中的employees表。

接下来，我们可以在PostgreSQL中查询该外部表的数据，例如查询所有员工的记录：

SELECT * FROM employees;

该命令将在PostgreSQL中返回MySQL中employees表的所有员工记录。这样，我们便可以在PostgreSQL中轻松访问MySQL的数据了。

示例2

除了封装其他数据库的表，FDW还可以用于封装其他数据源的数据，例如CSV文件。

下面，我们以一个名为test.csv的CSV文件为例，演示如何使用file_fdw插件将其封装到PostgreSQL中，并进行查询操作。

首先，我们需要在PostgreSQL中创建连接：

CREATE SERVER csv_test FOREIGN DATA WRAPPER file_fdw OPTIONS(program 'cat');

该命令创建了一个名为csv_test的服务器对象，使用cat命令读取CSV文件，供后续使用。

然后，我们需要在PostgreSQL中创建外部表：

CREATE FOREIGN TABLE test (
  col1 INTEGER,
  col2 TEXT,
  col3 NUMERIC
)
SERVER csv_test
OPTIONS ( format 'csv', delimiter ',' );

该命令创建了一个名为test的外部表，对应了CSV文件中的数据。

接下来，我们可以在PostgreSQL中查询该外部表的数据，例如：

SELECT * FROM test;

该命令将在PostgreSQL中返回CSV文件中的所有数据。

总结

本攻略简单介绍了PostgreSQL的FDW机制以及使用mysql_fdw和file_fdw插件封装外部数据源的方法。在实际应用中，FDW可以大大简化数据处理流程，让我们轻松地处理来自不同数据源的数据。