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.

  For details, see Configuring a Lifecycle Rule.

• 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.

Figure 2 SQL job editing window

Table 2 Components of the SQL job editing window

No.	Button & Drop-Down List	Description
1	Engine	SQL jobs support the Spark and Trino engines. Spark is suitable for offline analysis. Trino is suitable for interactive analysis.
2	Queues	Select a queue from the drop-down list box. If no queue is available, the default queue is displayed. Refer to Creating a Queue and create a queue. SQL jobs can only be executed on SQL queues.
3	Database	Select a database from the drop-down list box. If no database is available, the default database is displayed. For details about how to create a database, see Creating a Database or a Table. NOTE: If you specify the database in the SQL statements, the database you choose from the drop-down list will not be used.
4	Execute	Click this button to run the SQL statements in the job editing window.
5	Format	Click this button to format the SQL statements.
6	Syntax Reference	Click this button to view the Data Lake Insight SQL Syntax Reference.
7	Settings	Add parameters and tags. Parameter Settings: Set parameters in key/value format for SQL jobs. For details, see the Data Lake Insight SQL Syntax Reference. Tags: Set tags in key/value format for SQL jobs.
8	More	The drop-down list includes the following options: Click Verify Syntax to check whether the SQL statements are correct. Click Set as Template to set SQL statements as a template. For details, see Managing SQL Templates. Click Change Theme to switch between dark and light modes.

Table 3 Keyboard shortcut description

Shortcut	Description
Ctrl+Enter	Execute SQL statements. You can run SQL statements by pressing Ctrl+R or Ctrl + Enter on the keyboard.
Ctrl+F	Search for SQL statements. You can press Ctrl+F to search for a required SQL statement.
Shift+Alt+F	Format SQL statements. You can press Shift + Alt + F to format a SQL statement.
Ctrl+Q	Syntax verification. You can press Ctrl + Q to verify the syntax of SQL statements.
F11	Full screen. You can press F11 to display the SQL Job Editor window in full screen. Press F11 again to leave the full screen.

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.
  Figure 3 Executed SQL queries
  

  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. Currently, only the latest 100 job information records are displayed on the SparkUI of DLI. 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. Execution results of asynchronous DDL and QUERY jobs can be downloaded to a local directory. NOTE: The View Log button is not available for synchronization jobs and jobs running on the default queue.

  Results of asynchronous DDL and QUERY statements can be downloaded to a local directory. The procedure is as follows:

  1. Click More > Submit Download Request in the Operation column of the asynchronous DDL or QUERY statements that is successfully executed. In the dialog box that is displayed, click OK. After a few seconds, the Submit Download Request button would change to Download.

     

  2. Click Download to download the result to your local PC.

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 Set Job Bucket dialog box displayed, 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. For details about how to create a queue, see Creating a Queue.
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.
   

   If you click Execute when there is no SQL statement in the job editing window, the message "You have not entered any SQL statements." is displayed.

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.

  Figure 4 Saving a query as a template