Is it possible to get access to the underlying SQL database

Developer Support APIs & Integrations
1

Hi all;

We need the ability to perform queries where we filter by any combination of values against any set of properties in an object. For example, all Deals for Customers that closed in Q4 and the Company is in California.

The API is not designed to do this.

Is there a way to get (preferably read-only) access to the underlying SQL database? Or is there an OData API to the underlying data?

I’m aware of CData & Skyvia but both of them are clearly reading from the API which makes them quite slow. Too slow.

If this functionality does not exist, then any downside to our just pulling everything to stuff in a local instance of SQL Server? Of course, that then gives us the lovely problem of keeping the data in sync. New records are easy but deleted or updated records - that could be problematic.

??? - thanks - dave

2

Hello @DavidThielen

This is a toughy. To the best of my knowledge there is not an API that is going to give you this level of detail.

I am pretty sure you would have to pull all of the contacts down with their associated deal making sure to also add any additional properties such as state to your request and do this server side.

Once this is done, you could look into webhooks to subscribe to changes to specific properties.

But, I am not the end all be all on this :grinning_face:

Maybe @Oscar1 , @jpsanchez or @DanielSanchez can prove me wrong (it wouldn’t be the first time)

3

Hi David,

Really good Q ;)… good one.

Ok my idea :

First in order to to the Query you can create via API POST/CONTACTS/v1/lists a static or dynamic list with the values you request.

Second, you should have to request list memberships in order to get the results.

reference: https://legacydocs.hubspot.com/docs/methods/lists/create_list

This API call is going to be update in the near future by the new ones.

Example of this.

Here is an example of some more complex filters. This list 
will include all contacts named Alice who have filled out 
a certain form on a certain landing page after April 12, 2013 
as well as all contacts with a lifecycle stage of lead, 
salesqualifiedlead, or marktingqualifiedlead who have 
filled out more than three forms.

{
 "name": "My Wacky List",
 "dynamic": true,
 "portalId": 62515,
 "filters": 
 [
 [
 {
 "operator": "EQ",
 "value": "Alice",
 "property": "firstname",
 "type": "string"
 },
 {
 "operator": "HAS_FILLED_OUT_FORM",
 "form": "92364e25-6aa7-46f0-bfb0-aacbb537513d",
 "page": "19612330",
 "afterTimestamp": "1365739200000"
 }
 ],
 [
 {
 "operator": "SET_ANY",
 "value": "lead;salesqualifiedlead;marketingqualifiedlead",
 "property": "lifecyclestage",
 "type": "string"
 },
 {
 "operator": "GT",
 "computedProperty": "NUMBER_OF_FORMS_FILLED_OUT",
 "value": "3",
 "type": "number"
 }
 ]
 ]
}

If the request succeeds, you'll get an HTTP 200 response with 
a JSON object, which represents the new list that you've just 
created in the system. At first, you'll notice that the 
'metaData -> processing' field has a value of 'INITIALIZING', 
which means that a dynamic list is processing and building itself. 
This can take a bit of time, depending on the size of your list. 
Once the list has finished processing, that field will be set 
to a 'DONE' value and the 'metaData -> size' value will grow 
with the amount of contacts that are in the list. 
Here's the JSON response from the request made with JSON above:

{
 "name": "tweeters",
 "internalListId": 6,
 "listId": 6,
 "deleted": false,
 "dynamic": true,
 "portalId": 56043,
 "filters": [
 [
 {
 "operator": "EQ",
 "property": "twitterhandle",
 "type": "string",
 "value": "@hubspot"
 }
 ]
 ],
 "updatedAt": 1335387180052,
 "createdAt": 1335387180052,
 "metaData": {
 "lastProcessingStateChangeAt": 1335387180052,
 "processing": "INITIALIZING",
 "lastSizeChangeAt": 0,
 "error": "",
 "size": 0
 }
}

Please note this is a walk-arround as @DavidThielen mention.

Let me know if this helps!,. if yes please say it!

KR

JP

:wink:

4

@DavidThielen @dennisedson We offer an app in the HubSpot App Marketplace called MS SQL Server for HubSpot . The app pulls your HubSpot data into a SQL data warehouse via prebuilt API connectors. A SQL data warehouse in Azure is included in the cost.
Once your HubSpot data is in the data warehouse, it automatically refreshes on a scheduled basis. You can connect the SQL data warehouse to any other application that has a SQL connector.

You could also use our app that connects HubSpot to Power BI. Microsoft Power BI is a good solution for what you want to accomplish. Power BI Free is included with Office365 and can be licensed separately. The HubSpot Connector for Power BI is available in the HubSpot App Marketplace. By using the connector, you will be able to create an automated data refresh schedule so that each time you open Power BI, the most recent HubSpot data is used to update your report. There are a free and a $9.99 per month version of Power BI.

5

Hi;

That’s a useful solution for some but we need access to the live data.

thanks - dave

6

@DavidThielen It’s the same thing as the solution you mentioned of “pulling stuff” to a local SQL server database -- except in the cloud. HubSpot only makes data available via API. Our low cost pre-built solution allows for updates from 1x to 24x a day. There is a faster option but it is more costly. This amount of data we are talking about can be huge.

7

You can do this nowadays with a product like ours, Stacksync, which allows you to sync your CRM data to a database or data warehouse but also allows you to set up triggers, webhooks, or even automated workflows as needed. While we do get the data from HubSpot via the API as well, as there’s no other way, currently, you have more flexibility in what you can do with that data, and the updates are quite fast as they can be triggered by data updates in HubSpot and sent immediately to your database.