Query and download data

Limits

Query acceleration is not supported for ad hoc queries if you have not purchased the query acceleration feature.

Create an ad hoc query

1. On the Dataphin homepage, in the top menu bar, choose Develop > Data Development.

2. In the top menu bar, select a Project. If you are using Dev-Prod mode, also select an Environment.

3. In the navigation pane on the left, choose Ad-hoc Query. In the ad hoc query list, click the icon to create a Compute Engine Ad-hoc Query or a Database SQL Ad-hoc Query.

4. In the Create Ad-hoc Query dialog box, configure the following parameters.

   Parameter	Description
   Name	Enter a name for the ad hoc query. The name can be up to 256 characters long and cannot contain vertical bars (|), forward slashes (/), backslashes (\), colons (:), question marks (?), angle brackets (<>), asterisks (*), or double quotation marks (").
   Description	Enter a brief description of the ad hoc query.
   Select Directory	Select the directory where the job is stored. The default directory is Temporary Code. If no directory is created, create a new folder as follows: Above the list of computing jobs on the left side of the page, click the icon to open the Create Folder dialog box. In the New Folder dialog box, enter a Name for the folder and select a location in the Select Directory field as needed. Click OK.
   Data source type	Select the data source type for the SQL job. For more information about the supported data source types, see the Offline Development - Database SQL column in Data sources supported by Dataphin. Note This parameter is available only when you create a Database SQL Ad-hoc Query.
   Datasource	Select the data source for the ad hoc query. If no data source is available, click + New Data Source to create one. Note This parameter is available only when you create a Database SQL Ad-hoc Query.
   Catalog	If the data source type is Presto or Trino, you must also configure the Catalog after you configure the data source.
   Database/Schema	If the data source type is MySQL, PostgreSQL, AnalyticDB for PostgreSQL, Oracle, Presto, GaussDB (DWS), Microsoft SQL Server, ClickHouse, Hologres, Doris, openGauss, StarRocks, DM, OceanBase (Oracle Tenant Mode), SelectDB, Trino, or PolarDB-X 2.0, you must also configure the Schema after you configure the data source.

5. Click OK to create the ad hoc query file.

6. (Optional) Configure and enable query acceleration, and then select an acceleration method.

   MCQA: This method uses MaxCompute Query Acceleration (MCQA). Each tenant has limits on the number of jobs and concurrency for MCQA. These limits may cause acceleration to fail or execution errors to occur. For more information, see Query Acceleration (MCQA). You can disable MCQA acceleration in Management Center > System Settings > Development Platform > Query Acceleration.

   When the current tenant uses a MaxCompute compute engine:

   • If you have not purchased query acceleration but have enabled query acceleration on the development platform, the acceleration method is MCQA.

   • If you have purchased query acceleration, enabled query acceleration on the development platform, and the compute engine for the current project is not attached to an acceleration source, the acceleration method is MCQA.

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

7. Write the query code based on the project's compute engine, database SQL type, .

8. After you write the code, click Run at the top of the editor.

9. After the query statement is successfully executed, view the results in the Console.

Download the result data

1. After the query statement is successfully executed, click the icon in the upper-right corner of the console to download the results.

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

   • Download Data Range: You can download the Full Result Set or a Limited Result Set.

     • Full data download: Downloading the full dataset may take a long time. You can monitor the download progress in the Message Center or by viewing the run logs.

       Important

       A full data download creates a temporary table based on the query statement.

     • Sample data download: By default, all query statements return only a subset of the data. You can configure the number of records to return in Management Center > Standard Settings > Data Download. For more information, see Configure data download.

   • Download Data Format: If you select Sample Data as the download range, you can select CSV or Excel as the download format. If you select Full Data, you can only select CSV.

3. Click OK to start the download.

   If you select Full Data as the download range, click OK to start data preparation. After the data is prepared, click OK again to start the full data download.