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 .
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.
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.
No. | Button & Drop-Down List | 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 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. Tags: Set tags in key/value format for SQL jobs. |
8 | More | The drop-down list includes the following options:
|
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.
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.
Note
The View Log button is not available for synchronization jobs and jobs running on the default queue.
View Result
Table 5 Operations on the result tab¶ Operation
Description
Clear the result
Clear the displayed SQL statement query results.
View chart/table
Click
to view the query result in a chart or table.Export job results
Export the job execution results to the created OBS bucket.
SQL Query Procedure¶
Log in to the DLI management console. On the page displayed, choose Job Management > SQL Jobs. On the page displayed, click Create Job.
Note
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.
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:
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.
If the verification fails, check the SQL statement syntax by referring to Data Lake Insight SQL Syntax Reference.
If the syntax verification is successful, click Execute. Read and agree to the privacy agreement. Click OK to execute the SQL statement.
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, click
to display the query result in a chart. Click 
to switch back to the table view.Note
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.