Hubspot Exporter

📘

Check out the API docs here for more information.

🚧

Warning

Please be advised that this exporter has the potential to significantly alter your data if not used correctly. Exercise caution and avoid misuse. If you are unsure of how to operate it, there is a risk of causing irreversible changes to your data.

Set up

Accessing the Hubspot APIs

In order to access the Hubspot API's you will first need to create a Private App within your Hubspot account.
To create a private app follow the below steps:

In your HubSpot account, click the settings icon in the main navigation bar.
In the left sidebar menu, navigate to Integrations > Private Apps.
Click Create private app.
On the Basic Info tab, configure the details of your app:
Enter your app's name.
Hover over the placeholder logo and click the upload icon to upload a square image that will serve as the logo for your app.
Enter a description for your app.
Click the Scopes tab.
Select the Read or Write checkbox for each scope you want your private app to be able to access. You can also search for a specific scope using the Find a scope search bar.
Below are the scopes you will definitely need to make requests to the available Kleene extracts:

Business-Intelligence
Integration-sync
Content
Social
crm.objects.marketing_events.read (requires Enterprise level access)
crm.objects.companies.read
crm.objects.deals.read
crm.objects.contacts.read
crm.objects.owners.read
sales-email-read (to read engagement emails)

For the exporter connector you need the additional permissions:

crm.objects.custom.write

crm.objects.custom.read

(and any similar write permissions for other objects you may want to export)

After you're done configuring your app, click Create app in the top right.
Once the Private app has been set-up you will be able to see the API access token upon request. You will use this token to set-up the source in the Kleene app.

Set up Source in the Kleene app

Now you should have everything you need to set up the source in the Kleene app.

Source name
Choose any name you wish

Description
Give the source a meaningful description.

Hubspot API Key
This is the API key associated with the private app created for the Kleene integration.

Features

Reports detail

📘

Behaviour of exporter (and additional table for logging)

Whenever you run reports the connector will store the requests in a table in the same schema with the same name as the destination table + _responses

so if you export little_pond.big_fish, it’ll be little_pond.big_fish_responses

The rows will contain the first level of the request map flattened (like url, method, headers, etc) as columns and the first level of the form-params map as columns, then a response and error columns with the response or error that came from that request.

In subsequent runs, the new rows will be appended to that same table.

It is important to note that our suggestion is for you to just use the _responses table for information purposes and logging. We do not advise you to use the data there as source of truth. If you want to match objects created from an Exporter connector, we still advise you to use an Extract report (for the same object) and match it to exported data using columns and any timestamp for updated time.

The Create Object and Update Objectand report uses the "Create an Object" POST endpoint and the "Update an Object" PATCH endpoint of the API.

For example the Update Object report would send a request per row in the source table.

Given a table with e.g. an 'Institution' objects with a row:

id       |  properties
123      |  {"name": "abc"}

It will send a request with the body:

{"properties": {"name": "abc"}}

To the following url:
PATCH "https://api.hubapi.com/crm/v3/objects/institution/{id}"
where "{id}" is replaced with the value of the "id" column, in this example 123. So:
"https://api.hubapi.com/crm/v3/objects/institution/123"

If you’ve imported an object (e.g. Institutions) using the Hubspot extract connector (and you have the properties in a PROPERTIES column) you can get the properties in separate columns in a new table via SQL:

CREATE TABLE TRANSFORM_TEST.TEST.transformed_institutions_table AS
SELECT 
    ID,  
    PARSE_JSON(PROPERTIES):name::STRING AS name,
    PARSE_JSON(PROPERTIES):institution_foundation_year::STRING AS foundation_year,
    PARSE_JSON(PROPERTIES):country::STRING AS country,
    PARSE_JSON(PROPERTIES):website::STRING AS website
FROM TRANSFORM_TEST.TEST.institution_extract_data

The columns/fields will of course differ depending on your real world Hubspot object. The above is just an example!

Many Keys/Columns (that you may have obtained from a Hubspot Extractor report) may have to be removed from the Object's properties data before the table is usable for the Hubspot Exporter report, as otherwise they will cause an error for the API. These are the read-only properties for an object, that will differ based on your own Hubspot specific objects and setup.

You can edit the data depending on your needs and then you can reshape them back into a table with the following SQL:

CREATE TABLE TRANSFORM_TEST.TEST.restructured_institutions_table AS
SELECT 
    ID,
    OBJECT_CONSTRUCT_KEEP_NULL(
        'name', name,
        'institution_foundation_year', foundation_year,
        'country', country,
        'website', website
    )::VARIANT AS PROPERTIES
FROM TRANSFORM_TEST.TESTTTT.transformed_institutions_table;

You can also edit properties in an already restructured table for a given ID in case you need to:

UPDATE TRANSFORM_TEST.TEST.restructured_institutions_table
SET PROPERTIES = OBJECT_INSERT(
    PROPERTIES,
    'name',
    'Piggledy',
    true
)
WHERE ID = 12345;

No matter what way you prefer to adjust your data, in the end before running an Hubspot Exporter report you need to have:

• A table with a PROPERTIES column that contains the properties data of your Object that you want to export (in the valid format that the API expects). The table must only contain one type of object (the one you want to export)!
• This PROPERTIES column needs to be of VARIANT type.
• The data in PROPERTIES needs to already have been cleaned of any read-only properties.
• If you are using an Update Object report you need to have an ID column with valid/existing id-s of the object.
• If you are using a Create Object report the table must not contain an ID column, but of course must still contain a PROPERTIES column with data in the valid format.

Limitations

🚧

Read only properties

Many objects properties that you get from an extract from the Hubspot Extractor connector are read-only and will not be accepted by the API if sent for a Create/Update operation vie the Hubspot Exporter connector and will cause an error.

The list of such properties will depend vastly on your own setup in Hubspot and also on the Object you are trying to work with.

For the connector to work, please ensure you remove such properties from the table before trying to use exporter.

🚧

Create Object report must not have an ID column

the Create Object report is intended for creating new objects, so it will rely on a table where objects are new and not already existing in Hubspot. As such, once you run a report with data from a table, Hubspot will create these objects and generate an ID itself.

🚧

Object types

The Create and Update object reports rely on the user specifying the object type on the Extract Setup Page. This needs to be a value accepted by the API, which it will recognise.

Please also note that the table you try to use for exporting also needs to only contain data of that specified object! Do not try to mix objects in one exporter report, as that can cause unpredictable behaviour and errors (and potentially changes to data you did not intend to change)!

🚧

Connector will change data within Hubspot

Use of the report on this connector will result in changes to the data within your Hubspot system. We advise having backup of the data, in case you want to revert back.

Our suggestion is also to experiment and explore the behaviour of the connector and the API, by first spending sufficient time testing things with dummy data on a Sandbox environment before making any attempts to go to Production changes.