Submitting a SQL Job Using the SQL Editor

Introduction

You can edit and run SQL statements in the SQL job editor to execute data query.

The editor supports SQL:2003 and is compatible with Spark SQL. For details about the syntax, see Data Lake Insight SQL Syntax Reference.

To access the SQL editor, choose SQL Editor in the left navigation pane of the DLI console, or click Create Job in the upper right corner of the Job Management > SQL Jobs page.

This topic describes the main functions of the SQL editor.

Notes

• If you access the SQL editor for the first time, the system prompts you to set a bucket for DLI jobs. The created bucket is used to store temporary data generated by DLI, such as job logs.

  You cannot view job logs if you choose not to create the bucket. The bucket name will be set by the system.

  On the OBS console, you can configure lifecycle rules for a bucket to periodically delete objects in it or change object storage classes.

• SQL statements can be executed in batches on the SQL editor page.

• Commonly used syntax in the job editing window is highlighted in different colors.
• Both single-line comment and multi-line comment are allowed. Use two consecutive hyphens (--) in each line to comment your statements.

Navigation pane

The navigation pane on the left consists of Databases, Queues, and Templates tabs.

Figure 1 SQL editor navigation pane

Table 1 Navigation pane buttons

No.	Name	Description
1	Databases	Displays all the existing databases and tables in these databases. Click a database name to view the tables in the database. Click a table name to view the metadata in the table. A maximum of 20 metadata records can be displayed. After you double-click a table name, a SQL query statement is automatically entered in the editing window.
2	Queues	Displays existing queues.
3	Templates	Click the drop-down button to view 22 built-in standard TPC-H query templates and custom templates.

SQL Editing Window

SQL job editing window is displayed in the upper right part of the page. For details about the parameters, see Table 2.

The SQL statement editing area is below the operation bar. For details about keyboard shortcuts, see Table 3.

Executed Queries (Last Day) and View Result

After the SQL job is executed, you can view the execution history and result in the lower part of the editing area.

• Executed Queries (Last Day)

  You can filter the execution history in the following ways:

  • In the search box in the upper right corner of the Executed Queries (Last Day) pane, select a queue name or enter an execution statement in the search box.
  • In the list, click the icon next to Created and choose Ascending or Descending.
  • Select a job status from the Status list.

  Table 4 Area description

  Area

  Description

  Executed Queries (Last Day)

  The latest daily information about the submitted jobs, including the following items: Queues: Queue name Username: User who executes the SQL statements Type: Type of the SQL job Status: Execution status of the SQL job Query Created Operation Edit: Edit the SQL statement. SparkUI: Switch to the SparkUI page to view the SQL statement execution process. NOTE: When you execute a job on a created queue, the cluster is restarted. It takes about 10 minutes. If you click SparkUI before the cluster is created, an empty projectID will be cached. The SparkUI page cannot be displayed. You are advised to use a dedicated queue so that the cluster will not be released. Alternatively, wait for a while after the job is submitted (the cluster is created), and then check SparkUI. SparkUI can currently only display the latest 100 jobs. This function is not supported for synchronization jobs and jobs running on the default queue. More: The following operations vary depending on the SQL job types and running status. Cancel: Cancel a SQL job that is running or being submitted. Re-execute: Execute the SQL statement again. View Result: View the execution result of a QUERY job. Export Result: Export the execution results of a QUERY job to a specified OBS path. View Log: View the OBS path for storing SQL statement execution logs. NOTE: The View Log button is not available for synchronization jobs and jobs running on the default queue.

SQL Query Procedure

1. Log in to the DLI management console. On the page displayed, choose Job Management > SQL Jobs. On the page displayed, click Create Job.
   

   On the SQL editor page, the system prompts you to create an OBS bucket to store temporary data generated by DLI jobs. In the Set Job Bucket dialog box, click Setting. On the page displayed, click the edit button in the upper right corner of the job bucket card. In the displayed Set Job Bucket dialog box, enter the job bucket path and click OK.

2. Select a queue from the queue list in the upper left corner of the SQL job editing window.
3. In the upper right corner of the SQL job editing window, select a database, for example, qw, from the Databases drop-down list.
4. Create a table, for example, qw. For details about how to create a database and table, see Creating a Database or a Table.
5. In the SQL job editing window, enter the following SQL statement:

   1	SELECT * FROM qw.qw LIMIT 10;

   Alternatively, you can double-click the table name qw. The query statement is automatically entered in the SQL job editing window.

6. On top of the editing window, click More > Verify Syntax to check whether the SQL statement is correct.
   1. If the verification fails, check the SQL statement syntax by referring to Data Lake Insight SQL Syntax Reference.
   2. If the syntax verification is successful, click Execute. Read and agree to the privacy agreement. Click OK to execute the SQL statement.
   3. After the execution is complete, you can view the execution result in the area under the SQL job editing window.
7. (Optional) A maximum of 1000 records can be displayed in the query result on the current console. To view more or all data, click to export the data to OBS.
8. (Optional) In the View Result tab, click to display the query result in a chart. Click to switch back to the table view.
   

   • If no column of the numeric type is displayed in the execution result, the result cannot be represented in charts.
   • You can view the data in a bar chart, line chart, or fan chart.
   • In the bar chart and line chart, the X axis can be any column, while the Y axis can only be columns of the numeric type. The fan chart displays the corresponding legends and indicators.

Quickly Importing SQL Statements

• Double-click a table name in the navigation pane on the left to import the query statement of the selected table into the SQL statement editing window, and then click Execute to query.
• You can click More and choose Save as Template to save the SQL statement as a template for future use.

  To use the SQL statement template, click Templates from the left pane of the SQL editor page. Double-click the required template in the template list, and modify it as required before executing the SQL statements.