SQL query (new version)

Supported data sources

SQL query supports the following data source types: MaxCompute, Hologres, EMR, CDH, ADB for PostgreSQL, ADB for MySQL, ClickHouse, StarRocks, MySQL, PostgreSQL, Oracle, SQL Server, Doris, SelectDB, and Lindorm Spark.

Data source permissions

Access the feature

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

• If Switch to New DataAnalysis appears in the navigation bar, click it to switch to the new version.

• If Return to Legacy DataAnalysis appears in the navigation bar, you are in the new version.

Create an SQL query

1. Hover over Personal Directory > My Files, and then click > New SQL File.

   You can also click New Folder to create a custom folder structure for your SQL query files.

2. In the SQL editor, write your SQL query.

   Important

   You can also generate SQL statements in the following ways:

   • On the Data Catalog page, after you add a data catalog, find the target table, right-click the table, and then select Generate Query SQL.

   • Copy an SQL query that is shared with you from the Shared Files folder.

   • After you write the SQL code, click the Format button in the toolbar to format the code.

   • SQL query supports Copilot. You can click the Copilot icon () in the upper-right corner of the navigation bar to use features such as code generation and code correction to help you write code. Copilot also provides in-editor code completion to improve your efficiency.

   Example SQL

   The following SQL statement queries the public GitHub event data in MaxCompute:

   -- Enable session-level schema syntax.
   SET odps.namespace.schema=true; 
   -- Query 100 rows from the dwd_github_events_odps table.
   SELECT * FROM bigdata_public_dataset.github_events.dwd_github_events_odps WHERE ds='${dt}' LIMIT 100;

   Note

   • When you query a MaxCompute project that has schema syntax enabled, you must add SET odps.namespace.schema=true; before the query statement. This enables session-level schema syntax and prevents query failures.

   • By default, the query runs on the data source specified in the current Run Configuration. To query other MaxCompute projects you are a member of, you must explicitly specify the project name in your query statement. For example, if the computing resource in your run settings is MaxCompute A and your query statement specifies MaxCompute B (SELECT * FROM B.schema_name.table_name WHERE ****), the system uses the execution engine of A to query data in B.

3. After writing the SQL query, click Run Configuration on the right to configure settings such as Data Source and Script Parameters.

   • Type: Select the target data source type for the SQL query. If the data source type is MaxCompute, you can configure computing quotas. Pay-as-you-go and subscription billing methods are supported for both batch processing and interactive scenarios. For more information about how to manage MaxCompute quotas, see Manage computing resources (quotas).

   • Computing Resource: Specifies the target data source for the SQL query. When you query only MaxCompute data, direct connections or data source connections are supported. For other types of data sources, you can query only data from data sources in workspaces for which you have permissions. For more information about permissions, see Data source permissions.

   • Script Parameters: If your SQL query uses parameter variables, you can assign values to them at runtime.

     Important

     If parsing fails, go to Management > Settings in the lower-left corner, search for parsing, and then select the Enable DataWorks ISP code parameter function checkbox.

4. In the SQL editor toolbar, click Run. You can also click the drop-down icon next to the Run button to switch the run mode. You can select a run mode based on your scenario.

   Run mode	Use case	Trigger condition	Applicable engine
   Query mode (LIMIT 10000)	Quickly preview data and validate query logic. This mode is suitable for preliminary data exploration where you need to view only a small sample of results.	The query result contains ≤ 10,000 rows and is ≤ 10 MB in size.	All
   Query mode (full data)	Retrieve the complete result set for analysis or export. The system automatically triggers this mode when you need to process and view all data.	The query result contains > 10,000 rows or is > 10 MB in size.	All
   Temporary table mode	Reuse results in multi-step complex queries. You can use the output of one query as input for a subsequent query, which improves development and debugging efficiency.	The query result contains ≤ 10,000 rows and is ≤ 10 MB in size. The result is automatically written to a temporary table.	MaxCompute only

5. After the SQL query runs, you can view the Operational Log, Results, and the corresponding SQL code on the results 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.

View BLOB-type fields

MaxCompute supports BLOB-type fields, which store binary large objects such as images and text. In the query results table, double-click a BLOB field cell to view its content in the Current field value dialog box.

The Current field value dialog box opens in Blob view by default and automatically selects a display format based on the returned data:

• For image formats such as JPEG, PNG, GIF, WebP, BMP, or ICO, the dialog box renders the image directly. To save the image, right-click it and select Save as.

• For text formats, including text/*, application/json, and application/xml, the dialog box displays the content as read-only text.

Use the buttons at the bottom of the dialog box to switch among Blob view, Text view, and JSON view.

Visualize query results

In the toolbar to the left of the query results, click the button to automatically generate a chart from the results.

Export and share

To the right of the SQL query results, click Export. The following export options are supported:

• Local File: Downloads the query results to your local computer as a CSV file. The following table provides details.

  Item	Description
  Download limits	Only the MaxCompute, EMR, and Lindorm Spark engines are supported. For more information, see Data download limits. If the data protection mechanism is enabled for the MaxCompute project, which prohibits data downloads, downloads from DataAnalysis will fail.
  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. By default, a maximum of 10000 rows can be downloaded. All Data: Exports all query results within the export limit.
  Download method	You can select Download After Approval or Download Without Approval. Download with Approval: This method requires an approval request to download data. It helps ensure security by using configurable risk detection rules to identify potential data risks. Note Only DataWorks Enterprise Edition supports risk detection rules. Download without Approval: This is the default method. No approval is required.

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

  The first time you use this feature, you must grant DataWorks permission to access your OSS resources. In the File path drop-down list, click the one-click authorization link in the prompt 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, which you can also modify.
  File format	Select the file format. Supported formats: csv, text, orc, and parquet.
  Delimiter	The delimiter used to separate columns. The default delimiter is a comma (,).
  Encoding format	The encoding format of the file, such as UTF-8 or GBK.
  CU	The number of computing units (CUs) for the export task. Default value: 1.
  Resource group	Select the Serverless resource group to run this export task. If you do not select a resource group, the data integration resource group set in DataAnalysis > System Administration is used.

  After you configure the parameters, click OK to start the export task. On the task running page, you can view the export progress, operational log, and configuration details. After the task succeeds, go to the OSS console to download the exported file to your local computer.

• DingTalk Sheet: Exports the results to a DingTalk sheet. For more information, see Spreadsheet.

• Workbooks and sharing: You can save query data to a workbook to perform further analysis. You can also share the latest analysis results from a workbook with others.

Share files

The new version of DataAnalysis supports file-level sharing for SQL query files, Notebook files, and folders. You can quickly share a file or folder with your collaborators. Shared items appear in the recipient's Shared Files folder, where the recipient has read-only access to view or copy them.

Share from any of the following entry points:

• Share a file from the edit page: Open the target SQL query or Notebook file. In the toolbar at the top of the edit page, click Share.

• Share a file from the file tree: In the left-side file tree, right-click the target SQL query or Notebook file, and then select Share.

• Share a folder from the file tree: In the left-side file tree, right-click the target folder, and then select Share. When you share a folder, all files in the folder are shared together.

In the Share dialog box that appears, configure the following settings, and then click OK:

• Sharing method: Select Workspace or Alibaba Cloud account.

• Sharing scope: Based on the selected sharing method, select the specific workspaces or Alibaba Cloud accounts to share with. Multiple selections are supported.

• Link address: Available only when you share an SQL query or Notebook file. Click Copy Link to copy the share link, and then send it to your collaborators so that they can open the file directly through the link. No link address is provided when you share a folder.