All Products
Search
Document Center

Data Management:Use the task orchestration feature for e-commerce tagging

Last Updated:May 14, 2024

Data Management (DMS) provides the task orchestration feature. You can use this feature to orchestrate different types of tasks, and then schedule and run the tasks. You can create a task flow composed of one or more task nodes to implement complex scheduling and improve data development efficiency.

Background information

During sales promotions, e-commerce companies often give discounts to their customers. For example, when the cumulative purchase amount of a user reaches a certain value, the e-commerce company issues a coupon with a certain discount to the user so that the user can purchase a service or product at a lower price. 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 a database.

Prerequisites

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

Prepare the environment

  1. Log on to the DMS console V5.0.
  2. Move the pointer over the 2023-01-28_15-57-17.png icon in the upper-left corner of the DMS console and choose All functions > SQL Console > SQL Console.

    Note

    If you use the DMS console in normal mode, choose SQL Console > SQL Console in the top navigation bar.

  3. In the Please select the database first dialog box, enter a keyword to search for a database, select the database instance 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. Copy and paste the following SQL statement 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 'Limit on purchase amount',
          `quota` int(11) NOT NULL COMMENT 'Coupon discount',
          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. Copy and paste the following SQL statement 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 'Purchase time',
          `count` int(11) NOT NULL COMMENT 'Purchase amount',
          `user_id` bigint(20) NOT NULL COMMENT 'User ID',
          PRIMARY KEY (`id`)
      ) ENGINE=InnoDB
      DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
      COMMENT='Purchase record table'
      AUTO_INCREMENT=8001
      ROW_FORMAT=COMPACT
      AVG_ROW_LENGTH=54;
    3. Create a coupon issuance table named voucher_send_list. Copy and paste the following SQL statement 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 'Purchase amount',
          `quota` int(11) NULL COMMENT 'Coupon discount',
          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. Copy and 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 test data. For more information, see Generate test data.

    The following figure shows an example on how to generate data for the consumption_records table.

    数据构建

Procedure

  1. Create a configuration table, a business table, and a coupon issuance table. Insert data into the tables. For more information, see the Prepare the environment section of this topic.

  2. Create a task flow.

    1. Log on to the DMS console V5.0.

    2. In the top navigation bar, choose DTS > Data Development > Task Orchestration.

    3. On the Task orchestration page, click Create Task Flow.

    4. In the New Task Flow dialog box, set the Task Flow Name and Description parameters 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 circle on the right side of the SQL Assignment for Single Instance node, and then draw a line to connect to the Conditional Branch node.

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

  4. Configure the task nodes.

    1. Double-click the SQL Assignment for Single Instance node and configure the node. For more information, see Configure an SQL assignment node.

      1. Enter a keyword in the search box to search for a database and select the database from the drop-down list.

      2. In the SQL editor, enter the following SQL statement:

        select * from activity_setting limit 1
      3. In the right-side navigation pane, click the Variable Setting tab. On the tab, click the Output Variables tab. Then, click Increase Variable to add the Quota, HasPromotion, and ConsumptionLimit output variables.

    2. Double-click the Conditional Branch node and configure the node. For more information, see Configure a conditional branch node.

      The following figure shows the configured conditional expression.

      image

    3. Double-click the Single Instance SQL node and configure the node.

      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`) `consumption_records`
      where `consumption_sum`>${ConsumptionLimit}
  5. In the upper-left corner of the canvas, click Try Run to test the task flow.

    You can view the voucher_send_list table on the SQLConsole tab. The coupon data is inserted into the table.1 优惠结果

  6. Optional. Configure the scheduling properties for the task flow.

    1. Click the Hide/Show icon in the lower part.

    2. Then, 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 run based on the specified scheduling cycle.

    1. In the upper-left corner of the canvas, click Publish.

    2. In the Publish dialog box, set the Remarks parameter and click Publish.