SQL Editor

You can use SQL statements in the SQL job editor to execute data query. DLI supports SQL 2003 and complies with Spark SQL.

On the Overview page, click SQL Editor in the navigation pane on the left or Create Job in the upper right corner of the SQL Jobs pane. The SQL Editor page is displayed.

A message is displayed, indicating that a temporary DLI data bucket will be created. 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 it. You can to periodically delete objects in a bucket or transit objects between different storage classes. The bucket name is set by default.

If you do not need to create a DLI temporary data bucket and do not want to view this message, select Do not show again and click Cancel.

The SQL Editor page is composed of the following parts:

Navigation pane

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

**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.
4	Create	Click the button on the related page to create a queue, database, or table. For details, see Creating a Queue and Creating a Database or a Table.
5	Refresh	Click the button to refresh the existing queue, database, and table lists.
6	Search	Enter a keyword to search for a database and table.

SQL Editing Window

The SQL job editing window is displayed in the upper right corner. The following describes the components of the window.

**Table 2** Components of the SQL job editing window
No.	Button	Description
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 be executed only 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 section 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. 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 SQL Template Management. Click Change Theme to switch between dark and light modes.
9	SQL statement editing area	Area for you to write and edit SQL statements.
10	Cursor position	The line and column where the cursor is in the job editing area.
11	Shortcuts	For details, see Table 3.

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

Execution Queries and View Result

The Executed Queries (Last Day) and View Result tab pages are displayed in the lower part of the page.

Executed Queries

**Table 4** Area description
Area	Description
Executed Queries (Last Day)	Displays the latest daily information about the submitted jobs. 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. Export Log: Export SQL statement execution logs. NOTE: To export the logs, you need to obtain the permission to create an OBS bucket. View Log and Export Log buttons are not available for synchronization jobs and jobs running on the default queue.

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.

View Result

**Table 5** Function description
No.	Button	Description
1	Clear All	Clear the displayed SQL statement query results.
2		Display the query result in a graph or table.
3		Exports the query result to OBS. For details, see Exporting Query Results. A maximum of 1000 records can be displayed in the query result on the console. To view more or all data, you can click Export Result to export the data to OBS.

SQL Query Procedure

Log in to the Data Lake Insight management console, choose Job Management > SQL Jobs. In the displayed page, click Create Job to go to the SQL Editor page.
After you enter the SQL Editor page, the system prompts you to create a temporary OBS bucket to store temporary data generated by DLI, such as job logs. You cannot view job logs if you choose not to create it. Click OK. The system displays a message indicating that the temporary bucket is created successfully.
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.
In the upper right corner of the SQL job editing window, select a database, for example, qw, from the Databases drop-down list.
Create a table, for example, qw. For details about how to create a database and table, see Creating a Database or a Table.
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.
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.
(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.
(Optional) In the View Result tab page, click in the upper right corner to display the result in a graph. Click to display the query result in a table.
- 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.

Operation Skills in the Job Editing Window

A simple method for importing SQL statements in the SQL job editing window:
- 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 click Execute or modify it as required before executing the SQL statements.
- SQL statements can be executed in batches.
Features of the job editing window
The job editing window has the following three features:
- Highlighting: Statement keywords are highlighted.
- Comment support: You can write comments in a single line or multiple lines. In each line, the comments follow two consecutive hyphens (--).
- Cursor location display: After you click in a line in the job editing window, the location of your cursor in the job editing window, including the column number and line number, will be automatically displayed in the lower left corner of the window.