The configuration of a HubSpot database for HubSpot data exchange with SQL, Power BI Desktop, PowerBI.com, Power Query, Azure Data Factory, Qlik and Tableau is the central theme of this topic. Power BI c.s. are typically used for reporting purposes, and Invantive SQL is typically used to load data into a data warehouse and/or to integrate data from HubSpot with other (cloud) applications.
After defining the database, follow the steps on Invantive Cloud to connect HubSpot to Power BI and the other mentioned platforms.
For data warehousing such as on SQL Server, follow for instance the steps on:
- with Azure Data Factory: https://cloud.invantive.com/en/hubspot/azure-data-factory-connector/setup
- to an on-premises database, see the example on Copy Exact Online to SQL Server or Import Teamleader data in an on-premise SQL Server database through Invantive Cloud
- to a cloud database using Invantive as ETL tool: see Elementary Data Replication Module between Exact Online and Azure SQL Server
For HubSpot integration with other platforms, please find tips on the powerful synchronize and union distinct SQL extensions to exchange or load data with ease:
- Sieve your Data using the Union Distinct Set Operator
- Synchronize your Data with one Statement across multiple Applications
- Custom logic triggers on data synchronize statement for easier data integration
- Easily determine changed values in custom data-integration triggers with synchronize
- Configurable maximum number of transactions on data synchronization using SQL synchronize statement
API Integration Types
HubSpot allows various types of API integrations to exchange HubSpot data in real-time, including:
- private apps with a shared and token (fixed value, unless rotated),
- public apps using OAuth with a client ID, client secret, refresh token and rotating access token
In general, it is recommended to use a private app to create a HubSpot database. Use of a public app is only recommended for use by Invantive OEM-partners with a HubSpot specialization.
To connect Power BI to HubSpot or use other BI tools, both approaches can be used and provide the same HubSpot data. Custom fields and their values such as company properties and contact properties are identically available through both approaches. For enterprise integration it can be helpful to create a public app due to the availability of webhooks for more real-time process flows.
HubSpot database using a private app with token authentication
A private app is for use by a regular HubSpot account (see HubSpot Account Types).
With a private app, the available tables with HubSpot data depend on the HubSpot plan chosen. These features are further restricted by the scopes defined on the private app. Scopes are centrally defined from within the HubSpot private app pages.
Execute the following steps to create a HubSpot-database using a private app for use with SQL or Power BI.
- Log on to Invantive Cloud.
- Go to “Databases”.
- Choose the “New” button.
- Choose “HubSpot”:
- The following fields appear:
- Choose “Use token from private app”.
- Go to https://hubspot.com/.
- Log in or - when already logged in - choose “Go To My Account”.
- Choose the gear icon:
- Go to the menu “Integrations” and choose “Private Apps”:
- Choose the button to create a private app (reference documentation on Public apps).
- Specify the name and description:
- Choose the scopes which represent the data elements that can be accessed:
- Choose “Create app”.
- Choose “Continue creating” after reading the warning.
- Choose “Skip for now”
- Copy XXX
- Go back to Invantive Cloud and enter the token:
- Choose “Finish”.
HubSpot database using a public app and OAuth authentication
A public app is for use by a App developer HubSpot account (see HubSpot Account Types).
With a public app, the available tables depend on the HubSpot plan chosen. These features are further restricted by the scopes defined on the public app by the app vendor on developers.hubspot.com. Furthermore, the features can be restricted further by specifying more restrictive scopes during the execution of the so-called “OAuth authentication flow” which requests the user to authorize the app by providing his credentials.
Execute the following steps to create a HubSpot-database using a public app with SQL or Power BI:
- Log on to Invantive Cloud.
- Go to “Databases”.
- Choose the “New” button.
- Choose “HubSpot”:
- The following fields appear:
- Choose “Use OAuth with public app”:
- Go to https://developers.hubspot.com/.
- Log in using a developer account or choose “Create a Developer Account”.
- Once logged in, choose the Apps menu:
- Choose “Create app”:
- Specify name and description:
- Specify the redirect URL
https://cloud.invantive.com/app/setup/databases/new/hubspot/tokenin the Auth tab:
- Choose in the Auth tab the scopes which represent the data elements that can be accessed. As an absolute minimum always specify “oauth”:
- Select the text below “Selected scopes” and copy it to a text editor.
- Choose “Create app”.
- Copy the Client ID and Client secret values to a text editor:
- Copy client ID, client secret and all scopes (linefeeds are automatically changed to spaces) into appropiate fields.
- Choose “Finish”.
- Approve connection.
Frequent Problems
Public app: “This account doesn’t have access to some HubSpot features that are required by this app.”
The following error can occur when authenticating on HubSpot from the “New Database” process steps using a public app:
Couldn’t complete the connection
This account doesn’t have access to some HubSpot features that are required by this app.
Please contact the app developer.
Possible causes are:
- The scopes listed on the HubSpot public app on https://developers.hubspot.com don’t match the scopes listed in the “Choose App” window in Invantive Cloud.
- A scope was specified in the “Choose App” window which is not available with free or test accounts due to licensing reasons, such as “accounting”.
- A test account is being used and has expired. Please make sure that the HubSpot user used to log on still has access to HubSpot itself by opening for instance the list of contacts or companies.