Developing a DWS SQL Job
This section describes how to use the DWS SQL operator to develop a job on DataArts Factory.
Scenario
This tutorial describes how to develop a DWS job to collect the sales volume of a store on the previous day.
Preparing the Environment
Creating a DWS Data Connection
Before developing a DWS SQL job, you must create a data connection to DWS on the Manage Data Connections page of Management Center. The data connection name is dws_link.
The key parameters are as follows:
- Cluster Name: Select the DWS cluster you have created when preparing the environment.
- Agent: Select the CDM cluster you have created when preparing the environment.
Creating a Database
Create a gaussdb database by following the instructions in Creating a Database.
Creating Data Tables
Create tables trade_log and trade_report in the gaussdb database. The following is an example script for creating the tables:
create schema store_sales; set current_schema= store_sales; drop table if exists trade_log; CREATE TABLE trade_log ( sn VARCHAR(16), trade_time DATE, trade_count INTEGER(8) ); set current_schema= store_sales; drop table if exists trade_report; CREATE TABLE trade_report ( rq DATE, trade_total INTEGER(8) );
Developing a DWS SQL Script
Choose Development > Develop Script and create a DWS SQL script named dws_sql. Enter an SQL statement in the editor to collect the sales amount of the previous day.
Key notes:
- The script development area in Figure 1 is a temporary debugging area. After you close the script tab, the development area will be cleared. You can click Submit to save and submit a script version.
- Connection: Select the data connection created in Creating a DWS Data Connection.
Developing a DWS SQL Job
After developing the DWS SQL script, create a job for periodically executing the DWS SQL script.
- Create an empty job named job_dws_sql.
Figure 2 Creating the job_dws_sql job
- Go to the job development page, drag the DWS SQL node to the canvas, and click the node to configure its properties.
Figure 3 Configuring properties for the DWS SQL node
Key properties:
- SQL script: Associate with the dws_sql script developed in Developing a DWS SQL Script.
- Data Connection: Select the data connection configured in the dws_sql script. The data connection can be changed.
- Database: Select the database configured in the dws_sql script. The database can be changed.
- Script Parameter: Obtain the value of yesterday using the following EL expression:
#{Job.getYesterday("yyyy-MM-dd")}
- Node Name: The name of the dws_sql script is displayed by default. The name can be changed.
- After configuring the job, click to test it.
- If the test is successful, click the blank area on the canvas and then the Scheduling Setup tab on the right. On the displayed page, configure the scheduling policy.
Figure 4 Configuring the scheduling policy
Parameter descriptions:
From Aug 6 to Aug 31 in 2021, the job was executed once at 02:00 every day.
- Click Submit and then Execute. The job will be executed automatically every day.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.