Help Center/ Data Lake Insight/ User Guide/ Submitting a SQL Job Using DLI/ Creating and Managing SQL Job Templates/ Creating a SQL Job Template
Updated on 2024-09-30 GMT+08:00
View PDF
Share

Creating a SQL Job Template

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 Templates Preset on DLI.

In the navigation pane on the left, choose Job Templates > SQL Templates. In the upper right corner of the displayed page, click Settings. In the displayed Settings dialog box, choose whether to display templates by group.

If you enable Display by Group, the display options are Expand the first group, Expand all, and Collapse all.

Creating a SQL Job 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:
    1. On the left of the management console, choose Job Templates > SQL Templates.
    2. 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 1.

      Figure 1 Creating a template
      Table 1 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.
    3. 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 1.

    3. Click OK.

Submitting a SQL Job Using a Template

Perform the template operation as follows:
  1. On the left of the management console, choose Job Templates > SQL Templates.
  2. 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.
  3. 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 SQL Job 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 SQL Job Template

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

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

Deleting a Template

On the SQL Templates page, select one or more templates to be deleted and click Delete to delete the selected templates.