DWS SQL

Functions

The DWS SQL node is used to transfer an SQL statement to DWS.

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 11-26, Table 11-27, and Table 11-28 describe the parameters of the DWS SQL node.

Table 11-26 Parameters of DWS SQL nodes

Parameter Mandator

y Description

SQL Statement or

Script Yes You can select SQL statement or SQL

script.

● SQL Statement

In the SQL statement text box, 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 referring to Creating a Script and Developing an SQL Script.

NOTEIf you select the SQL statement mode, the DLF 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 a built-in function and EL expression. For details about built-in functions and EL expressions, see

Expression Overview.

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

Dirty Data Table No Enter the name of the dirty data table defined in the SQL script.

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

Name of the node. Must consist of 1 to 128 characters and contain only letters, digits, underscores (_), hyphens (-), slashes (/), less-than signs (<), and greater-than signs (>).

Table 11-27 Advanced parameters

Parameter Mandator

y Description

Node Status Polling

Interval (s) Yes Specifies how often the system check completeness of the node task. 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 not be retried and is set to the failed state.

Retry upon Failure Yes Indicates whether to re-execute a node task if its execution fails. Possible values:

● Yes: The node task will be re-executed, and the following parameters must be configured:

– Maximum Retries – Retry Interval (seconds)

● No: The node task will not be re-executed. This is the default setting.

NOTEIf Timeout Interval is configured for the node, the node will not be executed again after the execution times out. Instead, the node is set to the failure state.

Failure Policy Yes Operation that will be performed if the node task fails to be executed. Possible values:

● End the current job execution plan

● Go to the next job

● Suspend the current job execution plan

● Suspend execution plans of the current and subsequent nodes

Table 11-28 Lineage

Parameter Description

Input

Parameter Description

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

● DWS

– Connection Name: Click . In the displayed dialog box, select a DWS data connection.

– Database: Click . In the displayed dialog box, select a DWS database.

– Schema: Click . In the displayed dialog box, select a DWS schema.

– Table Name: Click . In the displayed dialog box, select a DWS table.

● OBS

– Path: Click . In the displayed dialog box, select an OBS path.

● CSS

– Cluster Name: Click . In the displayed dialog box, select a CSS cluster.

– Index: name of the CSS index.

● DLI

– Connection Name: Click . In the displayed dialog box, select a DLI data connection.

– Database: Click . In the displayed dialog box, select a DLI database.

– Table Name: Click . In the displayed dialog box, select a DLI table.

OK Click OK to save the parameter settings.

Cancel Click Cancel to cancel the parameter settings.

Edit 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

Parameter Description

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

● DWS

– Connection Name: Click . In the displayed dialog box, select a DWS data connection.

– Database: Click . In the displayed dialog box, select a DWS database.

– Schema: Click . In the displayed dialog box, select a DWS schema.

– Table Name: Click . In the displayed dialog box, select a DWS table.

● OBS

– Path: Click . In the displayed dialog box, select an OBS path.

● CSS

– Cluster Name: Click . In the displayed dialog box, select a CSS cluster.

– Index: Enter a CSS index name.

● DLI

– Connection Name: Click . In the displayed dialog box, select a DLI data connection.

– Database: Click . In the displayed dialog box, select a DLI database.

– Table Name: Click . In the displayed dialog box, select a DLI table.

OK Click OK to save the parameter settings.

Cancel Click Cancel to cancel the parameter settings.

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

Delete Click to delete the parameter settings.

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