This topic describes how to use the task orchestration feature of Data Management (DMS) to efficiently schedule events.
- High technical requirements: You must be familiar with the syntax of specific SQL statements that are used to define events, such as
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.
- 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.
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 . 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_clickand 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:
- Select the database that you want from the Database drop-down list.
- 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
SETstatement 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);
- 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.
df_archive_clicktask flow can achieve the same goal as the
event_click_archiveevent. You can replace the
event_click_archiveevent 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.
- 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.