在 Snowflake 中查询 HubSpot 数据

将 Snowflake 连接到您的 HubSpot 帐户后，您可以使用 Snowflake 的数据共享从您的 Snowflake 帐户查询 HubSpot 数据。

数据滞后和您的 Snowflake 帐户区域

您可以访问每 15 分钟更新一次的 V2_LIVE 模式和每天更新一次的 V2_DAILY 模式。

请注意：V2_LIVE 模式中的以下视图仅每日更新：association_definitions、owners、pipelines 和 pipeline_stages。

设置您的 Snowflake 环境

要在 Snowflake 中查询 HubSpot 数据，您需要选择要查询的仓库、数据库和模式。您可以在工作表级别进行设置，使其自动包含在查询中，也可以手动添加。

在工作表级别选择仓库、数据库和模式：

• 在工作表顶行，单击工作表操作栏。
  
  
• 在弹出的窗口中
  • 单击仓库下拉菜单，然后选择仓库。
  • 单击数据库下拉菜单，然后选择你的 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 只包含联系人记录。这样就无需在查询时指定对象类型 ID。由于不需要知道 ObjectTypeIds，所以这些视图更容易查询。它们还能以更实用的格式返回数据，每一行都对应一个对象及其属性的当前版本。只要查询速度不慢，建议使用这些视图来检索对象数据。更多信息请参阅下面的 Objects_X 视图。
• 组合视图 所有对象类型的所有记录都合并到一个数据库对象中。因此，在查询时，需要指定一个过滤器，如 "WHERE objectTypeId='0-1'"。例如，查询时可以使用以下对象类型 ID：

自定义对象在创建时将被分配一个ID，标识为2-unique_ID。例如，2-12345。了解有关object_and_event_type_definitions 的更多信息。

如果希望获得比 objects_x 视图更好的查询性能，通常适合使用这些数据库对象。更多详情，请参阅下面的 object_with_object_properties、object_properties、object 和 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 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




返回

带对象属性的对象

该数据库对象包含来自 HubSpot 记录的数据。对该数据库对象的查询会为对象类型中的每条记录返回一条记录（例如，联系人对象类型中的所有联系人记录）。所有属性都存储在 "属性"列中，这是一个Snowflake 变体/JSON列。

该视图可返回以下列：

请注意： 2022 年 1 月 25 日，object_with_object_properties表和视图中的列timestamp（表示对象记录在 HubSpot 中最后一次更新的日期和时间）将改为updatedat。这是为了与 其他数据共享中表示日期时间数据的方式保持一致 。

由于这是一项重大变更，因此已在表和视图中 添加了新列 updatedat。在接下来的 90 天内，这两个新列将与它们要取代的列 timestamp 共存。

90 天后，即 2022 年 1 月 25 日，列 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 语法。

查询

返回：

对象属性历史记录

此数据库对象包含历史属性值。对于联系人，它包含最近的 45 个值。对于所有其他对象，它包含最近的 20 个值。为每个历史属性值显示一行。属性的当前值由属性历史记录中最新的 UPDATEDAT时间戳决定。

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

例 1

检索单个联系人的单个属性的历史记录。

查询：

返回：

例 2

读取生命周期阶段为订阅者的所有联系人的当前姓名。

查询

返回

关联查询

与对象数据一样，关联数据也以两种方式组织：

• 个人：每个关联类型都存储在自己的视图中，只包含该类型的记录。例如，associations_contacts_to_deals 只包含联系人和交易对象之间的关联记录。这样就不需要在查询时指定关联类型 ID。这些视图不需要熟悉客户关系管理数据模型就能使用，因此查询起来非常简单。这些视图还能返回两个对象之间关联记录的丰富描述，在与 objects_x 视图连接时可以轻松使用。只要查询速度不慢，建议使用这些视图来检索关联数据。更多信息请参阅下面的关联_x_to_y 视图。
• 合并：所有关联类型的所有记录都合并到一个数据库对象中。因此，在查询时，需要指定一个过滤器，如 "WHERE combinedassociationtypeid='0-32'"。请参阅下文关联和关联定义中的更多内容。

关联_x_to_y视图

每种关联类型都有自己的视图，标记为 associations_<关联类型名称>，例如 associations_contacts_to_deals。每个视图都是关联数据库对象中数据的子集，只包含特定组合关联类型 ID 的记录。

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

例 1

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

查询：

返回：

示例 2

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

示例 3

检索所有联系人和每个联系人的成交总额。

返回 返回 返回 返回值值值值

关联定义

此数据库对象包含 HubSpot 对象之间所有可用关联类型的信息，每个关联类型有一行。您可以将此数据库对象与关联数据库对象连接起来，以获取特定记录的关联信息。

同一对象之间可以有多种关联类型。主要关联类型是记录关联时在 HubSpot 中显示的类型，使用关联，其中ISMAINASSOCIATIONDEFINITION 是true 。

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

例 1

检索账户中可用的关联类型。

查询：

返回：

示例 2

读取交易和公司之间可用的关联类型。

查询

返回

关联

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

如果账户数据量较大，导致关联_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 teamId, ownerId, isTeamPrimary
FROM teams
WHERE teamId = 29584574

返回

管道查询

下面将介绍如何查询 交易和票据管道数据，例如账户中的可用管道及其阶段。

您可以将这些数据与对象数据结合起来，以全面了解您的管道。例如，通过将 objects_deals_view 或object_with_object_properties数据与管道数据连接起来，可以查询交易及其管道。

管道

此数据库对象包含有关您账户中交易和票据管道的信息。返回的每一行代表一条管道。

此表可返回以下列：

示例 1

检索所有可用的交易管道，按其在 HubSpot 中的顺序排列。

查询：

返回：

示例 2

检索特定管道中的交易。连接object_with_object_properties和管道。

查询：

返回：

例 3

与上述例 2 相同，检索特定管道内的交易。但是，在连接中不要使用 objects_deals，而要使用 object_with_object_properties。

返回 返回 返回 返回值值值值

管道阶段

该视图包含管道内各个阶段的信息。通过组合对象类型 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 连接获取联系人数据。

查询：

返回 返回 返回 返回值值值值

对象和事件类型查询

对象和事件类型定义

该数据库对象包含 HubSpot 账户中所有对象和事件的定义。

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

例 1

检索 HubSpot 账户中的所有对象和事件。

查询：

返回：

分段查询

下面，我们将学习如何查询细分信息，如可用的联系人和公司细分信息，以及这些细分信息中包含的记录。

细分市场

该数据库对象包含有关 HubSpot 联系人和公司细分市场的信息。每个分段返回一行，列包括分段详细信息，如分段名称、大小和所含记录的对象类型。不包含每个分段中单条记录的信息（参见segment_membership）。

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

例 1

检索所有联系人段。

查询：

返回：

例 2

检索名为 "顶级订阅者 2021"的特定联系人段。

查询

返回：

segment_membership

此数据库对象包含哪些记录是哪些分段的成员的信息，分段中每条记录一条记录。例如，如果您有一个包含 500 个联系人的博客订阅者分段，数据库对象将返回 500 条记录。

只包含段 ID 和对象 ID。有关更多信息，请加入段数据库对象。

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

示例 1

检索Top subscribers 2021分部中所有联系人的 ID。

查询：

返回：

示例 2

读取 "2021 年最热门订阅者"分段中联系人姓名的详细信息。

查询

返回

常见问题

我想生成与 Hubspot 应用程序中的报告类似的数据，我该怎么做？

一般来说，你需要查看你要比较的报告中的列。如果报告的可视化不是表格形式的，将其转换为表格将显示报告包含的列。这些列通常是对象类型或事件类型的属性。下一步是找出列所对应的对象类型属性。这可以通过查询 property_definitions 来实现：

可能并不总是完全匹配，因为有些属性名称通常会在应用程序中解析为更具描述性的名称。在这种情况下，使用 LIKE 操作符对 property_definitions 中的标签（甚至是描述）列进行模糊搜索会有所帮助。

在确定列属于哪种对象类型后，就可以确定要查询的内容了。只需搜索与该类型相对应的 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

结果将显示为

该视图按时间倒序提供集成状态更新。将此查询结果整合到仪表板中，有助于跟踪集成的性能并检测任何停机或中断。