Dataphin:Create A SQL Query

Procedure

1. On the Dataphin home page, choose Analysis > SQL Query from the top navigation bar.

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

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

   Parameter	Description
   SQL Query Name	The name must be 256 characters or fewer. It cannot contain the following characters: vertical bar (|), forward slash (/), backslash (\), colon (:), question mark (?), angle brackets (<>), asterisk (*), or double quotation mark (").
   Directory	Select a directory to save the query. If a directory does not exist, you can click the icon in the SQL Query list to create a folder. In the New Folder dialog box, enter a Name, select a Location for the folder, and then click OK.
   Query Data	You can query project data or data from a data source. Project Data: Queries data from the project's compute source. Data Source: Queries data from a data source. For a list of supported data source types, see Supported data sources. To query data from a data source, you must also configure the following parameters: Data Source: Select a data source, including external data sources. If no data source is available, 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: You must select a database or schema 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, OushuDB, TDSQL for MySQL, TDSQL for PostgreSQL, TDengine, and GBase 8a.

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

   You can write SQL code in the editor tab.

5. (Optional) Configure and enable query acceleration. If you enable query acceleration, you must also select an acceleration method.

   MCQA: Uses MaxCompute Query Acceleration (MCQA). Job and concurrency limits for each tenant may cause acceleration failures or errors. For more information, see Query acceleration (MCQA) . You can disable MCQA acceleration by navigating to Management Center > System Settings > Development Platform > Query Acceleration.

   Supported acceleration methods vary by compute engine and scenario.

   For tenants using a Hadoop compute engine: If you have a data query acceleration service and the project's cluster is bound to an acceleration source, the acceleration method is Acceleration Source.

6. After writing your SQL code, click Run. The system parses the code and performs a permission check. If the check fails, a dialog box appears, listing the objects for which you need permissions.

   Note

   A running SQL query uses the default resource group of the selected data source, not the default resource group of the analysis project.

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

   • Object Name: The name of the object for which you do not have permissions.

   • Object Type: Tables and global variables.

   • Permission Type: The type of permission required. For example, data source tables require read permission, while data sources require execute permission.

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

     • To request permissions for a single object, click the icon in the Actions column for that object. This action requests personal permissions, which default to a 30-day validity period.

     • To request permissions for multiple objects at once, select them or choose Select All Tables, and then click Request Permissions.

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

     Note

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

Download query results

1. After the query runs successfully, click the Download icon in the upper-right corner of the results pane to download the query results.

2. In the Data Download dialog box, select the Download Scope and Download Format.

   • Download Scope: You can download Full Data or Sample Data.

     • Full Data Download: Downloading the full dataset can be time-consuming. You can check the download progress in the message center or in the run history.

       Important

       A full data download creates a temporary table based on the query statement. For SQL queries that use a Hadoop compute engine, Dataphin uses the default storage format configured in Management Center > System Settings > Development Platform > Table Management Settings.

       For example, if the default storage format is hudi, Dataphin adds the using hudi statement when it creates the temporary table. If the default storage format is Engine Default, Dataphin creates the temporary table without specifying a storage format.

     • Sample Data Download: Downloads the data sample that is returned by default for all queries.

   • Download format: When the download data range is set to Sample data, you can select the CSV or Excel format. When the download data range is set to Full data, you can only select the CSV format.

3. Click OK to start the download.

   If you set Download Scope to Full Data, clicking OK starts data preparation. After preparation is complete, click OK again to start the full data download.

Single-tenant multi-engine

In multi-engine mode, if you set Query Data to Project Data, you must also specify an Execution Project by selecting a cluster and a project. The other parameters are the same as in single-engine mode.

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

• Project: Options include projects under the selected cluster and analysis-only projects. An analysis-only project is named using the format {Cluster Name} Analysis Project. For projects without a cluster, the name is No Cluster Independent Analysis Project.

After you select an execution project, the system creates an SQL query that corresponds to the engine type of the selected cluster. The following table shows the mappings.