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 using SQL statements.

Important

DataWorks recommends that you try the new version of DataAnalysis 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, O&M, 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.
Data Source Default Access Identity	The access identity configured when the data source was created.	All data sources.	If your current account is not the default access identity for the data source, ask a Workspace Administrator to grant this identity to your account.

Important

If whitelist-based access control is enabled for a MaxCompute project, you must add the IP addresses for Data Analysis to the IP address whitelist of the MaxCompute project.

Prerequisites

Log on to DataWorks DataAnalysis, switch to the target region, and then click Enter DataAnalysis.

If you see Switch to New DataAnalysis in the navigation bar, you are on the legacy Data Analysis page.
(Not recommended) If you see Return to Legacy DataAnalysis in the navigation bar, click it to return to the legacy Data Analysis page.

Step 1: Add a catalog

You can add system-recommended catalogs and tables from authorized data sources to your catalog. After you add a catalog, you can quickly view its tables, view the table schema, and generate SQL queries.

On the SQL Analysis page, click the icon to the right of the search box above the catalog to add a catalog.
- DataMap - Metadata: Contains table metadata collected by Data Map. Each data source or query engine can be added as a dataset.
- DataMap - Data Album: Data albums in Data Map that group tables by subject. Each data album can be added as a dataset.
- My Favorites: Tables that you have added to your favorites in Data Map.
- My MaxCompute Tables: All MaxCompute tables owned by the currently logged-on account.
- Public Data: Public datasets provided by MaxCompute that you can use to quickly generate test data.
- Alibaba Cloud bill data: Provides access to your Alibaba Cloud bill data. After adding it, you can perform bill analysis.
Note
You can add a maximum of 12 datasets. You can remove datasets that you no longer need.

Step 2: Create an SQL query

Query data based on a data catalog

After you add data to a catalog, the directory tree on the left displays the corresponding datasets.

In the directory tree on the left, click to open an added dataset, such as My MaxCompute Tables.
Right-click the name of the table that you want to analyze and select Generate SQL Statement. This action generates a temporary file that contains a recommended SQL statement for the table.
Modify the SQL statement as needed and click Save to save the temporary file to My Files.

Query data based on a data source

In the directory tree on the left, hover the pointer over My Files and click the icon 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 the SQL statement, DataWorks automatically completes the names of MaxCompute tables for which you have permissions.

Query data based on a shared SQL file

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

Query data based on a public dataset

After you add a public dataset to the catalog, click the dataset. On the details page that appears, you can select an engine from the top bar and click Generate SQL Statement. You can use public datasets for testing.

Step 3: Configure engine and run query

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

Parameter	Description
Workspace	The workspace where the query engine is located. Important Make sure that you have permissions to access the workspace. If you do not have the required permissions, contact a workspace administrator to add you as a workspace member.
Datasource Type	The type and name of the query engine. Important If no project is specified in the SQL statement, the query engine is the default data source.
Data Source Name
Access Identity Mode	Select an access mode for the SQL query: Executor Identity: This mode supports only MaxCompute and Hologres engines. This mode is recommended if 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 not the same as the default access identity that is set when the data source was created, grant this identity to your current account.

After writing the SQL statement, you can run the full statement or run a selected part. Click the drop-down icon next to the Run button to switch the run mode based on your scenario.

Before you run a MaxCompute SQL statement, the system displays an estimated cost. You can also click More > Estimate Costs in the toolbar above the SQL file to obtain an estimate.

Run mode	Use case	Trigger condition	Applicable engines
Query mode (LIMIT 10000)	Quickly preview data and validate query logic. This mode is suitable for initial data exploration where you only need to view a small sample of results.	The query returns for display a maximum of 10,000 rows and 10 MB of data.	All engines
Query mode (full data)	Retrieve the complete result set for analysis or export. Use this mode when you need to process the entire dataset.	The query returns for display more than 10,000 rows or 10 MB of data.	`MaxCompute`, `Hologres`, `StarRocks`, `ClickHouse`, and `MySQL`
Temporary table mode	Reuse results in complex, multi-step queries. Use the output of one query as the input for the next to improve development and debugging efficiency.	The query returns for display a maximum of 10,000 rows and 10 MB of data, and automatically writes the results to a temporary table.	Only `MaxCompute`

After the SQL query runs, you can view the run log, run result, and the corresponding SQL code on the query result page.
You can click the button in the upper-right corner of the query results to switch between a side-by-side layout and a top-and-bottom layout.

Step 4: Visualize query results

In the toolbar on the left of the query results, click a button to automatically generate a chart from the results. You can click the Edit button in the upper-right corner to create a chart. You can save the chart as a card to perform enhanced analysis.

Note

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

Step 5: Export and share results

Important

If you need to export data to a local file and then import it into another data source, we recommend that you use an offline sync task in Data Integration. This provides more efficient and stable data migration and synchronization.

You can export SQL query results in the following ways:

Local file: Download query results to a local device in CSV, TXT, or XLS format. Key information includes the following:

Item	Description
Download limits	Only the MaxCompute and EMR engines are supported. For more information, see Limits on the number of data rows that can be downloaded. You cannot download data from Data Analysis if the project's data protection mechanism is enabled to prohibit downloads. Important If this option is not available, see What do I do if the query results or download options are limited? 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 on the current page, up to a default maximum of `10,000` records. All Data: Exports all queried result data within the download limit.
Download method	Supports Download After Approval and Download Without Approval. Download with Approval: You can configure a fraud detection rule 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 fraud detection rules. Download without Approval: This is the default method. No permission request is required during the download process.

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

The first time that you use this feature, you must authorize DataWorks to access your OSS resources. In the File Path drop-down list, click the One-click authorization link in the prompt message and follow the on-screen instructions to complete RAM authorization.

Parameter	Description
File path	Click the folder icon on the right to select the OSS bucket and directory where you want to store the result file.
File name	The system automatically generates a file name. You can also modify it.
File format	Select the export file format. The `csv`, `text`, `orc`, and `parquet` formats are supported.
Delimiter	The delimiter that is used to separate columns. The default delimiter is a comma (`,`).
Encoding	The encoding format of the file, such as `UTF-8` or `GBK`.
CU	The number of CUs (computing units) for the export task. The default value is 1 CU.
Resource group	The serverless resource group used to run the export task. If you do not select a resource group, the system uses the data integration resource group that is configured in Data Analysis>System Management by default.

After completing the configuration, click OK to start the export task. You can view the export progress, run 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 device.

MaxCompute table: You can directly export the query results to a MaxCompute table without downloading and then re-uploading the data. You can set a lifecycle for the table.
This option is available only when you query a MaxCompute engine.
Workbook: You can save the results to a Workbook for further data analysis. You can also share the latest analysis results from the Workbook with other users.
DingTalk Sheet: You can export the results to a DingTalk Sheet document.

More operations

Manage SQL file versions

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

Code search

Above the directory tree on the left, click and enter a keyword to search for code. This feature is available only in DataWorks Standard Edition and later.

View the run history

Above the directory tree on the left, click to view the run history of SQL queries.

FAQ

Authorize the 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 permission control page.
Switch the workspace, find the target data source, and click the Authorize button on the right to grant permissions.

SQL query failures

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

Limited query results or downloads

Data Analysis can only display some of the SQL query results. Perform the following steps to maximize the display limit. You can also manage download capabilities. For more information, see Data query and analysis control.

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 permission control page.
Switch to the Query Result Control tab and adjust the Max records per display, Max records per copy, Max records per download, and Allow Download settings.