DataWorks:Create an API by using the code editor

Step 1: Select a data source

On the API configuration tab, set Datasource Type, Datasource Name, and Data Source Environment.

Table join queries are supported only within the same data source. For workspaces in standard mode, you can set Data Source Environment to Production or Environment. See Differences between basic mode and standard mode workspaces. For MaxCompute tables, select Acceleration Service (DataWorks acceleration) or MCQA (MaxCompute Query Acceleration) for faster query performance. Acceleration Service requires acceleration items to be created in advance. See Acceleration solutions for API-based data queries.

Step 2: Write the query SQL

In the Edit Query SQL section, write the SQL statement for your API.

Basic SQL rules

All SQL in Basic SQL mode must follow these rules:

Rule	Correct	Incorrect
One statement only; no comments	SELECT id, name FROM users WHERE id = ${id}	Two statements or -- comment in the query
SELECT only; no data modification	SELECT id FROM orders	INSERT INTO ..., UPDATE ..., DELETE FROM ...
Specify columns explicitly	SELECT id, name FROM users	SELECT * FROM users
Do not wrap ${param} in single quotes	concat('prefix_', ${id})	'${id}' or 'prefix_${id}'
Use an alias when the column has a table prefix	SELECT t.name AS name FROM users t	SELECT t.name FROM users t
Use an alias for aggregate functions	SELECT SUM(amount) AS total_amount FROM orders	SELECT SUM(amount) FROM orders
Parameters cannot be configured as optional	—	Marking a parameter as optional

Use ${param} to define request parameters. To treat the placeholder as a literal string, escape it with a backslash: \${param}.

Single-table queries, table joins, and nested queries are all supported within the same data source.

Advanced SQL rules

Advanced SQL supports MyBatis tags for dynamic query logic: null checks, conditional filters, loop traversal, and dynamic sorting.

When using MyBatis tags, escape special characters in SQL conditions:

Special character	Escape sequence	Meaning
>	>	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

For MyBatis tag examples, see Sample code for the Advanced SQL syntax.

In Advanced SQL mode, manually add all request parameters used in the SQL statement to the request parameter list. This keeps the API definition consistent with the SQL logic.

Step 3: Configure request parameters

In the right-side panel, click the Request Param tab. Add and configure the parameters your API accepts.

To improve query performance, use indexed fields as request parameters.

Step 4: Configure response parameters

In the right-side panel, click the Response Param tab. Add the fields your API returns.

In Advanced SQL mode, manually add all response parameters from the SQL statement to this list.

Pagination

By default, the API returns up to 2,000 records per request. If the result set may exceed this limit, enable Pagination for Return Results.

When pagination is enabled, any SQL-level LIMIT clauses are ignored—the pagination settings take effect instead. Go to the Resource Group tab to configure the maximum number of records per request.

Enabling pagination adds the following parameters automatically:

Request parameters added:

Response parameters added:

If an API has no request parameters, enable Pagination for Return Results.

Step 5: Configure a filter (optional)

Filters let you pre-process request parameters before the SQL query runs (pre-filter) or post-process results before they are returned (post-filter).

In the right-side panel, click Filter. Select Use Pre-filter or Use Post-filter, set Function Type, and select one or more functions. If you select multiple functions, they are applied in the order selected. Click Preview Responses Returned by API Operation to verify the output.

Python functions as filters require DataWorks Professional Edition or higher, and the Shared Resource Group for DataService Studio.

Aviator functions as filters work with all DataWorks editions, but require an exclusive resource group.

If a function does not appear in the drop-down list, check that it is published. See Publish a function.

For instructions on creating filters, see Create an Aviator function and Create a Python function.

Step 6: Configure a resource group

1. In the right-side panel, click the Resource Group tab. Under Resource Group Type, set the Scheme parameter:

   • Exclusive Resource Group for DataService Studio: select a specific exclusive resource group from the drop-down list.

   • Shared Resource Group for DataService Studio: no selection needed. DataWorks uses the shared resource group automatically.

   - If the resource group you want is not in the list, go to the Resource Groups page, find the group, and click Associate Workspace in the Actions column. - If the resource group appears in the list but cannot be selected, go to the Resource Groups page, click > Manage Quota, and configure Occupied CUs (pay-as-you-go) or Minimum CUs (subscription) for DataService Studio.

   

2. Under Environment Configuration, set the following parameters:

   • Memory: allocated memory for the API runtime.

   • Function Timeout: the maximum execution time in milliseconds. Set this based on the expected SQL execution time to avoid request failures.

     Instance type	Scheme	Maximum timeout
     Shared API Gateway instance	Any	30,000 ms
     Dedicated API Gateway instance	Shared Resource Group for DataService Studio	30,000 ms
     Dedicated API Gateway instance	Exclusive Resource Group for DataService Studio	90,000 ms

   • Maximum Number of Data Records for a Single Request: the upper limit of records returned per request when pagination is enabled.