DataWorks:RestAPI Reader best practices

Practice 1: Read data from an API that queries data within a time range

Sample scenario: API definition

Preparation: Create a MaxCompute partitioned table

CREATE TABLE IF NOT EXISTS ods_xiaobo_rest2
(
  `axis`  STRING
  ,`value` BIGINT
  ,`createTime` STRING
)
PARTITIONED BY
(
  ds  STRING
)
LIFECYCLE 3650;

Configure the synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source in your DataWorks workspace. For more information, see Add a RestAPI data source. In the Add RestAPI Data Source dialog, specify Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Header (default value: {}). The key configurations are as follows.

   • url: Set this to the RESTful API address.

   • Authentication Method: Select an authentication method supported by your data source API and configure the required authentication parameters.

   • Resource group connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. Select an exclusive resource group for Data Integration and test the connectivity between the data source and the resource group.

2. Create a batch synchronization node and configure the synchronization task.

   Create a batch synchronization node in DataWorks Data Studio. For more information, see Create a batch synchronization node. The key configurations are as follows.

   • Data Source configurations:

     • Data Source: Select the RestAPI data source created in the previous step.

     • Request Method: The sample API is a GET API. Select GET.

     • Return Data Structure: The response of the sample API is a JSON array. Select Array Data.

     • The JSON path for data storage: The data returned by the sample API is stored under the data field. Set this to data.

     • Request Param: Use request parameters together with scheduling parameters to synchronize data for the current day on a daily basis.

       • Set the request parameters to startTime=${extract_day} ${start_time}&endTime=${extract_day} ${end_time}

       • In the schedule settings, add three scheduling parameters: extract_day=${yyyy-mm-dd}, start_time=00:00:00, and end_time=23:59:59.

       If the run date is 2023-01-05, the value of extract_day is 2023-01-04, and the request parameters are concatenated as: startTime=2023-01-04 00:00:00&endTime=2023-01-04 23:59:59.

   • Data Destination configurations:

     • Data Source and Table: Select the MaxCompute partitioned table created in the previous step.

     • Partition Information: Use partition information together with scheduling parameters.

       • Set the partition information to ${bizdate}.

       • In the schedule settings, add one scheduling parameter: bizdate=$bizdate.

       If the run date is 2023-01-05, the partition information value is 20230104.

   • Field Mapping configurations: Based on the data definition in the API, enter the column names from the RestAPI response. Column names are case-sensitive. After you add the columns, use The same name mapping or manually draw lines to create column mappings.

Test run

Data verification

select * from ods_xiaobo_rest2 where ds='20230104' order by createtime;

Submit, deploy, and backfill data

Practice 2: Read data from a paginated RestAPI

Sample scenario: API definition

Preparation: Create a MaxCompute partitioned table

CREATE TABLE IF NOT EXISTS ods_xiaobo_rest1
(
  `id` BIGINT
  ,`name` STRING
)
PARTITIONED BY
(
  ds  STRING
)
LIFECYCLE 3650;

Configure the synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source in your DataWorks workspace. For more information, see Add a RestAPI data source. In the Add RestAPI Data Source dialog, specify Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Header (default value: {}). The key configurations are as follows.

   • url: Set this to the RESTful API address.

   • Authentication Method: Select an authentication method supported by your data source API and configure the required authentication parameters.

   • Resource group connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. Select an exclusive resource group for Data Integration and test the connectivity between the data source and the resource group.

2. Create a batch synchronization node and configure the synchronization task.

   Create a batch synchronization node in DataWorks Data Studio. For more information, see Create a batch synchronization node. The key configurations are as follows.

   • Data Source configurations:

     • Data Source: Select the RestAPI data source created in the previous step.

     • Request Method: The sample API is a GET API. Select GET.

     • Return Data Structure: The response of the sample API is a JSON array. Select Array Data.

     • The JSON path for data storage: The data returned by the sample API is stored under the data field. Set this to data.

     • Request Param: The page size is fixed. Set this to pageSize=50. We recommend that you do not set a large page size, as it may put excessive pressure on the RestAPI server and the synchronization task.

     • The number of requests: Select Multiple Requests for this example.

       The pagination parameter for this API is pageNum. After you select Multiple Requests, configure the related parameters as follows:

       • Parameter used for multiple requests: Set this to pageNum.

       • StartIndex: Set this to 1.

       • Step: Set this to 1.

       • EndIndex: Set this to 100.

   • Data Destination configurations:

     • Data Source and Table: Select the MaxCompute partitioned table created in the previous step.

     • Partition Information: Use partition information together with scheduling parameters.

       • Set the partition information to ${bizdate}.

       • In the schedule settings, add one scheduling parameter: bizdate=$bizdate.

       If the run date is 2023-01-05, the partition information value is 20230104.

   • Field Mapping configurations: Based on the data definition in the API, enter the column names from the RestAPI response. Column names are case-sensitive. After you add the columns, use The same name mapping or manually draw lines to create column mappings.

Test run

Data verification

select * from ods_xiaobo_rest1 where ds='20230104' order by id;

Practice 3: Read data from a POST-type RestAPI

Sample scenario: API definition

Preparation: Create a MaxCompute partitioned table

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;

Configure the synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source in your DataWorks workspace. For more information, see Add a RestAPI data source. In the Add RestAPI Data Source dialog, specify Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Header (default value: {}). The key configurations are as follows.

   • url: Set this to the RESTful API address.

   • Authentication Method: Select an authentication method supported by your data source API and configure the required authentication parameters.

   • Resource group connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. Select an exclusive resource group for Data Integration and test the connectivity between the data source and the resource group.

2. Create a batch synchronization node and configure the synchronization task.

   Create a batch synchronization node in DataWorks Data Studio. For more information, see Create a batch synchronization node. The key configurations are as follows.

   • Data Source configurations:

     • Data Source: Select the RestAPI data source created in the previous step.

     • Request Method: The sample API is a POST API. Select POST.

     • Return Data Structure: The response of the sample API is a JSON array. Select Array Data.

     • The JSON path for data storage: The data returned by the sample API is stored under the data field. Set this to data.

     • Header: The POST API in this example accepts a JSON request body. Set this to {"Content-Type":"application/json"}.

     • Request Param: Use request parameters together with scheduling parameters to synchronize data for the current day on a daily basis.

       • Set the request parameters to

         {
         
             "userId":16,
         
             "startTime":"${extract_day} 00:00:00",
         
             "endTime":"${extract_day} 23:59:59"
         
         }

       • In the schedule settings, add one scheduling parameter: extract_day=${yyyy-mm-dd}.

   • Data Destination configurations:

     • Data Source and Table: Select the MaxCompute partitioned table created in the previous step.

     • Partition Information: Use partition information together with scheduling parameters.

       • Set the partition information to ${bizdate}.

       • In the schedule settings, add one scheduling parameter: bizdate=$bizdate.

       If the run date is 2023-01-05, the partition information value is 20230104.

   • Field Mapping configurations: Based on the data definition in the API, enter the column names from the RestAPI response. You can use dots (.) to separate nested columns. Column names are case-sensitive. After you add the columns, use The same name mapping or manually draw lines to create column mappings.

Test run

Data verification

select * from ods_xiaobo_rest3 where ds='20230105' order by create_time;

Practice 4: Loop through a request parameter to read from a RestAPI

Sample scenario: API definition

In this practice, you iteratively read data from a RESTful API and write it to a MaxCompute partitioned table. The sample RESTful API is a self-built test GET API that returns temperature data based on the date, province, and city input parameters.

• 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"
  }

• Sample API call in a testing tool: Send a GET request in an API testing tool such as Postman. Pass in the parameters date=2023-01-04, province=p1, and city=hz. The API returns a JSON response that contains the corresponding minTemperature (-14), maxTemperature (-7), unit (℃), and weather (COOL) information.

Preparation: Create a parameter table and a MaxCompute partitioned table

In this practice, data read from the API is synchronized to a MaxCompute partitioned table. You must first create a parameter table to store the province and city values for iteration, and then create a partitioned table to store the synchronized data.

The CREATE TABLE statements are as follows:

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 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 submit the created tables to the production environment, you can view the tables in Data Map.

Configure the synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source in your DataWorks workspace. For more information, see Add a RestAPI data source. In the Add RestAPI Data Source dialog, specify Data Source Name and Data Source Description, select Applicable Environment (development or production), and configure Default Request Header (default value: {}). The key configurations are as follows:

   • url: Set this to the RESTful API address.

   • Authentication Method: Select an authentication method supported by your data source API and configure the required authentication parameters.

   • Resource group connectivity: Select the specified resource group and test the connectivity.

2. Create an assignment node named setval_citys in DataStudio. For more information, see Create an assignment node.

   The key configurations are as follows:

   No.	Description
   ①	Assignment language: ODPS SQL Assignment code: SELECT province ,city FROM citys;
   ②	Rerun property: Set this to You can run again after successful or failed operation..

   After the configuration is complete, submit and deploy the assignment node.

3. Create a for-each node in DataStudio. For more information, see Create a for-each node. The key configurations are as follows:

   No.	Description
   ①	Rerun property: Set this to You can run again after successful or failed operation..
   ②	Upstream node dependency: Select the node from the previous step, which is the setval_citys node.
   ③	Node context parameter: Select the source of the input parameter.
   ④	Batch synchronization node: Configure the batch synchronization node inside the for-each node. For details, see the following steps.

4. Create a batch synchronization node and configure the synchronization task. For more information, see Create a batch synchronization node.

   The key configurations are as follows:

   No.	Description
   ①	Configure the scheduling parameters as follows: bizdate=$[yyyymmdd-1] bizdate_year=$[yyyy-1] bizdate_month=$[mm-1] bizdate_day=$[dd-1]
   ②	Configure the RestAPI request parameters. The province and city parameters come from the for-each node. date=${bizdate_year}-${bizdate_month}-${bizdate_day}&province=${dag.foreach.current[0]}&city=${dag.foreach.current[1]}
   ③	Configure the MaxCompute partition parameter. The province parameter comes from the for-each node. province=${dag.foreach.current[0]}
   ④	Configure the MaxCompute partition parameter. The city parameter comes from the for-each node. city=${dag.foreach.current[1]}
   ⑤	Configure the MaxCompute partition parameter. The ds parameter comes from the scheduling parameters. ds=${bizdate}
   ⑥	Based on the data definition in the API, enter the column names from the RestAPI response. Column names are case-sensitive. After you add the columns, use The same name mapping or manually draw lines to create column mappings.

   After the configuration is complete, submit and deploy the for-each node.

Test run

1. After you successfully submit and deploy the assignment node and for-each node, go to the scheduled tasks in Operation Center and perform a backfill data operation on the assignment node. For more information, see Backfill data.

2. Select the business date for the backfill data task and the downstream nodes based on your actual scenario.

3. After the task runs, check the execution parameters in the backfill data instance details to verify whether they meet your expectations, and check the runtime log for correctness.

   In this example, data is written to the province=shanghai,city=shanghai,ds=20231215 partition of the MaxCompute table.

Data verification

You can run an ad hoc query in Data Studio to verify whether the data is correctly synchronized to MaxCompute. The following is a sample query:

In the example, ods_xiaobo_rest5 is the MaxCompute partitioned table created in the Preparation step.

SELECT  weather
        ,mintemperature
        ,maxtemperature
        ,unit
        ,province
        ,city
        ,ds
FROM    ods_xiaobo_rest5
WHERE   ds != 1
ORDER BY ds,province,city;

After the query is complete, check whether the data is correctly synchronized to MaxCompute.