Developing an SQL Script

Prerequisites

• A corresponding cloud service has been enabled and a database has been created in the cloud service. For example, before developing a DLI script, DLI has been enabled and a database has been created in DLI. This prerequisite is not applied to Flink SQL scripts, so you do not need to create a database before developing a Flink SQL script.
• A data connection that matches the data connection type of the script has been created in Data Development. For details, see Creating a Data Connection. Flink SQL scripts are not involved.
• An SQL script has been added. For details, see Creating a Script.

Procedure

1. Log in to the DLF console.
2. In the navigation tree of the Data Development console, choose Data Development > Develop Script.
3. In the script directory list, double-click a script that you want to develop. The script development page is displayed.
4. In the upper part of the editor, select script properties. Table 1 describes the script properties. When developing a Flink SQL script, skip this step.

   Table 1 SQL script properties

   Property	Description
   Data Connection	Selects a data connection.
   Resource Queue	Selects a resource queue for executing a DLI job. Set this parameter when a DLI or SQL script is created. You can create a resource queue using either of the following methods: Click to go to the Queue Management page. Go to the DLI console. To set properties for submitting SQL jobs in the form of key/value, click . A maximum of 10 properties can be set. The properties are described as follows: dli.sql.autoBroadcastJoinThreshold: specifies the data volume threshold to use BroadcastJoin. If the data volume exceeds the threshold, BroadcastJoin will be automatically enabled. dli.sql.shuffle.partitions: specifies the number of partitions during shuffling. dli.sql.cbo.enabled: specifies whether to enable the CBO optimization policy. dli.sql.cbo.joinReorder.enabled: specifies whether join reordering is allowed when CBO optimization is enabled. dli.sql.multiLevelDir.enabled: specifies whether to query the content in subdirectories if there are subdirectories in the specified directory of an OBS table or in the partition directory of an OBS partition table. By default, the content in subdirectories is not queried. dli.sql.dynamicPartitionOverwrite.enabled: specifies that only partitions used during data query are overwritten and other partitions are not deleted.
   Database	Name of the database.
   Data Table	Name of the data table that exists in the database. You can also search for an existing table by entering the database name and clicking .

5. Enter an SQL statement in the editor. You can enter multiple SQL statements. To facilitate script development, DLF provides system functions and script parameters (Flink SQL and RDS scripts are excluded).
   

   SQL statements are separated by semicolons (;). If semicolons are used in other places but not used to separate SQL statements, escape them with backslashes (\). For example:

   select 1;
   select * from a where b="dsfa\;";  --example 1\;example 2.

   • System Functions

     To view the functions supported by this type of data connection, click System Function on the right of the editor. You can double-click a function to the editor to use it.

   • Script Parameters

     You can directly write script parameters in SQL statements. When debugging scripts, you can enter parameter values in the script editor. If the script is referenced by a job, you can set parameter values on the job development page. The parameter values can use EL expressions (see Expression Overview).

     An example is as follows:

     select ${str1} from data;

     In the preceding command, str1 indicates the parameter name. It can contain only letters, digits, hyphens (-), underscores (_), greater-than signs (>), and less-than signs (<), and can contain a maximum of 16 characters. The parameter name must be unique.

6. (Optional) In the upper part of the editor, click Format to format the SQL statement. When developing a Flink SQL script, skip this step.
7. In the upper part of the editor, click Execute. If you need to run some SQL statements separately, select the SQL statements first. After the SQL statement is executed, you can view the execution history and execution result of the script in the lower part of the editor . When developing a Flink SQL script, skip this step.

   You can download or dump execution results as required. Only users with the DAYU Administrator or Tenant Administrator permissions can download or dump execution results. The following is an example:

   • Download result: Download the CSV result files to the local host.
   • Dump result: Dump the CSV result files to OBS. For details, see Table 2.

     Table 2 Dump result

     Parameter	Mandatory	Description
     Data Format	Yes	Format of the data to be exported. Only CSV result files can be exported.
     Resource Queue	No	DLI queue where the export operation is to be performed. This parameter is available only when the DLI SQL script is used.
     Compression Format	No	Format of compression. Set this parameter when a DLI or SQL script is created. none bzip2 deflate gzip
     Storage Path	Yes	OBS path where the result file is stored. After selecting an OBS path, customize a folder. Then, the system will create it automatically for storing the result file.
     Cover Type	No	If a folder that has the same name as your customized folder exists in the storage path, select a cover type. This parameter is available only when a DLI SQL script is created. Overwrite: The existing folder will be overwritten by the customized folder. Report: The system reports an error and suspends the export operation.

8. Above the editor, click to save the script.
   If the script is created but not saved, set the parameters listed in Table 3.

   Table 3 Script parameters

   Parameter	Mandatory	Description
   Script Name	Yes	Name of the script. It contains a maximum of 128 characters. Only letters, digits, hyphens (-), underscores (_), and periods (.) are allowed.
   Description	No	Descriptive information about the script.
   Select Directory	Yes	Directory to which the script belongs. The root directory is selected by default.