Create a query API via direct connection

Limitations

• When you call an API, if the data source supports pagination and the operation type is LIST, you can use the PageStart and PageSize parameters to paginate the results, whether or not the paged query feature is enabled.

• When you create an API in advanced SQL mode and use a LIMIT clause in the SQL script to restrict the number of returned rows, the OrderByList parameter in the API call only sorts the data returned by the LIMIT clause. Therefore, LIMIT has a higher priority than OrderByList. For example, you can sort the top 10 records by phone_no and then re-sort them by paper_no.

  SELECT * FROM (
    select paper_no,phone_no,vip_no from aaaa order by phone_no limit 1,10
    ) T0 -- This is the SQL script in the API.
  ORDER BY paper_no ASC -- This is the execution statement when OrderByList is added during the call.

• If you use a LIMIT clause in a basic SQL script, the paged query feature is not supported.

• For a list of data sources that support creating APIs in direct connection mode, row-level permission, and paged query, see Data sources supported by Data Service.

Permissions

Project administrators and developers with the required service permissions can create APIs.

Asynchronous call workflow

The following diagram illustrates the asynchronous data query lifecycle: getting a job ID, checking the job status, retrieving the result, and closing the query job.

• Asynchronous query workflow:

  • GetJobStatus: Gets the execution status of the API call.

  • GetJobResult: Returns the request result. This operation succeeds only if the job status is Success. The query result can only be retrieved sequentially.

  • GetJobExecutionLog: Gets the execution log of the API.

  • CloseJob: Completes the request and releases all resources occupied by the job, including database connections and caches. To release resources, call this operation even if the job status is Failed.

• Asynchronous query cancellation workflow:

  CancelJob: Cancels a query request. If a database query is running, it is canceled synchronously. If the database query has not started or has already completed, it cannot be canceled.

Step 1: Choose API creation method

1. In the top menu bar of the Dataphin homepage, choose Service > API Development.

2. In the upper-left corner, select your project. In the left-side navigation pane, click API Services. On the API page, click + New API.

3. In the New API dialog box, select Direct connection mode.

Step 2: Configure API parameters

1. On the New API page, configure the basic information and parameters for the API.

   Basic API information

   Parameter	Description
   API name	Enter a name for the API. The name must meet the following requirements: It can contain Chinese characters, letters, digits, and underscores (_). It must be 4 to 100 characters in length. It must start with a Chinese character or a letter. It must be globally unique.
   Operation type	GET: Requests a specific resource from the server. LIST: Requests a collection of resources from the server.
   Data update frequency	Defines how often the API data is updated, informing consumers about data timeliness. Supported frequencies are Daily, Hourly, Per Minute, and Custom. If you choose Custom, you can enter a description of up to 128 characters.
   API group	Select a group for the API. To create a new group, see Create a service group.
   Description	Enter a brief description for the API. It can be up to 128 characters in length.
   Protocol	The protocol for the API. HTTP and HTTPS are supported. HTTP: The HyperText Transfer Protocol (HTTP) is the most widely used network protocol. HTTPS: You can select HTTPS if your gateway is configured as an Alibaba Cloud API Gateway instance (dedicated or shared). Ensure that the SSL certificate for your independent domain name is valid to avoid call failures. You can configure the SSL certificate by choosing Platform Management Network Configuration and navigating to the Network Configuration page.
   Invocation mode	The communication mode between client and server. Synchronous call (default) and asynchronous call are supported. Synchronous call: After a client sends a request, it waits for the server's response before sending another request. For complex queries, this can lead to long response times and increased server load. Suitable for scenarios that require high real-time performance and short processing times. Asynchronous call: After a client sends a request, it can perform other operations without waiting for a response. The server notifies the client after processing is complete, reducing duplicate database query results when fetching data in batches. Suitable for long-running processes that do not require high real-time performance, such as batch processing.
   Execution timeout	Available when the invocation mode is asynchronous call. Specifies the maximum allowed SQL execution time. The default value is 60 seconds. You can set it to any integer from 1 to 7,200 (2 hours).
   Timeout	The maximum duration for an API call. For a synchronous call, the default value is 3 seconds, and the valid range is 3 to 60 seconds. For an asynchronous call, the default value is 600 seconds, and the valid range is 3 to 7,200 seconds (2 hours). If an API call exceeds the specified timeout, an error is returned. This helps you identify and handle exceptions. For more information about viewing exceptions, see Manage O&M and monitoring APIs.
   Max response entries	Available when the operation type is LIST. Defines the maximum number of entries an API can return, up to 10,000. You can set it to any integer from 1 to 10,000.
   Cache settings	Available when the invocation mode is synchronous call. You can Enable or Disable caching. If enabled, you must configure the Cache Duration. The default value is 300 seconds. You can set it to any integer from 60 to 1,000,000 seconds (approximately 277.78 hours).
   Version	Enter a version for the API. Each configuration has a version for comparison with previous ones. The version must be unique for this API and meet the following requirements: It can be up to 64 characters in length. It can contain uppercase and lowercase letters, digits, underscores (_), periods (.), and hyphens (-).
   Response type	The default is JSON.
   Return SQL	Controls whether the API response includes the actual executed SQL statement. Yes (Enabled): The API response includes the physical SQL statement executed by the database. No (Disabled): The API response includes the original SQL script.

   API request and return parameter configuration

   To configure the request parameters and return parameters, first identify the source table for the input and output parameters, write the API SQL script, parse the parameters, and then configure their basic information.

   1. In the API parameter configuration section, identify the source table for your input and output parameters, and write the API SQL script based on the Reference samples.

      

      Parameter	Description
      Mode	Select the environment for the data source. Basic and Dev-Prod are supported. In Basic mode, all development, submission, and publishing operations read from the production database. In Dev-Prod mode, development and submission operations read from the development database, while publishing operations read from the production database.
      Data source	Select a data source based on its type. For a list of data sources that support the asynchronous call mode, see Data sources supported by Data Service. Note MySQL 5.1.43, 5.6, 5.7, and 8.0 are supported.
      Query Acceleration	This parameter is available when the data source is MaxCompute. If enabled, MaxCompute Query Acceleration (MCQA) speeds up queries, reducing execution time to seconds. Acceleration may fail because each MCQA tenant has job and concurrency limits. For information about how to resolve execution errors, see .
      SQL mode	You can select basic SQL or advanced SQL. Basic SQL: Write query logic using basic SQL syntax. For example logic, see Reference Samples. Advanced SQL: Write query logic using SQL syntax that supports MyBatis tags. Currently, the supported tags include if, choose, when, otherwise, trim, foreach, and where. For example logic, see Reference Samples.
      Result pagination	Available when the invocation mode is synchronous call and the operation type is List. If enabled, you must specify a sort field to ensure stable query results and prevent duplicate or missing entries in paginated results. If disabled, the pagination parameters (PageStart and PageSize) are not displayed on the API debugging or testing page. You can clear the Hide Parameters checkbox to display them.
      Sort priority	If SQL mode is set to basic SQL, you can choose the sort priority. Options include SQL Script and OrderByList Request Parameter. SQL Script: If the SQL script specifies a sort order, the OrderByList request parameter is ignored. OrderByList Request Parameter: When you test or debug the API, both the sort order defined in the SQL script and the OrderByList common request parameter take effect. The OrderByList parameter has a higher priority than the sort setting defined in the API. If SQL mode is set to advanced SQL, both the sort order defined in the SQL script and the OrderByList common request parameter take effect when you test or debug the API. The OrderByList parameter has a higher priority than the sort setting defined in the API. Note You cannot configure sort priority for the following data sources: TDengine, and SAP HANA.
      API SQL script editing	Your script must follow SQL editing standards. For more information, see API SQL script editing guide.
      Reference samples	The following are SQL script templates that Dataphin can use to parse return and request parameters: Get/List basic SQL samples: -- Example 1: Query a single record based on a condition. If the 'id' parameter is not required and not passed, the condition is automatically ignored. SELECT id,name FROM tablename WHERE id = ${id} -- Example 2: Perform a batch query with an IN condition. The id_list parameter is comma-separated. SELECT id,name FROM tablename WHERE id in (${id_list}) -- Example 3: Use LIKE for fuzzy matching and use semantic aliases for aggregate functions. SELECT MAX(a) AS max_a, SUM(a) AS sum_a, MIN(a) AS min_a, COUNT(*) AS count_all FROM tableName WHERE name LIKE ${name_pattern} -- Example 4: Query with a table alias. SELECT t.name as name FROM tablename t WHERE id=${id_card} -- Example 5: Perform a calculation with an expression and query with multiple conditions. SELECT (a+b) as sum_ab, (b+c) as sum_bc FROM tablename WHERE id=${id_card} and b>=${num} and c<=${num1} -- Example 6: Group and count with a CASE statement. SELECT category, SUM(CASE WHEN name LIKE ${name_pattern} THEN 1 ELSE 0 END) AS proj_score FROM table WHERE id=${id} GROUP BY category Get/List advanced SQL samples: -- Supported MyBatis tags include: if, choose, when, otherwise, trim, foreach, and where. -- SQL parameters within tags can be identified with a $ or # symbol. See the following examples. -- Example 1: Use <where> and <if> to filter by conditions. SELECT id, name, age FROM tableName <where> <if test="name != null and name != ''"> AND age &gt; #{age} </if> <if test="name == null"> AND age &lt; #{age} </if> </where> -- Example 2: Use <choose> for mutually exclusive conditions. SELECT id, name, age FROM tableName <where> <choose> <when test="name != null and name != ''"> AND age &gt; #{age} </when> <when test="age != null"> AND age &lt; #{maxAge} </when> <otherwise> AND status = 'active' </otherwise> </choose> </where> -- Example 3: Use <foreach> for an IN query. SELECT id, name FROM tableName <where> id IN <foreach item="item" index="index" collection="idList" open="(" separator="," close=")"> #{item} </foreach> </where> -- Example 4: Use <trim> for custom prefixes (as a replacement for <where>). SELECT id, name, age FROM ${tableName} <trim prefix="WHERE" prefixOverrides="AND | OR "> <if test="name != null"> AND name LIKE #{namePattern} </if> <if test="minAge != null"> AND age &gt;= #{minAge} </if> <if test="status != null"> AND status = #{status} </if> </trim> -- Example 5: Query with dynamic fields (var_cols). SELECT category,${var_cols_metrics} FROM tableName WHERE id = ${id} GROUP BY category
      Format	Formats the SQL statement for display. Only basic SQL is supported.
      Field reference	The Field Reference panel displays all fields in the selected data table. Copy: You can copy the table name, all table fields, or a single field. Quick Insert: Click Quick Insert to insert a SQL script statement based on the operation type. For more information about the script statements, see Quickly import a SQL script for an API. An anomalous field is marked with a warning icon . Verify that the service unit to which the field belongs has been published to the production environment and still exists.

   2. Click Parse Parameters. Dataphin automatically parses the input and output parameters from the API SQL script and adds them to the request parameters and return parameters sections. If the SQL mode is advanced SQL, you can select Keep Manual Configurations. When you modify the SQL script and parse parameters again, the system retains the parameter information you have already entered. You must manually delete any unnecessary parameter information. This is useful for complex SQL statements where parameters must be configured manually.

      Note

      • If the SQL mode is Advanced SQL, parameters that start with var_cols are dynamic parameters. You can use these parameters to dynamically specify the fields that an SQL statement returns. You can add all supported fields to the return parameters. When you call the API, pass the fields that you want to query in the dynamic parameters. If you do not pass any fields, the values for these fields in the return parameters are null.

      • If the SQL mode is basic SQL, and an optional parameter is not provided, the system automatically rewrites the SQL to ignore the corresponding filter condition. If the parameter value type is between, the parameter is required.

      • Advanced SQL statements can be complex, and the parameters parsed by the SQL compiler may not be complete or correct. You can add or delete request and return parameters based on the SQL statement.

      Parameter		Description
      Request parameters	Parameter name	Required. The public name of the parameter. This is parsed from the SQL and cannot be modified.
      	Parameter type	Required. Select the data type of the field bound to the parameter. Supported types include DOUBLE, FLOAT, STRING, DATE(yyyy-MM-dd HH:mm:ss), BOOLEAN, INT, LONG, SHORT, BYTE, BIGDECIMAL, and BINARY. If the source field's data type is not in this list, we recommend that you select STRING.
      	Parameter value type	Required. Select the value type of the parameter. Single Value and Multiple Values are supported. Single value: For this parameter type, the input parameter is parsed as a single value, and the applicable operators are =, like, >=, <=, >, <, !=, between. Multi-value: The input parameter is parsed into multiple values separated by a comma (,), and the applicable operator is in.
      	Parameter processing	Configure this when the operator is LIKE. If you do not select a processing option, you must manually include wildcards in the input value. Supported options include Fuzzy Match (%keyword%), Right Match (%keyword), and Left Match (keyword%).
      	Example	An example value to help developers understand usage. Maximum length: 1,000 characters.
      	Description	A brief description of the parameter. Maximum length: 1,000 characters.
      	Required	Specifies whether the request parameter is required when calling the API. No: The API call can be executed even if the parameter is not included in the statement. Yes: The API call fails if this parameter is not included. For example, if the request parameter is id (required) and the return parameter is name, executing the following statements will produce different results: Returns the corresponding data from the 'name' field: select name from tableA where id=5;. The SQL statement fails to execute: select name from tableA;.
      	Actions	You can batch modify the Parameter Type, Parameter Value Type, and Parameter Processing (only for the LIKE operator) of request parameters. You can also batch delete parameters (only in advanced SQL mode). When advanced SQL mode is selected, you can click Add Request Parameter to add parameters manually.
      Return parameters	Parameter name	Required. The public name of the parameter. This is parsed from the SQL and cannot be modified.
      	Parameter type	Required. Select the data type of the field bound to the parameter. Supported types include DOUBLE, FLOAT, STRING, DATE(yyyy-MM-dd HH:mm:ss), BOOLEAN, INT, LONG, SHORT, BYTE, BIGDECIMAL, and BINARY. If the source field's data type is not in this list, we recommend that you select String.
      	Example	An example value to help developers understand usage. Maximum length: 1,000 characters.
      	Description	A brief description of the parameter. Maximum length: 1,000 characters.
      	Actions	You can batch modify the Parameter Type of return parameters and batch delete parameters (only in advanced SQL mode). When advanced SQL mode is selected, you can click the Add Return Parameter button to add parameters manually.

   3. Click SQL Trial Run. In the Request Parameter Input dialog box, select the Parameter Type, Parameter Value Type, Parameter Processing, and enter a Trial Run Input Value, then click Confirm.

      • Run Log: View the actual SQL statement executed during the trial run.

      • Trial Run Input Value: You must provide a value for the bound field. You can find field values in the Data Preview panel.

      • Batch Operations: You can batch modify the Parameter Type, Parameter Value Type, and Parameter Processing (only for the LIKE operator) of request parameters. You can also batch delete parameters (only in advanced SQL mode).

   4. Click Populate Parameter Sample Values. The system populates the request and return parameters with the sample values from the last successful trial run. If a sample value already exists, it is not overwritten. You can modify the values.

   5. This option is available only when the SQL mode is advanced SQL and a successful trial run has been completed. Click Populate Return Parameters/Import from Trial Run Result. In the Populate Parameters dialog box, configure the addition method and how to handle parameters with the same name.

      • Addition Method: The strategy for adding imported parameters. You can choose to append new parameters or replace all existing parameters.

        • Append New Parameters: Keeps existing parameters in the return parameter list and appends new, uniquely named parameters parsed from the trial run result.

        • Replace All Existing Parameters: Replaces all parameters in the return parameter list with the parameters parsed from the trial run result.

      • Handling of Parameters with the Same Name: This setting is available when you choose to append new parameters. It defines the strategy for handling duplicate parameter names.

        • Keep Unchanged: Retains the original parameter information without any changes.

        • Replace: If a parameter from the trial run result has the same name as an existing parameter, the existing parameter's type and sample value are updated with the information from the trial run. If the trial run result's value is empty, no replacement occurs.

      If you select Synchronously Populate Sample Values for Request Parameters, the sample values in the request parameter list are replaced according to the parameter population settings.

   6. This option is available only when the SQL mode is basic SQL. Click Parse Parameters, and the system automatically parses the row-level permission information associated with the data source table. This includes the row-level permission name, description, control field, data source environment, associated table, and associated field. You can also perform the following actions:

      • Enable or disable row-level permission: Controls whether the row-level permission is active and whether the row-level permission list is visible when viewing, comparing, debugging, or testing the API.

      • Create Row-level Permission: This action requires permission to create row-level permissions. Clicking this navigates you to the Management Center > Permission Management page to create a row-level permission.

        Note

        • You must purchase the row-level permission value-added service to use this feature.

        • The returned data varies depending on the user's permissions.

        • When the mode is Basic, the row-level permissions for data source tables associated with the production environment are displayed. When the mode is Dev-Prod, the row-level permissions for tables in both the development and production environments are displayed.

2. Click Submit. The system validates that the fields referenced by the API exist in the specified data source. If validation is successful, the API is created.