Create an API (operation type) by directly connecting to a data source

Limits

• The create, update, and delete operations are available only if you have purchased the data augmentation feature.

• For a list of data sources that support direct SQL connections for create, update, and delete operations, see Supported data sources for DataService Studio.

• Create, update, and delete APIs consume significant computing resources. A single-record request uses two concurrent connections, and a batch request uses 2 × degree of parallelism concurrent connections. Plan resource usage based on your business needs.

• In Advanced SQL mode, batch operations execute individually rather than as a batch, which degrades performance. Avoid using Advanced SQL mode for create, update, and delete APIs.

• If your SQL statement includes an in clause, each record is processed individually, which results in poor performance. Avoid using in clauses.

Permissions

• Project administrators and developers can create APIs.

• To create, update, or delete APIs, you must have write-through or execute permissions for the data source.

Step 1: Select a method to create an API

1. On the Dataphin homepage, choose Service > API Development in the top menu bar.

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 Connect to Data Source - SQL Mode.

Step 2: Configure API parameters

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

   API basic information

   Parameter	Description
   API Name	Enter a name for the API. The naming conventions are as follows: The name can contain Chinese characters, letters, digits, and underscores (_). The length must be between 4 and 100 characters. Must start with a Chinese character or an English letter. Globally unique.
   Operation Type	Create: Creates an object. You can create a single object or multiple objects in a batch. Update: Updates an object. You can update a single object or multiple objects in a batch. Delete: Deletes an object. You can delete a single object or multiple objects in a batch.
   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 in length.
   Protocol	The protocol used by 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. Ensure that the SSL certificate for the independent domain name is valid to prevent call failures. To configure an SSL certificate, choose Platform Management Network Configuration and go to the Network Configuration page.
   Data Volume	Select the amount of data to process. You can process a Single record or a Batch of records.
   Invocation Mode	The invocation mode for API calls. Only synchronous invocation is supported. Synchronous Invocation: The client waits for the server to return a result before sending the next request. This mode is suitable for scenarios that require real-time responses and short processing times.
   Maximum Number Of Input Records	This parameter is available when Data Volume is set to Batch. The maximum number of input records for the API. The default value is 1,000. You can enter a positive integer from 1 to 1,000,000. The system supports different request body sizes based on the gateway configuration: If the gateway is an Alibaba Cloud API Gateway - Dedicated Instance, the request body cannot exceed 8 MB.
   Timeout	The timeout for API calls. For synchronous invocations, the default value is 3 seconds, and the valid range is positive integers from 3 to 60 seconds. For asynchronous invocations, the default value is 600 seconds, and the valid range is positive integers from 3 to 7,200 seconds (2 hours). If the API call exceeds the specified timeout period, an error is reported. This helps you promptly identify and handle API call exceptions. For more information about how to view exceptions, see Manage service monitoring APIs.
   Error Handling	This parameter is available when Data Volume is set to Batch. The method for handling SQL runtime errors. You can select Allow Partial Success or Succeed Only If All Succeed. You can select Succeed only if all succeed only if the data source supports transactions.
   Version Number	Enter the version number of the API. Each configuration has a version number for comparison with the previous version. The version number must be unique for the API. The naming conventions are as follows: The value cannot exceed 64 characters. The version number 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 executed SQL statement. Select Yes (enable): The API response will return the physical SQL statement actually executed by the database. Select No (Disable): The API response will display the original SQL script.

   API request and response parameters

   To configure request and response parameters, determine the source tables, write the API SQL statement, parse the parameters, and then configure basic parameter information.

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

      

      Parameter	Description
      Mode	Select the environment of the data source. You can select Basic or Dev-Prod. In Basic mode, the production database is read during development, submission, and online publishing. In Dev-Prod mode, the development database is read during development and submission, and the production database is read during online publishing.
      Datasource	Select a data source based on its type. For a list of data sources that support create, update, and delete operations, see Supported data sources for DataService Studio. Note MySQL 5.1.43, MySQL 5.6/5.7, and MySQL 8 are supported.
      SQL Mode	You can select Basic SQL or Advanced SQL mode. 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. The supported tags are: if, choose, when, otherwise, trim, foreach, and where. For SQL logic examples, see Reference Example.
      Transaction Processing Mode	Available when the data source supports transactions. Specifies the transaction processing mode for data operations. If Data Volume is set to Single, only no transaction is supported, which means transaction processing is not used. If Data Volume is set to Batch, no batching and batch processing are supported. No Batching: The entire batch of data is processed in a single transaction. Batch Processing: This option is available only when Error Handling is set to Allow partial success. Transactions are split and processed by batch.
      Data Volume Per Batch	This parameter is available when Transaction Processing Mode is set to Batch processing. The amount of data in a single run. The system merges SQL statements from multiple calls into a single batch. The order within the batch is maintained, but the order between batches cannot be guaranteed. The default value is 1,000 records. You can enter an integer greater than 1. The system assigns the total number of batches to different nodes based on the degree of parallelism. The number of batches is calculated as follows: Total number of records / Data volume per batch.
      Parallelism	This parameter is available when Transaction Processing Mode is set to Batch processing. The number of batches of SQL nodes that can run at the same time. The default value is 1. You can enter a value from 1 to 5.
      API SQL Script Editor	Follow the SQL editing specifications when you edit API SQL scripts. For more information, see API SQL script editing instructions.
      Reference Example	The following are SQL script templates from which Dataphin can parse response and request parameters: Create Basic/Advanced SQL example: -- Example 1: Insert a data row INSERT INTO customers ( customer_id, first_name, last_name, ) VALUES ( ${customerId}, ${firstName}, ${lastName}, ) -- Example 2: Insert a data row. customer_id is an auto-increment field. INSERT INTO customers ( first_name, last_name, ) VALUES ( ${firstName}, ${lastName}, ) RETURNING customer_id -- Example 3: Upsert data <choose> <!-- Scenario 1: If the record exists, update it. --> <when test="exists == true or forceUpdate == true"> UPDATE customers SET first_name = #{firstName}, last_name = #{lastName}, WHERE customer_id = ${customerId} </when> <!-- Scenario 2: If the record does not exist, insert it. --> <otherwise> INSERT INTO customers ( customer_id, first_name, last_name, ) VALUES ( #{customerId}, #{firstName}, #{lastName}, ) </otherwise> </choose> Update Basic/Advanced SQL example: -- Example: Update data UPDATE customers SET first_name = ${firstName}, last_name = ${lastName}, WHERE customer_id = ${customerId} Delete Basic/Advanced SQL example: -- Example: Delete data DELETE FROM customers WHERE customer_id = ${customerId}
      Format	Formats and displays the SQL statement. This feature is available only for Basic SQL.
      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 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 containing the field has been published to production or whether it exists.

   2. Click Parse Parameters. The system 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 for SQL Mode, you can select Keep Manual Configurations. If you select this option, when you modify the SQL script and parse the parameters again, the system retains the existing parameter information. You must manually delete any unwanted parameters. This is useful in scenarios where parameters of complex SQL statements cannot be parsed automatically and must be entered manually.

      Note

      • If SQL Mode is set to Advanced SQL, parameters that start with var_cols are dynamic parameters. You can pass parameters to specify the fields returned by the SQL query. You can 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 values of the fields in the response parameters are null.

      • In Basic SQL mode, if a value is not provided for an optional parameter, 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.

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

      • The system checks for different permissions based on the SQL operation type:

        • SELECT: Requires read-through or execute permissions for the data source.

        • INSERT, UPDATE, DELETE: Requires write-through or execute permissions for the data source.

      Parameter		Description
      Request Parameters	Parameter Name	Required. The externally visible parameter name. The system parses it from the SQL statement. You cannot modify it.
      	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. You can select Single value or Multiple values. Single Value: The input parameter is parsed as a single value. The applicable operators are =, like, >=, <=, >, <, !=, and between. Multiple Values: The input parameter is parsed as multiple values separated by commas (,). The applicable operator is in.
      	Parameter Processing	This parameter is available when the operator is LIKE. If you do not select a parameter processing method, 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	Enter an example of the request and response parameter values for developer reference. The example can be up to 1,000 characters in length.
      	Description	Enter a brief description of the request and response parameters. The description can be up to 1,000 characters in length.
      	Required	Specifies whether the request parameter is required when calling the API. Select No: The API call SQL statement can be executed even if the parameter is not included in the API call statement. Select Yes: The API call SQL statement cannot be executed if the parameter is not included in the API call statement. For example, if the request parameters are ID and name, and both are required, different results are returned based on the input: If both ID and name are entered, the SQL statement is executed normally: update tableA set name='Tom' where ID=5;. If ID or name is not entered, the API request fails.
      	Default Value	This parameter is available when Required is set to No. If no value is specified for the parameter, the default value is used. If no default value is set, the value is NULL. The default value can be up to 1,000 characters in length.
      	Operations	You can modify the parameter type, parameter value type, and parameter processing (only when the operator is LIKE) of multiple request parameters in a batch. You can also specify whether the parameters are required and delete multiple parameters in a batch (only in Advanced SQL mode). If you select Advanced SQL mode, you can click Add Request Parameter to manually add parameters.
      Response Parameters	Operation completed successfully	Required. This parameter is available when Data Volume is set to Batch. Specifies whether to return result data and the format of the result data for successful operations. The default value is No. You do not need to configure response parameters. If you select Yes, you can click Add Response Parameter to manually add response parameters for successful operations. In Advanced SQL mode, you can also import the returned fields from the SQL test run results as response parameters.
      	Operation failed	Required. This parameter is available when Data Volume is set to Batch. Specifies whether to return result data and the format of the result data for failed operations. The default value is Yes. You can click Add Response Parameter to manually add response parameters for failed operations. If you select No, you do not need to configure response parameters. In Advanced SQL mode, you can also import the returned fields from the SQL test run results as response parameters.
      	Parameter Name	Required. The externally visible parameter name. If Data Volume is set to Single, the system parses fields from the API SQL script as request and response parameters. You cannot add or delete request parameters, but you can add and delete response parameters. If Data Volume is set to Batch and SQL Mode is set to Basic SQL, the system parses fields from the API SQL script as request and response parameters. You cannot add or delete request parameters, but you can add and delete response parameters. If Data Volume is set to Batch and SQL Mode is set to Advanced SQL, the system parses fields from the API SQL script as request and response parameters. You can add and delete both request and response parameters.
      	Data Source	Identifies the data source of the parameter. In Basic SQL mode, the system parses the response parameters from the API SQL script. The data source cannot be modified. In Advanced SQL mode, the system parses the response parameters from the API SQL script. You can select Input Parameter or SQL Return to identify the data source.
      	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	Enter an example of the request and response parameter values for developer reference. The example can be up to 1,000 characters in length.
      	Description	Enter a brief description of the request and response parameters. The description can be up to 1,000 characters in length.
      	Operations	You can modify the parameter type of multiple response parameters in a batch and delete multiple parameters in a batch (only in Advanced SQL mode). If you select Advanced SQL mode, you can click Add Response Parameter to manually add parameters.

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

      Important

      The test run modifies data, and the changes cannot be rolled back.

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

      • Test Run Input Value: You must configure the values for the bound fields. You can view the field values on the Data Preview panel.

      • Batch Operations: Allows you to modify the parameter type, parameter value type, and parameter processing method (only when the operator is LIKE) for multiple request parameters at once. You can also delete multiple parameters at once (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. Existing example values are not overwritten. You can modify the values.

   5. If the SQL mode is Advanced SQL and test run result records exist, you can click Fill Return Parameters or Import From Trial Run Results to configure how to add parameters and handle parameters with the same name in the Fill Parameters dialog box.

      • Add Mode: Specifies the policy for adding parameters when importing. You can choose to append new parameters or replace all existing parameters.

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

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

      • Handling of Duplicate Parameter Names: This parameter is available when Add Mode is set to Append new parameters. It specifies the policy for handling duplicate parameter names. You can keep the existing parameters or replace them.

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

        • Replace: If a parameter name from the test run results matches an existing parameter name, the information for the existing parameter is updated with the parameter type and example value from the current test run results. If a value from the test run results is empty, the existing value is not replaced.

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

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