HubSpot to Postgres

This page provides you with instructions on how to extract data from HubSpot and load it into PostgreSQL. (If this manual process sounds onerous, check out Stitch, which can do all the heavy lifting for you in just a few clicks.)

What is HubSpot?

HubSpot provides an inbound marketing and sales platform that helps businesses feature content, perform search engine optimization, interact on social media, and see analytics about their content.

What is PostgreSQL?

PostgreSQL, or Postgres, is a popular object-relational database management system (ORDBMS) that calls itself "the world's most advanced open source database." PostgreSQL, which is licensed as open source software, offers enterprise-grade features, a strong emphasis on extensibility, and standards compliance.

The database runs on all major operating systems, including Linux, Unix, and Windows. It's ACID-compliant and supports foreign keys, joins, views, triggers, and stored procedures in multiple languages. PostgreSQL is frequently used as a back-end database for web systems and software tools, and is available in cloud-based deployments by most major cloud vendors. The PostgreSQL syntax forms the basis for querying Amazon Redshift, which makes migration between the two systems relatively painless and makes Postgres a good platform for developers who may later work on Redshift's data warehouse platform.

Getting data out of HubSpot

HubSpot provides a REST API that lets you get dozens of different kinds of data from the platform. For instance, to get a list of blog topics, you could call GET /blogs/v3/topics. All of Hubspot's API calls require a parameter that specifies an OAuth Access Token or API Key, and some provide optional parameters that let you tailor the information that HubSpot returns.

Sample HubSpot data

HubSpot's API returns JSON-format data. For example, the output of the topics query might look like this:

  "objects": [
      "id": 349001328,
      "portalId": 62515,
      "name": "api-docs",
      "slug": "api-docs",
      "description": "",
      "created": 1381896200000,
      "updated": 1381896200000,
      "deletedAt": 0
      "id": 447332568,
      "portalId": 62515,
      "name": "Test Topic 20170110160733",
      "slug": "test-topic-20170110160733",
      "description": "",
      "created": 1389388053000,
      "updated": 1389388053000,
      "deletedAt": 0
      "id": 450113517,
      "portalId": 62515,
      "name": "Test",
      "slug": "test",
      "description": "",
      "created": 1389387669000,
      "updated": 1389387669000,
      "deletedAt": 0
  "message": null,
  "total": 3,
  "limit": 1000,
  "offset": 0

Preparing HubSpot data



Loading data into Postgres





Keeping HubSpot data up to date



And remember, as with any code, once you write it, you have to maintain it. If HubSpot modifies its API, or the API sends a field with a datatype your code doesn't recognize, you may have to modify the script. If your users want slightly different information, you definitely will have to.

Other data warehouse options


Easier and faster alternatives


Thankfully, products like Stitch were built to solve this problem automatically. With just a few clicks, Stitch starts extracting your HubSpot data via the API, structuring it in a way that is optimized for analysis, and inserting that data into your PostgreSQL data warehouse.