Query any REST API using ODBC quickly – Paging any REST API to access all the data (Part 4)

Editor's Note: Progress DataDirect Autonomous REST Connector's User Interface (UI) simplifies the connectivity process. Read the Getting Started tutorial to learn more.

In the earlier tutorials of this 4-part series, we showed you how you can sample and connect to a REST API, query the REST API using SQL and how you can change the metadata by editing the configuration file, when you use Progress Autonomous REST Connector. Before you start this tutorial, we highly recommend you go through the first 3 tutorials.

When you want to access the data from REST API, where it’s exposing large amount of data – most of the APIs implement paging to reduce loads on the server and abuse. In the previous tutorials, when you queried Yelp’s API, you were only seeing data from first page of the API response. Often you would need to access the data beyond the first page and in this tutorial, we will show you how you can get the data beyond the first page when you use Progress Autonomous REST connector to query the Yelp’s REST API using SQL.

Autonomous REST Connector supports the following paging mechanisms:

• Row offset paging
• Page number paging
• Next page token

The following are the basic parameters used to configure paging by mechanism:

#maximumPageSize

The maximum page size in rows. Eg: 50

#firstRowNumber

The number of the first row. The default is 0; however, some systems begin numbering rows at 1.

#pageSizeParameter

The name of the URI parameter that contains the page size.

#rowOffsetParameter

The name of the URI parameter that contains the starting row number for this set of rows.

If your API is based on page number paging, you need to use below parameters to configure paging

#firstPageNumber

Specifies the number of the first page. The default is 0; however, some systems begin numbering pages at 1.

#pageSizeParameter

Specifies the name of the URI parameter that contains the page size.

#pageNumberParameter

When requesting a page of rows, this is the name of the URI parameter to contain the page number.

In this section, we are going to configure the Autonomous REST Connector to page results returned from Yelp.

If we take a look at Yelp’s documentation on how to page results, you will notice that they use offset paging parameters.

Since Yelp uses offset paging parameters, you will need to configure the driver's row offset parameters to control paging.

In the following steps, we will demonstrate configuring paging for the business endpoint:

Navigate to the JSON entry for the business table.

"business":{
  "#path":[
    "https://api.yelp.com/v3/businesses/search"
  ],
  "businesses[]":{
    "id":"VarChar(64)",
    "alias":"VarChar(73)",
    "name":"VarChar(64)",
    "image_url":"VarChar(102)",
    "is_closed":"Boolean",
    "url":"VarChar(309)",
    "review_count":"Integer",
    "categories[]":{
      "alias":"VarChar(19)",
      "title":"VarChar(33)"
    },
    "rating":"Double",
    "coordinates":{
      "latitude":"Double",
      "longitude":"Double"
    },
    "transactions[]":"VarChar(64)",
    "price":"VarChar(64)",
    "location":{
      "address1":"VarChar(64)",
      "address2":"VarChar(64)",
      "address3":"VarChar(64)",
      "city":"VarChar(64)",
      "zip_code":"Integer",
      "country":"VarChar(64)",
      "state":"VarChar(64)",
      "display_address[]":"VarChar(36)"
    },
    "phone":"VarChar(64)",
    "display_phone":"VarChar(64)",
    "distance":"Double"
  },
  "total":"Integer,#key",
  "region":{
    "center":{
      "longitude":"Double",
      "latitude":"Double"
    }
  },
  "location":{
    "#type":"Integer",
    "#virtual":true,
    "#mandatory":true,
    "#default":27617,
    "#eq":"location"
  }
}

After the #path parameter,

Here #maximumPageSize is set to 50 because Yelp returns a maximum of 50 results per page. #firstrow is set to 0, as yelp starts the results with 0. #pageSizeParameter is set to the value limit and #rowOffsetParameter is set to same as limit as per the documentation.

Click save. Your updated file should take the following form:

"business":{
  "#path":[
    "https://api.yelp.com/v3/businesses/search"
  ],
  "#maximumPageSize":50,
  "#firstRowNumber":0,
  "#pageSizeParameter":"limit",
  "#rowOffsetParameter":"offset",
  "businesses[]":{
    "id":"VarChar(64)",
    "alias":"VarChar(73)",
    "name":"VarChar(64)",
    "image_url":"VarChar(102)",
    "is_closed":"Boolean",
    "url":"VarChar(309)",
    "review_count":"Integer",
    "categories[]":{
      "alias":"VarChar(19)",
      "title":"VarChar(33)"
    },
    "rating":"Double",
    "coordinates":{
      "latitude":"Double",
      "longitude":"Double"
    },
    "transactions[]":"VarChar(64)",
    "price":"VarChar(64)",
    "location":{
      "address1":"VarChar(64)",
      "address2":"VarChar(64)",
      "address3":"VarChar(64)",
      "city":"VarChar(64)",
      "zip_code":"Integer",
      "country":"VarChar(64)",
      "state":"VarChar(64)",
      "display_address[]":"VarChar(36)"
    },
    "phone":"VarChar(64)",
    "display_phone":"VarChar(64)",
    "distance":"Double"
  },
  "total":"Integer,#key",
  "region":{
    "center":{
      "longitude":"Double",
      "latitude":"Double"
    }
  },
  "location":{
    "#type":"Integer",
    "#virtual":true,
    "#mandatory":true,
    "#default":27617,
    "#eq":"location"
  }
}

Open WinSQL, or your database or SQL query tool, and execute the following query:

You should receive over 500 record, instead of the 20 records you saw with the default paging.

This concludes this tutorial. If you have any questions, please feel free to contact us.