在 Snowflake 中查詢 HubSpot 資料

將 Snowflake 連接至您的 HubSpot 帳戶後，即可透過 Snowflake 的 Data Share 功能，從您的 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 資料安全檢視的即時架構。此架構中的資料每 15 分鐘更新一次。若查詢大量資料，查詢速度可能較慢。此時建議先透過ETL 流程將資料複製至自有資料表，再對該資料表進行查詢。
     
     
     
     

完成環境設定後，您即可直接查詢 HubSpot 資料，無需手動在查詢中添加資料庫或資料模式類型。

若需手動將資料庫與資料模式類型加入查詢，請在FROM欄位開頭依序添加資料庫名稱及資料模式名稱。例如：

資料概覽

查詢採用 SQL 構建，可用於檢索 HubSpot 數據，例如：

• 物件：標準 與自訂物件，例如聯絡人、公司、產品。 
• 記錄：個別 記錄，例如單一聯絡人或公司。 
• 關聯：所有可用關聯類型及當前關聯記錄。
• 事件：包含標準 HubSpot 事件（如郵件開啟）與自訂行為事件的相關數據。
• 區段：HubSpot聯絡人與公司區段。
• 擁有者：HubSpot 帳戶中的使用者，與其被設定為擁有者的記錄相關聯。
• 銷售管道：交易與工單管道，包含各階段狀態。
• 屬性：CRM 記錄上的屬性及其值，包含屬性歷史記錄。

深入了解 HubSpot 的 CRM 資料模型。 

請注意： 隨著資料共享機制持續演進，我們將進行變更（部分可能為破壞性變更）。強烈建議訂閱HubSpot 開發者變更日誌。所有更新前，相關變更皆會預先公告於變更日誌。

限制與排序回傳資料

在構建查詢時，可透過加入LIMIT和ORDER BY控制返回資料量及排序方式。例如：若需查詢所有聯絡人及其屬性，但僅返回三筆結果並依聯絡人 ID 排序，可採用以下查詢語法：

欄位資料類型

在 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 的需求。由於無需知曉物件類型識別碼，此類視圖更易於查詢，且以更實用的格式回傳資料——每行對應一個物件及其當前版本的屬性。 只要未出現查詢緩慢的情況，建議使用這些檢視表擷取物件資料。詳情請參閱下文的 Objects_X 檢視表說明。 
• 合併模式：所有物件 類型的記錄皆 整合至單一資料庫物件。因此查詢時需指定篩選條件，例如「WHERE objectTypeId='0-1'」。查詢時可使用下列物件類型識別碼：

自訂物件於建立時將被指派 ID，並以2-unique_ID 格式識別（例如2-12345）。深入瞭解物件與事件類型定義。

若需優於 objects_x 檢視的查詢效能，此類資料庫物件通常較為適用。詳情請參閱下文的 object_with_object_properties、object_properties、objects 及 object_properties_history。

objects_x 檢視

在此資料庫物件中，每個可用物件類型（包含自訂物件）皆擁有專屬檢視，各檢視僅包含該特定物件類型的記錄。 這些檢視表的命名格式為 OBJECTS_<物件類型名稱>，其中 <物件類型名稱>為該物件類型的複數形式標籤。（請注意，若物件類型無複數形式，則 <物件類型名稱> 將直接採用該物件類型的單數形式標籤後附加「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 交易金額

FROM 物件交易表

LIMIT 3

返回：

範例 3

檢索金額排名前三的交易名稱與階段。

查詢：

由於交易金額資料以數值資料型別儲存，可直接用於排序，無需轉換或型別轉換。


SELECT objectId, property_dealName, property_dealStage,
property_amount AS 交易金額

FROM 物件交易表
ORDER BY 物業成交金額 DESC
LIMIT 3




返回：

物件_含物件屬性

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

此檢視可返回下列欄位：

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

由於此為破壞性變更，新欄位 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_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 筆值。每筆歷史屬性值會顯示為獨立資料列。屬性當前值由其歷史紀錄中最新的 UPDATEDAT時間戳記決定。

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

範例 1

檢索單一聯絡人中單一屬性的歷史記錄。

查詢語句：

 
返回結果：

範例 2

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

查詢：

返回：

關聯查詢

與物件資料類似，關聯資料以兩種方式組織：

• 個別：每種關聯類型皆儲存於專屬檢視表中，僅包含該類型的記錄。例如 associations_contacts_to_deals 僅存放聯絡人與交易物件間的關聯記錄。此設計免除查詢時指定關聯類型ID的需求。這些檢視表的查詢操作相當簡易，使用者無需熟悉 CRM 資料模型即可運用。 它們還會返回兩個物件間關聯記錄的豐富描述，可輕鬆用於與 objects_x 檢視表進行聯結。只要未遇到查詢速度緩慢的情況，建議使用這些檢視表擷取關聯資料。詳情請參閱下文 associations_x_to_y 檢視表說明。 
• 合併型：所有關聯類型的記錄整合為單一資料庫物件。因此查詢時需指定篩選條件如「WHERE combinedassociationtypeid='0-32'」。詳見下文關聯表與關聯定義表說明。

關聯_x_to_y 檢視

每種關聯類型皆擁有專屬檢視，標記為 associations_<關聯類型名稱>（例如 associations_contacts_to_deals）。每個檢視皆為關聯資料庫物件中資料的子集，僅包含特定 combinedassociationtypeid 的記錄。

每個資料庫物件將返回以下欄位：

範例 1

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

查詢語句：

返回：

範例 2

檢索所有位於波哥大時區之聯絡人及其交易紀錄。

範例 3

檢索所有聯絡人及其各自成交交易金額的總和。

返回：

關聯定義

此資料庫物件包含所有可用於 HubSpot 物件間關聯類型的資訊，每種關聯類型佔一行。可將此資料庫物件與關聯資料庫物件進行關聯查詢，以取得記錄特定的關聯資訊。

相同物件間可能存在多種關聯類型。主要關聯類型（即記錄關聯時於 HubSpot 顯示的類型）需使用ISMAINASSOCIATIONDEFINITION為true 的關聯。

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

範例 1 

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

查詢語句：

 
返回結果：

範例 2

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

查詢：

返回：

關聯

此資料庫物件包含特定記錄間關聯的資訊。每行代表一個物件與另一個物件間的關聯。即使被引用的記錄已被刪除，此資料庫物件中仍可能存在行。您可透過與物件表進行關聯查詢來確認記錄是否存在。

當帳戶資料量龐大導致 associations_x_to_y 檢視執行緩慢時，此資料庫物件可發揮效用。

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

範例 1

檢索所有關聯的交易與聯絡人。

查詢語句：

 
返回結果： 

範例 2 

檢索與特定聯絡人（聯絡人 ID：3005）相關的所有票證。

查詢：

返回結果：

範例 3 

檢索與特定聯絡人（聯絡人 ID：4464）相關的所有交易。此範例採用關聯定義表進行關聯查詢，而非硬編碼combinedAssociationTypeId。

查詢：

返回值：

擁有者查詢

所有者

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

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

範例 1 

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

查詢：

 
返回值： 

範例 2

透過擁有者 ID 檢索特定擁有者。

查詢：

返回： 

範例 3

檢索所有聯絡人及其擁有者。此查詢透過object_with_object_properties物件與聯絡人屬性hubspot_owner_id 將記錄與擁有者進行關聯。

返回： 

團隊查詢

團隊

HubSpot 中的團隊功能可讓您將使用者分組，以利組織管理與報表分析。您可建立主團隊及額外團隊。

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

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

範例 1

檢索所有團隊。

查詢語句：

返回結果：

owner_team

此資料庫物件包含 HubSpot 帳戶中各擁有者對應球隊的資訊。 

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

範例 1

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

查詢語句：

SELECT teamId, ownerId, isTeamPrimary
FROM 團隊
WHERE teamId = 29584574

返回

管道查詢

以下將說明如何查詢 交易與工單流程資料，例如您帳戶中可用的管道及其階段。

您可將此資料與物件資料進行關聯，以完整檢視您的流程。例如，透過將 objects_deals_view 或object_with_object_properties資料與流程資料關聯，即可查詢交易及其對應流程。

pipelines

此資料庫物件包含帳戶中交易與工單管道的相關資訊。每筆回傳資料行代表單一管道。

此表格可返回下列欄位：

範例 1

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

查詢語句：

 
返回結果： 

範例 2

檢索特定管道內的交易。結合object_with_object_properties與pipelines 物件。

查詢：

返回： 

範例 3

與上述範例 2 相同，擷取特定銷售管道內的交易。但請注意，在執行聯結時應使用object_with_object_properties 而非 objects_deals。

返回值： 

pipeline_stages

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

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

範例 1 

檢索名為「銷售管道」的交易管道所屬各階段。

查詢語句：

 
返回結果： 

範例 2

檢索所有交易及其對應的管道階段。

查詢：

返回： 

屬性查詢

property_definitions

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

您可透過組合物件類型 ID 與名稱來識別特定屬性。您亦可使用屬性 API 檢索類似資訊。

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

範例 1

檢索所有聯絡人屬性的名稱與描述。

查詢語句：

 
返回：

事件查詢

事件包含標準 HubSpot 事件與自訂行為事件。每個事件皆擁有專屬資料庫物件，命名為events_eventname。

可透過組合事件類型ID與ID來識別事件。返回的欄位取決於事件類型，每個事件屬性對應一欄位。所有事件皆會返回下列欄位：

範例 1

檢索最近 50 筆電子郵件點擊事件。

查詢語句：

返回結果： 

範例 2

檢索所有曾點擊郵件的聯絡人相關點擊事件。此查詢透過與object_with_object_properties 物件進行關聯查詢取得聯絡人資料。

查詢：

返回： 

物件與事件類型查詢

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 的標籤欄位（甚至描述欄位）使用 LIKE 運算子進行模糊搜尋。

確定欄位所屬物件類型後，查詢方式便顯而易見：直接搜尋對應該類型的 object_x 檢視表即可。

如何將此資料類型連結至另一類型？例如：如何擷取活動表單？

若需將兩個或多個不同記錄相互關聯，請參閱關聯性（associations）。關聯性代表 HubSpot CRM 中物件間的關係，所有可能的關聯類型皆列於 association_definitions。以上例而言，若要查詢某活動關聯的所有表單，需查詢 ASSOCIATIONS_CAMPAIGNS_TO_FORMS：

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

若需即時獲取 HubSpot-Snowflake 整合的停機與問題狀態更新，請查詢 data_share_status 檢視表：

SELECT * FROM hubspot.public.data_share_status
LIMIT 1

查詢結果將顯示為：

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