使用 Snowflake 进行数据建模——通过Snowflake对象讲述建模

数据智能老司机
183 阅读39分钟

在其最纯粹的形式中，关系建模（规范化表格并严格执行物理约束）最常见于在线事务处理（OLTP）数据库中。事务数据库存储最新的（即时的）业务信息版本，不同于数据仓库，后者存储历史快照并跟踪信息随时间的变化，从而允许跨时间维度进行额外的（当前时刻的）分析。

然而，这并不意味着关系建模的概念不适用于在线分析处理（OLAP）数据库——恰恰相反。数据仓库不仅复制事务系统中的现有实体和关系，还需要管理额外的任务，包括从其他来源对维度进行整合，并将它们在下游的转换和分析中结合起来。

另一个学习建模通用语言的原因是上一章中提到的混合单体表（Hybrid Unistore tables），它们有望模糊事务系统和数据仓库之间的界限，并解锁在其他架构中从未实现过的用例（例如，能够实时、无需ELT的分析访问同一平台上的事务数据，该平台既作为操作系统，又作为数据仓库）。

在本章中，我们将涵盖以下主要主题：

  • 通过理解如何将实体表示为表格以及Snowflake如何管理底层存储，确保高效的设计。
  • 探索为属性选择合适数据类型的好处。
  • 理解数据库约束并在Snowflake中管理它们。
  • 学习如何利用标识符和主键的优势。
  • 当存在多个主键候选项时，使用备用键。
  • 通过外键关系将模型连接起来。
  • 指定并检查必填列。

让我们首先回顾一下上一章中涉及的基础建模概念，并将其与对应的Snowflake对象联系起来。

实体作为表格

在深入了解数据库细节之前，让我们回顾一下业务层面上“实体”的概念：一个与业务相关的个人、物体、地点、事件或概念，是组织希望维护其信息的内容。换句话说，实体是一个具有共同属性的与业务相关的概念。识别和命名实体的一条经验法则是，它们通常符合单数形式的英语名词，例如：客户、物品和预订。

在Snowflake中，存储和维护信息的显而易见的候选对象是表格。通过SQL，表格为用户提供了一种标准和熟悉的方式来访问和操作实体细节。正如我们在上一章所看到的，Snowflake表格有多种类型，提供不同的备份和恢复选项。除了选择提供适当时间旅行（Time Travel）和故障保护（Fail-safe）的表类型外，Snowflake表格也符合公司关于接近零维护的声明——没有需要手动维护的索引、表空间或分区。

用户唯一能够控制的存储方面是Snowflake用于以内部列式格式存储数据的微分区（micro-partitions）以及如何对其进行聚簇。让我们更详细地看一下这两个方面。

Snowflake如何存储数据

Snowflake表中的数据以列式格式存储，使用名为微分区（micro-partitions）的连续存储单元。通常，数据库会将大型表划分为物理分区，以提高性能和可扩展性。然而，物理分区需要维护，并且会导致数据倾斜（某些分区会比其他分区增长得更大）。

Snowflake通过保持微分区小（大约50-500 MB未压缩）并通过服务层透明地管理它们，突破了这些限制。微分区是不可变的，因此数据操作语言（DML）操作更加高效，并且总是会创建新的分区。由于微分区的大小较小，可以为其中的单个列保留详细统计信息，并且每个微分区会根据其数据类型进行最佳压缩。与传统的分区方式不同，微分区可以在其值范围上重叠（但单个记录永远不会跨多个分区）。

微分区是通过数据加载到表格中的自然顺序创建的。例如，如果新的交易每天加载到一个名为SALES的表格中，则transaction_date会形成该表的自然分区。另一方面，如果表格按客户进行排序，则会形成按客户划分的分区，而transaction_date值会重叠。

微分区的主要好处是其元数据（如值范围、不同计数等）可以让Snowflake优化器修剪用户查询。修剪可以避免搜索那些已知不包含过滤值的微分区。例如，即使前面提到的SALES表增长到数百万条记录并跨越数千个微分区，通过修剪不必要的分区，只扫描那些值范围与查询过滤条件匹配的分区，查询结果仍然可以在毫秒级别返回。

通过一个简化的概念示例，让我们来看看微分区的实际运作。以下表格包含了关于超级英雄及其创作者的数据。这是用户看到的数据：

image.png

在内部，创建了两个微分区，并使用列式格式对数据进行压缩。

image.png

由于该表按“首登日期”（Date Debuted）排序，当服务层将表拆分为微分区时，该列将自然地形成值的聚类。虽然这个示例只使用了两个微分区，但根据表的大小，Snowflake表可以由数百万个微分区组成。

通过使用分区元数据（其中部分在图5.2中显示，例如列值范围），Snowflake的查询优化器可以根据查询过滤条件修剪或忽略分区。

在下面的示例中，优化器可以安全地跳过读取分区1，因为其值范围（从元数据中已知）与过滤条件不匹配。

image.png

如果优化器无法根据值范围排除任何微分区，那么所有的分区及其内容都需要被读取，以找到匹配的记录。

在以下示例中，由于两个分区的值范围都与搜索条件匹配，因此无法进行查询修剪，即使创作者Paul Gustavson只存在于分区2中：

image.png

Snowflake如何管理微分区取决于数据加载到表中的顺序。数据可以在插入时显式地使用ORDER BY子句进行排序，或者通过自然加载模式（例如，通过CREATED_DATE或LOAD_DATE）隐式排序。当分区与搜索过滤器匹配时，通过修剪可以提高查询性能。例如，在一个具有数百万行的表中，如果数据按LOAD_DATE加载并查询LOAD_DATE = CURRENT_DATE，则查询性能将与仅包含CURRENT_DATE数据的表相同。然而，忽略此过滤条件或搜索任何其他属性可能不会从查询修剪中受益。

如前面的示例所示，微分区创建了多个数据段，列值的重叠程度各不相同。这种数据分割被称为聚类，它可以用于战略性地组织表结构并提高查询性能。让我们了解如何利用它。

聚类

随着表格数据被拆分为微分区，列值自然会将自己分组到数据段中。列值的变化程度及其在微分区之间的重叠程度称为聚类。分区之间的重叠越少，优化器能够提供的查询和列修剪的程度就越高。

除了微分区的列元数据外，Snowflake服务层还存储了聚类统计信息，例如：

  • 每个表中微分区的总数
  • 包含互相重叠值的微分区数量（在指定的表列子集内）
  • 重叠微分区的深度

以下是一个简化的示例，展示了三个微分区如何在它们的聚类范围内重叠。实际的表可能有更多的分区和列，并且实现完美的分离既不太可能，也不是提高查询性能的必要条件。

image.png

在考虑如何从表中加载和查询数据时，聚类深度或重叠程度（重叠越少越有利于修剪和性能）应该被考虑，尤其是当表的规模达到相当大时。尽管通过在一个或多个维度上对表进行排序可能通过修剪提高查询性能，但排序操作的成本可能会抵消这些收益。

通常，即使是具有几百万行的表，如果没有对行进行任何排序或排序操作，也能提供足够的性能。然而，如果加载模式与查询模式不匹配（例如，数据按TRANSACTION_DATE加载，但按CLIENT查询），查询性能可能会在表达到一定大小后下降。

用户可以使用Snowflake的可视化查询分析器评估查询修剪信息。屏幕上会显示微分区的总数，以及查询期间访问的分区数量。

以下截图显示，扫描的分区数量等于总数——这意味着优化器无法执行任何修剪操作。

image.png

如前所述，缺乏修剪本身并不是查询性能差的指标。查询时长是主观的，因为它受缓存、查询复杂性和数据量等因素的影响。然而，当查询性能未达到预期时，可能会出现高表扫描百分比（如图5.6所示），与其他查询操作相比，并且缺乏修剪，这可能表明需要重新聚类表。

重新聚类可以通过手动重新创建表并应用所需的排序来执行，也可以由Snowflake自动处理。

自动聚类

Snowflake通过传入数据的自然加载模式自动处理聚类。然而，在大型表（多TB及以上）上，DML操作可能会降低所需列的聚类质量，从而影响查询性能。

由于表的大小，手动重新排序表会非常昂贵，因此Snowflake提供了通过指定的聚类键自动重新聚类表的选项。聚类键是Snowflake用来自动对给定表中的数据进行排序，并在必要时透明地重新排序的数据列（或表达式）。

定义聚类键的好处包括：

  • 通过修剪提高查询性能，跳过与查询过滤器不匹配的数据
  • 更好的列压缩和减少存储成本
  • Snowflake执行的免维护自动重新聚类

然而，请记住，自动重新聚类操作的处理成本与手动操作相同。因此，只有在性能优先于成本或聚类性能能够抵消所需的计算资源时，才应考虑设置聚类键。以下是帮助确定何时可能发生后者的考虑因素：

  • 表包含多个TB的数据和许多微分区。

  • 查询可以利用聚类。通常，这意味着以下一个或两个条件为真：

    • 查询只需要读取表中的一小部分行
    • 查询对数据进行排序（例如，使用ORDER BY子句）

  • 高百分比的查询可以通过选择或排序相同的几列来受益于相同的聚类键。

有关聚类深度的更多信息，请参阅Snowflake文档（docs.snowflake.com/en/user-gui…）。

实体作为关系模型的主要元素

在本节中，我们看到如何将实体转换为Snowflake中的物理表，并考虑了最佳性能和存储的设计因素。在下一节中，我们将通过添加属性来扩展实体的概念。

属性作为列

回顾上一节的内容，实体是一个与业务相关的概念，组织希望维护该概念的信息。属性——在概念建模过程中与业务团队共同定义，或在ETL过程中从现有源数据加载——是描述实体的属性，并作为列存储。属性可以是描述性数据（如姓名、地址、数量）或元数据（如ETL_SOURCE和LOAD_DATE）。

属性的性质——无论是数字、字符串、日期还是其他——是理解业务需求和在物理层选择正确数据类型的关键细节。Snowflake提供了在其他数据库中常见的基本数据类型（如VARCHAR、DATE和INTEGER）以及一些不太常见的数据类型（如VARIANT和GEOGRAPHY），这些数据类型为建模和处理表内容提供了令人兴奋的可能性。

让我们了解一下Snowflake的数据类型及其独特的属性，以便我们能够将它们适当地分配给实体的属性。

Snowflake数据类型

Snowflake是一个符合ANSI标准的数据库，支持常见的SQL数据类型，用于存储字符串、日期和数字。此外，Snowflake还提供了更高级的数据类型，用于存储半结构化和地理空间数据。在讨论微分区时，我们提到过列压缩（基于数据类型）是影响性能和成本管理的关键因素。然而，正确使用数据类型还可以在查询表内容时带来可用性上的好处。

在继续之前，请查看Snowflake文档中的数据类型列表（docs.snowflake.com/en/sql-refe…），除非您已经熟悉它们。

一些数据类型提供了可以在创建表时应用的属性，如排序规则（collation）和自增（autoincrement）。排序规则允许用户设置字符串比较时的选项，例如区分大小写、重音符号和空格修剪。对于数字数据类型，身份（identity）和自增（autoincrement）选项可以生成唯一的序列，这对于代理键（将在后续章节中描述）非常有用。

除了存储和压缩之外，适当的属性列数据类型还提供了从可用性角度的优势，确保数据加载过程中格式的一致性（例如，数字列中没有杂散字符，日期列中没有不正确的日期格式）。更重要的是，正确的数据类型解锁了Snowflake支持的所有相关数据类型函数，例如针对日期的DATEDIFF（计算两个日期之间的差异）和针对字符串的STRTOK（将给定字符串分割并返回请求的部分）。完整的SQL函数参考可以在Snowflake文档中找到（docs.snowflake.com/en/sql-refe…）。

在Snowflake中存储字符串

Snowflake使用VARCHAR数据类型来存储字符串值。VARCHAR列的最大长度为16 MB，或16,777,216个字符/字节。16 MB可以存储多少数据呢？比如，《战争与和平》的内容（587,287个单词）四遍。值得注意的是，16 MB的长度是默认值。但请不要担心，Snowflake会高效地压缩列内容到其实际长度，并不会按16 MB的最大值收费。然而，有时明确将列的字符数量限制为固定值是有意义的，以通知数据库用户该列的内容不应超过指定的设置（例如，将包含ISO货币代码的列设置为VARCHAR(3)）。

现在，让我们从结构化列转到半结构化数据类型。

存储半结构化数据

Snowflake因其处理半结构化数据的简便性和灵活性而广受认可。Snowflake提供了三种专门的数据类型来存储半结构化信息（VARIANT、ARRAY和OBJECT），除了每种类型具有独特的功能（稍后会讨论），它们还允许用户使用本地SQL查询其半结构化内容，而无需事先转换或扁平化数据。关于查询半结构化数据的内容将在后续章节中讨论，并在Snowflake文档中解释（docs.snowflake.com/en/user-gui…）。

Snowflake表可以由结构化数据和半结构化数据类型组成，且可以在同一个SELECT语句中本地查询这两类数据类型。然而，这三种半结构化数据类型在使用上有一些差异，因此我们需要了解它们的具体内容：

  • VARIANT：这是处理层次化半结构化数据的推荐数据类型，允许用户加载并查询JSON、Avro、ORC和Parquet数据，而无需转换或明确描述它们的结构。VARIANT的最大长度为16 MB，可以存储任何其他数据类型的值（除了VARIANT）。VARIANT数据类型的常见应用包括电子邮件日志、网页活动和事件数据。
  • OBJECT：Snowflake的OBJECT类似于其他编程语言中的JSON对象、字典、哈希或映射。一个OBJECT是一个键值对，其中键是标识值的VARCHAR文本，值本身是VARIANT类型的值。当键（名称）传达有意义的信息时（例如，按ISO货币代码列出的国家列表），就使用OBJECT。OBJECT的最大大小为16 MB，可以存储任何其他数据类型的值，包括OBJECT。
  • ARRAY：Snowflake的ARRAY类似于其他编程语言中的数组。一个数组包含零个或多个元素（长度是动态的，不会显式设置），并引用它们的位置。与通过键名访问元素的OBJECT不同，ARRAY元素通过其位置来引用。这使得ARRAY数据类型适合于按自然顺序或时间顺序循环或处理数据。ARRAY的最大长度为16 MB，可以存储任何其他数据类型的值，包括ARRAY。然而，ARRAY的位置会影响16 MB的限制，因此其有效大小较小。

我们只简单介绍了Snowflake的各种结构化和半结构化数据类型及其功能。有关Snowflake数据类型的进一步阅读，您可以参考以下文档，其中为每种数据类型提供了分节参考：docs.snowflake.com/en/sql-refe…。

在理解了用于定义属性列的数据类型后，我们可以继续学习可以在这些列上定义的各种约束。然而，在详细探讨约束之前，我们需要突出一个关于Snowflake管理约束的关键因素。

约束与强制执行

本章的其余部分将讨论表约束，因此有必要了解它们是什么，并提到几个关于它们在Snowflake中使用的重要细节。在ANSI-SQL标准中，约束定义了存储在表中的数据的完整性和一致性规则。Snowflake支持四种约束类型：

  • PRIMARY KEY
  • UNIQUE
  • FOREIGN KEY
  • NOT NULL

由于每种约束的功能将在本章后面讨论，本节将仅限于其强制执行部分。

在数据库中的强制执行意味着在对表执行DML操作时，数据库会主动监控给定约束的完整性规则。通过强制执行约束，数据库确保在违反约束时抛出错误，并且不允许违规的DML操作完成。

例如，列上的NOT NULL约束表示该列不能包含NULL值。通过强制执行此约束，Snowflake会在操作尝试插入或更新NULL值时抛出错误。

选择NOT NULL作为示例是有策略性的，因为在四个现有约束中，只有它是强制执行的。其他三个约束是指示性的，这意味着它们没有强制执行（可以违反而不会导致错误），但它们向数据库用户提供一些有价值的元数据细节（稍后将在本章中讨论）。至少，在Hybrid Unistore表被宣布之前，情况是这样的。

Hybrid Unistore表（在上一章中描述）具有完全不同的HTAP架构，允许Snowflake强制执行所有四个约束。

考虑到这一点，让我们来看一下PRIMARY KEY约束，以便我们理解这个约束在强制执行和不强制执行的情况下分别起到什么作用。

标识符作为主键

表格使用相关数据类型的属性来存储业务实体的信息。CUSTOMER表中的一行保存了某个客户的信息，ORDERS表中的一行代表一个订单——还是说一个订单包含多个产品并跨越多行？为了确定实体的唯一实例，需要使用标识符——如果是指物理数据库，则是主键（PK）。

主键（PK）是一个列或一组列，其值唯一地确定实体的一个实例。每个表只能定义一个主键。从业务角度来看，主键代表一个实体实例。回到之前的示例——一个订单是一行只包含一个产品，还是我们的组织允许每个订单包含多个产品？主键向数据库用户提供了在表级别上这一现实的样子。

以下图展示了来自虚构ORDERS表的一些示例数据。

image.png

看起来O_ORDERKEY列没有重复值，但这只是一个来自拥有数百万条记录的表的小样本。从数据中无法判断订单是否仅通过O_ORDERKEY唯一标识，或者是通过O_ORDERKEY和O_PRODKEY的组合，亦或是通过其他列的组合来唯一标识（当主键由多个列组成时，它被称为复合键或复合主键）。

image.png

即使检查表中的每一条记录并且没有找到重复，也不能保证以后不会出现重复，特别是当业务模型和相应的数据模型按照图5.8中的场景2配置时。只有通过建立概念上的标识符，并在相应的Snowflake表中物理定义它，用户才能明确知道如何在其中隔离唯一的实体实例。

在理解了主键（PK）如何用于识别唯一记录之后，让我们讨论使用主键的实际优势。

主键的好处

支持主键最有力的论据并非来自教科书，而是来自Dr. Seuss的儿童诗《Too Many Daves》。这首诗讲述了McCave夫人和她不明智的生活选择，比如生育了二十三个孩子，并且给他们都取名为Dave。由此可见，如果没有唯一标识符，McCave夫人无法隔离出唯一的Dave——这对许多数据库用户来说是一个再熟悉不过的问题（allpoetry.com/poem/115758…）。

除了隔离出你想要的Dave外，定义主键还有其他令人信服的理由。

确定粒度

一旦数据填充到表中，接下来就需要查询单个实体或汇总实体的统计信息。然而，用户如何知道多少行构成了一个单一记录呢？一个订单是存储在一行中，还是为它包含的每个产品生成一行？换句话说，最小的细节层次是什么？

主键通过定义表的粒度来回答这个问题，告诉用户它包含的数据的最低级别是什么，以及如何唯一标识实体实例。

知道表的主键允许数据库用户精确地在所需的列和数值上查询数据，从而消除歧义（例如，避免出现多个Dave）。

精确隔离唯一实体的能力也适用于对表数据进行汇总，以便计算和总结其度量（数值属性）。Snowflake提供了各种汇总函数来总结表数据——如COUNT、SUM和MAX——但它们要求用户在GROUP BY语句中指定实体的唯一标识符（或其他分组维度）。如果错误地指定（或假设）表的有效标识符，查询将返回错误的计数和汇总结果。

确保正确的连接结果

查询通常需要将来自一个表的数据与另一个表中的信息进行连接和增强。虽然有多种连接类型（如LEFT、INNER和FULL），用户往往会避免笛卡尔连接（Cartesian Join），因为它会将一个表中的行数乘以另一个表中匹配行的数量。笛卡尔连接最常见的原因是无法正确识别连接中一个或两个表中的唯一记录。

在Dr. Seuss的例子中，歧义导致了23行而不是1行。在数据库中，当查询可能涉及数百万条记录时，意外的记录爆炸可能会导致数十亿额外行，且最终结果毫无意义。此类查询在正确连接条件下可能比预期运行时间长得多。在任何数据库中，这都会是极大的时间浪费，但在Snowflake中，这也会导致高昂的费用。

避免重复值（Hybrid Unistore）

主键唯一标识一个实体。因此，主键列中的重复值将违反此约束，并应当被禁止。然而，只有Hybrid Unistore表能够强制执行这一规则。它们通过一个索引来实现这一点，从而允许Snowflake在执行DML操作时有效地扫描现有值。

如果使用常规的Snowflake表，可以在DML操作之前或之后进行临时验证，以测试是否存在重复。

Dr. Seuss将诗歌推向了合乎逻辑的结论：McCave夫人如果为她的孩子们取独特的名字，就能避免许多困境，她说：“但她没有这样做，现在已经太晚了。”然而，与命名孩子不同，给表分配主键永远不会太晚。接下来，让我们看看Snowflake提供的三种方法来实现这一点。

指定主键（PK）

在创建Snowflake表时，可以通过两种方式指定主键（PK）：内联方式（在CREATE TABLE语句中对应列旁边）或外联方式（在语句的末尾）。

  • 新表内联：在创建表时，主键可以直接在相应列旁边声明。这种方式仅适用于单列主键，不能用于定义复合主键：
CREATE TABLE ORDERS
(
    O_ORDERKEY number(38,0) CONSTRAINT my_pk_name PRIMARY KEY,
    O_PRODUCTKEY number(38,0) NOT NULL,
    O_CUSTKEY number(38,0) NOT NULL,
    -- <其余列>
    O_COMMENT varchar(200) COMMENT 'Order details'
);
  • 新表外联：这种方式允许Snowflake用户在CREATE语句的末尾指定主键，并可以将多个列作为主键定义的一部分：
CREATE TABLE ORDERS
(
    O_ORDERKEY number(38,0),
    O_PRODUCTKEY number(38,0),
    O_CUSTKEY number(38,0) NOT NULL,
    -- <其余列>
    O_COMMENT varchar(200) COMMENT 'Order details',
    CONSTRAINT my_pk_name PRIMARY KEY (O_ORDERKEY, O_PRODUCTKEY)
);
  • 现有表外联：对于现有的表，也可以通过执行ALTER TABLE语句来定义主键，并像外联约束一样进行指定：
ALTER TABLE ORDERS
ADD CONSTRAINT my_pk_name PRIMARY KEY (O_ORDERKEY, O_PRODUCTKEY);

现在我们已经看到如何定义主键，接下来让我们讨论存在的不同类型的键及其在建模中的使用。

键的分类

在了解了主键（PK）在定义和查询数据库表中的重要作用后，接下来我们将深入探讨各种键的类型，以理解它们的分类和在设计表时所扮演的角色。

尽管主键的功能保持不变——用于唯一标识表中的记录——但建模语言允许根据键列的性质进行区分。让我们看看在实际应用中这些区别是如何体现的，从最常见的类型开始：业务键。

业务键

业务键是一个主键，其值在组织内部具有意义或重要性。任何唯一的字符组合都可以用于标识实体实例，只要它们是唯一的。使业务键与众不同的是，其值本身就具有重要意义。

本章中的所有示例都是业务键。例如，在图5.7中的例子中，O_ORDERKEY 和 O_PRODKEY 的值在操作团队及其系统中被理解，并且在CRM系统和其他包含这些值的表中都可以引用。如果作为主键使用，社会安全号码或ISO国家代码也符合业务键的定义，因为它们在外部世界中具有更广泛的意义。

在建模包含业务键的列时，通常会使用前缀或后缀“BKEY”来标明其区别。

然而，一个键不必具备业务意义才能在表设计中发挥作用。接下来，我们来认识一下代理键。

代理键

与业务键不同——业务键具有业务意义——代理键是一个主键，其值没有特殊含义。代理键通常通过随机字符、列哈希或顺序整数生成。它们满足唯一性要求，但其值没有内在的意义。

表格通常有复合主键，包含多个成员列，这使得在需要将一个表与另一个表连接时，键的输入变得非常繁琐。代理键能够解决这个不便，因为它们本质上是单列的，并且具有标准的值格式（例如，列哈希或序列）。一些数据模型（如后续章节中讨论的Data Vault 2.0）依赖于代理键，以确保严格的命名和值模式，从而使表设计可重复且一致。

在建模包含代理键的列时，通常会使用前缀或后缀“SKEY”来标明其区别。

序列

在创建顺序代理键时，Snowflake提供了一种生成顺序代理键的机制，称为序列（sequence）。序列是一个独立的数据库对象，用于生成顺序的（但不一定是无间隙的）整数，从而确保列值的唯一性。在创建序列时，可以指定起始数字和递增值。

作为独立对象，单个序列可以被多个表共享，或者通过函数调用进行操作。

序列可以作为代理键使用，通过在创建表时将其作为默认列值来实现。为此，使用序列的 nextval 函数作为列的默认值，如下所示：

sql
复制代码
create or replace sequence seq1;
create or replace table foo (k number default seq1.nextval, v number);

以下示例展示了在各种nextval函数条件下，新记录的行为：

-- 插入带有唯一键（由seq1生成）和显式值的行
insert into foo (v) values (100);
insert into foo values (default, 101);

-- 插入带有唯一键（由seq1生成）和重用值的行。
-- 新键与现有的键是不同的。
insert into foo (v) select v from foo;

-- 插入两列的显式值
insert into foo values (200, 202);

select * from foo;
+------+------+
|    K |    V |
|------+------|
|    1 |  100 |
|    2 |  101 |
|    3 |  100 |
|    4 |  101 |
|  200 |  202 |
+------+------+

另外，Snowflake可以通过在创建列时使用AUTOINCREMENT或IDENTITY关键字，为给定的表透明地创建和管理序列对象：

CREATE TABLE FOO
(
    k number DEFAULT seq1.nextval,
    v number
);

CREATE TABLE BAR
(
    k number NOT NULL AUTOINCREMENT START 10 INCREMENT 5,
    v number NOT NULL
);

有关使用序列的更多信息，请参阅Snowflake的文档：docs.snowflake.com/en/user-gui…。

我们现在了解了主键的分类，可以区分具有业务意义的业务键和没有内在意义且唯一标识实体实例的代理键。然而，回想一下，一个表只能定义一个主键。那么，当有多个列可以唯一标识一个实体实例时，应该怎么办呢？

备用键作为唯一约束

假设我们正在建模一个包含EMPLOYEE_ID列（唯一的业务标识符）和社会安全号码（由政府颁发的个人标识符）的EMPLOYEE表。任一列都能满足主键（PK）的要求，即唯一标识EMPLOYEE表中的记录，但请记住，一个表只能分配一个主键。为了让数据库用户知道另一个列（或列组合）也满足主键条件，可以在已经存在主键的情况下定义备用键（AK）或唯一约束（UNIQUE）。

在前面的示例中，EMPLOYEE表有两个有效的主键候选项：EMPLOYEE_ID和SOCIAL_SECURITY_ID。在OLTP数据库中，作为组织业务键的列应当被设为主键。而在数据仓库中，由于可能加载来自多个源系统的业务键，通常使用代理键。按照这一惯例，EMPLOYEE表应当将EMPLOYEE_ID设为主键，将SOCIAL_SECURITY_ID设为备用键（UNIQUE）。

以下示例展示了这种情况：

CREATE TABLE EMPLOYEE
(
    EMPLOYEE_ID varchar(10),
    SOCIAL_SECURITY_ID number(9),
    -- <其余列>
    NAME varchar,
    CONSTRAINT pk_employee PRIMARY KEY (EMPLOYEE_ID),
    CONSTRAINT ak_employee UNIQUE (SOCIAL_SECURITY_ID)
);

与主键不同，一个表可以有多个备用键。备用键为数据库用户提供了表及其各个列的粒度信息，并且与主键一样，备用键具有相同的功能性好处。像主键一样，唯一约束仅在Hybrid Unistore表中强制执行，对于标准表而言，它们仅作为指示性约束。

到目前为止，本章集中讨论了单个表及其约束，但建模的目标是定义整个数据库的结构，表示多个业务实体之间的相互关系。接下来的部分将解释如何在实体之间建立关系，以及为什么这是数据库模型中的关键细节。

关系作为外键

任何业务都可以分解为一组实体及其相互作用。例如，客户会为供应商提供的商品下订单，同时使用来自当前营销活动的促销代码——这只是典型组织活动的一个非常狭窄的切面。到目前为止，本章集中讨论了实体本身：例如订单、商品和供应商。现在是时候集中讨论相互作用——或称为建模术语中的关系——例如下订单、提供商品和应用促销。

当业务实体之间存在关系时，它们对应的表必须有一种方式来捕捉这种相互作用的细节。当客户下订单时，订单详情必须记录客户是谁以及他们订购了哪些商品。记住，主键（PK）用于标识表中的唯一记录。因此，当两个表之间存在关系时，一个表的主键必须包含在另一个表中，以存储交互的细节。在数据库中，这种关系通过一个名为外键（FK）的表约束来建立。

当一个表（称为父表）中的主键列被包含在另一个表（称为子表）中时，可以在子表中声明外键约束，以正式化这种关系。外键约束告诉数据库及其用户，这两个表中有些列是共同的，但它们通过某些业务上下文共享关系。

外键在图表中通过一条线连接父表和子表来表示。回顾一下第1章《解锁建模的力量》中的PERSON和ACCOUNT示例，其中确立了账户必须分配给一个人，而一个人可以开设零个或多个账户。如果确定PERSON_ID唯一标识一个PERSON（即它的主键），则该列必须包含在ACCOUNT表中，并声明为外键约束。在创建ACCOUNT表（子表）时，必须定义主键和外键约束。

以下代码片段展示了如何创建PERSON和ACCOUNT表：

CREATE TABLE PERSON
(
    PERSON_ID number(8,0) NOT NULL,
    SOCIAL_SECURITY_NUM number(9,0) NOT NULL,
    DRIVERS_LICENSE varchar(10) NOT NULL,
    NAME varchar NOT NULL,
    BIRTH_DATE date NOT NULL,
    CONSTRAINT PK_1 PRIMARY KEY (PERSON_ID)
);

CREATE TABLE ACCOUNT
(
    PERSON_ID number(8,0) NOT NULL,
    ACCOUNT_ID varchar(12) NOT NULL,
    ACCOUNT_TYPE varchar(3) NOT NULL,
    IS_ACTIVE boolean NOT NULL,
    OPEN_DATE date NOT NULL,
    CLOSE_DATE date,
    CONSTRAINT PK_2 PRIMARY KEY (PERSON_ID, ACCOUNT_ID),
    CONSTRAINT FK_1 FOREIGN KEY (PERSON_ID) REFERENCES PERSON (PERSON_ID)
);

通过这个代码，结果图表使得这种关系对人类来说更加容易理解，这个图表最初在第1章《解锁建模的力量》中展示。

image.png

前面DDL中定义的主键（PK）和外键（FK），以及图5.10中所示的内容，确保了关于账户开设的业务规则被嵌入到数据库设计中。

以下图示展示了结果表的示例值，用于说明这种模型可能包含的数据。

image.png

在数据库中，外键（FK）到主键（PK）的引用是最常见的关系形式，但它并不是唯一的形式。外键也可以引用备用键（AK）。通常，一个表可能将代理键作为主键，并将各种业务键声明为备用键。由于代理键是一个没有业务价值的技术产物，相关的表将改为引用备用的业务键，如下例所示：

image.png

如附带代码所示，引用备用键（AK）作为外键（FK）的语法与引用主键（PK）完全相同：

CREATE OR REPLACE TABLE employee
(
    employee_skey      integer NOT NULL AUTOINCREMENT START 1   INCREMENT 1,
    employee_bkey      varchar(10) NOT NULL,
    name               varchar NOT NULL,
    social_security_id number(8,0) NOT NULL,
    healthcare_id      integer NOT NULL,
    birth_date         date NOT NULL,
    CONSTRAINT pk_employee_skey PRIMARY KEY (employee_skey),
    CONSTRAINT ak_employee_bkey UNIQUE (employee_bkey),
    CONSTRAINT ak_healthcare_id UNIQUE (healthcare_id),
    CONSTRAINT ak_ss_id        UNIQUE (social_security_id)
);

CREATE OR REPLACE TABLE employee_of_the_month
(
    month          date NOT NULL,
    employee_bkey  varchar(10) NOT NULL,
    awarded_for    varchar NOT NULL,
    comments       varchar NOT NULL,
    CONSTRAINT pk_employee_of_the_month_month PRIMARY KEY (month),
    CONSTRAINT fk_ref_employee FOREIGN KEY (employee_bkey)
    REFERENCES employee (employee_bkey)
);

在这段代码中，我们可以看到引用备用键（employee_bkey）作为外键（FK）时，语法与引用主键（PK）是相同的。

理解了外键（FK）是什么后，接下来让我们了解它们的好处。

外键（FK）的好处

声明外键（FK）正式化了两个表之间的功能关系。这些元数据现在成为数据模型的一部分，并且对于使用数据库的任何人都可以参考。以下是它带来的一些优势。

可视化数据模型

人类使用可视化来增强认知能力。上一节展示了如何通过简单的图表比代码更快、更详细地传达相同的信息。现在，想象一下，从两个表扩展到整个数据库架构的过程。如果没有可视化的指导帮助映射出实体和关系，没有先前的知识，理解所有表及其内容将是一个挑战。

以下图示展示了从2个表到20个表在图表中的表现。考虑到企业系统中的数据模型可能跨越数百甚至数千个表格，图表的帮助在导航业务/数据景观时变得不可或缺。

image.png

然而，表之间的关系不仅仅是可视化辅助工具——它们在编写查询时也起着实际的作用。

指导连接（Joins）

定义外键（FK）要求用户指定父表主键（PK）列与相应子表中的列之间的引用关系。这意味着两个表之间的链接现在已经被嵌入到数据模型中，用户在进行分析或转换时可以引用这一关系来连接表数据。

以下图示展示了外键关系如何突出显示两个表之间的共同列（PERSON_ID）。

image.png

现在，用户可以利用这些元数据编写查询，通过使用PERSON_ID连接两个表，从而检索PERSON和ACCOUNT数据。

然而，外键（FK）不仅对人类有帮助——商业智能（BI）工具也会利用它们。

更重要的是，Snowflake可以利用这些细节，在不需要连接时避免执行连接，从而提高性能（有关更多内容及RELY属性，请参见第12章《将变革性建模付诸实践》）。

在BI工具中自动化功能

许多BI工具使用外键（FK）来增强可用性并节省用户时间。例如，像Tableau和Power BI这样的仪表板和分析工具能够检测外键，并使用它们自动创建表之间的连接，而无需用户的显式帮助。

建模工具和一些SQL IDE可以通过读取数据库DDL自动生成实体关系（ER）图。虽然每个数据库对象都有相应的CREATE语句，但只有当CREATE语句所表示的对象也通过外键（FK）相关联时，ER图才有意义。

强制引用完整性（Hybrid Unistore）

接下来我们讨论的好处只有通过强制外键（FK）约束才能实现，因此它仅适用于Hybrid Unistore表。然而，它在Snowflake平台上解锁了强大的新功能，用户应该了解这些功能。

正如我们所讨论的，外键关系不仅仅是指示性的——它通过数据库约定正式化了业务规则。回想一下之前的示例，其中规定了账户不能在没有PERSON_ID的情况下开设。那么，当新账户创建时，数据库如何确保提供了PERSON_ID，更重要的是，它与PERSON表中的有效记录相对应？这通过强制执行外键约束来实现。

当数据库强制执行外键约束时，它会检查确保子表中使用的唯一标识符在父表中存在——这一条件被称为引用完整性。引用完整性确保子表中的外键值对应于父表中的有效记录。就像为一个不存在于数据库中的人开设账户是没有意义的，引用完整性检查确保DML操作不会导致错误和异常数据。

就像强制主键（PK）约束一样，外键约束的强制执行依赖于索引的存在，以便快速执行引用完整性检查，因此只有在Hybrid Unistore表中才能实现（除非手动执行）。

无论是否强制执行，使用外键有许多令人信服的理由。我们将讨论的最后一个约束是Snowflake所有表上都强制执行的唯一约束，不仅限于Hybrid Unistore表，因此让我们了解它是如何工作的。

必填列作为NOT NULL约束

在为实体定义属性时，必然会出现哪些属性是必填的，哪些是可选的问题。与大多数建模决策一样，答案更多地取决于业务上下文，而不是任何技术性数据库属性。例如，同一个属性，像CUSTOMER的电子邮件地址，对于在线商店来说可能是必填的，而对于实体零售商来说则是可选的。在后者的情况下，缺少电子邮件地址意味着错过销售信息，而在前者中，则可能意味着无法访问网站。

从概念模型过渡到物理Snowflake设计时，必填列可以通过NOT NULL约束进行定义。NOT NULL约束在相应列旁边内联声明，并且不需要命名。因此，不能将NOT NULL约束声明为外联。

将NOT NULL约束添加到列的格式如下：

<col1_name> <col1_type> [ NOT NULL ]

前面的例子，如ACCOUNT表，已经展示了NOT NULL约束的实例。这里，我们可以看到CLOSE_DATE是ACCOUNT表中唯一不是必填的列，允许其为空（NULL）：

CREATE TABLE ACCOUNT
(
    PERSON_ID number(8,0) NOT NULL,
    ACCOUNT_ID varchar(12) NOT NULL,
    ACCOUNT_TYPE varchar(3) NOT NULL,
    IS_ACTIVE boolean NOT NULL,
    OPEN_DATE date NOT NULL,
    CLOSE_DATE date,
    CONSTRAINT PK_2 PRIMARY KEY (PERSON_ID, ACCOUNT_ID),
    CONSTRAINT FK_1 FOREIGN KEY (PERSON_ID) REFERENCES PERSON (PERSON_ID)
);

与其他约束不同，强制执行NOT NULL不需要查找或引用其他表——它可以直接通过检查指定列中的值来对单个行进行操作。因此，NOT NULL对于所有Snowflake表都强制执行，并且在DML操作过程中不会带来性能开销。

总结

本章讨论了如何从逻辑建模概念过渡到物理Snowflake对象。在此过程中，我们了解了Snowflake如何通过将表分解为可管理的微分区来处理近乎无限大小的表，并且这些分区如何通过聚类优化查询和DML性能。

接下来，我们了解了如何通过理解Snowflake的数据类型及其属性来定义属性。Snowflake提供了各种功能，使得与数据类型的交互更加简便和高效，此外，它还为半结构化数据提供了强大的选项。

在深入探讨各类约束类型之前，我们了解了数据库约束是什么，以及Snowflake如何根据约束适用的表类型来组织和强制执行这些约束。

我们看到，唯一标识符对于定义表至关重要，且Snowflake通过主键（PK）约束来管理这一过程。主键帮助数据库用户通过识别表中最低级别的细节来确保准确的搜索和连接。我们还看到，有时当多个候选主键存在时，可以通过唯一约束（UNIQUE）定义备用键（AK），并提供相同的好处。

随后，我们看到如何通过定义外键（FK）约束在物理数据库中正式化表之间的关系。外键通过捕捉表之间的功能关系，使得可视化和导航复杂的数据景观成为可能，这是数据库用户和BI工具经常利用的信息。

最后，我们了解了如何声明必填列并使用NOT NULL约束强制执行规则，以确保数据质量。

本章及其他章节经常依赖关系图来阐明示例和解释抽象概念。然而，尚未正式解释建模符号或对可视建模工具包中各元素的概述。现在，我们已经理解了逻辑和物理层面上的数据模型核心元素，下一章将集中讨论用于设计或可视化数据模型的建模符号，适用于任何规模的建模。

avatar
文章
阅读
粉丝