Data service

Preparations

/** Create a table. **/
CREATE TABLE `student_courses` (
  `id` BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT 'Primary key',
  `student_id` BIGINT(20) DEFAULT NULL COMMENT 'Student ID',
  `course_id` BIGINT(20) DEFAULT NULL COMMENT 'Course ID',
  `start_time` DATETIME DEFAULT NULL COMMENT 'Creation time',
  `score` int(11) DEFAULT NULL COMMENT 'Examination score',
  KEY `idx_student_course` (`student_id`,`course_id`),
  KEY `idx_student_time` (`student_id`,`start_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Table for data service tests';

/** Insert data. **/
INSERT INTO `student_courses` VALUES (1,1,1,'2020-11-24 11:19:54',78);
INSERT INTO `student_courses` VALUES (2,1,2,'2020-11-24 05:51:36',68);
INSERT INTO `student_courses` VALUES (3,1,3,'2020-11-24 05:28:08',60);
INSERT INTO `student_courses` VALUES (4,2,1,'2020-11-24 07:21:17',67);
INSERT INTO `student_courses` VALUES (5,2,2,'2020-11-24 02:07:52',91);
INSERT INTO `student_courses` VALUES (6,2,3,'2020-11-24 10:35:35',94);
INSERT INTO `student_courses` VALUES (7,3,1,'2020-11-24 03:19:03',53);
INSERT INTO `student_courses` VALUES (8,3,2,'2020-11-24 08:19:18',94);
INSERT INTO `student_courses` VALUES (9,3,3,'2020-11-24 02:04:17',89);

Step 1: Create and publish an API

1. Log on to the DMS console.
2. In the top navigation bar, move the pointer over the More icon and choose Data Factory > Data Service.
3. In the left-side navigation pane, click the API Development tab.
4. Click New API in the upper-right corner. On the page that appears, define an API operation.
   
5. Configure the properties as required.

   The properties of an API are divided into four parts and displayed on four tabs: AttributeConfiguration, ExecuteConfiguration, RequestParameters, and Return parameter. This example describes only some of the properties on the four tabs. For more information about the properties, see Create an API operation.

   • AttributeConfiguration

     Property	Description
     APIName	Required. The name of the API. The name must be 4 to 100 characters in length and can contain letters, digits, and underscores (_). It must start with a letter. Example: POC_API_test.
     Path	Required. The path that forms a part of the URL that is used to call the API. The path must start with a forward slash (/) and can contain only letters, digits, underscores (_), and hyphens (-). Example: /item/add. The URL that is used to call the API is in the https://{Group domain name}{Path} format. If the path is defined as /item/POC_API_test and the group domain name is xxxx-cn-hangzhou.alicloudapi.com, the URL that is used to call the API is https://xxxx-cn-hangzhou.alicloudapi.com/item/POC_API_test.
     RequestMode	Required. The method to be used to call the API. Valid values: POST GET In this example, the RequestMode parameter is set to GET.

   • ExecuteConfiguration

     In this example, the Instance query type parameter is set to Single InstanceQuery, and the ConfigurationMode parameter is set to Table boot mode on the ExecuteConfiguration tab. In addition, the student_id field is specified as the request parameter, and the student_id, course_id, and score fields are specified as response parameters.

     Property	Description
     Instance query type	The type of instance query. Valid values: Single InstanceQuery: You can query data only in one database instance. In this case, the API can be defined in table boot mode or script mode. Cross-instanceQuery: You can write dynamic SQL statements for the API to query data across multiple database instances. In this case, the API can be defined only in script mode.
     Data source	The database to be queried when the API is called. You can enter a keyword in the field to search for the databases that you have permissions to query. This parameter is displayed only when you set the Instance query type parameter to Single InstanceQuery.
     ConfigurationMode	The method that is used to configure data query. Valid values: Table boot mode: You can configure data query by selecting a table and fields. Script mode: You can configure data query by writing SQL statements and defining variables. Note When you switch from the table boot mode to the script mode, a message appears to remind you that the current definitions of request parameters and response parameters will be cleared. This parameter is displayed only when you set the Instance query type parameter to Single InstanceQuery.
     SelectTable	The table to be queried. You can select a table in table boot mode. You can enter a keyword in the field to search for the table. This parameter is displayed only when you set the ConfigurationMode parameter to Table boot mode.
     FieldList	The fields in the table. This section lists all of the fields in the selected table for you to define request parameters and response parameters. You can define fields as request parameters and response parameters by selecting the check boxes. This parameter is displayed only when you set the ConfigurationMode parameter to Table boot mode.

   • RequestParameters

     Property	Description
     ParametersName	The name of the request parameter that is needed to call the API. The name must be 1 to 50 characters in length and can contain letters, digits, underscores (_), and hyphens (-). It must start with a letter or an underscore (_).
     VariableName or FieldName	The source of the parameter. The value may be a field that you have selected in table boot mode or a variable that you have defined in script mode.
     Data type	The data type of the request parameter. Valid values: String, Integer, and Floating point. Default value: String. In this example, set the Data type parameter of the student_id field to Integer. You can specify a proper data type to ensure that the data type of the request parameter is valid for the API to be called. This also ensures that the SQL statements for the API can be executed as required.

   • Return parameter

     Property	Description
     ParametersName	The name of the response parameter. The name must be 1 to 50 characters in length and can contain letters, digits, hyphens (-), and underscores (_). It must start with a letter or an underscore (_).
     VariableName or FieldName	The name of the field to be returned.
     Data type	The data type of the response parameter. Valid values: String, Integer, and Floating point. Default value: String. After you specify a proper data type for the response parameter, the queried data is converted to the specified data type and is returned in the JSON format.

6. After you complete the configurations, click Publish.
   Note You can also click Save to save the configurations of the API.
   This way, the API is published.

Step 2: Test an API

Perform this step to verify the configurations of an API.

1. Log on to the DMS console.
2. In the top navigation bar, move the pointer over the More icon and choose Data Factory > Data Service.
3. In the left-side navigation pane, click the API Test tab.
4. Select POC_API_test from the drop-down list.
5. In the Parameter value column, enter 1 in the field. Then, click Test.
   After the API is tested, view the execution information in the ExecuteInformation section, and view the results in the form or JSON format in the ReturnResults section on the right of the page.

Step 3: Call an API

You can use various methods to call APIs. For example, you can use SDKs or clients to call APIs. The authentication methods for calling APIs include simple identity authentication and signature identity authentication. To perform simple identity authentication, only an AppCode is required. For more information, see Overview.

This example describes how to use an AppCode to call an API. The URL that is used to call the API is in the https://{Endpoint}{API path}?AppCode=AppCode value&Request parameter 1=xxx&Request parameter 2=xxx format. You can obtain information such as the endpoint and AppCode from the DMS console.

1. Log on to the DMS console.
2. In the top navigation bar, move the pointer over the More icon and choose Data Factory > Data Service.
3. In the left-side navigation pane, click the API Call tab.
4. On the API calls page, obtain the endpoint and AppCode.

   In this example, the URL that is used to call the API is https://xxxe3e-cn-hangzhou.alicloudapi.com/item/POC_API_test?AppCode=3284xxxxx&student_id=1.

   You can copy the URL to the address bar of a browser or run a curl command to call the API.