MySQL 外表插件

本文将介绍如何在 OceanBase 数据库安装和使用外表 JDBC 插件。通过外表 JDBC 插件，可以访问 JDBC 支持的数据源（当前仅支持访问 MySQL 数据库数据源）。

注意

从 V4.4.1 版本开始支持外表 JDBC 插件服务。同时 OceanBase 数据库也基于 JDBC 插件服务发布了 MySQL 外表插件。
当前该功能为实验特性，暂不建议在生产环境使用。

功能限制

OceanBase 数据库 Oracle 模式：当前该功能不支持 Oracle 模式。
数据类型：Array 类型暂未支持。
查询限制：
- 不支持并发查询。
- 同一数据源多表 JOIN 不会作为一条 SQL 下推到数据源。
- 聚合函数、LIMIT 等不会下推。
依赖管理：jar 包不支持动态加载，在进程启动前就需要把 jar 包放在指定的目录。
超时设置：无法通过插件配置查询超时时间。
参数修改：表 PARAMETERS 属性不支持修改，必须重新建表。

前提条件

已完成部署 OceanBase 数据库并且创建了 MySQL 模式用户租户。
已安装 JDK（版本 ≥ 11），并配置好 JAVA_HOME 环境变量。

配置 MySQL 外表插件的 jar 包路径

从 MySQL 外表插件下载地址下载 MySQL 外表插件的 jar 包，然后把 MySQL 外表插件的 jar 包放到指定目录（配置项 ob_java_connector_path 目录下）。

注意

配置目录需用 admin 用户，即 OBServer 服务对其有读写权限。

示例如下：

创建存放 jar 包放到指定目录。

mkdir -p /jdbc/plugin/jar/package/directory

进入到目录。
```
cd /jdbc/plugin/jar/package/directory
```

下载 MySQL 外表插件的 jar 包。

wget https://github.com/oceanbase/oceanbase-plugins/releases/download/external-v1.0.0/oceanbase-external-plugin-1.0.0-jar-with-dependencies.jar

配置 OceanBase 数据库参数（重启后生效）

说明

如果是初次使用 JAVA SDK 环境，不需要重启 observer 进程。

启用 Java。

示例如下：
```
ALTER SYSTEM SET ob_enable_java_env = true;
```
该设置的详细介绍信息，参见 ob_enable_java_env。
设置当前 OBServer 运行节点上的 java home 目录。

说明

该路径来自于 JDK 的 HOME 目录，设置为环境中的 $JAVA_HOME 即可。

示例如下：
```
ALTER SYSTEM SET ob_java_home = "/java/home/path";
```
该设置的详细介绍信息，参见 ob_java_home。
设置 JDBC 插件 jar 包目录。

说明

只需要设置 MySQL 外表插件 jar 包的目录，不需要设置 jar 包名称，会自动展开目录下的 jar 包。

示例如下：
```
ALTER SYSTEM SET ob_java_connector_path = "/jdbc/plugin/jar/package/directory";
```
该设置的详细介绍信息，参见 ob_java_connector_path。

设置 java 环境启动的相关配置项

创建对应的日志文件夹路径。

mkdir -p /home/user/jvmlogs
mkdir -p /home/user/jvmlogs/heapdumps

设置 java 运行的 jvm 启动配置项。

示例如下：

ALTER SYSTEM SET ob_java_opts = "-Djdk.lang.processReaperUseDefaultStackSize=true -XX:+HeapDumpOnOutOfMemoryError -Xrs -Xmx2048m -Xms2048m -Xloggc:/home/user/jvmlogs/gc.log -XX:+PrintGCDetails -XX:+PrintGCDateStamps -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/user/jvmlogs/heapdumps/ -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=100M -XX:+UseG1GC  -XX:-CriticalJNINatives --add-opens=java.base/java.nio=org.apache.arrow.memory.core,ALL-UNNAMED -Darrow.allocation.manager.type=Netty";

该设置的详细介绍信息，参见 ob_java_opts。

注意

该配置项变更需要重启 observer 进程，由于当前使用的内存拷贝为数据流直接拷贝到 cpp 内存堆上，可以适当减少 -Xmx2048m -Xms2048m 的设置。
相关 gc 日志文件，需要对应的配置文件夹路径存在即可。如果不存在对应路径，则相关的日志文件不存在。

重启 OceanBase 数据库，检查插件是否安装成功。

示例如下：

SELECT *
FROM oceanbase.GV$OB_PLUGINS
WHERE TYPE = 'EXTERNAL TABLE'
AND STATUS = 'READY';

返回结果如下：

+--------------+----------+-------+--------+----------------+---------+-----------------+------------------+-------------------+-----------------------+---------------+-----------------------------------------------------+
| SVR_IP       | SVR_PORT | NAME  | STATUS | TYPE           | LIBRARY | LIBRARY_VERSION | LIBRARY_REVISION | INTERFACE_VERSION | AUTHOR                | LICENSE       | DESCRIPTION                                         |
+--------------+----------+-------+--------+----------------+---------+-----------------+------------------+-------------------+-----------------------+---------------+-----------------------------------------------------+
| 127.0.0.1    |    55803 | java  | READY  | EXTERNAL TABLE | NULL    | 0.1.0           | NULL             | 0.2.0             | OceanBase Corporation | Mulan PubL v2 | This is the java external table data source plugin. |
| 127.0.0.1    |    55803 | jdbc  | READY  | EXTERNAL TABLE | NULL    | 0.1.0           | NULL             | 0.2.0             | OceanBase Corporation | Mulan PubL v2 | This is a java external data source                 |
| 127.0.0.1    |    55803 | mysql | READY  | EXTERNAL TABLE | NULL    | 0.1.0           | NULL             | 0.2.0             | OceanBase Corporation | Mulan PubL v2 | This is a java external data source                 |
+--------------+----------+-------+--------+----------------+---------+-----------------+------------------+-------------------+-----------------------+---------------+-----------------------------------------------------+

其中 java 是 Java 外表插件的通用实现，jdbc 是一个 Java JDBC 数据源，基于 java 插件实现，MySQL 是一个MySQL JDBC 数据源，基于 jdbc 插件实现。

使用示例

步骤一：环境准备

假设有以下数据库环境：

组件	地址	端口	用户	数据库
MySQL 数据库	xxx.xxx.xxx.1	3306	root	test
OceanBase 集群	xxx.xxx.xxx.2	2881	root@mysql001	test

步骤二：创建测试表

在 MySQL 数据库中创建表。

示例如下：

CREATE TABLE `lineitem` (
    `l_orderkey`      bigint(20) NOT NULL,
    `l_partkey`       bigint(20) NOT NULL,
    `l_suppkey`       bigint(20) NOT NULL,
    `l_linenumber`    bigint(20) NOT NULL,
    `l_quantity`      bigint(20) NOT NULL,
    `l_extendedprice` decimal(10,2) NOT NULL,
    `l_discount`      decimal(10,2) NOT NULL,
    `l_tax`           decimal(10,2) NOT NULL,
    `l_returnflag`    char(1) DEFAULT NULL,
    `l_linestatus`    char(1) DEFAULT NULL,
    `l_shipdate`      date DEFAULT NULL,
    `l_commitdate`    date DEFAULT NULL,
    `l_receiptdate`   date DEFAULT NULL,
    `l_shipinstruct`  char(25) DEFAULT NULL,
    `l_shipmode`      char(10) DEFAULT NULL,
    `l_comment`       varchar(44) DEFAULT NULL,
    PRIMARY KEY (`l_orderkey`,`l_linenumber`)
    ) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在 OceanBase 数据库中创建外表。

示例如下：

CREATE EXTERNAL TABLE lineitem (     
    l_orderkey           bigint,
    l_partkey            bigint,
    l_suppkey            bigint,
    l_linenumber         bigint,
    l_quantity           bigint,
    l_extendedprice      decimal(10,2),
    l_discount           decimal(10,2),
    l_tax                decimal(10,2),
    l_returnflag         char(1) ,
    l_linestatus         char(1) ,
    l_shipdate           date ,
    l_commitdate         date ,
    l_receiptdate        date ,
    l_shipinstruct       char(25) ,
    l_shipmode           char(10) ,
    l_comment            varchar(44)
    ) 
    PROPERTIES (
        TYPE='plugin',
        NAME='mysql',
        PARAMETERS='{"user":"root","password":"","table":"lineitem","jdbc_url":"jdbc:mysql://xxx.xxx.xxx.1:3306/test?useSSL=false"}'
    );

注意

在 JDBC 连接串中增加了 useSSL=false参数，因为 MySQL JDBC 插件使用了 8.0.3 版本，连接比较 MySQL 5.7 版本时需要增加此选项。

在 MySQL 数据库表中插入测试数据。

示例如下：

INSERT INTO `lineitem` (`l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment`) VALUES
    (1, 155190, 7706, 1, 17, 21168.23, 0.04, 0.02, 'N', 'O', '1996-03-13', '1996-02-12', '1996-03-22', 'DELIVER IN PERSON', 'TRUCK', 'egular courts above the'),
    (1, 67310, 7311, 2, 36, 45983.16, 0.09, 0.06, 'N', 'O', '1996-04-12', '1996-02-28', '1996-04-20', 'TAKE BACK RETURN', 'MAIL', 'ly final dependencies: slyly bold '),
    (1, 63700, 3701, 3, 8, 13309.60, 0.10, 0.02, 'N', 'O', '1996-01-29', '1996-03-05', '1996-01-31', 'TAKE BACK RETURN', 'REG AIR', 'riously. regular, express dep'),
    (1, 2132, 4633, 4, 28, 28955.64, 0.09, 0.06, 'N', 'O', '1996-04-21', '1996-03-30', '1996-05-16', 'NONE', 'AIR', 'lites. fluffily even de'),
    (1, 24027, 1534, 5, 24, 22824.48, 0.10, 0.04, 'N', 'O', '1996-03-30', '1996-03-14', '1996-04-01', 'NONE', 'FOB', ' pending foxes. slyly re'),
    (1, 15635, 638, 6, 32, 49620.16, 0.07, 0.02, 'N', 'O', '1996-01-30', '1996-02-07', '1996-02-03', 'DELIVER IN PERSON', 'MAIL', 'arefully slyly ex'),
    (2, 106170, 1191, 1, 38, 44694.46, 0.00, 0.05, 'N', 'O', '1997-01-28', '1997-01-14', '1997-02-02', 'TAKE BACK RETURN', 'RAIL', 'ven requests. deposits breach a'),
    (3, 4297, 1798, 1, 45, 54058.05, 0.06, 0.00, 'R', 'F', '1994-02-02', '1994-01-04', '1994-02-23', 'NONE', 'AIR', 'ongside of the furiously brave acco'),
    (3, 19036, 6540, 2, 49, 46796.47, 0.10, 0.00, 'R', 'F', '1993-11-09', '1993-12-20', '1993-11-24', 'TAKE BACK RETURN', 'RAIL', ' unusual accounts. eve'),
    (3, 128449, 3474, 3, 27, 39890.88, 0.06, 0.07, 'A', 'F', '1994-01-16', '1993-11-22', '1994-01-23', 'DELIVER IN PERSON', 'SHIP', 'nal foxes wake. ');

在 OceanBase 数据库中查询数据。

示例如下：

SELECT * FROM lineitem;

返回结果如下：

+------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+-------------------------------------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct    | l_shipmode | l_comment                           |
+------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+-------------------------------------+
|          1 |    155190 |      7706 |            1 |         17 |        21168.23 |       0.04 |  0.02 | N            | O            | 1996-03-13 | 1996-02-12   | 1996-03-22    | DELIVER IN PERSON | TRUCK      | egular courts above the             |
|          1 |     67310 |      7311 |            2 |         36 |        45983.16 |       0.09 |  0.06 | N            | O            | 1996-04-12 | 1996-02-28   | 1996-04-20    | TAKE BACK RETURN  | MAIL       | ly final dependencies: slyly bold   |
|          1 |     63700 |      3701 |            3 |          8 |        13309.60 |       0.10 |  0.02 | N            | O            | 1996-01-29 | 1996-03-05   | 1996-01-31    | TAKE BACK RETURN  | REG AIR    | riously. regular, express dep       |
|          1 |      2132 |      4633 |            4 |         28 |        28955.64 |       0.09 |  0.06 | N            | O            | 1996-04-21 | 1996-03-30   | 1996-05-16    | NONE              | AIR        | lites. fluffily even de             |
|          1 |     24027 |      1534 |            5 |         24 |        22824.48 |       0.10 |  0.04 | N            | O            | 1996-03-30 | 1996-03-14   | 1996-04-01    | NONE              | FOB        |  pending foxes. slyly re            |
|          1 |     15635 |       638 |            6 |         32 |        49620.16 |       0.07 |  0.02 | N            | O            | 1996-01-30 | 1996-02-07   | 1996-02-03    | DELIVER IN PERSON | MAIL       | arefully slyly ex                   |
|          2 |    106170 |      1191 |            1 |         38 |        44694.46 |       0.00 |  0.05 | N            | O            | 1997-01-28 | 1997-01-14   | 1997-02-02    | TAKE BACK RETURN  | RAIL       | ven requests. deposits breach a     |
|          3 |      4297 |      1798 |            1 |         45 |        54058.05 |       0.06 |  0.00 | R            | F            | 1994-02-02 | 1994-01-04   | 1994-02-23    | NONE              | AIR        | ongside of the furiously brave acco |
|          3 |     19036 |      6540 |            2 |         49 |        46796.47 |       0.10 |  0.00 | R            | F            | 1993-11-09 | 1993-12-20   | 1993-11-24    | TAKE BACK RETURN  | RAIL       |  unusual accounts. eve              |
|          3 |    128449 |      3474 |            3 |         27 |        39890.88 |       0.06 |  0.07 | A            | F            | 1994-01-16 | 1993-11-22   | 1994-01-23    | DELIVER IN PERSON | SHIP       | nal foxes wake.                     |
+------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+-------------------------------------+
10 rows in set

说明

由于需要加载 jar 包等资源，首次运行时比较慢。

数据类型

MySQL 数据库中有丰富的数据类型，不过 OceanBase 数据库的 MySQL 外表插件还未完全支持。当前版本 OceanBase 数据库的 MySQL 外表插件支持的数据类型如下所示：

整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT（含 UNSIGNED）。
浮点数：FLOAT、DOUBLE、DECIMAL。
字符串类型：CHAR、VARCHAR。
文本类型：TEXT、CLOB。
二进制类型：BINARY、VARBINARY、BLOB。
日期时间：DATE、TIME、DATETIME、TIMESTAMP、YEAR。
布尔类型：BOOLEAN。
空间类型：GIS 相关。
其它类型：JSON、ENUM、SET。

谓词下推规则

为了提高数据的访问效率，OceanBase 数据库支持部分谓词下推到远程 MySQL 数据库。

说明

这里的谓词就是 SQL 中的 WHERE 条件，有些地方写作 predicate 或 filter。

支持下推的谓词的条件：

数据类型：整数、浮点数（部分场景）、字符串（不包括 CHAR 类型）。
运算符：=、!=(<>)、<=、<、>=、>、IS [NOT] NULL、[NOT] IN、[NOT] LIKE、[NOT] BETWEEN。
组合表达式：AND、OR、NOT。

系统升级

对于已有的 OceanBase 集群，可以采取逐个节点重启的办法安装或升级插件。

建议升级步骤如下：

将新版本的插件 jar 包放到指定目录（配置项 ob_java_connector_path 指向的目录）。
逐个重启节点，重启时检查节点加载插件情况。
OceanBase 集群重启完成，检查集群插件安装情况。

关于集群升级的信息，参见升级 OceanBase 集群。

注意

功能限制

前提条件

配置 MySQL 外表插件的 jar 包路径

注意

配置 OceanBase 数据库参数（重启后生效）

说明

说明

说明

注意

使用示例

步骤一：环境准备

步骤二：创建测试表

注意

说明

数据类型

谓词下推规则

说明

系统升级

相关文档