In the part 1 of the tutorial series, we guided you through connecting and querying data on a REST endpoint using the Progress DataDirect Autonomous REST Connector. If you stumbled on this tutorial first, we would recommend you start with Part 1 of this tutorial series.
Also in Part 1, we demonstrated querying data from just a single endpoint; however, REST services are typically comprised of multiple endpoints that return data.
Open a text file and save it with a .rest extension. For example, I created the file yelp.rest in a directory called exampleREST:
Note that the .rest and file name should use a monospace/code font, not quotes.
C:\exampleREST\yelp.restThe Model file uses the JSON format and is comprised of a single object. For each endpoint that you want to query, provide an entry that takes the form "
"categories":https://api.yelp.com/v3/categoriesAfter you have finished, your Model file should resemble the JSON below. Save the file.
"categories":"https://api.yelp.com/v3/categories",
"events":"https://api.yelp.com/v3/events",
Notice that for the businesses endpoint, as it requires an input in the form of location, I provided a default value 27617(which is just a postal code) just to make sure that Autonomous REST Connector can sample it and create a table with the correct metadata.
Let’s look at the three endpoints that we are going to sample in this tutorial.
Categories: This endpoint returns all the Yelp business categories for all locales by default.
Events: This endpoint returns detailed input of Yelp events.
Businesses search: This endpoint returns all the businesses in the specified location. This endpoint differs from the other examples in that it requires an input parameter to define the location. To sample this type of endpoint, you must provide a default query parameter as demonstrated below. If necessary, you can override this query parameter using your SQL statement.
In part 1 we sampled a single endpoint by setting the REST Sample Path to a single Yelp endpoint. The REST Sample path, allows you to sample one endpoint at a time, but if you want to sample multiple endpoints, you need to set the REST Config file to yelp.rest file that you have created.
Open your preferred database/SQL query tool, such as WinSQL, and try executing the following queries.
Note that we assigned names to our tables in the Model file. These names might differ from the auto-generated names used in Part 1.
SELECT
*
FROM
V3_CATEGORIESSELECT * FROM V3_CATEGORIES WHERE alias LIKE '%food%'SELECT C.ALIAS, C.TITLE, CW.COUNTRY_WHITELIST, CW."POSITION"FROM CATEGORIES CINNER JOIN COUNTRY_WHITELIST CW ON CW.CATEGORIES_ALIAS = C.ALIASSELECT C.ALIAS, C.TITLE, CB.COUNTRY_BLACKLIST, CB."POSITION" FROM V3_CATEGORIES CINNER JOIN COUNTRY_BLACKLIST CB ON CB.V3_CATEGORIES_TITLE = C.TITLESELECT C.ALIAS, C.TITLE, CW.COUNTRY_BLACKLIST, CW."POSITION" FROM V3_CATEGORIES CINNER JOIN COUNTRY_WHITELIST CW ON CW.V3_CATEGORIES_TITLE = C.TITLE--Events APISELECT * FROM EVENTSSELECT
* FROM
BUSINESS WHERE
LOCATION=10001SELECT * FROM BUSINESS WHERE LOCATION='94016'SELECT * FROM BUSINESSES BINNER JOIN CATEGORIES_1 C ON B."POSITION" = C.BUSINESSES_POSITIONWHERE B.BUSINESS_LOCATION='10001' AND C.LOCATION='10001'SELECT * FROM BUSINESSES BINNER JOIN CATEGORIES_1 C ON B."POSITION" = C.BUSINESSES_POSITIONWHERE B.LOCATION='10001' AND C.LOCATION='10001' AND c.ALIAS LIKE '%brunch%'Optionally, add additional endpoints to your Model file. Note that you will need to reconnect for the connector to sample and generate tables for the endpoints that you have added. After sampling, you will be able to query these tables.
In the next tutorial, we will guide you through editing the auto-generated schema.