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

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

Let’s get started.

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.

To install the ODBC Autonomous REST Connector for Windows platforms:

1. Download the driver from here.
2. Unzip the installer files to a temporary directory.
3. From the installer directory, run the appropriate installer file to start the installer. The installer file takes the following form:
   PROGRESS_DATADIRECT_ODBC_nn_WIN_xx_INSTALL.exe
4. Follow the prompts to complete installation.

On the User DSN tab, click Add to display a list of installed drivers. Select the DataDirect Autonomous REST Connector and click Finish to display the driver Setup dialog box.

In the Data Source Name field, specify the name of your data source. For example, Autonomous REST Connector.

In the REST Sample Path field, provide the REST Endpoint containing the data you want to access.
There are a lot of endpoints in the Yelp API. However, for the purposes of this tutorial, specify the Category Endpoint:

This endpoint returns all the business categories in Yelp.

Click TEST Connect; then, Execute. A list of relational tables generated from the end point is displayed in the Query Results.

Additional Step:
In the query field, test your connection by querying one of the tables. For example:
SELECT * FROM V3_CATEGORIES

Now you are ready to query your Yelp endpoint with a SQL application.

You can now begin using your favorite database or SQL query tool to connect using the data source you created in the previous section. For this tutorial, we will use a tool called WinSQL to connect and query our Yelp data.

Open WinSQL. The Database Connection window opens.

Select the tab for your data source. Expand the Tables node to expose the relational tables that the driver generated from sampling the response from your Yelp endpoint.

Try running the following queries by copying them into the query field and clicking Execute.

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