Implement complex data analysis by using the do-while node

Scenario

Consider an e-commerce order table partitioned by day, where the partition key ds is formatted as YYYYMMDD. For example, all orders placed on September 1, 2022 are stored in partition 20220901.

| id | user_id | order_amount | ds       |
|----|---------|--------------|----------|
| 1  | 1001    | 500          | 20220901 |
| 2  | 1002    | 1500         | 20220901 |
| 7  | 1003    | 890          | 20221021 |
| 8  | 1004    | 240          | 20221021 |

Goal: For each of the 12 months in 2022, calculate the total order count and amount for the last 30 days, last 60 days, and last 90 days from the first of that month.

| stat_day | stat_type | order_total | order_amount_total |
|----------|-----------|-------------|--------------------|
| 20220901 | 30d       | 10          | 0                  |
| 20220901 | 60d       | 20          | 0                  |
| 20220901 | 90d       | 30          | 0                  |

Solution: Run 12 loops—one per month—using a do-while node. Inside each loop, three ODPS SQL nodes calculate the 30-day, 60-day, and 90-day statistics. The built-in variable ${dag.loopTimes} tracks the current loop count (1 for January, 2 for February, and so on through 12 for December), so you can dynamically compute the correct partitions in each SQL query.

do-while node constraints

Create the do-while node

1. Log on to the DataWorks console. In the top navigation bar, select a region. In the left-side navigation pane, choose Data Development and O&M > Data Development. Select your workspace and click Go to Data Development.

2. On the DataStudio page, move the pointer over the icon and choose Create Node > General > do-while. Alternatively, find the target workflow, click the workflow name, right-click General, and choose Create Node > do-while.

3. In the Create Node dialog box, configure the Name and Path parameters, then click Confirm.

Add inner nodes

1. Double-click the do-while node name to open its configuration tab.

2. Delete the default sql node: right-click it and choose Delete Node, then click OK.

3. Create the following nodes inside the do-while node:

   • Three ODPS SQL nodes (choose MaxCompute > ODPS SQL): name them 30_day, 60_day, and 90_day.

   • One Shell node (choose General > Shell): name it echo.

4. On the canvas, drag connections to configure the following dependency order: start → echo → 30_day, 60_day, 90_day → end The echo node runs first, followed by the three SQL nodes in parallel, and the end node runs last.

Write the node code

`echo` node — logs the current loop count:

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

`30_day` node — inserts 30-day order statistics for the first day of each month. The ${dag.loopTimes} variable is padded to two digits and used to build the partition boundary:

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 prior 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 SQL uses these built-in MaxCompute functions:

• String and date functions: CONCAT, LPAD, REPLACE, TO_DATE, ADD_MONTHS

• Aggregate functions: COUNT, SUM, NVL

`60_day` node — same structure as 30_day, with the ADD_MONTHS offset changed to -2:

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')
;

`90_day` node — same structure, with the ADD_MONTHS offset changed to -3:

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')
;

`end` node — controls when the loop exits. Double-click the end node, select Python from the Language drop-down list, and enter:

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

When ${dag.loopTimes} is less than 12, the loop continues. When it reaches 12 (December), the loop ends—giving you one iteration per month across all 12 months of 2022.

Commit and deploy

1. On the do-while node configuration tab, click the icon to save.

2. Click the icon to commit. In the Submit dialog box, enter a Change description and choose whether to enable code review.

   Important

   Configure the Rerun and Parent Nodes parameters on the Properties tab before committing. If code review is enabled, the node is deployed only after the code passes review. For more information, see Code review.

3. If your workspace is in standard mode, click Deploy in the upper-right corner to push the node to the production environment.

Run the do-while node

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

2. In the left-side navigation pane, choose Cycle Task Maintenance > Cycle Task.

3. In the task list, right-click the do_while_test node and choose Run > Backfill Data for Current Node.

   The SQL in this example does not depend on the Data Timestamp parameter, so the default value works fine.

4. To monitor progress, right-click do_while_test in the DAG and select View Runtime Log.

View loop-level logs

1. In the task DAG, right-click the node and select View Internal Nodes to open the do-while node's inner view. The inner view has three panes:

   • Left: rerun history, with one record per run instance.

   • Middle: loop record list showing all loops and their status.

   • Right: details for the selected loop, including each inner node's status.

2. In the middle pane, click a completed loop. In the right pane, right-click a node and select View Runtime Log to read its output.

3. To check the echo node's output for loop 3, click Loop 3 in the middle pane, then view the log for the echo node. The expected output is:

   loop times: 3

How the loop works:

1. Execution starts at the start node.

2. Inner nodes run in the order defined by their dependencies.

3. The end node evaluates the exit condition.

4. If the condition returns True, the next loop starts. If it returns False, the do-while node ends.

Verify the output data

Create an ODPS SQL node and run:

SELECT * FROM orders_stat;

Expected result (36 rows, three stat types for each of the 12 months):

| 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                  |

The output reflects the sample data inserted in the preparation step: orders are concentrated in September and October 2022, so statistics for earlier months are 0.