Create an API in script mode

1. Select a table

Double-click the API to open its editing page. In the Table section, configure parameters such as Data Source Type, Data Source Name, and Data Source Environment.

The required parameters depend on the data source type. See the UI for details.

2. Write SQL query

In the Edit Query SQL section, enter the SQL query statement.

• If you select the Basic SQL mode, only basic SQL syntax is supported.

  SELECT
  name,
  addr AS address,
  SUM(num) as total_num
  FROM
  table_name
  WHERE
  user_id =${uid}

  Note

  Fields in the SELECT clause define the response parameters of the API. Parameters in the WHERE clause define the request parameters of the API. Use the ${} format to identify request parameters.

  Follow these rules when writing the SQL statement:

  • Single-table queries, multi-table join queries, and nested queries within the same data source are supported.

  • The following are not supported:

    • Multiple SQL statements.

    • Comments.

    • Non-SELECT statements, such as INSERT, UPDATE, and DELETE.

    • The SELECT * statement. You must explicitly specify the columns to query.

    • Do not enclose the ${param} variable in quotation marks. For example, '${id}' and 'abc${xyz}123' are not supported. To do this, use the concat('abc', ${xyz}, '123') function.

    • Optional parameters.

  • If a column name in the SELECT clause is prefixed with a table name (for example, t.name), you must specify an alias for the response parameter (for example, t.name as name).

  • If you use an aggregate function (such as min, max, sum, or count), you must specify an alias for the response parameter. For example, sum(num) as total_num.

  • The ${param} variables in an SQL statement, including those in strings, are replaced with request parameters. When a ${param} variable is preceded by a backslash (\), it is treated as a literal string.

• If you select the Advanced SQL mode, MyBatis tags are supported.

  SELECT id, name, create_name
  FROM t_parameter
  <where>
  <if test="'list!=null'">
      id in (
          <foreach collection="list" open="(" close=")" separator="," item="id_num">
              ${id_num}
          </foreach>
      )
  </if>
  </where>

  The supported MyBatis tags in Advanced SQL are if, choose, when, otherwise, trim, foreach, and where. You can use these tags to implement complex query logic, such as null value checks, multi-value traversals, dynamic table queries, dynamic sorting, and aggregations. For code samples in common scenarios, see Advanced SQL (MyBatis syntax) examples.

  When using special characters in MyBatis tags, you must escape these characters. The following table lists common escape 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. Configure request parameters

In the right-side pane of the API editing page, click Request Parameters and configure the parameters.

If you use the Advanced SQL mode, manually add all request parameters from the SQL script to the list. This ensures the API documentation accurately reflects the required parameters.

4. Configure response parameters

In the right-side pane of the API editing page, click Response Parameters and configure the parameters.

1. Configure the response parameters.

   If you use the Advanced SQL mode, manually add all response parameters from the SQL script to the list. This ensures the API documentation accurately reflects the returned data.

   Parameter	Description
   Parameter Name	The name of the response parameter. The name can be up to 64 characters in length, must start with a letter, and can contain letters, digits, underscores (_), and hyphens (-).
   Parameter Type	Supported data types are STRING, INT, LONG, FLOAT, Double, and Boolean.
   Sample Value	An example value for the response parameter.
   Description	A brief description of the response parameter.

2. In the Advanced Settings section, specify whether to enable Pagination for Return Results.

   • If you do not enable Pagination for Return Results, the API returns a maximum of 2,000 records by default.

   • Enable Pagination for Return Results if the API might return more than 2,000 results. After it is enabled, you can go to the Resource Group for DataService Studio page in the right-side navigation pane to set the maximum number of entries per page based on the resource group type.

   Note

   If Pagination for Return Results is enabled on the Response Parameters tab of the API editing page, and you use a SQL statement with a LIMIT clause in the Edit Query SQL section, the LIMIT clause is ignored, and the number of returned results is determined by the Pagination for Return Results settings.

   After you enable pagination, the following common parameters are automatically added:

   • Common request parameters

     • returnTotalNum: determines whether to return the total number of data records in a single request.

     • pageNum: the current page number.

     • pageSize: the number of entries per page.

   • Common response parameters

     • pageNum: the current page number.

     • pageSize: the number of entries per page.

     • totalNum: the total number of records.

   Note

   An API can have no request parameters, but in that case, you must enable Pagination for Return Results.

5. Configure filters

If you need to preprocess the request parameters or post-process the query results, click Filter in the right-side navigation pane of the API editing page. Select Use Pre-filter or Use Post-filter as needed. Select a Function Type, and then select one or more functions from the pre-filter or post-filter drop-down list. Functions are executed in the order they are added. After the configuration is complete, you can click Preview Responses Returned by API Operation to check whether the results meet your expectations. For more information about how to create and use filters, see Create an Aviator function and Create a Python function.

6. Configure service resource group

1. In the right-side navigation pane of the API editing page, click Resource Group for DataService Studio. In the Resource Group Type section, configure the type of resource group to use when calling the API.

   You can select Exclusive Resource Group for DataService Studio or Shared Resource Group for DataService Studio. For an Exclusive Resource Group for DataService Studio, you can select a target resource group name from the list. A Shared Resource Group for DataService Studio is automatically managed by DataWorks and cannot be selected by name.

   Note

   • If the target resource group name is not in the list, go to the Resource Groups page, find the resource group, and click Associate Workspace in the Operation column.

   • If the target resource group is in the list but cannot be selected, go to the Resource Groups page, find the resource group, and in the Operation column, click > Manage Quota . Manually set the Occupied CUs (for pay-as-you-go resource groups) or Minimum CUs (for subscription resource groups) for the Data Services purpose.

2. In the Environment Configuration area, you can set Memory, Timeout, and Maximum Number of Data Records for a Single Request.

   • The maximum timeout depends on the type of DataWorks service resource group and API Gateway instance selected:

     • shared API Gateway instance: up to 30,000 ms for a Shared Resource Group for DataService Studio, and up to 30,000 ms for an Exclusive Resource Group for DataService Studio.

     • dedicated API Gateway instance: up to 30,000 ms for a Shared Resource Group for DataService Studio, and up to 90,000 ms for an Exclusive Resource Group for DataService Studio.

     Note

     An API's response time depends on its SQL execution time. To prevent request failures, set the API Timeout to a value greater than the expected execution time.

   • The maximum entries per page depends on the type of service resource group selected:

     • If you select a Shared Resource Group for DataService Studio, the maximum number of data records per page with pagination enabled is 2,000.

     • If you select an Exclusive Resource Group for DataService Studio, the maximum number of data records per page with pagination enabled is 10,000.

     Note

     There is no upper limit on the total number of results an API can return.

7. Save and submit

Click the Save icon in the toolbar. After you save the API, the selected resource group is used for testing.