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.
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:
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
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
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