Task orchestration for e-commerce promotions

Log in to DMS 5.0.

Move the pointer over the icon in the upper-left corner of the DMS console and choose All Features > SQL Console > SQL Console.

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.

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 into the SQL editor and click Execute.

   SQL statement:

   CREATE TABLE `activity_setting` (
       `has_promotion` tinyint(1) NOT NULL COMMENT 'Indicates whether a promotion is active',
       `consumption_limit` int(11) NOT NULL COMMENT 'Consumption threshold',
       `quota` int(11) NOT NULL COMMENT 'Coupon amount',
       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 into the SQL editor and click Execute.

   SQL statement:

   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',
       `user_id` bigint(20) NOT NULL COMMENT 'User ID',
       PRIMARY KEY (`id`)
   ) ENGINE=InnoDB
   DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci
   COMMENT='Consumption records 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 into the SQL editor and click Execute.

   SQL statement:

   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 'Total consumption',
       `quota` int(11) NULL COMMENT 'Coupon amount',
       PRIMARY KEY (`Id`)
   ) ENGINE=InnoDB
   DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
   AUTO_INCREMENT=1
   ROW_FORMAT=COMPACT
   AVG_ROW_LENGTH=0;

Insert data into the configuration table. Paste the following SQL statement into the SQL editor and click Execute.

INSERT INTO activity_setting( has_promotion, consumption_limit, quota)
VALUES('1','100','30');

Insert data into the business table. Use the test data generation feature to generate data. For more information, see Test data generation.

Create and populate the configuration, business, and coupon issuance tables. For more information, see Prepare the environment.

Create a task flow.

1. Log on to Data Management (DMS) 5.0.

2. Move the pointer over the icon in the upper-left corner and choose All Features > Data+AI > Data Development > Task Orchestration.

   Note

   If you use the DMS console in normal mode, choose Data+AI > Data Development > Task Orchestration in the top navigation bar.

3. Click Create Task Flow.

4. In the New Task Flow dialog box, enter a Task Flow Name and a Description, and then click OK.

Orchestrate the task nodes.

1. From the Task Type list on the left, drag an SQL Assignment for Single Instance node, a Conditional Branch node, and a Single Instance SQL node to the canvas.

2. Connect the nodes to form a task flow.

   Hover over the Single Instance SQL Assignment node, and click the hollow circle that appears on the right side of the Single Instance SQL Assignment node and drag a connection line to the Conditional Branch node.

   Hover the pointer over the Conditional Branch node, click the hollow circle that appears on the right side of the Conditional Branch node and drag a connection line to the Single Instance SQL node.

Configure the task nodes.

1. Select the SQL Assignment for Single Instance node to configure it. For more information, see Configure an SQL Assignment for Single Instance node.

   1. Select the target database.

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

      select * from activity_setting limit 1

   3. In the Variable Setting section on the right, add three output variables: Quota (Coupon amount), HasPromotion (Indicates whether a promotion is active), and ConsumptionLimit (Consumption threshold).

      In the SQL editor on the left, enter select * from activity_setting limit 1. In the output variable mapping section on the right, map the variables to the query results as follows: Quota to Row 0 and Column 2, HasPromotion to Row 0 and Column 0, and ConsumptionLimit to Row 0 and Column 1.

2. Select and configure the Conditional Branch node. For more information, see Configure a Conditional Branch node.

   Configure the conditional expression as follows: set Condition Field to HasPromotion, Operator to ==, and Value to 1.

3. Select the Single Instance SQL node to 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`) `consumption_records`
   where `consumption_sum`>${ConsumptionLimit}

In the upper-left corner of the canvas, click Try Run to test the task flow.

Optional: Configure periodic scheduling.

1. On the task flow editing page, click the Task Flow Information tab at the bottom of the page.

2. In the Scheduling Settings section, turn on the Enable Scheduling switch and configure the schedule. For information about the parameters, see Scheduling cycle configuration.

Publish the task flow. It will then run automatically according to the configured schedule.

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

2. In the Publish dialog box, enter a comment in the Remarks field and click Publish.