DocsICP FilingConsole
Documentation
Home Page Dataphin Intelligent Data construction and governance Dataphin-sharing mode (fully managed version) User Guide Analysis Platform Create SQL queries based on the analysis platform Create a SQL query

Create a SQL query

Updated at:
Copy as MD
Product
My Favorites

Dataphin offers efficient SQL querying and advanced data visualization. This topic explains how to create a SQL query in Dataphin.

Procedure

  1. On the Dataphin homepage, choose Analyze > SQL Query from the top navigation bar.

  2. In the SQL Query list, click the image 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. 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 store the task.

    If a directory does not exist, you can click the image icon on the SQL Query list to create a folder. In the Create Folder dialog box, enter a Name, select a Location, and click OK.

    Query Data

    Select whether to query project data or a data source.

    • Project data: Queries data from the project's compute source.

    • Data source: Queries data from a specified data source. For a list of supported data source types, see Supported data sources. You also need to configure the following parameters:

      • Data source: Select a data source. This can be an external data source. If none are available, click + Create Data Source to add one. For more information, see Data Source Management.

      • Catalog: For Presto and Trino data sources, you must select a Catalog.

      • Database/Schema: For the following data sources, you must select a Database/Schema: 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 new SQL query opens by default.

    You can write and edit your SQL code in the editor tab.

  5. (Optional) Configure and enable query acceleration. If enabled, select an acceleration method.

    Acceleration source : Use an acceleration source to accelerate queries. If you select Acceleration Source, you must also specify an acceleration source and a resource group.

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

    • Resource group (Optional): Select a resource group from the chosen acceleration source.

    Note

    If you select Acceleration Source, the query runs using the SQL syntax of that source. Your SQL statement must follow that syntax. For example, if the acceleration source type is StarRocks, Dataphin runs this SQL query by using StarRocks syntax.

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

    Supported acceleration methods vary by compute engine and scenario.

    When the current tenant uses the MaxCompute compute engine :

    • If you have not purchased data query acceleration but have enabled query acceleration in the R&D platform, the acceleration method is MCQA.

    • If you have purchased data query acceleration, enabled query acceleration in the R&D 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 choose either an acceleration source or MCQA as the acceleration method.

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

  6. After you finish editing the SQL code, click Run. The system parses all objects in the code to verify that the user has the necessary permissions. If the permission check passes, the query runs. If the check fails, a dialog box appears that lists the objects for which the user lacks permissions.

    Note

    By default, a SQL query uses the default resource group configured in the selected data source, not the project's default resource group.

    The permission check failure dialog box displays information about the object name, object type, permission type, and available operations.

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

    • Object Type: Tables and global variables.

    • Permission Type: The permissions required to run the script. For tables, you need permission to query data. For data sources, you need execution permission.

    • Operation: If the object check fails, you can apply for permissions for one or more objects.

      • Apply for permissions for a single object: Click the image.png icon in the Operation column for the target object to request permissions. The permission is valid for 30 days by default.

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

      For detailed instructions, see Apply for permissions. After the permissions are granted, you can run the query again.

      Note

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

Previous：Create SQL queries based on the analysis platformNext：SQL visualization charts