The task orchestration feature of Data Management (DMS) is used to orchestrate and schedule various tasks. You can create a task flow composed of one or more task nodes to implement complex task scheduling and improve data development efficiency.

Prerequisites

A database is created, and you have change permissions on the database. For more information about how to apply for permissions, see t1919605.html#multiTask2785.

Background information

During sales promotions, e-commerce companies often provide users with discounts. For example, if the accumulated consumption amount of a user reaches the specified value, the e-commerce companies issue a coupon with a corresponding quota to the user. This topic describes how to use the task orchestration feature to create a task flow composed of the SQL Assignment for Single Instance, Conditional Branch, and Single Instance SQL nodes to implement coupon issuance in the database.

Procedure

  1. Create a configuration table, a business table, and a coupon issuance table and insert data into the tables. For more information, see Environment preparation.
  2. Create a task flow.
    1. Log on to the DMS console.
      Note To switch to the previous version of the DMS console, click the Return to old version icon icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
    2. In the top navigation bar, click DTS. In the left-side navigation pane, choose Data Development > Task Orchestration.
      Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Factory > Task Orchestration (New).
    3. 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.
    4. In the New Task Flow dialog box, set the Task Flow Name and Description parameters as required and click OK.
  3. Orchestrate task nodes.
    1. In the Task Type list on the left side of the canvas, drag the SQL Assignment for Single Instance, Conditional Branch, and Single Instance SQL nodes to the blank area on the canvas.
    2. Connect the nodes to form a task flow.

      Move the pointer over the SQL Assignment for Single Instance node, click the hollow dot on the right side of the SQL Assignment for Single Instance node, and then pull a line to connect to the Conditional Branch node.

      Move the pointer over the Conditional Branch node, click the hollow dot on the right side of the Conditional Branch node, and then pull a line to connect to the Single Instance SQL node.

  4. Configure the task nodes.
    1. Click the Single-instance SQL Assignment node and configure it. For more information, see Configure an SQL assignment node.
      The following figure shows the configured output variables.输出变量
      Enter the following SQL statement:
      select * from activity_setting limit 1
    2. Click the Conditional Branch node and configure it. For more information, see Configure a conditional branch node.
      The following figure shows the configured conditional expression.Conditional expression
    3. Click the Single Instance SQL node and configure it.
      Enter the following SQL statement:
      INSERT INTO `voucher_send_list`(`user_id`,`consumption_sum`,`quota`)
      SELECT * FROM
      (SELECT `user_id`, sum(count) AS consumption_sum,${Quota} FROM `consumption_records`
       WHERE `time` > '${bizdate}'  GROUP BY `user_id`) t
      where `consumption_sum`>${ConsumptionLimit}
  5. Click Try Run in the upper-left corner of the canvas to test the task flow.
    You can query the voucher_send_list table in the SQLConsole. The coupon data has been inserted into the table. Coupon issuance result
  6. Optional:Enable scheduling for the task flow.
    1. Click the blank area on the canvas.
    2. Click the Task Flow Information tab.
    3. In the Scheduling Settings section, turn on Enable Scheduling. For more information, see Configure a task flow.
  7. Publish the task flow. After the task flow is published, it is automatically executed based on the specified scheduling cycle.
    1. Click Publish in the upper-left corner of the canvas.
    2. In the Publish dialog box, set the Remarks parameter and click OK.

Environment preparation

  1. Log on to the DMS console V5.0.
    Note To switch to the previous version of the DMS console, click the 5租户头像 icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. In the top navigation bar, click SQL Console. The SQL Console tab appears.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose SQLConsole > Single Database Query.
  3. In the Please select the database first dialog box, enter a keyword to search for a database, select the database from the search results, and then click Confirm.
  4. Create a configuration table, a business table, and a coupon issuance table.
    1. Create a configuration table named activity_setting. Paste the following SQL statement that is used to create the table to the SQL editor and click Execute.
      Use the following SQL statement to create the table:
      CREATE TABLE `activity_setting` (
          `has_promotion` tinyint(1) NOT NULL COMMENT 'Whether discounts are provided',
          `consumption_limit` int(11) NOT NULL COMMENT 'Consumption amount limit',
          `quota` int(11) NOT NULL COMMENT 'Coupon quota',
          PRIMARY KEY (`has_promotion`)
      ) ENGINE=InnoDB
      DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
      ROW_FORMAT=COMPACT
      AVG_ROW_LENGTH=16384;
    2. Create a business table named consumption_records. Paste the following SQL statement that is used to create the table to the SQL editor and click Execute.
      Use the following SQL statement to create the table:
      CREATE TABLE `consumption_records` (
          `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
          `time` datetime NOT NULL COMMENT 'Consumption time',
          `count` int(11) NOT NULL COMMENT 'Consumption amount',
          `teacher_id` varchar(20) NOT NULL COMMENT 'User ID',
          PRIMARY KEY (`id`)
      ) ENGINE=InnoDB
      DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
      COMMENT='Consumption record table'
      AUTO_INCREMENT=8001
      ROW_FORMAT=COMPACT
      AVG_ROW_LENGTH=54;
    3. Create a coupon issuance table named voucher_send_list. Paste the following SQL statement that is used to create the table to the SQL editor and click Execute.
      Use the following SQL statement to create the table:
      CREATE TABLE `voucher_send_list` (
          `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Record ID',
          `user_id` int(11) NULL COMMENT 'User ID',
          `consumption_sum` int(11) NULL COMMENT 'Consumption amount',
          `quota` int(11) NULL COMMENT 'Coupon quota',
          PRIMARY KEY (`Id`)
      ) ENGINE=InnoDB
      DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
      AUTO_INCREMENT=1
      ROW_FORMAT=COMPACT
      AVG_ROW_LENGTH=0;
  5. Insert data into the configuration table. Paste the following SQL statement to the SQL editor and click Execute.
    Use the following SQL statement to insert data into the table:
    INSERT INTO activity_setting( has_promotion, consumption_limit, quota)
    VALUES('1','100','30');
  6. Insert data into the business table. Use the Test Data Generation feature of DMS to generate data. For more information, see Generate test data.
    The following figure shows an example on how to generate data for the consumption_records table.
    Generate data