Use the task orchestration feature to schedule events - Data Management

This topic describes how to use the task orchestration feature of Data Management (DMS) to efficiently schedule events.

Background information

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 graphical user interface (GUI).
Dependent on the database kernel: The kernel must support the event scheduler. The event scheduler must be enabled.
Applicable 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.
Difficult to track: You cannot view the execution status, history, and duration of a scheduled event.
Difficult operations and maintenance (O&M): You cannot pause or rerun an event. You cannot restore an event after an event failure.
Incapable of sending notifications: Conventional event schedulers cannot send text messages or emails to inform you of the event execution status, for example, whether the execution is successful or fails.

To resolve these issues, we recommend that you use the task orchestration feature of DMS. This feature provides you with an external orchestration and scheduling system that is independent of the database kernel for scheduling events. The task orchestration feature provides you with the following benefits:

Supports more than 10 types of database engines and a variety of database tools. For example, the database engines contain 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 multiple notification channels, such as emails, text messages, and DingTalk notifications.
Supports various O&M operations, such as Pause, Exits, and Rerun.

Procedure

In this example, the task orchestration feature of DMS is used to create the event_click_archive event for the following purpose: Transfer the data that is stored in the user_click table two days before to the click_history table every day at 00:00 starting from December 1, 2020. Delete the transferred data from the user_click table so that the user_click table stores only the data of the previous day.

The following SQL statements define the event:

DELIMITER |

CREATE EVENT IF NOT EXISTS event_click_archive
ON SCHEDULE EVERY 1 DAY STARTS '2020-12-01 00:00.00'
ON COMPLETION PRESERVE
COMMENT 'Periodically archive data in the user_click table.'
DO
  BEGIN
    INSERT INTO click_history SELECT * FROM user_click
      WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
    DELETE FROM user_click WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
  END |

DELIMITER ;

Log on to the DMS console.
In the top navigation bar, choose Data Factory > Task Orchestration. The Home tab of the Task Orchestration page appears.
In the Free orchestration tasks section, click New task flow.
In the New Task Flow dialog box, set the Task Flow Name parameter to df_archive_click and the Description parameter to test. Then, click OK. The Task Orchestration tab appears.
In the left-side pane of the Task Orchestration tab, drag the Single Instance SQL node under Database O & M to the canvas.
Click the Single Instance SQL node. In the right-side panel, click the Content tab and perform the following operations:
1. Select the database that you want from the Database drop-down list.
2. Enter the following SQL statements:
```
INSERT INTO click_history SELECT * FROM user_click
      WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
      
DELETE FROM user_click WHERE create_time < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY);
```
  Note The task orchestration feature does not support local variables. If local variables are declared in the definition of an event, you must replace the local variables with user-defined variables when you use the task orchestration feature to create the event. You must also define a SET statement to assign values to the user-defined variables. In the following example, a local variable is replaced with a user-defined variable:
  The following SQL statements declare the local variable:
  DECLARE n_total BIGINT DEFAULT 0; SELECT COUNT(*) INTO n_total FROM test_table;
  The following SQL statement assigns a value to the user-defined variable:
  SET @n_total := (SELECT COUNT(*) FROM test_table);
3. After you complete the configuration, Click Save.
Click the blank area on the canvas. In the right-side panel, click the Scheduling tab, turn on Turn on/off, set the parameters, and then click Save.

After you complete the configuration, the node will be scheduled to run at 00:00 every day starting from December 1, 2020.

The df_archive_click task flow can achieve the same goal as the event_click_archive event. You can replace the event_click_archive event with the df_archive_click task flow. In addition, the task orchestration feature of DMS allows you to perform specific operations that are not supported by MySQL Event Scheduler. For example, you can use the task orchestration feature of DMS to send notifications and track the status of an event.

Note You can schedule the task flow to run as required. For example, you can schedule the task flow to run by day, week, or month. For more information, see Configure scheduling properties for the task flow.
In the right-side panel, click the Properties tab. Set the Partner parameter and turn on Enable message notification.

Note The task orchestration feature enables notifications to be immediately sent when the task flow is run or fails to be run. For more information, see Configure basic properties for the task flow and Notification rule management.
Click Try Run in the upper-left corner to run the task flow.

What to do next

To view the running result, click the Operation Centre icon.

Note On the Operation Centre page, you can view the status of each run of the task flow. For example, the task flow may be in the Success, Fail, or Running state. You can also view the time points at which the task flow starts and ends running and perform operations on the task flow. For example, you can pause or rerun the task flow.