MaxCompute:SQL Analysis

Description

After you activate MaxCompute, you can create a project in the MaxCompute console and use the SQL Analysis feature to quickly edit and execute SQL statements. You can use the SQL Analysis feature of MaxCompute to perform the following operations in an efficient manner:

• Edit and execute SQL statements and analyze the execution results in charts.

• Open an SQL file of the built-in public dataset demo and run the code. This way, you can use and test MaxCompute based on the public datasets.

Scenarios

You can use the SQL Analysis feature in the following scenarios:

• If you use and test MaxCompute for the first time, you can use the SQL Analysis feature to experience the core features of MaxCompute based on the public datasets.

• You can use the SQL Analysis feature to temporarily execute specific SQL statements. For example, you can use the feature to temporarily query the data of specific tables.

Precautions

• You can go to the SQL Analysis page in the MaxCompute console immediately after you activate MaxCompute. However, you can use the SQL Analysis feature to run SQL jobs only after you create a project.

• The SQL Analysis (Beta) feature does not support cloud file systems. This feature allows MaxCompute to access your local file directories. This way, you can open and edit local SQL files. You can also create SQL files and save the files to your on-premises machine. The name of each SQL file is suffixed with .sql.

UI

The following figure shows the user interface (UI) of the SQL Analysis feature.

• 1. Editing section

  Module	Description
  Tab bar	The file tabs. You can open multiple files at the same time. You can double-click the blank area in the tab bar to quickly create an SQL file. You can right-click the blank area in the tab bar and select New File to create a file that is not in the SQL format. For example, you can create a JSON-formatted file. Note If you create a file that is not in the SQL format, you must save the file as an SQL file to use the toolbar as expected.
  Toolbar	Run: Run the SQL script. You can select a specific code segment in the current SQL file and click Run to run the code. If you do not select a specific code segment, all code in the SQL file is run by default. Stop: Stop a job. After you click Run, the Stop button appears. You cannot use the SQL Analysis (Beta) feature to stop a job. If you want to stop a job, you can go to the Jobs page. For more information about how to stop a job, see Manage jobs in the new MaxCompute console. Save: Save a file. The SQL Analysis (Beta) feature does not support cloud file systems. This feature allows you to save files only to your on-premises machine. You must save files as SQL files. Format: Format the SQL script. Run Result: Display the result that is hidden after the SQL script in the SQL file is run. Cost Analysis: Execute the COST SQL statement to estimate the cost of running an SQL job. The complexity of the SQL job and the number of data records to be scanned are returned. For more information about the COST SQL statement, see COST SQL.
  Editor	The SQL editor. When you perform operations in the SQL editor, take note of the following items: The first line of the script must be run. If an SQL file contains multiple SQL scripts and SET statements, make sure that the SET statements are placed in the file header. The SET statements are applied to all SQL scripts in the file. You can write SQL scripts, run SQL scripts, and view results in the SQL editor in full-screen mode.

• 2. Run Params tab

  Module	Description
  Project	The project in which you want to run the SQL code. This parameter is required. You must specify a project on which your Alibaba Cloud account has the CreateInstance permission. You must specify a project the first time you run the SQL code. By default, the project that you specify is used for the subsequent operations. You can change the value of this parameter. The value of this parameter takes effect on all file tabs.
  Quota	The computing quota that you want to use. This parameter is optional. If you configure this parameter, you must specify a computing quota on which your Alibaba Cloud account has the Usage permission. If you do not configure this parameter, the SQL code is run in the default computing quota configured for the project. The value of this parameter takes effect on all file tabs.

• 3. Result section

  Module	Description
  PROBLEMS	Before you run an SQL script, MaxCompute automatically checks the syntax and specifications of the script in the editing section. This helps you identify script standardization issues before the script is run.
  METADATA	You can select an object, such as a table, resource, or user-defined function (UDF), in the Table Data or Dataset Manage pane to view the metadata of the object. The metadata includes the basic information, column information, and DDL statement. You can also preview data of the object. You must have the relevant permissions on the objects. For more information, see MaxCompute permissions.
  RESULT	The results of running an SQL script. You can view the following results: Running Records: The records of the start time each time an SQL statement in the current file is executed. You can click the start time to view information about each running record. Logs: Logs are generated each time an SQL statement of the file is executed. SQL: The specific SQL code that is run. Results: The execution results. Analysis: You can analyze the execution results in charts.
  COST ANALYSIS	The execution results that are returned after you click Cost Analysis. The execution results include execution logs.

• 4. EXPLORER pane

  Module	Description
  Folders	Displays a list of files. The SQL Analysis (Beta) feature allows MaxCompute to access your local files. The first time you go to the SQL Analysis page, NO FOLDER OPENED is displayed. You can click Open Folder to open a local folder. The first time you open a folder, a message that prompts you to confirm the permissions to view the folder is displayed. Important To ensure data security, you must manually authorize MaxCompute to access all files in the specified folder each time you open a page and the system attempts to read your local folder. MaxCompute does not save the files of your folder to the server.
  COMMONDATASET DEMO	Displays the query files of the built-in public dataset demo. You can directly open and run the files, or temporarily edit the files and then run the files. However, you cannot save the content that you modify. If you save the modified content and refresh the page, the system retains the original content of the file. You can save the modified content of the file to your on-premises machine. The built-in demo files contain SQL statements that are written based on public datasets. Public datasets are stored in schemas. By default, the set odps.namespace.schema=true; statement is added to the SQL script to enable the schema syntax. Each built-in demo file contains multiple SQL scripts. The number before -query in the script name indicates the number of queries. Each demo file of a TPC-DS dataset has 99 queries, and the volume of queried data varies based on the specifications of TPC-DS datasets. The execution of the SQL script consumes computing resources and generates computing fees. Proceed with caution.
  TIMELINE	Displays the operation log generated each time a file is opened in the editing section. The logs are stored in the cache. If you clear the cache, the logs are deleted.
  Right-click operation	Allows you to right-click the blank area in the EXPLORER pane to perform operations. For example, if you want to hide the COMMONDATASET DEMO module, you can right-click the blank area and click CommonDataSet Demo to deselect the module.

• 5. Search section

  Module	Description
  Search	Provides a global search of all files in the EXPLORER pane. For example, you can search for files that contain from table1 statements. You can enter a keyword to search for files. Then, you can click a search result to open the file in the editing section and directly locate the keyword. Allows you to search for files in the Folders pane.
  Replace	Allows you to replace a keyword in the search results with another keyword. For example, if you want to replace from table1 in all files with from table2, you can enter from table1 in the Search field to search for the files, enter from table2 in the Replace field, and then click the button to replace the keyword in the files.
  Table Data	Displays the objects in all projects of the current tenant in the region. The objects include schemas, tables, views, resources, and functions. To view the list of objects in all projects, you must have the List permission on the objects. If you have the Select permission on a table, the icon is displayed on the left side of the table name. This icon helps you quickly identify the table on which you have the Select permission. The SYSTEM_CATALOG project in the list is the system project in which the Tenant-level Information Schema service is enabled. The system creates the project by default. You can directly access the views provided by the INFORMATION_SCHEMA schema. An Alibaba Cloud account can view the list of all projects. A RAM user must be added to a project to view the project in the project list. You can click a project to expand the objects in the project. If your project contains schemas, the schema list is displayed. If your project does not contain schemas, the data object categories are displayed. You can click each category to expand the object list under the related category. If you click an object, the metadata of the object is displayed on the METADATA tab in the result section.
  DataSet Manage	Displays the table metadata of public datasets. For more information about the public datasets, see Overview.

Use the SQL Analysis feature

1. Log on to the MaxCompute console. In the upper-left corner of the console, select a region.

2. In the left-side navigation pane, choose Workspace > SQL Analysis.

3. On the SQL Analysis page, create an SQL file.

   The first time you go to the SQL Analysis page, a file named untitled_x is opened by default. You can directly write SQL scripts in the file. You can also double-click the blank area in the tab bar to create an SQL file.

4. Write SQL scripts in the SQL editor and click the Run Params tab. In the panel that appears, configure the parameters.

   • Project: the project in which the SQL code is run. This parameter is required. You must specify a project on which your Alibaba Cloud account has the CreateInstance permission.

   • Quota: the computing quota that you specify for a job. This parameter is optional. If you configure this parameter, you must specify a computing quota on which your Alibaba Cloud account has the Usage permission. If you do not configure this parameter, the SQL code is run in the default computing quota configured for the project.

5. Click Run. On the RESULT tab, view the execution result.

   You can click the icon to perform visualized analysis.

6. Click Save to save the SQL file to your on-premises machine.