Connect to HubSpot Using ODBC

Starting in MicroStrategy ONE Update 12, a HubSpot ODBC driver is used to connect to HubSpot to import data on Web and Workstation, rather than a JDBC driver.

If you are using MicroStrategy ONE Update 11 or earlier, see Connect to HubSpot Using JDBC.

HubSpot is an inbound marketing and sales platform that helps companies attract visitors, convert leads, and close customers.

HubSpot is a SaaS data source so it does not provide many query processing capabilities except for some filtering. The HubSpot ODBC driver supports standard SQL-92 syntax and functions but is limited in write back and performance. MicroStrategy will support in-memory data import and choose to process data on our side.

Data Retrieve Mode: ODBC Call Only
Data Import supported: Select Tables, Free Form SQL, Query Builder (In-memory)

ODBC Connection URL

MicroStrategy ships the HubSpot ODBC driver to connect HubSpot as a data source with the following connection methods. You can find them on the data source dialog when importing data.

Using access token
DRIVER={MicroStrategy HubSpot ODBC Driver};Host=[Host];Auth_Type=OAuth 2.0; Auth_AccessToken=[Access Token];
Using refresh token
DRIVER={MicroStrategy HubSpot ODBC Driver}; Host=[Host]; Auth_Type=OAuth 2.0; Auth_Client_ID=[Client ID]; Auth_Client_Secret=[Client Secret]; Auth_RefreshToken =[Refresh Token];

See How to Get Credentials for HubSpot for steps to acquire the correct parameters.

ODBC Schema Tables

HubSpot stores data in structures which do not follow the rules of traditional tables and columns. The ODBC drivers map HubSpot data into its designed relational schema 'HubSpot' with fixed schema tables. The mapping method is complicated, so lower performance is expected when querying tables with large amounts of data.

Configure HubSpot using ODBC on Web and Workstation

Workstation
Web

Add an environment connection and log in to Workstation.
Click Data Source under Administration.
Find and choose HubSpot.
Expand Default Database Connector and choose Add New Database Connection.
Enter the required information.

In the Authentication Mode drop-down, choose Access Token or Refresh Token:
- Access Token: Enter the required fields Name and Access Token.
- Refresh Token: Enter the required fields Name, Client ID, Client Secret, and Refresh Token.
Click Save.
In the Add Data Source dialog, choose a Project to assign and access the data source.

Click Save.

In-Memory Data Import

After creating and connecting to a HubSpot data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

In the left Navigation pane, click next to Datasets.
Choose a Project and select Data Import Cube.

Click OK.
Click on the data source to connect to HubSpot.
If needed, click Prepare Data to wrangle your data or rename columns.
Click Finish.
Select Import as an In-memory Dataset.

Click Create > Add External Data.

Find and choose HubSpot.
Choose Select Tables, Type a Query, or Build a Query to import data.
It is recommended to use Select Tables to reduce joins on the driver side for best performance.
In the Data Source interface, select DSN-less Data Source and choose HubSpot for both Database and Version. Use one of the following methods to configure the DSN-less connection:
- Access Token: Enter the required fields Host Name, Access Token, and Data Source Name.
- Refresh Token: Enter the required fields Host Name, Client ID, Client Secret, Refresh Token, and Data Source Name.
- Edit the Connection String: Click Advanced Settings and select Edit connection string. Enter the connection information of the ODBC connection string to connect.
Click Save.
Click on the new Data Source to connect.

In-Memory Data Import

After creating and connecting to a HubSpot data source, you can retrieve data through ODBC and then publish a cube for dashboards, reports, and documents.

The examples below are using the Select Tables import option.

Click your HubSpot data source and select the tables you want to import.
If needed, click Prepare Data to wrangle your data or rename columns.
Click Finish.
Select Import as an In-memory Dataset Mode to publish a cube.

How to Get Credentials for HubSpot

Create an App in HubSpot

Sign up for a developer account here.
Click Create app.
You will see the Client ID and Client Secret displayed once you finish creating your app.

Generate Authorization Code

To generate the authorization code, users are redirected to the following URL:

https://app.hubspot.com/oauth/authorize?client_id={{Auth_Client_ID}}&scope={{Auth_Scope}}&redirect_uri={{Auth_BaseRedirectUri}}

Where:

Auth_Client_ID is the Client ID of your HubSpot account.

Auth_Scope must include all fields below:

contacts
content
reports
social
automation
forms
files

This redirects the user to the HubSpot login page.

After logging in, select the sandbox you want to use.

The sandbox requests integration permissions and then redirects to the Auth_BaseRedirectUri. The authorization code is returned in the format below:

https://www.hubspot.com/?code=<Authorization_Code>

Get Refresh Token and Access Token

Use the authorization code to get an access token and refresh token.

The access token is used to authenticate requests that your app makes. Access tokens expire after 6 hours, so you can use the refresh token to get a new access token when the first access token expires.

Copy

POST URL:
https://api.hubapi.com/oauth/v1/token
Headers:
Content-Type: application/x-www-form-urlencoded;charset=utf-8
Data:
grant_type=authorization_code&client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx&client_secret=yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy&redirect_uri=https://www.example.com/&code=zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz
If successful, you will receive a JSON response with the tokens:
{
"access_token": "xxxx",
"refresh_token": "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy",
"expires_in": 21600
}
If there are any problems with the request, you will receive a 400 response with an error message.
{
"error": "error_code",
"error_description": "A human readable error message"
}

Renewal of Access Token

Use a previously obtained refresh token to generate a new access token.

Access tokens expire after 6 hours. If you need offline access to data in HubSpot, you will need to store the refresh token you get when initiating your OAuth integration. Use the refresh token to generate a new access token once the initial access token expires.

Copy

POST URL:
https://api.hubapi.com/oauth/v1/token
Headers:
Content-Type: application/x-www-form-urlencoded;charset=utf-8
Data:
grant_type=refresh_token&client_id=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx&client_secret=yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy&redirect_uri=http://www.hubspot.com/&refresh_token=zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz
If successful, you will receive a JSON response with a new access_token:
{
"access_token": "xxxx",
"refresh_token": "zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz",
"expires_in": 21600
}
If there are any problems with the request, you will receive a 400 response with an error message.
{
"error": "error_code",
"error_description": "A human readable error message"
}