Configure a Loop SQL node-Data Management-阿里云帮助中心

A Loop SQL node generates SQL statements in a loop and submits them in batches to any relational database managed by Data Management (DMS). You can use variables to control the number of loops, simplify the loop logic, and balance the load on the source database. This topic describes how to configure a Loop SQL node.

Background information

In task orchestration, the Single-Instance SQL node executes an SQL statement only once in a database. This is not suitable for scenarios that require repeating the same logic in a loop, such as deleting redundant database tables or updating many tables for Software as a Service (SaaS) users. The Loop SQL node is designed for these scenarios.

For example, you can delete redundant data from a database table.

On the configuration page of the Loop SQL node, you can write a simple SQL statement. You can use the node's loop index, `${loop.index}`, and the number of loops to control the execution. You do not need to write complex loop statements, such as `while` or `loop`, to control the number of iterations. This lets you repeatedly delete data from a database table.

DELETE FROM test WHERE id=${loop.index};

Procedure

Log in to DMS 5.0.
In the top navigation bar, choose Data+AI > Data+AI > Task Orchestration.

Note
If you use the DMS console in simple mode, move the pointer over the icon in the upper-left corner and choose All Features > Data+AI > Data Development > Task Orchestration.
In the Task Flow area, click the name of the target task flow to open its details page.
From the Task Type list, select the Loop SQL node and drag it to the canvas.
Double-click the Loop SQL node.
On the node configuration page, click the Variable Settings tab on the right to configure variables.
Note
You can click the icon in the upper-right corner of the Variable Settings section to view tips on configuring variables.
- Click the Node Variable tab to configure node variables. For more information, see Configure time variables.
- Click the Task Flow Constants tab to configure task flow constants. Task flow constants are static fields that can be used in all nodes. Use the ${name} format to reference them in SQL statements.
- Click the Task Flow Variable tab to configure task flow variables. For more information, see Configure time variables.
- Click the Input Variables tab to view input variables.

On the Loop SQL node configuration page, configure the following parameters.

Category	Configuration Item	Description
Database	None	Search for and select the target database. Note You must have query and export permissions for the database. For more information about how to request permissions, see Manage access control permissions. Click the Metadata tab on the right to view the table structures in the database.
Loop Configuration	Loop Type	List: Configure the loop list, element name, and separator. For a configuration example, see Configuration examples. Note The loop list supports upstream variables, task flow constants, and assignments. You can also use them in combination. Example: `cat,dog,duck,${listStr}`. The separator is used to delimit elements in the loop list. It supports regular expressions and defaults to a comma (`,`). The element name for the first loop list defaults to `loop.item`. If you do not specify an element name for the second loop list, it defaults to `loop.item1`, and so on. In an SQL statement, use the `${loop.item1}` format to get the list element for the current loop. The variable name for the loop index is `loop.index`. In SQL, use the `${loop.index}` format to get the current loop index. Iteration: Configure the number of SQL loop iterations. For a configuration example, see Configuration examples.
	Loop List	Configure the loop List, Element Name (customizable), and other information. Click Add above the loop list area to add multiple loop lists. If multiple loop lists exist, they must have the same number of elements after being split by the separator. Otherwise, the trial run fails. Note This configuration item appears if you set Loop Type to List.
	Loop Count	The number of SQL loop iterations. The value must be an integer from 1 to 100. Note This configuration item appears if you set Loop Type to Iteration.
Execution Configuration	Batch Size	Divides the total number of loop iterations into as many equal batches as possible. The value must be an integer from 1 to 200. The system submits the SQL statements generated in each batch to DMS for execution at one time. Example: If Batch Size is 2 and Loop Count is 7, the SQL statements are executed in batches of two loops. They are submitted to DMS for execution four times. The last batch contains only one loop.
Execution Configuration	Batch Execution Interval	The time interval between the execution of each batch of SQL tasks, in seconds. The value must be an integer from 0 to 180. Example: If Batch Execution Interval is 3 seconds, Batch Size is 2, and Loop Count is 7, there are four batches and three intervals. Each interval is 3 seconds. Note The batch execution interval takes effect after the previous batch task is successfully executed. SQL tasks are executed in batch order. If a batch task fails, subsequent batch tasks are not executed.

In the SQL editor, write an SQL statement using the syntax of the database selected for the current node. The system automatically saves the SQL statement.
- SQL Preview: Click SQL Preview at any time while writing the code to preview it.
- Validity Check: After you write the SQL statement, click Validity Check.
Click Try Run to test the node.
- If the last line of the execution log shows status SUCCEEDED, the test run passed.
- If it shows status FAILED, check the log to identify which node failed and why, fix the configuration, then try again.

Configuration examples

Note

The numbers used in the following steps are for demonstration purposes only.

Iteration

Set Loop Type to Iteration.
Set Loop Count to 7.
Set Batch Size to 2.
Set Batch Execution Interval to 3.
In the SQL editor, enter the SQL statement.
This SQL statement deletes data where the `id` is 0, 1, 2, 3, 4, 5, or 6 from the `test` table. With a batch size of 2, DMS loops the SQL statement seven times and submits it for execution in four batches. The interval between each batch execution is 3 seconds.
```
DELETE FROM test WHERE id=${loop.index};
```
Click Trial Run.

List

Set Loop Type to List.
Add a loop list and configure its List, Element Name, and other information.
1. Click the Variable Settings tab on the right to configure Task Flow Constants and Upstream Variables.
  Task flow constant configuration
  - Key: looplist0
  - Value: task1
  In this example, the upstream variable name is looplist1 and its value is "xiaobai". For more information, see Configure time variables.
2. In the loop list section, enter ${looplist0} as the list and key as the element name. Add a second list and enter ${looplist1} as the list and key1 as the element name.
  Note
  If you do not set an element name, the system uses the default element name.
  If the Loop SQL node has an ancestor node with output variables configured, you can also add the existing upstream variables to the loop list.
  You must ensure that all lists have the same number of elements after they are split by the separator. Otherwise, the trial run will fail.
Set Batch Size to 2.
Set Batch Execution Interval to 3.
In the SQL editor, enter the SQL statement.
This SQL statement concatenates the values of the task flow constant and the upstream variable and uses the result as the value for `name`. The statement also uses the ${loop.index} index as the value for `id` and inserts both values into the `test` table.
```
INSERT INTO test(id,name) VALUE(${loop.index}, concat('${key}','${key1}'));
```
Click Trial Run.