Dataphin:Create a SQL query

Procedure

1. On the Dataphin homepage, choose Analysis > SQL Query from the top menu bar.

2. In the SQL Query list, click the create icon.

3. In the Create SQL Query dialog box, configure the following parameters.

   Parameter	Description
   SQL Query Name	The name can be up to 256 characters long. The following characters are not supported: vertical bar (|), forward slash (/), backslash (\), colon (:), question mark (?), angle brackets (<>), asterisk (*), and double quotation mark (").
   Directory Location	Select the directory where you want to save the query. If a directory does not exist, you can click the icon in the SQL Query list to create one. In the New Folder dialog box, enter a Name, select a Location, and then click OK.
   Query Data	You can query data from either project data or a data source. Project Data: Queries data from the compute source of the current project. Data Source: Queries data from a data source. For a list of supported data source types, see Supported data sources in Dataphin. When you query data from a data source, you must also configure the following parameters: Data Source: Select a data source, including external ones. If no data source is available, click + New Data Source to create one. For more information, see Manage data sources. Catalog: This parameter is required for Presto and Trino data sources. Database/Schema: This parameter is required for the following data sources: MySQL, PostgreSQL, Hologres, AnalyticDB for PostgreSQL, OceanBase (Oracle tenant mode), Lindorm, Oracle, ClickHouse, DM, openGauss, StarRocks, Doris, SelectDB, Presto, GaussDB (DWS), Trino, PolarDB-X 2.0, and OushuDB.

4. Click OK. Dataphin creates the SQL query and opens its code editor tab.

   You can write SQL statements in the code editor tab.

5. (Optional) To use query acceleration, enable it and select an acceleration method.

   • Acceleration Source: Uses a dedicated source to accelerate queries. Specify the acceleration source and a resource group.

     • Acceleration Source: Select an acceleration source bound to the compute source of the current project. If none are available, click Create Acceleration Source to create one.

     • Resource Group (Optional): Select a configured resource group under the selected acceleration source.

     Note

     When using an acceleration source, the query syntax must match the source type. For example, if the acceleration source is StarRocks, the query must use StarRocks syntax.

   • MCQA: MaxCompute Query Acceleration (MCQA). Each tenant has job and concurrency limits for MCQA. Exceeding these limits can cause acceleration failures or execution errors. For more information, see Query Acceleration (MCQA). You can disable MCQA in Management Center > System Settings > R&D Platform > Query Acceleration.

   Supported acceleration methods vary by compute engine and scenario.

   When the current tenant uses the MaxCompute compute engine:

   • If you enable query acceleration in the R&D Platform without purchasing it, the system uses MCQA.

   • The system also uses MCQA if you have purchased and enabled query acceleration, but the project's compute source is not bound to an acceleration source.

   • If you have purchased query acceleration and the compute source for the project is bound to an acceleration source, you can choose either an acceleration source or MCQA.

   When the current tenant uses the Hadoop compute engine: If you have purchased query acceleration and the cluster corresponding to the project's compute source is bound to an acceleration source, that acceleration source is used.

6. After writing your SQL code, click Run. The system parses the code and verifies your permissions on all referenced objects. If the check fails, the Permission Check Failed dialog box lists the objects for which you lack permissions.

   Note

   An SQL query job runs using the Default Resource Group that is configured in the selected Data Source, not the default resource group of the analysis project.

   The Permission Check Failed dialog box displays the object name, object type, permission type, and available actions.

   • Object Name: The object you lack permission for.

   • Object Type: Table or global variable.

   • Permission Type: The specific permission needed. For example, querying a table requires query permission, while using a data source requires execute permission.

   • Actions: Options to apply for the required permissions.

     • To apply for permissions on a single object, click the icon in the Actions column for the target object. Permissions are valid for 30 days by default.

     • To apply for permissions in batch, select multiple objects or select Select All Tables, and then click Apply for Permissions.

     For more information, see Apply for permissions. Once the permission is granted, you can run the query again.

     Note

     Table-level permission control is supported only for MySQL and Oracle data sources.

Single-tenant multi-engine

In single-tenant multi-engine mode, if you select Project Data for Query Data, you must also specify an Execution Project by selecting a cluster and a project. Other parameters are the same as described in the procedure above.

• Cluster: The options include all clusters under the current tenant and the Clusterless Project option. You cannot change the cluster after the SQL query is created.

• Project: The options include projects under the selected cluster and the dedicated analysis project for that cluster. The name of a dedicated analysis project for a cluster is in the format of {Cluster Name} Analysis Project. The name of the dedicated analysis project for a clusterless project is Independent Analysis Project for Clusterless Projects.

After you select an execution project, the system creates an SQL type based on the selected cluster.