Data Management (DMS) allows you to configure logical databases and logical tables to facilitate data query and management in database shards and table partitions. You can configure a logical database or logical table for one or more physical databases. This way, you can query and manage the complex and massive database shards and table partitions in the physical databases with ease.

Background information

In database sharding and table partitioning, business data is distributed to numerous tables in multiple physical databases based on routing algorithms. This may raise the following issues:
  • Data query and analysis can be difficult. You may need to traverse all the tables to query the data that you want.
  • The workload is heavy to add or remove a field to or from a table partition in a database shard.

To resolve the preceding issues, DMS allows you to create a logical table for physical tables that have the same table schema and table name prefix. This way, you can manage these physical tables by managing the logical table.

Prerequisites

  • The databases are of the following types:
    • Relational databases
      • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, MyBase for MySQL, AnalyticDB for MySQL V3.0, and MySQL databases from other sources
      • SQL Server: ApsaraDB RDS for SQL Server, MyBase for SQL Server, and SQL Server databases from other sources
      • PostgreSQL: ApsaraDB RDS for PostgreSQL, PolarDB for PostgreSQL, MyBase for PostgreSQL, AnalyticDB for PostgreSQL, and PostgreSQL databases from other sources
      • OceanBase: ApsaraDB for OceanBase in MySQL mode, ApsaraDB for OceanBase in Oracle mode, and self-managed OceanBase databases
      • PolarDB for Oracle
      • Oracle
      • Db2
    • Data warehouses: DLA, ApsaraDB for ClickHouse, MaxCompute, and Hologres
  • The database instances to which the physical databases belong are managed in Secure Collaboration mode. For more information, see View the control mode of an instance.
  • You are the owner of the physical databases. For more information about how to view the owner of a database, see View resource roles.

Configure a logical database

  1. Go to the DMS console V5.0.
  2. In the top navigation bar, click Data Assets. In the left-side navigation pane, click Global Search.
  3. Enter the name of a physical database in the search box, and click the Search icon icon.
  4. Find the physical database, move the pointer over More in the Actions column, and then select Configure Logical Database.
    Configure a logical database
    Note This physical database is the first database shard of the logical database.
  5. Optional:In the Create Logical Database Configuration dialog box, add other physical databases to the logical database.
    Note This step is optional. You can also configure a logical database only for one physical database.
    The physical databases to be added to the same logical database must meet the following requirements:
    • These physical databases are of the same type, such as MySQL.
    • These physical databases are of the same environment type, such as the development environment. For more information, see Change the environment type of an instance.
    • A physical database can belong only to one logical database. If the physical database belongs to an existing logical database, remove the physical database from the logical database and try again.
    To add other physical databases to the logical database, perform the following steps:
    • Enter the name of the physical database in the search box. Select the physical database in the search results.
    • Click the 5 icon to add the selected physical database to the database list on the right.
      Note In the right-side database list, the physical databases must be in the same order as they are in the logical database. Otherwise, the queries of table partitions in the logical database may be affected.
  6. Name the logical database. The name is in the Logical database name[Alias] format.
    The default name of the logical database is the name of the first database shard in the logical database. You can set the Alias parameter to provide additional information about the logical database.
  7. Assign one or more data owners for the logical database.
    • If you are a DMS administrator or a database administrator (DBA), you can click Change Data Owners to assign one or more data owners for the logical database. The owners of the logical database are also the owners of all physical databases in the logical database.
    • If Failed to provide recommendations because databases in the logical database belong to different owners. appears, the physical databases added to the logical database have different owners. In this case, you must specify the same owner for all the physical databases.
      Note For more information about how to apply for the owner permissions, see Manage permissions.
  8. Click Save Configuration.

What to do next

After you configure a logical database, create logical tables to query data in the logical database. For more information, see Logical table.