To meet the personalized query requirements of advanced users, DataService Studio provides a code editor that you can use to customize the SQL statements of APIs. The code editor allows you to use table join queries, complex queries, and aggregate functions. This topic describes how to create an API in the code editor.

Prerequisites

A data source is configured on the Data Source page. For more information, see Configure a data source.

Create an API

  1. Go to the DataService Studio page.
    1. Log on to the DataWorks console.
    2. In the left-side navigation pane, click Workspaces.
    3. In the top navigation bar, select the region in which the workspace that you want to manage resides. Find the workspace and click DataService Studio in the Actions column.
  2. On the Service Development page, move the pointer over the Create icon and choose New API > Generate API.
    You can also select a business process, right-click API, and then choose New > Generate API.
  3. In the Generate API dialog box, configure the parameters as required.
    Script Mode
    Parameter Description
    API mode The mode used to create the API. Valid values: Wizard Mode and Script Mode. In this example, select Script Mode.
    SQL Mode The SQL mode. Valid values:
    • Basic SQL: Use basic SQL statements to implement the query logic. This mode provides the SQL capability the same as that in earlier versions.
    • Advanced SQL: Use SQL statements with MyBatis tags to implement the query logic. This mode supports the following tag types: if, choose, when, otherwise, trim, foreach, and where.
    API Name The name of the API. The name must be 4 to 50 characters in length, and can contain letters, digits, and underscores (_). The name must start with a letter.
    API Path The path of the API, such as /user.
    Protocol The protocol used by the API. Valid values: HTTP and HTTPS.

    If you want to call the API by using HTTPS, you must bind an independent domain name to the API in the API Gateway console after the API is published to API Gateway. You must also upload a Secure Sockets Layer (SSL) certificate in the API Gateway console. For more information, see Enable HTTPS for an API operation.

    Request Method The request method. Valid values: GET and POST.
    Note If you select GET for the Request Method parameter, you can select only QUERY for the Parameter Position parameter. If you select POST for the Request Method parameter, you can select QUERY or BODY for the Parameter Position parameter.
    Response Content Type The format of the data returned by the API. Set the value to JSON.
    Visible Range The range of users to whom the API is visible. Valid values:
    • Work Space: The API is visible to all members in the current workspace.
    • Private: The API is visible only to its owner and permissions on the API cannot be granted to other members.
      Note If you set this parameter to Private, other members in the workspace cannot view the API in the API list.
    Label Select tags from the Label drop-down list. For more information, see Manage API tags.
    Note A tag can be up to 20 characters in length and can contain letters, digits, and underscores (_). You can set at most five tags for an API.
    Description The description of the API. The description can be up to 2,000 characters in length.
    Target Folder The folder that stores the API.
  4. Click OK.

Configure the API

  1. Double-click the API in the API list. On the tab that appears, set the Datasource Type and Datasource Name parameters in the Select Table section.
    Select Table section
    Note A data source must be selected first, and only table join queries in the same data source are supported.
  2. In the Environment Configuration section, set the Memory and Function Timeout parameters.
    Environment Configuration section
  3. In the Edit query SQL section, enter an SQL statement for querying data.
    • If you set the SQL Mode parameter to Basic SQL, you can enter only a basic SQL statement. Edit query SQL section
      Note The SELECT statement specifies the parameters that the API returns. The WHERE clause specifies the request parameters of the API. You must use ${} to interpolate a request parameter.
      Follow these rules when you enter an SQL statement:
      • Single-table queries, table join queries, and nested queries in the same data source are supported.
      • The SQL statement must meet the following requirements:
        • You can enter only one SQL statement.
        • Only the SELECT clause is supported. Other clauses such as INSERT, UPDATE, or DELETE are not supported.
        • The SELECT \* clause is not supported. You must specify the columns to be queried.
        • ${param} cannot be enclosed in single quotation marks (' '). For example, '${id}' or 'abc${xyz}123' is not allowed. If necessary, you can use concat('abc', ${xyz}, '123') instead.
        • Parameters cannot be configured as optional.
        • ${param} is not allowed in comments. For example, --${id} is not allowed.
      • If the name of the column that the SELECT clause specifies has a table prefix, such as t.name, you must create an alias for the corresponding response parameter. For example, you can specify t.name as name.
      • If you use an aggregate function, such as min, max, sum, or count, you must create an alias for the corresponding response parameter. For example, you can specify sum(num) as total\_num.
      • ${param} in the SQL statement, including ${param} in strings, is regarded as request parameters and replaced. If an escape character (\) is placed before ${param}, ${param} is processed as a common string.
    • If you set the SQL Mode parameter to Advanced SQL, you can enter an SQL statement with MyBatis tags.

      This mode supports the following MyBatis tag types: if, choose, when, otherwise, trim, foreach, and where.

  4. In the right-side navigation pane, click Request Parameters. In the Request Parameters pane, configure the parameters as required.
    If you set the SQL Mode parameter to Advanced SQL, you must manually add all request parameters that are specified in the SQL statement to the parameter list. This ensures that the parameters that are described in the API details are consistent with the parameters that are used. Request Parameters
    Parameter Description
    Parameter Name The name of the request parameter. The name can be up to 64 characters in length, and can contain letters, digits, underscores (_), and hyphens (-). The name must start with a letter.
    Parameter Type The type of the request parameter. Valid values: STRING, INT, LONG, FLOAT, DOUBLE, and BOOLEAN.
    Parameter Position The position of the request parameter. Valid values: QUERY and BODY.
    Note If you set the Parameter Position parameter to BODY for one or more request parameters, you must set the Content-Type parameter to define the format of the request parameters in the request body.
    Valid values of Content-Type:
    • application/json;charset=utf-8: the JSON format.
    • application/xml;charset=utf-8: the XML format.
    • application/x-www-form-urlencoded;charset=utf-8: the FORM format.
    Required Specifies whether the request parameter is required.
    Example Value The example value of the request parameter.
    Default Value The default value of the request parameter.
    Description The description of the request parameter.
    To preprocess the request parameters of the API, select Use prefilter in the Advanced Settings section. For more information, see Use prefilters.
    Note
    • Only workspaces of DataWorks Professional Edition or a more advanced edition in the China (Shanghai) region support prefilters.
    • To improve the match efficiency, specify an indexed field as a request parameter.
    • To make sure that API callers understand the details about the API, we recommend that you specify information such as the example value, default value, and description for each parameter of the API.
  5. In the right-side navigation pane, click Response Parameters. In the Response Parameters pane, configure the parameters as required.
    If you set the SQL Mode parameter to Advanced SQL, you must manually add all response parameters that are specified in the SQL statement to the parameter list. This ensures that the parameters that are described in the API details are consistent with the parameters that are used. Response Parameters
    Parameter Description
    Parameter Name The name of the response parameter. The name can be up to 64 characters in length, and can contain letters, digits, underscores (_), and hyphens (-). The name must start with a letter.
    Parameter Type The type of the response parameter. Valid values: STRING, INT, LONG, FLOAT, DOUBLE, and BOOLEAN.
    Example Value The example value of the response parameter.
    Description The description of the response parameter.

    You can select Pagination and Filter in the Advanced Settings section.

    Select Pagination based on your needs.
    • If you do not select Pagination, the API returns up to 2,000 records.
    • If the API returns more than 2,000 records, we recommend that you select Pagination.
    Note If you select Pagination in the Response Parameters pane, the number of the returned records can be more than 2,000 even if you set the limit for the returned records in the Edit query SQL section.
    The following common parameters are available when you select Pagination:
    • Common request parameters
      • pageNum: the number of the page to return.
      • pageSize: the number of entries to return on each page.
    • Common response parameters
      • pageNum: the page number of the returned page.
      • pageSize: the number of entries returned per page.
      • totalNum: the total number of returned entries.
    If you need to process the query results that are returned by the API, select Filter. For more information, see Use post filters.
    Note
    • Only workspaces of DataWorks Professional Edition or a more advanced edition in the China (Shanghai) region support post filters.
    • Request parameters are optional for an API. If you do not specify request parameters for an API, you must select Pagination.
  6. Configure an exclusive service resource group for DataService Studio.
    1. In the right-side navigation pane, click Resource Groups for DataService Studio. Select Exclusive Resource Group for DataService Studio or Shared Resource Group for DataService Studio for the Scheme parameter. If you select Exclusive Resource Group for DataService Studio, select an exclusive resource group from the drop-down list. If you select Shared Resource Group for DataService Studio, you do not need to select a shared resource group. DataWorks automatically assigns a shared resource group to generate the API.
      Note If you cannot select an exclusive resource group in the drop-down list, log on to the DataWorks console and bind the exclusive resource group to your workspace.
  7. Click the Save icon in the toolbar. After the API is saved, the selected resource group takes effect during the test.
    After the API is configured, you can test it. For more information, see Test an API.

    After the test succeeds, click Submission in the upper-right corner.

    In the right-side navigation pane of the API configuration tab, click Version. Find the API version that you want to publish and click Application for publication in the Actions column to go to the application page. You can use the default application type and enter the reason for the application. Then, submit the application.
    Note If you have defined an approval policy in DataWorks Approval Center, the API must be approved through the approval process before the API can be published. For more information, see Overview.

    After the API is published, the configuration of the resource group for DataService Studio takes effect when the API is called.

    On the Service Development tab, you can find the registered API in the left-side navigation pane and manage the API. For example, you can clone or delete the API. On the Service Management tab, you can find the published API in the API list and view the details of the published API. For more information, see Manage APIs.