MySQL external table plugin

This topic describes how to install and use the external table JDBC plugin in OceanBase Database. With this plugin, you can access data sources that support JDBC (currently only MySQL data sources).

Limitations

• OceanBase Database in Oracle mode: This feature is not supported in Oracle mode.

• Data types: The Array data type is not supported.

• Query limitations:

  • Concurrent queries are not supported.
  • JOIN operations between multiple tables from the same data source will not be pushed down as a single SQL statement to the data source.
  • Aggregation functions and LIMIT will not be pushed down.

• Dependency management: JAR packages cannot be dynamically loaded. They must be placed in the specified directory before the process starts.

• Timeout settings: Query timeout cannot be configured through plugins.

• Parameter modification: The PARAMETERS attribute of a table cannot be modified; a new table must be created.

Prerequisites

• OceanBase Database has been deployed, and a MySQL mode user tenant has been created.
• JDK (version ≥ 11) has been installed, and the JAVA_HOME environment variable has been configured.

Configure the path of the MySQL external table plugin JAR package

Download the MySQL external table plugin JAR package from MySQL external table plugin download address and place it in the specified directory (ob_java_connector_path directory).

Here is an example:

1. Create a directory to store the JAR package.

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

2. Navigate to the directory.

   cd /jdbc/plugin/jar/package/directory
   

3. Download the MySQL external table plugin JAR package.

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

Configure OceanBase Database parameters (effective after restart)

1. Enable Java.

   Here is an example:

   ALTER SYSTEM SET ob_enable_java_env = true;
   

   For more information about this setting, see ob_enable_java_env.

2. Set the Java home directory for the current OBServer node.

   Note

   This path is the HOME directory of the JDK. Set it to $JAVA_HOME in the environment.

   Here is an example:

   ALTER SYSTEM SET ob_java_home = "/java/home/path";
   

   For more information about this setting, see ob_java_home.

3. Set the directory for the JDBC plugin JAR package.

   Note

   Only the directory of the MySQL external table plugin JAR package needs to be set. The JAR package name does not need to be specified, and the JAR packages in the directory will be automatically expanded.

   Here is an example:

   ALTER SYSTEM SET ob_java_connector_path = "/jdbc/plugin/jar/package/directory";
   

   For more information about this setting, see ob_java_connector_path.

4. Set the relevant Java environment startup parameters.

   1. Create the corresponding log folder path.

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

   2. Set the JVM startup parameters for Java execution.

      Here is an example:

      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";
      

      For more information about this setting, see ob_java_opts.

      Notice

      • Changing this parameter requires restarting the observer process. Since the current memory copy is directly copied to the C++ memory heap, you can appropriately reduce the -Xmx2048m -Xms2048m setting.
      • The corresponding GC log files must exist in the configured log folder. If the folder does not exist, the related log files will not be generated.

5. Restart OceanBase Database to check if the plugin is installed successfully.

   Here is an example:

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

   The returned result is as follows:

   +--------------+----------+-------+--------+----------------+---------+-----------------+------------------+-------------------+-----------------------+---------------+-----------------------------------------------------+
   | 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                 |
   +--------------+----------+-------+--------+----------------+---------+-----------------+------------------+-------------------+-----------------------+---------------+-----------------------------------------------------+
   

   In the result, java is the general implementation of the Java external table plugin, jdbc is a Java JDBC data source implemented based on the Java plugin, and MySQL is a MySQL JDBC data source implemented based on the jdbc plugin.

Examples

Step 1: Prepare the environment

Assume that the following database environment is available:

Step 2: Create a test table

1. Create a table in the MySQL database.

   Here is an example:

   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;
   

2. Create an external table in the OceanBase database.

   Here is an example:

   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"}'
       );
   

   Notice

   The useSSL=false parameter is added to the JDBC connection string because the MySQL JDBC plugin uses version 8.0.3, and this parameter is required when connecting to a MySQL 5.7 database.

3. Insert test data into the MySQL database table.

   Here is an example:

   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. ');
   

4. Query data from the OceanBase database.

   Here is an example:

   SELECT * FROM lineitem;
   

   The query result is as follows:

   +------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+-------------------------------------+
   | 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
   

   Note

   Because the jar package and other resources need to be loaded, the initial execution is relatively slow.

Data types

MySQL databases support a variety of data types, but the MySQL external table plugin of OceanBase Database does not fully support all of them. The following table lists the data types supported by the MySQL external table plugin of OceanBase Database in the current version:

• Integer types: TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT (including UNSIGNED).
• Floating-point types: FLOAT, DOUBLE, and DECIMAL.
• String types: CHAR and VARCHAR.
• Text types: TEXT and CLOB.
• Binary types: BINARY, VARBINARY, and BLOB.
• Date and time types: DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
• Boolean type: BOOLEAN.
• Spatial types: related to GIS.
• Other types: JSON, ENUM, and SET.

Predicate pushdown rules

To improve data access efficiency, OceanBase Database supports pushing down some predicates to the remote MySQL database.

The supported predicates are subject to the following conditions:

• Data types: Integer, floating-point (in some scenarios), and string (excluding CHAR).
• Operators: =, !=(<>), <=, <, >=, >, IS [NOT] NULL, [NOT] IN, [NOT] LIKE, and [NOT] BETWEEN.
• Combined expressions: AND, OR, and NOT.

Upgrade the system

For an existing OceanBase cluster, you can install or upgrade the plugin by restarting each node one by one.

We recommend that you perform the following steps:

1. Place the new version of the plugin jar package in the specified directory (the directory pointed to by the ob_java_connector_path parameter).
2. Restart each node one by one and check whether the node has loaded the plugin.
3. After the OceanBase cluster is restarted, check whether the plugin is installed.

For more information about how to upgrade an OceanBase cluster, see Upgrade an OceanBase cluster.

References

For more information about the syntax of the CREATE EXTERNAL TABLE statement, see CREATE EXTERNAL TABLE.