使用PXF(JDBC)访问SQL数据库

您的某些数据可能已经存储在外部SQL数据库中。 PXF通过PXF JDBC连接器提供对此数据的访问。 JDBC 连接器是一个JDBC客户端。 它可以从SQL数据库(包括MySQL, ORACLE, PostgreSQL, Apache Ignite和Hive)读取或向SQL数据库写入数据。

本节描述如何使用PXF JDBC 连接器访问外部SQL数据库中的数据，包括如何创建引用外部数据库表的PXF外部表，向该表查询数据或将数据插入该表中。

先决条件

在您使用PXF JDBC连接器访问外部数据库前，请确保：

• 您已配置并初始化PXF, 并且PXF正在每台segment主机上运行。更多详情，请参阅配置 PXF。
• 您可以确定PXF 用户配置目录($PXF_CONF)。
• 所有Greenplum数据库segment主机和外部SQL数据库库之间都可以连接。
• 您已经配置了外部SQL数据库，以便从所有Greenplum数据库segment主机进行访问。
• 您已经注册了所有JDBC驱动程序的JAR依赖。
• (推荐)您已经按照 配置PXF JDBC 连接器 中的描述创建了一个或多个命名的PXF JDBC连接服务配置。

数据类型支持

PXF JDBC 连接器支持以下数据类型:

• INTEGER, BIGINT, SMALLINT
• REAL, FLOAT8
• NUMERIC
• BOOLEAN
• VARCHAR, BPCHAR, TEXT
• DATE
• TIMESTAMP
• BYTEA

PXF JDBC 连接器不支持上面未列出的任何数据类型。

注意: JDBC连接器不支持读取或写入以字节数组(byte[])存储的Hive数据。

访问外部SQL数据库

PXF JDBC连接器支持一个名为Jdbc的配置文件。您可以使用此概要文件从外部SQL数据库表读取数据或将数据写入外部SQL数据库表。您还可以使用连接器在外部SQL数据库中运行静态的命名查询并读取结果。

使用以下语法创建引用外部SQL数据库表的Greenplum数据库外部表，并使用JDBC连接器读取或写入数据： 要访问远程SQL数据库中的数据，您可以创建一个引用该远程数据库表的可读或可写的Greenplum数据库外部表。Greenplum数据库外部表和远程数据库表或查询结果元组必须具有相同的定义；列名称和类型必须匹配。

使用以下语法创建引用远程SQL数据库表或来自远程数据库的查询结果的Greenplum数据库外部表：

CREATE [READABLE | WRITABLE] EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<external-table-name>|query:<query_name>?PROFILE=Jdbc[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import'|'pxfwritable_export');

CREATE EXTERNAL TABLE命令中使用的特定关键字和值见下表中描述。

注意: 在创建PXF外部表时，不能在 FORMAT 规范中使用 HEADER 选项。

JDBC自定义选项

您可以在LOCATION URI中包含JDBC连接器自定义选项，并在每个选项前加上&符号。 Jdbc概要文件支持的CREATE EXTERNAL TABLE <custom-option>包括：

批量写入操作(写)

当外部SQL数据库的JDBC驱动程序支持它时，批量 INSERT 操作可能会大大提升性能。

默认情况下启用批量写， 默认批处理大小为100。 要禁用批处理或修改批处理大小的值，请使用 BATCH_SIZE 设置创建PXF外部表：

• BATCH_SIZE=0 或 BATCH_SIZE=1 - 关闭批处理
• BATCH_SIZE=(n>1) - 将 BATCH_SIZE 设置为 n

当外部数据库的JDBC驱动程序不支持批处理时，PXF JDBC 连接器的行为取决于 BATCH_SIZE 设置，如下所述：

• BATCH_SIZE 省略 - JDBC 连接器插入时不使用批处理。
• BATCH_SIZE=(n>1) - INSERT 操作失败并且连接器返回错误。

批量读取操作

默认情况下，PXF JDBC连接器自动批处理从外部数据库表中获取的行。 默认的行获取大小为1000。要修改默认的获取大小值，请在创建PXF外部表时指定FETCH_SIZE。 例如：

FETCH_SIZE=5000

如果外部数据库JDBC驱动程序不支持读取时批处理，则必须通过设置FETCH_SIZE=0来显式禁用读取行批处理。

线程池(写)

当外部数据库的JDBC驱动程序支持线程化时，PXF JDBC连接器可以通过在多个线程中处理 INSERT 操作来进一步提升性能。

考虑将批处理和线程池一起使用。 当一起使用时，每个线程将接收并处理一批完整的数据。如果您使用线程池而不使用批处理，则线程池中的每个线程都恰好接收一个元组。

当线程池中的任一线程失败时，JDBC 连接器返回一个错误。请注意 INSERT 操作失败，部分数据可能会写入外部数据库表中。

要禁用或启动线程池并设置线程池大小，请使用 POOL_SIZE 设置创建PXF外部表，如下所述：

• POOL_SIZE=(n<1) - 线程池大小是系统中的CPU数量
• POOL_SIZE=1 - 关闭线程池
• POOL_SIZE=(n>1)- 将 POOL_SIZE 设为 n

分区(读)

PXF JDBC 连接器支持运行在多个segment主机上的PXF实例同时对外部SQL表的读取访问。 此功能被称为分区。 默认情况下，未启用读取分区。 要启用读取分区，请在创建PXF外部表时设置PARTITION_BY，RANGE和INTERVAL自定义选项。

PXF使用您指定的RANGE和INTERVAL值以及PARTITON_BY列将外部表中的特定数据行分配给在Greenplum数据库segment主机上运行的PXF实例。 此列选择特定于PXF处理，与您可能已为外部SQL数据库中的表指定的分区列没有关系。

标识分区参数的示例JDBC <custom-option> 子字符串：

&PARTITION_BY=id:int&RANGE=1:100&INTERVAL=5
&PARTITION_BY=year:int&RANGE=2011:2013&INTERVAL=1
&PARTITION_BY=createdate:date&RANGE=2013-01-01:2016-01-01&INTERVAL=1:month
&PARTITION_BY=color:enum&RANGE=red:yellow:blue

启用分区时，PXF JDBC连接器将SELECT查询拆分为多个子查询，这些子查询检索数据的子集，每个子集称为一个片段。 JDBC连接器会自动向每个片段添加额外的查询约束（WHERE表达式），以确保从外部数据库中检索每个元组的数据都恰好一次。

例如，当用户查询使用指定&PARTITION_BY=id:int&RANGE=1:5&INTERVAL=2的LOCATION子句创建的PXF外部表时，PXF会生成5个片段：根据分区设置两个，和最多三个隐式片段生成的碎片。 与每个片段相关的约束如下：

• 片段 1: WHERE (id < 1) - 隐式生成的片段，用于RANGE起始区间
• 片段 2: WHERE (id >= 1) AND (id < 3) - 分区设置指定的片段
• 片段 3: WHERE (id >= 3) AND (id < 5) - 分区设置指定的片段
• 片段 4: WHERE (id >= 5) - 隐式生成的片段，用于RANGE结束区间
• 片段 5: WHERE (id IS NULL) - 隐式生成的片段

PXF在Greenplum数据库segment之间分配片段。 在segment主机上运行的PXF实例为服务片段的主机上的每个segment生成一个线程。 如果片段的数量小于或等于在片段主机上配置的Greenplum segment的数量，则单个PXF实例可以为所有片段提供服务。 每个PXF实例将其结果发送回Greenplum数据库，在此收集它们并将其返回给用户。

当您指定PARTITION_BY选项时，根据与目标数据库的最佳JDBC连接数以及跨Greenplum数据库segment的最佳外部数据分配，调整INTERVAL值和单位。 INTERVAL低边界由Greenplum数据库segment的数量驱动，而高边界由与目标数据库的可接受的JDBC连接数量驱动。 INTERVAL设置会影响片段的数量，理想情况下不应设置得太高或太低。 使用多个值进行测试可以帮助您选择最佳设置。

示例: 读取和写入PostgreSQL表

在本例中，您将：

• 创建一个PostgreSQL数据库和表，并将数据写入表中
• 创建一个PostgreSQL用户并将表上的所有权限都赋予该用户
• 配置PXF JDBC 连接器以访问PostgreSQL数据库
• 创建一个引用PostgreSQL表的PXF可读外部表
• 读取PostgreSQL表中的数据
• 创建一个引用PostgreSQL表的PXF可写外部表
• 将数据写入PostgreSQL表
• 再次读取PostgreSQL表中的数据

创建一个PostgreSQL表

执行以下步骤在名为 pgtestdb 的数据库的 public 模式下创建一个名为 forpxf_table1 的PostgreSQL表，并向名为 pxfuser1 的用户赋予该表的所有权限：

1. 确定PostgreSQL服务器的主机名和端口。

2. 以 postgres 用户连接默认PostgreSQL数据库。 例如，假设您的PostgreSQL服务以默认端口运行在 pserver 主机上：

   $ psql -U postgres -h pserver
   

3. 创建一个名为pgtestdb的PostgreSQL数据库并连接到这个数据库：

   =# CREATE DATABASE pgtestdb;
   =# \connect pgtestdb;
   

4. 创建一个名为 forpxf_table1 的表并并向表中写入一些数据：

   =# CREATE TABLE forpxf_table1(id int);
   =# INSERT INTO forpxf_table1 VALUES (1);
   =# INSERT INTO forpxf_table1 VALUES (2);
   =# INSERT INTO forpxf_table1 VALUES (3);
   

5. 创建一个名为 pxfuser1 的PostgreSQL用户：

   =# CREATE USER pxfuser1 WITH PASSWORD 'changeme';
   

6. 为用户 pxfuser1 分配 forpxf_table1 表的所有权限，并退出 psql 子系统：

   =# GRANT ALL ON forpxf_table1 TO pxfuser1;
   =# \q
   

   有了这些权限， pxfuser1 可以读取和写入 forpxf_table1 表。

7. 更新 PostgreSQL 配置以允许用户 pxfuser1 从每个Greenplum数据库segment主机访问 pgtestdb 。 此配置特定于您的PostgreSQL环境。 您将更新 /var/lib/pgsql/pg_hba.conf 文件，然后重启PostgreSQL服务。

配置JDBC连接器

您必须为PostgreSQL创建JDBC服务配置，将PostgreSQL驱动程序JAR文件下载到您的系统，将该JAR文件复制到PXF用户配置目录，同步PXF配置， 然后重启PXF。

此过程通常由Greenplum数据库管理员执行。

1. 登录到Greenplum数据库master节点：

   $ ssh gpadmin@<gpmaster>
   

2. 如示例配置步骤中所述为PostgreSQL创建JDBC服务配置，命名服务目录为 pgsrvcfg。 jdbc-site.xml 文件的内容应类似于以下内容(将PostgreSQL主机系统替换为 pgserverhost)：

   <?xml version="1.0" encoding="UTF-8"?>
   <configuration>
   <property>
       <name>jdbc.driver</name>
       <value>org.postgresql.Driver</value>
   </property>
   <property>
       <name>jdbc.url</name>
       <value>jdbc:postgresql://pgserverhost:5432/pgtestdb</value>
   </property>
   <property>
       <name>jdbc.user</name>
       <value>pxfuser1</value>
   </property>
   <property>
       <name>jdbc.password</name>
       <value>changeme</value>
   </property>
   </configuration>
   

3. 同步PXF 配置到Greenplum数据库集群。例如：

   gpadmin@gpmaster$ $GPHOME/pxf/bin/pxf cluster sync
   

从PostgreSQL表中读取

执行以下步骤创建一个PXF外部表，该表引用您在上一节创建的 forpxf_table1 PostgreSQL表， 并读取该表中的数据：

1. 指定 Jdbc 配置文件创建PXF外部表。例如：

   gpadmin=# CREATE EXTERNAL TABLE pxf_tblfrompg(id int)
               LOCATION ('pxf://public.forpxf_table1?PROFILE=Jdbc&SERVER=pgsrvcfg')
               FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
   

2. 显示 pxf_tblfrompg 表的所有行：

   gpadmin=# SELECT * FROM pxf_tblfrompg;
    id
   ----
     1
     2
     3
   (3 rows)
   

写入PostgreSQL表

执行以下步骤，将一些数据写入Postgres forpxf_table1 表，然后从该表中读取它们。 您必须为写操作创建一个新的外部表。

1. 指定 Jdbc 配置文件创建一个可写的PXF外部表。 例如：

   gpadmin=# CREATE WRITABLE EXTERNAL TABLE pxf_writeto_postgres(id int)
               LOCATION ('pxf://public.forpxf_table1?PROFILE=Jdbc&SERVER=pgsrvcfg')
             FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
   

2. 将一些数据写入 pxf_writeto_postgres 表。 例如：

   =# INSERT INTO pxf_writeto_postgres VALUES (111);
   =# INSERT INTO pxf_writeto_postgres VALUES (222);
   =# INSERT INTO pxf_writeto_postgres VALUES (333);
   

3. 使用您在上一节创建的可读外部表 pxf_tblfrompg 来查看PostgreSQL forpxf_table1 表中的数据：

   gpadmin=# SELECT * FROM pxf_tblfrompg ORDER BY id DESC;
    id
   -----
    333
    222
    111
      3
      2
      1
   (6 rows)
   

关于使用命名查询

PXF JDBC连接器允许您指定静态定义的查询以对远程SQL数据库运行。 考虑在以下情况下使用命名查询：

• 您需要联接所有都驻留在同一外部数据库中的几个表。
• 您想在数据源附近执行复杂的聚合。
• 您将在外部数据库中使用但不允许创建VIEW。
• 您宁愿消耗外部系统中的计算资源，以最大程度地减少Greenplum数据库资源的利用率。
• 您要运行HIVE查询并通过YARN控制资源利用率。

Greenplum数据库管理员定义了一个查询，并为您提供了创建外部表时要使用的查询名称。 在表CREATE EXTERNAL TABLE LOCATION子句中指定query:<query_name>代替表名，以指示PXF JDBC连接器在远程SQL数据库中运行名为<query_name>的静态查询。

PXF仅支持具有可读外部表的命名查询。 您必须为要运行的每个查询创建一个唯一的Greenplum数据库可读外部表。

外部表列的名称和类型必须与查询结果返回的列的名称，类型和顺序完全匹配。 如果查询返回聚合或其他函数的结果，请确保使用AS限定符指定特定的列名。

例如，假设您正在使用具有以下定义的PostgreSQL表：

CREATE TABLE customers(id int, name text, city text, state text);
CREATE TABLE orders(customer_id int, amount int, month int, year int);

这个PostgreSQL查询中，管理员将其命名为order_rpt：

SELECT c.name, sum(o.amount) AS total, o.month
  FROM customers c JOIN orders o ON c.id = o.customer_id
  WHERE c.state = 'CO'
GROUP BY c.name, o.month

该查询返回类型为(name text, total int, month int)的元组。 如果为名为pgserver的PXF JDBC服务器定义了order_rpt查询，则可以创建Greenplum数据库外部表来读取这些查询结果，如下所示：

CREATE EXTERNAL TABLE orderrpt_frompg(name text, total int, month int)
  LOCATION ('pxf://query:order_rpt?PROFILE=Jdbc&SERVER=pgserver&PARTITION_BY=month:int&RANGE=1:13&INTERVAL=3')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

该命令引用在pgserver服务器配置中定义的名为order_rpt的查询。 它还指定了JDBC读取分区选项，这些选项为PXF提供了用于在其服务器segment之间划分查询结果数据的信息。

PXF JDBC连接器自动将列投影和过滤器下推应用于引用命名查询的外部表。

示例：读取PostgreSQL查询的结果

在此示例中，您：

• 使用在示例：读取和写入PostgreSQL数据库中创建的PostgreSQL数据库pgtestdb，用户pxfuser1和PXF JDBC连接器服务器配置pgsrvcfg。
• 创建两个PostgreSQL表并将数据插入表中。
• 将表上的所有特权分配给pxfuser1。
• 定义一个在两个PostgreSQL表上执行复杂SQL语句的命名查询，并将该查询添加到pgsrvcfg JDBC服务器配置中。
• 创建一个与查询结果元组匹配的PXF可读外部表定义，并指定读取分区选项。
• 使用PXF列投影和过滤器下推读取查询结果。

创建PostgreSQL表并分配权限

执行以下过程，以在名为pgtestdb的数据库的public模式中创建名为customers和orders的PostgreSQL表，并向用户pxfuser1授予这些表的所有特权：

1. 确定PostgreSQL服务器的主机名和端口。

2. 以postgres用户身份连接到pgtestdb PostgreSQL数据库。 例如，如果您的PostgreSQL服务器正在名为pserver的主机的默认端口上运行：

   $ psql -U postgres -h pserver -d pgtestdb
   

3. 创建一个名为customers的表，并将一些数据插入该表：

   CREATE TABLE customers(id int, name text, city text, state text);
   INSERT INTO customers VALUES (111, 'Bill', 'Helena', 'MT');
   INSERT INTO customers VALUES (222, 'Mary', 'Athens', 'OH');
   INSERT INTO customers VALUES (333, 'Tom', 'Denver', 'CO');
   INSERT INTO customers VALUES (444, 'Kate', 'Helena', 'MT');
   INSERT INTO customers VALUES (555, 'Harry', 'Columbus', 'OH');
   INSERT INTO customers VALUES (666, 'Kim', 'Denver', 'CO');
   INSERT INTO customers VALUES (777, 'Erik', 'Missoula', 'MT');
   INSERT INTO customers VALUES (888, 'Laura', 'Athens', 'OH');
   INSERT INTO customers VALUES (999, 'Matt', 'Aurora', 'CO');
   

4. 创建一个名为orders的表，并将一些数据插入该表：

   CREATE TABLE orders(customer_id int, amount int, month int, year int);
   INSERT INTO orders VALUES (111, 12, 12, 2018);
   INSERT INTO orders VALUES (222, 234, 11, 2018);
   INSERT INTO orders VALUES (333, 34, 7, 2018);
   INSERT INTO orders VALUES (444, 456, 111, 2018);
   INSERT INTO orders VALUES (555, 56, 11, 2018);
   INSERT INTO orders VALUES (666, 678, 12, 2018);
   INSERT INTO orders VALUES (777, 12, 9, 2018);
   INSERT INTO orders VALUES (888, 120, 10, 2018);
   INSERT INTO orders VALUES (999, 120, 11, 2018);
   

5. 为用户pxfuser1分配表customers和orders的所有特权，然后退出psql子系统：

   GRANT ALL ON customers TO pxfuser1;
   GRANT ALL ON orders TO pxfuser1;
   \q
   

配置命名查询

在此过程中，您将创建一个命名查询文本文件，将其添加到pgsrvcfg JDBC服务器配置中，并将PXF配置同步到Greenplum数据库集群。

此过程通常由Greenplum数据库管理员执行。

1. 登录到Greenplum数据库主节点：

   $ ssh gpadmin@<gpmaster>
   

2. 导航到JDBC服务器配置目录pgsrvcfg。 例如：

   gpadmin@gpmaster$ cd $PXF_CONF/servers/pgsrvcfg
   

3. 在文本编辑器中打开名为pg_order_report.sql的查询文本文件，然后将以下查询复制/粘贴到该文件中：

   SELECT c.name, c.city, sum(o.amount) AS total, o.month
     FROM customers c JOIN orders o ON c.id = o.customer_id
     WHERE c.state = 'CO'
   GROUP BY c.name, c.city, o.month
   

4. 保存文件并退出编辑器。

5. 将对PXF配置的这些更改同步到Greenplum数据库集群。 例如：

   gpadmin@gpmaster$ $GPHOME/pxf/bin/pxf cluster sync
   

阅读查询结果

在Greenplum数据库群集上执行以下过程，以创建一个PXF外部表，该表引用您在上一节中创建的查询文件，然后读取查询结果数据：

1. 创建指定Jdbc配置文件的PXF外部表。 例如：

   CREATE EXTERNAL TABLE pxf_queryres_frompg(name text, city text, total int, month int)
     LOCATION ('pxf://query:pg_order_report?PROFILE=Jdbc&SERVER=pgsrvcfg&PARTITION_BY=month:int&RANGE=1:13&INTERVAL=3')
   FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
   

   通过这种分区方案，PXF将向远程SQL数据库发出4个查询，每季度一个查询。 每个查询将返回目标季度每个月的客户名称以及给定月份中每个客户每月的总订单总额。 然后，当您查询外部表时，Greenplum数据库将为您将数据合并到单个结果集中。

2. 显示查询结果的所有行：

   SELECT * FROM pxf_queryres_frompg ORDER BY city, total;
   
    name |  city  | total | month
   ------+--------+-------+-------
    Matt | Aurora |   120 |    11
    Tom  | Denver |    34 |     7
    Kim  | Denver |   678 |    12
   (3 rows)
   

3. 使用列投影来显示每个城市的订单总数：

   SELECT city, sum(total) FROM pxf_queryres_frompg GROUP BY city;
   
     city  | sum
   --------+-----
    Aurora | 120
    Denver | 712
   (2 rows)
   

   当您执行此查询时，PXF仅请求和检索city和total列的查询结果，从而减少了发送回Greenplum数据库的数据量。

4. 提供其他过滤器和聚合以过滤PostgreSQL中的total：

   SELECT city, sum(total) FROM pxf_queryres_frompg
               WHERE total > 100
               GROUP BY city;
   
     city  | sum
   --------+-----
    Denver | 678
    Aurora | 120
   (2 rows)
   

   在此示例中，PXF将向子查询添加WHERE过滤器。 该过滤器被推送到远程数据库系统并在其上执行，从而减少了PXF发送回Greenplum数据库的数据量。 但是，GROUP BY聚合不会推送到远程，而是由Greenplum执行。

用DDL覆盖JDBC服务器配置

您可以通过直接在CREATE EXTERNAL TABLE LOCATION子句中指定自定义选项，来为特定的外部数据库表覆盖JDBC服务器配置中的某些属性：

通过自定义选项指定的示例JDBC连接字符串：

&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://pgserverhost:5432/pgtestdb&USER=pguser1&PASS=changeme
&JDBC_DRIVER=com.mysql.jdbc.Driver&DB_URL=jdbc:mysql://mysqlhost:3306/testdb&USER=user1&PASS=changeme

例如：

CREATE EXTERNAL TABLE pxf_pgtbl(name text, orders int)
  LOCATION ('pxf://public.forpxf_table1?PROFILE=Jdbc&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://pgserverhost:5432/pgtestdb&USER=pxfuser1&PASS=changeme')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

有关PXF用于获取Greenplum数据库用户的配置属性设置的优先级规则的详细信息，请参考配置属性优先级。