hive用merge管理缓慢变化维度的三种方法

这是系列的第二部分。

管理缓慢变化的维度

在第1部分中，我们展示了使用SQL MERGE，UPDATE和DELETE更新Hive中的数据是多么容易。让我们把事情做好，看看Hive中用于管理缓慢变化维度（SCD）的策略，这些维度可以让您随时分析数据的整个变化。

在数据仓库中，缓慢变化的维度（SCD）捕获的数据在不规则和不可预知的时间间隔内变化。根据不同的业务需求，管理SCD有几种常用的方法。例如，您可能想要跟踪客户维度表中的完整历史记录，以便随时跟踪客户的发展情况。在其他情况下，您不关心历史，但需要一种简单的方法将报告系统与源操作数据库同步。

最常见的SCD更新策略是：

• 类型1：用新数据覆盖旧数据。这种方法的优点在于它非常简单，只要您希望轻松地将报告系统与操作系统同步，就可以使用它。缺点是你每次更新都会丢失历史记录。
• 类型2：添加版本历史记录的新行。这种方法的优点是它可以让你跟踪完整的历史记录。缺点是您的维度表增长不限，可能会变得非常大。当您使用Type 2 SCD时，通常还需要创建其他报告视图来简化仅查看最新维度值的过程。
• 类型3：添加新行并管理受限版本历史记录。类型3的优点是您可以获得一些版本历史记录，但维度表与源系统保持相同的大小。您也不需要创建其他报告视图。缺点是你得到有限的版本历史，通常只包括最近的2或3个变化。

本博客展示了如何使用Hive在HDP 2.6中引入的新MERGE功能管理Apache Hive中的SCD。这里的所有例子都被捕获到一个GitHub存储库中，以便在您的Hadoop集群上轻松复制。由于管理SCD的方式有很多变化，所以参考标准文献（例如“数据仓库工具包”）可以获得更多的想法和方法。

hive- SCD策略概述

Hive SCD策略概述

入门：通用元素

所有这些示例都是以作为外部表格加载的分阶段数据开始的，然后将其复制到可用作合并目标的Hive托管表中。第二个外部表，表示来自操作系统的第二个完整转储，也作为另一个外部表加载。两个外部表格都具有相同的格式：由ID，名称，电子邮件和状态组成的CSV文件。初始数据加载有1,000条记录。第二个数据负载有1,100条记录，包括100个新的记录，加上对原始1,000条记录的93个更改。捕获这些新的和改变的记录取决于各种合并策略。如果你想跟随，所有的数据和脚本都在GitHub仓库上。

类型一：SCD

由于类型1更新不会跟踪历史记录，我们可以使用与暂存数据完全相同的格式将数据导入到托管表中。以下是我们托管表的示例。

第1类更新的合并SQL代码非常简单，如果记录匹配，更新它; 如果没有，添加它。

merge into

 contacts_target

using

 contacts_update_stage as stage

on

 stage.id = contacts_target.id

when matched then

 update set name = stage.name, email = stage.email, state = stage.state

when not matched then

 insert values (stage.id, stage.name, stage.email, stage.state);

让我们来看看它对于一个特定记录的变化，记录93：

这里要重要强调的事情是，所有的插入和更新都是在一次完成的情况下进行的，对上游SQL查询具有完全的原子性和隔离性，如果发生故障则会自动回滚。使用传统的SQL-on-Hadoop方法保证原子性和隔离性属性非常困难，几乎没有人将它们付诸实践，但Hive的MERGE使它变得微不足道。

类型二：SCD

类型2更新允许完整的版本历史记录和跟踪记录的当前状态的额外字段的方式。在这个例子中，我们将添加开始和结束日期到每个记录。如果结束日期为空，记录是最新的。再次，查看GitHub了解如何在数据中进行数据的细节。

我们将使用单通道Type 2 SCD，它将并发阅读器与进行中的更新完全隔离，这意味着对于更改，我们要更新现有记录以将其标记为过时，并插入将成为当前记录的净新记录。

接下来，合并本身：

merge into contacts_target

using (

 — The base staging data.

 select

contacts_update_stage.id as join_key,

contacts_update_stage.* from contacts_update_stage

 union all

— Generate an extra row for changed records.

 — The null join_key forces records down the insert path.

 select

   null, contacts_update_stage.*

 from

   contacts_update_stage join contacts_target

   on contacts_update_stage.id = contacts_target.id

 where

   ( contacts_update_stage.email <> contacts_target.email

     or contacts_update_stage.state <> contacts_target.state )

   and contacts_target.valid_to is null

) sub

on sub.join_key = contacts_target.id

when matched

 and sub.email <> contacts_target.email or sub.state <> contacts_target.state

 then update set valid_to = current_date()

when not matched

 then insert

 values (sub.id, sub.name, sub.email, sub.state, current_date(), null);

要认识到的关键是using 子句将为每个更新的行输出2个记录。其中一个记录将有一个空联接键（所以将成为一个插入），并有一个有效的联接键（所以将成为一个更新）。如果您阅读本系列的第1部分，您将看到此代码与我们用于跨分区移动记录的代码类似，只是使用更新而不是删除。

让我们来看看这是怎么做的记录93。

我们同时并原子地过期了第一条记录，同时添加了最新的细节记录，使我们可以轻松地跟踪我们的维度表的完整历史记录。

类型三：SCD

类型2的更新是强大的，但代码比其他方法更复杂，维度表增长没有约束，这可能是相对于你所需要的太多。3型SCD开发较为简单，并且与源维度表具有相同的大小，但仅提供部分历史记录。如果您只需要对历史进行部分观察，那么3型SCD可能是一个很好的折衷。

对于这个例子，我们只会跟踪当前的值和之前版本的值，并且会跟踪同一行的版本。这是一个示例：

更新到来时，我们的任务是将当前值移到“最后”值列中。代码如下：

merge into

 contacts_target

using

 contacts_update_stage as stage

on stage.id = contacts_target.id

when matched and

 contacts_target.email <> stage.email

 or contacts_target.state <> stage.state — change detection

 then update set

 last_email = contacts_target.email, email = stage.email, — email history

 last_state = contacts_target.state, state = stage.state  — state history

when not matched then insert

 values (stage.id, stage.name, stage.email, stage.email,

 stage.state, stage.state);

你可以看到这个代码相对于类型2是非常简单的，但只提供有限的历史。让我们来看看93号纪录之前和之后：

更简单的变更跟踪方法

如果你有很多领域需要比较，那么编写变化检测逻辑会变得很麻烦。幸运的是，Hive包含一个散列UDF，使得检测变得简单。散列UDF接受任意数量的参数，并返回基于参数的校验和。如果校验和不匹配，则该行中的某些内容已经更改，否则它们是相同的。

举一个例子，我们将更新Type 3的代码：

merge into

 contacts_target

using

 contacts_update_stage as stage

on stage.id = contacts_target.id

when matched and

 hash(contacts_target.email, contacts_target.state) <>

   hash(stage.email, stage.state)

 then update set

 last_email = contacts_target.email, email = stage.email, — email history

 last_state = contacts_target.state, state = stage.state  — state history

when not matched then insert

 values (stage.id, stage.name, stage.email, stage.email,

 stage.state, stage.state);

好处是，无论我们是比较2个字段还是20个字段，代码几乎不会改变。

结论：

SCD管理是数据仓库中一个非常重要的概念，是一个深度而丰富的主题，有很多策略和方法。通过ACID MERGE，Hive可以轻松管理Hadoop上的SCD。我们甚至没有涉及代理密钥生成和校验和变更检测等概念，但Hive也能够解决这些问题。所有这些示例的代码都可以在GitHub上找到，我们鼓励您在Hortonworks Sandbox或Hortonworks Data Cloud上自己尝试一下。