在 Snowflake 中查詢 HubSpot 資料

將 Snowflake 連接到您的 HubSpot 帳戶後，您即可透過 Snowflake 的「資料共享」功能，從您的 Snowflake 帳戶查詢 HubSpot 資料。

資料滯後與您的 Snowflake 帳戶區域

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

請注意：V2_LIVE 資料結構中的以下檢視僅每日更新：association_definitions、owners、pipelines 以及 pipeline_stages。

設定您的 Snowflake 環境

若要在 Snowflake 中查詢您的 HubSpot 資料，您需要選擇要進行查詢的資料倉儲、資料庫和模式。您可以選擇在工作表層級設定這些項目，使其自動包含在查詢中，或者手動新增。

若要在工作表層級選取資料倉儲、資料庫和模式：

1. 在工作表的頂部行中，點擊「工作表動作列」。
   
   
2. 在彈出視窗中：
   • 點擊「資料倉儲」下拉式選單，然後選擇您的資料倉儲。
   • 點擊「資料庫」下拉式選單，然後選擇您的 HubSpot 資料庫。
   • 點擊「模式」下拉式選單，然後選擇您要使用的模式：
     • V2_DAILY： 基於資料表查詢資料的 每日模式 。此模式中的資料每天更新一次。使用此模式可加快查詢速度，建議用於在 Snowflake 中直接查詢。
     • V2_LIVE：基於 HubSpot 資料「安全檢視 (Secure Views)」的即時模式。此模式中的資料最多每 15 分鐘更新一次。若您要查詢大量資料，查詢速度可能會較慢。在此情況下，建議先透過ETL 流程將資料複製到您自己的資料表中，再對該資料表進行查詢。
     
     
     

完成環境設定後，您即可對 HubSpot 資料進行查詢，無需手動在查詢中新增資料庫或模式類型。

若要手動將資料庫和模式類型新增至查詢，請在FROM欄位的開頭輸入資料庫名稱，並接上模式名稱。例如：

資料概覽

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

• 物件：標準 與自訂物件，例如聯絡人、公司、產品。 
• 記錄：個別 記錄，例如單一聯絡人或公司。 
• 關聯：所有可用的關聯類型以及目前已關聯的記錄。
• 事件：包含標準 HubSpot 事件（例如電子郵件開啟）以及自訂行為事件的相關資料。
• 分段：HubSpot聯絡人與公司分段。
• 擁有者：您 HubSpot 帳戶中的使用者，與其被設定為擁有者的記錄相關。
• 銷售管道：交易與服務單管道，包含管道階段。
• 屬性：CRM 記錄中的屬性及其值，包含屬性歷史紀錄。

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

請注意： 隨著我們持續優化資料共享機制，將會引入各項變更，其中部分可能屬於破壞性變更。強烈建議您訂閱HubSpot 的開發者變更日誌。這些變更將在任何更新實施前，預先發佈於變更日誌中。

限制與排序回傳資料

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

欄位資料類型

在 Snowflake 中查詢資料時，回傳的欄位取決於您所查詢的資料。您可透過 在左側邊欄選取視圖，查看 Snowflake 中特定資料表或視圖的可用欄位 。

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

若要擷取特定欄位，請將該欄位納入查詢的SELECT欄位中。例如，以下查詢將僅擷取OBJECTID和UpdatedAt這兩欄


以下是查詢可能回傳的欄位的一般定義。若需了解未列於下方的其他定義，請參閱上文所述文章中的個別檢視定義。

• 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 物件和記錄中查詢資料，以及查詢結果所包含的資料。 

物件資料可於共用資料庫物件中取得。請透過objectTypeId進行篩選 來擷取特定物件類型。使用 OBJECT_WITH_OBJECT_PROPERTIES 來取得每筆記錄的當前屬性。屬性值儲存於 properties JSON 欄位中儲存。若需取得 OBJECTS 來處理核心記錄屬性，並使用 OBJECT_PROPERTIES 或 OBJECT_PROPERTIES_HISTORY 用於各屬性資料。

自訂物件將在建立時被指派一個 ID，並以2-unique_ID 的形式標示。例如：2-12345。進一步了解object_and_event_type_definitions。

請參閱下文的 object_with_object_properties、object_properties、objects 以及 object_properties_history 以獲取更多詳細資訊。

object_with_object_properties

此資料庫物件包含來自您的 HubSpot 記錄的資料。對此資料庫物件的查詢會針對物件類型中的每筆記錄返回一行（例如，聯絡人物件類型中的所有聯絡人記錄）。所有屬性皆儲存於「屬性」欄位中，該欄位為Snowflake Variant / JSON欄位。 

此檢視可回傳以下欄位：

請注意： 自 2022 年 1 月 25 日起，object_with_object_properties資料表及檢視中的「timestamp」欄位（代表物件記錄在 HubSpot 中最後一次更新的日期與時間）將更名為「updatedat」。此變更旨在與 其餘資料共享中日期時間資料的表示方式。

由於此為破壞性變更，新欄位「 updatedat」已新增至該資料表及檢視中。在接下來的 90 天內，這兩個新欄位將與「timestamp」（即它們將取代的欄位）並存。這讓您有機會轉而使用新欄位。

90 天後，即 2022 年 1 月 25 日，「timestamp」欄位將從資料表和檢視中移除。此日期之後，任何嘗試使用「timestamp」欄位查詢object_with_object_properties資料表或檢視的操作都將失敗。

範例 1

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

查詢：

回傳結果：

範例 2

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

查詢：

回傳：

物件

此資料庫物件是 object_with_object_properties 資料庫物件中資料的子集。每筆回傳的資料列皆會回傳各筆記錄的物件 ID。此資料庫物件僅回傳現有記錄。當 HubSpot 中刪除某筆記錄時，該記錄將從此資料庫物件中移除。此資料庫物件是 object_with_object_properties 資料表中資料的子集。 每筆回傳的資料行皆會回傳各筆記錄的物件 ID。此檢視僅回傳現有記錄。當 HubSpot 中刪除某筆記錄時，該記錄將從此檢視中移除。 

此資料庫物件包含不含屬性值的當前物件資料。當您僅需核心記錄屬性時，請使用此物件；若需特定屬性，則可將其與 OBJECT_PROPERTIES 進行聯結。將此資料庫物件與 object_properties 資料庫物件搭配使用，即可複製 object_with_object_properties 中的資料，同時仍能控制要回傳哪些屬性。

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

範例 1

擷取所有聯絡人記錄。

查詢：

回傳結果：

object_properties

此資料庫物件是「object_with_object_properties」資料庫物件的子集。每行包含指定記錄上目前設定的屬性值。例如，一個聯絡人會針對其「名字」屬性有一行，針對其「姓氏」屬性有一行。若某筆記錄的某個屬性沒有值，該行要麼顯示為空字串，要麼根本不會出現。

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

範例 1

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

查詢：

回傳結果：

範例 2

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

查詢：

回傳結果：

object_properties_history

此資料庫物件包含屬性的歷史值。對於聯絡人，其中包含最近的 45 個值；對於所有其他物件，則包含最近的 20 個值。每項歷史屬性值會顯示一行。屬性的當前值由該屬性歷史紀錄中最新的 UPDATEDAT時間戳決定。

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

範例 1

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

查詢：

 
回傳結果：

範例 2

擷取所有曾處於「訂閱者」生命週期階段之聯絡人的當前姓名。

查詢：

回傳結果：

關聯查詢

關聯資料可於ASSOCIATIONS 中取得。可透過combinedAssociationTypeId進行篩選，以擷取特定的關聯類型。將ASSOCIATIONS與ASSOCIATION_DEFINITIONS進行聯結，以識別相關的物件類型及關聯定義。

association_definitions

此資料庫物件包含 HubSpot 物件之間所有可用關聯類型的資訊，每種關聯類型對應一行。您可以將此資料庫物件與associations資料庫物件進行聯結，以取得特定記錄的關聯資訊。

同一組物件之間可能存在多種關聯類型。主要關聯類型（即記錄建立關聯時在 HubSpot 中顯示的類型）應使用ISMAINASSOCIATIONDEFINITION為true 的關聯。

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

範例 1 

擷取您帳戶中可用的關聯類型。

查詢：

 
回傳結果：

範例 2

檢索交易與公司之間可用的關聯類型。

查詢：

回傳結果：

關聯

此資料庫物件包含特定記錄之間關聯的相關資訊。每行代表從一個物件到另一個物件的關聯。即使被引用的記錄已被刪除，此資料庫物件中仍可能存在相關行。您可以透過與「objects」資料表進行聯結來檢查記錄是否存在。

此資料庫物件包含所有關聯類型的關聯記錄。若需取得關聯元資料，請透過combinedAssociationTypeId進行篩選，並與ASSOCIATION_DEFINITIONS進行聯結。

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

範例 1

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

查詢：

 
回傳結果： 

範例 2 

擷取與特定聯絡人（聯絡人 ID：3005）相關的所有工單。

查詢：

回傳結果：

範例 3 

擷取與特定聯絡人（聯絡人 ID：4464）相關的所有交易。此範例未將combinedAssociationTypeId 硬編碼，而是透過association_definitions 進行關聯。

查詢：

回傳結果：

所有者查詢

所有者

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

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

範例 1 

擷取所有使用者。WHERE archived = false篩選條件會從回傳的資料中移除已刪除的使用者。

查詢：

 
回傳結果： 

範例 2

根據擁有者 ID 檢索特定擁有者。

查詢：

回傳結果： 

範例 3

根據所有者 ID 檢索所有聯絡人及其所有者。此查詢透過 `object_with_object_properties` 及聯絡人屬性 `hubspot_owner_id`，將記錄與所有者進行關聯。

回傳結果： 

團隊查詢

團隊

HubSpot 中的「團隊」功能可讓您將使用者組織成群組，以滿足組織管理與報表分析的需求。您可以建立一個主要團隊以及多個額外團隊。

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

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

範例 1

擷取所有團隊。

查詢：

回傳結果：

owner_team

此資料庫物件包含您 HubSpot 帳戶中各擁有者對應至哪些團隊的相關資訊。 

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

範例 1

擷取指定團隊的所有擁有者。

查詢：

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

回傳

管線查詢

以下將介紹如何查詢 交易與工單管線資料，例如您帳戶中可用的管線及其各階段。

您可以將此資料與物件資料進行關聯，以全面掌握您的銷售管道狀況。例如，您可以透過將 `object_with_object_properties` 與pipelines` 進行關聯，來查詢商機及其銷售管道。 

銷售管道

此資料庫物件包含您帳戶中交易與工單流程的相關資訊。每筆回傳的資料列代表一個流程。

此資料表可回傳以下欄位：

範例 1

擷取所有可用的交易流程，並按其在 HubSpot 中的順序排序。

查詢：

 
回傳結果： 

範例 2

使用object_with_object_properties 擷取特定銷售管道中的交易。

回傳結果： 

pipeline_stages

此檢視包含管線內各階段的相關資訊。可透過結合物件類型 ID、管線 ID 及階段 ID 來識別各階段。與管線進行聯結時，請使用物件類型 ID 和管線 ID。

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

範例 1 

擷取名為「Sales pipeline」的交易管線中的各階段。

查詢：

 
回傳結果： 

範例 2

擷取所有交易及其對應的銷售流程階段。

查詢：

回傳結果： 

屬性查詢

property_definitions

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

您可以透過結合物件類型 ID 與名稱來識別特定屬性。您亦可使用屬性 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

擷取名為「2021 年頂級訂閱者」的特定聯絡人區段。 

查詢：

回傳結果： 

群組成員關係

此資料庫物件包含哪些記錄屬於哪些受眾群組的資訊，該受眾群組中的每筆記錄對應一行。例如，若您有一個包含 500 位聯絡人的「部落格訂閱者」受眾群組，則此資料庫物件將回傳 500 行。 

僅包含受眾群組 ID 和物件 ID。如需更多資訊，請與「受眾群組」資料庫物件進行聯結。

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

範例 1

擷取「2021 年頂尖訂閱者」分段中所有聯絡人的 ID。

查詢：

 
回傳結果： 

範例 2

擷取「2021 年頂級訂閱者」受眾群組中聯絡人的姓名及姓氏詳細資料。

查詢：

回傳結果： 

常見問題

我想產生與 HubSpot 應用程式中的報告類似的資料，該如何操作？

一般來說，您需要先檢視要比對的報告中所包含的欄位。如果報告的可視化形式並非表格，將其轉換為表格後，即可顯示報告所包含的欄位。這些欄位通常是物件類型或事件類型的屬性。下一步是釐清某個欄位對應於哪個物件類型的屬性。您可以透過查詢 property_definitions 來完成此步驟：

由於某些屬性名稱在應用程式中通常會被解析為更具描述性的名稱，因此未必總能找到完全匹配的結果。在這種情況下，可透過在 `property_definitions` 的 `label`（甚至 `description`）欄位上使用 `LIKE` 運算子進行模糊搜尋來協助解決。

確定該欄位屬於哪個物件類型後，要決定查詢內容就非常簡單了。查詢object_with_object_properties` 並根據該類型的 `objectTypeId` 進行篩選。從 `properties` JSON 欄位中讀取所需的屬性值。

如何將此資料類型與另一資料類型建立關聯？例如，如何擷取某個行銷活動的表單？

要擷取與各行銷活動相關聯的表單，請將OBJECT_WITH_OBJECT_PROPERTIES、ASSOCIATIONS 及ASSOCIATION_DEFINITIONS 進行關聯。

如何查詢 HubSpot 與 Snowflake 整合的狀態？

若要接收有關 HubSpot-Snowflake 整合服務中斷及問題的即時狀態更新，請查詢 data_share_status 檢視：

SELECT * FROM hubspot.public.data_share_status
LIMIT 1

結果將顯示如下：

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