Query any REST API using SQL via JDBC in 5 mins - Getting Started (Part 1)

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 for to check out reviews on restaurants and businesses is Yelp. Yelp has a REST API using which you can access the data and reviews about local restaurants, businesses. In this tutorial, we will walk you through how you can connect to Yelp’s REST API using Autonomous REST JDBC connector and show you how easy it is to query the data from REST API using SQL.

Let’s get started.

1. Download and Install Autonomous REST JDBC connector from our website.
2. Install the connector by running the setup executable file on your machine. The installation process is straight forward, just follow the instructions on the installer.
3. After you have finished installation, you should find the Autonomous REST connector at the below default path, unless you have chosen to install it at a different location.

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.

1. To query using Autonomous REST JDBC connector, we recommend you start with a database query tool like Dbeaver or Squirrel SQL.
2. In this tutorial, I will be using Dbeaver as my database query tool. First off, let’s register Autonomous REST Connector with Dbeaver. Go to Database -> Driver Manager. You should see the Driver Manager as shown below.

    
   
   

3. Click on New and you should 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 you chose to install the connector to a different location.

    

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

    

5. Your form should have the information as shown below, when you are done with all 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. 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
2. The response from the API is simple too, with an array of objects. Let’s query this endpoint from Autonomous REST connector. In Dbeaver, go to Database -> New Connection.
3. Choose the driver which you registered in the previous section, in my case it was ARC. So, I choose ARC and click on next.

   
   
   

4. Now you should be in the connection configuration window. Before we go in to configuration, let me 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 provide the driver on 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 you should be able to connect successfully. Click on OK.
8. Now open a new SQL Editor by going to SQL Editor -> New SQL Editor. Select the connection you have just created as shown below.
   
   
   
   
9. If you expand the connection in the Database Navigator in the left, you should 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. You can 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, 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.
13. 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)"
  }
}

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.