If you have personalized query requirements, you can use the code editor provided by DataWorks to create an API. When you use the code editor to create an API, you can customize SQL statements for the API. The code editor allows you to use table join queries, complex queries, and aggregate functions. This topic describes how to create an API by using the code editor.

Prerequisites

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

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, move the pointer over the More icon in the Actions column, and then select DataService Studio.

Create an API

  1. In the Service Development pane, move the pointer over the Create icon icon and choose Create API > Generate API.
    You can also select a business process, right-click API, and then choose Create API > Generate API.
  2. In the Generate API dialog box, configure the parameters.
    Code editor
    Parameter Description
    API Mode The mode that is 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 that is 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 that is 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 group to which the API belongs in the API Gateway console after the API is published to API Gateway. You must also upload an 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 Position parameter. If you select POST for the Request Method parameter, you can select QUERY or BODY for the 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 Create and manage API tags.
    Note A tag can be up to 20 characters in length and can contain letters, digits, and underscores (_). You can add up to five tags to an API.
    Description The description of the API. The description can be up to 2,000 characters in length.
    Destination Folder The folder in which you want to store the API.
  3. Click Determine.

Configure the API

  1. Double-click the API in the API list. On the tab that appears, configure the Datasource Type, Datasource Name, and Data Source Environment parameters in the Select Table section.
    The parameters that you need to configure vary based on the selected data source type. The parameters in the console prevail.
    Note
    • You must add a data source on the Data Source page first. You can enter a table name in the Table Name field to search for the table that you want to use.
    • A data source must be selected first, and table join queries are supported only in the same data source.
    • For a workspace in standard mode, you can select Production or Environment for the Data Source Environment parameter. For more information, see Differences between workspaces in basic mode and workspaces in standard mode.
    • If you select a MaxCompute table for an API, you can select Acceleration Service or MCQA for the Acceleration Method parameter for acceleration. Acceleration Service is an acceleration method provided by DataWorks and MCQA is an acceleration method provided by MaxCompute. If you select Acceleration Service, you must make sure that acceleration items are created. For more information, see Acceleration solutions for API-based data queries.
  2. In the Edit Query SQL section, write an SQL statement for querying data.
    • If you set the SQL Mode parameter to Basic SQL, you can write 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 the following rules when you write an SQL statement:
      • Single-table queries, table join queries, and nested queries are supported in the same data source.
      • The SQL statement must meet the following requirements:
        • You can write only one SQL statement.
        • An SQL comment is not allowed.
        • Only the SELECT statement is supported. Other statements such as INSERT, UPDATE, or DELETE are not supported.
        • The SELECT \* statement 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.
      • If the name of a column that the SELECT statement specifies has a table prefix, such as t.name, you must set 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 set 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 write an SQL statement with MyBatis tags. Mybatis

      The Advanced SQL mode supports the following types of tags: if, choose, when, otherwise, trim, foreach, and where. You can use SQL statements with MyBatis tags to implement complex query logic, such as null parameter checking, cyclic traversal over multiple values, dynamic queries, dynamic sorting, and aggregate analysis. For more information about sample code for common scenarios, see Sample code for the advanced SQL syntax used for creating an API in script mode.

      If the written SQL statement with MyBatis tags contains special characters, you must escape the special characters. The following table describes the escape characters that correspond to the common special characters.
      Special character Escape character Description
      > > Greater than
      >= >= Greater than or equal to
      < &lt; Less than
      <= &lt;= Less than or equal to
      & &amp; And
      ' &apos; Single quotation mark
      " &quot; Double quotation mark
  3. In the right-side navigation pane, click the Request Param tab. On the Request Param tab, configure the parameters based on your business requirements.
    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.
    Note
    • Before you preview the result, you must configure the information such as the example value, default value, and description for each request parameter of the API.
    • To improve the match efficiency, specify an indexed field as a request parameter.
    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.
    Type The data type of the request parameter. Valid values: STRING, INT, LONG, FLOAT, DOUBLE, and BOOLEAN.
    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.
  4. In the right-side navigation pane, click the Response Param tab. On the Response Param tab, configure the parameters.
    1. Configure the response parameters.
      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.
      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.
      Type The data 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.
    2. Configure the Pagination parameter in the Advanced Settings section.
      • If you do not select Pagination, the API returns up to 2,000 records by default.
      • If more than 2,000 records may be returned, we recommend that you select Pagination. If you select Pagination, you can go to the Resource Group tab to configure the Maximum Number of Data Records for a Single Request parameter based on the resource group you selected.
      Note If you select Pagination on the Response Param tab, the number of records to return is subject to the pagination settings. Even if you set a limit for the number of records to return in the Edit Query SQL section, this limit does not take effect.
      The following common parameters are available if 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 entries returned.
      Note Request parameters are optional for an API. If you do not specify request parameters for an API, you must select Pagination.
  5. Configure a filter.
    If you need to process the request parameters of an API or process the results returned by an API, you can configure a filter for the API by performing the following steps: In the right-side navigation pane, click Filter. In the Filter panel, select Use Pre-filter or Use Post-filter based on your needs. After you specify the Function Type parameter, select one or more functions from the drop-down list next to the Use Pre-filter or Use Post-filter check box. If you select multiple functions, the functions are used to process parameters based on the sequence in which they are selected. Then, you can click Preview Responses Returned by API Operation and check whether the processing results of the selected functions meet your expectations. For more information about how to create and use a filter, see Create an Aviator function and use the Aviator function as a filter and Create and use Python functions as a filter.
    Note
    • If you want to use a Python function as a filter, you must activate DataWorks Professional Edition or a more advanced edition and use the shared resource group for DataService Studio.
    • If you want to use an Aviator function as a filter, all DataWorks editions are supported, but you must use an exclusive resource group for DataService Studio.
    • If the function that you want to use is not available in the drop-down list, check whether the function is published. If the function is not published, publish the function. Alternatively, you can create and publish a new function. For more information, see Publish a function.
    Filter panel
  6. Configure a resource group for DataService Studio.
    1. In the right-side navigation pane, click the Resource Group tab. In the Resource Group Type section, configure the Schema parameter.
      You can 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 Exclusive Resource Group for DataService Studio drop-down list. If you select Shared Resource Group for DataService Studio, you do not need to select a resource group. DataWorks automatically uses the shared resource group to generate the API.
      Note If the exclusive resource group for DataService Studio that you want to select is not displayed in the drop-down list, you can go to the Resource Groups page in the DataWorks console, and then associate the exclusive resource group with your workspace on the Exclusive Resource Groups tab.
    2. In the Environment Configuration section, you can configure the Memory, Function Timeout, and Maximum Number of Data Records for a Single Request parameters.
      • The maximum value of the Function Timeout parameter varies based on the type of the resource group you select and the type of the API Gateway instance you use:
        • Shared API Gateway instances: The maximum value of this parameter is 30000.
        • Dedicated API Gateway instances: If you select Shared Resource Group for DataService Studio, the maximum value of this parameter is 30000. If you select Exclusive Resource Group for DataService Studio, the maximum value of this parameter is 90000.
      • The maximum value of the Maximum Number of Data Records for a Single Request parameter varies based on the type of the resource group you select:
        • If you select Shared Resource Group for DataService Studio, the maximum value of this parameter is 2000.
        • If you select Exclusive Resource Group for DataService Studio, the maximum value of this parameter is 10000.
  7. Click the Save icon 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 View, delete, move, and clone an API.