Help Center> DataArts Studio> User Guide> DataArts DataService> API Development> Creating an API> Generating an API Using a Script or MyBatis
Updated on 2024-04-29 GMT+08:00
View PDF

Generating an API Using a Script or MyBatis

This section describes how to generate an API using a script or MyBatis.

This mode can meet personalized query requirements of users. It allows you to compile API query SQL statements and provides multi-table join, complex query conditions, aggregation functions, and more capabilities.

  • Script: Only common SQL syntax is supported.
  • MyBatis: Only DataArts DataService Exclusive supports this mode. In this mode, the script supports the Mybatis tag syntax. The parameter parsing format is #{parameter}. Tag syntax such as if, choose, when, foreach, and where is supported. You can use the tag syntax to implement complex query logic such as null value verification, multi-value traversal, dynamic table query, dynamic sorting, and aggregation.

Configuring Basic API Information

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

  1. In the left navigation pane, choose an edition, for example, Exclusive Edition. The Overview page is displayed.
  2. 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, /getUserInfo

    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 2 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

    Protocol used to transmit requests. The shared edition supports HTTP and HTTPS, and the exclusive edition supports HTTPS.

    HTTPS is recommended. HTTP is insecure and may have security risks.

    • 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

    A reviewer who has permissions to review APIs. Click Add to enter the Review Center page and click Add on the Reviewers tab page to add a reviewer.

    Security Authentication
    When creating an API, you can select one of the following authentication modes. The three modes differ in how the API is called. You are advised to use App Authentication, which is more secure that the other two modes.
    • App authentication: App authentication is used for calling an API. The AppKey & AppSecret is used for authentication. It is highly secure.

      When App authentication is used, an SDK is required for access. Java, Go, Python, JavaScript, C#, PHP, C++, C, and Android SDKs are available. For details about how to call APIs in each language, see Calling APIs Through App Authentication.

    • IAM authentication: IAM authenticates API requests. This mode is available only for Huawei cloud users. The security level is medium.

      When using IAM authentication, you need to call the Obtaining a User Token API of IAM to obtain a token, add the X-Auth-Token parameter with the obtained token as the value to the request header, and use an API calling tool or SDK to call released APIs.

    • Non-authentication: No authentication is required. This mode allows all users to access APIs, which may pose security risks. It is recommended only for testing APIs. If the caller is not a trusted user, there is a risk of data leakage, breakdowns caused by high concurrent access, SQL injection, and others.

      This mode does not require any authentication information. You can use an API calling tool or SDK to directly call an API by specifying required parameters.

    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 Parameters

    Parameters required for calling the API. The parameters are used as the request parameters on the Set Data Extract Logic page.

    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.
    NOTE:

    When defining an input parameter, ensure that the following size requirements are met:

    • Query and Path: 32 KB.
    • HEADER: The maximum size is 128 KB.
    • BODY: The maximum size is 128 KB.
    You need to set input parameters based on the designed request parameters for the API. For example, the request path of the API used to query user information in a table by user ID is /getUserInfo. You can configure input parameters as follows:
    • If the request parameter for calling the API is id, and the information about the user with id needs to be returned, configure an input parameter as follows:
      1. Click Add and enter id for Name.
      2. Set Parameter Location to Query.
      3. Set Type to Number.
      4. Select Yes for Mandatory.
      5. Retain the default value.
    • If the request parameters for calling the API are id1 and id2, and the user information between id1 and id2 needs to be returned, configure input parameters as follows:
      1. Click Add and enter id1 for Name.
      2. Set Parameter Location to Query.
      3. Set Type to Number.
      4. Select Yes for Mandatory.
      5. Retain the default value.
      6. Click Add again and configure parameter id2.
  3. After the basic API information is complete, click Next to go to the Data Extract Logic page.

Configuring the Data Extraction Logic

This section uses a script to describe how to configure the API data extraction logic. The configuration procedure for the Mybatis mode is the same as that for the script mode, except for the parameter parsing mode and supported syntax.

If you use Mybatis to generate an API, you need to change the parameter parsing format in the scripts in this section from ${parameter} to #{parameter}. In addition, you can click in the script editing area to view the tag syntax supported by Mybatis.

Set Data Acquisition Method to Script or MyBatis.
  1. Set Data Source, Data Connection, and Database.

    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.

  2. Set Paging Mode. You are advised to select Custom.
    • 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 * FROM userinfo WHERE id=${userid}

      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 * FROM userinfo WHERE id=${userid}) LIMIT {limitValue} OFFSET {offsetValue}

      limitValue indicates the number of data records to be read, and offsetValue indicates the number of data records to be skipped (that is, offset). For example, if limitValue is set to 20 and offsetValue is set to 40, 40 data records will be skipped and 20 will be read. Generally, limitValue and offsetValue can be used as input parameters. You can transfer values to them when debugging or calling an API. If limitValue or offsetValue is not specified, the system assigns default values to them.

    • Custom pagination: DataArts DataService does not process the SQL script for creating an API. You need to define the pagination logic when writing the SQL statement.
      If limitValue (number of data records to be read) and offsetValue (number of data records to be skipped) have been obtained, you can define the pagination logic using the following script: 
      SELECT * FROM userinfo WHERE id=${userid} LIMIT {limitValue} OFFSET {offsetValue}

      More commonly, you can use pageSize and pageNum to define the pagination logic. The script format is as follows:

      SELECT * FROM userinfo WHERE id=${userid} LIMIT {pageSize} OFFSET {pageSize*(pageNum-1)}

      The syntax style varies depending on the data source, and so does the pagination script. The following are some example data sources:

      • DLI does not support the LIMIT {limitValue} OFFSET {offsetValue} format. It only supports the LIMIT {limitValue} format.
      • HetuEngine does support the LIMIT {limitValue} OFFSET {offsetValue} format. It only supports the OFFSET {offsetValue} LIMIT {limitValue} format.
  3. Compile the SQL statement for a query API.

    On the script editing page, click next to Edit Script and develop a SQL query statement as prompted. You can click to add input parameters to the SQL statement as API request parameters.

    For example, you can write the following script to query user information in a user table based on the user ID. id is a field in the userinfo table, and userid is an input parameter defined for the API.

    SELECT * FROM userinfo WHERE id=${userid}

    If custom pagination is used, the value of pageSize is 10, and the value of pageNum is 2, write the following script based on the LIMIT {pageSize} OFFSET {pageSize*(pageNum-1)} conversion method:

    SELECT * FROM userinfo WHERE id=${userid} LIMIT 10 OFFSET 10
    Figure 3 Compiling the SQL statement for a query API

    Click Test SQL under the script editing window, set the value for the input parameter, and click Run to check whether the expected result can be returned. If the test fails, you can check whether the SQL statement meets the expectation on the SQLs tab page or view the error message on the Logs tab page.

    Figure 4 Testing the SQL statement

    • The fields obtained by SELECT are the response parameters of the API. (The aliases can be obtained through AS.)
    • Parameters in the where condition are API request parameters. In the script mode, the parameter format is ${Parameter name}. In the MyBatis mode, the parameter format is #{Parameter name}.
    • You can enable Return Total Records. Then the total number of script execution results will be returned.
    • If you want to set multiple values for a parameter, observe the following format:
      • String: 'a','b','c'
      • Value: 1,2
      • Field: a,b,c
  4. Add ranking parameters.

    In the ranking parameter list, click Add to add ranking fields.

    • Field names are invisible to external systems. They are fields of the selected tables and are accessed during an API call. If the query SQL statement of the API has been compiled and verified through a test, you can select a ranking field from the Field Name text box.
    • Variables can be customized and associated with field names. Enter a parameter name in the Variable text box. The system automatically changes the parameter name to a variable.
    • If you select Optional, the parameter is optional.
    • The ranking mode can be ascending, descending, or custom. If you set Ranking Mode to Ascending or Descending, but set pre_order_by to a value different from the value of Ranking Mode when testing or calling the API, the API cannot be called.

    Ranking parameters take effect only after they are added to the SQL script. Click to add ranking parameters to the SQL statement and use ORDER BY to sort the parameters.

    For example, you can write the following script to query user information in a user table based on the user ID, with age and kk used to sort the query results and pageSize and pageNum set to 10 and 2, respectively.

    SELECT * FROM userinfo WHERE id=${userid} order by (${age},${kk}) LIMIT 10 OFFSET 10
    Figure 5 Adding ranking parameters

    Click Test SQL under the script editing window, set the values for userid and pre_order_by, and click Run to check whether the expected result can be returned. The default value of pre_order_by is provided by the system based on the ranking parameter information, which is the ascending order. Generally, the value of pre_order_by is in either of the following formats: Ranking parameter name:ASC (ascending order) or Ranking parameter name:DESC (descending order). Separate multiple ranking parameter descriptions by semicolons (;).

    If the test fails, you can check whether the SQL statement meets the expectation on the SQLs tab page or view the error message on the Logs tab page.

    • The pre_order_by parameter is optional. By default, the default value (ascending) of the mandatory ranking field is used.
    • Ensure that you set the pre_order_by parameter by strictly following the ranking parameter sequence, optional attributes, and ranking mode configured in the ranking parameter list. Otherwise, the API cannot be called.
    Figure 6 Testing the SQL statement

  5. Click Next to go to the API test page.

Testing the API

  1. Set values for input parameters.
    If you want to set multiple values for a parameter, observe the following format:
    • String: 'a','b','c'
    • Value: 1,2
    • Field: a,b,c
    Figure 7 Setting values for input parameters

  2. (Optional) Change the value of pre_order_by, which indicates the ranking parameter description.

    The system provides a default value based on the ranking parameter value, which indicates the ascending order. Generally, the value of pre_order_by is in either of the following formats: Ranking parameter name:ASC (ascending order) or Ranking parameter name:DESC (descending order). Separate multiple ranking parameter descriptions by semicolons (;).

    • The pre_order_by parameter is optional. By default, the default value (ascending) of the mandatory ranking field is used.
    • Ensure that you set the pre_order_by parameter by strictly following the ranking parameter sequence, optional attributes, and ranking mode configured in the ranking parameter list. Otherwise, the API cannot be called.
    Figure 8 Changing the value of pre_order_by

  3. (Optional) View the values of pagination parameters.

    If the default pagination mode is used, you can view the pagination parameters. pageSize indicates the size of a page, and pageNum indicates the page number. By default, the page size is 100, and data on the first page is returned.

    Figure 9 View the values of pagination parameters

  4. 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.
    • During the test, if the DataArts DataService API does not return a query result within 30 seconds (default value), a timeout error is reported.
    • If the test fails, follow the instructions as prompted and restart the test.

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