Create an API (service unit) in SQL mode

Prerequisites

To generate an API based on a service unit, you must first create a service unit. For more information, see Create a service unit.

Limits

• When calling an API, if the data source supports pagination and the API request method is List, you can use PageStart and PageSize to set pagination regardless of whether pagination is enabled.

• When you use the Limit statement in an SQL script to specify the number of query results to return, the PageStart and PageSize parameters become invalid, and the results will return the number of records specified by the Limit statement.

• When you create an API using basic SQL and use the LIMIT statement in the SQL script to restrict the number of query results to return, pagination is not supported.

Permissions

Project administrators and developers can generate APIs for services.

Precautions

• The request parameters and response parameters of an API must be obtained from the same service unit (single physical table service unit or multiple physical table service unit). Otherwise, the API cannot be called properly.

• Writing SQL statements based on service units supports MySQL syntax but not other SQL languages.

Step 1: Select a method to generate an API

1. In the top navigation 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 Service Unit API-SQL Mode.

Step 2: Configure API parameters

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

   Basic API information configuration

   Parameter	Description
   API Name	Enter a name for the API. The name must meet the following requirements: It can contain only Chinese characters, letters, digits, or underscores (_). The name must be 4 to 100 characters in length. The name starts with a letter. This value must be globally unique.
   Request Method	API request methods include GET and LIST: GET: Requests the server to retrieve a specific resource. LIST: Requests the server to retrieve a portion of resources.
   Data Update Frequency	Define the update frequency of the data returned by the API to help callers understand the timeliness of the data. Supported update frequencies include Daily, Hourly, Every Minute, 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.
   Result Pagination	When the request method is List, you can set result pagination. After enabling this feature, you must specify sorting fields to ensure stable query results and avoid duplicate or missing results in pagination queries. If this feature is disabled, the API debugging or testing page does not display pagination parameters (PageStart and PageSize). You can deselect Hide Parameters to display pagination parameters.
   Description	Enter a brief description of the API. It cannot exceed 128 characters.
   Protocol	The API supports the HTTP and HTTPS protocols. HTTP: The 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. To prevent call failures, make sure the SSL certificate for the independent domain is valid. To configure the SSL certificate, go to the Network Configuration page by choosing Platform Management > Network Configuration.
   Timeout	The maximum duration for monitoring an API call. The default value is 3 seconds. You can set this to an integer from 3 to 60. If an API call exceeds the specified Timeout, an error is reported to help you promptly detect and handle exceptions. For more information, see Manage service monitoring APIs.
   Cache Settings	You can Enable or Disable this feature. If you enable it, configure Cache Timeout. The default value is 300 seconds. You can set a positive integer from 60 to 1,000,000 seconds (approximately 277.78 hours).
   Version Number	Enter the version number of the API. Each configuration has its own version number for comparison with the previous version. The version number must be unique for this API. The version number must 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 (-).
   Return Type	The default is JSON.
   Return SQL	Controls whether the API response includes the actual SQL statement that is executed. Select Yes (Enable): The API response returns the physical SQL statement that is executed in the database. Select No (Disable): The API response displays the original SQL script.

   API request parameters and response parameters configuration

   When configuring API Request Parameters and Response Parameters, you need to first determine the source of the input and output parameters (Service Unit), then write the API SQL and parse the Request Parameters and Response Parameters, and finally configure the basic information of the Request Parameters and Response Parameters.

   1. In the Parameter Checking section, select Mode and Service Unit. The system displays all fields in the selected service unit as a reference for writing API SQL.

      

      Parameter	Description
      Mode	The supported modes are Basic and Dev-Prod. 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 during publishing.
      Service unit fields	You can copy all table fields or a single field. Abnormal fields are marked with an alert icon. If a field is abnormal, verify that its service unit exists and has been published to the production environment.
      Operation	Data Preview: Click to open the Data Preview panel. By default, the panel displays the first 100 data records from the current service unit. You can select the number of records to return: 10, 50, 100, 500, or 1000. View Row-Level Permissions: This operation is available when row-level permissions are enabled for the referenced service unit. Click to open the View Row-Level Permissions panel. The panel displays row-level permission information for the selected service unit, such as the permission name, description, control field, associated table, and foreign key field. When the mode is Basic, the panel shows row-level permissions for the service unit in the production environment. When the mode is Dev-Prod, the panel shows row-level permissions for the service unit in both the development and production environments.

   2. Write the API SQL script based on the examples and the following rules:

      • When creating an API based on a service unit, the SQL script must use MySQL syntax.

      • The following are not supported:

        • Multiple SQL statements are not supported.

        • Statements other than SELECT, such as INSERT, UPDATE, CREATE, or DELETE, are not supported.

        • Table joins and nested queries are not supported.

        • Aliases for tables are not supported.

      • If you use an aggregate function, such as MIN, MAX, SUM, or COUNT, you must specify an alias for the response parameter name. For example, sum(num) as total_num.

   3. Dataphin can parse SQL script templates for Response Parameters and Request Parameters as follows:

      • Template 1: Query and return fields from the service unit only.

        • Command format

          select <response parameter> from <service unit name> where <request parameter>=${request parameter value}

        • Example

          --Query id_card and name from service unit A when c=id_card.
          select id_card,name from service unit A where c=${id_card} 

      • Template 2: Query and return fields contained in the service unit.

        • Command format

          select <response parameter> from <service unit name> where <request parameter> in (${request parameter value})

        • Example

          --Query id_card and name from service unit A when c %in% id_card.
          select id_card,name from service unit A where c in (${id_card})

      • Template 3: Fields support aggregate functions such as max, sum, min, and count, and also support fuzzy matching using like.

        • Command format

          select max(<response parameter1>) as <alias c>,sum(<response parameter1>) as <alias b> ,min(<response parameter1>) as <alias d>,count(*) as <alias e> from <service unit A> where <request parameter name1> like ${request parameter value1}

        • Example

          --Query the maximum value of field a as parameter name c, the minimum value of a as parameter name d, and the sum of a as parameter name b from service unit A when field c %like% id_card.
          select max(a) as c,sum(a) as b ,min(a) as d,count(*) as e from service unit A where c like ${id_card}

      • Template 4: Field operations support + (addition), - (subtraction), * (multiplication), / (division), % (modulo), // (integer division), ** (power), along with logical operators and and or.

        • Command format

          select (<response parameter1>+<response parameter2>) as <alias>,(<response parameter2>+<response parameter3>) as <alias> from <service unit A> where <field c>=${request parameter name1} and <field b>>=${request parameter name2} or <field c><=${request parameter name3}

        • Examples

          --In service_unit_A, returns the values of a+b and b+c as acd and bcf respectively, where c=${id_card} or (b>=${num} and c<=${num1}).
          select (a+b) as acd, (b+c) as bcf from service_unit_A where c=${id_card} or (b>=${num} and c<=${num1})

      Important

      In all four templates above, the API request parameters and response parameters must be obtained from the same service unit (single physical table service unit or multiple physical table service unit). Otherwise, the API cannot be called properly.

   4. After clicking Parse Parameters, Dataphin automatically parses the input and output parameters of the API SQL and adds them to the Request Parameters and Response Parameters sections. The basic information for request parameters and response parameters is essentially the same. The following table describes the basic information and configuration requirements for request parameters and response parameters.

      Parameter	Description
      Parameter Name	Displays the parameter name exposed externally.
      Parameter Type	Parameter types include DOUBLE, FLOAT, STRING, DATE(yyyy-MM-dd HH:mm:ss), BOOLEAN, INT, LONG, SHORT, BYTE, BIGDECIMAL, and BINARY. You need to select the parameter type corresponding to the bound field of the parameter name. The field type of the service unit is consistent with the field type of the request parameter. You can select the corresponding field type.
      Parameter Value Type	Select the type of parameter value, which can be single value or multiple values. Single Value: The input parameter will be parsed as a single value, applicable operators are =, like, >=, <=, >, <, !=, between. Multiple Values: The input parameter will be parsed as multiple values, separated by commas (,), applicable operator is in.
      Parameter Processing	This needs to be configured when the operator is LIKE. If parameter processing is not selected, you need to manually enter wildcards for matching when inputting parameters. You can select Fuzzy Match (%keyword%), Right Match (%keyword), or Left Match (keyword%).
      Example	Enter example values for the request and response parameters to help developers understand them. You can enter up to 1000 characters.
      Description	Enter a brief description of the request and response parameters. You can enter up to 1000 characters.
      Required	Specify whether the request parameter is required when calling the API. Select No: The SQL statement for calling the API can be executed without this parameter. Select Yes: The SQL statement for calling the API cannot be executed without this parameter. For example, if the request parameter is id, the request parameter is required, and the response parameter is name, executing the following statements will yield different results: select name from tableA where id=5;: Returns the name field and data results. select name from tableA;: The SQL statement execution fails with an error.
      Operation	The following operations are supported for request parameters and response parameters: Request Parameters: Supports batch modification of parameter type, parameter value type, parameter processing (only supported when the operator is LIKE), and whether the parameter is required. Response Parameters: Supports batch modification of parameter type.

   5. Click SQL Test Run. In the Request Parameter Input dialog box, select Parameter Type, Parameter Value Type, Parameter Processing, and Test Run Input Value, and then click OK.

      • Run Log: Allows you to view the actual SQL statement executed during the SQL test run.

      • Test Run Input Value: You need to configure field values for bound fields. You can view the field values of bound fields in the Data Preview panel.

      • Batch Operations: Supports batch modification of parameter type, parameter value type, and parameter processing (only supported when the operator is LIKE) for request parameters.

   6. Click Fill Parameter Example Values. The system will fill the example values from the most recent successful test run into the request parameters and response parameters. If examples already have values, they will not be overwritten. You can modify the values.

2. Click Submit. The system will verify whether the fields referenced by the API exist in their respective service units. After the verification is passed, the API generation is complete.