在 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 中查询数据时，返回的列取决于您查询的数据。您可以在 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 记录的数据。对该数据库对象的查询会针对对象类型中的每条记录返回一行（例如，联系人对象类型中的所有联系人记录）。所有属性都存储在“Properties”列中，该列为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

检索交易与公司之间可用的关联类型。

查询：

返回结果：

关联

此数据库对象包含特定记录之间关联的信息。每一行代表从一个对象到另一个对象的关联。即使被引用的记录已被删除，此数据库对象中仍可能存在相关行。您可以通过与对象表进行连接来检查记录是否存在。

该数据库对象包含所有关联类型的关联记录。当您需要关联元数据时，请按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_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_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

结果将显示如下：

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