This page provides a guide to Establishing a Connection to HubSpot in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to HubSpot

Establishing a Connection shows how to authenticate to HubSpot and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from HubSpot through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to HubSpot by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Authenticating to HubSpot

There are two authentication methods available for connecting to HubSpot data source: PrivateAppToken-based and OAuth Authentication.

Some tables in the HubspotV3 schema may not work with the OAuth mechanism. This API limitation restricts their usage only to the PrivateAppToken-based authentication. Specifically, each table in the data-model section will mention the supported authentication mechanism.

OAuth

Accessing Custom Fields

If you have defined a large number of custom fields, SELECT * queries can fail with an error as HubSpot limits how many fields can be requested. To avoid the error, you can specify the custom fields you want to return in the text files specified by ContactPropertiesFile and DealPropertiesFile. Alternatively, specify the primary key in the WHERE clause.

If the detected column sizes for custom fields are not sufficient, you can specify the sizes in ColumnSizes.

Fine Tuning Data Access

You can use the following properties to gain more control over column names:

• UseDisplayNames
• UseSimpleNames

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models HubSpot entities as relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Using HubSpot API

See HubSpot Data Model for the available entities in the HubSpot API.

Using HubSpot V3 API

See HubSpot V3 Data Model for the available entities in the HubSpot V3 API.

Using HubSpot V4 API

See HubSpot V4 Data Model for the available entities in the HubSpot V4 API.

The CData Cloud models HubSpot entities as relational Tables, Views, and Stored Procedures. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

The Cloud models the data in HubSpot as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - HubSpot Tables

Companies may be kept track of in HubSpot just like contacts. They can be added to, updated, and removed from this table.

Table Specific Information

Companies in HubSpot represent different organizations you are interested in keeping track of for potential marketing opportunities. They are treated similarly like contacts and have a number of custom properties that will be dynamically determined and added to the Companies table from your HubSpot Hub.

SELECT

When selecting companies, they can only be filtered by the CompanyId and only one Company at a time. Otherwise they can be selected without a filter, which will cause all companies in your HubSpot account to be listed. For example:

SELECT * FROM Companies WHERE CompanyId = '123456789'

Columns

Company properties are the standard and custom pieces of field data that appear in HubSpot.

Table Specific Information

Company properties are a number of custom properties that are available to store data about the companies in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Companies on a new connection.

SELECT

Company properties can only be filtered by the unique company property name. For example:

SELECT * FROM CompanyProperties WHERE Name = 'property_name'

Columns

Company property groups in HubSpot offer a way of organizing individual types of properties for companies. Each company property must belong to a property group.

Table Specific Information

Company property groups offer a means of organizing the various custom properties that are available for defining properties about a given company. Company property groups may be selected, inserted, updated, or deleted from this table.

SELECT

Company property groups may only be filtered by the unique company property group name. For example:

SELECT * FROM CompanyPropertyGroups WHERE Name = 'property_group_name'

Columns

Contact lists in HubSpot can be used to group together contacts with similar characteristics.

Table Specific Information

Contact lists represent lists that contacts can be added to in order to more easily sort them. They can be either manual lists or dynamic lists. Contacts will be added to the list automatically when they are added to HubSpot based on some criteria.

SELECT

Contact lists can be filtered by ListId or by whether or not they are dynamic. For example:

SELECT * FROM ContactLists WHERE ListId = '123456789'
SELECT * FROM ContactLists WHERE IsDynamic = 'false'

Multiple ListIds may be specified together with an IN operator:

SELECT * FROM ContactLists WHERE ListId IN (1, 2, 3)

Columns

Contact properties are the standard and custom pieces of field data that appear in HubSpot.

Table Specific Information

Contact properties are a number of custom properties that are available to store data about the contacts in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Contacts on a new connection.

SELECT

Contact properties can only be filtered by the unique contact property name or group name. For example:

SELECT * FROM ContactProperties WHERE Name = 'property_name'
SELECT * FROM ContactProperties WHERE GroupName = 'group_name'

Columns

Contact property groups in HubSpot offer a way of organizing individual types of properties for contacts. Each contact property must belong to a property group.

Table Specific Information

Contact property groups offer a means of organizing the various custom properties that are available for defining properties about a given contact. Contact property groups can be selected, inserted, updated, or deleted from this table.

SELECT

Contact property groups can only be filtered by the unique contact property group name. For example:

SELECT * FROM ContactPropertyGroups WHERE Name = 'property_group_name'

Columns

Your contacts in HubSpot may be used for marketing campaigns and can be retrieved, inserted, updated, and deleted from this table.

Table Specific Information

Contacts in HubSpot represent different contacts you are interested in keeping track of for potential marketing opportunities. Contacts are similar to companies: They both have a number of custom properties that will be dynamically determined and added to the table from your HubSpot Hub.

SELECT

When selecting contacts, they can only be filtered by the VID, Email, UserToken, ListId, or AssociatedCompanyId. However, most of these filters cannot be used at the same time. The accepted filters are illustrated below:

SELECT * FROM Contacts WHERE VID = 123456789
SELECT * FROM Contacts WHERE VID IN (123, 456)
SELECT * FROM Contacts WHERE VID > 123456789
SELECT * FROM Contacts WHERE Email = '[email protected]'
SELECT * FROM Contacts WHERE Email IN ('[email protected]', '[email protected]')
SELECT * FROM Contacts WHERE UserToken = 'ABC123456'
SELECT * FROM Contacts WHERE SearchTerms = 'Contact Name'
SELECT * FROM Contacts WHERE ListId = '123456789'
SELECT * FROM Contacts WHERE AssociatedCompanyId = '123456789'

UPDATE

You can update contacts, for example, Additional email addresses:

UPDATE [Contacts] SET [Additional email addresses] = '[email protected];[email protected]', [last name] = 'last name 5' WHERE VID = 123456789

Columns

Retrieves the contacts in a given list in HubSpot. New contacts may be added to the list or removed from it. A ListId must be specified to retrieve the current contacts in the list.

Table Specific Information

Contacts in a given list can be selected, inserted, and removed from that list using this table.

SELECT

When selecting from ContactsInList, the ListId must be specified in order to retrieve any results. For example:

SELECT * FROM ContactsInList WHERE ListId = '123456789'

Columns

Query the available CRM Associations in HubSpot.

Table Specific Information

The CrmAssociations table is used to manage associations between tickets, products, line items, and their related contacts, companies, and deals.

SELECT

Get the IDs of objects associated with the given object (FromObjectId), based on the specified association type (DefinitionId).See the HubSpot API documentation for a list of DefinitionIds. FromObjectId and DefinitionId are required. For example, to get the ticket IDs for all tickets associated with a contact record with vid 25, the FromobjectId would be 25, and the definitionId would be 15:

SELECT * FROM CrmAssociations WHERE FromObjectId = '25' AND DefinitionId = '15'

INSERT

You would use insertion to associate a ticket with a contact, or to associate a line item object to a deal. When creating a new CRM Association FromObjectId, ToObjectId, Category (currently this must be 'HUBSPOT_DEFINED') and DefinitionId are required. For example:

INSERT INTO CrmAssociations (FromObjectId, ToObjectId, Category, DefinitionId) VALUES (496346, 176602, 'HUBSPOT_DEFINED', 15)

UPDATE

UPDATEs are not supported for this table.

DELETE

When deleting a CRM Association FromObjectId, ToObjectId, Category (currently this must be 'HUBSPOT_DEFINED') and DefinitionId are required. For example:

DELETE FROM CrmAssociations WHERE FromObjectId = 496346 AND ToObjectId = 176602 AND Category = 'HUBSPOT_DEFINED' AND DefinitionId = 15

Columns

Deal associations can be used to insert or delete individual associations with Deals.

Table Specific Information

Deal associations represent Company and Contact associations made with existing deals that may be modified.

SELECT

When selecting deal associations, they can only be filtered by the Id. For example:

SELECT * FROM DealAssociations WHERE DealId = 12345

INSERT

New associations may be inserted by specifying the DealId, AssociationId, and the AssociationType during an INSERT statement. For example:

INSERT INTO DealAssociations (DealId, AssociationId, AssociationType) VALUES (12345, 6789, 'COMPANY')

DELETE

Associations may be removed by specifying the DealId, AssociationId, and the AssociationType during a DELETE statement. For example:

DELETE FROM DealAssociations WHERE DealId = 12345 AND AssociationId = 6789 AND AssociationType = 'COMPANY'

Columns

Deal properties are the standard and custom pieces of field data that appear in HubSpot.

Table Specific Information

Deal properties are a number of custom properties that are available to store data about the deals in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Deals on a new connection.

SELECT

Deal properties can only be filtered by the unique deal property name. For example:

SELECT * FROM DealProperties WHERE Name = 'property_name'

Columns

Contact property groups in HubSpot offer a way of organizing individual types of properties for contacts. Each contact property must belong to a property group.

Table Specific Information

Deal property groups offer a means of organizing the various custom properties that are available for defining properties about a given deal. Deal property groups may be selected, inserted, updated, or deleted from this table.

SELECT

Deal property groups may only be filtered by the unique deal property group name. For example:

SELECT * FROM DealPropertyGroups WHERE Name = 'property_group_name'

Columns

Your deals in HubSpot which may be used for making offers to potential customers.

Table Specific Information

Deals in HubSpot represent different organizations you are interested in keeping track of for potential marketing opportunities. They are treated similarly like contacts and have a number of custom properties that will be dynamically determined and added to the Deals table from your HubSpot Hub.

SELECT

When selecting deals, they can only be filtered by the DealId and only one Deal at a time. Otherwise they can be selected without a filter, which will cause all deals in your HubSpot account to be listed. For example:

SELECT * FROM Deals WHERE DealId = '123456789'

Columns

Retrieve the current ecommerce settings for your portal or app.

Table Specific Information

The Ecommerce Settings table is where you set up the property mappings for your use of the Ecommerce Bridge API.

SELECT

When selecting settings, they can only be filtered by the AppId and only one AppId at a time. Otherwise they can be selected without a filter, which will cause all settings in your HubSpot account to be listed. For example:

SELECT * FROM EcommerceSettings

SELECT * FROM EcommerceSettings WHERE AppId = '123456'

Columns

The subscription types a given email is subscribed to. An email must be specified to return results. Subscriptions may be removed by deleting them.

Table Specific Information

Email subscriptions in HubSpot represent different types of subscriptions a given contact email may be subscribed to. When deleting an email subscription the contact will be opted out and will not receive emails from the specified subscription type(mailing group). EmailSubscriptions lists the subscriptions a given email address is subscribed to.

SELECT

When selecting email subscriptions, an email must be provided. For example:

SELECT * FROM EmailSubscriptions WHERE Email = '[email protected]'

Columns

Engagements represent any of a number of different types of engagements you have in HubSpot.

Table Specific Information

Engagements represent any of a number of different types of engagements you have in HubSpot. These can very from simple Tasks, to Emails, Calls, Meetings, or others. Due to the different types of engagements available, any individual Engagement will have several columns that come back null due to its type.

SELECT

When selecting engagements, they can only be filtered by the Id. For instance:

SELECT * FROM Engagements WHERE Id = 12345

Insert and Update

When inserting Engagements, a Type must be specified. In addition, different fields are available on insert depending on the type used. The available fields for each type are as follows: CALL, EMAIL, MEETING, NOTE, PUBLISHING_TASK, TASK

• CALL: ToNumber, FromNumber, Body, DurationMilliseconds, Status, ExternalId, ExternalAccountId, RecordingUrl, Disposition
• EMAIL: FromEmail, FromFirstName, FromLastName, ToEmail, Cc, Bcc, Subject, EmailHtml, EmailText
• MEETING: Body, StartTime, EndTime, Title
• PUBLISHING_TASK: Body, CampaignGuid, Category, CategoryId, State, Name
• TASK: Body, Status, ForObjectType

INSERT INTO Engagements (DateTime, Type, Body, CampaignGuid, Category, CategoryId, ContentId, State, Name, AssociatedContacts) VALUES ('1/1/2011', 'PUBLISHING_TASK', 'Cool Post with Topics', 'f43fe9fd-4082-4a45-93d8-cb8a88f01654', 'BLOG_POST', 3, '2682673052', 'TODO', 'Test Blog Task With topics 3', '234, 567')

Columns

Create and manage HubSpot Forms.

Table Specific Information

Forms represent the forms on your website that a user or contact can fill out, such as a survey.

SELECT

When selecting forms, they can only be filtered by GUID. For example:

SELECT * FROM Forms WHERE GUID = '123456789'

Columns

Get all of the properties for the line item object type, along with the property definitions.

Columns

Query the available line items in HubSpot.

Table Specific Information

A line item represents a line in an order, containing details such as the product, quantity, and price for each line of an order.

SELECT

When selecting line items, they can be filtered by ID or by ID and IncludeDeleted. For example:

SELECT * FROM LineItems WHERE Id = '123456789'
SELECT * FROM LineItems WHERE Id = '123456789' AND IncludeDeleted = 'true'

INSERT

When creating a new line item ProductId is required. You can also specify Name, Description, Price and Quantity. For example:

INSERT INTO LineItems (ProductId, Name, Description, Quantity, Price) VALUES ('123', 'NameExample', 'DescriptionExample', '50', '1324')

UPDATE

When updating line items, you can change non readOnly field by specifying the line item Id. For example:

UPDATE LineItems SET Name = 'UpdatedName', Description = 'Updated description', Price = 123 WHERE id = '123' 

DELETE

You can delete line items singularly by Id, or in batches. For example:

DELETE FROM LineItems WHERE Id = 123
DELETE FROM LineItems WHERE Id IN ( '123', '234')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Create and manage HubSpot Marketing Emails.

Columns

Get all of the properties for the product object type, along with the property definitions.

Columns

Query the available products in HubSpot.

Table Specific Information

Products are a foundational object in HubSpot CRM. Products represent goods or services sold by your company.

SELECT

When selecting products, they can be filtered by ID or by ID and IncludeDeleted. For example:

SELECT * FROM Products WHERE Id = '123456789'
SELECT * FROM Products WHERE Id = '123456789' AND IncludeDeleted = 'true'

INSERT

When creating products, you can specify Name, Description and Price. For example:

INSERT INTO Products (Name, Description, Price) VALUES ('NameExample', 'This is a description', 1324)

UPDATE

When updating products, you can change Name, Description and Price by specifying the product Id. For example:

UPDATE Products SET Name = 'UpdatedName', Description = 'Updated desciption', Price = 123 WHERE id = '123' 

DELETE

You can delete products singularly by Id, or in batches. For example:

DELETE FROM Products WHERE Id = 123
DELETE FROM Products WHERE Id IN ( '123', '234')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Create and manage HubSpot social media messages.

Table Specific Information

Social media messages can be submitted to your social media channels to make announcements about your products or company.

SELECT

When selecting social media messages, data can be filtered by the MessageGUID or by a combination of Status, ChannelGUID, and TriggerAt. For example:

SELECT * FROM SocialMediaMessages WHERE MessageGUID = '123456789'
SELECT * FROM SocialMediaMessages WHERE Status = 'WAITING' AND ChannelGUID = '123456789' AND TriggerAt > '1/30/2015'

Note that TriggerAt can only be used with the > or >= comparison.

Columns

Get all of the properties for the ticket object type, along with the property definitions.

Table Specific Information

Ticket properties are a number of custom properties that are available to store data about the tickets in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Tickets on a new connection.

SELECT

Ticket properties can only be filtered by the unique ticket property name. For example:

SELECT * FROM TicketProperties WHERE Name = 'property_name'

Columns

Get all of the property groups for the specified Ticket object type.

Table Specific Information

Ticket property groups offer a means of organizing the various custom properties that are available for defining properties about a given ticket. Ticket property groups can be selected, inserted, updated, or deleted from this table.

SELECT

Ticket property groups can only be filtered by the unique ticket property group name. For example:

SELECT * FROM TicketPropertyGroups WHERE Name = 'property_group_name'

Columns

Get all tickets from a portal.

Table Specific Information

Tickets, along with contacts, companies and deals, are a foundational object in HubSpot CRM. A ticket represents a customer request for help, support, service, or a response, and the responses generated in reply to this request. Tickets are similar to companies: They both have a number of custom properties that will be dynamically determined and added to the table from your HubSpot Hub.

SELECT

When selecting Tickets, they can only be filtered by the Id. The accepted filters are illustrated below:

SELECT * FROM Contacts WHERE Id = 123456789
SELECT * FROM Contacts WHERE Id IN (123, 456)

Columns

Select and manage HubSpot workflows.

Table Specific Information

Workflows are automated tasks in HubSpot. You can perform automated tasks with contacts by enrolling contacts in workflows.

SELECT

When selecting workflows, data may be filtered by the WorkflowId. For example:

SELECT * FROM Workflows WHERE WorkflowId = '123456789'

Columns

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - HubSpot Views

Get analytics data broken down by the specified category.

Table Specific Information

Get analytics data broken down by the specified category.

SELECT

When selecting analytics by breakdown category, they can only be filtered by the StartDate, EndDate, Granularity, BreakdownBy, Filter, FirstDrilldown, SecondDrilldown, Exclude, FilterId, Sort or SortDirection. Defaults to the Totals dimension. The filters must use an exact comparison. For example:

SELECT Totals FROM AnalyticsBreakdowns WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT Sources FROM AnalyticsBreakdowns WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT Geolocation FROM AnalyticsBreakdowns WHERE Granularity = 'monthly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018' AND Filter = 'hubspot'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Get analytics data for your HubSpot hosted content. This would include your website and landing pages, as well as any blog pages hosted on HubSpot.

Table Specific Information

Get analytics data for your HubSpot hosted content. This would include your website and landing pages, as well as any blog pages hosted on HubSpot.

SELECT

When selecting analytics by content type, they can only be filtered by the StartDate, EndDate, Granularity, ContentType, Filter, Exclude, FilterId, Sort or SortDirection. Defaults to the StandardPages dimension. The filters must use an exact comparison. For example:

SELECT LandingPages FROM AnalyticsContents WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT ListingPages FROM AnalyticsContents WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT BlogPosts FROM AnalyticsContents WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018' AND Filter = 'hubspot'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Get analytics data for event completion objects.

Table Specific Information

Get analytics data for event completion objects.

SELECT

When selecting analytics for event completions, they can only be filtered by the StartDate, EndDate, Granularity, Filter, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsEventCompletions WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT * FROM AnalyticsEventCompletions WHERE Granularity = 'summarize/weekly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Get analytics data for form objects.

Table Specific Information

Get analytics data for form objects.

SELECT

When selecting analytics for forms, they can only be filtered by the StartDate, EndDate, Granularity, Filter, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsForms WHERE Granularity = 'summarize/monthly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT * FROM AnalyticsForms WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018 AND Sort = 'FormViews''

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Get analytics data broken down by sessions.

Table Specific Information

Get analytics data broken down by sessions.

SELECT

When selecting analytics for event completions, they can only be filtered by the StartDate, EndDate, Granularity, Filter, FirstDrilldown, SecondDrilldown, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsSessions WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT * FROM AnalyticsSessions WHERE Granularity = 'monthly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018' AND Filter = 'hubspot'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Get analytics data for social assist objects.

Table Specific Information

Get analytics data for social assist objects.

SELECT

When selecting analytics for event completions, they can only be filtered by the StartDate, EndDate, Granularity, Filter, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsSocialAssists WHERE Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'
SELECT * FROM AnalyticsSocialAssists WHERE Granularity = 'summarize/weekly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source. For more information, see the WHERE clause section.

Get the details for the analytics views set up in the portal.

Columns

Retrieve the available blog authors in HubSpot.

Table Specific Information

Blog authors represent the users in your HubSpot Hub that can make posts on your HubSpot blog.

SELECT

When selecting blog authors, they can only be filtered by the Id, FullName, CreatedAt, or Slug. CreatedAt can be used to specify a range while the others must be used with an exact comparison. For example:

SELECT * FROM BlogAuthors WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'
SELECT * FROM BlogAuthors WHERE Slug = 'abc123'

Columns

Retrieve the available blog posts in HubSpot.

Table Specific Information

Blog posts represent the individual posts in your HubSpot blog.

SELECT

When selecting blog posts, they can only be filtered by the Id, BlogAuthorId, ArchivedInDashboard, CampaignId, ContentGroupId, CreatedAt, ArchivedAt, Name, Slug, and UpdatedAt columns. CreatedAt, ArchivedAt, and UpdatedAt can be used with > and < where a range can be formed with CreatedAt and UpdatedAt. In addition, Name can be used with the LIKE comparison. All others must be used with an exact comparison. For example:

SELECT * FROM BlogPosts WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'
SELECT * FROM BlogPosts WHERE UpdatedAt >= '1/1/2014' AND UpdatedAt <= '10/31/2014'
SELECT * FROM BlogPosts WHERE ArchivedAt >= '1/1/2014'
SELECT * FROM BlogPosts WHERE Name LIKE 'My Post'

Columns

Retrieve the available blogs in HubSpot.

Table Specific Information

SELECT

When selecting blogs, they can only be filtered by the Id, Name, and CreatedAt. CreatedAt can be used to specify a range. For example:

SELECT * FROM Blogs WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'
SELECT * FROM Blogs WHERE Name = 'myblog'

Columns

Retrieve the available blog topics in HubSpot.

Table Specific Information

Blog topics represent any number of topics a blog post can be posted under.

SELECT

When selecting blogs, they can only be filtered by the Id, Name, Slug, and CreatedAt. CreatedAt can be used to specify a range. For example:

SELECT * FROM BlogTopics WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'
SELECT * FROM BlogTopics WHERE Name = 'mytopic'

Columns

Retrieve the available comments from your blog in HubSpot.

Table Specific Information

Comments represent any comments that can be made on a given blog post.

SELECT

When selecting comments, they can only be filtered by the Id, State, PostId, and Comment. Comment can be used with the LIKE comparison. For example:

SELECT * FROM Comments WHERE Comment LIKE 'comment text'
SELECT * FROM Comments WHERE Id = '123456789'

Columns

A list of the historical values of the property.

Columns

List of Contact's Form Submissions.

Table Specific Information

A list of form submissions for the contact. This list will be empty for records with no form submissions.

SELECT

Contact form submissions can only be filtered by the unique contact id. For example:

SELECT * FROM ContactFormSubmissions  
SELECT * FROM ContactFormSubmissions WHERE ContactVID = '123456'

Columns

List of Contact's Identity profiles

Table Specific Information

A list of objects representing the identities of the contact. Each identity represents an identifier for the object, many records will only have a single identity, but merged records may have multiple.

SELECT

Contact identity profiles can only be filtered by the unique contact id. For example:

SELECT * FROM ContactIdentityProfiles  
SELECT * FROM ContactIdentityProfiles WHERE ContactVID = '123456'

Columns

Returns the list memberships of contacts in HubSpot.

Columns

A list of the historical values of the property.

Columns

Basic contact statistics that can be retrieved from HubSpot such as the total number and when the last new contact was created.

Columns

Deal pipelines may be used to organize what stages a given deal is on.

Table Specific Information

Deal pipelines represent different types of pipelines deals and be placed into to track their progress.

SELECT

When selecting deal pipelines, they can only be filtered by the PipelineId. For example:

SELECT * FROM DealPipelines WHERE PipelineId = 12345

Columns

The stages for a given Deal Pipeline.

Table Specific Information

Deal pipeline stages represent the individual stages of a given pipeline that a Deal may currently be on.

SELECT

When selecting deal pipeline stages, they can only be filtered by the PipelineId. For example:

SELECT * FROM DealPipelineStages WHERE PipelineId = 12345

Columns

A list of the historical values of the property.

Columns

The stages for a given Deal.

Columns