In the Part 1 of the tutorial series, we have walked you through on how you can connect to a REST API (Yelp) endpoint using Progress DataDirect Autonomous REST Connector and query the Yelp REST API using SQL. If you stumbled on this tutorial first, we would recommend you start with Part 1 of this tutorial series.
But we have only shown you how you can sample and connect to just one endpoint that Yelp REST service offers. Often, each REST API offers multiple endpoints from which you can get the data. In this tutorial, we will look in to how you can sample multiple endpoints and query them using a single database connection.
Let’s look at the 3 different endpoints that we are going to sample in this tutorial.
C:\Users\sbobba\Desktop\yelp.rest
"categories":https://api.yelp.com/v3/categories
jdbc:datadirect:autorest:config=C:\<
path-to-file
>\yelp.rest;
--CATEGORIES ENDPOINT
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
--Events API
SELECT
*
FROM
EVENTS_1
--Location can be zip code, city, state
SELECT
*
FROM
BUSINESS
WHERE
LOCATION=
'New York'
SELECT
*
FROM
BUSINESSES
WHERE
BUSINESS_LOCATION=
'San Francisco'
SELECT
*
FROM
BUSINESSES B
INNER
JOIN
CATEGORIES_1 C
ON
B.
"POSITION"
= C.BUSINESSES_POSITION
WHERE
B.BUSINESS_LOCATION=
'Denver'
AND
C.BUSINESS_LOCATION=
'Denver'
SELECT
*
FROM
BUSINESSES B
INNER
JOIN
CATEGORIES_1 C
ON
B.
"POSITION"
= C.BUSINESSES_POSITION
WHERE
B.BUSINESS_LOCATION=
'Denver'
AND
C.BUSINESS_LOCATION=
'Denver'
AND
c.ALIAS
LIKE
'%brunch%'
In the next tutorial, Query any REST API using JDBC quickly – Editing the auto generated schema (Part 3), we will walk you through on advanced configuration options in REST Config file to let you customize the schema as you need. If you have any questions or issues, please feel free to talk to us.