All Products
Search

This Product

    Dataphin:Guidelines for editing API SQL scripts

    Document Center

    Dataphin:Guidelines for editing API SQL scripts

    Last Updated:Nov 18, 2025

    This topic describes how to edit scripts for query and operation APIs.

    Get/List (query type)

    • Supported query patterns:

      • Supports single-table queries, table joins, and nested queries (subqueries) within the same data source.

      • Supports advanced SQL mode (MyBatis style).

      • Supports configuring parameters in the SELECT clause. For example: SELECT id_card, SUM(CASE WHEN id_card LIKE ${id_card} THEN 1 ELSE 0 END) AS proj_score FROM table WHERE c LIKE ${id_card} GROUP BY id_card.

    • Unsupported syntax and usage:

      • Multiple SQL statements: A single API cannot execute multiple SQL statements, such as SELECT ...; SELECT ...;.

      • Non-DQL statements: Get and List APIs do not support non-DQL statements such as INSERT, UPDATE, CREATE, or DELETE.

      • SELECT *: Do not use SELECT *. You must specify the columns to query.

      • Column prefixes: If a column name has a table prefix, such as `t.name`, you must use an alias for the return parameter name. For example: SELECT t.name AS name FROM table.

      • Aggregate functions: When you use aggregate functions such as SUM(), COUNT(), MAX(), or MIN(), you must use an alias for the return parameter name. For example: SELECT SUM(num) AS total_num FROM table.

      • Paging statements: Paging statements are not supported in SQL. Instead, use paging parameters to implement paging.

        Note

        • For SAP HANA data sources, you can implement paging using a paging statement in the SQL and defining paging parameters.

        • For TDengine data sources, use a paging statement in the SQL. If you do not pass the `PageStart` and `PageSize` parameters, the full data is returned. If you pass these parameters, data is returned based on the values that you define.

    • A dynamic field query, which is available in advanced SQL mode, lets you dynamically specify return fields by including parameters in the search statement.

      • Parameter format: The parameter must start with the `var_cols_` prefix, such as `var_col_args`. For example: SELECT id, ${var_cols_args} FROM table1.

      • Return parameters: Add all supported dynamic query fields as return parameters.

      • API call: When you call the API, pass the query fields in the dynamic parameter. If a field is not passed, its value is null in the result. For example: var_col_args=name,age,dept.

      • Actual execution: The executed SQL statement is: SELECT id, name, age, dept FROM table1.

    • When you use advanced SQL mode, you must use escape characters for comparison operators in MyBatis SQL.

      Original Symbol

      Escaped operator

      <

      &lt;

      >

      &gt;

      <=

      &lt;=

      >=

      &gt;=

    • Data source-specific limitations:

      Data source

      Limitation

      TDengine

      • Use LIMIT/OFFSET in the SQL to implement paging.

      • Optional parameters are not supported.

      SAP HANA

      • Paging is not supported. Passing PageStart and PageSize parameters has no effect.

      • Optional parameters are not supported.

      Elasticsearch

      • Use ScrollId to implement paged queries. If you do not specify a ScrollId, you can query a maximum of 10,000 data entries. When setting the PageStart and PageSize paging parameters, do not query more than 10,000 entries, or the call will fail.

        For example, if you set PageStart to 9998, the maximum value for PageSize is 2.

      • To query data beyond the first 10,000 entries, use a `where` clause to specify the scrollId. You can specify a scrollId request parameter during API development and pass the corresponding scrollId value in the query.

        Note

        When you query using a specified scrollId value, you cannot use paging. Do not pass values for PageStart and PageSize, or the call will fail.

      • In a `where` clause, only the scrollId field is supported for conditional queries. Other fields have no effect.

        For example: select a from table where scrollId=${scrollId}.

      • Subquery statements are not supported.

      HBase

      • Only single rowkey queries are supported. The rowkey is a required request parameter. The query syntax is:

        select info1.id as name from table where rowkey = ${rowkey}

      • The `Order by` sorting syntax is not supported.

      • Paging is not supported. Passing PageStart and PageSize parameters has no effect.

      • The `JOIN` statement is not supported.

      PostgreSQL

      Cross-schema queries are not supported. Data can only be queried from the schema of the current connection.

      Impala

      Advanced SQL mode (MyBatis style) is not supported.

      Lindorm

      The JOIN statement is not supported.

    Create/Update/Delete (operation type)

    • Supported SQL modes:

      • Basic SQL mode: Supports standard INSERT, UPDATE, and DELETE statements. Use the ${param} placeholder to pass parameters. This mode is suitable for simple scenarios.

      • Advanced SQL mode (MyBatis style): This mode can be used for complex logic such as conditional statements, dynamic fields, and batch operations. The following tags are supported:

        <if>, <choose>, <when>, <otherwise>, <trim>, <foreach>

    • When you use advanced SQL mode for batch processing, each data entry is executed separately, which results in poor performance. Therefore, you should avoid using advanced mode for batch create, update, or delete APIs.

    • If an SQL statement contains an in condition parameter, each data entry is executed separately. This results in poor performance. Therefore, you should avoid using in condition parameters.

    • Only PostgreSQL supports the `RETURNING` syntax.