All Products
Search

This Product

    Dataphin:Query and download data

    Document Center

    Dataphin:Query and download data

    Last Updated:Sep 16, 2026

    Ad-hoc queries allow you to create and run custom query statements and download the query results. For example, after you develop a compute task, you can use an ad-hoc query to verify whether the task runs as expected. This topic explains how to create an ad-hoc query and download the results.

    Background information

    Ad-hoc queries use the compute engine configured for your current project. When you use a Hadoop compute engine, you can enable Impala tasks. You can then create both Impala SQL and Hive SQL ad-hoc query tasks. Because Impala performs in-memory computing, Impala SQL ad-hoc queries provide faster response times than Hive SQL ad-hoc queries. Dataphin also lets you quickly switch between Impala SQL and Hive SQL task types, eliminating the need to rewrite code for previous tasks. For more information, see Appendix: Switch task types.

    Prerequisites

    To create an Impala SQL ad-hoc query, you must enable Impala Task for your Hadoop compute engine. For more information, see Create a Hadoop compute engine.

    To download ad-hoc query results, ensure you have the required project permissions and that both full data download and sample data download are enabled. For more information, see Manage project space permissions and compute sources, and Configure data download.

    Limits

    Query acceleration for ad-hoc queries is unavailable unless you have purchased a query acceleration service.

    Create an ad-hoc query

    1. On the top navigation bar of the Dataphin homepage, choose Develop > Data Development.

    2. In the top navigation bar, select a Project. If you are in Dev-Prod mode, you also need to select an Environment.

    3. In the left-side navigation pane, choose Ad-hoc Query. In the ad-hoc query list, click the image icon. You can create an Ad-hoc Query for Compute Engine or an Ad-hoc Query for Database SQL.

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

      Note

      If your compute engine is Hadoop and the Impala task is enabled, you can create a Hive SQL Ad-hoc Query or an Impala SQL Ad-hoc Query.

      Parameter

      Description

      Name

      Enter a name for the ad-hoc query.

      The name must be no more than 256 characters and cannot contain the following special characters: |, /, \, :, ?, <>, *, or ".

      Description

      Enter a brief description for the ad-hoc query.

      Select Directory

      Select a directory to store the task. By default, Temporary Code is selected.

      If no directory exists, you can create a New Folder. To do so, follow these steps:

      1. Above the compute task list on the left, click the image icon to open the New Folder dialog box.

      2. In the New Folder dialog box, enter a folder Name and, if necessary, select a directory.

      3. Click OK.

      Data Source Type

      Select the data source type for the SQL task. For a list of 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 an Ad-hoc Query for Database SQL.

      Data Source

      Select a data source for the ad-hoc query. If no data sources are available, click + New Data Source to create one.

      Note

      This parameter is available only when you create an Ad-hoc Query for Database SQL.

      Catalog

      If the data source type is Presto or Trino, you must also configure the catalog after selecting 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, PolarDB-X 2.0, TDSQL for MySQL, TDSQL for PostgreSQL, TDengine, or GBase 8a, you must also configure the schema after selecting the data source.

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

    6. (Optional) Configure and enable query acceleration. After you enable this feature, you must select an acceleration method.

      MCQA: MaxCompute Query Acceleration (MCQA). MCQA limits the total and concurrent jobs for each tenant, which may 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.

      The supported acceleration methods vary depending on the compute engine and other conditions.

      If the current tenant uses the Hadoop compute engine: If you have purchased a query acceleration service and the cluster for the project's compute engine is bound to an acceleration source, the acceleration method for Hive SQL, Impala SQL, and Spark SQL ad-hoc query tasks is acceleration source.

    7. Write the query code using the syntax that corresponds to 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 runs successfully, view the results in the console.

    Download results

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

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

      • Download Data Scope: You can download either full data or sample data.

        • Full data download: Downloading the full data may take a long time. You can monitor the download progress in the message center or in the run logs.

          Important

          To perform a full data download, the system creates a temporary table based on the query statement. For Spark SQL ad-hoc queries that use a Hadoop compute engine, the temporary table is created using the default storage format specified in Management Center > System Settings > Development Platform > Table Management Settings.

          For example, if the default storage format in Table Management Settings is hudi, the statement using hudi is added when the temporary table is created. If the default storage format is Engine Default, no specific storage format is specified when the temporary table is created.

        • Sample data download: By default, all queries return a portion of the data. You can set the number of records for sample data in Management Center > Standard Settings > data download. For more information, see Configure data download.

      • Download Data Format: You can download sample data in either CSV or Excel format. Full data downloads are only available in CSV format.

    3. Click OK to start the download.

      When downloading full data, clicking OK starts data preparation. After the full data is prepared, click OK again to start the download.

    Note

    • If download approval is enabled in Standard Settings, you must submit a Data Download Request before you can download data. After the request is approved, you can view the approval task, check the current run results, or download data from the run history. For more information, see Download Approval Configuration.

    • If watermarking for download approvals is enabled, downloaded Excel files are automatically watermarked. The downloaded data files are retained for 30 days. For more information, see Configure data download approval.

    Appendix: Switch task types

    This feature is available if your project's compute engine is a Hadoop compute engine with the Impala task enabled. You can switch between Impala SQL and Hive SQL task types.

    1. In the ad-hoc query list, find the Impala SQL or Hive SQL task, click the image icon next to the task name, and select Modify Type.

    2. In the Modify Type dialog box, select the new task type. The following figure shows an example of switching from an Impala SQL task to a Hive SQL task.image.png

    3. Click OK to switch the task type.