Managing SQL Templates

To facilitate SQL operation execution, DLI allows you to customize query templates or save the SQL statements in use as templates. After templates are saved, you do not need to compile SQL statements. Instead, you can directly perform the SQL operations using the templates.

SQL templates include sample templates and custom templates. The default sample template contains 22 standard TPC-H query statements, which can meet most TPC-H test requirements. For details, see TPC-H Sample Data in the SQL Template.

SQL template management provides the following functions:

Sample Templates
Custom Templates
Creating a Template
Executing the Template
Searching for a Template
Modifying a Template
Deleting a Template

Table Settings

In the upper right corner of the SQL Template page, click Set Property to determine whether to display templates by group.

Figure 1 Template settings

If you select Display by Group, the following display modes are available:

Expand the first group
Expand all
Collapse All

Sample Templates

The current sample template contains 22 standard TPC-H query statements. You can view the template name, description, and statements. For details about TPC-H examples, see TPC-H Sample Data in the SQL Template.

**Table 1** Template management parameters
Parameter	Description
Name	Indicates the template name. A template name can contain only digits, letters, and underscores (_), but cannot start with an underscore (_) or contain only digits. It cannot be left empty. The template name can contain a maximum of 50 characters.
Description	Description of the template you create.
Statement	SQL statement created as the template.
Operation	Execute: After you click this button, the system switches to the SQL Editor page, where you can modify or directly perform the statement as required. For details, see Executing the Template.

The existing sample templates apply to the following scenarios:

Price summary report query
Minimum cost supplier analysis
Shipping priority analysis
Analysis of order priority check
Analysis of the number of local suppliers
Analysis of forecasted income changes
Freight volume analysis
National market share analysis
Profit estimation analysis by product type
Analysis of returned parts
Analysis of key inventory indicators
Freight mode and command priority analysis
Consumer allocation analysis
Promotion effect analysis
Analysis of the supplier with the largest contribution
Analysis of the relationship between parts and suppliers
Revenue analysis of small-lot orders
Customer analysis for large orders
Discounted revenue analysis
Analysis of potential component improvements
Analysis of suppliers who fail to deliver goods on time
Global sales opportunity analysis

Custom Templates

The custom template list displays all templates you have created. You can view the template name, description, statements, and more.

**Table 2** Template management parameters
Parameter	Description
Name	Indicates the template name. A template name can contain only digits, letters, and underscores (_), but cannot start with an underscore (_) or contain only digits. It cannot be left empty. The template name can contain a maximum of 50 characters.
Description	Description of the template you create.
Statement	SQL statement created as the template.
Operation	Execute: After you click this button, the system switches to the SQL Editor page, where you can modify or directly perform the statement as required. For details, see Executing the Template. Modify: Click Modify. In the displayed Modify Template dialog box, modify the template information as required. For details, see Modifying a Template.

Creating a Template

You can create a template on either the Job Templates or the SQL Editor page.

To create a template on the Job Templates page:

On the left of the management console, choose Job Templates > SQL Templates.

On the SQL Templates page, click Create Template to create a template.

Enter the template name, SQL statement, and description information. For details, see Table 3.

Figure 2 Creating a template

**Table 3** Parameter description
Parameter	Description
Name	Indicates the template name. A template name can contain only digits, letters, and underscores (_), but cannot start with an underscore (_) or contain only digits. It cannot be left empty. The template name can contain a maximum of 50 characters.
Statement	SQL statement to be saved as a template.
Description	Description of the template you create.
Group	Use existing Use new Do not use
Group Name	If you select Use existing or Use new, you need to enter the group name.

Click OK.

To create a template on the SQL Editor page:
1. On the left of the management console, click SQL Editor.
2. In the SQL job editing area of the displayed SQL Editor page, click More in the upper right corner and choose Save as Template.
  Enter the template name, SQL statement, and description information. For details, see Table 3.
3. Click OK.

Executing the Template

Perform the template operation as follows:

On the left of the management console, choose Job Templates > SQL Templates.
On the SQL Templates page, select a template and click Execute in the Operation column. The SQL Editor page is displayed, and the corresponding SQL statement is automatically entered in the SQL job editing window.
In the upper right corner of the SQL job editing window, Click Execute to run the SQL statement. After the execution is complete, you can view the execution result below the current SQL job editing window.

Searching for a Template

On the SQL Templates page, you can enter the template name keyword in the search box on the upper right corner to search for the desired template.

Modifying a Template

Only custom templates can be modified. To modify a template, perform the following steps:

On the SQL Templates page, locate the target template and click Modify in the Operation column.
In the displayed Modify Template dialog box, modify the template name, statement, and description as required.
Click OK.