Query any REST API using JDBC quickly- Connecting to Multiple endpoints (Part 2)

In the Part 1 of the tutorial series, we have walked you through on 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 in to how you can sample multiple endpoints and query them using a single database connection.

In the Part 1 of the tutorial series, we have walked you through on 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 in to how you can sample multiple endpoints and query them using a single database connection.

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 it in the previous tutorial, Categories endpoint, which returns all Yelp business categories across all locales by default.

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

2. The second one is Events endpoint, which returns detailed input of Yelp events.

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

3. The third one 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.
   https://api.yelp.com/v3/businesses/search?location=27617
   

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 it in the previous tutorial, Categories endpoint, which returns all Yelp business categories across all locales by default.

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

2. The second one is Events endpoint, which returns detailed input of Yelp events.

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

3. The third one 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.
   https://api.yelp.com/v3/businesses/search?location=27617
   

1. Open a text file and save it with a “. rest" extension.  In this example, use yelp.rest as the file name.  I have saved this file to my desktop and is available at 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 value will be your table name and value will be your endpoint URL.
   "categories":https://api.yelp.com/v3/categories

   
   
   

3. After you have finished, your “.rest” config file should like as 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 search endpoint, as it requires an input in the form of location, I provided a default value 27617, which is a zip/postal code, just to make sure that Autonomous REST Connector can sample it and create a table with correct metadata.

1. Open a text file and save it with a “. rest" extension.  In this example, use yelp.rest as the file name.  I have saved this file to my desktop and is available at 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 value will be your table name and value will be your endpoint URL.
   "categories":https://api.yelp.com/v3/categories

   
   
   

3. After you have finished, your “.rest” config file should like as 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 search endpoint, as it requires an input in the form of location, I provided a default value 27617, which is a zip/postal code, just to make sure that Autonomous REST Connector can sample it and create a table with correct metadata.

1. In the previous tutorial, we tried to sample one endpoint and so we set the ‘sample’ connection property to that URL. The sample property 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 use the ‘config’ connection parameter.
2. To sample the URLs in the above configuration file, change the JDBC URL to the below
   jdbc:datadirect:autorest:config=C:\<path-to-file>\yelp.rest;
   
   
   
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.

   
   

   --CATEGORIES ENDPOINT

   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, Query any REST API using JDBC quickly – Editing the auto generated schema (Part 3), we will walk you through on advanced configuration options in REST Config file to let you customize the schema as you need. If you have any questions or issues, please feel free to talk to us.

1. In the previous tutorial, we tried to sample one endpoint and so we set the ‘sample’ connection property to that URL. The sample property 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 use the ‘config’ connection parameter.
2. To sample the URLs in the above configuration file, change the JDBC URL to the below
   jdbc:datadirect:autorest:config=C:\<path-to-file>\yelp.rest;
   
   
   
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.

   
   

   --CATEGORIES ENDPOINT

   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, Query any REST API using JDBC quickly – Editing the auto generated schema (Part 3), we will walk you through on advanced configuration options in REST Config file to let you customize the schema as you need. If you have any questions or issues, please feel free to talk to us.