Dataphin:Create an API in Direct Connection Data Source Mode

Limits

• When calling an API, if the data source supports paging and the API request method is List, PageStart and PageSize can be used to set paging, regardless of the paged query status.

• In advanced SQL mode, if the SQL script uses Limit to restrict query results, the OrderByList in API call parameters will only sort the data returned by Limit, with Limit taking precedence. For example: Sort the top 10 data by phone_no and then by paper_no.

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

• In basic SQL mode, if the SQL script uses Limit to restrict query results, paged query is not supported.

• APIs can be created by directly connecting to a data source using SQL. For a list of supported data sources, see Supported Data Sources for DataService Studio.

Permissions

Project administrators and developers who support the service can generate APIs.

Step 1: select the method to generate the API

1. On the Dataphin home page, navigate to the top menu bar and select Service > Development.

2. Select a project in the upper left corner, click API in the left-side navigation pane, and then click + New API on the API page.

3. In the API Creation Method Selection dialog box, select Direct Connection Data Source Mode - SQL Mode, and then click Confirm.

Step 2: Configure API Parameter Information

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

   API Basic Information Configuration

   Parameter	Description
   API Name	Enter the name of the API. The naming rules are as follows: Supports Chinese characters, letters, numbers, or underscores (_). Length is 4 to 42 characters. Must start with a Chinese character or an English letter. Globally unique.
   Request Method	The API request methods include GET and LIST: GET: Request the server to obtain a specified resource. LIST: Request the server to obtain a portion of resources.
   Data Update Frequency	Define the update frequency of the data returned by the API to help the caller understand the timeliness of the data. Supported update 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 needs to belong. To create a group, see Create a Service Group.
   Description	Provide a brief description of the API. Up to 128 characters.
   Protocol	Only HTTP is supported. HTTP, or Hypertext Transfer Protocol, is the most widely used network protocol. If the gateway is configured as Alibaba Cloud API Gateway (dedicated instance or shared instance), HTTPS protocol is supported. Ensure that the SSL certificate of the independent domain is valid to avoid call failures. Configure the SSL certificate on the Platform Management Network Configuration page in Network Configuration.
   Timeout	Used to monitor the duration of API calls. the default is 3 seconds, and the supported time range is a positive integer from 3 to 30 seconds. If the API call exceeds the set timeout during the API call process, an error will be reported, allowing you to promptly identify and handle abnormal situations in the API call. For more information on viewing abnormal situations, see View and Manage Service Monitoring API.
   Max Return Count	The maximum number of API returns is 10,000. Supports entering a positive integer between 1 and 10,000. This configuration item is displayed when Request Method is selected as LIST.
   Cache Settings	Supports Enable or Disable. After enabling, you need to configure Cache Timeout. The default is 300 seconds, and the supported time range is a positive integer between 60 seconds and 1,000,000 seconds (approximately 277.78 hours).
   Version Number	Please enter the version number of the API. Each configuration information will have its version number for comparison with the previous version information. The version number is unique under this API. The naming rules are as follows: Up to 64 characters. Supports entering uppercase and lowercase English letters, numbers, underscores (_), half-width periods (.), and hyphens (-).
   Return Type	Default is JSON.

   API Request Parameters and Return Parameters Configuration

   When configuring Request Parameters and Return Parameters, first determine the source table for input and output parameters, write the API SQL, parse out the parameters, and then configure their basic information.

   1. In the API Parameter Configuration area, determine the source table for input and output parameters, and write the API SQL script based on the Reference Example.

      

      Parameter	Description
      Mode	Select the data source environment. Supports Basic or Dev-Prod. In Basic mode, development, submission, and online publishing all read the production database. In Dev-Prod mode, development and submission read the development database, and online publishing reads the production database.
      Datasource	Select the data source type and the data source of that type. For supported data sources, see Supported Data Sources for DataService Studio. Note MySQL supports MySQL5.1.43, MySQL5.6/5.7, and MySQL8 versions.
      Sql Mode	Supports selecting Basic SQL and Advanced SQL modes. Basic SQL: Write query logic using basic SQL syntax. For SQL logic examples, see Reference Example. Advanced SQL: Write query logic using SQL syntax that supports Mybatis tags. Currently supported tag types include the following: if, choose, when, otherwise, trim, foreach, and where. For SQL logic examples, see Reference Example.
      Result Pagination	When the request method is List, result pagination can be set. After enabling, be sure to specify the sorting field to ensure the stability of the returned query results, avoiding partial result duplication and loss in paged queries. After disabling, the API debugging or test page does not display paging parameters (PageStart and PageSize). You can uncheck Hide Parameters to display paging parameters.
      Sorting Priority	When Sql Mode is selected as Basic SQL, you can choose the sorting priority order, supporting SQL script or OrderByList request parameters. SQL Script: If the SQL script specifies sorting, the OrderByList in the public request parameters is not effective. Orderbylist Request Parameters: When testing or debugging the API, the sorting defined in the SQL script and the OrderByList public request parameters are both effective. The priority of the OrderByList public request parameters is higher than the sorting settings defined in the API. When Sql Mode is selected as Advanced SQL, during API testing or debugging, the sorting defined in the SQL script and the OrderByList public request parameters are both effective. The priority of the OrderByList public request parameters is higher than the sorting settings defined in the API. Note When the data source is HBase0.9.4/1.1.x/2.x, TDengine, or SAP HANA, sorting priority configuration is not supported.
      API SQL Script Editor	Supports single-table queries, table join queries, and nested queries in the same data source. The PostgreSQL data source type does not support cross-schema. The following usages are not supported: Multiple SQL statements are not supported. Non-SELECT syntax such as INSERT, UPDATE, CREATE, and DELETE is not supported. SELECT * is not supported. You must explicitly specify the columns to query. Setting optional parameters in SQL statements is not supported. If the column name of the SELECT query has a table name prefix (for example, t.name), you must use an alias as the return parameter name (for example, t.name as name). If using aggregate functions (MIN, MAX, SUM, and COUNT, etc.), you must use an alias as the return parameter name. For example, sum(num) as total_num. Parameters can be configured in the SELECT area, for example, select id_card, sum(case when id_card like ${id_card} then 1 else 0 end) as proj_score from tableA where c like ${id_card} group by id_card. Paging statements are not supported in SQL. Paging must be implemented through paging parameters. Note When the data source type is TDengine or SAP HANA, paging statements must be used in SQL to define paging parameters. The advanced SQL mode supports dynamically specifying the fields returned by the SQL statement query through parameter passing. The parameter name must start with var_cols (format: var_cols_xxx), and the parameter must be written in the query statement. All supported fields need to be added as return parameters. When calling the API, pass in the fields to be queried in the dynamic parameters. Fields not passed in will be null in the return result. For example: API SQL statement: select id,${var_cols_args} from table1. Value passed to var_cols_args parameter: name,age,old. Actual SQL statement executed by the API: select id,name,age,old from table1. When the data source type is Elasticsearch: When scrollId is not specified, only the first 10,000 data can be queried. When setting paging PageStart and PageSize, be careful not to query more than 10,000 data, otherwise, the call will fail. For example: If PageStart is set to 9998, the maximum PageSize can only be set to 2. To query data after 10,000, you need to use the where statement to specify scrollId. You can specify a request parameter scrollId during API development and pass in the corresponding scrollId value during the query. Note When specifying a scrollId value for the query, paging cannot be used, that is, PageStart and PageSize cannot be passed in, otherwise, the call will fail. Currently, only the scrollId field is supported as a condition for querying. Other fields are not effective. For example: select a from table where scrollId=${scrollId}.
      Reference Example	Dataphin can parse out the SQL script template for return parameters and request parameters as follows: Basic SQL Example: Template 1: select id_card,name from tableA where c=${id_card} Template 2: select id_card,name from tableA where c in (${id_card}) Template 3: select max(a) as c,sum(a) as b ,min(a) as d,count(*) as e from tableA where c like ${id_card} Template 4: select t.name as name from tableA c=${id_card} Template 5: select (a+b) as acd,(b+c) as bcf from tableA where c=${id_card} and/or b>=${num} and/or c<=${num1} Template 6: select id_card, sum(case when name like ${name} then 1 else 0 end) as proj_score from tableA where c=${id_card} group by id_card Advanced SQL Example: Example 1: select id from table <where> <if test="name != null "> AND age &gt; ${age} </if> <if test="name == null"> AND age &lt; ${age} </if> </where> Example 2: select id from table <where> <if test="name != null "> AND age > #{age} </if> <if test="name == null"> AND age &lt; #{age} </if> </where>
      Format	Supports formatting and displaying SQL statements, only supports basic SQL.
      Field Reference	In the Field Reference panel, all fields in the selected data table are displayed for you. Supports copying table names, all table fields, or individual fields. Abnormal fields are marked with an alert icon. You need to 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 will automatically parse the input and output parameters of the API SQL, adding them to the Request Parameters and Return Parameters areas, respectively. If you select advanced SQL as the SQL mode, you can opt to check Retain Manual Configuration. This allows you to keep the manually filled parameter information when you modify the SQL script and need to re-parse the parameters. Should you want to remove any unnecessary parameter information, you must do so manually. This feature is particularly useful in scenarios where complex SQL statement parameters cannot be automatically parsed and require manual input.

      Note

      • In advanced SQL, parameters starting with var_cols are dynamic, allowing for dynamic specification of fields returned by the SQL query. Include all supported fields in the return parameters and pass in the desired fields when calling the API. If not passed, the fields will be null in the return parameters.

      • In basic SQL, when a parameter is not required and there is no input, the system will automatically rewrite the SQL to ignore the corresponding filter conditions. If the parameter value type is between, the parameter value is required.

      • Advanced SQL statements are complex, and the parameters parsed may not be complete or correct. Therefore, you may need to delete, add request parameters, or add return parameters based on the SQL statement.

      The basic information for request parameters and return parameters is similar. The table below describes their basic information.

      Parameter	Description
      Parameter Name	Display the parameter name open to the public.
      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. If the field type of the logical table is not within the range of the parameter types to be selected, it is recommended to select String.
      Parameter Value Type	Select the type of parameter value. Supports single value and multiple values. Single Value: Passed in according to the parameter type. The passed parameter will be parsed as a single value. Applicable operators are =, like, >=, <=, >, <, !=, between. Multiple Values: The passed parameter will be parsed as multiple values, separated by half-width commas (,). Applicable operators are in.
      Parameter Processing	Configuration is required when the operator is LIKE. If parameter processing is not selected, you need to manually enter wildcard characters for matching when entering parameters. Supports selecting Fuzzy Match (%keyword%), Right Match (%keyword), and Left Match (keyword%).
      Example	Provide examples of request parameter values and return parameter values to help developers understand. Supports entering up to 50 characters.
      Description	Provide a brief description of the request parameters and return parameters. Supports entering up to 100 characters.
      Required	Select whether the request parameter is required when calling the API. Select No: The API call statement can be executed without this parameter. Select Yes: The API call statement cannot be executed without this parameter. For example, if the request parameter is id and the request parameter is required, and the return parameter is name, the following statements will have different returns: Return the corresponding name field and data: select name from tableA where id=5;. SQL statement execution error: select name from tableA;.

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

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

      • Trial Run Input Value: Configure the field value of the bound field. View the field value in the Data Preview panel.

   4. Click Fill Trial Run Result. The system will populate the most recent successful trial run value into the examples for request parameters and return parameters. Existing values will not be overwritten, and modifications are allowed.

2. Click Submit. The system will verify the existence of the fields referenced by the API in their respective data sources. Upon successful verification, the API generation is complete.