Data Management: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

1. Log on to the DMS console V5.0.
2. Move the pointer over Shortcuts in the top navigation bar. Then, click Cross-database Query 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.
3. 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:
     1. Click Apply for Database Permission.
     2. In the Access apply dialog box, select the permissions for which you want to apply.
     3. Select a period of time from the Duration drop-down list based on your business requirements.
     4. 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.
     5. Click OK.
     After your application is approved, you are authorized to access the database and you can perform the next step.
4. 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;

5. In the Execution History section, view the execution result of the SQL statement.
   Note

   • You can also choose Security and Specifications > Operation Audit > Operation Logs 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 O&M > Configuration Management. 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.