在 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数据安全视图的实时架构。该架构数据每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 的需求。由于无需了解对象类型ID，此类视图更易于查询。它们还以更易用的格式返回数据——每行对应一个对象及其当前版本的属性。 只要查询速度正常，建议使用这些视图检索对象数据。更多详情请参阅下文的Objects_X视图。 
• 合并模式：所有 对象类型的记录合并为单一数据库对象。因此查询时需添加过滤条件如“WHERE objectTypeId='0-1'”。例如可使用以下对象类型ID进行查询：

自定义对象在创建时会被分配一个ID，格式为2-唯一ID（例如2-12345）。更多详情请参阅object_and_event_type_definitions。

若需比 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 对象ID, 房产交易名称, 房产交易阶段,
property_amount AS 交易金额

FROM 交易对象表

LIMIT 3

返回结果：

示例 3

检索金额排名前三的交易的交易名称和阶段。

查询：

由于交易金额数据以数字数据类型存储，可直接用于排序，无需进行转换或强制类型转换。


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

FROM 交易对象表
ORDER BY 房产金额 DESC
LIMIT 3




返回：

object_with_object_properties

此数据库对象包含来自您的 HubSpot 记录的数据。对该数据库对象的查询将返回对象类型内每条记录的一行（例如，联系人对象类型中的所有联系人记录）。所有属性都存储在Properties列中，该列是Snowflake 变体/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_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）。每个视图是 associations 数据库对象数据的子集，仅包含特定 combinedassociationtypeid 的记录。

每个数据库对象将返回以下列：

示例 1

检索与特定联系人关联的所有交易。

查询语句：

返回：

示例 2

检索所有位于波哥大时区的联系人及其交易记录。

示例 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

检索所有团队。

查询：

返回：

owner_team

此数据库对象包含HubSpot账户中各所有者对应团队的信息。 

该数据库对象可返回以下列：

示例 1

检索指定团队的所有所有者。

查询：

SELECT 团队ID, 所有者ID, 是否团队主要负责人
FROM teams
WHERE 团队ID = 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账户中对象属性与事件属性的信息。事件属性涵盖标准事件（如邮件打开率和点击率）及自定义行为事件属性。这些信息有助于定义 object_properties和events数据 中的值。

通过组合对象类型ID与名称可识别特定属性。您可使用properties 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年顶级订阅者”的特定联系人分段。 

查询：

返回： 

segment_membership

此数据库对象包含记录所属分段的信息，每个分段记录对应一行。例如，若"博客订阅者"分段包含500个联系人，则该数据库对象将返回500行。 

仅包含分段ID和对象ID。如需更多信息，请与分段数据库对象进行关联查询。

该数据库对象可返回以下列：

示例 1

检索"2021年顶级订阅者"细分中所有联系人的ID。

查询语句：

 
返回结果： 

示例 2

检索2021年顶级订阅者分段中联系人的姓名详情。

查询：

返回结果： 

常见问题

如何生成类似Hubspot应用报告的数据？

通常需参考目标报告的列结构。若报告非表格形式，可将其转换为表格以查看列信息。这些列通常对应对象类型或事件类型的属性。下一步需确定列对应的对象类型属性，可通过查询property_definitions实现：

由于某些属性名称在应用中会被解析为更具描述性的名称，因此未必能找到完全匹配项。此时可通过在property_definitions表的label（甚至description）列使用LIKE运算符进行模糊搜索。

确定列所属对象类型后，查询操作便变得简单：直接搜索对应该类型的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

查询结果将显示为：

该视图按时间倒序提供集成状态更新。将查询结果集成到仪表板中，可帮助您跟踪集成性能并检测任何停机或故障。