Synchronize data across databases with task orchestration

Create the order, product, and reporting tables in your MySQL database.

1. Log in to DMS 5.0.

2. Move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > SQL Console > SQL Console.

   Note

   If you use the DMS console in normal mode, choose SQL Console > SQL Console in the top navigation bar.

3. In the Please select the database first dialog box, search for and select your MySQL database, and then click Confirm.

4. In the MySQL database, create the order table t_order, the product table t_product, and the reporting table t_order_report_daily to store synchronized analysis results.

   1. Create the order table named t_order. Paste the following SQL statement into the SQL editor and click Execute.

      SQL statement to create the t_order table:

      CREATE TABLE `t_order` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
        `product_id` bigint(20) NOT NULL COMMENT 'Product ID',
        `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
        `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Modification time',
        `customer_id` bigint(20) NOT NULL COMMENT 'Customer ID',
        `price` decimal(14,2) NOT NULL COMMENT 'Price',
        `status` varchar(64) NOT NULL COMMENT 'Order status',
        `province` varchar(256) DEFAULT NULL COMMENT 'Transaction province',
        PRIMARY KEY (`id`),
        KEY `idx_product_id` (`product_id`),
        KEY `idx_customer_id` (`customer_id`),
        KEY `idx_status` (`status`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='Order table'
      ;

      The structure of the t_order table is as follows:

      The table has eight columns: id, product_id, gmt_create, gmt_modified, customer_id, price, status, and province. The id column is the auto-incrementing primary key. The province column can be null. The gmt_create and gmt_modified columns default to CURRENT_TIMESTAMP.

   2. Create the product table named t_product. Paste the following SQL statement into the SQL editor and click Execute.

      SQL statement to create the t_product table:

      CREATE TABLE `t_product` (
        `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
        `name` varchar(128) NOT NULL COMMENT 'Product name',
        `type` varchar(64) NOT NULL COMMENT 'Product category',
        `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
        `gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Modification time',
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Product table'
      ;

      The structure of the t_product table is as follows:

      The table has five columns: id, name, type, gmt_create, and gmt_modified. The id column is the auto-incrementing primary key. The gmt_create and gmt_modified columns default to CURRENT_TIMESTAMP.

   3. Create the reporting table named t_order_report_daily. Paste the following SQL statement into the SQL editor and click Execute.

      SQL statement to create the t_order_report_daily table:

      CREATE TABLE `t_order_report_daily` (
        `dt` varchar(64) NOT NULL COMMENT 'Statistics date',
        `product_type` varchar(64) NOT NULL COMMENT 'Product type',
        `order_cnt` bigint(64) NOT NULL COMMENT 'Number of orders',
        `order_amt` decimal(38,2) NOT NULL  COMMENT 'Total amount',
        PRIMARY KEY (`dt`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Reporting table'
      ;

      The structure of the t_order_report_daily table is as follows:

      The table has four columns: dt, product_type, order_cnt, and order_amt. The dt column is the primary key.

In the AnalyticDB for MySQL data warehouse, create the order, product, and reporting tables to receive data and store analysis results.

1. Go to the SQL editor page. In the Please select the database first dialog box, search for and select your AnalyticDB for MySQL data warehouse, and then click Confirm.

2. In the AnalyticDB for MySQL data warehouse, create the t_order and t_product tables to receive synchronized data, and the t_order_report_daily table to store analysis results.

   Note

   For the SQL statements to create the t_order, t_product, and t_order_report_daily tables, see Step 4 in the preceding section.

3. Populate the t_order and t_product tables with test data. You can use the test data generation feature. For more information, see Test data generation.

   • Generate 20 million rows of data for the t_order table.

     Note

     For an instance in Flexible Management mode, each ticket can generate a maximum of 1 million rows.

   • Generate 10,000 rows of data for the t_product table.