Complex data analysis with a do-while node

Prerequisites

• You have activated MaxCompute and created a MaxCompute project. For more information, see Activate MaxCompute and Create a MaxCompute project.

• You have activated DataWorks Standard Edition or a later edition. For more information, see DataWorks purchase guide and Features of DataWorks editions.

• Your DataWorks workspace is in standard mode. For more information, see Upgrade the mode of a workspace.

  Note

  When you create a DataWorks workspace, the Join the public beta for Data Development (Data Studio) option is disabled by default. This example is not applicable to public beta workspaces.

• You have associated a MaxCompute engine with your workspace in DataWorks. For more information, see Associate a MaxCompute engine with a workspace.

Prepare data

1. Log on to the MaxCompute console. In the left-side navigation pane, choose DataWorks > Data Development to go to Data Development.

2. In the left-side navigation pane, click Data Development to go to DataStudio.

3. In the target workflow, under MaxCompute > Data Development, right-click and select New Node > ODPS SQL. Name the node, such as init_test_data.

4. In the init_test_data node, output the following SQL script.

   -- Create the orders table.
   CREATE TABLE orders
   (
       id            BIGINT
       ,user_id      BIGINT -- User ID
       ,order_amount BIGINT -- Order amount
   )
   PARTITIONED BY 
   (
       ds            STRING -- Order date in yyyyMMdd format
   )
   ;
   
   -- Insert order data.
   INSERT INTO orders PARTITION (ds = '20220901') VALUES (1,1001,500) ,(2,1002,1500);
   INSERT INTO orders PARTITION (ds = '20220905') VALUES (3,1005,260) ,(4,1002,780);
   INSERT INTO orders PARTITION (ds = '20221010') VALUES (5,1003,890) ,(6,1004,240);
   INSERT INTO orders PARTITION (ds = '20221021') VALUES (7,1003,890) ,(8,1004,240);
   INSERT INTO orders PARTITION (ds = '20221025') VALUES (9,1002,260) ,(10,1007,780);
   
   -- Create the order statistics table.
   CREATE TABLE orders_stat
   (
       stat_day            STRING -- The statistics date, which is the first day of each month.
       ,stat_type          STRING -- The statistics type.
       ,order_total        BIGINT -- The total number of orders.
       ,order_amount_total BIGINT -- The total order amount.
   )
   ;

5. Submit the init_test_data node to the production environment and backfill the data.

   1. On the node's toolbar, click the Save and Commit icons. When you commit the node, enter a change description and select whether to perform a code review and smoke testing as needed.

      Note

      • You must set the rerun property and configure Parent Nodes in the scheduling configuration before you can commit the node.

      • If code review is enabled, the submitted node code must be approved by a reviewer before it can be deployed. For more information, see Code review.

      • To ensure that the scheduled node runs as expected, perform smoke testing on the task before you deploy it. For more information, see Smoke testing.

   2. If you are using a workspace in standard mode, you must click Deploy in the upper-right corner to deploy the node after a successful commit. For more information, see Standard mode of a workspace and Deploy nodes.

   3. On the right side of the node's toolbar, click O&M to go to the Operation Center. In the left-side navigation pane, choose Auto Triggered Task O&M > Auto Triggered Tasks. In the cycle task list, find and click init_test_data. In the DAG view on the right, right-click the init_test_data node and select Test from the shortcut menu to run a test and initialize the data.

6. Freeze the node.

   Note

   This step performs a one-time data initialization. After this node runs successfully once, you can Freeze it to prevent it from running periodically.

   In the node task list, select this node, click the actions button at the bottom, and then click Freeze from the shortcut menu.

Procedure

Create a do-while node

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

2. Create a do-while node.

   1. On the Data Studio page, move the pointer over the icon and choose Create Node > General > do-while.

      Alternatively, you can open a workflow, right-click General, and select Create Node > do-while.

   2. In the Create Node dialog box, specify the node name and path.

   3. Click OK.

Add Shell and ODPS SQL nodes

1. Double-click the do-while node to open its internal view.

2. Delete the default sql node. Right-click the sql node inside the do-while node and click Delete Node. In the Delete dialog box, click OK.

3. From the left-side component list, drag MaxCompute > ODPS SQL and General > Shell into the canvas to create three ODPS SQL nodes and one Shell node.

   1. Name the three ODPS SQL nodes 30_day, 60_day, and 90_day.

   2. Name the Shell node echo.

4. Set the dependencies between the ODPS SQL and Shell nodes. Drag connection lines to set the start node as the parent of the echo node. Then, set the echo node as the parent of the 30_day, 60_day, and 90_day nodes. Finally, set all three SQL nodes as parents of the end node.

Configure node code

1. Configure the code for the echo node. This node is used to output a log of the current loop count. Double-click the echo node to open the edit page of the Shell node and enter the following code.

   #!/bin/bash
   echo "loop times: ${dag.loopTimes}"

2. Configure the code for the 30_day node. This node is used to calculate order data for the last month on the first day of each month in 2022. Double-click the 30_day node and enter the following code on the edit page of the ODPS SQL node.

   INSERT INTO orders_stat
   SELECT  CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01') AS stat_day
           ,'30d' AS stat_type
           ,COUNT(id) AS order_total
           ,nvl(SUM(order_amount),0) AS order_amount_total
   FROM    orders
   WHERE   
   -- The first day of the previous month
   ds >= REPLACE(ADD_MONTHS(TO_DATE(CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01'),'yyyyMMdd'),-1),'-','') 
   -- The first day of the current month
   AND ds < CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01')
   ;

   The following DML statements and functions are used in the preceding code:

   • INSERT INTO and INSERT OVERWRITE

   • CONCAT

   • COUNT

   • SUM

   • ADD_MONTHS

   • REPLACE

   • TO_DATE

   • LPAD

   • NVL

3. Configure the 60_day node. This node is used to aggregate order data from the preceding two months on the first day of each month in 2022.

   INSERT INTO orders_stat
   SELECT  CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01') AS stat_day
           ,'60d' AS stat_type
           ,COUNT(id) AS order_total
           ,nvl(SUM(order_amount),0) AS order_amount_total
   FROM    orders
   WHERE   
   -- The first day of two months ago
   ds >= REPLACE(ADD_MONTHS(TO_DATE(CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01'),'yyyyMMdd'),-2),'-','') 
   -- The first day of the current month
   AND ds < CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01')
   ;

4. Configure the 90_day node. This node is used to calculate the order data from the last three months for the first day of each month in 2022.

   INSERT INTO orders_stat
   SELECT  CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01') AS stat_day
           ,'90d' AS stat_type
           ,COUNT(id) AS order_total
           ,nvl(SUM(order_amount),0) AS order_amount_total
   FROM    orders
   WHERE   
   -- The first day of three months ago
   ds >= REPLACE(ADD_MONTHS(TO_DATE(CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01'),'yyyyMMdd'),-3),'-','') 
   -- The first day of the current month
   AND ds < CONCAT('2022',LPAD(${dag.loopTimes},2,'0'),'01')
   ;

5. Set the end node. This node controls when to exit the loop.

   1. Double-click the end node to open its editor.

   2. From the Select Language drop-down list, select Python.

   3. Enter the following code to define the end condition for the do-while node.

      Important

      • A do-while node can loop a maximum of 128 times. Therefore, the maximum value of ${dag.loopTimes} is 128.

      • do-while nodes do not support concurrent execution. The next loop can start only after the previous one is complete.

      if ${dag.loopTimes} < 12:
          print True
      else:
          print False

      The condition ${dag.loopTimes} < 12 ensures the loop runs 12 times. Each loop calculates statistics for one month, covering all 12 months of 2022.

Commit node

Double-click the do-while node and perform the following operations in the node settings pane on the right:

1. Click the icon in the toolbar to save the node.

2. Click the icon in the toolbar to submit the node.

   When you submit the node, enter a Change Description in the Submission dialog box and specify whether to initiate a code review after the node is submitted.

   Important

   • You must configure the Rerun attribute and Parent Nodes before you can submit the node.

   • Code review helps ensure the quality of task code and prevents errors caused by deploying unreviewed code directly to the production environment. If code review is enabled, the submitted node code must be approved by reviewers before it can be deployed. For more information, see Code review.

   If you use a workspace in standard mode, you must click Deploy in the upper-right corner of the node editing page to deploy the task to the production environment after it is submitted. For more information, see Deploy nodes.

Test node

The commit and deploy process for a do-while node is the same as for a regular node. However, direct testing from DataStudio is not supported.

1. Go to the cycle task page to backfill data.

   1. In the upper-right corner of the page, click Operation Center to go to the Operation Center.

   2. In the left-side navigation pane, choose Auto Triggered Task O&M > Auto Triggered Tasks.

   3. Find the required node. In the DAG view on the right, right-click the do_while_test node and choose Data Backfill > Backfill Data for Current Node.

      Because the SQL script logic in this example does not depend on the Data Timestamp, you can run the node with the default Data Timestamp.

   4. Go to the backfill instance page, click the task name do_while_test, right-click the task name in the DAG view on the right, and then click View Runtime Log in the shortcut menu.

2. View the loop node's execution log.

   1. On the backfill instance page, right-click the task name in the DAG view and click View Internal Nodes in the shortcut menu.

      For composite nodes such as loop nodes, you must view their internal nodes to see detailed execution logs.

      The internal view of a do-while node has three parts:

      • The left side of the view shows the rerun history list of the do-while node. Each time the do-while instance runs as a whole, a corresponding record is added to the history list.

      • The middle section shows the loop record list, which displays the total number of loop iterations and the status of each iteration.

      • The right side of the view shows the details of each iteration. Click an iteration in the loop record list to display the running status of each instance in that iteration.

   2. On the internal nodes page, click an iteration number on the left, right-click the desired node, and then select View Runtime Log.

3. View the detailed execution log for a specific loop iteration.

   On the Internal Nodes page, click 3rd on the left to view the logs of the echo node from the third iteration.

   As shown in this example, the workflow of a do-while node is as follows:

   1. The execution starts from the Start node.

   2. Tasks are executed sequentially based on the defined dependency relationships.

   3. The exit condition is defined in the End node.

   4. After a set of tasks completes, the exit condition statement in the End node is executed.

   5. If the evaluation statement in the End node prints True in the logs, the loop continues from step 1.

   6. If the evaluation statement in the End node prints False in the logs, the entire loop exits and the do-while node completes.

4. View the result table data.

   Create a new ODPS SQL node and execute the following SQL statement:

   SELECT * FROM orders_stat;

   Output:

   | stat_day | stat_type | order_total | order_amount_total |
   |----------|-----------|-------------|--------------------|
   | 20220101 | 60d       | 0           | 0                  |
   | 20220101 | 30d       | 0           | 0                  |
   | 20220101 | 90d       | 0           | 0                  |
   | 20220201 | 60d       | 0           | 0                  |
   | 20220201 | 30d       | 0           | 0                  |
   | 20220201 | 90d       | 0           | 0                  |
   | 20220301 | 30d       | 0           | 0                  |
   | 20220301 | 60d       | 0           | 0                  |
   | 20220301 | 90d       | 0           | 0                  |
   | 20220401 | 30d       | 0           | 0                  |
   | 20220401 | 90d       | 0           | 0                  |
   | 20220401 | 60d       | 0           | 0                  |
   | 20220501 | 30d       | 0           | 0                  |
   | 20220501 | 90d       | 0           | 0                  |
   | 20220501 | 60d       | 0           | 0                  |
   | 20220601 | 60d       | 0           | 0                  |
   | 20220601 | 30d       | 0           | 0                  |
   | 20220601 | 90d       | 0           | 0                  |
   | 20220701 | 90d       | 0           | 0                  |
   | 20220701 | 30d       | 0           | 0                  |
   | 20220701 | 60d       | 0           | 0                  |
   | 20220801 | 60d       | 0           | 0                  |
   | 20220801 | 90d       | 0           | 0                  |
   | 20220801 | 30d       | 0           | 0                  |
   | 20220901 | 90d       | 0           | 0                  |
   | 20220901 | 60d       | 0           | 0                  |
   | 20220901 | 30d       | 0           | 0                  |
   | 20221001 | 90d       | 4           | 3040               |
   | 20221001 | 60d       | 4           | 3040               |
   | 20221001 | 30d       | 4           | 3040               |
   | 20221101 | 30d       | 16          | 8860               |
   | 20221101 | 90d       | 20          | 11900              |
   | 20221101 | 60d       | 20          | 11900              |
   | 20221201 | 90d       | 20          | 11900              |
   | 20221201 | 60d       | 16          | 8860               |
   | 20221201 | 30d       | 0           | 0                  |

Related documentation

• do-while node

• Built-in functions