Query any REST API via ODBC in 5 mins: Connecting to Multiple endpoints (Part 2)

Introduction

In the Part 1 of the tutorial series, we walked you through how you can connect to a REST API (Yelp) endpoint using Progress DataDirect Autonomous REST Connector and query the Yelp REST API using SQL. If you stumbled on this tutorial first, we would recommend you start with Part 1 of this tutorial series.

But we have only shown you how you can sample and connect to just one endpoint that Yelp REST service offers. Often, each REST API offers multiple endpoints from which you can get the data. In this tutorial, we will look into how you can sample multiple endpoints and query them using a single database connection.

Create REST Config file

  1. Open a text file and save it with a “.rest" extension.  In this example, I use yelp.rest as the file name.  I have saved this file to my desktop and it is available at the path

     

    C:\Users\sbobba\Desktop\yelp.rest
  2. The rest config file is formatted as JSON and is comprised of a single object.  You need to add a key and value for each of the endpoints that you want to connect. The key will be your table name and the value will be your endpoint URL.

  3. After you have finished, your “.rest” config file should like like the JSON below. Save the file.


  4. Notice that for the businesses search 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.

A look at Yelp Endpoints

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

  1. The first one, we have already done in the previous tutorial, Categories endpoint. This endpoint returns all Yelp business categories across all locales by default.
  2. The second one is Events endpoint, which returns detailed input of Yelp events.


  3. The third option is the Business search endpoints, which returns all the business in the location specified. This endpoint is a bit different from above two endpoints as it needs a mandatory input parameter for latitude and longitude or location name. To sample this type of endpoints, all you must do is provide a default value as an input parameter as shown below. When querying this endpoint using SQL, if you provide another location, that value will be pushed down, and you will get the appropriate results, else it will default to the value you have provided when you sampled.

Configure and Connect

  1. In the previous tutorial, we tried to sample one endpoint by setting the REST Sample Path to a single Yelp endpoint. The REST Sample path will only let you sample one endpoint at a time, but if you want to sample multiple endpoints from the REST Config file we just created, you need to set the REST Config file path to yelp.rest file that you have just created.
  2. Open ODBC Administrator and remove the URL from REST Sample Path and add the path to yelp.rest file on your machine to REST Config file connection parameter as shown below


    6

  3. Now, try reconnecting. Autonomous REST Connector will sample all the REST endpoints you have provided in the config file, normalize them and create tables automatically.

     

  4. Feel free to run the below queries to get the data from Yelp API.

    SELECT * FROM V3_CATEGORIES
     
    SELECT * FROM V3_CATEGORIES WHERE alias LIKE '%food%'
     
    SELECT C.ALIAS, C.TITLE, PA.PARENT_ALIASE, PA.POSITION FROM V3_CATEGORIES C
    INNER JOIN PARENT_ALIASES PA ON PA.V3_CATEGORIES_TITLE = C.TITLE
     
    SELECT C.ALIAS, C.TITLE, CB.COUNTRY_BLACKLIST, CB."POSITION" FROM V3_CATEGORIES C
    INNER JOIN COUNTRY_BLACKLIST CB ON CB.V3_CATEGORIES_TITLE = C.TITLE
     
     
    SELECT C.ALIAS, C.TITLE, CW.COUNTRY_BLACKLIST, CW."POSITION" FROM V3_CATEGORIES C
    INNER JOIN COUNTRY_WHITELIST CW ON CW.V3_CATEGORIES_TITLE = C.TITLE
     
    --Events API
    SELECT * FROM EVENTS_1
     
    --Location can be zip code, city, state
    SELECT * FROM BUSINESS WHERE LOCATION='New York'
    SELECT * FROM BUSINESSES WHERE BUSINESS_LOCATION='San Francisco'
     
    SELECT * FROM BUSINESSES B
    INNER JOIN CATEGORIES_1 C ON B."POSITION" = C.BUSINESSES_POSITION
    WHERE B.BUSINESS_LOCATION='Denver' AND C.BUSINESS_LOCATION='Denver'
     
     
    SELECT * FROM BUSINESSES B
    INNER JOIN CATEGORIES_1 C ON B."POSITION" = C.BUSINESSES_POSITION
    WHERE B.BUSINESS_LOCATION='Denver' AND C.BUSINESS_LOCATION='Denver' AND c.ALIAS LIKE '%brunch%'

     

  5. Feel free to add an as many endpoints as you like to the REST config. Once you have added all the endpoints, you can reconnect, and Autonomous REST Connector will sample, normalize and make the endpoints available as tables for you to query using SQL.

    In the next tutorial, we will walk you through on advanced configuration options in REST Config file to let you customize the connectivity as you need. If you have any questions or issues, please feel free to talk to us.

ODBC TUTORIAL

Query any REST API via ODBC in 5 mins: Connecting to Multiple endpoints (Part 2)

View all Tutorials

Connect any application to any data source anywhere

A product specialist will be glad to get in
touch with you