The DBLink feature of OceanBase AI Database enables cross-data source access, allowing you to access remote databases from a local database. After a DBLink is successfully created, you can use it to access objects in a remote database, including tables and views.
Limitations
Currently, the DBLink read feature of OceanBase AI Database supports only reading data between different tenants.
The data types supported for access via DBLink in OceanBase AI Database are as follows:
Numeric types:
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,FLOAT,DOUBLE,DECIMAL.Time types:
DATE,TIME,YEAR,DATETIME,TIMESTAMP.Character types:
CHAR,VARCHAR,TINYBLOB,TINYTEXT,BLOB,TEXT,MEDIUMBLOB,LONGBLOB,LONGTEXT.Other types:
ENUM,SET.Note
Due to limitations of OBClient, when reading table data via DBLink, columns of the
ENUMandSETtypes in the remote table are treated asVARCHARtype columns.
For more information about each data type, see Overview of data types.
Prerequisites
A DBLink has been created. For details on creating a DBLink, see Create a DBLink.
Query data from a remote database table
The SQL statement is as follows:
SELECT select_expr_list FROM table_name@dblink_name WHERE where_conditions;
The parameters are described as follows:
select_expr_list: Specifies the expressions or column names to query. Separate multiple columns with commas (,). An asterisk (*) indicates all columns.table_name@dblink_name: Specifies the table in the remote database to query. Separate multiple tables with commas (,). By default, it accesses tables in the database specified when creating the DBLink. To access tables in other databases, specify them in the SQL statement in the formatdatabase_name.table_name@dblink_name.where_conditions: Specifies the filter conditions. The query results will only include data that meets the conditions. This is optional.
Example
To use DBLink, perform the following steps:
Use the following SQL statement to create a DBLink connecting to a remote OceanBase AI Database. The DBLink name is
my_link, the remote tenant ismysql001, and the default access database istest.CREATE DATABASE LINK my_link CONNECT TO root@mysql001 DATABASE test IDENTIFIED BY '******' HOST '10.10.10.1:2881';Use the following SQL statement to query data in the remote database through the DBLink created in Step 1.
Query data from the
test_tbl1table in thetestdatabase of the remote database throughmy_link.SELECT * FROM test_tbl1@my_link WHERE id = 1;The query result is as follows:
+------+------+ | id | name | +------+------+ | 1 | A1 | +------+------+ 1 row in setRead the
test_tbl1table from thetestdatabase of the remote database throughmy_linkand perform a JOIN operation with thetbl1table in the local database.SELECT a.id, b.name FROM tbl1 a, test_tbl1@my_link b WHERE a.id = b.id;The query result is as follows:
+------+------+ | id | name | +------+------+ | 1 | A1 | | 2 | A2 | +------+------+ 2 rows in setRead data from the
studenttable in thestudent_dbdatabase (the database specified when creating the DBLink istest) of the remote database throughmy_link.SELECT * FROM student_db.student@my_link LIMIT 3;The query result is as follows:
+------+---------+--------+------+-------+-----------------+ | id | name | gender | age | score | enrollment_date | +------+---------+--------+------+-------+-----------------+ | 1 | Emma | 0 | 20 | 85 | 2021-09-01 | | 2 | William | 1 | 21 | 90.5 | 2021-09-02 | | 3 | Olivia | 0 | 19 | 95.5 | 2021-09-03 | +------+---------+--------+------+-------+-----------------+ 3 rows in set
References
For more information about data types, see Overview of data types.
For more information about creating a DBLink, see Create a DBLink.
For more information about using a DBLink, see Access data in a remote database through DBLink.
For more information about using
SELECT, see SELECT.