In Data Management (DMS), an SQL assignment node can assign the data that is read by using the SELECT statement to the output variables of the node. Then, the output variables can be used as the input variables of the downstream node of the SQL assignment node. This topic describes how to configure an SQL assignment node.

Background information

An SQL assignment node allows a task flow to obtain data from a table and assign the data to the input variables for a downstream node. You can use variables for the following purposes on task nodes:
  • On a Single Instance SQL node, variables can be used as table names or in filter conditions.
  • On a Script node, variables can be used as parameters in a script.
  • On a Lindorm File Check node, variables can be used as file names.

Procedure

  1. Log on to the DMS console V5.0.
    Note To switch to the previous version of the DMS console, click the 5租户头像 icon in the lower-right corner of the page. For more information, see Switch to the previous version of the DMS console.
  2. In the top navigation bar, click DTS. In the left-side navigation pane, choose Data Development > Task Orchestration.
    Note If you are using the previous version of the DMS console, move the pointer over the More icon in the top navigation bar and choose Data Factory > Task Orchestration (New).
  3. Click the name of a task flow. The details page of the task flow appears.
    Note For more information about how to create a task flow, see Step 3 in Overview.
  4. In the Task Type list on the left side of the canvas, drag the SQL Assignment for Single Instance node to the blank area on the canvas.
  5. Double-click the SQL Assignment for Single Instance node to go to the node information tab.
  6. In the field on the node information tab, enter a keyword to search for a database and select the required database from the drop-down list.Node Information
    Note
    • You must have the read permissions on the database that you select. For more information about how to apply for permissions, see Permission management.
    • You can click the Metadata tab in the right-side navigation pane to view the schemas of tables in the database.
  7. Click the Variable Setting tab in the right-side navigation pane and configure variables.
    Note For more information about variables, see Variables.
    • Configure node variables. Click the Node Variable tab and configure node variables. For more information, see Configure time variables.
      Note You can click the Info icon icon in the upper-right corner of the Variable Setting tab to view the tips about variable configurations.
    • Configure task flow variables. Click the Task Flow Variable tab and configure task flow variables. For more information, see Configure time variables.
      Note You can click the Info icon icon in the upper-right corner of the Variable Setting tab to view the tips about variable configurations.
    • View input variables. Click the Input Variables tab to view input variables.
    • Configure output variables. Click the Output Variables tab and click Increase Variable to configure output variables.
      Parameter Description
      Variable The name and description of the variable.
      Row
      • All Rows: All rows of the table are used in the value assigned to the variable.
      • Specific Row: Specific rows of the table are used in the value assigned to the variable.
      Column
      • All Columns: All columns of the table are used in the value assigned to the variable.
      • Specific Column: Specific columns of the table are used in the value assigned to the variable.
      Link The operator used to concatenate values. This parameter is displayed if you set the Row or Column parameter to All Rows or All Columns. The concatenated string is assigned to the output variable.
      Note
      • You cannot set the Row and Column parameters to All Rows and All Columns at the same time.
      • The value of an output variable is a string.
      • You can click the Info icon icon in the upper-right corner of the Variable Setting tab to view the tips about variable configurations.
  8. In the SQL editor, use the syntax of the database that is selected for the current node to write SQL statements.
    • To preview the SQL statements that you are compiling, click SQLPreview.
    • To verify the SQL statements that are compiled, click Validity Check.
    • To save the SQL statements that are compiled, click Save.
  9. Optional:Click the Advanced Settings tab in the right-side navigation pane and turn on Enable re-run of failed task. Set the Maximum rerun(1~5) and Rerun interval(1~1800s) parameters and click Save.