Dataphin:Create an SQL query

Procedure

1. In the Dataphin console, choose Analyze > SQL Query from the top navigation bar.

2. On the SQL Query page, click the icon.

3. In the New 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	Select the directory to store the query. If no directory has been created, click the icon on the SQL Query page to create a new folder. In the New Folder dialog box, enter a Name, select a Location for the folder, and then click OK.
   Query Data	Supports Project Data and Data Source. Project Data: Queries data from the compute source of the current project. Data Source: Queries data from a data source. For supported data source types, see Supported data sources for the analysis platform. If you select this option, you must also configure the following parameters: Data Source: Select a data source, including an external data source. If no data sources are available, you can click + New Data Source to create one. For more information, see Data Source Management. Catalog: For Presto and Trino data sources, you must select a catalog. Database/Schema: For data sources such as 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, OushuDB, TDSQL for MySQL, and TDSQL for PostgreSQL, you must select a corresponding database or schema.

4. Click OK to create the SQL query. The code editor tab for the SQL query opens by default.

   You can write SQL code in the editor.

5. (Optional) You can configure and enable query acceleration. If enabled, you must select an acceleration method.

   Acceleration Source: Accelerates queries by using an acceleration source. If you select this method, you must also select an acceleration source and a resource group.

   • Acceleration Source: You can select an acceleration source that is bound to the compute source of the current project. If no acceleration sources are available, click New Acceleration Source to go to the creation page and create one as needed.

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

   Note

   If you select Acceleration Source as the acceleration method, the job runs by using the syntax of the selected source type (for example, StarRocks syntax for a StarRocks source).

   MCQA: MaxCompute Query Acceleration (MCQA). MCQA has per-tenant limits on jobs and concurrency, which can cause acceleration failures or execution errors. For more information, see Query acceleration (MCQA). You can disable MCQA acceleration in Management Center > System Settings > Development Platform > Query Acceleration.

   Supported acceleration methods vary based on the compute engine and scenario.

   If the current tenant uses the MaxCompute compute engine:

   • If you have not purchased data query acceleration but have enabled Query Acceleration in the Development Platform, the acceleration method is MCQA.

   • If you have purchased data query acceleration, enabled Query Acceleration in the Development Platform, and the compute source for the current project is not bound to an acceleration source, the acceleration method is MCQA.

   • If you have purchased data query acceleration and the compute source for the project is bound to an acceleration source, you can select either Acceleration Source or MCQA as the acceleration method.

   If the current tenant uses the Hadoop compute engine: The acceleration method is Acceleration Source if you have purchased query acceleration and the project's compute source cluster is bound to an acceleration source.

6. After you finish editing the database SQL code, click Run. The system parses the code to verify you have the required permissions for all referenced objects. If the permission check fails, a dialog box appears listing the objects for which you lack permission.

   Note

   An SQL query job uses the default resource group of the selected data source, not the default resource group of the project.

   The permission check failure dialog box displays the object name, object type, permission type, and available actions.

   • Object Name: The name of the object for which you lack permissions.

   • Object Type: Tables and global variables.

   • Permission Type: The type of permission required to run the script. Examples include query permission for tables and execution permission for a data source.

   • Actions: If the permission check fails, you can request permissions for one or more objects.

     • Request permissions for a single object: In the Actions column for the target object, click the icon to request personal permissions for that object. The default validity period is 30 days.

     • Request permissions in bulk: Select multiple objects, or select Select All Tables and then click Request Permissions.

     For more information about how to request permissions, see Request permissions. After the permissions are granted, you can run the query again.

     Note

     Only MySQL and Oracle data sources support table-level permission control.

Multi-engine for a single tenant

In multi-engine mode, if you query Project Data, you must also specify an execution project by selecting a cluster and a project. The other parameters are configured as described in the preceding sections.

• Cluster: The options include all clusters under the current tenant and clusterless projects. The cluster cannot be changed after the query is created.

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

After you select the execution project, the system creates an SQL query of the corresponding engine type based on the selected cluster. The mappings are as follows.