MySQL external table plugin

This topic describes how to install and use the MySQL external table JDBC plugin in OceanBase Database. With this plugin, you can access data from MySQL databases using JDBC.

Limitations

• This feature is not supported in OceanBase Database's Oracle-compatible 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 are not pushed down to the data source as a single SQL statement.
  • Aggregation functions and LIMIT are not pushed down.

• Dependency management: JAR packages cannot be loaded dynamically. Place the JAR files in the specified directory before starting the process.

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

• Parameter modifications: The PARAMETERS attribute of a table cannot be modified. You must recreate the table.

Prerequisites

• OceanBase Database is deployed and a MySQL-compatible tenant is created.
• JDK (version ≥ 11) is installed, and the JAVA_HOME environment variable is configured.

Configure the JAR path for the MySQL external table plugin

Download the JAR package for the MySQL external table plugin from the download page and place it in the directory specified by ob_java_connector_path.

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 (takes effect after a restart)

1. Enable Java.

   Here is an example:

   ALTER SYSTEM SET ob_enable_java_env = true;
   

   For more information, see ob_enable_java_env.

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

   Note

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

   Here is an example:

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

   For more information, see ob_java_home.

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

   Note

   Set only the directory for the MySQL external table plugin JAR file. You do not need to specify the JAR file name. All JAR files in the directory will be automatically loaded.

   Here is an example:

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

   For more information, see ob_java_connector_path.

4. Set the relevant parameters for the Java environment.

   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.

      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, see ob_java_opts.

      Notice

      • Changing this parameter requires a restart of the observer process. Since the current memory copy is a direct data stream copy to the C++ memory heap, you can reduce the -Xmx2048m -Xms2048m setting.
      • The corresponding GC log files must exist in the specified configuration folder. If the folder does not exist, the 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 return 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 Java external table plugin, jdbc is a JDBC data source based on the Java plugin, and mysql is a MySQL JDBC data source 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. 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 return 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

   Due to the need to load jar packages and other resources, the initial run is relatively slow.

Data types

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

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

Predicate Pushdown Rules

To improve data access efficiency, OceanBase Database supports partial predicate pushdown to remote MySQL databases.

Predicates that can be pushed down must meet the following conditions:

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

System upgrade

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 and check whether the plugin is loaded.
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.