AnalyticDB for MySQL allows you to use the task orchestration feature of Data Management (DMS) to orchestrate, schedule, manage, and monitor AnalyticDB for MySQL tasks. This topic describes how to use DMS to develop and schedule tasks.

Background information

  • Challenges and demands: event scheduling
    Conventional event schedulers such as MySQL Event Scheduler provide powerful capabilities in scheduling database events. However, these event schedulers have the following limits:
    • High technical requirements: You must be familiar with the syntax of specific SQL statements that are used to define events, such as CREATE EVENT and ALTER EVENT. You cannot define events by performing simple configurations on a GUI.
    • Dependency on the database kernel: The kernel must support the event scheduler. The event scheduler must be enabled.
    • Applicability only to a single database: Scheduling can be performed only for a single database. You cannot use a conventional event scheduler to schedule events across databases or collaborate with other tools.
    • Difficulty in tracking: You cannot view the execution status, history, and duration of a scheduled event.
    • Difficulty in O&M: You cannot pause or rerun an event. You cannot restore an event after the event fails to be executed.
    • Incapability of sending notifications: Conventional event schedulers cannot inform you whether an event is executed or fails to be executed by SMS or email.
  • Solution: DMS task orchestration
    The task orchestration feature of DMS can resolve these issues. This feature provides an external orchestration and scheduling system that is independent of the database kernel for scheduling events. The task orchestration feature provides the following benefits:
    • Supports more than 10 types of database engines such as MySQL, Oracle, PostgreSQL, and SQL Server as well as a variety of database tools. The database tools provide features such as data migration, data backup, and cross-database analysis. This way, you can schedule events across databases and services with ease.
    • Provides a GUI. You can create complex scheduling tasks by drag-and-drop operations and simple configurations on the GUI.
    • Provides a variety of notification channels, such as email, SMS, and DingTalk.
    • Supports various O&M operations, such as Pause, Exits, and Rerun.

Precautions

AnalyticDB for MySQL clusters in the Indonesia (Jakarta) region do not support the end-to-end data management feature.

Workflow

In this example, you must use the task orchestration feature of DMS to filter completed orders whose total price is greater than USD 10,000 from the orders table of an AnalyticDB for MySQL database. Perform the following operations:

Note The following database information is used in this example:
  • Database name: adb_test.
  • You have created the orders, finish_orders, and large_finish_orders tables in the database. The following statements are used to create the tables:
    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)
      ); 
Step Description
Step 1: Create a task flow Create a task flow.
Step 2: Create a task node Create the following task nodes in the created task flow:
  • Order Cleansing: filters completed orders from the orders table by using the order_status=F filter condition and writes the results to the finish_orders table.
  • Large Order Generation: filters orders whose total price is greater than USD 10,000 from the finished_orders table by using the total_price>10000 filter conditionand writes the results to the large_finish_orders table.
Step 3: Configure task flow scheduling Start the task and make it periodically run.

Step 1: Create a task flow

  1. Log on to the AnalyticDB for MySQL console by your Alibaba Cloud account.
  2. In the upper-left corner of the page, select the region where clusters reside.
  3. In the left-side navigation pane, click Clusters.
  4. On the V3.0 Clusters tab, click the target Cluster ID.
  5. In the left-side navigation pane, choose End-to-end Data Management > Job Development and Scheduling.
    Note
    • When you log on to an AnalyticDB for MySQL database from the DMS console and use the task development and scheduling feature for the first time, you must first configure the database logon information. For more information, see Log on to a database.
    • For an AnalyticDB for MySQL database to which you have logged on before and the control mode of the cluster is Flexible Management or Stable Change, if Remember password is not selected when you configure the database logon information, you must enter the password of your database account in the dialog box that appears after you go to the DMS console. Then, you can perform subsequent operations. For more information about the control modes, see Control modes.
  6. In the left-side navigation pane, click Task Orchestration. On the page that appears, click Create Task Flow.
  7. In the dialog box that appears, set Task Flow Name (required) and Description (optional). Then, click OK to go to the task orchestration page. In this example, the task flow name is Order Filtering.
    2

Step 2: Create a task node

  1. On the Order Filtering page, create the following task nodes:
    • Order Cleansing
      1. In the left-side navigation pane, select Single Instance SQL and drag it to the canvas on the right.
      2. Select the added task node and click the 1 icon to rename the task node as Order Cleansing.
      3. On the Node Information tab in the lower part of the page, select a database that you want to manage from the drop-down list.
      4. In the code editor, enter the following task statement and click Save:
        insert into finish_orders
         select order_id,total_price,order_date
         from orders 
         where order_status = 'F';
      4
    • Large Order Generation
      1. In the left-side navigation pane, select Single Instance SQL and drag it to the canvas on the right.
      2. Select the added task node and click the 1 icon to rename the task node as Large Order Generation.
      3. On the Node Information tab in the lower part of the page, select a database that you want to manage from the drop-down list.
      4. In the code editor, enter the following task statement and click Save:
        insert into large_finish_orders
         select order_id,total_price,order_date
         from finish_orders 
         where total_price > 10000;
      5
  2. On the task flow canvas, move the pointer over the Order Cleansing node, click and hold the small blue circle on the right side of the node, and then draw a line from the circle to the Large Order Generation node. This way, the two task nodes are connected to form a task flow.
    6

Step 3: Configure task flow scheduling

  1. In the Scheduling Settings section below the task flow canvas, turn on Enable Scheduling and set related parameters.
    7
    Note In this example, the scheduling task is set to periodically run at 01:00 every day from 2021-06-01 to 2021-06-30. You can also customize the scheduling task based on your business needs. For more information about scheduling parameters, see Configure scheduling properties for a task flow.
  2. In the upper-left corner, click Publish.
    8
    Note To unpublish a task flow, click Unpublish. In the message that appears, click OK.
  3. In the dialog box that appears, specify the remarks of the task to be published and click OK to publish the latest task flow online.

Subsequent step: Manage task O&M

After the task is published, click Go to O&M in the upper-right corner of the task flow canvas. On the Operations Management tab, you can view task details such as the creation time, creator, running history, and publishing information of the task.

11