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.
C:\Program Files\Progress\DataDirect\JDBC_60\lib\autorest.jar
Name: <Any Name>
C:\Program Files\Progress\DataDirect\JDBC_60\lib\autorest.jar
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.
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
{
"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.