To meet the personalized query requirements of advanced users, DataService Studio provides a code editor for you 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 connection is configured on the Data Source page under Workspace Management. For more information, see Configure a connection.

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 where the target workspace resides. Find the target workspace and click DataService Studio in the Actions column.
  2. On the Service Development tab, move the pointer over the Create icon and choose API > Generate API.
    You can also click the required business process, right-click API, and then choose New > Generate API.
  3. In the Generate API dialog box, set the parameters as required.
    Code editor
    Parameter Description
    API mode Valid values: Wizard Mode and Script Mode. In this example, select Script Mode.
    SQL Mode Valid values: Basic SQL and Advanced SQL.
    • Basic SQL: uses basic SQL statements to write the query logic. This mode provides the SQL capability the same as that in earlier versions.
    • Advanced SQL: uses SQL statements with MyBatis tags to write the query logic. This mode supports the following tags: if, choose, when, otherwise, trim, foreach, and where.
    API Name The name must be 4 to 50 characters in length and can contain letters, digits, and underscores (_). It must start with a letter.
    API Path The path for storing the API, such as /user.
    Protocol Valid values: HTTP and HTTPS.

    If you need 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. In addition, you must upload a Secure Sockets Layer (SSL) certificate in the API Gateway console. For more information, see Enable HTTPS for an API operation.

    Request Method Valid values: GET and POST.
    Response Content Type Set the value to JSON.
    Visible Range Valid values: Work Space and Private.
    • 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 users.
      Note If you set the Visible Range parameter to Private, the API is visible only to you in the API list. 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 for storing the API.
  4. Click OK.

Configure the API

  1. Double-click the API. On the configuration tab of the API, set the Datasource Type and Datasource Name parameters in the Select Table section.
    Select Table
    Note You must select a connection first. DataService Studio supports table join queries only under the same connection.
  2. In the Environment Configuration section, set the Memory and Function Timeout parameters.
    Environment Configuration
  3. In the Edit query SQL section, enter the 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
      Note The SELECT clause specifies the response parameters of the API. The WHERE clause specifies the request parameters of the API. You must use ${} to specify a request parameter.
      Follow these rules when you enter the SQL statement:
      • Single-table queries, table join queries, and nested queries under the same connection 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, and 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}' and 'abc${xyz}123' are 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, 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, 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.

      The Advanced SQL mode supports the following tags: if, choose, when, otherwise, trim, foreach, and where.

  4. In the right-side navigation pane, click the Request Parameters tab. Set 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 actually 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 (-). It must start with a letter.
    Parameter Type Valid values: STRING, INT, LONG, FLOAT, DOUBLE, and BOOLEAN.
    Required Specifies whether the request parameter is required.
    Example Value The sample value of the request parameter.
    Default Value The default value of the request parameter.
    Description The description of the request parameter.
    If you need to preprocess the request parameters of the API, select Use prefilter in the Advanced Settings section. For more information, see Use prefilters.
    Note
    • Prefilters are supported only in the China (Shanghai) region for DataWorks Professional Edition or more advanced editions.
    • We recommend that you set an indexed field as a request parameter.
    • To make it easier for API callers to know the details about the API, we recommend that you specify information such as the sample value, default value, and description for each parameter of the API.
  5. In the right-side navigation pane, click the Response Parameters tab. Set 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 actually 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 (-). It must start with a letter.
    Parameter Type Valid values: STRING, INT, LONG, FLOAT, DOUBLE, and BOOLEAN.
    Example Value The sample value of the response parameter.
    Description The description of the response parameter.

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

    Take note of the following items when you set the Pagination parameter:
    • If you do not select Pagination, the API returns a maximum of 2,000 records by default.
    • If the API may return more than 2,000 records, we recommend that you select Pagination.
    The following common parameters are displayed after 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
    • Post filters are supported only in the China (Shanghai) region for DataWorks Professional Edition or more advanced editions.
    • Request parameters are optional for an API. If you do not specify request parameters for an API, you must select Pagination.
  6. Click the Save icon in the top toolbar.
    After the API is configured, you can test it. For more information, see Test an API.

    After the API is tested, close the Test APIs dialog box. Click Publish in the upper-right corner of the configuration tab.

    On the Service Development tab, you can find the published API in the API list. You can view the details of the API and clone or delete the API. For more information, see Manage APIs.