Generating an API in the Script Mode

This topic describes how to generate an API in the script mode.

To meet personalized query requirements of users, DataArts DataService also supports API generation in the SQL script mode. It allows you to compile API query SQL statements and provides multi-table join, complex query conditions, and aggregation functions.

Configuring Basic API Information

On the DataArts Studio console, locate an instance and click Access. On the displayed page, locate a workspace and click DataArts DataService.

In the left navigation pane, choose an edition, for example, Exclusive Edition. The Overview page is displayed.

Choose API Development > APIs from the left navigation bar, and click Create. On the displayed page, enter the basic information.

**Table 1** API basic configuration
Parameter	Description
API	An API name consists of 3 to 64 characters and starts with a letter. Only letters, numbers, and underscores (_) are allowed.
API Catalog	A collection of APIs for a specific function or scenario. It is similar to a folder and specifies the location of APIs. You can search for APIs in a specified API catalog. The API catalog is the minimum organization unit of APIs in DataArts DataService and also the minimum management unit in the API gateway. Click Select Catalog to create an API catalog or select an existing one created in Creating an API Directory.
Request Path	API access path, for example, /v2/{project_id}/streams. It is the part between the domain name and query parameters in the URL of a request path, for example, /blogs/xxxx shown in the following figure. Figure 1 API access path in the URL Braces ({}) can be used to identify parameters in a request path as wildcard characters. For example, /blogs/{blog_id} indicates that any parameter can follow /blogs. /blogs/188138 and /blogs/0 can both match /blogs/{blog_id}, and are processed by this API. In addition, duplicate request paths are not allowed for the same domain name. When a path parameter is used as a wildcard, the name is not unique. For example, /blogs/{blog_id} and /blogs/{xxxx} are considered as the same path.
Parameter Protocol	A protocol used to transmit requests. HTTP and HTTPS are supported. HTTP is a basic network transmission protocol. It is stateless, connectionless, simple, fast, and flexible, and uses plaintext for transmission. It is easy to use but has poor security. HTTPS is an HTTP-based protocol with SSL or TLS encryption verification. It can effectively verify identities and protect data integrity. To access HTTPS APIs, you need to configure related SSL certificates or skip SSL verification.
Request Method	HTTP request method, indicating the type of the requested operation, such as GET and POST. The method complies with the resultful style. GET requests the server to return specified resources. This method is recommended. POST requests the server to add resources or perform special operations. This method is used only for API registration. The POST request does not have a body. Instead, it involves transparent transmission.
Description	A brief description of the API to create.
Tag	API tag. The tag is used to mark the API attributes. After the API is created, you can quickly search for the API by tag. A maximum of 20 tags can be set for an API.
Reviewer	An owner who has permissions to review APIs. Click Add to enter the Review Center page. On the displayed page, click Add on the Reviewer Management tab page to add a reviewer.
Security Authentication	Security authentication mode, which can be: App Authentication: API Gateway authenticates API requests. This mode has the highest security level. IAM Authentication: IAM authenticates API requests. This mode has a medium security level. No authentication: No authentication is required for accessing the API. This mode has a low security level, and is not recommended.
Display Scope	After the API is published, all users in the selected scope can view the API in the service catalog. Current workspace APIs Current project APIs Current tenant's APIs
Access Log	If you select this option, the API query result will be recorded and retained for seven days. You can choose Operations Management > Access Logs and select the request date to view the logs.
Min. Retention Period	Minimum retention period of the API publishing status, in hours. Value 0 indicates that the retention period is not limited. You can suspend, unpublish, or cancel authorization for an API only after the minimum retention period ends. The system notifies the authorized users. If all authorized users have processed the notifications or unbound the API from their apps, the API will be suspended or unpublished, or the API authorization will be canceled. Otherwise, the system will forcibly suspend, unpublish, or cancel authorization for the API when the minimum retention periods ends. For example, if the minimum retention period is set to 24 hours, the API can be suspended 24 hours after it is published. If the authorized user handles the notifications in the review center or unbind the API from the app, the API will be directly suspended. Otherwise, the API will be forcibly suspended when the minimum retention period ends.
Input Parameter	Configure parameters in the API request. An input parameter consists of the parameter location, parameter type, whether the parameter is mandatory, and the default value. The parameter location can be Query, Header, Path, or Body. In addition, static parameters are supported. Query is the query parameter following the URL. It starts with a question mark (?) and connects multiple parameters with &. Header is located in the request header and is used to transfer current information, for example, host and token. Path is a request parameter in the request path. If you configure a path parameter, you must also add this parameter to the request path. Body is a parameter in the request body and is generally in JSON format. Static is a static parameter that does not change with the value passed by API callers. The parameter value is determined upon API authorization. If the parameter value is not set during authorization, the default value of the API input parameter is used. The parameter type can be Number or String. Number corresponds to numeric data types such as int, double, and long. String corresponds to text data types such as char, vachar, and text. Mandatory and Default Value: If you select Yes for Mandatory, parameters must be passed for accessing the API. Otherwise, the default value of the parameter will be used if the parameter is not passed for accessing the API. Constraints for the parameters are as follows: Query and Path: 32 KB. HEADER: The maximum size is 128 KB. BODY: The maximum size is 128 KB. For instance, to configure the dynamic parameter project_id in the /v2/{project_id}/streams request path, do as follows: Click Add and enter project_id for Name. Set Parameter Location to PATH. Set Type to STRING. Select Yes for Mandatory. Leave Default Value blank.

After the basic API information is complete, click Next to go to the Data Extract Logic page.

Configuring the Data Extraction Logic

Set Data Acquisition Method to Script.

Select a data source, data connection, database, and queue to obtain the tables to be configured.

For details on the data sources supported by DataArts DataService, see Data Sources. Configure data sources in Management Center in advance and enter SQL statements as prompted.
Compile an SQL statement to query APIs.
On the script editing page, enter the SQL statement as prompted.
- The fields obtained by SELECT are the returned parameters of the API. (The alias is supported.)
- The parameters in the WHERE statement are parameters requested by APIs. The parameter format is ${parameter name}.
Select a pagination mode.
- Default pagination: If you enter a SQL script when creating an API, DataArts DataService automatically adds the pagination logic to the SQL script. For example, if you enter the following SQL script:
```
SELECT name as Student_Name FROM tableofresults
```
  When processing API debugging or calling, DataArts DataService automatically adds the pagination logic to the preceding SQL script and generates the following script:
```
SELECT * FROM (SELECT name as Student_Name FROM tableofresults) LIMIT {pageSize} OFFSET {offsetValue}
```
  pageNum and offsetValue are the input parameters for API debugging or calling. If the pageNum parameter is not specified, DataArts DataService sets it for the API by default. offsetValue is calculated based on the value of the input parameter pageSize. If pageSize is not specified, DataArts DataService sets it for the API by default.
- Custom pagination: DataArts DataService does not process the SQL script for creating an API. The pagination logic is defined by you. If you want to create an API that supports pagination, you can add the pagination logic when writing the SQL statement. Example:
```
SELECT name as Student_Name FROM tableofresults LIMIT {pageSize} OFFSET {offsetValue}
```
Add ranking parameters.
In the list of ranking parameters, set whether the ranking parameters are optional, set the ranking mode, and enter the description.

Click to add the input and ranking parameters to the API requests of the SQL statement.

Before adding ranking parameters, ensure that the SQL statement is correct.
Edit request parameters.
After the SQL statement is compiled, click Test SQL and bind the HTTP input parameters on the database field tab page. Set pre_order_by by referring to 6 in Configuring the Data Extraction Logic.

pre_order_by is optional. If pre_order_by is not set, the mandatory fields are used as the ranking criterion.

Testing the API

After setting and saving all parameters, click Next.

Specify Value and click Debug. You can view the Request and Response details on the right part of the displayed page. If the test fails, follow the instructions as prompted and restart the test. During the configuration, pay attention to the settings of the normal response example.

After the test is complete, click OK.

Modifying the API

To modify an API, choose API Development > API Catalogs or API Development > APIs, locate the API, and click Edit to modify the API.

An API can be edited only when it is in the Created, Rejected, Offline, or Disabled state.

Parent topic: Creating an API

Previous topic: Generating an API Using Configuration

Next topic: Registering APIs