Quickly edit and run SQL by using SQL analysis - MaxCompute

For ad-hoc data exploration and quick validation, such as previewing table data or testing a function, configuring a local client or a formal DataWorks project often introduces unnecessary overhead. SQL Analysis provides an immediate, web-based environment within the MaxCompute console for these exact scenarios. It enables you to write, execute, and analyze SQL queries on the fly, streamlining temporary tasks and accelerating your data discovery process.

Features

Write and run SQL queries in an interactive editor, and visualize query results with built-in charting tools.
Explore MaxCompute features instantly using built-in public dataset demos.

Use cases

Quick exploration: Explore the core features of MaxCompute with public datasets.
Ad hoc queries: Run temporary queries, such as previewing table data.

SQL analysis UI

Editor area

Module	Description
Tab bar	File tabs. You can open multiple files at the same time. Double-click the blank area of the tab bar to quickly create a `.sql` file. Right-click the blank area of the tab bar and select New File.
Toolbar	Run: Run the SQL script. You can select a specific SQL script in the current file to run it. If you do not make a selection, all SQL scripts in the file are run by default. Stop: After you click Run, the Stop button appears. Save: This feature is in public preview and does not support saving files to the cloud. You can only save files to an on-premises folder. Save the file as a `.sql` file. Format: Format the SQL code in the current file. Run Result: If an SQL script has been run in the current file and the results area is hidden, click this button to show the results area. Cost Analysis: Run a COST SQL statement to estimate the cost. The statement returns the data scan volume and complexity of the SQL statement.
Editor	The first line of the script must be a script that can be run. If a `.sql` file contains multiple SQL scripts, place all flags (SET statements) at the beginning of the file. The flags apply to all SQL scripts in the file. Open the editor in full-screen mode to enter and run scripts, and view the results.

Run configurations area

Module	Description
Project	Select the project where you want to run the SQL code. This parameter is required. The current account must have the `create instance` permission on the selected project. Select a project for the first run. The cache saves your selection. You can change the project at any time. The selection applies to all open file tabs.
Computing Quota	Select the computing quota to use. This parameter is optional. If you select a quota, the current account must have the `usage` permission on that quota. If you do not select a quota, the default computing quota of the project is used. The selection applies to all open file tabs.

Result area

Module	Description
PROBLEMS	Before a script is run, the syntax and specifications of all scripts in open files in the editor are automatically checked.
METADATA	Select an object, such as a table, resource, or user-defined function, in Table Data or Public Dataset to view its metadata details. Details include basic information, column information, DDL generation, and data preview. Note: You must have the required database permissions on the object.
RESULT	The results of the script run: Run List: Each time an SQL statement in the current file is run, a record with a start time is created. Click a record to view information about the corresponding run. Log: The log content for each run. SQL: The specific SQL code for each run. Result List: If there are results to display, they appear here. Analysis: If there are results, you can perform simple chart analysis here.
COST ANALYSIS	Displays the results of the COST SQL statement that is run for the Cost analysis. The results include the execution log and the result data.

Explorer

Right-click the empty area in the EXPLORER to configure which modules are displayed. For example, you can hide the TIMELINE module.

Module	Description
File List	When you access the file list for the first time, No Folder Opened is displayed. Click the Open Folder button to open an on-premises folder. The first time you open a folder, a dialog box appears and asks you to confirm the permission to view the folder. Important Each time the system tries to read an on-premises folder, manually grant access permissions to all files in the folder to ensure data security. The content of the folder is not saved on the server.
COMMONDATASET DEMO	MaxCompute SQL analysis provides built-in public dataset demo query files. You can run them directly or edit them before running. You cannot modify the online demo files. If you refresh the page, the file content is reset. Save the modified files to your on-premises computer. The built-in demos contain SQL statements edited based on public datasets. The public datasets all use schema storage. Therefore, the `set odps.namespace.schema=true;` statement is added to the scripts by default to enable the schema syntax. In the built-in demo files, each file contains multiple SQL scripts. The number before `-query` in the script name indicates the number of queries. For example, the TPC-DS dataset demo files each contain 99 queries of different data volume specifications. Running these queries consumes computing resources. Proceed with caution.
TIMELINE	Records the operation logs for files opened in the editor. The log information is stored in the cache. If you clear the cache, the logs are deleted.

Search

Module	Description
Search	Performs a global search for content in all files in the Resource Manager. For example, search for files that contain the statement FROM table_test1. Click a search result to open the corresponding file in the editor on the right and go directly to the keyword's location.
Replace	Replaces the searched keyword with another statement. For example, to replace the `FROM table_test1` statement with `FROM table_test2` in all files, enter `FROM table_test1` in the Search box, enter `FROM table_test2` in the Replace box, and click the button to perform the replacement.

Table data

Lists data objects (such as schema, tables, views, resources and functions) for all projects in the current region that you have access to.

Display scope: An Alibaba Cloud Account can view all projects. A RAM User can only view Projects to which they have been added.
The SYSTEM_CATALOG project in the list is a system project for the tenant-level Information Schema. The project is created by the system by default. The INFORMATION_SCHEMA is placed as a view under the "INFORMATION_SCHEMA" schema of this project and can be viewed directly.
View permissions: To view the list of objects in a project, you must have the required List permission.
For a table object, if the current logon account has the SELECT permission, the table icon in the list is a green table icon . This helps you quickly identify the tables that you have permission to access.
Schema level: After you expand a project, if the project has a schema level, the schema list is displayed first. Otherwise, the data object categories are displayed directly. You can click a category to expand the list of objects of that type.
Click an object to view its metadata on the Metadata tab in the right-hand results area.

Public dataset

This section displays the table metadata of the public datasets provided by the system.

Use SQL analysis

Log on to the MaxCompute console and select a region in the top-left corner.
Create a MaxCompute project. You must have a Project because all SQL Jobs run within one.
In the navigation pane on the left, choose Data Exploration > SQL Analysis.
On the Data Exploration > SQL Analysis page, create an SQL file.
1. When you open the SQL Analysis page for the first time, an SQL file named untitled_x is created and opened by default. You can edit this file directly.
2. Double-click a blank area on the file tab bar in the editor to create an SQL file.
3. Right-click the file tab bar in the editor and select New File.
After you enter SQL in the SQL editor, click Run Configurations on the right and configure the Project and Computing Quota.
- Project: Required. The project in which to execute the SQL statements. You must have the CREATE instance permission for this project.
- Computing Quota: Optional. This parameter specifies the compute resource quota for the job. If you specify a quota, your account must have the USAGE permission for it. If you do not specify a quota, the SQL statements are executed using the default compute resource quota configured for the project.
Click Run and wait for the query to complete. In the execution results, you can click the icon to perform a simple visual analysis.
The SQL Analysis does not directly support downloading query results. Use one of the following workarounds:
- Run the SQL task in the MaxCompute Client (odpscmd), then use the Tunnel command to download the results. For more information, see Download instance data.
- Run the SQL task in an ODPS SQL node in DataWorks, then use the export button in the interface to download the results. For more information, see Export, share, and download a workbook.
Click Save to save the current SQL file to your local computer.