查詢雪花中的HubSpot數據

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

資料延遲和您的Snowflake帳戶區域

您將可以訪問每15分鐘更新一次的V2_LIVE模式，以及每天更新的V2_DAILY模式。

設定您的雪花環境

若要查詢雪花中的HubSpot資料，您需要選擇要查詢的倉庫、資料庫和架構。您可以將它們設定在工作表層級，以自動將它們包含在查詢中，也可以手動新增它們。

要在工作表層級選擇倉庫、資料庫和架構：

• 在工作表頂行中，按一下工作表操作列。
  
  
• 在彈出窗口中：
  • 按一下倉庫下拉選單，然後選擇你的倉庫。
  • 按一下資料庫下拉菜單，然後選擇您的HubSpot資料庫。
  • 單擊架構下拉菜單，然後選擇要使用的架構：
    • V2_DAILY ：根據表格查詢資料的每日模式。此架構中的數據每天更新一次。使用此架構將導致更快的查詢，並建議直接在Snowflake中查詢。
    • V2_LIVE ：基於Secure Views of HubSpot數據的實時架構。此架構中的資料最多每15分鐘更新一次。如果你正在查詢大量資料，查詢速度可能會很慢。在這種情況下，建議首先使用ETL流程將資料複製到自己的表格中，然後查詢該表格。
    
    
    

配置環境後，您可以對HubSpot資料進行查詢，而無需手動將資料庫或架構類型新增到查詢中。

若要手動將資料庫和架構類型新增至查詢，請在FROM欄位開頭新增資料庫名稱和架構。例如：

數據概述

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

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

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

請注意：隨著資料分享的發展，我們會推出變更，其中一些會打破變更。強烈建議訂閱HubSpot的開發人員變更日誌。這些變更將在任何更新之前隨時發佈在變更日誌中。

限制和排序返回的數據

在構建查詢時，您可以控制傳回多少數據，以及傳回的順序，通過包含LIMIT和在查詢ORDER BY中。例如，若要查詢所有聯絡人及其屬性，但只傳回三個結果並按聯絡人ID排序，您可以按照以下方式建立查詢：

列數據類型

查詢雪花中的資料時，傳回的欄位取決於您要查詢的資料。您可以透過選擇左側邊欄中的檢視方式，在「雪花」中查看指定表格或檢視方式的可用欄位。

請注意：在本文檔的剩餘部分，當上下文適用於表格和檢視時，我們會使用「資料庫對象」一詞。

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


SELECTobjectId, updatedAt
FROM objects_contacts



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

• OBJECTTYPEID ：表示物件類型的VARCHAR值。例如， 0-1表示聯系人對象， 0-2表示公司對象， 2-1232可能表示您帳戶中的自訂對象。在列表中，這指的是列表類型（例如，聯絡人或公司）。您可以使用OBJECT_AND_EVENT_TYPE_DEFINIONS檢視功能查詢帳戶中的所有對象和活動ID。
• OBJECTID ：一個BIGINT數字，表示HubSpot帳戶中給定類型物件的單個記錄。若要識別記錄，您需要使用物件類型ID和物件ID。
• INGESTEDAT ：一行數據被添加到Snowflake的時間戳記，由TIMESTAMP_NTZ值表示。與HubSpot中創建或更新操作的輯理時間戳無關。
  • 由於HubSpot的內部處理，即使行上的其他列沒有更改，行吞噬時間戳也可能會被更新。
  • 當建立複製最近變更的資料的ETL流程時，建議您將查詢建立在吞噬時間戳的基礎上。此外，如果您的流程每天複製資料，建議您複製前兩天的資料，以處理延遲情況。
• CREATEDAT和UPDATEDAT ：在HubSpot中建立或更新資料時的邏輯時間戳記，由TIMESTAMP_NTZ值表示。這些時間戳將與HubSpot中的時間戳匹配。例如，記錄建立日期的HubSpot中顯示的時間將與雪花中的CREATEDAT欄中的時間戳相同。這些與吞噬時間戳沒有直接關係，不建議將ETL過程建立在這些屬性上。
• COMBINEDASSOCIATIONTYPE ：唯一標識關聯定義的VARCHAR值。

正在轉換資料類型

有時候，數據是從帶有VARCHAR欄位的查詢中返回的，當需要將它們作為不同的類型時。在這種情況下，建議使用雪花函數將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’”。例如，查詢時可以使用以下objecttypeid ：

自訂物件將在建立時分配一個ID ，並識別為2-Unique_ID。例如， 2-12345。進一步瞭解OBJECT_AND_EVENT_TYPE_DEFINION

如果您希望查詢性能高於可從object_x視圖中取得的查詢性能，這些資料庫對象通常是合適的。請參閱下面的「OBJECT_WITH_OBJECT_PROPERTYS」、「OBJECT_PROPERTYS」、「OBJ

對象_x視圖

在此資料庫物件中，每種可用的物件類型（包括自訂物件）都有自己的特定檢視，每種檢視都僅包含該特定物件類型的物件的記錄。這些視圖以format OBJECTS_<object type name>where命名，<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




退貨：

object_with_object_properties

此資料庫對象包含來自HubSpot記錄的資料。對此資料庫物件的查詢會為物件類型中的每個記錄（例如，聯絡人物件類型中的所有聯絡人記錄）傳回一行。所有屬性都存儲在屬性列中，這是Snowflake Variant/JSON列。 

此檢視可傳回以下列：

請注意：在2022年1月25日，將更新object_with_object_properties表格和檢視中的列，時間戳，代表物件記錄上次在HubSpot中更新的日期和時間。這與剩餘數據分享中日期時間數據的顯示方式相符。由

於這是一個突破性變更，新列更新 已新增至表格和檢視中。在接下來的90天內，這兩個新欄位將與時間戳共存，即他們正在尋找替換的欄位。這讓您可以切換到使用新欄位。90天

後，系統將於2022年1月25日從表格和檢視中刪除列時間戳。在此日期之後，任何查詢object_with_object_properties表格或使用時間戳列檢視的嘗試都將失敗。

示例1

檢索所有聯系人及其屬性。

查詢：

退貨：

示例 2

檢索最多三個交易的交易名稱和階段。

查詢：

退貨：

對象

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

如果您的帳戶擁有大量數據，讓OBJECT_WITH_OBJECT_PROPERTYS_OBJECT_X檢視速度緩慢，這個資料庫對象可能會非常有用。將此資料庫對象與object_properties資料庫對象一起使用，可以複製object_with_object_properties中的資料，同時保持對傳回哪些屬性的控制。

此資料庫對象可以傳回以下欄：

示例1

檢索所有聯絡人記錄。

查詢：

退貨：

object_properties

此資料庫對象是object_with_object_properties資料庫對象的子集。每行都包含給定記錄上設定的當前屬性值。例如，聯系人的姓氏屬性有一行，姓氏屬性有一行。如果記錄沒有屬性的值，行會顯示為空字串，或根本不會出現。

此資料庫對象可以傳回以下欄：

示例1

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

查詢：

退貨：

示例2

檢索所有聯絡人的名字、姓氏和生命周期階段，並將其合併成每個聯絡人的一行。此示例使用SQL Pivot語法。

查詢：

退貨：

對象_特性_歷史

此資料庫對象包含歷史屬性值。對於聯系人，它包含最新的45值。對於所有其他對象，它包含最近的20個值。顯示每個歷史屬性值的行。房源的現有價值由房源歷史中最新的UPDATEDAT時間戳決定。

此資料庫對象可以傳回以下欄：

示例1

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

查詢：

   
退貨：

示例2

檢索所有曾經有訂閱者生命周期階段的聯絡人的現有名稱。

查詢：

退貨：

關聯查詢

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

• 個人：每種關聯類型都儲存在自己的檢視中，只包含該類型的記錄。例如， ASSOCIATIONS_CONTACTS_TO_DEALS只包含聯系人和交易對象之間的聯系記錄。這無需在查詢時指定associationtypeid。這些檢視很容易查詢，因為使用它們不需要熟悉CRM資料模型。他們還會傳回兩個物件之間關聯記錄的豐富描述，這些記錄可以輕鬆用於帶有object_x視圖的連結中。只要您沒有遇到緩慢的查詢，建議您使用這些視圖來檢索關聯資料。在下方查看更多協會_x_to_y視圖。 
• 合併：所有關聯類型的所有記錄合成一個資料庫對象。因此，在查詢時，您需要指定一個篩選器，如“WHERE combinedassociationtypeid = ’0-32’”。請參閱下方關聯與協會_定義中的更多內容。

associations_x_to_y查看

每種聯盟類型都有自己的檢視，標籤為association_<association type name>e.g. association_contact_to_deal。每個檢視都是關聯資料庫對象中的資料的子集，僅包含特定連合關聯類型的記錄。

每個資料庫對象都會傳回以下欄：

示例1

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

查詢：

退貨：

示例2

檢索波哥大時區的所有聯絡人及其交易。

示例3

檢索每個聯絡人的所有聯絡人和交易結束金額總和。

退貨：

association_definition

此資料庫對象包含有關HubSpot對象之間所有可用關聯類型的資訊，每個關聯類型有一行。您可以使用關聯資料庫對象加入此資料庫對象，以獲取特定於記錄的關聯資訊。

同一對象之間可能有多種類型的關聯。主關聯類型是HubSpot在關聯記錄時顯示的類型，使用關聯ISMAINASSOCIATIONDEFINITION位置true。

此資料庫對象可以傳回以下欄：

示例1 

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

查詢：

  
退貨：

示例2

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

查詢：

退貨：

聯想；聯想

此資料庫對象包含有關特定記錄之間關聯的資訊。每行表示從一個物件到另一個物件的關聯。儘管引用的記錄已被刪除，但此資料庫對象中仍可能存在行。您可以透過與物件表結合來檢查記錄是否存在。

如果您的帳戶擁有大量數據，讓關聯_x_to_y視圖表表現緩慢，這個資料庫對象可能會非常有用。

此資料庫對象可以傳回以下欄：

示例1

檢索所有關聯的交易和聯系人。

查詢：

  
退貨： 

示例2 

檢索與特定聯絡人關聯的所有工單（聯絡人ID ： 3005 ）。

查詢：

退貨：

示例3 

檢索與特定聯絡人相關的所有交易（聯絡人ID ： 4464 ）。此示例使用 association_definition聯接，而不是硬編碼combAssociationTypeId。

查詢：

退貨：

所有者查詢

所有者

此資料庫對象包含有關您HubSpot帳戶中用戶的資訊。用戶可以設定為記錄擁有者，並且會根據其上下文包含用戶ID和擁有者ID。兩個ID可用於識別同一用戶。 

此資料庫對象可以傳回以下欄：

示例1 

檢索所有用戶。WHERE archived = false 過濾器從返回的數據中刪除已刪除的用戶。

查詢：

  
退貨： 

示例2

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

查詢：

退貨： 

示例3

檢索所有聯系人及其所有者。此查詢將紀錄連結到擁有object_with_object_properties和contact屬性hubspot_owner_id的所有者。

退貨： 

管道查詢

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

您可以使用對象數據加入此數據，以全面查看管道。例如，您可以使用管道數據連結object_deals_view或object_with_object_properties數據，以查詢交易及其管道。

管道

此資料庫對象包含有關您帳戶中的交易和工單管道的資訊。返回的每一行表示單一的管道。

此表可以返回以下列:

示例1

在HubSpot中尋找所有按其訂單排序的可用交易管道。

查詢：

  
退貨： 

示例2

在特定管道中檢索交易。聯接object_with_object_properties和管道。

查詢：

退貨： 

示例3

與上面的示例2相同，在特定管道內檢索交易。但是，與其在聯接中使用objects_deals ，不如使用object_with_object_properties。

退貨： 

pIPLINE_STAGES

此檢視包含管道內個別階段的資訊。透過組合物件類型ID、管道ID和階段ID來識別階段。使用管道時，使用對象類型ID和管道ID。

此資料庫對象可以傳回以下欄：

示例1 

檢索名為銷售管道的交易管道的管道階段。

查詢：

  
退貨： 

示例2

檢索所有交易及其各自的管道階段。

查詢：

退貨： 

旅居查詢

特性定義

此資料庫物件包含有關HubSpot帳戶中物件屬性和事件屬性的資訊。事件屬性包括標準事件（如電子郵件開啟和點擊）和自訂行為事件屬性。此資訊可能對於在object_properties和事件資料中找到的值有幫助。

您可以透過組合對象類型ID和名稱來識別特定屬性。您可以使用屬性API檢索類似資訊。

此資料庫對象可以傳回以下欄：

示例1

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

查詢：

   
退貨：

事件查詢

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

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

示例1

檢索最新的50個電子郵件點擊事件。

查詢：

退貨： 

示例2

檢索按一下聯絡人的所有電子郵件點擊事件。此查詢透過與object_with_object_properties結合來獲取聯絡資料。

查詢：

退貨： 

Object_and_event_type_queries

object_and_event_type_definition

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

此資料庫對象可以傳回以下欄：

示例1

檢索HubSpot帳戶中可用的所有物件和事件。

查詢：

  
退貨： 

列出查詢

在下方，瞭解如何查詢清單，例如可用的聯絡人和公司清單以及這些清單中包含的記錄。

列表

此資料庫對象包含有關HubSpot聯絡人和公司清單的資訊。傳回每個清單一行，列包含清單細節，如清單名稱、大小及其所含記錄的對象類型。不包含每個清單中個別記錄的資訊（請參閱LIST_MEMBILITION ）。 

此資料庫對象可以傳回以下欄：

示例1

檢索所有聯系人列表。

查詢：

  
退貨： 

示例2

檢索名爲Top subscribers 2021的特定聯系人列表。 

查詢：

退貨： 

list_membership

此資料庫對象包含有關哪些記錄是哪些清單成員的資訊，每項記錄在清單中有一行。例如，如果您有包含500個聯絡人的網誌訂閱者清單，則資料庫對象將傳回500行。 

僅包含列表ID和對象ID。如需更多資訊，請加入清單資料庫對象。

此資料庫對象可以傳回以下欄：

示例1

檢索「頂尖訂閱者2021」清單中所有聯絡人的ID。

查詢：

  
退貨： 

示例2

在2021年頂級訂閱者清單中檢索聯絡人詳細資料的名字和姓氏。

查詢：

退貨： 

常見問題

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

一般而言，您需要查看與之相比較的報告中的欄位。如果報告可視化不是表格，轉換成表格時將會顯示報告所包含的欄位。這些欄通常是對象類型或事件類型的屬性。下一步是找出列對應的對象類型屬性。您可以查詢PROPERTY_DEFINIONS ：

由於某些旅居名稱通常會在應用程式上解析為更具描述性的名稱，因此可能不總是完全匹配。在這種情況下，使用PROPERTY_DEFINITIONS中的標籤（甚至說明）欄中的類似數字來進行模糊搜尋可能會有所幫助。

在確定欄位屬於哪種物件類型後，決定要查詢甚麼是微不足道的。您可以輕鬆搜尋對應類型的object_x視圖。

如何將此資料類型連結到另一個資料類型，例如如何檢索活動的表單？

如果您需要將兩個或多個不同的記錄連結到對方，請查看關聯。關聯代表HubSpot CRM中物件之間的關係。兩種類型之間可能的所有不同關聯都列於association_definition中。在上面的示例中，要了解所有與廣告活動相關的表格，您需要查詢ASSOCIATIONS_CAMPATS_TO_FORMS ：

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

若要接收有關HubSpot-Snowflake整合的中斷和問題的實時狀態更新，請查詢DATA_SHARE_STATUS視圖：

SELECT * FROM hubspot.public.data_share_status
LIMIT 1

結果將顯示為：

此視圖按逆向時間順序提供有關整合狀態的更新。將此查詢的結果整合到控制面板中，將有助於您跟蹤整合的性能，並檢測任何停機或中斷。