Query any REST API using JDBC quickly – Paging any REST API to access all the data (Part 4)
Introduction
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.
- Query any REST API using SQL via JDBC in 5 mins - Getting Started (Part 1)
- Query any REST API using JDBC quickly- Connecting to Multiple endpoints (Part 2)
- Query any REST API using JDBC 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.
Introduction to Paging configuration
- Autonomous REST Connector supports two types of paging.
- Offset
- Page Number paging
- If your API is based on Offset paging, you need to use the below parameters to configure paging
- #maximumPageSize
The maximum page size in rows. Eg: 50
- #firstRow
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.
- #maximumPageSize
- If your API is based on page number paging, you need to use below parameters to configure paging
- #maximumPageSize
The maximum page size in rows.
- #firstPage
The number of the first page. The default is 0; however, some systems begin numbering pages at 1.
- #pageSizeParameter
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.
- #maximumPageSize
Paging Yelp’s REST API
- 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.
- 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.
{"categories": {"#path": ["https://api.yelp.com/v3/categories /categories"],"alias":"VarChar(48)","title":"VarChar(52),#key","parent_aliases[]":"VarChar(34)","country_whitelist[]":"VarChar(3)","country_blacklist[]":"VarChar(3)"},"events": {"#path": [],"events[]": {"attending_count":"Integer","category":"VarChar(64)","cost":"VarChar(64)","cost_max":"VarChar(64)","description":"VarChar(238)","event_site_url":"VarChar(310)","id":"VarChar(75)","image_url":"VarChar(102)","interested_count":"Integer","is_canceled":"Boolean","is_free":"Boolean","is_official":"Boolean","latitude":"Double","longitude":"Double","name":"VarChar(64)","tickets_url":"VarChar(64)","time_end":"Timestamp(0)","time_start":"Timestamp(0)","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(64)","cross_streets":"VarChar(64)"},"business_id":"VarChar(64)"},"total":"Integer,#key"},"business": {"#path": [],"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"}}}
- 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.
- You need to add the above parameters to the yelp.rest configuration as shown below to enable paging on business endpoint.
{"categories": {"#path": ["https://api.yelp.com/v3/categories /categories"],"alias":"VarChar(48)","title":"VarChar(52),#key","parent_aliases[]":"VarChar(34)","country_whitelist[]":"VarChar(3)","country_blacklist[]":"VarChar(3)"},"events": {"#path": [],"events[]": {"attending_count":"Integer","category":"VarChar(64)","cost":"VarChar(64)","cost_max":"VarChar(64)","description":"VarChar(238)","event_site_url":"VarChar(310)","id":"VarChar(75)","image_url":"VarChar(102)","interested_count":"Integer","is_canceled":"Boolean","is_free":"Boolean","is_official":"Boolean","latitude":"Double","longitude":"Double","name":"VarChar(64)","tickets_url":"VarChar(64)","time_end":"Timestamp(0)","time_start":"Timestamp(0)","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(64)","cross_streets":"VarChar(64)"},"business_id":"VarChar(64)"},"total":"Integer,#key"},"business": {"#path": [],"#maximumPageSize": 50,"#firstrow": 0,"#pageSizeParameter":"limit","#rowOffsetParameter":"offset","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"}}}
- 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.
In the next tutorial, we will walk you through how you can push down any filters to REST API using query parameters. If you have any questions or issues until now, please feel free to talk to us.