Connect to HubSpot Using JDBC

Connecting to HubSpot using JDBC is only supported on MicroStrategy ONE Update 11 or earlier. If you are using MicroStrategy ONE Update 12 or later, see Connect to HubSpot Using ODBC.

HubSpot is an inbound marketing and sales platform that helps companies attract visitors, convert leads, and close customers. MicroStrategy ships the HubSpot JDBC connector for customer to connect HubSpot as a data source when doing data import on Web and Workstation.

The HubSpot JDBC driver supports standard SQL-92 syntax and functions but limited in write back and performance. MicroStrategy will support in-memory data import and choose to process data on our side.

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

JDBC Connection URL

MicroStrategy ships the HubSpot JDBC driver to connect HubSpot as a data source using the Access Token method. You can find it on the data source dialog when importing data.

Using Access Token method
jdbc:hubspot://[Host];Auth_Type=OAuth 2.0;Auth_AccessToken=[Access Token]
Using Refresh Token method
jdbc:HubSpot://[Host];Auth_Type=OAuth 2.0;Auth_Client_ID=[Client ID];Auth_Client_Secret=[Client Secret];Auth_RefreshToken =[Refresh Token]

HubSpot has an API limit of 40,000 requests per day with a rate limit of 10 requests per second.

JDBC Schema Tables

HubSpot stores data in structures which do not follow the rules of data typing and structure that apply to traditional relational tables and columns. The JDBC drivers use schema tables to map the HubSpot data. The mapping method is complicated, so lower performance is expected when querying tables with large amounts of data.

Configure HubSpot using JDBC on Web and Workstation

Workstation
Web

See Create and Edit Data Sources for steps to create a new data source and connection.

Choose Add Data > New Data.
Find the HubSpot connector from the data source list.
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 a DSN-less connection:
- Access Token: Enter the required Host Name, Access Token, User, Password, and Data Source Name fields.
- Refresh Token: Enter the required Host Name, Client ID, Client Secret, Refresh Token, User, Password, and Data Source Name.
- Edit the Connection String: Click Show connection string and select Edit connection string. Enter the connection information of the JDBC connection string to connect.
  Ignore Driver. Type any character in the User and Password fields, as they will not be used in the connection.
Click OK.
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 JDBC 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.
Click Prepare Data.
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.

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.

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"
}