Online Data Provider: HubSpot

Ole Dyring

May 29, 2024 06:32

[Version: 2023 - August]
[Build: 23.08.31502]

HubSpot provides an API for data exchange.

TARGIT has developed an Online Data Provider that can access HubSpot API data in TARGIT InMemory and in TARGIT Data Discovery.

License

Your TARGIT license needs to support the HubSpot Online Data Provider. Contact your TARGIT Account Manager if it doesn't.

HubSpot API Token

First, you need to acquire a Token from the Administrative interface of your HubSpot environment.

Follow HubSpot's own guidelines on how to achieve the API token: HubSpot Private apps

Creating the HubSpot data source, InMemory

(Look towards the end of this article for instructions on setting up the HubSpot Online Data Provider for TARGIT Data Discovery.)

To add HubSpot as a new data source in TARGIT InMemory:

Then insert the API token in the required field:

Query HubSpot data in TARGIT InMemory ETL

Only SQL queries are used to retrieve data. The snippet will try to load all the data using pagination.

If the query parameter "limit" is not specified, the snippet will load 10 elements per query (according to HubSpot API documentation).

For some requests the limit can be set to a maximum of 100, for some - to 50.

If the set limit is higher than the allowed limit, you will see an error like this:

For example, to get all contacts, you need set custom query for data source to:

select * from [/crm/v3/objects/contacts] (OPTIONS:depth=10)

All supported endpoints can be found in the HubSpot API Documentation: https://developers.hubspot.com/docs/api/overview

Limitations

'Where' clauses only work to replace custom values in a given SQL. You can not use WHERE to filter result. See examples section.

Errors

If your request returns no data, an error will be thrown since we are not obtaining any data/metadata:

Query custom objects

Retrieve existing custom objects

To retrieve all custom objects, make a GET request to /crm/v3/schemas.

To retrieve a specific custom object, make a GET request to one of the following endpoints:

/crm/v3/schemas/{objectTypeId}
/crm/v3/schemas/p_{object_name}
/crm/v3/schemas/{fullyQualifiedName}. You can find an object's
fullyQualifiedName in its schema, which is derived from p{portal_id}_{object_name}. You can find your account's portal ID using the account information API.

For example, for an account with an ID of 1234 and an object named lender, your request URL could look like any of the following:

SELECT * FROM [/crm/v3/schemas/2-3465404](OPTIONS:depth=10)
SELECT * FROM [/crm/v3/schemas/p_lender](OPTIONS:depth=10)
SELECT * FROM [/crm/v3/schemas/p1234_lender](OPTIONS:depth=10)

Retrieve custom objects data

To retrieve a custom object data, make a GET request to one of the following endpoints:

/crm/v3/objects/{objectTypeId}
/crm/v3/objects/p_{object_name}
/crm/v3/objects/{fullyQualifiedName}. You can find an object's
fullyQualifiedName in its schema, which is derived from p{portal_id}_{object_name}. You can find your account's portal ID using the account information API.

For example, for an account with an ID of 1234 and an object named lender, your request URL could look like any of the following:

SELECT * FROM [/crm/v3/objects/2-3465404](OPTIONS:depth=10)
SELECT * FROM [/crm/v3/objects/p_lender](OPTIONS:depth=10)
SELECT * FROM [/crm/v3/objects/p1234_lender](OPTIONS:depth=10)

Retrieve associations for objects to specific object type

To retrieve associations between two objects you need to make a POST request to follow endpoint:

/crm/v4/associations/{fromObjectType}/{toObjectType}/batch/read

and specify the identifiers of those {fromObjectType} for which you need to get associations to {toObjectType} in the request body

For example, to obtain the company's association to the deals:

SELECT * FROM [/crm/v4/associations/companies/deals/batch/read] WHERE [HTTP_REQUEST_METHOD]="POST" AND [HTTP_REQUEST_BODY]='{
  "inputs": [
    {
      "id": "2885617098"
    }}
  ]
}}' (OPTIONS:depth=10)

If you need to get associations for two companies, set the multiple IDs in the request body:

SELECT * FROM [/crm/v4/associations/companies/deals/batch/read] WHERE [HTTP_REQUEST_METHOD]="POST" AND [HTTP_REQUEST_BODY]='{
  "inputs": [
    {
      "id": "2885617098"
    }},
    {
        "id": "2885617099"
    }}
  ]
}}' (OPTIONS:depth=10)

Examples

Get all contacts https://developers.hubspot.com/docs/api/crm/contacts	SELECT * FROM [/crm/v3/objects/contacts] (OPTIONS:depth=10)
Get all contacts using where clauses	SELECT * FROM [/crm/v3/objects/{object}] WHERE [object] = 'contacts' (OPTIONS:depth=10)
Get all contacts using the 5 contact/request. NOTE: Snippet anyway returns all data	SELECT * FROM [/crm/v3/objects/contacts?limit=5] (OPTIONS:depth=10) or SELECT * FROM [/crm/v3/objects/{object}?limit={limit}] WHERE [object] = 'contacts' AND [limit] = '5' (OPTIONS:depth=10) or SELECT * FROM [{endpoint}] WHERE [endpoint] = '/crm/v3/objects/contacts?limit=5' (OPTIONS:depth=10)
Get all contacts starting from 5th contact. (skip 4 contacts at start).	SELECT * FROM [/crm/v3/objects/contacts?after=5] (OPTIONS:depth=10)
Get only needed properties for contacts	SELECT * FROM [/crm/v3/objects/contacts?properties=email,firstname] (OPTIONS:depth=10) or SELECT * FROM [/crm/v3/objects/contacts?properties={props}] WHERE [props] = 'email,firstname' (OPTIONS:depth=10)
Return all stages of a pipeline https://developers.hubspot.com/docs/api/crm/pipelines	SELECT * FROM [/crm/v3/pipelines/{objectType}/{pipelineId}/stages] WHERE [objectType] = 'contacts' AND [pipelineId] = 'contacts-lifecycle-pipeline' (OPTIONS:depth=10)
Search contacts, using the POST request https://developers.hubspot.com/docs/api/crm/search	SELECT * FROM [/crm/v3/objects/{object}] WHERE [object] = 'contacts/search' AND [HTTP_REQUEST_METHOD]="POST" AND [HTTP_REQUEST_BODY]='{ "filterGroups": [ { "filters": [ { "values": ["emailmaria@hubspot.com"], "propertyName": "email", "operator": "IN" } ] } ], "limit": 10, "after": 0 }' (OPTIONS:depth=10)

WARNING! Known issue

Due to an error in tiImport: for ETL Studio and Data Discovery, with Data Source generation OOP enabled - you must duplicate all closing curly brackets!

If you don't, you will get an error like this:

So, until this bug has been fixed, your closing curly brackets should look like this:

And like this:

Creating the HubSpot data source, Data Discovery

To add HubSpot as a new data source in TARGIT Data Discovery:

When creating a new HubSpot connection, just insert the API token in the required field:

To query HubSpot data in Data Discovery, you will need to create a data source for each query. Open the 'Advanced Settings' and go to the 'Query' tab as in this example: