You can use the cross-database query feature of Data Management (DMS) to efficiently
query data in databases and tables across multiple database instances. This topic
describes how to query data across multiple database instances.
Usage notes
- Each involved database instance runs one of the following database engines:
- MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL V3.0, and other MySQL databases
- SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and other SQL Server databases
- PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and other PostgreSQL databases
- PolarDB for Oracle
- ApsaraDB for Redis
- You can query data only across physical databases.
Note
- A physical database is a specific database.
- A logical database consists of one or more physical databases and supports sharding.
For more information, see Logical database.
Procedure
- Log on to the DMS console V5.0.
- Move the pointer over Shortcuts in the top navigation bar. Then, click in the SQL Console section.
Note If the cross-database query feature is enabled for your database instances, you can
also click Cross-database Query on the SQL Console tab of a database that belongs to one of the database instances.
You can find the Cross-database Query button in the SQLConsole section.
- Check whether you are authorized to access the involved databases.
Note Parameter descriptions:
- Permission:
- Authorized: The cross-database query feature is enabled for the database instances
of the displayed databases. You are authorized to access the database instances and
the databases and tables in the database instances. If you are a DMS administrator
or a database administrator (DBA), you are authorized to access all database instances.
- All: You are authorized to access all database instances for which the cross-database
query feature is enabled and the databases in the database instances within the tenant.
- Object:
- DBLink: the database links that are filtered based on the value of the Permission
parameter. A database link is established for each database instance.
- Database: the databases that are filtered based on the value of the Permission parameter.
In the left-side navigation pane, right-click an involved database and select View
Database Permissions. In the Information message, check whether you are authorized
to access the database.
- If the message "You have access to the database" appears, go to the next step.
- If the message "You do not have permission for this database. Do you want to apply
for permission now?" appears, you are not authorized to access the database. Perform
the following steps to apply for access to the database:
- Click Apply for Database Permission.
- In the Access apply dialog box, select the permissions for which you want to apply.
- Select a period of time from the Duration drop-down list based on your business requirements.
- In the Permission field, enter the reason and background for your application to reduce unnecessary
communication and simplify the approval process.
Note By default, You are not authorized to use SQL Console to query data. Click to submit a ticket. is used.
- Click OK.
After your application is approved, you are authorized to access the database and
you can perform the next step.
- In the SQLConsole section, enter the SQL statement that is used to query data across
the database instances and click Execute.
Sample SQL statement:
SELECT *
FROM dblink1.db1.table1 t1,
dblink2.db2.table2 t2
where t1.id= t2.id;
- In the Execution History section, view the execution result of the SQL statement.
Note
- You can also choose to view the operation logs of cross-database queries.
- By default, up to 100 rows can be returned for a cross-database query. To view more
rows of data, choose . Find the Maximum number of returned rows for cross-database queries parameter and click Change in the Actions column. In the dialog box that appears, specify the maximum number of rows that can
be returned for a cross-database query. The maximum number cannot exceed 3,000.
- If you want to view more than 3,000 rows for a cross-database query, you can use the
task orchestration feature to configure a cross-database Spark SQL node, write the
execution result set of the SQL statement to a temporary table, and then perform a
single-database query on the temporary table. For more information about cross-database
Spark SQL nodes, see Configure a cross-database Spark SQL node.