Dataphin:Create a query API in direct connection mode

Limits

• The row-level permissions feature is available only after you purchase it.

• When you call an API, if the data source supports paging and the operation type is List, you can use the PageStart and PageSize parameters for paging. This applies even if the paged query feature is disabled.

• When you create an API in Advanced SQL mode, if you use LIMIT in the SQL script to restrict the number of query results, the OrderByList parameter in the API call sorts only the data returned by LIMIT. This means LIMIT has a higher priority than OrderByList. For example, sort the top 10 records by phone_no and then sort the results 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 statement executed when OrderByList is added during a call.

• When you create an API in Basic SQL mode, if you use LIMIT in the SQL script to restrict the number of query results, paged query is not supported.

• For a list of data sources that support creating APIs in direct connection mode, associating row-level permissions, and using paged query, see Data sources supported by DataService Studio.

Permissions

Project administrators and developers can create APIs.

Asynchronous invocation flow

The following flowchart shows the lifecycle of an asynchronous data query. This includes retrieving the job ID, status, and result, and closing the query job.

• Asynchronous query flow:

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

  • GetJobResult: Returns the request result. This operation can be called successfully only when the job status is Success. Otherwise, the call fails. You can only retrieve query results in sequence.

  • GetJobExecutionLog: Retrieves the execution log of the API.

  • CloseJob: Completes the request and releases all resources that are occupied by the job, such as database connections and cache. You must call this operation to release resources, even if the job status is Failed.

• Asynchronous query cancellation flow:

  CancelJob: Cancels the query request. If a database query request is running, it is also canceled. This operation cannot cancel a query that has not started or has already completed.

Step 1: Select a method to create the API

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

2. In the upper-left corner, select a project. In the navigation pane on the left, click API Service. On the API page, click + Create API.

3. In the Create API dialog box, select Direct Connection - SQL Mode.

Step 2: Configure API parameters

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

   API basic information

   Parameter	Description
   API Name	Enter a name for the API. The name must follow these rules: Can contain Chinese characters, letters, digits, and underscores (_). Must be 4 to 100 characters in length. Must start with a Chinese or English character. Must be globally unique.
   Operation Type	GET: Requests a specific resource from the server. LIST: Requests a part of a resource from the server.
   Data Update Frequency	Defines the update frequency of the data returned by the API. This helps callers understand the timeliness of the data. Supported frequencies are Daily, Hourly, Minutely, and Custom. If you select Custom, you can enter up to 128 characters.
   API Group	Select the group to which the API belongs. To create a group, see Create a service group.
   Description	Enter a brief description of the API. The description can be up to 128 characters long.
   Protocol	The protocol for the API. HTTP and HTTPS are supported. HTTP: Hypertext Transfer Protocol (HTTP) is the most widely used network protocol. HTTPS: If the gateway is an Alibaba Cloud API Gateway (dedicated or shared instance), you can select the HTTPS protocol. Make sure the SSL certificate for the independent domain is valid to avoid call failures. Choose Platform Management Network Configuration and configure the SSL certificate on the Network Configuration page.
   Invocation Mode	Used for communication between the client and the server to retrieve or process data. You can select synchronous call or asynchronous invocation. The default is synchronous call. Synchronous Call: After a client sends a request, it must wait for the server's response before it can send other requests. For complex queries, the response time can be long, and waiting occupies server connections, which puts pressure on the server. This mode is suitable for scenarios that require high real-time performance and short processing times. Asynchronous Invocation: After a client sends a request, it can continue to send other requests without waiting for the server's response. The server notifies the client after processing is complete. When retrieving data in batches, this can reduce the duplication of database query results. Use DataService Studio APIs for data retrieval. This mode is suitable for scenarios with long processing times and low real-time requirements, such as batch processing.
   Execution Timeout	This parameter is available when the invocation mode is asynchronous invocation. It is used to monitor the SQL execution duration. The default value is 60 seconds. You can set it to an integer from 1 to 7200 (2 hours).
   Timeout	Used to monitor the maximum duration of an API call. When the invocation mode is synchronous call, the default value is 3 seconds. You can set it to an integer from 3 to 60. When the invocation mode is asynchronous invocation, the default value is 600 seconds. You can set it to an integer from 3 to 7200 (2 hours). If an API call exceeds the specified timeout period, an error is reported. This helps you promptly identify and handle exceptions in API calls. For more information about viewing exceptions, see View and manage service monitoring APIs.
   Max Return Count	This parameter is available when the operation type is LIST. The maximum number of records that an API can return is 10,000. You can enter an integer from 1 to 10,000.
   Cache Settings	This parameter is available when the invocation mode is synchronous call. You can Enable or Disable it. If you enable it, you must configure Cache Timeout. The default value is 300 seconds. You can set it to an integer from 60 to 1,000,000 (about 277.78 hours).
   Version Number	Enter a version number for the API. Each configuration has a version number for comparison with the previous version. The version number must be unique for this API. The naming rules are as follows: Up to 64 characters. Can contain uppercase and lowercase letters, digits, underscores (_), periods (.), and hyphens (-).
   Return Type	The default is JSON.

   API request and response parameters

   When you configure the API Request Parameters and Response Parameters, you must first determine the source tables for the input and output parameters. Then, you can write the API SQL statement, parse the request and response parameters, and configure their basic information.

   1. In the API Parameter Configuration area, determine the source tables for the input and output parameters. You can write the API SQL script based on the Reference Examples.

      

      Parameter	Description
      Mode	Select the data source environment. Basic and Dev-Prod are supported. In Basic mode, the production database is read during development, submission, and publishing. In Dev-Prod mode, the development database is read during development and submission, and the production database is read after publishing.
      Datasource	Select a data source based on its type. For data sources that support asynchronous invocation mode, see Data sources supported by DataService Studio. Note MySQL supports versions 5.1.43, 5.6/5.7, and 8.
      Query Acceleration	This parameter is available when the data source is MaxCompute. If you enable it, MaxCompute MCQA is used to accelerate queries, which can improve query speed and reduce execution time to seconds. Each tenant has limits on the number of jobs and concurrency for MCQA. This may cause acceleration to fail. For information about how to resolve execution errors, see MaxCompute Query Acceleration (MCQA).
      Sql Mode	You can select Basic SQL or Advanced SQL. Basic SQL: Write query logic using basic SQL syntax. For SQL logic examples, see Reference Examples. Advanced SQL: Write query logic using SQL syntax that supports Mybatis tags. The supported tag types are: if, choose, when, otherwise, trim, foreach, and where. For SQL logic examples, see Reference Examples.
      Result Pagination	When the invocation mode is synchronous call and the operation type is List, you can enable result pagination. If you enable it, you must specify a sorting field to ensure stable query results and prevent duplicate or missing results in paged queries. If you disable it, the paging parameters (PageStart and PageSize) are not displayed on the API testing page. You can deselect Hide Parameters to show them.
      Sorting Priority	When Sql Mode is set to Basic SQL, you can select the sorting priority. You can choose between the SQL script or the OrderByList request parameter. SQL Script: If sorting is specified in the SQL script, the OrderByList common request parameter does not take effect. OrderByList Request Parameter: When testing an API, both the sorting defined in the SQL script and the OrderByList common request parameter take effect. The OrderByList common request parameter has a higher priority than the sorting settings defined in the API. When Sql Mode is set to Advanced SQL, both the sorting defined in the SQL script and the OrderByList common request parameter take effect when testing an API. The OrderByList common request parameter has a higher priority than the sorting settings defined in the API. Note Sorting priority cannot be configured when the data source is HBase 0.9.4/1.1.x/2.x, TDengine, or SAP HANA.
      API SQL Script Editor	The API SQL script helps you follow SQL editing standards when writing scripts. For more information, see API SQL script editing instructions.
      Reference Examples	The following are SQL script templates from which Dataphin can parse response and request parameters: Get/List Basic SQL Examples: -- Example 1: Query a single record based on a condition. If id is not required and not passed, the condition is automatically ignored. SELECT id,name FROM tablename WHERE id = ${id} -- Example 2: Batch query with an In condition. The id_list parameter is delimited by commas (,). 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: Expression calculation and multi-condition query. 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: Grouping and CASE statistics. 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 Examples: -- The currently supported Mybatis tag types are: if, choose, when, otherwise, trim, foreach, and where. -- SQL parameters within tags can be identified by the $ or # symbol. The following are examples. -- Example 1: Use <where> + <if> for conditional filtering. 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 a custom prefix (replaces <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: Dynamic field query (var_cols). SELECT category,${var_cols_metrics} FROM tableName WHERE id = ${id} GROUP BY category
      Format	Formats the SQL statement for display. This is only supported for Basic SQL.
      Field Reference	The Field Reference panel displays all fields from the selected data table. Copy: Copy the table name, all table fields, or a single field. Quick Insert: Click Quick Insert. The system inserts an SQL script statement based on the operation type. For more information about the script statement, see Quickly import SQL for API SQL scripts. Abnormal fields are marked with an alert icon. Check whether the service unit to which the field belongs has been published to the production environment or whether the service unit exists.

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

      Note

      • If the SQL mode is Advanced SQL, parameters that start with var_cols are dynamic parameters. You can pass parameters to dynamically specify the fields that are returned by the SQL query. Add all supported fields to the response parameters. When you call the API, pass the fields that you want to query in the dynamic parameter. If you do not pass any fields, the corresponding values in the response are null.

      • If the SQL mode is Basic SQL, when a parameter is not required and no input is provided, the system automatically rewrites the SQL statement to ignore the corresponding filter condition. However, if the parameter value type is `between`, the parameter is required.

      • Because advanced SQL statements can be complex, the parameters that are parsed by the SQL compiler may not be complete or correct. You can manually add or delete request and response parameters as needed.

      Parameter		Description
      Request Parameters	Parameter Name	Required. The externally visible parameter name. The system parses it from the SQL. It cannot be modified.
      	Parameter Type	Required. Select the parameter type for the field bound to the parameter name. You can select DOUBLE, FLOAT, STRING, DATE(yyyy-MM-dd HH:mm:ss), BOOLEAN, INT, LONG, SHORT, BYTE, BIGDECIMAL, or BINARY. If the field type of the logical table is not in the list of available parameter types, we recommend that you select String.
      	Parameter Value Type	Required. Select the type of the parameter value. Single value and multiple values are supported. Single Value: The passed parameter is parsed as a single value. Applicable operators are =, like, >=, <=, >, <, !=, between. Multiple Values: The passed parameter is parsed as multiple values, separated by commas (,). The applicable operator is in.
      	Parameter Processing	This must be configured when the operator is LIKE. If no parameter processing is selected, you must manually enter a wildcard character in the input parameter for matching. You can select Fuzzy Match (%keyword%), Right Match (%keyword), or Left Match (keyword%).
      	Example	Provide examples of request and response parameter values to help developers understand them. You can enter up to 1,000 characters.
      	Description	Enter a brief description of the request and response parameters. You can enter up to 1,000 characters.
      	Required	Select whether the request parameter is required when calling the API. Select No: The API call SQL statement can be executed even if the statement does not contain this parameter. Select Yes: The API call SQL statement cannot be executed if the statement does not contain this parameter. For example, if the request parameter is `id` and it is required, and the response parameter is `name`, executing the following statements will have different results: Returns the corresponding `name` field and data: select name from tableA where id=5;. The SQL statement execution fails: select name from tableA;.
      	Operations	You can perform batch operations on request parameters. This includes modifying the parameter type, parameter value type, and parameter processing (only when the operator is LIKE), and specifying whether they are required. You can also delete parameters in batches (only in Advanced SQL mode). When you select Advanced SQL mode, you can click Add Request Parameter to manually add parameters.
      Response Parameters	Parameter Name	Required. The externally visible parameter name. The system parses it from the SQL. It cannot be modified.
      	Parameter Type	Required. Select the parameter type for the field bound to the parameter name. You can select DOUBLE, FLOAT, STRING, DATE(yyyy-MM-dd HH:mm:ss), BOOLEAN, INT, LONG, SHORT, BYTE, BIGDECIMAL, or BINARY. If the field type of the logical table is not in the list of available parameter types, we recommend that you select String.
      	Example	Provide examples of request and response parameter values to help developers understand them. You can enter up to 1,000 characters.
      	Description	Enter a brief description of the request and response parameters. You can enter up to 1,000 characters.
      	Operations	You can perform batch operations on response parameters. This includes modifying the parameter type and deleting parameters (only in Advanced SQL mode). When you select Advanced SQL mode, you can click the Add Response Parameter button to manually add parameters.

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

      • Run Log: Displays the actual SQL statement that was executed during the SQL test run.

      • Test Input Value: Enter a value for the bound field. You can view the field value in the Data Preview panel.

      • Batch Operations: You can perform batch operations on request parameters. These operations include modifying the parameter type, parameter value type, and parameter processing (only when the operator is LIKE), or deleting parameters (only in Advanced SQL mode).

   4. Click Fill Parameter Example Values. The system populates the request and response parameters with the example values from the last successful test run. If an example value already exists, it is not overwritten. You can still modify the values.

   5. This step is available only when the SQL mode is Advanced SQL and a test run record exists. Click Fill Response Parameters / Import From Test Run Result. In the Fill Parameters dialog box, configure the method for adding parameters and handling duplicate parameter names.

      • Add Method: The policy for adding parameters during import. You can append new parameters or replace all existing parameters.

        • Append New Parameters: Retains existing parameters in the response parameter list and appends the parameters that are parsed from the current test run. New parameters are added based on name uniqueness.

        • Replace All Existing Parameters: Replaces all existing parameters in the response parameter list with the parameters parsed from the test run.

      • Duplicate Parameter Handling: This option is available only when you select Append New Parameters as the add method. It is the policy for handling duplicate parameter names. You can keep the existing parameter or replace it.

        • Keep Existing: Retains the original parameter information without changes.

        • Replace: If a parameter name from the test run result is the same as a parameter name in the list, the existing parameter information is updated with the parameter type and example value that are parsed from the current test run. If the value from the test run result is empty, the existing parameter is not replaced.

      If you select Sync Example Values For Request Parameters, the example values in the request parameter list are replaced based on the fill parameter configuration.

   6. The associated row-level permissions are displayed only when the SQL mode is Basic SQL. Click the Parse Parameters button. The system automatically parses the row-level permission information that is associated with the data source table. This information includes the row-level permission name, description, control field, data source environment, associated table, and foreign key field. You can also perform the following operations:

      • Enable or Disable row-level permissions: Controls whether the row-level permissions are active and whether the row-level permission list is visible when you view, compare versions, or test the API.

      • Go To Create Row-level Permission: To perform this operation, you must have the permission to create row-level permissions. Click this button to go to the row-level permission creation page in Management Center > Permission Management to create row-level permissions.

        Note

        • The data range that is returned by this API is controlled by row-level permissions. Different row-level permissions result in different data results.

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

2. Click Submit. The system checks whether the fields that are referenced by the API exist in the data source. If the check is successful, the API is created.