Query multiple REST endpoints with your ODBC application in 5 minutes

In the Part 1 of the tutorial series, we guided you through connecting and querying data on a REST endpoint using the Progress DataDirect Autonomous REST Connector. If you stumbled on this tutorial first, we would recommend you start with Part 1 of this tutorial series.

Also in Part 1, we demonstrated querying data from just a single endpoint; however, REST services are typically comprised of multiple endpoints that return data.

1. Open a text file and save it with a .rest extension. For example, I created the file yelp.rest in a directory called exampleREST: Note that the .rest and file name should use a monospace/code font, not quotes.
   
   
   C:\exampleREST\yelp.rest
2. The Model file uses the JSON format and is comprised of a single object. For each endpoint that you want to query, provide an entry that takes the form " ": . For example: The key will be your table name and the value will be your endpoint URL.
   
   
   "categories":https://api.yelp.com/v3/categories
3. After you have finished, your Model file should resemble the JSON below. Save the file.
   
   
   {

       "categories":"https://api.yelp.com/v3/categories",

       "events":"https://api.yelp.com/v3/events",

       "business":"https://api.yelp.com/v3/businesses/search?location=27617"

   }
4. Notice that for the businesses endpoint, as it requires an input in the form of location, I provided a default value 27617(which is just a postal code) just to make sure that Autonomous REST Connector can sample it and create a table with the correct metadata.

Let’s look at the three endpoints that we are going to sample in this tutorial.

1. Categories: This endpoint returns all the Yelp business categories for all locales by default.
2. Events: This endpoint returns detailed input of Yelp events.
3. Businesses search: This endpoint returns all the businesses in the specified location. This endpoint differs from the other examples in that it requires an input parameter to define the location. To sample this type of endpoint, you must provide a default query parameter as demonstrated below. If necessary, you can override this query parameter using your SQL statement.
   
   
   https://api.yelp.com/v3/businesses/search?location=27617
   
   

In part 1, we sampled a single endpoint by setting the REST Sample Path to a single Yelp endpoint. The REST Sample path, allows you to sample one endpoint at a time, but if you want to sample multiple endpoints, you need to set the REST Config file to yelp.rest file that you have created.

1. Navigate to the Configuration Manager window for your data source. Remove the value from the REST Sample Path Option.
2. In the REST Config File field, specify the path to your Model file. Click Update.
3. Click Test Connect to sample and regenerate the relational view to include your new endpoints.
   
   Open your preferred database/SQL query tool, such as WinSQL, and try executing the following queries.
   Note that we assigned names to our tables in the Model file. These names might differ from the auto-generated names used in Part 1.
   
   

   SELECT * FROM V3_CATEGORIES
   
   SELECT * FROM CATEGORIES WHERE alias LIKE '%food%' 
   
   SELECT C.ALIAS, C.TITLE, CW.COUNTRY_WHITELIST, CW."POSITION" 
   FROM
   CATEGORIES C
   INNER JOIN 
   COUNTRY_WHITELIST CW 
   ON 
   CW.CATEGORIES_ALIAS = C.ALIAS
   
   --Events API
   
   SELECT * FROM EVENTS
   
   SELECT * FROM BUSINESS WHERE LOCATION= 10001 
   
   SELECT * FROM BUSINESS WHERE LOCATION='94016'
   
   SELECT * FROM BUSINESSES B
   INNER JOIN CATEGORIES_1 C ON B."POSITION" = C.BUSINESSES_POSITION
   WHERE B.LOCATION='10001' AND C.LOCATION='10001'
   
   SELECT * FROM BUSINESSES B
   INNER JOIN CATEGORIES_1 C ON B."POSITION" = C.BUSINESSES_POSITION
   WHERE B.LOCATION='10001' AND C.LOCATION='10001'
   AND c.ALIAS LIKE '%brunch%'

Optionally, add additional endpoints to your Model file. Note that you will need to reconnect for the connector to sample and generate tables for the endpoints that you have added. After sampling, you will be able to query these tables.

In the next tutorial, we we will guide you through editing the auto-generated schema.