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 job introduction. The SQL Editor page is displayed.

Enter the SQL Editor. 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 and job results. 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 receive this message, select Do not show again and click Cancel.

The areas and buttons on the SQL Editor page are described as follows:

Navigation pane

The navigation pane on the left contains Databases and Queues.

**Table 1** Navigation pane button description
No.	Tab/Button Name	Description
1	Databases	Displays all the existing databases and tables in these databases. After you click a database name, the tables in the database are displayed. After you click a table name, the metadata in the table is displayed below. A maximum of 20 metadata records can be displayed. After you double-click a table name, the SQL query statement is automatically entered in the job editing window.
2	Queues	Displays existing queues.
3	Templates	Click the drop-down button to view the built-in SQL sample templates, including 22 standard TPC-H query statements.
4	Create	You can 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	Refreshes the existing queues, databases, and table lists.
6	Search	You can enter a keyword to search for the corresponding database and table.

SQL Job Editing Window

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

**Table 2** Description of buttons in the SQL job editing window
No.	Button	Description
2	Queue	Select a queue from the drop-down list box. If no queue is available, the message Select a queue is displayed. Create a queue first.
3	Database	Select a database from the drop-down list box. If no database is available, the message "Please select a database" is displayed. Create a database first. NOTE: If the database where the table is located has been specified in your entered SQL statement, the selected database will become invalid.
4	Execute	You can click this button to run the SQL statements in the job editing window.
5	Format	Format SQL statements.
6	Syntax Reference	Click the button to switch to the Data Lake Insight SQL Syntax Reference.
7	Settings	Set the properties for submitted SQL jobs in the key/value format. A maximum of 10 attributes can be set.
8	More	Including: Click Verify Syntax to check whether the SQL statement is correct. Click Set as Template to set a common SQL statement as a template. For details, see SQL Template Management. Click Choose Template to select the SQL statement that has been saved as a template. Click Change Theme to select the black or white background.
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	Keyboard shortcuts	For details about keyboard shortcuts, see Table 3.

**Table 3** Keyboard shortcut description
Keyboard Shortcut	Description
Ctrl+R or Ctrl+Enter	Execute SQL statements. You can run SQL statements by pressing Ctrl+R or Ctrl + Enter on the keyboard.
Ctrl+F	Format SQL statements. You can press Ctrl + F to format the SQL statement.
Ctrl+Q	Syntax verification. You can press Ctrl + Q to verify the syntax of SQL statements.
Ctrl+Z	Undo your operation. You can press Ctrl + Z to undo the SQL statement in the job editing window to the previous step. For example, undo the formatted SQL statement to the state before formatting. This function is similar to the undoing operation.
Tab	Autocomplete. You can press Tab to quickly find the required SQL statement keywords, database name, and table name. Before pressing the Tab key, ensure that the cursor is located in the job editing window. If there are no statements in the job editing window, the prompted information is sequenced in alphabetical order. If you press Tab after entering the first letter of a keyword in the job editing window, the prompted information contains the SQL statement keywords, database names, and table names, whose first letter is the entered letter.
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 7 Days) and View Result tab pages are displayed in the lower right corner of the page.

Executed Queries

**Table 4** Area description
Area	Description
Executed Queries (Last 7 Days)	Executed Queries (Last 7 Days): Displays information about jobs submitted in the last 7 days. Including: Name Created Status Statement Operation Modify: Re-edit the SQL statement. Cancel: Stop executing the SQL statement. SparkUI: Switch to the SparkUI page to view the SQL statement execution process. Export Log: Export SQL statement execution logs.

You can filter the execution history in the following ways:

In the upper right corner, select a queue name or enter an execution statement.
In the list, click the icon next to Created and choose Ascending or Descending.
Select a job status from the list.

View Result

**Table 5** Area description
No.	Button	Description
1	Clear All	Clear the displayed SQL statement query result.
2	Graphical Result/Table Result	Display the query result in a graph or table.
3	Export Result	Exports the query result to OBS. For details, see Exporting Query Results.

SQL Query Procedure

Log in to the Data Lake Insight management console, select SQL Jobs, and 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 DLI data bucket to store temporary data generated by DLI, such as job logs and job results. 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 the queue to be used from the queue list in the upper left corner of the SQL job editing window. The default queue is used. 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 on the left. The query statement is automatically entered in the SQL job editing window.
Click Verify Syntax in More to check whether the SQL statement is correct.
1. If the verification fails, check the SQL statement accuracy 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 SQL statement is executed successfully, you can view the execution result in the area under the SQL job editing window.
(Optional) In the View Result tab page, click Graphical Result in the upper right corner to display the result in a graph. Then click Table Result to switch to the table form.
- If no column of the numeric type is displayed in the execution result, the result cannot be represented in charts.
- The chart types include the bar chart, line chart, and fan chart.
- In the bar chart and line chart, the X axis can be any column, while the Y axis supports only 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 drag table names and column names to the job editing window to compile SQL statements.
- 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, click More > Choose Template. Double-click the required SQL statement in the existing templates, import it to the SQL statement editing window, and click Execute, or modify it as required before executing the SQL statement.
- SQL statements can be executed in batches.
Features of the job editing window
The job editing window has the following three features:
- Highlighting: Common syntax is highlighted in different colors.
- 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 right corner of the window.