Create an API from a data source (API Gateway)

Mode comparison

Choosing a mode

• Use wizard mode: for simple queries on a single data table, such as equality, fuzzy match, or range filtering. This mode provides a visual, no-code interface for API configuration.

• Use script mode: for advanced queries, such as multi-table JOINs, nested subqueries, aggregation, or dynamic conditions with optional parameters.

1. Select a data source and table

Double-click the API to open its edit page. In the Table area, configure parameters such as Data Source Type, Data Source Name, and Data Source Environment. Configuration parameters vary by data source type. Refer to the UI for the specific parameters.

2. Define the query logic

You define query logic differently in wizard mode and script mode.

3. Configure request parameters

Click Request Parameters in the right-hand pane of the API edit page to configure the parameters.

4. Configure response parameters and pagination

Click Response Parameters in the right-hand pane of the API edit page to configure the parameter name, parameter type, sample value, and description. In Advanced SQL mode, you must manually add all response parameters based on your SQL script.

In the Advanced Settings area, specify whether to enable Pagination for Return Results:

• Disabled: The API returns a maximum of 2,000 records by default.

• Enabled: You can set the maximum number of records per page on the Resource Group for DataService Studio page based on the resource group type.

When pagination is enabled, the system automatically adds the following common parameters:

• Common request parameters: returnTotalNum (whether to return the total number of records), pageNum (the current page number), and pageSize (the number of records per page).

• Common response parameters: pageNum, pageSize, and totalNum (the total number of records).

5. Configure filters (optional)

To preprocess request parameters or post-process query results, click Filter in the right-side navigation pane. Select the Use Pre-filter or Use Post-filter checkbox. Then, select a Function Type and choose a function. You can add multiple functions, which are executed in the order they are added. For more information about how to create and use filters, see Create an Aviator function and Create a Python function.

6. Configure service resource groups

In the right-side navigation pane, click Resource Group for DataService Studio to configure the resource group type for the API.

You can use an Exclusive Resource Group for DataService Studio or a Shared Resource Group for DataService Studio. For an exclusive service resource group, you can select a target resource group by name. A public service resource group is automatically maintained by DataWorks.

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

• Timeout: The timeout cannot exceed 30,000 ms for shared API Gateway instances, or 90,000 ms for exclusive service resource groups that use dedicated API Gateway instances.

  Note

  The API's response time depends on the SQL execution time. Set the timeout value high enough to accommodate this and prevent request failures due to timeouts.

• Maximum number of records per page: Up to 2,000 for a public service resource group and up to 10,000 for an exclusive service resource group. The total number of results an API can return is not limited.

Example 1: Control sort order

The value of var determines the sort order.

select col01,col02
from table_name
<choose>
    <when test='var == 1'>
    order by col01
    </when>
    <when test='var == 2'>
    order by col02
    </when>
    <when test='var == 3'>
    order by col01,col02
    </when>
    <when test='var == 4'>
    order by col02,col01
    </when>
</choose>

Request parameter: var (Type: INT, Required). Response parameters: col01, col02.

Example 2: Query different tables

Different values of var determine which table to query.

select col01
from
<choose>
 <when test='var == 1'>
 table_name01
 </when>
 <when test='var == 2'>
 table_name02
 </when>
</choose>

Example 3: Dynamic WHERE clause

Query conditions are dynamically generated based on whether the list collection is empty. If list is not null, a query condition that includes the value of the area field is generated.

SELECT area_id, area, amount
FROM table_name
<where>
	<if test='list!=null'>
	area in
		<foreach collection="list" open="(" close=")" separator="," item="area">
		#{area}
		</foreach>
	</if>
</where>

Request parameter: list (Type: STRING_LIST, Required, Example: Beijing,Hangzhou). Response parameters: area_id, area, and amount.

Optional parameters in wizard mode

Implementing an optional parameter in wizard mode is straightforward. After selecting uid and gender as request parameters in the Select Parameters area, go to the Request Parameters panel on the right and clear the Required checkbox for the gender parameter.

• Selected: You must provide a value for the parameter when calling the API. Otherwise, a parameter validation exception is thrown.

• Cleared: You can choose whether to pass a value for the parameter. If you do not pass a value, the parameter is not included as a query condition.

Example Calls:

• Scenario 1: Values are passed for both uid and gender. The system executes the following query:

  SELECT uid, gender, age_range, zodiac
  FROM ods_user_info_d
  WHERE uid = 0016359810821
  AND gender = 'Female';

• Scenario 2: A value is passed for uid, but no value is passed for gender. The system executes the following query:

  SELECT uid, gender, age_range, zodiac
  FROM ods_user_info_d
  WHERE uid = 0016359810821;

Optional parameters in script mode

In advanced SQL mode, use the MyBatis <if> tag to implement conditional logic:

SELECT uid, gender, age_range, zodiac
FROM ods_user_info_d
<where>
    <if test='gender!=null'>
    gender = ${gender}
    </if>
    and uid = ${uid}
</where>

• The <where> tag automatically handles the WHERE keyword and removes redundant AND or OR prefixes.

• The <if test='gender!=null'> tag adds the condition to the query only if you pass a non-null value for the gender parameter.

• After you configure the SQL statement, manually add the uid and gender parameters in the Request Parameters panel on the right, and clear the Required checkbox for the gender parameter.

The behavior is the same as in wizard mode: the gender condition is applied when a value is passed, and is ignored when omitted.

Optional parameter configuration