Connect to any REST API from SAS using JDBC interface

SAS is the leader in analytics. Through innovative analytics, BI and data management software and services, SAS helps turn your data into better decisions. A lot of organizations now use REST API to expose and consume data – and there are scenarios where we often see from time to time where they want to store this data coming from REST APIs to provide real time intelligence or analytics. The problem is each of these REST APIs are built differently – their authentication schemes differ, the response structures differ and when you want to bring in this data to SAS - which can be a lot of effort, that can be placed elsewhere.

With Progress DataDirect Autonomous REST Connector, you can connect to any REST API without you having to write a single line of code and be able to run SQL queries to access the data via SAS/ACCESS Interface to JDBC. In this tutorial we will show how you can use Autonomous REST Connector with SAS so that you can read any REST API. We will be using Yelp API as a part of this tutorial and use SSAS/ACCESS Interface to JDBC to read data from Yelp API using Autonomous REST Connector.

1. Download and Install Autonomous REST JDBC connector from our website.
2. Install the connector by running the setup executable file on your machine. The installation process is straight forward, just follow the instructions on the installer.
3. After you have finished installation, you should find the Autonomous REST connector at the below default path, unless you have chosen to install it at a different location.
   
   
   C:\Program Files\Progress\DataDirect\JDBC_60\lib\autorest.jar
   
   

1. To use the Yelp Fusion API, you need to register as a developer and create an app on the Yelp developer website.
2. Provide App Name, Industry, Contact Email and Description to create your App. You should now see the ClientID and API Key on your screen, which will help you authenticate with Yelp’s API.

1. To connect to the Yelp API using Autonomous REST connector, you can just point it to the endpoint with some default values.
   
   
2. For the purpose of the tutorial, I will connect to business search endpoint offered by the Yelp. Let’s say I want to get all the business in my area or search the businesses of specific category.
   
   
3. To configure the driver to connect to the above endpoint, all you must do is to use the below JDBC URL

4. When you connect to any REST API using Autonomous REST Connector, it will automatically sample the API and create a configuration, which you can access by querying the _CONFIGURATION table. You can get this configuration by using the Autonomous REST JDBC driver in any SQL querying tools like Dbeaver, Squirrel SQL etc.,

5.For the tutorial, I am providing this configuration here, download this config file from GitHub and save it as yelp.rest.

6. If you notice the configuration, you can notice that Autonomous REST Connector has detected all the objects, and their data types.

7. To learn more about Autonomous REST Connector and how you can configure it to your needs and to connect to multiple endpoints, we recommend you go through these tutorials after you have finished this one.

1. Getting Started
2. Connecting to Multiple Endpoints
3. Editing Auto generated Schema
4. Paging any REST API

1. With SAS/ACCESS Interface to JDBC, you can now easily connect to your data in any REST API using Progress DataDirect Autonomous REST driver.
2. To get started, Open SAS and create a New Program by going to File->New Program.
3. Below is a sample program you can use to connect to Yelp API and read the data from Businesses table. Don’t forget to change the path to config file (yelp.rest) you downloaded in previous section in the below code

    

   libname x jdbc driverclass="com.ddtek.jdbc.autorest.AutoRESTDriver"

   URL="jdbc:datadirect:autorest:config=C:\Users\sbobba\Desktop\ARCConfigFiles\yelp.rest;databasename=yelp2w1q;authenticationMethod=HttpHeader;authheader=Authorization;SecurityToken='Bearer <Access Token>'"

   classpath="C:\Users\sbobba\Downloads\Debug Drivers";

   proc print data=x.businesses;

   run;

    

4. Now when you run the program, you should see your data as shown below.

 

With SAS/ACCESS Interface to JDBC, you can now connect to any of your internal/external REST API as easily as that. If you prefer using SAS/ACCESS for ODBC, we also have an ODBC offering of Progress Autonomous REST Connector, which you can download and try it for free. Not only you can connect to REST API’s using Progress DataDirect drivers, you can connect to sources such as MongoDB, DB2, Redshift, Oracle, SQL Server, Eloqua etc., Feel free to try any our JDBC drivers and if you have any questions, please contact us.