DataWorks:Use RestAPI Reader to read data

Example 1: Read data from a RESTful API that queries data within a specified time range

Sample scenario: Define the RESTful API

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;

Add a RestAPI data source and configure a batch synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source to the workspace that you want to use. For more information, see Add a RestAPI data source. You must take note of the following items:

   • url: You must specify the URL of the RESTful API.

   • Auth method: You can select an authentication method that is supported by the RestAPI data source and configure the required authentication parameters.

   • Network connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. You must select an exclusive resource group for Data Integration that you want to use and test the network connectivity between the resource group and the RestAPI data source.

2. Create and configure a batch synchronization task.

   On the DataStudio page of the DataWorks console, create a batch synchronization task and configure the task by using the codeless user interface (UI). For more information, see Configure a batch synchronization task by using the codeless UI. You must take note of the following items.

   • Key configuration items for the source:

     • Data source: You must select the RestAPI data source that is added in the preceding step.

     • Request Method: In this example, the request method of the RESTful API is GET. You must set the request method to GET.

     • Data Structure: In this example, the response returned by the RESTful API is a JSON array. You must set the Data Structure parameter to Array Data.

     • json path to store data: In this example, the fields that are queried by the RESTful API are stored in the data path. You must enter data in the field.

     • Request parameters: You can use request parameters together with scheduling parameters to enable the batch synchronization task to synchronize data every day.

       • You can configure the following request parameters: startTime=${extract_day} ${start_time}&endTime=${extract_day} ${end_time}.

       • When you configure scheduling properties for the batch synchronization task, add the following scheduling parameters: extract_day=${yyyy-mm-dd}, start_time=00:00:00, and end_time=23:59:59.

       For example, if the date on which the batch synchronization task is scheduled to run is 2023-01-05, the value of extract_day is 2023-01-04. The request parameters are concatenated in the following format: startTime=2023-01-04 00:00:00&endTime=2023-01-04 23:59:59.

   • Key configuration items for the destination:

     • Data source and table: You must select the MaxCompute data source that you want to use and the MaxCompute partitioned table that is created in the preparation for this example.

     • Partition information: You can use a scheduling parameter to specify the partition to which you want to write data in the MaxCompute partitioned table.

       • You can enter ${bizdate} in the field.

       • When you configure scheduling properties for the data synchronization task, add the scheduling parameter bizdate=$bizdate.

       For example, if the date on which the batch synchronization task is scheduled to run is 2023-01-05, the partition to which data is written is represented by the date 20230104.

   • Key configuration item for field mappings: When you configure field mappings for the batch synchronization task, you must specify the fields that you want the RESTful API to return based on the data definition in the RESTful API. The field names are case-sensitive. After you specify the fields, you can click Map Fields with Same Name to map source fields to destination fields whose names are the same, or manually drag lines to map source fields to destination fields.

Test the batch synchronization task

Check whether data is synchronized to MaxCompute

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

Commit and deploy the batch synchronization task, and backfill data for the batch synchronization task

Example 2: Read data from the paged result returned by a RESTful API

Sample scenario: Define the RESTful API

Preparation: Create a MaxCompute partitioned table

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

Add a RestAPI data source and configure a batch synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source to the workspace that you want to use. For more information, see Add a RestAPI data source. You must take note of the following items:

   • url: You must specify the URL of the RESTful API.

   • Auth method: You can select an authentication method that is supported by the RestAPI data source and configure the required authentication parameters.

   • Network connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. You must select an exclusive resource group for Data Integration that you want to use and test the network connectivity between the resource group and the RestAPI data source.

2. Create and configure a batch synchronization task.

   On the DataStudio page of the DataWorks console, create a batch synchronization task and configure the task by using the codeless UI. For more information, see Configure a batch synchronization task by using the codeless UI. You must take note of the following items.

   • Key configuration items for the source:

     • Data source: You must select the RestAPI data source that is added in the preceding step.

     • Request Method: In this example, the request method of the RESTful API is GET. You must set the request method to GET.

     • Data Structure: In this example, the response returned by the RESTful API is a JSON array. You must set the Data Structure parameter to Array Data.

     • json path to store data: In this example, the fields that are queried by the RESTful API are stored in the data path. You must enter data in the field.

     • Request parameters: In this example, you must configure pageSize=50 to limit the number of entries to return on each page to 50. We recommend that you do not specify an excessively large value for the pageSize parameter. Otherwise, loads on the RestAPI server side and the batch synchronization task may be high.

     • Number of requests: In this example, you must select Multiple requests.

       The paging parameter used in this example for the RESTful API is pageNum. After you select Multiple requests, you must configure the following parameters:

       • Multiple requests parameter: Enter pageNum.

       • StartIndex: Enter 1.

       • Step: Enter 1.

       • EndIndex: Enter 100.

   • Key configuration items for the destination:

     • Data source and table: You must select the MaxCompute data source that you want to use and the MaxCompute partitioned table that is created in the preparation for this example.

     • Partition information: You can use a scheduling parameter to specify the partition to which you want to write data in the MaxCompute partitioned table.

       • You can enter ${bizdate} in the field.

       • When you configure scheduling properties for the data synchronization task, add the scheduling parameter bizdate=$bizdate.

       For example, if the date on which the batch synchronization task is scheduled to run is 2023-01-05, the partition to which data is written is represented by the date 20230104.

   • Key configuration item for field mappings: When you configure field mappings for the batch synchronization task, you must specify the fields that you want the RESTful API to return based on the data definition in the RESTful API. The field names are case-sensitive. After you specify the fields, you can click Map Fields with Same Name to map source fields to destination fields whose names are the same, or manually drag lines to map source fields to destination fields.

Test the batch synchronization task

Check whether data is synchronized to MaxCompute

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

Example 3: Read data from a RESTful API whose request method is POST

Sample scenario: Define the RESTful API

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;

Add a RestAPI data source and configure a batch synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source to the workspace that you want to use. For more information, see Add a RestAPI data source. You must take note of the following items:

   • url: You must specify the URL of the RESTful API.

   • Auth method: You can select an authentication method that is supported by the RestAPI data source and configure the required authentication parameters.

   • Network connectivity: RestAPI data sources support only exclusive resource groups for Data Integration. You must select an exclusive resource group for Data Integration that you want to use and test the network connectivity between the resource group and the RestAPI data source.

2. Create and configure a batch synchronization task.

   On the DataStudio page of the DataWorks console, create a batch synchronization task and configure the task by using the codeless UI. For more information, see Configure a batch synchronization task by using the codeless UI. You must take note of the following items.

   • Key configuration items for the source:

     • Data source: You must select the RestAPI data source that is added in the preceding step.

     • Request Method: In this example, the request method of the RESTful API is POST. You must set the request method to POST.

     • Data Structure: In this example, the response returned by the RESTful API is a JSON array. You must set the Data Structure parameter to Array Data.

     • json path to store data: In this example, the fields that are queried by the RESTful API are stored in the data path. You must enter data in the field.

     • Header: The request body of the RESTful API used in this example is in the JSON format. In this example, you must enter {"Content-Type":"application/json"} in the field.

     • Request parameters: You can use request parameters together with scheduling parameters to enable the batch synchronization task to synchronize data every day.

       • The following code provides an example for the configuration of request parameters:

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

       • When you configure scheduling properties for the batch synchronization task, add the scheduling parameter extract_day=${yyyy-mm-dd}.

   • Key configuration items for the destination:

     • Data source and table: You must select the MaxCompute data source that you want to use and the MaxCompute partitioned table that is created in the preparation for this example.

     • Partition information: You can use a scheduling parameter to specify the partition to which you want to write data in the MaxCompute partitioned table.

       • You can enter ${bizdate} in the field.

       • When you configure scheduling properties for the data synchronization task, add the scheduling parameter bizdate=$bizdate.

       For example, if the date on which the batch synchronization task is scheduled to run is 2023-01-05, the partition to which data is written is represented by the date 20230104.

   • Key configuration items for field mappings: When you configure field mappings for the batch synchronization task, you must specify the fields that you want the RESTful API to return based on the data definition in the RESTful API. If a field that you want to specify is a nested field, you can use a period (.) in the field name when you specify the field. The field names are case-sensitive. After you specify the fields, you can click Map Fields with Same Name to map source fields to destination fields whose names are the same, or manually drag lines to map source fields to destination fields.

Test the batch synchronization task

Check whether data is synchronized to MaxCompute

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

Example 4: Traverse request parameters of a RESTful API in loops to read data

Sample scenario: Define the RESTful API

In this sample scenario, a batch synchronization task that uses RestAPI Reader is configured to repeatedly read data from a RESTful API to a MaxCompute partitioned table. The RESTful API is a self-managed API whose request method is GET and returns data related to temperature based on the following request parameters: date, province, and city.

• 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 call made by using the API test tool

Preparation: Create a parameter table and a MaxCompute partitioned table

In this example, data that is read from a RESTful API needs to be written to a MaxCompute partitioned table. You must create a parameter table and a MaxCompute partitioned table in advance. The parameter table is used to store values of the province and city parameters, which need to be traversed in loops. The MaxCompute partitioned table is used to store the synchronized data.

Execute the following statements to create tables:

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 a workspace in standard mode and you commit and deploy the two tables to the production environment after they are created, you can view the tables in Data Map.

Add a RestAPI data source and configure a batch synchronization task

1. Add a RestAPI data source.

   Add a RestAPI data source to the workspace that you want to use. For more information, see Add a RestAPI data source. You must take note of the following items:

   • url: You must specify the URL of the RESTful API.

   • Auth method: You can select an authentication method that is supported by the RestAPI data source and configure the required authentication parameters.

   • Network connectivity: You must select a resource group that you want to use and test the network connectivity between the resource group and the RestAPI data source.

2. Create and configure an assignment node named setval_citys on the DataStudio page. For more information, see Configure an assignment node.

   You must take note of the following items.

   No.	Description
   1	Programming language: ODPS SQL Code for value assignment: SELECT province ,city FROM citys;
   2	Rerun property: Set the rerun property to Allow Regardless of Running Status.

   After the configuration of the assignment node is complete, commit and deploy the node.

3. Create and configure a for-each node on the DataStudio page. For more information, see topics in the Configure a for-each node directory. You must take note of the following items.

   Parameter	Description
   Rerun property	Set the rerun property to Allow Regardless of Running Status.
   Ancestor node	Select the setval_citys assignment node.
   Input parameter	Select the sources of the input parameters.
   Batch synchronization task	Refer to the following step to create and configure a batch synchronization task within the for-each node.

4. Create and configure a batch synchronization task by using the codeless UI. For more information, see Configure a batch synchronization task by using the codeless UI.

   You must take note of the following items.

   Parameter	Description
   scheduling parameters	Configure the following scheduling parameters: bizdate=$[yyyymmdd-1] bizdate_year=$[yyyy-1] bizdate_month=$[mm-1] bizdate_day=$[dd-1]
   request parameters	Configure request parameters for the RESTful API. The province and city parameters are the output parameters of the for-each node. date=${bizdate_year}-${bizdate_month}-${bizdate_day}&province=${dag.foreach.current[0]}&city=${dag.foreach.current[1]}
   province parameter	Configure the province parameter to specify the destination MaxCompute partition. The province parameter is the output parameter of the for-each node. province=${dag.foreach.current[0]}
   city parameter	Configure the city parameter to specify the destination MaxCompute partition. The city parameter is the output parameter of the for-each node. city=${dag.foreach.current[1]}
   ds parameter	Configure the ds parameter to specify the destination MaxCompute partition. The ds parameter is a scheduling parameter. ds=${bizdate}
   field mappings	Specify the fields that you want the RESTful API to return based on the data definition in the RESTful API. The field names are case-sensitive. After you specify the fields, you can click Map Fields with Same Name to map source fields to destination fields whose names are the same, or manually drag lines to map source fields to destination fields.

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

Test the assignment node and for-each node.

1. After you commit and deploy the assignment node and for-each node, find the assignment node on the Cycle Task page in Operation Center and backfill data for the assignment node. For more information, see Backfill data and view data backfill instances (new version).

2. Select a data timestamp and the nodes for which you want to backfill data. The data timestamp includes a start point in time and an end point in time.

3. After data backfill instances are generated for the nodes, check whether the scheduling parameters are correctly rendered in the run logs of the assignment node.

   In the preceding figure, the scheduling parameters are correctly rendered. Data will be written to the province=shanghai,city=shanghai,ds=20231215 partition of the MaxCompute table.

Check whether data is synchronized to MaxCompute

You can create an ad hoc query node in the Ad Hoc Query pane of the DataStudio page to query the data that is synchronized to MaxCompute.

ods_xiaobo_rest5 in the following sample statement is the MaxCompute partitioned table that is created in the Preparation: Create a MaxCompute partitioned table subsection.

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

After you execute the query statement, you can check whether data is successfully synchronized to MaxCompute on the result tab in the lower part of the configuration tab.