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

While modernization initiatives drive organizations 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.

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. However, you do not have to understand complexities of REST responses to begin working with REST data with the Autonomous REST Connector

A popular website 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 JDBC connector and show you how easy it is to query the data from REST API using SQL.

Let’s get started.

• Download the connector from the Autonomous REST Connector for JDBC webpage.
• Unzip the installer files to a temporary directory.
• From the installer directory, run the installer file to start the installer.
• Follow the prompts to complete installation.

To use the Yelp Fusion API, you must first obtain the client ID register as a developer and create an app on the Yelp developer site. You must provide the following information when creating your App:

• App name
• Industry
• Contact email
• Description

After you create your App, you will be provided with the Client ID and API Key that are used to authenticate to the Yelp Fusion API.

You must configure your analytics or SQL tool to use the connector before you can query REST data.

In this section, we use the Dbeaver query tool to demonstrate the configuration process. But this process will vary depending on the tool you are using.

Click New and enter the following values in the form.
Driver Name: ARC
Class Name: com.ddtek.jdbc.autorest.AutoRESTDriver

Click Add File and navigate to the location of the autorest.jar file. This is the default location of the connector jar file:

The form should have the information shown below

After configuring your analytics or SQL tool, you may proceed with querying Yelp. The following procedure shows us how to use the Autonomous REST Connector to sample Yelp data and query it, using Dbeaver. When sampling the data, the connector queries the Yelp endpoint, normalizes the JSON data, and displays in a tabular form. In the last step, we provide example SQL for querying Yelp

1. From Dbeaver, go to Database > New Connection.
   
   

   

2. Select the Autonomous REST Connector ("ARC" in this example). Then, click Next.
   
   

   

3. Enter the JDBC URL. The Sample connection property is used to specify the endpoint you want the connector to query. In this example, the Yelp "categories" endpoint is being sampled.
   
   
   jdbc:datadirect:autorest:sample=https://api.yelp.com/v3/categories
   
   
4. Select "Driver properties" from "Connection settings," and enter the required authentication information. In this example, the following must be provided:
   
   
   • Authenticationmethod: HttpHeader
   • Authheader: Authorization
   • Securitytoken: Bearer
5. Click "Test Connection" to connect. Then, click OK.
   
6. Open a new SQL editor in Dbeaver by going to SQL Editor > New SQL Editor. Then select the connection we just created.
   
   

   

7. To view the sampled categories data, expand the window in the Database Navigator. The connector has the normalized JSON data it obtained from the categories endpoint.
   
   
8. Run additional queries against the categories endpoint. Here are sample queries you can try:
   
   

   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_ALIAS = C.ALIAS
   
   SELECT C.ALIAS, C.TITLE, CB.COUNTRY_BLACKLIST, CB. "POSITION" FROM 
   V3_CATEGORIES C INNER JOIN COUNTRY_BLACKLIST CB ON 
   CB.V3_CATEGORIES_ALIAS = C.ALIAS
   
   SELECT C.ALIAS, C.TITLE, CW.COUNTRY_WHITELIST, CW. "POSITION" FROM 
   V3_CATEGORIES C INNER JOIN COUNTRY_WHITELIST CW ON 
   CW.V3_CATEGORIES_ALIAS = C.ALIAS
   

   
   

   You may have noticed that several of the example queries use the ALIAS field from V3_CATEGORIES table when querying other tables. This is because the ALIAS field was designated the primary key by the driver. When sampling, the driver determines which field in the parent table, V3_CATEGORIES in this example, is best suited to be the primary key. Child tables, such as COUNTRY_BLACKLIST and COUNTRY_WHITELIST, use this field, along with the POSITION field, to form the foreign key relationship

   {

     "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 field designated as the primary key is indicated by the #key element. In this example, the primary key is the alias field.

In Part 2 of this tutorial we will connect to multiple endpoints