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

In the previous tutorials of this 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 below tutorials first.

1. Query any REST API using SQL using ODBC in 5 mins - Getting Started (Part 1)
2. Query any REST API using ODBC quickly- Connecting to Multiple endpoints (Part 2)
3. Query any REST API using ODBC quickly – Editing the auto generated schema (Part 3)

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.

1. Autonomous REST Connector supports two types of paging.
   1. Offset
   2. Page Number paging
2. If your API is based on Offset paging, you need to use the below parameters to configure paging
   1. #maximumPageSize

      The maximum page size in rows. Eg: 50

   2. #firstRow

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

   3. #pageSizeParameter

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

   4. #rowOffsetParameter

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

3. If your API is based on page number paging, you need to use below parameters to configure paging
   1. #maximumPageSize

      The maximum page size in rows.

   2. #firstPage

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

   3. #pageSizeParameter
      The name of the URI parameter that contains the page size.
   4. #pageNumberParameter

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

1. Let’s look at the Yelp’s REST API, which we have been using in the past tutorials for demonstrating Autonomous REST Connector. If we take a look at Yelp’s documentation on how to page results, you will notice that they use offset paging. So, we will be using the parameters for offset paging.

   
   
   

2. To configure the paging, we would have to add the offset paging parameters to the config file we edited in the Part 3 of this tutorial series. In the last tutorial we edited the schema to suit our needs and we ended up having the below configuration by the end of the tutorial.

   
   

   "business":{

           "#path":[

               "https://api.yelp.com/v3/businesses/search"

           ],

           "businesses[]":{

               "id":"VarChar(64)",

               "alias":"VarChar(64)",

               "name":"VarChar(64)",

               "image_url":"VarChar(102)",

               "is_closed":"Boolean",

               "url":"VarChar(295)",

               "review_count":"Integer",

               "categories<business_category>[]":{

                   "alias":"VarChar(21)",

                   "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(40)"

               },

               "phone":"VarChar(64)",

               "display_phone":"VarChar(64)",

               "distance":"Double"

           },

           "total":"Integer,#key",

           "region":{

               "center":{

                   "longitude":"Double",

                   "latitude":"Double"

               }

           },

           "location":{

               "#type":"VarChar(64),#key",

               "#virtual":true,

               "#default":"27617",

               "#eq":"location"

           }

       }

   
   

3. Let’s say we need paging for Business endpoint, to do that we need to add the below offset paging parameters to the configuration above.

                                "#maximumPageSize":50,

                                   "#firstrow":0,

                                   "#pageSizeParameter":"limit",

                                   "#rowOffsetParameter":"offset",

   Here #maximumPageSize is set to 50 because Yelp will only provide 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 “offset” as per the documentation of Yelp.

4. You need to add the above parameters to the yelp.rest configuration as shown below to enable paging on business endpoint.
5. Disconnect and re-connect to the Yelp API using Autonomous REST Connector. Now run the below query, you should get 500+ records instead of the default 20 records you were seeing previously.
   
   

We hope this tutorial helped you to get started with Progress DataDirect Autonomous REST connector to connect to any REST API. If you have any questions, please feel free to contact us.