在 Snowflake 中查詢 HubSpot 資料

將 Snowflake 連接到您的 HubSpot 帳戶後，您可以使用 Snowflake 的資料共享從您的 Snowflake 帳戶查詢 HubSpot 資料。

數據滯後和您的 Snowflake 帳戶區域

您將可存取每 15 分鐘更新一次的 V2_LIVE 結構描述，以及每天更新一次的 V2_DAILY 結構描述。

請注意：V2_LIVE 模式中的下列檢視僅每日更新：association_definitions、owners、pipelines 和 pipeline_stages。

設定您的 Snowflake 環境

若要在 Snowflake 中查詢您的 HubSpot 資料，您需要選擇要查詢的倉庫、資料庫和模式。您可以在工作表層級設置它們，使它們自動包含在您的查詢中，或者您可以手動添加它們。

在工作表層級選擇倉庫、資料庫和模式：

• 在工作表的頂行中，按一下工作表動作列。
  
  
• 在彈出視窗中
  • 按一下倉庫下拉式功能表，然後選擇您的倉庫。
  • 按一下資料庫下拉式功能表，然後選擇您的 HubSpot 資料庫。
  • 按一下模式下拉式功能表，然後選擇您要使用的模式：
    • V2_DAILY： 每日模式，根據表格查詢資料。此模式中的資料每天更新一次。使用此模式會加快查詢速度，建議直接在 Snowflake 中查詢。
    • V2_LIVE：基於 HubSpot 資料安全檢視的即時模式。此模式中的資料最多每 15 分鐘更新一次。如果您要查詢大型資料集，查詢速度可能較慢。在這種情況下，建議先使用ETL 程序將資料複製到您自己的資料表，然後再查詢該資料表。
    
    
    

配置好您的環境後，您就可以對 HubSpot 資料進行查詢，而無需在查詢中手動添加資料庫或模式類型。

要在查詢中手動添加資料庫和模式類型，請在FROM 欄位的開頭添加資料庫名稱和模式。例如

資料概觀

查詢使用 SQL 建構，可用於擷取 HubSpot 資料，例如：

• 物件： 標準和自訂物件，例如聯絡人、公司、產品。
• 記錄： 個別記錄，例如個別聯絡人或公司。
• 關聯：所有可用的關聯類型和目前關聯的記錄。
• 事件：標準 HubSpot 事件（如電子郵件開啟）和自訂行為事件的事件資料。
• 區段：HubSpot聯絡人和公司區段。
• 擁有者：HubSpot 帳戶中的使用者，與他們設定為擁有者的記錄相關。
• 管道： 交易和票據管道，包括管道階段。
• 屬性：CRM 記錄上的屬性及其值，包括屬性歷史。

進一步了解 HubSpot 的 CRM 資料模式。

請注意： 隨著資料分享的演進，我們將引進變更，有些是破壞性的變更。強烈建議訂閱HubSpot 開發人員變更日誌。這些變更總是會在任何更新之前張貼到變更日誌中。

限制和排序返回的數據

在建構查詢時，您可以透過在查詢中加入LIMIT 和ORDER BY 來控制傳回資料的數量，以及傳回資料的順序。例如，若要查詢所有連絡人及其屬性，但只傳回三個結果，並按連絡人 ID 排序，您可以如下方式建立查詢：

欄位資料類型

在 Snowflake 中查询数据时，返回的列取决于您要查询的数据。您可 在左側欄中選擇 Snowflake 中特定表或檢視，以檢視該表或檢視的可用欄 。

請注意：在本文件的其餘部分，當上下文同時適用於資料表和檢視時，我們使用資料庫物件一詞。

若要返回特定列，請在查詢的SELECT 欄位中包含該列。例如，下面的查詢將只返回OBJECTID 和UpdatedAt 列：


SELECTobjectId, updatedAt
FROM objects_contacts



以下是查詢可能回傳列的一般定義。如需以下未包含的其他定義，請參閱上述文章中的個別檢視定義。

• OBJECTTYPEID：代表物件類型的VARCHAR值。例如，0-1代表聯絡人物件，0-2代表公司物件，而2-1232可能代表您帳戶中的自訂物件。在區段中，這指的是區段類型 (例如，聯絡人或公司)。您可以使用object_and_event_type_definitions檢視查詢帳戶中的所有物件和事件 ID。
• OBJECTID：一個BIGINT數字，代表您 HubSpot 帳戶中特定類型物件的單一記錄。要識別一筆記錄，您需要同時使用物件類型 ID 和物件 ID。
• INGESTEDAT：一列資料新增至 Snowflake 的時間戳記，以TIMESTAMP_NTZ值表示。與 HubSpot 中建立或更新作業的邏輯時間戳無關。
  • 由於 HubSpot 的內部處理，即使行上的其他欄位沒有改變，行的擷取時間戳也可能會更新。
  • 在建立複製最近變更資料的ETL 流程時，建議您以接收時間戳為基礎進行查詢。此外，如果您的流程每天複製資料，建議複製前兩天的資料以考量延遲的情況。
• CREATEDAT 和 UPDATEDAT：資料在 HubSpot 中建立或更新的邏輯時間戳記，以TIMESTAMP_NTZ 值表示。這些時間戳將與 HubSpot 中的時間戳相符。例如，HubSpot 中顯示的記錄建立日期時間將與 Snowflake 中CREATEDAT列中的時間戳相同。這些屬性與擷取時間戳記沒有直接關係，因此不建議ETL 流程以這些屬性為基礎。
• COMBINEDASSOCIATIONTYPE：唯一識別關聯定義的VARCHAR值。

轉換資料類型

有時候，從查詢傳回的資料是VARCHAR欄位，但我們希望它們是不同的類型。在這種情況下，建議使用 Snowflake 函式將 VARCHAR 資料類型轉換為所需的類型。例如，在大多數資料庫物件中，物件記錄的屬性都儲存為 VARCHAR 資料類型，但有時也包含數字和日期時間值。函數如
try_to_number, try_to_timestamp_ntz可以用來將這些 VARCHAR 資料分別轉換成NUMBER和DATETIME類型：


SELECT objectid, value AS amount_str, try_to_number(value) AS amount
FROM object_properties
WHERE objecttypeid='0-3' AND name='amount'
LIMIT 1



物件查詢

下面，學習如何從特定的 HubSpot 物件和記錄查詢資料，以及返回的資料。

不同類型的物件資料以兩種方式組織和提供：

• 個別：每個物件類型儲存在自己的檢視中，並且只包含相同物件類型的記錄，例如 objects_contacts 只包含聯絡人記錄。這樣就不需要在查詢時指定 objecttypeid。這些檢視更容易查詢，因為您不需要知道 ObjectTypeIds。它們也以更可用的格式傳回資料，每一行對應一個物件及其屬性的目前版本。只要您的查詢速度不慢，建議您使用這些檢視來擷取物件資料。請參閱下面的 Objects_X 檢視。
• 合併： 所有物件類型的所有記錄都會合併為一個資料庫物件。因此，在查詢時，您需要指定類似 "WHERE objectTypeId='0-1'"的篩選條件。例如，您可以在查詢時使用下列 objecttypeids：

自訂物件在建立時會被指派一個 ID，並識別為2-unique_ID。例如，2-12345。瞭解更多關於object_and_event_type_definitions 的資訊。

如果您想要比從 object_x 檢視中得到更好的查詢效能，這些資料庫物件通常是適合的。請參閱以下 object_with_object_properties、object_properties、objects 及 object_properties_history 的詳細資訊。

object_x 檢視

在此資料庫物件中，每個可用的物件類型，包括自訂物件，都有其特定的檢視，每個檢視只包含該特定物件類型的物件記錄。这些视图按照 OBJECTS_<object type name> 格式命名，其中 <object type name> 是对象类型的复数形式标签（请注意，如果对象类型没有复数形式，则 <object type name> 只是对象类型名称的单数形式标签，并与 "S "连接）。

查詢此檢視可為物件內的每條記錄傳回一列，物件的每個屬性都可作為單獨的一列。

請參閱以下可用 HubSpot（即非自訂）物件類型的完整清單、它們所代表資料的簡要說明及其對應的檢視：

每個資料庫物件都會傳回下列欄位：

範例 1

擷取所有聯絡人的名字、姓氏和生命週期階段。

查詢：


SELECT objectId, property_firstname AS firstname, property_lastname AS lastname,property_lifecyclestage AS lifecyclestage
FROM objects_contact



回傳：

範例 2

擷取最多三筆交易的交易名稱和階段。

查詢：


SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmount

FROM objects_deals

LIMIT 3

回傳：

範例 3

依金額擷取前三筆交易的交易名稱和階段。

查詢：

由於交易金額資料是以數字資料型態提供，因此可以用來排序，而不需要進行轉換或轉換。


SELECT objectId, property_dealName, property_dealStage,
property_amount AS dealAmount

FROM objects_deals
ORDER BY property_amount DESC
LIMIT 3




回傳值：

有物件屬性的物件

此資料庫物件包含 HubSpot 記錄的資料。查詢此資料庫物件會為物件類型中的每條記錄傳回一條記錄 (例如：聯絡人物件類型中的所有聯絡人記錄)。所有屬性都儲存在Properties列中，該列是Snowflake Variant / JSON列。

此檢視可傳回下列欄位：

請注意： 在 2022 年 1 月 25 日，object_with_object_properties資料表和檢視中的timestamp欄（代表物件記錄在 HubSpot 中最後更新的日期和時間）將會更新為updateat。這是為了與 其他資料分享中表示日期時間資料的方式保持一致 。

由於這是一項突破性的變更，新列 updatedat已新增到資料表和檢視中。在接下來的 90 天內，這兩個新列將與它們要取代的 timestamp 列共存。

90 天后，即 2022 年 1 月 25 日，列 timestamp 将从表和视图中移除。在此日期之後，任何嘗試使用時間戳列查詢object_with_object_properties 資料表或檢視的動作都會失敗。

範例 1

擷取所有連絡人及其屬性。

查詢：

回傳：

範例 2

擷取多達三個交易的交易名稱和階段。

查詢：

回傳：

物件

此資料庫物件是 object_with_object_properties 資料庫物件中資料的子集。每個回傳的資料列會回傳每筆記錄的物件 ID。此資料庫物件只會回傳現有的記錄。當記錄在 HubSpot 中被刪除時，它將從此資料庫物件中移除。此資料庫物件是 object_with_object_properties 資料表的資料子集。每條回傳的記錄都會回傳每條記錄的物件 ID。此檢視僅回傳現有記錄。當記錄在 HubSpot 中刪除時，它會從此檢視中移除。

如果您的帳戶有大量資料，導致 object_with_object_properties 和 objects_x 檢視執行緩慢，這個資料庫物件會很有用。將此資料庫物件與 object_properties 資料庫物件一起使用，可以複製 object_with_object_properties 中的資料，同時保持對回傳哪些屬性的控制。

這個資料庫物件可以傳回下列欄位：

範例 1

擷取所有連絡人記錄。

查詢：

傳回：

object_properties

這個資料庫物件是object_with_object_properties資料庫物件的子集。每一行包含設定在指定記錄上的目前屬性值。例如，一個聯絡人會有一列是其名稱屬性，一列是其姓氏屬性。如果某條記錄沒有屬性值，則該行會顯示為空字串或完全不顯示。

此資料庫物件可傳回下列欄位：

範例 1

擷取所有聯絡人的名字、姓氏和城市屬性。

查詢：

回傳：

範例 2

擷取所有連絡人的名、姓和生命週期階段，並將其合併為每個連絡人的一列。此範例使用SQL PIVOT 語法。

查詢：

回傳：

object_properties_history

此資料庫物件包含歷史屬性值。對於聯絡人，它包含最近的 45 個值。對於所有其他物件，則包含最近的 20 個值。為每個歷史屬性值顯示一列。屬性的目前值由該屬性歷史中最近的 UPDAT時間戳決定。

此資料庫物件可傳回下列欄位：

範例 1

擷取單一聯絡人的單一屬性歷史記錄。

查詢：

回傳：

範例 2

擷取所有生命週期階段為Subscriber 的聯絡人目前姓名。

查詢：

傳回：

關聯查詢

與物件資料一樣，關聯資料也以兩種方式組織：

• 個人：每個關聯類型都儲存在自己的檢視中，並且只包含該類型的記錄。例如，associations_contacts_to_deals 只包含 contact 和 deal 物件之間的關聯記錄。這樣就不需要在查詢時指定 associationtypeid。這些檢視很容易查詢，因為它們不需要熟悉 CRM 資料模型就能使用。它們還會返回兩個對象之間關聯記錄的豐富描述，這可以很容易地用於與 objects_x 視圖的連接。只要您的查詢速度不慢，建議您使用這些檢視來擷取關聯資料。請參閱下面的 associations_x_too_y 檢視。
• 合併：所有關聯類型的所有記錄都會合併為一個資料庫物件。因此，在查詢時，您需要指定類似 "WHERE combinedassociationtypeid='0-32' " 的篩選條件。請參閱以下關聯和關聯定義的更多資訊。

關聯_x_to_y 檢視

每個關聯類型都有自己的檢視，標示為 associations_<關聯類型名稱>，例如 associations_contacts_to_deals。每個檢視都是 associations 資料庫物件中資料的子集，只包含特定 combinedassociationtypeid 的記錄。

每個資料庫物件都會傳回下列欄位：

範例 1

檢索與特定連絡人相關的所有交易。

查詢：

傳回：

範例 2

擷取 Bogota 時區的所有連絡人及其交易。

範例 3

擷取所有連絡人以及每位連絡人的交易成交總金額。

回傳值：

關聯定義

此資料庫物件包含 HubSpot 物件之間所有可用關聯類型的資訊，每個關聯類型有一列。您可以將此資料庫物件加入關聯資料庫物件，以取得特定記錄的關聯資訊。

同一對象之間可以有多種關聯類型。主要的關聯類型，也就是記錄關聯時在 HubSpot 中顯示的類型，使用關聯，其中ISMAINASSOCIATIONDEFINITION 是true 。

這個資料庫物件可以回傳以下欄位：

範例 1

檢索帳戶中可用的關聯類型。

查詢：

回傳：

範例 2

擷取交易與公司之間可用的關聯類型。

查詢：

回傳

關聯

此資料庫物件包含特定記錄之間關聯的資訊。每一行代表從一個物件到另一個物件的關聯。即使引用的記錄已被刪除，此資料庫物件中仍可能存在記錄。您可以通过与对象表连接来检查记录是否存在。

如果您的帳戶有大量資料，導致 associations_x_to_y 檢視執行緩慢，此資料庫物件可能會很有用。

此資料庫物件可以傳回下列欄位：

範例 1

讀取所有關聯的交易和聯絡人。

查詢：

回傳：

範例 2

擷取與特定連絡人 (連絡人 ID：3005) 相關的所有票單。

查詢：

傳回：

範例 3

擷取與特定連絡人 (連絡人 ID：4464) 相關的所有交易。此範例使用association_definitions 連結，而非硬式編碼combinedAssociationTypeId。

查詢：

回傳：

業主查詢

擁有者

此資料庫物件包含 HubSpot 帳戶中使用者的相關資訊。使用者可被設定為記錄擁有者，並會同時包含使用者 ID 和擁有者 ID，視其上下文而定。這兩個 ID 可用於識別同一個使用者。

此資料庫物件可傳回下列欄位：

範例 1

擷取所有使用者。WHERE archived = false 過濾器會從傳回的資料中移除已刪除的使用者。

查詢：

回傳：

範例 2

根據擁有者 ID 擷取特定擁有者。

查詢：

回傳：

範例 3

擷取所有連絡人及其擁有者。此查詢使用object_with_object_properties和聯絡人屬性hubspot_owner_id 將記錄與擁有者連結。

回傳：

團隊查詢

團隊

HubSpot 中的團隊可讓您將用戶組織成群組，用於組織和報告目的。你可以有一個主隊伍和額外的隊伍。

此資料庫物件包含您 HubSpot 帳戶中的團隊資訊。

此資料庫物件可以傳回下列欄位：

範例 1

檢索所有團隊。

查詢：

返回：

所有者_團隊

此資料庫物件包含哪些所有者對應 HubSpot 帳戶中哪些團隊的資訊。

此資料庫物件可傳回下列欄位：

範例 1

檢索指定團隊的所有擁有者。

查詢：

SELECT teamId, ownerId, isTeamPrimary
FROM teams
WHERE teamId = 29584574

回傳

管道查詢

下面，瞭解如何查詢 交易和票務管道資料，例如帳戶中的可用管道及其階段。

您可以將此資料與物件資料結合，以獲得管道的完整檢視。例如，您可以將 objects_deals_view 或object_with_object_properties資料與管道資料結合，以查詢交易及其管道。

管道

此資料庫物件包含您帳戶中交易和票務管道的相關資訊。每個回傳的資料列代表一個管道。

此表可返回下列欄位：

範例 1

檢索所有可用的交易管道，依其在 HubSpot 中的順序排序。

查詢：

返回：

範例 2

擷取特定管道內的交易。結合object_with_object_properties和管道。

查詢：

傳回：

範例 3

與上面的範例 2 相同，擷取特定管道內的交易。但是，在連接時不使用 objects_deals，而使用 object_with_object_properties。

回傳值：

管道階段

此檢視包含管道中個別階段的資訊。結合物件類型 ID、管道 ID 和階段 ID 來識別階段。與管道連接時，使用物件類型 ID 和管道 ID。

此資料庫物件可傳回下列欄位：

範例 1

檢索名為Sales pipeline 的交易管道的管道階段。

查詢：

傳回：

範例 2

擷取所有交易及其各自的管道階段。

查詢：

回傳：

屬性查詢

屬性定義

此資料庫物件包含 HubSpot 帳戶中物件屬性和事件屬性的相關資訊。事件屬性包括標準事件，例如電子郵件開啟和點擊，以及自訂行為事件屬性。這些資訊有助於定義object_properties和事件資料中的值。

您可以結合物件類型 ID 和名稱來識別特定的屬性。您可以使用properties API 擷取類似資訊。

此資料庫物件可傳回下列欄位：

範例 1

擷取所有聯絡人屬性的名稱和說明。

查詢：

回傳：

事件查詢

事件包括標準 HubSpot 事件和自訂行為事件。每個事件都有自己的資料庫物件，標示為events_eventname 。

您可以結合事件類型 ID 和 ID 來識別事件。回傳的欄位取決於事件，每個事件屬性有一列。所有事件都會傳回下列欄位：

範例 1

擷取最近的 50 個電子郵件點選事件。

查詢：

傳回：

範例 2

依據點擊的聯絡人擷取所有電子郵件點擊事件。此查詢透過與object_with_object_properties 連結取得聯絡人資料。

查詢：

回傳值：

object_and_event_type_queries

object_and_event_type_definitions

此資料庫物件包含 HubSpot 帳戶中所有物件和事件的定義。

此資料庫物件可傳回下列欄位：

範例 1

擷取 HubSpot 帳戶中所有可用的物件和事件。

查詢：

回傳：

區段查詢

以下將介紹如何查詢您的區段，例如可用的聯絡人和公司區段，以及這些區段所包含的記錄。

區段

此資料庫物件包含有關 HubSpot 聯絡人和公司區段的資訊。每個區段傳回一列，列中包含區段詳細資訊，例如區段名稱、大小以及所包含記錄的物件類型。不包含每個區段內個別記錄的資訊 (請參閱segment_membership)。

此資料庫物件可傳回下列欄位：

範例 1

擷取所有連絡人區段。

查詢：

回傳：

範例 2

擷取名為Top subscribers 2021 的特定連絡人區段。

查詢：

傳回：

網段成員

此資料庫物件包含哪些記錄是哪些段落成員的資訊，段落中每筆記錄有一列。例如，如果您有一個有 500 位聯絡人的 Blog subscribers 區段，資料庫物件會傳回 500 行。

只包含區段 ID 和物件 ID。如需更多資訊，請加入段資料庫物件。

此資料庫物件可傳回下列欄位：

範例 1

擷取Top subscribers 2021區段中所有連絡人的 ID。

查詢：

回傳：

範例 2

擷取Top subscribers 2021區段中聯絡人詳細資料的姓和名。

查詢：

傳回：

常見問題

我想從 Hubspot 應用程式產生類似報表的資料，該怎麼做？

一般來說，您會想看看您要比較的報告中的欄位。如果報告的可視化不是表格式的，將它轉換成表會顯示報告包含的列。這些列通常是物件類型或事件類型的屬性。下一步是找出列所對應的物件類型屬性。您可以透過查詢 property_definitions 來做到這一點：

由於某些屬性名稱通常會在應用程式上解析成更具描述性的名稱，因此不一定會完全符合。在這種情況下，使用 LIKE 運算符對 property_definitions 中的 label (或甚至 description) 欄進行模糊搜尋會有幫助。

在確定欄位屬於哪個 objecttype 之後，就很容易決定要查詢什麼。您可以簡單地搜尋與該類型對應的 object_x 檢視。

我該如何將這個資料類型連結到另一個資料類型，例如，如何擷取廣告系列的表單？

如果您需要將兩個或更多不同的記錄連結到彼此，請看看關聯。關聯代表 HubSpot CRM 中物件之間的關係。兩種類型之間可能存在的所有不同關聯都列在 association_definitions 中。在上面的範例中，要知道一個廣告系列連結到的所有表單，你需要查詢 ASSOCIATIONS_CAMPAIGNS_TO_FORMS：

我如何知道 HubSpot-Snowflake 整合的狀態？

若要接收 HubSpot-Snowflake 整合的即時狀態更新，請查詢 data_share_status 檢視：

SELECT * FROM hubspot.public.data_share_status
LIMIT 1

結果將顯示為：

此檢視以相反的時間順序提供整合狀態的更新。將此查詢結果整合至儀表板，將有助於您追蹤整合的效能，並偵測任何停機時間或中斷情況。