RestAPI Reader best practices

Updated at:

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.

Note

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.

  • Basic Auth: Basic authentication.

    If the data source API supports authentication with a username and password, select this method. Then, configure the username and password. During data integration, the credentials are sent to the RESTful endpoint through the Basic Auth protocol for authentication.

  • Token Auth: Token-based authentication.

    If the data source API supports token-based authentication, select this method. Then, configure a fixed token value. During data integration, the token is passed in the request header for authentication. For example: {"Authorization":"Bearer TokenXXXXXX"}.

    Note

    To use a custom encryption method, you can use the Token authentication method and provide the encrypted authentication information as the AuthToken.

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:

Note

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.

Note

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

  1. 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.

  2. 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_day resolves 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:

Note

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=1

    pageSize 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.

Note

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

  1. 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.

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

Note

Adjust the configuration to match your own API. The sample API only demonstrates the procedure.

  • Sample request:

    http://TestAPIAddress:Port/rest/test3

    The 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.

Note

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

  1. 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.

  2. 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.

Note

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.

Note

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

  1. 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.

  2. 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.

  3. 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.

  4. 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

  1. 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.

  2. Select the business date and downstream nodes for the backfill task.

  3. 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