RestAPI Reader best practices
Data Integration RestAPI Reader reads data from RESTful APIs. Configure an HTTP request URL to retrieve data from a RestAPI data source, such as data in a time range, paginated data, or data obtained by looping over request parameters. RestAPI Reader converts the data to a type supported by Data Integration and passes it to the downstream Writer. This topic gives common examples.
-
This topic describes RestAPI Reader best practices. For details about the parameters in these examples, see REST API data source (HTTP).
-
For RestAPI Writer scripts, see REST API data source (HTTP) and Writer script parameters.
Background
The following table describes RestAPI Reader capabilities in DataWorks Data Integration for reading and returning data.
|
Dimension |
Supported capability |
|
Response type |
Only JSON responses are currently supported. |
|
Readable data types |
INT, BOOLEAN, DATE, DOUBLE, FLOAT, LONG, and STRING are supported. |
|
Request method |
HTTP GET and POST are supported. |
|
Authentication method |
No authentication or one of three authentication methods is supported: Basic Auth, Token Auth, or Aliyun API Signature. Choose the method supported by your data source API and configure its authentication parameters.
|
Practice 1: Read data from an API by time range
Example scenario: API definition
This example reads a RESTful API and writes its data to a partitioned MaxCompute table. The sample API is a self-hosted test GET API that returns data within the time range supplied in its request. Details:
Adjust the configuration to match your own API. The sample API only demonstrates the procedure.
-
Sample request:
http://TestAPIAddress:Port/rest/test2?startTime=<StartTime>&endTime=<EndTime>startTime and endTime are request parameters that specify the time range to read.
-
Sample response:
{ "status": "success", "totalNum": 187, "data": [ { "axis": "series1", "value": 9191352, "createTime": "2023-01-04 00:07:20" }, { "axis": "series1", "value": 6645322, "createTime": "2023-01-04 00:14:47" }, { "axis": "series1", "value": 2078369, "createTime": "2023-01-04 00:22:13" }, { "axis": "series1", "value": 7325410, "createTime": "2023-01-04 00:29:30" }, { "axis": "series1", "value": 7448456, "createTime": "2023-01-04 00:37:04" }, { "axis": "series1", "value": 5808077, "createTime": "2023-01-04 00:44:30" }, { "axis": "series1", "value": 5625821, "createTime": "2023-01-04 00:52:06" } ] }The returned data is under data and has three fields:
axis,value,createTime. -
Example API test: Send a GET request in Postman or another API test tool with startTime and endTime, such as 2023-01-04 00:00:00 to 2023-01-04 23:59:59. The API returns 200 OK, totalNum is 187, and data contains records from that range.
Prepare: Create a partitioned MaxCompute table
This example synchronizes API data to a partitioned MaxCompute table. First, create a partitioned table to store it.
With overwrite mode, partitioned tables allow reruns without duplicating data and facilitate later analysis.
Create the table with:
CREATE TABLE IF NOT EXISTS ods_xiaobo_rest2
(
`axis` STRING
,`value` BIGINT
,`createTime` STRING
)
PARTITIONED BY
(
ds STRING
)
LIFECYCLE 3650;
If you use DataWorks Standard Edition and publish the partitioned table to production, you can later find it in Data Map.
Configure a synchronization task
-
Add a RestAPI data source.
Add a RestAPI data source to your DataWorks workspace. For the procedure, see Configure a REST API data source. In the Add RestAPI Data Source dialog box, enter Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Headers (default:
{}). Key settings:-
url: Set the RESTful API URL.
-
Authentication Method: Choose the authentication method supported by the API and configure its parameters.
-
Resource group connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. Select one and test connectivity between the data source and resource group.
-
-
Create a batch synchronization node and configure the task.
Create a batch synchronization node in DataStudio. For the procedure, see Codeless UI configuration. Key settings:
-
Data SourceConfiguration:
-
Data Source: Select the RestAPI data source created above.
-
Request Method: This sample API uses GET. Select GET.
-
Return Data Structure: The sample response is a JSON array. Select Array Data.
-
The JSON path for data storage: Data is under data in the response. Set this to data.
-
Request Param: Use request parameters and scheduling parameters together to synchronize the current day’s data each day.
-
Set the request parameters to
startTime=${extract_day} ${start_time}&endTime=${extract_day} ${end_time} -
Add three scheduling parameters:
extract_day=${yyyy-mm-dd},start_time=00:00:00,end_time=23:59:59.
If the run date is 2023-01-05,
extract_dayresolves to 2023-01-04, so the request parameters become:startTime=2023-01-04 00:00:00&endTime=2023-01-04 23:59:59. -
-
-
Data DestinationConfiguration:
-
Data Source, Table: Select the partitioned MaxCompute table created above.
-
Partition Information: Use partition information with a scheduling parameter.
-
Set the partition information to
${bizdate}. -
Add one scheduling parameter:
bizdate=$bizdate.
If the run date is 2023-01-05, the partition value is 20230104.
-
-
-
Field MappingEnter fields from the RestAPI response. Field names are case-sensitive. After adding fields, use The same name mapping or manually connect columns to create mappings.
-
Test run
Because this example uses scheduling parameters, after configuring the batch synchronization task, click Run with Parameters at the top of the node page. Enter test scheduling parameter values as prompted to test the task. This toolbar button is a run icon with a parameter marker, to the right of Run. After the test, check the run schedule at the bottom of the page and confirm that parameter values are as expected.
Check data
Use Temporary Query in DataStudio to check whether data was synchronized to MaxCompute. Sample query:
select * from ods_xiaobo_rest2 where ds='20230104' order by createtime;
Here, ods_xiaobo_rest2 is the partitioned MaxCompute table created above, and 20230104 is the partition value used in the test run.
After the run, check query results at the bottom of the page. The table should show values for axis, value, createTime, and partition ds matching the API response.
Commit, publish, and backfill
After testing and checking the data, commit and publish the batch synchronization task to production. For the procedure, see Deployment process for a standard mode workspace. On the node editor toolbar, click the Commit icon. After publishing succeeds, find the periodic task in Operation Center and backfill historical data. For backfill instructions, see Backfill data instance operations.
Practice 2: Read paginated data from a RestAPI API
Example scenario: API definition
This example reads a self-hosted test GET RESTful API and writes the data to a partitioned MaxCompute table. API details:
Adjust the configuration to match your own API. The sample API only demonstrates the procedure.
-
Sample request:
http://TestAPIAddress:Port/rest/test1?pageSize=5&pageNum=1pageSize and pageNum are request parameters for page size and page number.
-
Sample response:
{ "status": "success", "totalNum": 304, "data": [ { "id": 6, "name": "Test user 6" }, { "id": 7, "name": "Test user 7" }, { "id": 8, "name": "Test user 8" }, { "id": 9, "name": "Test user 9" }, { "id": 10, "name": "Test user 10" } ] }The returned data is under data and has two fields:
id,name. -
Example API test: Send a GET request in Postman or another API test tool with pageSize=5 and pageNum=2. The API returns 200 OK, totalNum is 304, and data contains page 2 users, such as id:6 and name:Test user 6.
Prepare: Create a partitioned MaxCompute table
This example synchronizes API data to a partitioned MaxCompute table. First, create a partitioned table to store it.
With overwrite mode, partitioned tables allow reruns without duplicating data and facilitate later analysis.
Create the table with:
CREATE TABLE IF NOT EXISTS ods_xiaobo_rest1
(
`id` BIGINT
,`name` STRING
)
PARTITIONED BY
(
ds STRING
)
LIFECYCLE 3650;
If you use DataWorks Standard Edition and publish the partitioned table to production, you can later find it in Data Map.
Configure a synchronization task
-
Add a RestAPI data source.
Add a RestAPI data source to your DataWorks workspace. For the procedure, see Configure a REST API data source. In the Add RestAPI Data Source dialog box, enter Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Headers (default:
{}). Key settings:-
url: Set the RESTful API URL.
-
Authentication Method: Choose the authentication method supported by the API and configure its parameters.
-
Resource group connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. Select one and test connectivity between the data source and resource group.
-
-
Create a batch synchronization node and configure the task.
Create a batch synchronization node in DataStudio. For the procedure, see Codeless UI configuration. Key settings:
-
Data SourceConfiguration:
-
Data Source: Select the RestAPI data source created above.
-
Request Method: This sample API uses GET. Select GET.
-
Return Data Structure: The sample response is a JSON array. Select Array Data.
-
The JSON path for data storage: Data is under data in the response. Set this to data.
-
Request Param: Use a fixed page size of
pageSize=50. Avoid very large pages, which can stress the RestAPI server and synchronization task. -
The number of requests: Select Multiple Requests.
The pagination parameter is pageNum. Select Multiple Requests and set these parameters:
-
Parameter used for multiple requests: Set to pageNum.
-
StartIndex: Set to 1.
-
Step: Set to 1.
-
EndIndex: Set to 100.
-
-
-
Data DestinationConfiguration:
-
Data Source, Table: Select the partitioned MaxCompute table created above.
-
Partition Information: Use partition information with a scheduling parameter.
-
Set the partition information to
${bizdate}. -
Add one scheduling parameter:
bizdate=$bizdate.
If the run date is 2023-01-05, the partition value is 20230104.
-
-
-
Field MappingEnter fields from the RestAPI response. Field names are case-sensitive. After adding fields, use The same name mapping or manually connect columns to create mappings.
-
Test run
Because this example uses scheduling parameters, after configuring the batch synchronization task, click Run with Parameters at the top of the node page. Enter test scheduling parameter values as prompted to test the task. This toolbar button is a run icon with a parameter marker, to the right of Run. After the test, check the run schedule at the bottom of the page and confirm that parameter values are as expected.
Check data
Use Temporary Query in DataStudio to check whether data was synchronized to MaxCompute. Sample query:
select * from ods_xiaobo_rest1 where ds='20230104' order by id;
Here, ods_xiaobo_rest1 is the partitioned MaxCompute table created above, and 20230104 is the partition value used in the test run.
After the run, check query results at the bottom of the page. The table should show id, name, and partition ds matching the API response.
Practice 3: Read data from a POST RestAPI API
Example scenario: API definition
This example reads a self-hosted test POST RESTful API and writes its data to a partitioned MaxCompute table. API details:
Adjust the configuration to match your own API. The sample API only demonstrates the procedure.
-
Sample request:
http://TestAPIAddress:Port/rest/test3The requestBody format is JSON.
{ "userId":16, "startTime":"2023-01-04 00:00:00", "endTime":"2023-01-04 23:59:59" } -
Sample response:
{ "status": "success", "totalNum": 289, "data": [ { "user": { "id": 16, "name": "User 16" }, "axis": "series1", "value": 8231053, "createTime": "2023-01-04 00:04:57" }, { "user": { "id": 16, "name": "User 16" }, "axis": "series1", "value": 6519928, "createTime": "2023-01-04 00:09:51" }, { "user": { "id": 16, "name": "User 16" }, "axis": "series1", "value": 2915920, "createTime": "2023-01-04 00:14:36" }, { "user": { "id": 16, "name": "User 16" }, "axis": "series1", "value": 7971851, "createTime": "2023-01-04 00:19:51" }, { "user": { "id": 16, "name": "User 16" }, "axis": "series1", "value": 6598996, "createTime": "2023-01-04 00:24:30" } ] }The returned data is under data and has five fields:
user.id,user.name,axis,value,createTime. -
Example API test: Send a POST request in Postman or another API test tool. Set Body to raw JSON and supply userId, startTime, and endTime. The API returns 200 OK, totalNum is 289, and data contains that user’s data within the specified time range.
Prepare: Create a partitioned MaxCompute table
This example synchronizes API data to a partitioned MaxCompute table. First, create a partitioned table to store it.
With overwrite mode, partitioned tables allow reruns without duplicating data and facilitate later analysis.
Create the table with:
CREATE TABLE IF NOT EXISTS ods_xiaobo_rest3
(
`user_id` BIGINT
,`name` STRING
,`axis` STRING
,`value` BIGINT
,`create_time` STRING
)
PARTITIONED BY
(
ds STRING
)
LIFECYCLE 3650;
If you use DataWorks Standard Edition and publish the partitioned table to production, you can later find it in Data Map.
Configure a synchronization task
-
Add a RestAPI data source.
Add a RestAPI data source to your DataWorks workspace. For the procedure, see Configure a REST API data source. In the Add RestAPI Data Source dialog box, enter Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Headers (default:
{}). Key settings:-
url: Set the RESTful API URL.
-
Authentication Method: Choose the authentication method supported by the API and configure its parameters.
-
Resource group connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. Select one and test connectivity between the data source and resource group.
-
-
Create a batch synchronization node and configure the task.
Create a batch synchronization node in DataStudio. For the procedure, see Codeless UI configuration. Key settings:
-
Data SourceConfiguration:
-
Data Source: Select the RestAPI data source created above.
-
Request Method: This example uses POST. Select POST.
-
Return Data Structure: The sample response is a JSON array. Select Array Data.
-
The JSON path for data storage: Data is under data in the response. Set this to data.
-
Header: The POST API accepts a JSON request body. Set this to
{"Content-Type":"application/json"}. -
Request Param: Use request parameters and scheduling parameters together to synchronize the current day’s data each day.
-
Set the request parameters to
{ "userId":16, "startTime":"${extract_day} 00:00:00", "endTime":"${extract_day} 23:59:59" } -
Add one scheduling parameter:
extract_day=${yyyy-mm-dd}.
-
-
-
Data DestinationConfiguration:
-
Data Source, Table: Select the partitioned MaxCompute table created above.
-
Partition Information: Use partition information with a scheduling parameter.
-
Set the partition information to
${bizdate}. -
Add one scheduling parameter:
bizdate=$bizdate.
If the run date is 2023-01-05, the partition value is 20230104.
-
-
-
Field MappingEnter the RestAPI response fields. Separate nested field names with periods. Field names are case-sensitive. Then use The same name mapping or manually connect columns to create mappings.
-
Test run
Because this example uses scheduling parameters, after configuring the batch synchronization task, click Run with Parameters at the top of the node page. Enter test scheduling parameter values as prompted to test the task. This toolbar button is a run icon with a parameter marker, to the right of Run. After the test, check the run schedule at the bottom of the page and confirm that parameter values are as expected.
Check data
Use Temporary Query in DataStudio to check whether data was synchronized to MaxCompute. Sample query:
select * from ods_xiaobo_rest3 where ds='20230105' order by create_time;
Here, ods_xiaobo_rest3 is the partitioned MaxCompute table created above, and 20230105 is the partition value used in the test run.
After the run, check query results at the bottom of the page. The table should show user_id, name, axis, value, create_time, and partition ds matching the API response, confirming that nested fields were mapped and synchronized.
Practice 4: Loop over a request parameter to read a RestAPI API
Example scenario: API definition
This example repeatedly reads a self-hosted test GET RESTful API and writes data to a partitioned MaxCompute table. The API returns temperature data based on date, province, and city parameters.
Adjust the configuration to match your own API. The sample API only demonstrates the procedure.
-
Sample request:
http://TestAPIAddress:Port/rest/test5?date=2023-01-04&province=zhejiang&city=hangzhou -
Sample response:
{ "province": "P1", "city": "hz", "date": "2023-01-04", "minTemperature": "-14", "maxTemperature": "-7", "unit": "℃", "weather": "cool" } -
Example API test: Send a GET request in Postman or another API test tool with date=2023-01-04, province=p1, and city=hz. The API returns JSON with minTemperature (-14), maxTemperature (-7), unit (℃), and weather (COOL).
Prepare: Create a parameter table and partitioned MaxCompute table
First, create a parameter table containing province and city values to loop over, then a partitioned table to store synchronized API data.
With overwrite mode, partitioned tables allow reruns without duplicating data and facilitate later analysis.
Use these CREATE TABLE statements:
Create the parameter table
CREATE TABLE IF NOT EXISTS `citys`
(
`province` STRING ,
`city` STRING
);
insert into citys
select 'shanghai','shanghai'
union all select 'zhejiang','hangzhou'
union all select 'sichuan','chengdu';
Create the partitioned MaxCompute table
CREATE TABLE IF NOT EXISTS ods_xiaobo_rest5
(
`minTemperature` STRING ,
`maxTemperature` STRING ,
`unit` STRING ,
`weather` STRING
)
PARTITIONED BY
(
`province` STRING ,
`city` STRING ,
`ds` STRING
)
LIFECYCLE 3650;
If you use DataWorks Standard Edition and publish the tables to production, you can later find them in Data Map.
Configure a synchronization task
-
Add a RestAPI data source.
Add a RestAPI data source to your DataWorks workspace. For the procedure, see Configure a REST API data source. In the Add RestAPI Data Source dialog box, enter Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Headers (default:
{}). Key settings:-
url: Set the RESTful API URL.
-
Authentication Method: Choose the authentication method supported by the API and configure its parameters.
-
Resource group connectivity: Select the specified resource group and test connectivity.
-
-
In DataStudio, create an assignment node named setval_citys. For the procedure, see Assignment node.
Key settings:
No.
Description
①
-
Assignment language: ODPS SQL
-
Assignment code:
SELECT province ,city FROM citys;
②
Rerun property: Set to You can run again after successful or failed operation..
Commit and publish the assignment node after configuration.
-
-
In DataStudio, create a for-each node. For the procedure, see For-each node. Key settings:
No.
Description
①
Rerun property: Set to You can run again after successful or failed operation..
②
Dependent upstream node: Select setval_citys from the preceding step.
③
Node context parameter: Select the source for the input parameter.
④
Batch synchronization node: Configure a batch synchronization node inside for-each as described below.
-
Create a batch synchronization node and configure the task. For the procedure, see Codeless UI configuration.
Key settings:
No.
Description
①
Scheduling parameters:
bizdate=$[yyyymmdd-1] bizdate_year=$[yyyy-1] bizdate_month=$[mm-1] bizdate_day=$[dd-1]②
Configure RestAPI request parameters. province and city come from the loop node.
date=${bizdate_year}-${bizdate_month}-${bizdate_day}&province=${dag.foreach.current[0]}&city=${dag.foreach.current[1]}③
Configure the MaxCompute partition parameter province from the loop node.
province=${dag.foreach.current[0]}④
Configure the MaxCompute partition parameter city from the loop node.
city=${dag.foreach.current[1]}⑤
Configure the MaxCompute partition parameter ds from a scheduling parameter.
ds=${bizdate}⑥
Enter the RestAPI response fields. Field names are case-sensitive. Then use The same name mapping or manually connect columns to create mappings.
Commit and publish the for-each node after configuration.
Test run
-
After successfully committing and publishing the assignment and for-each nodes, backfill the assignment node in Operation Center under Periodic Tasks. For the procedure, see Backfill data instance operations.
-
Select the business date and downstream nodes for the backfill task.
-
After the task runs, check the execution parameters and logs in the backfill instance details.
In this example, data is written to the MaxCompute partition
province=shanghai,city=shanghai,ds=20231215.
Check data
Use Temporary Query in Data Development to check whether data was synchronized to MaxCompute. Sample query:
In this example, ods_xiaobo_rest5 is the partitioned MaxCompute table created in Prepare.
SELECT weather
,mintemperature
,maxtemperature
,unit
,province
,city
,ds
FROM ods_xiaobo_rest5
WHERE ds != 1
ORDER BY ds,province,city;
After the run, check whether data was synchronized to MaxCompute.
|
weather |
mintemperature |
maxtemperature |
unit |
province |
city |
ds |
|
COOL |
3 |
9 |
°C |
shanghai |
shanghai |
20231215 |
|
HAZY |
-2 |
6 |
°C |
sichuan |
chengdu |
20231215 |
|
FOGGY |
19 |
28 |
°C |
zhejiang |
hangzhou |
20231215 |
|
SNOWY |
-16 |
-5 |
°C |
shanghai |
shanghai |
20231216 |
|
SNOWY |
-16 |
-8 |
°C |
sichuan |
chengdu |
20231216 |
|
SUNNY |
15 |
25 |
°C |
zhejiang |
hangzhou |
20231216 |
|
COOL |
-10 |
2 |
°C |
shanghai |
shanghai |
20231217 |
|
HAZY |
15 |
24 |
°C |
sichuan |
chengdu |
20231217 |
|
FOGGY |
4 |
11 |
°C |
zhejiang |
hangzhou |
20231217 |
|
HOT |
-14 |
-7 |
°C |
shanghai |
shanghai |
20231218 |
|
FOGGY |
-2 |
4 |
°C |
sichuan |
chengdu |
20231218 |
|
RAINY |
9 |
19 |
°C |
zhejiang |
hangzhou |
20231218 |