Connect to any REST API from Tableau

Introduction

Do you want to access REST API data from Tableau for analytics or data visualization? It can be tricky, as connecting to any REST API usually requires investing time and custom programming to get it right. Fortunately, with Progress DataDirect Autonomous REST Connector, you can instantly connect to any REST API without any code and query your REST API using SQL 92.

Autonomous REST Connector uses intelligent sampling to normalize the JSON responses from REST API and make them available as relational tables. You are empowered to use the full capabilities of SQL, including joining data from different endpoints.

To get you started on how you can use the Autonomous REST Connector with Tableau, in this article we will walk you through on how you can connect to HubSpot API. HubSpot is a popular marketing automation and CRM platform for marketing, sales, and customer service. This will give you an overview of how flexible Autonomous REST Connector is and how it works. Let’s get started.

Download and Install Autonomous REST Connector

Download and install Autonomous REST Connector for ODBC.
Install the connector by running the setup executable file.

HubSpot Authentication

HubSpot offers two means of authentication – OAuth and API Keys. Both mechanisms are supported by Autonomous REST Connector.
For the purpose of the tutorial, we will use the API Keys for Authentication. Here’s an example of the request using the API Key.

curl https://api.hubapi.com/contacts/v1/lists/all/contacts/all?hapikey=456****cfa

Create the ODBC DataSource

Open ODBC Administrator (64-bit) and click on Add to create a new data source.
Choose DataDirect 8.0 Autonomous REST Connector as the driver.
On the Configuration Window, provide a name to the data source and under REST Sample Path paste the endpoint you want to connect to. If we want to connect to Get All Companies endpoint, we just paste the endpoint in the REST Sample Path.
Go to Authentication Tab and set:
1. Authentication Method: UrlParameter
2. Auth Param: hapikey
3. Security Token: <Your-Hubspot-API-Key>
Click on Test Connect to connect to the HubSpot API.

Connect from Tableau

Now connect to HubSpot from Tableau. In Tableau choose Other Databases (ODBC), and choose HubSpot as the DSN you have just created.
Now we see a list of tables made available by Autonomous REST Connector by normalizing the JSON response from HubSpot API. Access the REST API data like always.

Connecting to Multiple Endpoints

To configure multiple endpoints for Autonomous REST Connector, create a new file called Hubspot.rest and configure the endpoints as below and save the file.

{

"Companies":"https://api.hubapi.com/companies/v2/companies/paged",

"CompanyProperties":"https://api.hubapi.com/properties/v1/companies/properties",

"Contacts":"https://api.hubapi.com/contacts/v1/lists/all/contacts/all",

"ContactsLists":"https://api.hubapi.com/contacts/v1/lists",

"ContactProperties":"https://api.hubapi.com/properties/v1/contacts/properties",

"Deals":"https://api.hubapi.com/deals/v1/deal/paged",

"DealProperties":"https://api.hubapi.com/properties/v1/deals/properties",

"DealPipelines":"https://api.hubapi.com/deals/v1/pipelines",

"Engagement":"https://api.hubapi.com/engagements/v1/engagements/paged",

"Forms":"https://api.hubapi.com/forms/v2/forms",

"Owners":"https://api.hubapi.com/owners/v2/owners",

"Products":"https://api.hubapi.com/crm-objects/v1/objects/products/paged",

"Tickets":"https://api.hubapi.com/crm-objects/v1/objects/tickets/paged",

"Emails":"https://api.hubapi.com/marketing-emails/v1/emails"

}
Open the ODBC Administrator, open the DSN configuration you have created and clear the REST Sample Path.
Under REST Config File, browse to the configuration file Hubspot.rest as shown below.
Click on Test Connect and to connect successfully.
Open Tableau and refresh the connection. You will see more tables that normalized from HubSpot API by Autonomous REST connector.
Choose to extract data or use Live Connection to request data from REST API on the fly using Autonomous REST Connector.