RestAPI Reader best practices

Practice 1: Read API data by time range

API definition

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 batch synchronization task

1. Add a RestAPI data source.

   In your DataWorks workspace, add a RestAPI data source. For more information, see Configure a RestAPI data source. In the Add RestAPI Data Source dialog box, enter the Data Source Name and Data Source Description, select the Applicable Environment (development or production), and configure the Default Request Headers (default is {}). Key configurations are as follows:

   • url: Enter the URL of the RESTful API.

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

   • Resource group connectivity: RestAPI data sources support only an exclusive resource group for Data Integration. You must select one and test its connectivity to the data source.

2. Create and configure a batch synchronization node.

   In DataStudio, create a batch synchronization node. For more information, see Configure a batch synchronization node in wizard mode. Key configurations are as follows:

   • Key configurations for Data Source:

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

     • Request Method: This practice uses the GET method. Select GET.

     • Return Data Structure: The API in this practice returns a JSON array. Select Array Data.

     • The JSON path for data storage: The API returns data in the data field, so set this parameter to data.

     • Request Param: Use request parameters with scheduling parameters to synchronize the previous day's data.

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

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

       For example, if the task runs on January 5, 2023, the value of extract_day is 2023-01-04. The request parameters are concatenated as: startTime=2023-01-04 00:00:00&endTime=2023-01-04 23:59:59.

   • Key configurations for Data Destination:

     • Data Source and Table: Select the MaxCompute partitioned table that you created.

     • Partition Information: Configure the partition by using scheduling parameters.

       • Set the partition information to ${bizdate}.

       • In the scheduling configuration that follows, add one scheduling parameter: bizdate=$bizdate.

       For example, if the task runs on January 5, 2023, the partition value is 20230104.

   • Field Mapping: Based on the data schema defined in the API, enter the fields from the RestAPI. Note that field names are case-sensitive. After you add the fields, you can map the columns by using The same name mapping or by mapping the fields manually.

Test the run

Verify the data

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

Deploy the task and backfill data

Practice 2: Read paginated API data

Scenario: API definition

Prerequisites: 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.

   In your DataWorks workspace, add a RestAPI data source. For more information, see Configure a RestAPI data source. In the Add RestAPI Data Source dialog box, enter the Data Source Name and Data Source Description, select the Applicable Environment (development or production), and configure the Default Request Headers (default is {}). Key configurations are as follows:

   • url: Enter the URL of the RESTful API.

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

   • Resource group connectivity: RestAPI data sources support only an exclusive resource group for Data Integration. You must select one and test its connectivity to the data source.

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

   In DataStudio of DataWorks, create a batch synchronization node. For details, see Configure a task in the codeless UI. Key settings:

   • Key settings for Data Source:

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

     • Request Method: This example uses a GET API. Select GET.

     • Return Data Structure: The API in this example returns a JSON array. Select Array Data.

     • The JSON path for data storage: In this example, the data is located under the data key in the response. Set this parameter to data.

     • Request Param: The page size is fixed. Set this parameter to pageSize=50. Avoid setting a large page size, as it can place a high load on both the REST API server and the synchronization task.

     • The number of requests: In this example, select Multiple Requests.

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

       • Parameter used for multiple requests: Set to pageNum.

       • StartIndex: Set to 1.

       • Step: Set to 1.

       • EndIndex: Set to 100.

   • Key configurations for Data Destination:

     • Data Source and Table: Select the MaxCompute partitioned table that you created.

     • Partition Information: Configure the partition by using scheduling parameters.

       • Set the partition information to ${bizdate}.

       • In the scheduling configuration that follows, add one scheduling parameter: bizdate=$bizdate.

       For example, if the task runs on January 5, 2023, the partition value is 20230104.

   • Field Mapping: Based on the data schema defined in the API, enter the fields from the RestAPI. Note that field names are case-sensitive. After you add the fields, you can map the columns by using The same name mapping or by mapping the fields manually.

Run a test

Verify the data

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

Practice 3: Read data from a POST API

API definition

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 batch synchronization task

1. Add a RestAPI data source.

   In your DataWorks workspace, add a RestAPI data source. For more information, see Configure a RestAPI data source. In the Add RestAPI Data Source dialog box, enter the Data Source Name and Data Source Description, select the Applicable Environment (development or production), and configure the Default Request Headers (default is {}). Key configurations are as follows:

   • url: Enter the URL of the RESTful API.

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

   • Resource group connectivity: RestAPI data sources support only an exclusive resource group for Data Integration. You must select one and test its connectivity to the data source.

2. Create and configure a batch synchronization node.

   In DataStudio of DataWorks, create a batch synchronization node. For details, see Configure by using the codeless UI. Key settings are as follows.

   • Key settings for Data Source:

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

     • Request Method: This practice uses a POST API. Select POST.

     • Return Data Structure: The API in this practice returns a JSON array. Select Array Data.

     • The JSON path for data storage: Set this to data, which is the key that holds the data array in the API response.

     • Header: Set this parameter to {"Content-Type":"application/json"} to specify that the request body is in JSON format.

     • Request Param: Combine request parameters with scheduling parameters to synchronize the current day's data on a daily basis.

       • Configure the request parameters as follows:

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

       • In the scheduling configurations, add a new scheduling parameter: extract_day=${yyyy-mm-dd}.

   • Key configurations for Data Destination:

     • Data Source and Table: Select the MaxCompute partitioned table that you created.

     • Partition Information: Configure the partition by using scheduling parameters.

       • Set the partition information to ${bizdate}.

       • In the scheduling configuration that follows, add one scheduling parameter: bizdate=$bizdate.

       For example, if the task runs on January 5, 2023, the partition value is 20230104.

   • For Field Mapping, enter the fields from the RestAPI interface based on the data definitions in the API. Use periods to separate multiple fields. Note that field names are case-sensitive. After you add the fields, create column mappings by using The same name mapping or by manually creating connections.

Test run

Data verification

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

Practice 4: Iterate parameters to read from a REST API

Use case: API definition

This practice demonstrates how to build a workflow that iteratively reads data from a RESTful API and writes it to a MaxCompute partitioned table. This practice uses 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": "°C",
    "weather": "cool"
  }

• Sample API call in a testing tool: In an API testing tool such as Postman, send a GET request and pass the parameters date=2023-01-04, province=p1, and city=hz. The API returns a JSON response that includes the minTemperature (-14), maxTemperature (-7), unit (°C), and weather (cool) information.

Prerequisites: Create parameter and MaxCompute partitioned tables

In this practice, you synchronize data from an API to a MaxCompute partitioned table. First, create a parameter table to store the province and city values for iteration. Then, create a destination partitioned table to store the synchronized data.

The following are the DDL statements:

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 the Standard Edition of DataWorks and deploy 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.

   In your DataWorks workspace, add a RestAPI data source. For more information, see Configure a RestAPI data source. In the Add RestAPI Data Source dialog box, specify the Data Source Name and Data Source Description, select the Environment (Development or Production), and configure the Default Request Headers (the default is {}). The key configurations are as follows:

   • url: Enter the URL of the RESTful API.

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

   • Resource group connectivity: Select a resource group and test its connectivity.

2. In DataStudio, create an assignment node named setval_citys. For more information, see Assignment node.

   The key configurations are as follows:

   Item	Description
   ①	Assignment Language: ODPS SQL Assignment Code: SELECT province ,city FROM citys;
   ②	Rerun Property: Set this parameter to You can run again after successful or failed operation..

   After you configure the assignment node, deploy it.

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

   Item	Description
   ①	Rerun Property: Set this parameter to You can run again after successful or failed operation..
   ②	Ancestor Node: Select the node created in the previous step, the setval_citys node.
   ③	Node Context Parameters: Select the source of the input parameters.
   ④	Batch Synchronization Node: Configure the batch synchronization node within the for-each node. For more information, see the next step.

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

   The key configurations are as follows:

   Item	Description
   ①	Configure 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 are inherited 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 for province. This value is passed from the for-each node. province=${dag.foreach.current[0]}
   ④	Configure the MaxCompute partition parameter for city. This value is passed from the for-each node. city=${dag.foreach.current[1]}
   ⑤	Configure the MaxCompute partition parameter for ds. The value is inherited from the scheduling parameters. ds=${bizdate}
   ⑥	Based on the API response schema, enter the source fields from the RestAPI. Note that field names are case-sensitive. After you add the fields, click The same name mapping or map the fields manually.

   After you configure the node, deploy the for-each node.

Run a test

1. After deploying the assignment and for-each nodes, go to Operation Center, find the periodic task for the assignment node, and run a backfill. For more information, see Manage backfill instances.

2. Based on your business requirements, select the business date for the backfill task and its descendant nodes.

3. After the task runs, view the details of the backfill instance to confirm that the execution parameters and run logs are correct.

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

Verify the data

Run an ad hoc query in DataStudio to verify that the data was synchronized correctly to MaxCompute. The following is an example query:

In this example, ods_xiaobo_rest5 is the MaxCompute partitioned table that you created in the Prerequisites section.

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

When the query finishes, check the results to verify that the data has been synchronized to MaxCompute.