Activate SQL Analysis and Query Data Fast - MaxCompute - Alibaba Cloud - MaxCompute

The MaxCompute console provides the sql analysis feature for running SQL statements and managing the MaxCompute data catalog. This topic describes how to use the sql analysis feature to access MaxCompute.

New SQL analysis

To keep pace with cloud-native technologies and meet growing demands for complex data analytics, the sql analysis feature in the Alibaba Cloud MaxCompute console has been fully upgraded. It integrates deeply with DataWorks's new data analysis feature to provide a more efficient, intelligent, and unified data analysis and metadata management experience. The new sql analysis supports the following features:

Intelligent sql editor: Edit and run SQL commands, try out product features, and develop and debug your daily SQL code.
Data catalog: Manage metadata for various MaxCompute objects, including projects, schemas, tables, and views.
Public datasets : Explore built-in MaxCompute public datasets and SQL demo scripts. You can open the corresponding SQL files and run the code to quickly try out MaxCompute.

Enable the new SQL analysis

Prerequisites

The new sql analysis is available in the following regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), China (Zhangjiakou), China (Ulanqab), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Indonesia (Jakarta), Japan (Tokyo), Germany (Frankfurt), US (Silicon Valley), US (Virginia), UK (London), and UAE (Dubai).
You have activated MaxCompute and DataWorks.
You have activated a DataWorks resource group.
You have created a MaxCompute project.

Procedure

Log in to the MaxCompute console and select a region in the upper-left corner.
In the left-side navigation pane, choose Data Exploration > SQL Analysis.
In the dialog box that appears, activate DataWorks. If DataWorks is not activated, click Activate Now when prompted. After DataWorks is activated, click Try New Version to open the new SQL Analysis page.
- To use the new sql analysis, you must activate DataWorks and a DataWorks resource group. For more information about the editions of DataWorks, see Features of DataWorks editions.
  - Ensure you have activated the pay-as-you-go (free) DataWorks Basic Edition or a higher edition.
  - Ensure you have activated a DataWorks resource group. If you purchased a legacy resource group, go to the console. For more information about the differences between new and legacy resource groups, see DataWorks resource groups.
- Be aware of the change in how SQL files are managed: The new sql analysis provides a personal folder to create and manage SQL directories and files in the cloud. Local files from the old workspace are not automatically synchronized to the personal folder. We recommend that you manually move them to your personal folder for future editing.

Features

The new sql analysis includes the following key modules:

Resource manager: Manages MaxCompute SQL script files. For more information, see Use the resource manager and search feature.
Data catalog: Manages and operates on MaxCompute metadata. For more information, see Use the data catalog.
SQL editor: Edits and runs MaxCompute SQL scripts. For more information, see Use the editor for sql analysis.

Resource manager and search

The new sql analysis provides a resource manager and a search feature.

Resource manager: Manages sql analysis script files and provides several resource directories.
Search and replace: Supports global search across the content of all files in the resource manager and allows you to replace keywords with other statements.

Directory type	Description
Personal folder (recommended)	Go to resource manager > personal folder. The personal folder module serves as a file list for creating and managing SQL scripts stored in the cloud. In the personal folder > My Files directory, you can create folders or files as needed. Right-click an item to move, rename, or delete it. Only SQL files with the `.sql` extension are supported. After a file is created, you can click the filename to open the editor and edit the SQL script.
Workspace	Go to resource manager > Workspace. For users who have used the previous version of sql analysis, the resource manager retains the original workspace directory and SQL script files by default. For users who have not used the workspace in the previous version of sql analysis, this module is hidden by default. We recommend that you use the new personal folder. The workspace module serves as a file list for managing SQL scripts from a local directory. You can open a local folder or a local `.sql` file, and save new or edited `.sql` files back to your local machine. Note To ensure data security, you must manually grant access to all files in a specified folder each time the page tries to read a local folder. Alibaba Cloud does not store the content of local folders on its servers.
Public dataset demo	Go to resource manager > public dataset demo. These are built-in public dataset demo query files that you can open and run directly. You can also make temporary edits and run them, but the edits cannot be saved. You must save any modified content manually. Each file contains multiple SQL scripts. The number before `-query` in the script name indicates the number of queries. In particular, the TPC-DS dataset demos contain 99 queries per file, with different data volume specifications. Running these queries consumes computing resources. Proceed with caution.

Data catalog

The new sql analysis includes a data catalog module that provides a unified interface to manage MaxCompute metadata. This feature supports creating and managing data objects such as tables, views, external tables, resources, and functions. It supports the following key features:

Feature	Description
Add and remove projects	Go to Data Catalog > MAXCOMPUTE. By default, all MaxCompute projects accessible to the current tenant are added to the data catalog list as datasets. You can click to go to the project list page to create or remove MaxCompute projects.
Manage the data catalog	Go to Data Catalog > MAXCOMPUTE. Click the icon next to a specific MaxCompute project to add and manage data objects such as schemas, tables, and views in the data catalog. Integrated with DataWorks Copilot, it supports intelligent table creation and smart generation of fields and their descriptions. For more detailed instructions, see Manage the MaxCompute data catalog.
Public datasets	Go to Data Catalog > public datasets. MaxCompute provides a set of public data stored in the `BIGDATA_PUBLIC_DATASET` public project to help you try out the product. For more information, see Overview of public datasets. It displays all schemas and tables in the `BIGDATA_PUBLIC_DATASET` public project and lets you view the table list and their corresponding field details.

SQL editor

The new sql analysis provides a powerful editor that lets you write SQL scripts to process and query data in MaxCompute. It supports common SQL operations such as SELECT, INSERT, UPDATE, and DELETE, as well as MaxCompute-specific syntax and functions. For more information, see SQL overview.

The editor page includes the following features:

Area	Description
Editor area	Run: Executes the SQL script. You can select a segment of the SQL script in the current file to run. If you do not make a selection, all SQL scripts in the current file are executed by default. Stop: Stops a running node. After you click Run, you can click the Stop button. Save: Saves the currently edited SQL script file and related configurations. Format: Formats the SQL code in the current file. This is useful for long, single-line code. SQL Scan Estimate: Runs a COST SQL statement to estimate the cost. It returns the scan size and complexity of the SQL statement. For a detailed description of COST SQL, see COST SQL. Editor: The SQL code editing box. It supports a full-screen mode for entering, executing, and viewing results. If a file contains multiple SQL scripts, place all flags (SET statements) at the beginning. The flags apply to all SQL scripts in the file.
Runtime parameters	Type: The file type. The default is MaxCompute SQL. Computing resources: Project: Select the project in which to run the SQL code. This is required. Select a project where your account has `create instance` permissions. You must select a project for the first run. The system then caches your selection for subsequent runs. Computing quota: Select the computing quota to use. This is optional. Select a quota where your account has `usage` permission. If you do not select a quota, the default computing quota of the selected project is used. Script parameters: Define variables in your code using the ${parameter_name} format and configure their values in this section. At runtime, the variables are dynamically replaced with their actual values.
Results area	Problems: Before execution, the system automatically checks the syntax and standards of all open scripts in the editor to identify potential compliance issues. SQL Scan Estimate: Displays the execution log and results of the COST SQL statement from the SQL Scan Estimate operation. Result: The results of the script execution. Details are as follows: Run List: Each time a SQL script in the current file is run, a record with a start time is created. You can click a record in the list to view information about that run. SQL: The specific SQL code for each run. Log: The log content for each execution. Result: The run results are displayed directly in a spreadsheet format. Analysis: Supports simple chart-based analysis.

The SQL editing feature of the new sql analysis is based on the new data analysis capabilities of DataWorks. For more information about developing MaxCompute SQL nodes, see MaxCompute SQL node.

Version comparison

Item	SQL analysis (previous version)	SQL analysis (new version)
Product dependencies	Activate MaxCompute.	Activate MaxCompute. Activate the pay-as-you-go DataWorks Basic Edition (free) or any higher edition. Activate a DataWorks resource group.
Resource manager	Provides a workspace to manage SQL scripts based on a local file directory.	Provides a personal folder to create and manage SQL directories and files in the cloud.
Data catalog	Supports viewing MaxCompute objects such as tables, views, external tables, resources, and functions.	Supports creating and managing MaxCompute objects such as tables, views, external tables, resources, and functions.
SQL editor	Supports basic SQL script editing and execution.	Based on the new data analysis capabilities of DataWorks, it provides a more intelligent sql analysis experience.