在Snowflake中建立近实时的摄取管道

　　执行摘要外部表很棒，但是永远不会接近Snowflake内部表Snowpipe具有极强的弹性和快速性，只需确保您监视错误Snowpipe是一项相当新的技术，因此某些烦恼/缺失功能需要一些解决方法如何轻松地对登台表实施热/冷/冻结数据存储方法介绍

　　> ELT Pipeline

　　在构建数据仓库或数据湖时，您需要解决的第一个基础块是复制，它代表3个ELT步骤（E + L）中的2个。本文重点介绍“负载”部分，尤其是如何使用Snowflake快速，可靠且经济高效地每天摄取数十亿行。

　　要求

　　在深入研究细节之前，让我们对一些属性进行布局，以寻找理想情况下的过渡区域：

　　稳定，始终可用并尽可能保持最新没有数据限制（拥有所有产生的数据可用）高性能无缝提供给企业（使用相同的工具集和语言）成本效益

　　其中大多数都不是市场上大多数MPP平台所难以获得的，但是有2种特别重要，通常是最棘手的。

　　使所有数据可用

　　当您每天摄取数十亿行数据时，规模开始蔓延。通常，您只将这些数据保留在数据湖中，而只需将一小部分摄取到数据库中就可以避免庞大的存储费用。但是您的企业可能“有时”需要超过1年的数据才能…

　　始终保持最新

　　刷新暂存区的方式将直接影响数据仓库的最新状态。这是非常常见的ETL模式，即在启动ETL之前先完成加载，然后在运行ETL之前刷新数据。但是，对于使用暂存区的用户来说，这意味着仅刷新1小时，这已经是一个折衷，并且会限制某些用例。此外，当处理每两分钟到达的大量流数据时，每小时或每天加载一次甚至不是一个好的选择。

　　现在，我们了解了要解决的问题，让我们看看Snowflake Toolbox。

　　外部表

　　> Snowflake External Tables — Overview

　　可以将外部表想像成运行在BLOB存储数据（在本例中为S3）之上的Snowflake Presto引擎，该表与其他任何表一样都可以在数据库中使用（您可以在Snowflake的文档中找到更好的解释）。您可以在内部和外部表之间混合和匹配，这使这成为将Data Lake中的数据与Data Warehouse中的数据连接起来的一种非常强大的方法。

　　优点使用外部表作为将所有湖泊数据导入Snowflake的替代方法。数据始终保持同步，Lake和Staging之间的零延迟非常适合将大量数据加载到Snowflake表中，实际上比使用COPY命令要快得多……缺点您必须具有良好的分区方案。如果无法进行分区修剪，则性能将大大降低。外部表的查询优化器很容易混淆（请参阅链接），并且在这种情况下，它最终会执行“全表扫描”（花费大量的美元和时间）即使一切都经过了完美调整，性能也永远不会比内部表好Snowpipe

　　> Snowpipe — Overview

　　Snowpipe是一个托管的无服务器数据加载引擎，您可以在其中支付摄取的文件数。您可以使用Lambda函数来实现类似的行为，该函数会加载到达某个S3存储桶中的每个文件，但是随后您将需要运行仓库，这会变得非常昂贵。

　　使用它后，我发现它相当不错。在S3中的文件着陆与Snowpipe提取之间，它保持了非常稳定的40秒（通常为30秒左右）的最大延迟。另外，到目前为止，我还没有看到“丢失的文件”。有关更多信息，请参见此处的Snowflake文档。

　　优点数据位于Snowflake表中，并“几乎同步”（不到40秒）无仓库/ lambda来管理，可靠，无服务器比其他替代品便宜很多缺点从管道管理的角度来看，它仍不成熟（例如：只有对管道的授予才是所有权，您需要对管道进行监视；要查找错误，需要运行中的仓库，这会使监视工作变得昂贵）在很有可能需要加载超过7天的文件的情况下，您将需要使用其他过程如果您的Feed中包含大量小文件，则Snowpipe将会变得非常昂贵。合并文件，或考虑其他替代方法。复制—您的好朋友

　　复制命令不需要介绍。从性能的角度来看，与其他方法相比，我发现它的运行速度非常慢（我发现使用外部表复制数据的速度最多可以提高3倍），而且我还不清楚扩展对性能的影响（某些文件因扩大仓库规模而带来的收益为零，而另一些文件即使不是通常的线性缩放也具有某些收益）。

　　我将使用哪种摄取管道？

　　现在我们知道我们可以使用哪些工具，是时候开始做出一些选择了，选择总是非常特定于用例的。我想考虑的用例如下：

　　S3将成为主要的数据湖（而不是将Snowflake变成您的数据湖）暂存区数据必须是最新的数据仅追加，绝不重叠数据太大，无法全部保存在Snowflake中（太昂贵），并且需要积极的数据生命周期

　　在这种情况下，这是我认为是最佳的设置：

　　

　　> Best Staging Area architecture for this use-case

　　该架构为您提供了很多：

　　数据同步时间永远不会超过40秒（与Lake相比）低维护成本效益（将这些文件保留在100–250MB左右）简单的热/冷存储解决方案企业可以使用他们已经知道如何使用的相同工具访问Snowflake中的所有数据。性能遵循热/冷策略（最近的数据比旧的数据访问速度更快）在S3中很容易老化数据（可以使用S3数据生命周期）那么，该解决方案中的“妥协”在哪里？它依赖于基于日期的分区方案就像大多数Hadoop引擎一样，外部表在很大程度上依赖于分区来优化性能。如果您的查询错过了分区修剪，性能将受到严重打击Snowflake外部表的查询优化器当前存在一个错误（我在这里写过），该错误很容易导致查询性能下降如果您的数据具有upsert模式，则此模型将需要更改（仍然可行，但您需要对该视图进行一些调整，从而会降低性能）您需要一些自动化的流程才能将滑动窗口应用于数据并在Snowflake中查看如果您需要加载7天以上的数据，则需要一个替代的加载过程我现在就想要！！告诉我如何！

　　对于那些想尝试一下的人，这是您需要做的概述：

　　1.创建外部表

　　说明：使用外部表重要说明：

　　为此，您的数据必须按“老化”列进行分区如果分区键实现良好，则应该能够对查询运行EXPLAIN PLAN，并看到通过该键进行的筛选可以解析为1个正在读取的单个分区（partitionsAssigned=1）2.创建内部表

　　您可以将其创建为一个非常简单的“将表创建为…，其中1=0”，仅用于复制列。然后，您可以根据需要添加和删除列。

　　重要笔记：

　　如果有一些预计算会很方便并且可以在简单的副本中完成，请为其创建列我喜欢添加一些控制列，例如何时插入行或它来自什么文件（请参见元数据）3.创建Snowpipe

　　每个云提供商的说明：自动连续数据加载

　　加载期间转换数据的说明

　　重要笔记：

　　说明很长，但布局合理如果要在加载时添加/删除任何列，请在创建管道时执行此操作。示例：如果您想知道该行来自哪个文件，只需将metadata $ filename添加到复制命令中即可。请注意，如果您自定义副本，则将无法运行VALIDATION_MODE（空运行）创建Snowpipe之后，默认情况下将启用它，数据将开始流动4.定义您的起点

　　如果截止点在过去的7天之内，则可以使用refresh pipe命令有选择地将文件/路径加载到表中：

　　alter pipe mypipe refresh prefix='d1/year=2022/' modified_after='2022-01-15T13:56:46-07:00';

　　但是，如果您想让时间更进一步，我建议使用外部表将数据加载到内部表中。

　　5.创建您的视图

　　现在，您只需要创建视图：

　　CREATE VIEW my_new_shiny_view AS

　　SELECT ..... --make this look like the external table

　　FROM INTERNAL

　　WHERE date_part_key >='yyyy-mm-dd'::date

　　UNION ALLSELECT ..... --precalc cols if you can, to match internal table

　　FROM INTERNAL

　　WHERE date_part_key < 'yyyy-mm-dd'::date6.监控使用情况

　　监视使用情况非常重要，尤其要当心那些仍然最终进入外部表的查询。如果您确定了正确的切入点，那么现在大多数查询应该只在内部表中显示（否则，您需要查看查询计划）。

　　结论

　　Snowflake为您提供了许多工具来管理数据及其接收。与往常一样，每种实现都有优点和缺点，但是总的来说，我想说，将Snowpipe和外部表结合起来应该可以为大多数用例提供解决方案。