Export HubSpot data to Amazon Redshift

Summary

Export HubSpot CRM objects, associations, and events to Amazon Redshift using the Amazon Redshift app in the HubSpot Marketplace. You can choose the data to export, configure a sync schedule, and monitor sync activity from HubSpot.

This article covers how to install and connect the app, configure a data export sync, and monitor or manage the sync.

Please note: if you're a Super Admin, you can opt your account into the Improved Amazon Redshift Integration beta.

Before you get started

Understand requirements

Make sure you have the following:

• An AWS Identity and Access Management (IAM) role connected to your HubSpot account and configured to access your AWS account.
• An Amazon Redshift provisioned cluster or serverless workgroup that HubSpot can reach.
• An Amazon S3 bucket for staging temporary Parquet files. The bucket must be in the same AWS Region as your Redshift cluster or serverless workgroup. 
• HubSpot copies temporary Parquet files to the configured Amazon S3 staging bucket and doesn’t delete them. To remove older files and manage storage costs, create an S3 lifecycle rule in your AWS account.
• A Redshift administrator who can run a one-time SQL grant on the destination database.
• Access to the HubSpot and AWS consoles. It’s recommended to open them in separate tabs.

Understand limitations and considerations

• Each sync produces a full snapshot of the selected data.
• Each sync replaces the destination table. Any table-level grants or settings added in Redshift aren’t preserved between syncs.
• You control the amount of data exported by selecting which objects, associations, and events to include.
• If a sync fails, the next scheduled sync automatically attempts the full sync again.

Install and connect the Amazon Redshift app

1. In the HubSpot App Marketplace, open the Amazon Redshift app listing.
2. Select the AWS role connected to your HubSpot account.
3. Click Install.
4. Click Next.
5. Create an AWS IAM role that’s connected to your HubSpot account and configured to access your AWS account.
6. Enter the Amazon Resource Name (ARN) of the role.
7. Click Connect to Amazon Redshift.

Configure your AWS role

HubSpot uses the connected AWS role to write temporary Parquet files to your S3 staging bucket and load the files into Redshift.

1. In the S3 bucket URL field, enter the URL of the S3 bucket that HubSpot will use to stage temporary Parquet files.

You can include a prefix in the URL to limit access to a specific path within the bucket. The bucket must be in the same AWS Region as your Redshift cluster or serverless workgroup.

Please note: HubSpot copies Parquet files to the configured S3 staging bucket by design and doesn’t delete them. Create an S3 lifecycle rule in AWS to automatically remove older files.

2. Select your Redshift deployment type from the dropdown menu:
   • Redshift Serverless
   • Provisioned cluster
3. Update the connected AWS role with the permissions policy for your deployment type:
   • Redshift Serverless: enter your workgroup ARN. HubSpot will generate a policy containing the ARN. Copy the policy and attach it to the connected AWS role.
   • Provisioned cluster: copy the policy template. Replace YOUR_REGION, YOUR_ACCOUNT_ID, YOUR_CLUSTER_ID, and YOUR_DATABASE with values from your AWS account, then attach the policy to the connected AWS role.
4. In AWS IAM, open the role connected to HubSpot.
5. Attach the generated permissions policy to the role, or create an inline policy with the policy content.

6. In the same AWS IAM role, update the trust policy with the statement generated by HubSpot.

HubSpot generates a different trust policy statement based on the Redshift deployment type you selected.

Associate the AWS role with Redshift

In Amazon Redshift, associate the same AWS role that you updated in the previous section with your Redshift setup.

• Redshift Serverless: open the namespace configuration page for the namespace that you use with HubSpot. On the Security and encryption tab, click Manage IAM roles, then select Associate IAM roles.
• Provisioned cluster: open your cluster, click Actions, and select Manage IAM roles > Associate IAM roles.

Grant Redshift permissions

1. In HubSpot, select the destination database.
2. Ask a Redshift administrator to run the SQL script in the selected database.

HubSpot uses this permission to create a schema for the destination during the sync.

3. Click Test connection.
4. If the connection test is successful, click Next.

Configure the data export sync

1. Select the data sources to export.
2. Select the objects, associations, and events tables to include.
3. Configure the sync frequency and start time.
4. Click Start sync.

Each sync run exports a full snapshot of the selected data to Amazon Redshift.

Monitor and manage the sync

From the sync details page, you can review the following information:

• Sync status.
• Last sync time.
• Errors.
• Sync activity.
• Sync history.

To open the sync details page:

1. In your HubSpot account, navigate to Data Management > Data Integration.
2. On the App Syncs tab, search for Amazon Redshift app syncs.
3. Click the Amazon Redshift sync you want to review.

From the sync details page, you can also perform the following actions:

• Click Pause to pause the sync.
• Click Archive to archive the sync.
• Click Sync now to manually start a sync.

Sync statuses

Resolve sync failures

When a sync fails:

• The sync displays a Failed status in HubSpot.
• Per-table details show which tables succeeded or failed.
• The next scheduled sync automatically attempts the full sync again.
• You can click Sync now to manually start another sync.