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 scheduling capabilities for database events. However, these event schedulers have the following limits:
    • High technical requirements: You must be familiar with the syntax of the 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 engine: The engine 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, or 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 text message 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 event scheduling capabilities of database engines. The task orchestration feature provides the following benefits:
    • Supports more than 10 types of database engines and a variety of database tools. For example, the database engines include MySQL, Oracle, PostgreSQL, and SQL Server. 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 dragging nodes and performing simple configurations on the GUI.
    • Provides a variety of notification channels, such as emails, text messages, and DingTalk notifications.
    • Supports various O&M operations, such as Pause, Exits, and Rerun.

Precautions

The end-to-end data management feature is not supported for the AnalyticDB for MySQL clusters located in the Indonesia (Jakarta) region.

Task orchestration process

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 condition and 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.
  2. In the upper-left corner of the page, select the region where the cluster resides.
  3. In the left-side navigation pane, click Clusters.
  4. On the Data Warehouse Edition (V3.0) tab, find the cluster and click its Cluster ID.
  5. In the left-side navigation pane, choose End-to-end Data Management > Data Assets.
  6. In the top navigation bar, click DTS. In the left-side navigation pane, choose Data Development > Task Orchestration.
  7. Create a task flow. In this example, the task flow is named as Order Filtering.
    1. Click Create Task Flow.
      Note If you are using the previous version of the DMS console, click the Develop Space icon icon on the left-side navigation submenu of the Task Orchestration tab. On the page that appears, click New Task Flow.
    2. In the Create Task Flow dialog box, set the Task Flow Name and Description parameters as needed and click OK.

Step 2: Create a task node

  1. On the Order Filtering tab, create the following task nodes:
    • Order Cleansing
      1. In the left-side navigation pane, choose Data Processing Nodes > Single Instance SQL and drag Single Instance SQL to the right-side canvas.
      2. Select the added task node and click the 1 icon to rename the task node as Order Cleansing.
      3. Double-click the task node or click the 1 icon to edit the task node.
      4. Select a database from the Database drop-down list.
      5. 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';
        Note If you have selected Automatic Save, the SQL statement is automatically saved.
    • Large Order Generation
      1. In the left-side navigation pane, choose Data Processing Nodes > Single Instance SQL and drag Single Instance SQL to the right-side canvas.
      2. Select the added task node and click the 1 icon to rename the task node as Large Order Generation.
      3. Double-click the task node or click the 1 icon to edit the task node.
      4. Select a database from the Database drop-down list.
      5. 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;
        Note If you have selected Automatic Save, the SQL statement is automatically saved.
  2. On the task flow canvas, move the pointer over the Order Cleansing node, click and hold the small 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.
    Note In this example, the scheduling task is set to run on a periodic basis 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 the task flow.
  2. Publish the task flow.
    1. In the upper-left corner of the canvas, click Publish.
    2. In the Publish dialog box, set the Remarks parameter and click OK.
    Note If you no longer need to use the task flow, you can perform the following operations to unpublish the task flow:
    1. In the upper-left corner of the canvas, click Unpublish.
    2. In the message that appears, click OK.
  3. View the status of the task flow.
    1. In the upper-right corner of the canvas, click Go to O&M.
    2. On the right side of the page, check whether the task flow is published.
      • Published: The task flow is published.
      • Not published: The task flow is not published.