Updated on 2024-07-11 GMT+08:00

DWS SQL

Functions

The DWS SQL node is used to transfer SQL statements to DWS.

For details about how to use the DWS SQL operator, see Developing a DWS SQL Script and Job.

Context

This node enables you to execute DWS statements during batch or real-time job processing. You can use parameter variables to perform incremental import and process partitions for your data warehouses.

Parameters

Table 1, Table 2, and Table 3 describe the parameters of the DWS SQLnode node.

Table 1 Parameters of DWS SQL nodes

Parameter

Mandatory

Description

SQL or Script

Yes

You can select SQL statement or SQL script.

  • SQL Statement

    Click the text box under SQL statement and enter the SQL statement to be executed.

  • SQL Script

    Select a script to be executed. If the script is not created, create and develop the script by repeating steps Creating a Script and Developing an SQL Script.

    NOTE:

    If you select the SQL statement mode, the DataArts Factory module cannot parse the parameters contained in the SQL statement.

Data Connection

Yes

Data connection that is configured in the SQL script. The value can be changed.

Database

Yes

Database that is configured in the SQL script. The value can be changed.

Script Parameter

No

If the associated SQL script uses a parameter, the parameter name is displayed. Set the parameter value in the text box next to the parameter name. The parameter value can be an EL expression.

If the parameters of the associated SQL script are changed, click to refresh the parameters.

Dirty Data Table

No

Name of the dirty data table defined in the SQL script.

The dirty data attributes cannot be edited. They are automatically recommended by the SQL script content.

Syntax for the DWS dirty data table: with table_name or log into table_name

Matching Rule

N/A

Enter a Java regular expression used to match the DWS SQL result. For example, if the expression is (?<=\()(-*\d+?)(?=,) and the SQL result is (1,"error message"), then the matched result is "1".

Failure Matching Value

N/A

If the matched content equals the set value, the node fails to be executed.

Node Name

Yes

Name of the SQL script. The value can be changed. The rules are as follows:

Name of a node. The name must contain 1 to 128 characters, including only letters, numbers, underscores (_), hyphens (-), slashes (/), less-than signs (<), and greater-than signs (>).

By default, the node name is the same as that of the selected script. If you want the node name to be different from the script name, disable this function by referring to Disabling Auto Node Name Change.

Table 2 Advanced parameters

Parameter

Mandatory

Description

Node Status Polling Interval (s)

Yes

How often the system check completeness of the node. The value ranges from 1 to 60 seconds.

Max. Node Execution Duration

Yes

Execution timeout interval for the node. If retry is configured and the execution is not complete within the timeout interval, the node will be executed again.

Retry upon Failure

Yes

Whether to re-execute a node if it fails to be executed. Possible values:

  • Yes: The node will be re-executed, and the following parameters must be configured:
    • Retry upon Timeout
    • Maximum Retries
    • Retry Interval (seconds)
  • No: The node will not be re-executed. This is the default setting.
    NOTE:

    If retry is configured for a job node and the timeout duration is configured, the system allows you to retry a node when the node execution times out.

    If a node is not re-executed when it fails upon timeout, you can go to the Default Configuration page to modify this policy.

    Retry upon Timeout is displayed only when Retry upon Failure is set to Yes.

Policy for Handling Subsequent Nodes If the Current Node Fails

Yes

Operation that will be performed if the node fails to be executed. Possible values:

  • Suspend execution plans of the subsequent nodes: stops running subsequent nodes. The job instance status is Failed.
  • End the current job execution plan: stops running the current job. The job instance status is Failed.
  • Go to the next node: ignores the execution failure of the current node. The job instance status is Failure ignored.
  • Suspend the current job execution plan: If the current job instance is in abnormal state, the subsequent nodes of this node and the subsequent job instances that depend on the current job are in waiting state.

Enable Dry Run

No

If you select this option, the node will not be executed, and a success message will be returned.

Task Groups

No

Select a task group. If you select a task group, you can control the maximum number of concurrent nodes in the task group in a fine-grained manner in scenarios where a job contains multiple nodes, a data patching task is ongoing, or a job is rerunning.

Table 3 Lineage

Parameter

Description

Input

Add

Click Add. In the Type drop-down list, select the type to be created. The value can be DWS, OBS, CSS, HIVE, DLI, or CUSTOM.

OK

Click OK to save the parameter settings.

Cancel

Click Cancel to cancel the parameter settings.

Modify

Click to modify the parameter settings. After the modification, save the settings.

Delete

Click to delete the parameter settings.

View Details

Click to view details about the table created based on the input lineage.

Output

Add

Click Add. In the Type drop-down list, select the type to be created. The value can be DWS, OBS, CSS, HIVE, DLI, or CUSTOM.

OK

Click OK to save the parameter settings.

Cancel

Click Cancel to cancel the parameter settings.

Modify

Click to modify the parameter settings. After the modification, save the settings.

Delete

Click to delete the parameter settings.

View Details

Click to view details about the table created based on the output lineage.