Query data sources by using SQL-DataWorks(DataWorks)-阿里云帮助中心

You can use SQL statements to quickly query and analyze data from data sources such as MaxCompute, EMR Hive, and Hologres. This topic describes how to query data sources by using SQL statements.

Important

DataWorks recommends that you try the new version of Data Analysis to access the latest features and enjoy a better user experience.

Supported data sources

The SQL query feature supports data sources including MaxCompute, Hologres, EMR, CDH, StarRocks, ClickHouse, SelectDB, Doris, AnalyticDB for MySQL 3.0, AnalyticDB for PostgreSQL, Tablestore, MySQL, PostgreSQL, Oracle, and SQL Server.

Important

This feature only supports data sources that are added to a workspace.

Data source permissions

Data source scope

You can query data only from data sources within workspaces for which you have permissions. Contact an administrator to add you to a workspace as a data analyst, model designer, developer, operator, workspace administrator, or project owner.

Data source access permissions

Data source access supports the following two identity modes.

Access identity mode	Description	Supported data sources	Authorization
Executor Identity	The Alibaba Cloud account currently logged on to DataWorks.	MaxCompute and Hologres.	Ask the administrator of the specified MaxCompute project or Hologres instance to grant you member access permissions.
Data Source Default Access Identity	The access identity configured when the data source was created.	All data sources.	If your current logon account is not the default access identity of the data source, ask a user with the workspace administrator role to grant access to your current Alibaba Cloud account.

Important

If the MaxCompute project has allowlist-based access control enabled, add the required IP addresses to the IP allowlist of the MaxCompute project.

Access the feature

Log on to DataWorks Data Analysis, switch to the target region, and click Enter Data Analysis.

If you see Switch to New DataAnalysis in the navigation bar, you have entered the legacy Data Analysis page.
(Not recommended) If you see Return to Legacy DataAnalysis in the navigation bar, click it to switch back to the legacy Data Analysis page.

Step 1: Add directories

You can add tables from system-recommended directories and authorized data sources to your directory. After adding a directory, you can quickly view tables in the directory, view table schemas, and generate SQL queries.

On the SQL analysis page, click the button to the right of the search box above the directory to add a directory.
- DataMap - Metadata: Table metadata collected in Data Map. Each data source or compute resource can be added as a dataset.
- DataMap - Data Album: Data Collections that aggregate tables of specific topics in Data Map. Each Data Collection can be added as a dataset.
- My Favorites: Tables you have favorited in Data Map.
- My MaxCompute Tables: All MaxCompute tables for which the current logon account is the owner.
- Public Data: Public datasets provided by MaxCompute, which can be used to quickly generate test data.
- Alibaba Cloud billing data: Your Alibaba Cloud billing data. After adding it, you can perform billing analysis.
Note
You can add up to 12 datasets. Datasets that are no longer needed can be removed.

Step 2: Create an SQL query

Query based on data catalog

After you add data directories, the corresponding datasets are displayed in the directory tree on the left.

In the directory tree on the left, click to expand an added dataset (for example, my MaxCompute tables).
Right-click the name of the table that you want to analyze and select Generate SQL Statement from the context menu. A recommended SQL statement based on the table is automatically generated as a temporary file.
Modify the SQL statement as needed and click Save to save the temporary file to My Files.

Query based on data sources

In the directory tree on the left, hover over My Files and click the button on the right to create a file.
Write an SQL query statement in the new file and save it to My Files.
Note
When you edit SQL statements, DataWorks automatically suggests tables for which you have permissions in MaxCompute.

Query based on shared SQL

In the directory tree on the left, click Shared Files to view SQL files shared by other users. Click an SQL file, and on the details page on the right, click Copy SQL.

Query based on public datasets

After you add a public dataset to the directory, click the dataset. In the top bar of the details page on the right, you can select a different engine to Generate SQL Statement. You can use public datasets for testing.

Step 3: Configure the query engine and run the query

Click the button in the upper-right corner of the SQL details page to configure the SQL query engine.

Parameter	Description
Workspace	The workspace where the execution engine resides. Important Make sure that you have access permissions on the workspace. If not, contact the workspace administrator to add you as a workspace member.
Datasource Type	The type and name of the execution engine. Important If no specific project is specified in the SQL statement, the execution engine serves as the default data source.
Data Source Name
Access Identity Mode	Select the access mode for SQL queries: Executor Identity: Only supported for MaxCompute and Hologres engines. This mode is recommended when you are a member of the MaxCompute project or Hologres instance and have SELECT permissions. Data Source Default Access Identity: If your current account is different from the default access identity configured when the data source was created, grant your current account that identity.

After you write the SQL statement, you can directly run the entire script or select a portion to run partially. Click the drop-down icon next to the run button to switch the run mode. You can select different modes for different scenarios.

Before a MaxCompute SQL statement is run, the estimated cost is displayed. You can also click More > Estimate Costs in the toolbar above the SQL file to estimate the cost.

Run mode	Scenario	Trigger condition	Applicable engines
Query mode (LIMIT 10000)	Quickly preview data and verify query logic. This mode is suitable for scenarios where you only want to view a small sample of results for preliminary data exploration.	The query result displays ≤ 10,000 rows and ≤ 10 MB.	No limit
Query mode (full data)	Obtain the complete result set for analysis or export. Switch to this mode when you need to process and view all the data.	The query result displays > 10,000 rows or > 10 MB.	`MaxCompute`, `Hologres`, `StarRocks`, `ClickHouse`, `MySQL`
Temporary table mode	Reuse results in multi-step complex queries. Use the output of one query as the input of the next query to improve development and debugging efficiency.	The query result displays ≤ 10,000 rows and ≤ 10 MB, and is automatically written to a temporary table.	Only `MaxCompute`

After the SQL statement is executed, you can view the runtime log, results, and the corresponding SQL statement on the query result page.
You can click the buttons in the upper-right corner of the query result area to switch the page layout between side-by-side and top-bottom.

Step 4: Visualize query results

In the toolbar on the left of the query results, click the button to automatically generate visual charts from the results. You can click the edit button in the upper-right corner to create charts, save them to cards, and perform enhanced analysis.

Note

You can click the Copilot button above the chart to try the Copilot feature.

Step 5: Export and share

Important

If you need to export data to your local machine and then import it to another data source, we recommend that you use Data Integration batch synchronization tasks for more efficient and stable data migration and synchronization.

SQL query results can be exported in the following forms:

Local file: Download query results to your local machine in CSV, TXT, or XLS format. Key information is as follows:

Item	Description
Download limits	Only the MaxCompute and EMR engines are supported. For more information, see Download row count limits. If the MaxCompute project has data download restrictions enabled (data download is prohibited), downloading data through Data Analysis will fail. Important If this option does not appear, see FAQ for troubleshooting.
Scope	You can choose to download Only Data Displayed in Table or All Data. Only Data Displayed in Table: Downloads only the data displayed on the current page, which is up to `10000` rows by default. All Data: Exports all queried result data within the download limit.
Download method	Two download methods are supported: Download After Approval and Download Without Approval. Download after approval: You can configure risk identification rules to identify risks in data download operations. When downloading data, you must submit a download approval request to ensure data compliance and security. Note Only DataWorks Enterprise Edition supports configuring and enabling risk identification rules. Download without approval: By default, downloads do not require approval. No permission request is needed during the download process.

OSS: Export query results in a specified format (such as CSV or Parquet) to Alibaba Cloud Object Storage Service (OSS). This method is suitable for archiving large volumes of data or integrating with other cloud services.

When you use this feature for the first time, you need to authorize DataWorks to access your OSS resources. In the File Path drop-down list, click the Authorize link in the prompt and follow the instructions to complete RAM authorization.

Parameter	Description
File path	Click the folder button on the right to select the OSS bucket and directory where you want to store the result file.
File name	A file name is automatically generated. You can also manually modify it.
File type	Select the export file format. Supported formats include `csv`, `text`, `orc`, and `parquet`.
Delimiter	Specify the delimiter between columns. The default delimiter is a comma (`,`).
Encoding	Select the encoding format for the file, such as `UTF-8` or `GBK`.
CU	Configure the number of compute units (CUs) for this export task. The default value is 1 CU.
Resource group	Select the serverless resource group to execute this export task. If no resource group is selected, the Data Integration resource group configured in Data Analysis > System Management is used by default.

After you complete the configuration, click OK to start the export task. You can view the export progress, runtime log, and configuration details on the task execution page. After the task succeeds, go to the OSS console to download the exported file to your local machine.

MaxCompute table: You can export query results directly to a MaxCompute table without downloading data to your local machine first. You can set the lifecycle of the table as needed.
This option is available only when you query MaxCompute engine content.
Spreadsheet: You can save the results to a spreadsheet for further in-depth data analysis. You can also share the latest analysis results with others.
DingTalk spreadsheet: You can export results to a DingTalk spreadsheet document.

More operations

SQL file version management

On the SQL file editing page, you can also click More > Version in the toolbar to view the differences between auto-saved code and manually saved code, and select the version you want to save.

Code search

Above the directory tree on the left, click to search for code by keyword. This feature is available only for DataWorks Standard Edition and above.

View run history

Above the directory tree on the left, click to view the SQL query execution history.

FAQ

How do I grant access using the data source default access identity?

Go to Security Center.
Log on to the DataWorks console. In the target region, click Data Governance > Security Center in the left-side navigation pane. On the page that appears, click Go to Security Center.
In the left-side navigation pane, click Security policy > Data query and analysis control to go to the Data Analysis access control page.
Switch to the target workspace, find the target data source, and click the authorization button on the right to grant access.

SQL query execution error?

If you encounter an error related to This node can only run on exclusive resource groups during execution, configure Resource Group for Scheduling and Resource Group for Data Integration for the corresponding engine in Data Analysis > More > System Management.

Query result display or download is restricted?

SQL query results only display a limited number of rows. Follow these steps to adjust the display limit to the maximum. You can also manage download capabilities. For more information, see Configure query result display and download limits.

Go to Security Center.
Log on to the DataWorks console. In the target region, click Data Governance > Security Center in the left-side navigation pane. On the page that appears, click Go to Security Center.
In the left-side navigation pane, click Security policy > Data query and analysis control to go to the Data Analysis access control page.
Switch to the Query Result Control tab, and adjust the Maximum rows per display, Maximum rows per copy, Maximum rows per download, and Allow download settings.