Query any REST API using ODBC in 5 mins - Getting Started (Part 1)

Editor's Note: Progress DataDirect Autonomous REST Connector's User Interface (UI) simplifies the connectivity process. Read the Getting Started tutorial to learn more.

Many organizations are leveraging APIs to create data services to effectively govern how data is accessed, where it is accessed and by whom. While modernization drives companies to produce and consume APIs, the applications and tools they leverage are still SQL-based, making it difficult and time-consuming to connect these REST and SQL-based tools. Autonomous REST Connector solves this problem by providing seamless connectivity and integration with REST APIs, OData endpoints and Progress OpenEdge JSDO catalogs.

Progress Autonomous REST Connector allows you to query API data using standard SQL by normalizing the semi-structured JSON response data into structured data that can be used with SQL. There’s no need to understand the complexities of the JSON response to start effectively working with the data.

One of my favorite websites to go to for checking out reviews on restaurants and businesses is Yelp. Yelp has a REST API which you can use to access the data and reviews about local restaurants and businesses. In this tutorial, we will walk you through how you can connect to Yelp’s REST API using Autonomous REST ODBC connector and show you how easy it is to query the data from REST API using SQL.

 Let’s get started.

1. To use the Yelp Fusion API, you need to register as a developer and create an app on the Yelp developer website.
2. Provide App Name, Industry, Contact Email and Description to create your App. You should now see the ClientID and API Key on your screen, which will help you authenticate with Yelp’s API.
3. There are a lot of endpoints in Yelp API, in this tutorial, let’s start with a simple endpoint of Yelp API – Category Endpoint. This endpoint returns all the business categories in Yelp. All you got to do is send a GET request to the below endpoint with Authentication header containing the API Key we got from Yelp.
   https://api.yelp.com/v3/categories
4. The response from the API is simple too, with an array of objects. Let’s query this endpoint from Autonomous REST connector.

1. If you have installed 32-bit Autonomous REST Connector, open ODBC Administrator(32-bit) else open ODBC Administrator(64-bit)
2. Click on Add to create new data source and choose DataDirect 8.0 Autonomous REST Connector as shown below.

   
   
   

3. Click on Finish and now you should see the below form. Fill out the Name of the data source and set REST Sample Path to https://api.yelp.com/v3/categories which is the categories endpoint we discussed as shown below

   
   
   

4. Now go to Security Tab and under authentication configure it as follows:

    

   Authenticationmethod: HttpHeader

   Authheader: Authorization

   Securitytoken: Bearer <Your API Key from yelp>

   
   

5. Now click on Test Connect and it should Connect Successfully. Click on Apply and OK to save the configuration.

1. Use your favorite DB Query tool to connect to Yelp API via the ODBC configuration you have just created. I use a tool called WinSQL for this tutorial to connect and query ODBC Data sources.
2. When you create a connection in WinSQL, you should find the list of ODBC data sources on your machine, in which you should find the data source that you have created in the previous section as shown below. Click on Connect.

   
   
   

3. Now if you go to Catalog Tab and open the Tables node, you should find a list of tables Autonomous REST Connector has created for you by parsing the Yelp API and normalizing the response.
   
   
   
   
4. You can now query data from Yelp API in real-time using these tables. Try running below queries
   
   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
   
   
5. In the above queries, if you have noticed I am using the TITLE field from V3_CATEGORIES table with other tables COUNTRY_BLACKLIST, COUNTRY_WHITELIST and PARENT_ALIASES. This is because when we sampled the REST API, the driver chose TITLE in the V3_CATEGORIES table as a primary key and has made the references of this field in the child/nested tables COUNTRY_BLACKLIST, COUNTRY_WHITELIST and PARENT_ALIASES.
6. The only remaining table we haven’t touched is _CONFIGURATION table. If you query _CONFIGURATION table, you should find the schema Autonomous REST connector generated when it sampled the categories API as shown below.
   
   {

     "v3_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)"

     }

   }
7. And if you notice, the connector has detected all the fields, their datatypes and have set a primary key for the table by #key. In this case it has set the primary key to TITLE in V3_CATEGORIES by default and it has made references to that column in its child tables. That’s why we used the TITLE column to join with the child tables of categories table.

It is so easy to connect to REST API’s and query them using SQL and you don’t have to write single line of code when you use Autonomous REST Connector. In this tutorial, we looked in to how you can sample and query simple REST API using SQL, but that’s just the beginning. In the Part 2 of this tutorial we will look in to how you can connect to multiple endpoints and how you can make changes to the schema configuration to suit your needs. Feel free to download the Autonomous REST connector and connect to your favorite REST API’s and contact us if you have any questions or issues.