AnalyticDB:Schedule AnalyticDB for MySQL jobs using DMS

Background information

• Challenges: event scheduling

  Traditional database event scheduling, such as the MySQL Event Scheduler, is powerful but has several limitations:

  • High barrier to entry: Users must master special SQL syntax for event definitions (CREATE EVENT and ALTER EVENT) and cannot configure events through a simple interface.

  • Strong dependency on the database kernel: The kernel must support and enable the event scheduler.

  • Limited to a single database: Scheduling can only be performed for a single database and cannot be coordinated with other databases or tools.

  • Difficult to track: Users cannot view the execution status, execution history, or runtime of scheduled events.

  • Difficult to maintain: Users cannot pause or rerun events, and failed events cannot be recovered.

  • No notifications: Event execution status (success or failure) cannot be proactively sent to users via SMS, email, or other channels.

• Solution: DMS task orchestration

  The task orchestration feature of DMS resolves the preceding issues. It does not rely on the event scheduling capability of the database kernel. Instead, it is an independent external task orchestration and scheduling system. Key features include:

  • Supports multiple database engines (MySQL, Oracle, PostgreSQL, SQL Server, and others) and various ecosystem tools (data migration, backup, cross-database analysis, and others) to enable cross-database and cross-product workflows.

  • Provides a visual editing interface where you can create scheduling tasks through drag-and-drop operations and simple configurations.

  • Supports multiple notification mechanisms such as DingTalk, SMS, and email.

  • Supports various O&M operations such as pause, terminate, and rerun.

Sample data

In this example, the database name is adb_test. Three tables are created in this database: orders, finish_orders, and large_finish_orders.

create database adb_test;
create table orders(
  order_id bigint not null comment'order ID',
  order_status varchar not null comment'order state',
  total_price decimal(15,2) not null comment'total price',
  order_date date not null comment'orderdate',
  PRIMARY KEY (order_id)
  );
 create table finish_orders(
  order_id bigint not null comment'order ID',
  total_price decimal(15,2) not null comment'order state',
  order_date date not null comment'total price',
  PRIMARY KEY (order_id)
  );
  create table large_finish_orders(
  order_id bigint not null comment'order ID',
  total_price decimal(15,2) not null comment'order state',
  order_date date not null comment'total price',
  PRIMARY KEY (order_id)
  ); 

Overview

This topic uses an AnalyticDB for MySQL Data Warehouse Edition cluster as an example to describe the job scheduling process. You use the task orchestration feature of DMS to filter completed orders with an order amount greater than 10,000 USD from the orders table in an AnalyticDB for MySQL database.

Step 1: Create a task flow

1. Log on to the AnalyticDB for MySQL console. In the upper-left corner, select the region where the cluster is located. In the left-side navigation pane, click Clusters. Find the target cluster and click the cluster ID.

2. In the left-side navigation pane, choose Unified Data Management > Data Assets.

   Note

   • If this is the first time you log on to an AnalyticDB for MySQL cluster database through DMS (new version) to use the data asset management feature, you must first enter the database logon information. For more information, see Log on to a database.

   • If you have previously logged on to an AnalyticDB for MySQL cluster database and the cluster control mode is set to Flexible Management or Stable Change, but you did not select Remember Password when entering the database logon information, after you go to the DMS console, you must enter the password of the target database account in the dialog box that appears before you can proceed. For more information about control modes, see Control modes.

   • The unified data management feature is not supported for AnalyticDB for MySQL clusters in the Indonesia (Jakarta) region.

3. In the top menu bar, choose Integration & Development (DTS).

4. In the left-side navigation pane, choose Data Development > Task Orchestration.

5. Create a new task flow. In this example, the task flow is named Order Filtering.

   1. Click Create Task Flow.

   2. In the New Task Flow dialog box, enter the Task Flow Name and Description, and then click OK.

Step 2: Create task nodes

1. On the task orchestration page for Order Filtering, create and configure the following two task nodes:

   • Order Cleansing

     1. In the left-side pane, select Single Instance SQL and drag it to the blank canvas on the right.

     2. Select the newly added task node and click the icon to rename it Order Cleansing.

     3. Double-click the task node or click the icon to edit the task node.

     4. Select the target database from the database drop-down list.

     5. In the dialog box below the target database, enter the task statement and click Save. The statement is as follows:

        insert into finish_orders
         select order_id,total_price,order_date
         from orders
         where order_status = 'F';

        Note

        If Auto Save is selected, the SQL statement is saved automatically after you enter it.

   • Large Order Generation

     1. In the left-side pane, select Single Instance SQL and drag it to the blank canvas on the right.

     2. Select the newly added task node and click the icon to rename it Large Order Generation.

     3. Double-click the task node or click the icon to edit the task node.

     4. Select the target database from the database drop-down list.

     5. In the dialog box below the target database, enter the task statement and click Save. The statement is as follows:

        insert into large_finish_orders
         select order_id,total_price,order_date
         from finish_orders
         where total_price > 10000;

        Note

        If Auto Save is selected, the SQL statement is saved automatically after you enter it.

2. On the task flow canvas, hover over the Order Cleansing node. Click the small circle on the right side of the node and drag a connection line to the Large Order Generation node to create a dependency between the two task nodes.

Step 3: Configure task flow scheduling

1. In the Scheduling Settings section below the task flow canvas, turn on the Enable Scheduling switch and configure the relevant parameters.

   Note

   In this example, the scheduling task is set to run daily at 01:00 from 2023-02-01 to 2023-02-28. You can also customize the scheduling task based on your business needs. For more information about scheduling configuration parameters, see Configure scheduling.

2. Publish the task flow.

   1. Click Publish in the upper-left corner of the canvas.

   2. In the Publish dialog box, enter the Remarks information and click OK to publish the task flow.

3. Confirm the publication status.

   1. Click Go to O&M above the canvas.

   2. On the right side of the page, check the publication status of the task flow.

      • Published: The task flow is in the published state.

      • Unpublished: The task flow is in the unpublished state.