Help Center/ DataArts Studio/ User Guide/ DataArts DataService/ Developing APIs in DataArts DataService/ Creating an API/ Generating an API Using a Script or MyBatis
Updated on 2024-10-23 GMT+08:00
View PDF
Share

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.

Prerequisites

You have configured data sources on the Data Connection Management page of Management Center.

Notes and Constraints

APIs cannot be generated for the Chinese tables and columns in Hive data sources.

Creating an API Directory

An API catalog is an API index that is orchestrated and recorded in a certain sequence. It is a tool for reflecting categories, guiding API usage, and searching for APIs, helping API developers effectively classify and manage API services.

  1. Log in to the DataArts Studio console by following the instructions in Accessing the DataArts Studio Instance Console.
  2. On the DataArts Studio console, locate a workspace and click DataArts DataService.
  1. In the left navigation pane, choose an edition, for example, Exclusive Edition. The Overview page is displayed.
  2. Choose API Development > API Catalogs and click .

    In the dialog box displayed, enter an API catalog name, and click OK.

  3. Right-click the API catalog and select Edit or Delete to edit or delete the API catalog.

Configuring Basic API Information

  1. On the DataArts Studio console, locate a workspace and click 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. You can select an API catalog you have created by referring to 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 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

    Protocol used to transmit requests. 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. 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 security authentication modes. The three modes differ in how the API is called. You are advised to select App authentication, which is more secure that the other two modes.
    • App authentication: After the API is authorized to an application, the key pair (AppKey and AppSecret) of the application is used for security authentication. The API can be called using an SDK or API calling tool. This authentication mode is highly secure and recommended.
    • IAM authentication: After the API is authorized to the current account or another account, the user token obtained from IAM is used for security authentication. The API can be called using an API invoking tool. The security level of this mode is medium.
    • Non-authentication: This mode allows all users to access APIs, which may pose security risks. It is recommended only for testing APIs. In this mode, no authentication information is required. The security level is low. You can use an API invoking tool or browser to directly call the API.

    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, whether a null value is allowed, 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 transferred by the API caller. It is supported only when Security Authentication is App authentication. The value of a static parameter is determined during API authorization. (If the parameter value is not set during authorization, the default value of the API input parameter is used when the API is called using an SDK, and an error is reported indicating that the static parameter value is missing when the API is called using an API tool.)
    • 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.
    • Whether the parameter is mandatory, whether a null value is allowed, and default value
      • If the parameter is mandatory, it must be transferred for accessing the API.
      • If this parameter is not mandatory and if it is not transferred during API access, the default value will be used. If the parameter is not transferred and no default value is available, null will be used if it is allowed and this parameter will be ignored if null is not allowed.
    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 read, and offsetValue indicates the number of skipped data records (offset). They have default values.

    • 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 you select the custom pagination mode, you must add the pagination logic when writing SQL statements to prevent cluster exceptions that may occur when the API is used to query a large amount of data.
      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. In addition, you can enable Return Total Records. Then the total number of script execution results will be returned.

    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 2 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 3 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.
    • Whether the parameter is optional for calling the API. If you select it, this parameter is optional. You can use the value of pre_order_by to configure whether this parameter is used for ranking. If you do not select it, this parameter is mandatory. Even if this parameter is not set for pre_order_by, this parameter is still used for ranking.
    • The ranking mode can be ascending, descending, or custom. The default ranking mode of a custom ranking parameter is ascending. You can change the ranking mode by setting pre_order_by. If the ranking mode of a parameter is ascending or descending, the ranking mode cannot be changed using pre_order_by. If the value of pre_order_by is different from the ranking mode set, an error is reported during configuration debugging or API calling.
    • If there are multiple ranking parameters, when the first ranking parameter is the same, the subsequent parameters are used for ranking. Different from the configuration mode, the sequence of the ranking parameters is irrelevant to the sequence in which they are added. Instead, the sequence needs to be customized using a SQL script and cannot be adjusted using pre_order_by.

    Note that the ranking fields of an API created using a script/MyBatis must be added to the SQL statement using ORDER BY so that they can take effect. You can click to add a ranking parameter to the SQL statement. When adding the ORDER BY parameter, you only need to associate the field name. The sequence of multiple ranking fields is defined by the script. You cannot use ASC or DESC to set the sequence in the script. Ranking parameters that are not added to the SQL statement do not take effect even if they are defined in pre_order_by.

    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 in sequence 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 4 Adding ranking parameters

    Click Test SQL under the script editing window, enter the values for input parameters 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 all configured ranking parameters. The custom ranking mode is ascending by default. 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 you select Transfer Value, test results are sorted by the value of pre_order_by.

    You can change the value of pre_order_by as follows:
    • Delete an optional ranking parameter. The parameter is no longer used for ranking.
    • Change the ranking mode of a ranking parameter whose ranking mode is custom to ascending or descending. The ranking parameter is sorted based on the new ranking mode.
    The value of pre_order_by cannot be changed in any of the following ways. Otherwise, the change does not take effect or an error is reported during API calling.
    • If a mandatory ranking parameter is deleted, the parameter is still used for ranking and the deletion does not take effect.
    • If the sequence of ranking parameters is adjusted, the sequence of the parameters is still the same as that in the SQL statement. The adjustment does not take effect.
    • If you change the ranking mode of a ranking parameter whose ranking mode is ascending or descending, the API cannot be called. Such a change is not allowed.

    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 5 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 6 Setting values for input parameters

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

    The default value of pre_order_by is provided by the system based on all configured ranking parameters. The custom ranking mode is ascending by default. 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 you select Transfer Value, test results are sorted by the value of pre_order_by.

    You can change the value of pre_order_by as follows:
    • Delete an optional ranking parameter. The parameter is no longer used for ranking.
    • Change the ranking mode of a ranking parameter whose ranking mode is custom to ascending or descending. The ranking parameter is sorted based on the new ranking mode.
    The value of pre_order_by cannot be changed in any of the following ways. Otherwise, the change does not take effect or an error is reported during API calling.
    • If a mandatory ranking parameter is deleted, the parameter is still used for ranking and the deletion does not take effect.
    • If the sequence of ranking parameters is adjusted, the sequence of the parameters is still the same as that in the SQL statement. The adjustment does not take effect.
    • If you change the ranking mode of a ranking parameter whose ranking mode is ascending or descending, the API cannot be called. Such a change is not allowed.
    Figure 7 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 8 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.
    • If the total duration of API query and response exceeds the default value 60 seconds, a timeout error is reported.
    • If the test fails, modify parameters based on the error message and try again.

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