All Products
Search
Document Center

Data Management:Schema design for logical tables

Last Updated:Aug 29, 2023

The schema design feature that is provided by Data Management (DMS) allows you to change the schema of a table to make the table in compliance with specific R&D standards. You can customize R&D processes for different business to ensure the consistency of schemas among multiple environments, such as the development environment, test environment, and production environment. This topic describes how to use the schema design feature for logical tables.

Prerequisites

  • The databases are of the following types:

    • MySQL: ApsaraDB RDS for MySQL, PolarDB for MySQL, ApsaraDB MyBase for MySQL, and MySQL databases from other sources

    • OceanBase

  • The database instance to which the database belongs is managed in Security Collaboration mode. For more information, see View the control mode of an instance.

  • A logical database of the development environment type is specified as the base database in the design phase, and a logical database of the production environment type is specified as the required database in the release phase. For more information about how to configure the logical databases and environment types, see Logical database and Change the environment type of an instance.

    Note

    Based on the default security rules of MySQL, the database environment in the structural design phase of the R&D process is configured as the development database, and the database environment in the release phase is configured as the production environment. You can adjust the R&D process based on your business requirements. For more information, see Schema design.

  • You need to have the permission to make changes to the base and required databases.

Usage notes

The types of the base and required databases must be the same. For example, if the type of the base database is MySQL, the required database must also be of the MySQL type.

Background information

After a database or table is sharded, the DDL statements that are executed to change the schema of a shard must take effect in other shards at the same time. In addition, do not perform complicated operations if possible. The schema design feature provided by DMS is based on logical databases and logical tables, which enables tables in sharded databases to be used as single tables on which users can perform transparent operations.

Procedure

  1. Log on to the DMS console V5.0.
  2. In the top navigation bar, choose Database Development > Schema Change > Schema Design.

    Note

    If you use the DMS console in simple mode, move the pointer over the 2022-10-21_15-25-22.png icon in the upper-left corner of the DMS console and choose All functions > Database Development > Schema Change > Schema Design.

  3. On the Schema DesignTickets page, click Schema Design in the upper-right corner.

  4. Set the parameters for creating a schema design ticket and click Submit. The following table describes the parameters. Description of parameters:

    Parameter

    Description

    Change Base Database

    The logical database on which you want to perform schema design operations. In this example, select a database in Secure Collaboration mode. The type of the environment to which the logical database belongs must conform to the R&D standards corresponding to the security rules of the database. For more information, see Schema design.

    Change Stakeholder

    The stakeholders of the schema design. All the specified stakeholders can view the ticket details and assist developers in accelerating the development and approval processes.

    Note

    Irrelevant users other than DMS administrators and database administrators (DBAs) are not allowed to view the ticket details.

  5. Create a logical table.

    You can create a logical table in one of the following ways:

    • Use the GUI

      1. Click Create Logical Table.

      2. On the Create Logical Table tab, click Basic Info, Column Info, and Index Info to set parameters about the basic information, columns, and indexes.

      3. Click Partition Table Topology and enter the expression of the logical table in the Logical Table Expression field. For more information about the expression of a logical table, see Expressions of logical tables.

        Note

        If you modify the expression of a logical table, DMS deletes the original table and creates a new table based on the modified expression. This operation cannot be undone. Proceed with caution.

      4. Click Calculate Table Topology Distribution to preview the name and the physical distribution of the logical table. If the name or the physical distribution does not meet your requirements, modify the expression and try again.

      5. Click Save.

    • Use SQL statements

      1. Click Import SQL Statements.

      2. In the dialog box that appears, enter the CREATE TABLE or ALTER TABLE statement as required and click OK.

        For example, you can use the following statement to create tables named orders_logic_[05] and orders_logic_[06]:

        CREATE TABLE `orders_logic_[05-06]` (
            `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
            `gmt_create` datetime NOT NULL COMMENT 'create time',
            `gmt_modified` datetime NOT NULL COMMENT 'modify time',
            PRIMARY KEY (`id`)
        ) DEFAULT CHARACTER SET=utf8 COMMENT='Create Logical Table';
      3. On the Create Logical Table: orders_logic_[05-06] tab, confirm the table information and schema and click Save.

        Note

        We recommend that you do not modify the expressions of the table shards in existing tables.

  6. In the Precheck Result message, view the precheck result.
    • If the precheck is passed, click Confirm Changes and Submit to Save.
    • If only a warning is displayed, click Ignore the warning and continue to save.. Alternatively, click Close, modify the table schema based on the R&D standards, and then click Save.
    • If an error is reported, check the error cause, click Close, modify the table schema based on the R&D standards, and then click Save.
    Note
    • DMS verifies the preceding SQL statements for table creation or modification based on the R&D standards that are configured in the default security rules, and then reports errors and warnings, if any.
    • DMS provides nearly 40 R&D standards to help you create tables and modify fields and indexes. You can configure the R&D standards in security rules as required.
    After the precheck is passed, you are navigated to the Project Homepage tab. On the Tables Changed in Project tab, you can view the table that you modified or created and modify or delete other tables as required.
  7. Apply the schema changes to the base database.

    1. Click Perform Changes to Base Database.Perform Changes to Base Database

    2. In the Perform Changes to Base Database panel, set the Execution Strategy, Canary Mode, and Canary Action parameters and click Submit.

    After the schema change operation is approved, the system automatically applies the schema changes to the base database. You can view the status of the operation on the Change Execution History tab.

    Note
    • The status of the operation on each physical database is listed on the tab.

    • You can check whether the schema changes are performed as expected on the SQLConsole tab of the base database.

    • Based on the default security rules, you must submit tickets for schema changes. You can also use custom security rules. For example, you can specify that no approval is required for schema changes in development databases. You can also specify the users who can approve schema change tickets in production databases.

  8. Click Go to Next Node. In the message that appears, click Go to Next Node.
  9. Apply the schema changes to the production database.

    1. On the Project Homepage tab, click Perform Changes to Target Database.

    2. In the Perform Changes to Target Database panel, select the destination database and click Submit.

      Note

      You can also click Add Multiple Databases to execute the script in multiple production databases.

      After the schema change operation is approved, the system automatically applies the schema changes to the production database. You can check whether the schema changes meet your expectations on the SQLConsole tab of the production database.

    3. Click Go to Next Node. In the message that appears, click Go to Next Node.
      After the ticket is closed, you can click each step to view the change and publishing records.

What to do next

After you create a logical table, you need to configure the routing algorithm. For more information, see Configure a routing algorithm.