Bulk sync (ELT) from HubSpot to PostgreSQL

Before getting started make sure to set your Polytomic API key as an environment variable:

$export POLYTOMIC_API_KEY=YOUR-API-TOKEN

This example tutorial will cover three steps:

  1. Connecting to HubSpot.
  2. Connecting to PostgreSQL.
  3. Loading data from HubSpot to PostgreSQL.

1. Create a HubSpot connection

The following request will create a Hubspot connection. The configuration for each type of connection can be found at the connection configuration docs page.

$curl --request POST  \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json"  \
>     --header "content-type: application/json"  \
>     --header "X-Polytomic-Version: 2022-12-12"  \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}"  \
>     -d '{"name": "Hubspot Connection","type": "hubspot", "configuration": {}}

Since Hubspot connections uses OAuth to authenticate, we’ll need to follow the link returned in the auth_url parameter in the response

Redirection


By default, the API expects the auth_url to be open in a new browser window. If you’d like to modify the redirect behavior, there is an optional redirect_url parameter that can be added to the request body.

2. Create a PostgreSQL connection

The PostgreSQL server must be network-accessible from Polytomic.

$     curl --request POST \
>     --url https://app.polytomic.com/api/connections \
>     --header "accept: application/json" \
>     --header "content-type: application/json" \
>     --header "X-Polytomic-Version: 2022-12-12" \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>     "name": "Postgres Connection",
>     "type": "postgresql",
>     "configuration": {
>        "hostname": "localhost",
>        "port": 5432,
>        "database": "postgres",
>        "username": "user",
>        "password": "secret"
>        }
>    }'

Bulk sync schemas


Polytomic may take a few moments to cache source schemas after creating new connections.

3. Create a bulk sync

This will create a bulk sync of all Hubspot objects into Postgres:

$curl --request POST \
>     --url https://app.polytomic.com/api/bulk/syncs \
>     --header "accept: application/json" \
>     --header "content-type: application/json" \
>     --header "X-Polytomic-Version: 2022-12-12" \
>     --header "Authorization: Bearer ${POLYTOMIC_API_KEY}" \
>     -d '{
>          "name": "Hubspot Bulk Sync",
>          "source_connection_id": "YOUR-HUBSPOT-CONNECTION-ID",
>          "destination_connection_id": "YOUR-POSTGRES-CONNECTION-ID",
>          "mode": "replicate",
>          "schedule": {
>              "frequency": "manual"
>          },
>          "dest_configuration": {
>              "schema": "hubspot"
>          }
>    	}'