Use a DBLink in queries

The DBLink feature of OceanBase Database enables cross-database access, allowing you to access remote databases from the local database.

Prerequisites

• The account used to create the DBLink must be a superuser. For more information about how to create a superuser, see Create and manage an account.
• The remote database tenant must configure whitelists based on the local access address. For more information, see Set allowlist groups.
• You have obtained the connection information of the remote database. For more information, see Database connection overview.

Create a DBLink

After you create a DBLink, you can access objects such as tables and views in the remote database through the DBLink.

Limitations

• You cannot create a DBLink from a MySQL-compatible tenant of OceanBase Database to a MySQL database.
• The write and reverse link features are not supported in MySQL compatible mode.

Create a DBLink between OceanBase Databases

To create a DBLink named dblink_name that accesses a specified remote database, you need to specify the name of the DBLink and provide the name of the remote database, account name, tenant ID, password, and access address of the remote database.

In MySQL compatible mode, the SQL syntax for creating a DBLink between MySQL-compatible tenants of OceanBase Database is as follows:

CREATE DATABASE LINK [IF NOT EXISTS] dblink_name CONNECT TO user@tenantid DATABASE database_name IDENTIFIED BY 'remote_password' HOST 'ip:port';

The parameters are described as follows:

• dblink_name: the name of the DBLink to be created.

• IF NOT EXISTS: a syntax compatible with MySQL databases. If you specify IF NOT EXISTS, errors will not be reported even if the DBLink to be created already exists. If you do not specify it but the DBLink already exists, the system will report an error.

• user: the account name of the remote OceanBase Database.

• tenantid: the tenant ID of the remote OceanBase Database.

• remote_password: the login password of the remote OceanBase Database account. If the password contains special characters (non-alphanumeric) such as @#!, enclose the password in single or double quotation marks to to avoid a syntax error.

• database_name: the name of the database in the MySQL-compatible tenant of the remote OceanBase Database. By default, this database is accessed when you read data through the DBLink. If you want to access other databases, you can specify a specific database for the table in the SQL statement.

• ip:port: the access address of the MySQL-compatible tenant of the remote OceanBase Database.

  If the access address is a private IP address, the network between the local database and the specified tenant must be accessible.

Here is an example:

• Create a DBLink named ob_dblink that connects to the remote OceanBase Database, whose IP address and port number are those of a specified OBServer node.

  CREATE DATABASE LINK IF NOT EXISTS ob_dblink CONNECT TO username@t******** DATABASE test_database IDENTIFIED BY '********' HOST 'xx.xx.xx.xx:3306';
  

Use a DBLink to query data from a remote database table

Limitations

• In MySQL compatible mode, the following data types are supported for access through DBLink:

  • 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.

    Notice

    Due to the limitations of OBClient, when you read data from a remote table using DBLink, the ENUM and SET columns are treated as VARCHAR columns.

  For more information about these data types, see Overview of data types.

Procedure

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: the expressions or column names to be queried. Separate multiple columns with commas (,), and use an asterisk (*) to indicate all columns.

• table_name@dblink_name: the names of the remote tables to be queried. Separate multiple tables with commas (,). By default, the database specified when the DBLink is created is accessed. To access a table in another database, specify the database name in the format of database_name.table_name@dblink_name.

• where_conditions: the filter conditions. Only the data that meets the conditions is returned in the query result. This parameter is optional.

Examples

To use a DBLink, perform the following steps:

1. Run the following SQL statement to create a DBLink named my_link that connects to the MySQL compatible mode of an OceanBase database. The remote account is test_mysql, the tenant ID is t*******, and the default database is test_database.

   CREATE DATABASE LINK my_link CONNECT TO test_mysql@t******** DATABASE test_database IDENTIFIED BY '******' HOST 'xx.xx.xx.xx:3306';
   

2. Use the DBLink created in Step 1 to query data from a remote database.

   • Query data from the test_tbl1 table in the test_database database using my_link.

     SELECT * FROM test_tbl1@my_link WHERE id = 1;
     

     The return result is as follows:

     +------+------+
     | id   | name |
     +------+------+
     |    1 | A1   |
     +------+------+
     1 row in set
     

   • Read data from the test_tbl1 table in the test_database database using my_link, and perform a join operation with the tbl1 table in the local database.

     SELECT a.id, b.name FROM tbl1 a, test_tbl1@my_link b WHERE a.id = b.id;
     

     The return result is as follows:

     +------+------+
     | id   | name |
     +------+------+
     |    1 | A1   |
     |    2 | A2   |
     +------+------+
     2 rows in set
     

   • Read data from the student table in the student_db database using my_link. Note that when you create the DBLink, the database specified by default is test_database.

     SELECT * FROM student_db.student@my_link LIMIT 3;
     

     The return 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