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 the task flow that you want to manage to go to the details page of the task flow.
    Note For more information about how to create a task flow, see 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.
  6. On the configuration tab of the SQL Assignment for Single Instance node, enter a keyword to search for a database in the search box and select the required database from the drop-down list.
    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. You can click the Variable Setting tab in the right-side navigation pane to configure variables. You can click the Info icon icon in the upper-right corner of the Variable Setting panel to view the tips about variable configurations.
    • Click the Node Variable tab and configure node variables. For more information, see Configure time variables.
    • Click the Task Flow Variable tab and configure task flow variables. For more information, see Configure time variables.
    • Click the Input Variables tab to view input variables.
    • Click the Output Variables tab and click Increase Variable to configure the output variables in the following table.
      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.
  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. Click Try Run.
    • If status SUCCEEDED appears in the last line of the logs, the test run is successful.
    • If status FAILED appears in the last line of the logs, the test run fails.
      Note If the test run fails, view the node on which the failure occurs and the reason for the failure in the logs. Then, modify the configuration of the node and try again.