Query data across multiple database instances

Limit

• The database instance runs one of the following database engines:
  • MySQL series: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, PolarDB-X, AnalyticDB for MySQL V3.0, and other MySQL databases.
  • SQL Server series: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and other SQL Server databases.
  • PostgreSQL series: 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 For more information, see Logical database.

Procedure

1. Go to the DMS console V5.0.
2. In the top navigation bar, choose SQL Console > Cross-database Query.
   Note If the cross-database query feature is enabled for your database instances, you can also click Cross-database Query on the SQLConsole tab of a database that belongs to one of the database instances. You can find the Cross-database Query button in the SQL execution section.
3. Check whether you are authorized to access the involved database instances.
   Note Parameter descriptions:

   • Permission:
     • Authorized: You are authorized to access the displayed database instances for which the cross-database query feature is enabled and the databases and tables on 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 on 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 instance and select View Database Permissions. In the Information dialog box, check whether you are authorized to access the database instance.

   • If the message "You have access to the database." appears, you are authorized to access the database instance and you can perform 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 instance. Perform the following steps to apply for access to the database instance:
     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 for Duration 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 instance 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.