Editor's Note: Progress DataDirect Autonomous REST Connector's User Interface (UI) simplifies the connectivity process. Read the Getting Started tutorial to learn more.
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 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.
Authenticationmethod: HttpHeader
Authheader: Authorization
Securitytoken: Bearer <
Your
API Key from 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_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)"
}
}
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.