All Products
Search

This Product

    Dataphin:API SQL scripts

    Document Center

    Dataphin:API SQL scripts

    Last Updated:Jun 22, 2026

    Learn the SQL scripting rules and limitations for query-type (Get/List) and operation-type (Create/Update/Delete) APIs.

    Get/List (query type)

    • Supported query features:

      • Single-table queries, multi-table joins (JOIN), and nested queries (subqueries) are supported within the same data source.

      • Advanced SQL mode (MyBatis style) is supported.

      • Parameters are supported in SELECT statements. 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 query statements: Multiple SQL statements in a single API are not supported, such as SELECT ...; SELECT ...;.

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

      • SELECT *: SELECT * queries are not allowed. Explicitly specify the columns to query.

      • Column name prefixes: If a column name includes a table prefix (for example, t.name), you must use an alias as the response parameter name. For example: SELECT t.name AS name FROM table.

      • Aggregate functions: Aggregate functions such as SUM(), COUNT(), MAX(), and MIN() require an alias as the response parameter name. For example: SELECT SUM(num) AS total_num FROM table.

      • Do not include pagination statements in the SQL script. Use pagination parameters instead.

        Note

        • If the data source is SAP HANA, use pagination statements in SQL and define pagination parameters to implement pagination.

        • For TDengine data sources, you must use a pagination statement in the SQL script. If pagination parameters such as PageStart and PageSize are not provided, all data is returned. If they are provided, data is paginated according to the specified values.

    • In advanced SQL mode, dynamic field queries let you dynamically specify response fields by using parameters.

      • Parameter format: The parameter name must start with var_cols_ and follow the format var_cols_xxx, such as var_cols_args. For example: SELECT id, ${var_cols_args} FROM table1.

      • Response parameters: All supported dynamic query fields must be declared as response parameters.

      • Calling an API: Pass the fields to query in the dynamic parameters. Fields that are not passed return null in the response. For example: var_cols_args=name,age,dept.

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

    • In advanced SQL mode, comparison operators in MyBatis SQL must be escaped.

      Original operator

      Escaped operator

      <

      &lt;

      >

      &gt;

      <=

      &lt;=

      >=

      &gt;=

    • Data source-specific limitations:

      Data source

      Limitations

      TDengine

      • You must use LIMIT/OFFSET in SQL to implement pagination.

      • Optional parameters are not supported.

      SAP HANA

      • Pagination is not supported. The PageStart and PageSize parameters have no effect.

      • Optional parameters are not supported.

      Elasticsearch

      • Elasticsearch uses ScrollId for deep pagination. Without a ScrollId, only the first 10,000 records can be retrieved using the PageStart and PageSize parameters. Retrieving records beyond this limit causes the API call to fail.

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

      • To query data beyond the first 10,000 records, specify a scrollId in the WHERE clause. Define a scrollId request parameter when developing the API and pass the corresponding scrollId value in your request.

        Note

        When querying with a scrollId, standard pagination is not available. Do not pass values for PageStart and PageSize, or the call will fail.

      • The WHERE clause only supports scrollId as a condition. Other fields in the clause are ignored.

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

      • Subqueries are not supported.

      HBase

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

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

      • The ORDER BY clause is not supported.

      • Pagination is not supported. The PageStart and PageSize parameters have no effect.

      • The JOIN statement is not supported.

      PostgreSQL

      Cross-schema queries are not supported. Only data within the current connection's schema can be queried.

      Impala

      Advanced SQL mode (MyBatis style) is not supported.

      Lindorm

      JOIN statements are not supported.

      DolphinDB

      Native DolphinDB mode is not supported. To use DolphinDB, create a data source and set its SQL compatibility mode to either Oracle or MySQL.

      Important

      Modifying the compatibility mode of a data source may cause unpredictable errors in existing APIs. Adjust this setting based on your business requirements.

      GBase 8a

      The QUALIFY clause is not supported in basic SQL mode.

      OpenSearch

      • Simple subqueries are supported, but multi-level nested IN subqueries may not execute correctly.

        For example: SELECT ... WHERE age IN (SELECT age FROM ... WHERE age IN (18,39));.

      • Running a JDBC query with a subquery that contains JOIN may return an empty result set.

        For example: SELECT ... FROM (SELECT ... FROM t1 LEFT JOIN t2 ON ...) e1 WHERE ...;.

      • The WITH syntax for common table expressions is not supported.

        For example: WITH test_with AS (...) SELECT ...;.

      • UNION and UNION ALL statements are not supported, even if they are included in a subquery.

        For example: SELECT id FROM (SELECT id FROM t1 UNION SELECT id FROM t2) t;.

      • The COALESCE syntax has compatibility issues with the current JDBC driver. Use the CASE WHEN syntax as an alternative.

        Replace SELECT COALESCE(id, 0) FROM ...; with SELECT CASE WHEN id IS NULL THEN 0 ELSE id END FROM ...;.

      • Using a CASE WHEN expression with a JOIN clause may cause field parsing failures or missing columns. Verify syntax compatibility before building complex queries.

        For example: SELECT CASE WHEN a.id= ... END FROM t1 a JOIN t2 b ON ...;.

      Hive

      The query engine for Hive tables depends on the table format: the Spark engine queries data lake tables (such as Iceberg or Hudi), while the Hive engine queries regular tables (such as ORC, Parquet, or TextFile).

    Create/Update/Delete (operation type)

    • Supported SQL modes:

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

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

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

    • Batch operations in advanced SQL mode process records individually, which can degrade performance. Avoid using this mode for batch create, update, or delete APIs whenever possible.

    • If an SQL statement contains an in condition parameter, each record is executed separately, which degrades performance. Avoid using the in condition parameter.

    • Only PostgreSQL supports the RETURNING clause.