Data Management:Task orchestration

Preparations

/** Create a table. **/
CREATE TABLE task_data (
    `id` bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary key',
      `gmt_create` DATETIME NOT NULL COMMENT 'Creation time',
      `gmt_modified` DATETIME NOT NULL COMMENT 'Modification time',
    `content` TEXT COMMENT 'Test data'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table for a task orchestration test';

/** Insert data. **/
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-01-01 01:00:00', '2020-01-01 01:00:00', 'value1');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-02-01 01:00:00', '2020-02-01 01:00:00', 'value2');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-03-01 01:00:00', '2020-03-01 01:00:00', 'value3');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-04-01 01:00:00', '2020-04-01 01:00:00', 'value4');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-05-01 01:00:00', '2020-05-01 01:00:00', 'value5');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-06-01 01:00:00', '2020-06-01 01:00:00', 'value6');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-07-01 01:00:00', '2020-07-01 01:00:00', 'value7');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-08-01 01:00:00', '2020-08-01 01:00:00', 'value8');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-09-01 01:00:00', '2020-09-01 01:00:00', 'value9');
INSERT INTO task_data( `gmt_create`, `gmt_modified`, `content`) VALUES ('2020-10-01 01:00:00', '2020-10-01 01:00:00', 'value10');

Procedure

1. Log on to the DMS console V5.0.
2. In the top navigation bar, click DTS. In the left-side navigation pane, choose Data Development > Task Orchestration(Old).
3. In the Free orchestration tasks section of the Task Orchestration tab, click New task flow.
4. In the New Task Flow dialog box, set the Task Flow Name parameter to poc_task_test and the Description parameter to Test. Then, click OK.
   The Task Orchestration page appears.
5. Click the blank area on the canvas. In the right-side pane, click the Variables tab and configure the following variables.

   After you configure the variables, you can use ${Variable name} in SQL statements to specify a point in time or time period. For information about the rules and usage of variables, see Task.

   • Create the variable yearmonth6_name. The value of this variable is in the yyyy-MM format. The value indicates the month that is six months previous to the current month.

     The value is accurate to the month. For example, the current date is January 10, 2021. The value of this variable indicates July 2020.

   • Create the variable yearmonth6_date. The value of this variable is in the yyyy-MM-01 format. The value indicates the first day of the month that is six months previous to the current month.

     The value is accurate to the day. For example, the current date is January 10, 2021. The value of this variable indicates July 1, 2020.

   
6. Create a task to migrate data.
   This task is used to migrate the historical data from the task_data table to another table.
   1. In the left-side task node list of the Task Orchestration page, drag the Single Instance SQL task node under Database O & M to the canvas.
   2. On the canvas, right-click the Single Instance SQL task node and select Rename. Rename it Migrate data and press the Enter key.
   3. Click the Migrate data task node. In the right-side pane, click the Content tab.
   4. Select the database that you want to manage from the Database drop-down list and enter the following SQL statements.

      Note: The SQL statements are used to create a table to archive the historical data. This way, the historical data that meets the specified requirements is transferred from the source table to the created table.

      CREATE TABLE IF NOT EXISTS `task_data_${yearmonth6_name}` LIKE task_data;
      
      INSERT INTO `task_data_${yearmonth6_name}` 
      SELECT * FROM task_data 
      WHERE gmt_create < '${yearmonth6_date}';

      
   5. Click SQLPreview to check the validity of the SQL statements.
      
   6. After you configure the preceding information, click Save.
7. Create a task to delete the historical data.
   This task is used to delete the historical data from the task_data table.
   1. In the left-side task node list of the Task Orchestration page, drag the Single Instance SQL task node under Database O & M to the canvas.
   2. On the canvas, right-click the Single Instance SQL task node and select Rename. Rename it Delete historical data and press the Enter key.
   3. Click the Delete historical data task node. In the right-side pane, click the Content tab.
   4. Select the database that you want to manage from the Database drop-down list and enter the following SQL statement.

      Note: The SQL statement is used to delete the historical data from the source table.

      DELETE FROM task_data WHERE  gmt_create < '${yearmonth6_date}';

      
   5. Click SQLPreview to check the validity of the SQL statement.
   6. After you configure the preceding information, click Save.
8. On the canvas, move the pointer over the Migrate data task node, click and hold the circle, and then draw a line from the circle to the Delete historical data task node.
   

   Note You can draw lines to connect multiple task nodes and configure the sequence for running the tasks. In this example, the Migrate data task node is the first task to be run and the Delete historical data task node is the second.
9. Click the blank area on the canvas. In the right-side pane, click the Scheduling Settings tab, turn on Enable Scheduling, and then set the parameters, as shown in the following figure. Then, click Save.
   Note In this example, the task flow is scheduled to run at 01:00 on the first day of each month. You can schedule the task flow to run as needed. For more information, see Configure scheduling properties for the task flow.

   
10. Click Try Run in the upper-left corner to test the task flow. The statements are immediately executed.