Pull Data From Any REST API using SQL via JDBC in 5 mins - Getting Started Tutorial (Part 1/4)

Many organizations are leveraging APIs to create data services for effectively governing 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. Progress DataDirect Autonomous REST Connector solves this problem by providing seamless connectivity and integration with REST APIs, OData endpoints and Progress OpenEdge JSDO catalogs.

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 our favorite websites for restaurant and business reviews is Yelp. Yelp has a REST API which can access data and reviews. In this tutorial, we will walk through how to connect to Yelp’s REST API using Autonomous REST Connector for JDBC and explain how easy it is to query the data from REST API using SQL.

Let’s get started.

1. Download and install Autonomous REST Connector for JDBC.
2. Install the connector by running the setup executable file.
3. After finishing installation, find the Autonomous REST Connector at the below default path, unless it was installed in a different location.

1. To use the Yelp Fusion API, register as a developer and create an app on the Yelp developer website.
2. Provide App Name, Industry, Contact Email and Description to create the App. The ClientID and API Key on the screen will help to authenticate with Yelp’s API.

1. To query using Autonomous REST Connector for JDBC, we recommend starting with a database query tool like Dbeaver or Squirrel SQL.
2. In this tutorial, we will use Dbeaver as the database query tool. First, let’s register Autonomous REST Connector with Dbeaver. Go to Database and Driver Manager.  

    
   
   

3. Click on New and see a form for registering the driver. Fill in the below details:

   Name: <Any Name>  Class Name: com.ddtek.jdbc.autorest.AutoRESTDriver

4. Click on Add File and navigate to the location of autorest.jar, which should be in the below default install location, unless the connector was installed in a different location.

    

   C:\Program Files\Progress\DataDirect\JDBC_60\lib\autorest.jar

    

5. The form should have the information as shown below, when finished with the steps above. Click on OK to register the connector.
   
   
   

1. Let’s start with a simple endpoint of Yelp API – Category Endpoint. This endpoint returns all the business categories in Yelp. 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
2. The response from the API is simple with an array of objects. Let’s query this endpoint from Autonomous REST Connector. In Dbeaver, go to Database then New Connection.
3. Choose the driver registered in the previous section. We will choose Autonomous REST Connector and click on next.

   
   
   

4. Now we are in the connection configuration window. Before we go into configuration, let's explain what’s going to happen in the next few steps. The driver as of now doesn’t know anything about the API or how to interact with it. What we are going to do is tell the driver to sample the Yelp API by providing the information on how to authenticate with the API. The driver then connects to the API, gets the response and automatically normalizes the response JSON in to relational tabular format.

    

5. Let’s start with JDBC URL. Here we are going to provide to the Autonomous REST Connector the endpoint it has to sample and connect. We are trying to connect to Categories Endpoint, so let’ set the JDBC URL to
   
   
   jdbc:datadirect:autorest:sample=https://api.yelp.com/v3/categories

   where sample is a connection property to specify the endpoint it has to sample and let us query it via SQL.
   
   
   

6. Now, let’s tell the driver how to authenticate with Yelp’s API. Go to Driver properties and set the below properties:
   1. Authenticationmethod: HttpHeader
   2. Authheader: Authorization
   3. Securitytoken: Bearer <Your API Key from yelp>
7. Now, click on Test Connection and to connect successfully. Click on OK.
8. Open a new SQL Editor by going to SQL Editor then New SQL Editor. Select the connection just created as shown below.
   
   
   
   
9. Expand the connection in the Database Navigator on the left to find the JSON normalized as Relational tables under AUTOREST schema.
10. To learn more about how Autonomous REST Connector normalizes JSON objects to tables, we recommend you to take a look at this document.
11. Now try running some of these queries to access Yelp API. Use any query.

     

    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
    
    

     

12. In the above queries, we are 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.
13. The only remaining table we haven’t touched is _CONFIGURATION table. Query the _CONFIGURATION table to 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)"
  }
}

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 without writing a single line of code when we use Progress DataDirect Autonomous REST Connector. In this tutorial, we looked in to how to sample and query simple REST APIs using SQL, but that’s just the beginning. In the Part 2 of this tutorial we will look at how to connect to multiple endpoints and how you to make changes to the schema configuration. Feel free to download the Progress DataDirect Autonomous REST Connector and connect to your favorite REST APIs.