This topic describes how to install and use the external table JDBC plugin in OceanBase AI Database. With this plugin, you can access data sources that support JDBC (currently only MySQL data sources).
Notice
- OceanBase AI Database supports the external table JDBC plugin service. It also provides the MySQL external table plugin based on the JDBC plugin service.
- This feature is currently an experimental feature and is not recommended for use in a production environment.
Limitations
Data types: The
Arraydata type is not supported.Query limitations:
- Concurrent queries are not supported.
JOINoperations between multiple tables from the same data source are not pushed down to the data source as a single SQL statement.- Aggregation functions and
LIMITare not 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
PARAMETERSattribute of a table cannot be modified; a new table must be created.
Prerequisites
- OceanBase AI Database has been deployed, and a MySQL mode user tenant has been created.
- JDK (version 11 or later) has been installed, and the
JAVA_HOMEenvironment 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 (the ob_java_connector_path directory).
Notice
The OBServer service, which runs as the admin user, must have read and write permissions on the configured directory.
Here is an example:
Create a directory to store the JAR package.
mkdir -p /jdbc/plugin/jar/package/directoryNavigate to the directory.
cd /jdbc/plugin/jar/package/directoryDownload 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 AI Database parameters (effective after restart)
Note
If you are using the JAVA SDK for the first time, you do not need to restart the observer process.
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.
Set the Java home directory for the current OBServer node.
Note
This path is the HOME directory of the JDK. Set it to
$JAVA_HOMEin 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.
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 are 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.
Set the relevant Java environment startup parameters.
Create the corresponding log folder path.
mkdir -p /home/user/jvmlogs mkdir -p /home/user/jvmlogs/heapdumpsSet 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. Because the data stream is directly copied to the C++ memory heap, you can appropriately reduce the
-Xmx2048m -Xms2048msettings. - The corresponding GC log files are generated only if the configured log folder path exists. If the path does not exist, the related log files are not generated.
- Changing this parameter requires restarting the observer process. Because the data stream is directly copied to the C++ memory heap, you can appropriately reduce the
Restart OceanBase AI Database and check whether 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,
javais the general implementation of the Java external table plugin,jdbcis a Java JDBC data source implemented based on the Java plugin, andmysqlis a MySQL JDBC data source implemented based on thejdbcplugin.
Examples
Step 1: Prepare the environment
Assume that the following database environment is available:
Component |
Address |
Port |
User |
Database |
|---|---|---|---|---|
| MySQL database | xxx.xxx.xxx.1 | 3306 | root | test |
| OceanBase AI Database cluster | xxx.xxx.xxx.2 | 2881 | root@mysql001 | test |
Step 2: Create a test table
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;Create an external table in OceanBase AI 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=falseparameter is added to the JDBC connection string because the MySQL JDBC plugin uses version 8.0.3, which requires this option when connecting to MySQL version 5.7.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. ');Query data from OceanBase AI 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 setNote
Because resources such as the JAR package 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 AI Database does not fully support all of them. The following table lists the data types supported by the MySQL external table plugin of OceanBase AI Database in the current version:
- Integer types:
TINYINT,SMALLINT,MEDIUMINT,INT, andBIGINT(includingUNSIGNED). - Floating-point types:
FLOAT,DOUBLE, andDECIMAL. - String types:
CHARandVARCHAR. - Text types:
TEXTandCLOB. - Binary types:
BINARY,VARBINARY, andBLOB. - Date and time types:
DATE,TIME,DATETIME,TIMESTAMP, andYEAR. - Boolean type:
BOOLEAN. - Spatial types: related to
GIS. - Other types:
JSON,ENUM, andSET.
Predicate pushdown rules
To improve data access efficiency, OceanBase AI Database supports pushing down some predicates to the remote MySQL database.
Note
Here, a predicate refers to the WHERE condition in SQL, which is sometimes also called a filter or predicate.
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, andNOT.
Upgrade the system
For an existing OceanBase AI Database cluster, you can install or upgrade the plugin by restarting each node one by one.
We recommend that you perform the following steps:
- Place the new version of the plugin JAR package in the specified directory (the directory pointed to by the
ob_java_connector_pathparameter). - Restart each node one by one and check whether the node has loaded the plugin.
- After the OceanBase AI Database cluster is restarted, check whether the plugin is installed.
References
For more information about the syntax of the CREATE EXTERNAL TABLE statement, see CREATE EXTERNAL TABLE.