The MaxCompute console provides a query editor for you to execute SQL statements and analyze data. This topic describes how to use the query editor.

Overview

The query editor of MaxCompute is integrated into the DataAnalysis tool of DataWorks. You can use the query editor to edit MaxCompute SQL statements, query data, analyze data in workbooks, share data online, and download data. You can use the query editor to perform the following operations:
  • Edit and execute SQL statements and authorization commands, such as access control list (ACL)-based authorization commands.
  • Enable the MaxCompute Query Acceleration (MCQA) feature. After this feature is enabled, the time that is used to run small- and medium-sized query jobs is reduced from minutes to seconds. For more information about MCQA, see MCQA overview.
  • Use and test MaxCompute based on the public datasets of MaxCompute. By default, the public datasets are open to users. You can also use the query editor to create a table and import data to the table. For more information about table operations, see Table operations.
  • Analyze, download, and share query results by using Web Excel online.

Scenarios

The query editor can be used in the following scenarios:
  • If you use and test MaxCompute for the first time, you can use the query editor to experience the core features of MaxCompute based on the public datasets.
  • If you are a data analyst, you can use the query editor to query data. Then, you can switch to the analysis mode to analyze the query results by using Web Excel online. To reduce the frequency at which data is transferred and ensure data security, you can also download the query results to your computer for analysis.
  • If you are a security administrator, you can find a required project and click Project permission management in the Actions column to manage role permissions. However, this feature is in trial use. In many scenarios, you must run commands to manage permissions. The query editor allows you to run most of the security commands without the need to perform additional configurations.

Open the query editor

Note If you use the query editor for the first time, the system automatically creates a default project for you. The project creation takes a period of time. After the project is created, you can use the query editor.
  1. Log on to the MaxCompute console, select a region in the top navigation bar, and then click SQL Query to open the query editor.
  2. Configure Workspace, Engine Type, and Data Source, and click Save to go to the query editor, as shown in the following figure. Open the query editor
    Note If you select a project that is associated with a workspace in standard mode and commit a workflow by using the query editor, the workflow is committed in a project in the development environment. The name of such a project is ended with _dev.

Create an SQL query file

You can create SQL query files based on the workspace mode and the compute engine type.

  1. Create an SQL query file.
    You can create an SQL query file in the following scenarios:
    Scenario 1: An existing SQL query file is openedScenario 2: You go to the SQL Query tab for the first time or no SQL query file is displayed on the current pageScenario 3: General mode
    Create an SQL query fileClick the Icon icon to the right of the name of the existing file to create a new SQL query file. SQL QueryClick Create SQL Query in the right-side area to create an SQL query file. My FilesIn the left-side navigation pane of the SQL Query tab, move the pointer over the Add icon to the right of My Files and select Create File.
  2. Select a data source for the SQL query file.
    Select the desired workspace, the compute engine type, and the data source whose data you want to query. You can query the data of data sources of compute engine types such as MaxCompute, EMR, CDH, Hologres, and AnalyticDB. Select a data source
    Note You can select only the data sources on which you have query permissions. If no data source of a specific compute engine type is available, contact Workspace Manager to add your account to the workspace as a member and assign the Data Analyst, Model Developer, Development, O&M, Workspace Manager, or Project Owner role to your account.

Manage SQL query files

On the SQL Query page, you can perform the following operations on SQL query files. SQL query
ItemDescription
Search for a tableIn the area that is marked with 1, you can enter the table name in the search box and click the Search icon to search for a table.
View the running history of SQL statementsIn the area that is marked with 1, you can click the Running History icon to view the running history of SQL statements.
Add a directoryIn the area that is marked with 1, you can click the Dataset icon to add a directory. You can add the recommended directories or add tables in the data sources on which you have query permissions as a directory. After you add tables as a directory, you can view the SQL files or data tables in the directory. You can also view the schema of a table and perform simple operations to generate SQL statements for the table.
My FilesIn the area that is marked with 2, the My Files directory stores the SQL query files that you saved. You can share an SQL query file to a specific workspace.
  • Share: indicates that the file is being shared. All members of the workspace to which the file is shared can view the file in the Other People's Files directory. You can right-click a file and select Share to share the file.
Other People's FilesIn the area that is marked with 2, the Other People's Files directory stores the SQL query files that are saved by other members. The files can be viewed by all members of the current workspace.
Public TablesIn the area that is marked with 2, you can click the Dataset icon to add the Public Tables directory. The Public Tables directory stores the tables that are provided by DataAnalysis. You can use the tables to experience the SQL query feature without the need to apply for required permissions.
My MaxCompute tablesIn the area that is marked with 2, you can click the Dataset icon to add the My MaxCompute tables directory. The My MaxCompute tables directory stores the MaxCompute tables within your account, including tables in the development and production environments.
My FavoritesIn the area that is marked with 2, you can click the Dataset icon to add the My Favorites directory. The My Favorites directory stores the tables that are added to favorites. To add a table to your favorites, right-click the table and select Add to Favorites.