Sync App アプリケーションの接続 ページに移動し、接続の追加 パネルで対応するアイコンを選択して、HubSpot への接続を作成します。HubSpot アイコンが利用できない場合は、Add More アイコンをクリックしてCData サイトからHubSpot コネクタをダウンロードおよびインストールします。

必須プロパティは［設定］タブにリストされています。［Advanced］タブには、通常は必要ない接続プロパティが表示されます。

HubSpot への認証

HubSpot データソースへの接続に使用できる認証方法は2つあります。非公開アプリトークンベースおよびOAuth 認証です。

HubspotV3 スキーマのいくつかのテーブルは、OAuth の仕組みでは動作しないかもしれません。API 制限のため、これらのスキーマはPrivateAppToken ベース認証での使用に限定されます。具体的には、データモデルセクションの各テーブルで、サポートされる認証方法を説明します。

OAuth

Web アプリケーション

Web アプリケーション経由で接続する場合は、HubSpot にカスタムOAuth アプリケーションを登録する必要があります。それからSync App を使用してOAuth トークンの値を取得および管理します。詳しくは、カスタムOAuth アプリの作成 を参照してください。

OAuth アクセストークンの取得

次の接続プロパティを設定し、OAuthAccessToken を取得します。

• OAuthClientId：アプリケーション設定のクライアントId に設定。
• OAuthClientSecret：アプリケーション設定のクライアントシークレットに設定。

続いてストアドプロシージャを呼び出し、OAuth 交換を完了します。

1. GetOAuthAuthorizationURL ストアドプロシージャを呼び出します。AuthMode インプットをWEB に、CallbackURL をアプリケーション設定で指定したリダイレクトURI に設定します。 ストアドプロシージャがOAuth エンドポイントのURL を返します。
2. ステップ1でストアドプロシージャが返したURL に移動します。ログインしてWeb アプリケーションを認可します。コールバックURL にリダイレクトされます。
3. GetOAuthAccessToken ストアドプロシージャを呼び出します。AuthMode インプットをWEB に設定します。Verifier インプットを、リダイレクトURL のクエリ文字列のcode パラメータに設定します。

OAuthAccessToken 接続プロパティをストアドプロシージャで返されたアクセストークンに設定し、データに接続します。ExpiresIn 秒後に、アクセストークンの期限が切れたときは、GetOAuthAccessToken を再度呼び出し、新しいアクセストークンを取得します。

カスタムフィールドへのアクセス

多数のカスタムフィールドを定義している場合、HubSpot はリクエスト可能なフィールド数を制限するので、エラーが発生してSELECT * クエリが失敗するかもしれません。このエラーを避けるために、ContactPropertiesFile およびDealPropertiesFile で指定されたテキストファイル内に、返したいカスタムフィールドを指定することができます。または、WHERE 句に主キーを指定してください。

検知されたカスタムフィールドのカラムサイズが十分でない場合、ColumnSizes でサイズを指定できます。

データアクセスのファインチューニング

次のプロパティを使って、カラム名をより詳細に制御できます。

• UseDisplayNames
• UseSimpleNames

このセクションでは、HubSpot Sync App の高度な機能を厳選して説明します。

ユーザー定義ビュー

Sync App を使用すると、事前設定されたクエリによって内容が決定されるユーザー定義ビューと呼ばれる仮想テーブルを定義できます。 このビューは、ドライバーに発行されるクエリを直接制御できない場合に有効です。 カスタムビューの作成と設定の概要については、ユーザー定義ビュー を参照してください。

SSL の設定

SSL の設定 を使用して、Sync App が証明書のネゴシエーションをどのように扱うかを調整します。さまざまな証明書形式を選択できます。 詳しくは、接続文字列オプションにあるSSLServerCert プロパティを参照してください。

ファイアウォールとプロキシ

Windows プロキシとHTTP プロキシを含むファイアウォールとプロキシ に合致するようSync App を設定します。トンネル接続を設定することもできます。

クエリ処理

Sync App は、HubSpot にできるだけ多くのSELECT ステートメント処理をオフロードし、残りのクエリをクライアント側のインメモリで処理します。

詳しくはクエリ処理 を参照してください。

ログ

CData ログを調整するために使用可能な設定の概要については、ログ を参照してください。基本的なロギングでは、 次の2つの接続プロパティを設定するだけです。LogModules 接続プロパティを使用してログに記録する情報のサブセットを選択できる、 より洗練されたロギングをサポートする多数の機能があります。

SSL 設定のカスタマイズ

デフォルトでは、Sync App はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

CData Sync App は、HubSpot エンティティをリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 このセクションでは、API の制限および要件について説明します。既定の SupportEnhancedSQL 機能を使って、これらの制限の大半を回避できます。

HubSpot API の使用

Marketplace API で利用可能なエンティティについては、HubSpot データモデル を参照してください。

HubSpot V3 API の使用

Selling Partner API で利用可能なエンティティについては、HubSpot V3 データモデル を参照してください。

CData Sync App は、HubSpot エンティティをリレーショナルテーブル、ビュー、およびストアドプロシージャにモデル化します。 このセクションでは、API の制限および要件について説明します。既定の SupportEnhancedSQL 機能を使って、これらの制限の大半を回避できます。

テーブル

テーブル では、利用可能なテーブルを説明します。

ビュー

ビュー は変更ができないテーブルです。一般的には、Read-only で更新が不可能なデータはビューとして表されます。

ストアドプロシージャ

ストアドプロシージャ は、データソースのファンクションライクなインターフェースです。これらを使用して、データソース内の情報を検索、更新、および変更できます。

Sync App はHubSpot のデータを、標準のSQL ステートメントを使用してクエリできるテーブルのリストにモデル化します。

一般的には、HubSpot テーブルのクエリは、リレーショナルデータベースのテーブルのクエリと同じです。時には特別なケースもあります。例えば、テーブルの特定のカラムデータを取得するために特定のカラムをWHERE 句に含める必要がある場合などです。これは通常、特定のカラムを取得するために行ごとに個別のリクエストを行う必要がある場合に必要です。これらの特別な状況は、以下にリンクされているテーブルページの上部に明確に文書化されています。

HubSpot Connector for CData Sync テーブル

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

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

Create and manage HubSpot Marketing Emails.

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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

ビューは、カラムと疑似カラムで構成されます。ビューは、データを示すという点でテーブルに似ていますが、ビューでは更新はサポートされません。通常、ビューとして表されるエンティティは、読み取り専用のエンティティです。多くの場合、これらのデータはストアドプロシージャを使用することで更新できます（その機能がデータソースに適用できる場合）。

ビューに対しては、通常のテーブルと同じようにクエリを実行でき、このときに返されるデータも同様です。

ビューとして公開されるクエリなどの動的ビューや、project_team ワークアイテムの特定の組み合わせを検索するためのビューがサポートされています。

HubSpot Connector for CData Sync ビュー

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。 詳細については、WHERE 句のセクションを参照してください。

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, IsArchived, CampaignId, ContentGroupId, CreatedAt, DeletedAt, Name, Slug, and UpdatedAt columns. CreatedAt, DeletedAt, 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 DeletedAt >= '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

Retrieve the available domains in HubSpot.

Table Specific Information

Domains represent any domains you have registered with HubSpot to host your HubSpot blogs and content optimization system on.

Select

When selecting domains, they can only be filtered by the Id, CreatedAt, Domain, IsResolving, and PrimarySitePage columns. CreatedAt can be used with the > and < comparisons to form a range. For example:

SELECT * FROM Domains WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'
SELECT * FROM Domains WHERE Id = '123456789'

Columns

Get errors from previously processed sync messages.

Table Specific Information

Because sync messages are processed asynchronously, problems with processing are not surfaced at the time of submission. Instead, you can use the sync errors endpoint to retrieve all errors related to the processing of ecommerce data.

Select

When selecting sync errors, 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 EcommerceSyncErrors

SELECT * FROM EcommerceSyncErrors WHERE AppId='123456'

Columns

The events associated with an email campaign or a recipient.

Table Specific Information

Email campaign events represent individual events that occurred during an email campaign. These are generally events such as a contact clicking on a link that was included in the email.

Select

Email campaign events may be filtered by RecipientEmail, AppId, CampaignId, Type, and CreatedAt. Email campaign events can also be retrieved one at a time by specifying both the Id and CreatedAt, but the CreatedAt datetime will need to be correct to the millisecond. For example:

SELECT * FROM EmailCampaignEvents
SELECT * FROM EmailCampaignEvents WHERE CreatedAt='9/23/2014 5:28:00.280 PM' AND Id='123456789'
SELECT * FROM EmailCampaignEvents WHERE CampaignId='14229773' AND AppId = '113' AND Type='CLICK' AND CreatedAt > '9/23/2014 1:28:00 PM' AND CreatedAt < '9/23/2014 7:29:00 PM'

Columns