All Products
Document Center

Cross-database query

Last Updated: Jun 18, 2020

Dynamic SQL Object (DSQL), which is used for cross-database queries, has attracted a large number of users since its release on Alibaba Cloud. To make it easier for you to query data across databases, the cross-database query feature is now available in the Data Management Service (DMS) console. You can easily query data across instances and databases in DMS.

Applicable scope

  • Currently, DMS allows you to query data across the following types of databases: PolarDB, MySQL, SQL Server, PostgreSQL, Distributed Relational Database Service (DRDS), and Redis.
  • You can only query data across physical databases instead of logical databases.

Entrance to cross-database query

In DMS, you can use the cross-database query feature in one of the following ways:

  • In the DMS console, choose SQLConsole > Cross-database Query in the top navigation bar.
  • Click Go to Cross-database Query on a database query page where cross-database query is supported.

View database links, databases, and tables

  • On the cross-database query page, database links corresponding to the instances for which you have access permissions appear in the left-side pane.The access permissions mentioned herein indicate the permissions to access the databases and tables under an instance. The DMS administrator and database administrator (DBA) have permissions to access all instances.

  • You can view all database links of your enterprise. However, you must apply for the corresponding permission if you need to access the specific database or table. You can apply for the permission directly on the cross-database query page.

  • You can click a database link to view the list of databases for which you have permissions. Similarly, if you click a database, you can view the list of tables for which you have permissions.

  • You can also enter the corresponding keyword to search for databases for which you have permissions or all databases of your enterprise. Here, databases for which you have permissions indicate that you have permissions to access such databases or permissions to access tables in such databases.

Perform queries in the SQL editor

  • If you double-click a table for which you have the permission or drag the table to the SQL editor, the corresponding SQL statement is automatically generated.
  • In the SQL editor, you can perform join queries on databases and tables under an instance for which you have access permissions. You only need to specify the table to be queried in the format of DBLinkName.databaseName.tableName.

Apply for permissions

  • If you right-click a database, the Go to SQL Console and View Database Permissions buttons appear.

    • If you click Go to SQL Console, the query page corresponding to the database appears.
    • If you click View Database Permissions, you can view your permissions on the database. If you do not have the access permission, you can apply for the permission.
  • When you click a table or double-click a table to generate an SQL statement for query, a prompt appears if you do not have related table permissions. Similar to the application for database permissions, you can directly apply for related table permissions.

View more data

  • If you want to view no more than 3,000 data entries in a cross-database query, you can contact the DMS administrator, who can then go to the Security Rules page and change the maximum number of rows returned for a query.
  • If you want to view more than 3,000 data entries, you can use the data development feature of DMS to synchronize data to a table and then export the data from the table.

Generate a database link and change its name

  • DMS automatically creates database links for instances that exist before the cross-database query feature is released. The default name is in the DBLink_{number} format.

  • The DMS administrator and DBA can choose System Management > Instance in the top navigation bar to view the names of the database links corresponding to such instances.

  • If you import multiple instances at a time, DMS automatically creates and names database links for such instances.

  • If you add a single instance, you can manually name the database link corresponding to the instance. The naming dialog box is the same as the instance editing dialog box.

  • By default, the cross-database query feature is enabled for instances supported by DMS. You can disable this feature for an instance in the instance editing dialog box.

  • In the instance editing dialog box, you can change the default name of a database link.

  • If you have the DMS administrator or DBA permission, you can go to the cross-database query page and right-click a database link to change its default name.

View cross-database query logs

  • If you have the permission to view operations logs, you can click Cross-database Data Query on the Operation Logs page to view cross-database query logs.

Important notes

  • When you add a single instance, you cannot change the name of a database link after you name the database link.
  • The name of a database link must be unique in your enterprise.
  • For database links automatically created by DMS for existing instances and instances imported in batches, you can change their names only once.