由 Dominik Golebiewski (LinkedIn) & Konrad Maliszewski (LinkedIn) 提供
阅读Dominik Golebiewski在简书上的文章。热爱对数据库调优，编写优化SQL查询……https://medium.com/@dominik.golebiewski?source=post_page-----7012b73bdcad--------------------------------------- [Konrad Maliszewski — Medium阅读阅读 Konrad Maliszewski 在 Medium 上的文章。每天，Konrad Maliszewski 和数千名其他读者一起在 Medium 上阅读……]
我们之前讨论过如何监控Snowflake的成本，以便更好地掌握它们，以及我们提到的不同技术用于使dbt操作及其成本更加高效（这里 和 这里 及 这里）。在接下来的内容中，我们想更深入地探讨一个我们最近探索并测试的新想法。这个想法最初只是一个简单的分析，调查不同Snowflake仓库的工作负载是否平衡。我们发现，对于我们关键的数据转换仓库，查询执行时间的范围非常大，从几毫秒到超过五分钟。
起初我们认为在 dbt 模型层级进行控制是个好主意。例如，某些维度视图模型确实需要比复杂的中间事实转换逻辑更小的数据库仓库。但经过详细分析，我们发现 dbt 模式引入了另一个更深层面的不平衡。我们利用这一洞察设计了一种方法，根据实际的每个查询需求分配数据库仓库。我们结合了审查和重新应用表聚集的步骤。结果大大超出了我们的预期，实现了 60% 的成本节省，而没有影响查询延迟。而且它是开源的！详见 GitHub 项目！
背景：我们使用专门的DATATRANSFORM[size] 数据仓库来运行我们核心数据管道中的操作。数据仓库的大小从XS到2XL，以处理不同组件和dbt模型的工作负载。我们还有一个用于开发的等效数据仓库组（DEVELOPER_[size]）。所有数据仓库都可以进行水平扩展，因为它们可能在任何时间被多个作业共享。然后，我们通过根据使用时间来分配成本，从而计算特定作业和流程的成本（是的，我们还没有切换到Snowflake最近发布的查询分配表）。
dbt 可以非常方便地为每个 dbt 模型分配一个仓库。然而，我们没有花太多时间在这一点上。有时我们会这么做，特别是在模型处理大量数据并进行复杂操作时，这可能需要一个较大的仓库。但在大多数情况下，我们选择为通常位于子目录中的模型组定义一个仓库。一些项目最终大多使用 XL 和 2XL 规格的仓库，我们觉得这些仓储规格适合处理这些数据量和所需的复杂转换。
仓库存量不平衡问题在分析我们的Snowflake工作负载时，我们发现大多数转换型仓库处理了非常多样化的查询，涉及很长的时间范围。即便大型仓库（XL或2XL）也执行了许多在5秒以内的查询，以及一些较重和较长的查询。我们认为这是由于需要构建多种dbt模型所致，并着手优化它。然而，对我们查询历史的深入分析得出了三个结论：
- 我们大多数的模型都是增量的，每个模型构建涉及三个独立的操作，分别作为三个单独的查询执行：创建表为选择（CTAS）、删除 和 插入。这些查询类型在执行成本上差异很大，其中 CTAS 的执行时间最长，其次是 删除，最后是 插入。虽然我们没有严格测量确切的时间，但观察结果表明，这三个操作的大致时间分配分别为60%、30%和10%。这种不平衡造成了性能的差异：对于每个增量模型，其中两个查询比第三个查询快得多，这造成了性能的差异。
- 由于我们采用了“灵活批处理”系统（如前所述此处），每天由每个任务处理的数据批次变化很大。而且我们始终使用相同的仓库来处理它们。这意味着我们在计算资源的配置上可能过于保守，以应对一天中的最大批次。
- 由于我们的“灵活批处理”系统，不是所有的模型在每个批次中都有新的数据需要插入，但使用 dbt run 命令，每个模型都会被尝试运行。一个大的仓库仅仅用于检查是否有新的数据需要插入模型。
这让我们开始问起，
能否根据现有的相关信息，为每个操作点根据需求动态分配合适的仓库？
此时，我们对这种方法的可能性感到同样兴奋，同时也担心可能过度设计，需要频繁手动调整。我们之前尝试过一些承诺能自动完成这些任务的工具，但结果相当不理想。我们也意识到，虽然我们的“灵活的批次处理”在微观层面上（设计上）是不可预测的，但在宏观层面上其实相当稳定和可预测（有一个特别大的批次，大多数批次都在早上8点到11点之间运行等）。至少在过去三年中是这样的。而且数据管道占了我们Snowflake账单的80%，因此在这一领域减少成本对总账单有显著影响。于是我们决定继续前进！
实验方法 — 仓库优化工具通过互动的方式，我们开发了一系列针对上述各方面问题的元素。这些元素使我们能够构建Warehouse Optimiser (WO)。WO是一组作为dbt宏实现的算法，可以应用于dbt项目中模型的算法。WO的核心元素有：
- 监控 — 监控传入负载和正在使用的操作的算法。每个方法均已模块化，可以单独使用或与其他方法结合使用。对于负载，宏可以检查上游依赖项的新鲜度和负载大小。另一个宏检查正在执行的操作（CTAS、删除操作、插入）。根据用户定义的限制和宏检查结果，将仓库分配给操作。例如，如果上游依赖项没有新数据，则使用XS仓库，因为模型将不会执行任何数据转换。
- 调度 — 实际上，这些算法使我们能够优先考虑速度而非节省资源，而不会牺牲其他因素。特定仓库大小可以在特定日期和时间被强制或修改默认设置。调度优先于监控输入的结果。例如，我们可以在周一的8点到11点之间将某些默认设置从XS调整为M，因为我们知道这是这周最繁忙的时间，此时延迟至关重要。
- 缓存 — 一组工具方法，为上述算法提供输入。它们旨在减少数据库请求次数并保留关键信息，以便做出明智的决策。例如，缓存从表中返回的最大时间戳的结果，该结果在模型执行过程中将被多次使用。
- 仓库分配 — 上述方法的封装。它可能最好被描述为“转换器”。它定义了语法约束，使不同大小的仓库能够动态地进行替换。
- 日志记录 — 正如其名，这是一个日志记录模块，无论是在开发还是调试阶段都非常有用。
此外，我们提供了多种配置选项，以便开发环境和生产环境可以轻松隔离。
监视WO的监控组件旨在智能评估每个查询的工作负载特点，并动态调整最适合的Snowflake仓库大小。监控机制不再依赖固定的仓库分配，而是依据上游数据量、查询类型和上下文环境来确定最合适的仓库大小。
WO在模型运行前持续监控数据流入量。通过追踪行数和最近的数据加载情况，它能判断一个转换任务是否会涉及大量数据处理，还是保持较轻负载。它根据数据加载量所需资源进行估算，以确定仓库需求。从这个意义上说，它非常依赖用户对数据流程的深入了解，但正因为如此，它允许实现显著的成本节省。
另一个重要的因素是操作类型检测。仓库优化器评估模型是否正在进行全量刷新、空运行或CTAS/删除/插入操作。当查询不修改数据时，它会被重新分配到XS仓库以减少不必要的花费。相反，如果模型正在对大型数据集执行CTAS操作，仓库优化器会自动扩大仓库规模，然后再缩减规模。默认情况下，dbt的材料化操作不允许执行过程中切换仓库，这意味着同一仓库将用于执行CTAS/删除/插入操作。为解决这一限制，我们引入了一个自定义的删除+插入策略，这基本上是dbt实现的复制，但允许我们切换仓库。
一个示例配置如下：
meta:
仓库优化器:
开启功能: true # 在您的模型中启用仓库优化器
操作模式:
全量刷新操作:
仓库大小: xl # 全量刷新操作的仓库大小
运行操作:
ctas: # 增量 CTAS 操作的仓库大小
仓库大小: xs
监控:
开启功能: true # 启用源行数监控功能
阈值:
- 行数: 10000000
设置为仓库大小: s # 设置为仓库大小: s 如果源行数大于阈值
- 行数: 100000000
设置为仓库大小: m
删除: # 增量删除操作的仓库大小
仓库大小: xs
监控:
开启功能: true
阈值:
- 行数: 10000000
设置为仓库大小: s
插入:
仓库大小: xs
模拟运行操作: # 模拟运行操作的操作的上游依赖
操作的上游依赖: # 需要监控行数的上游模型
- model1
- model2在监控确保仓库分配能根据查询复杂度和数据量的变化作出调整的同时，它并未考虑可预测的工作负载模式这一问题。许多数据管道遵循固定的日程，处理高峰在特定的小时或一周中的特定日期出现。我们 WO 的调度组件解决了这个问题，允许用户定义基于时间的仓库调整方案，确保系统在关键时刻优先考虑性能，同时在非高峰时段优化成本并降低成本。
WO的调度机制的工作原理是通过比较当前时间和日期与预设的调度。若找到匹配，则相应地调整仓库规模，覆盖默认设置。例如，团队可能选择在工作时间（例如，工作日的08:00–16:00）提供更大的仓库，以确保处理的低延迟，而在非工作时间则减少计算资源，因为此时工作负载较少。
调度与监控协同工作，这意味着即使在预定的时间窗口内，仓库分配仍然会根据查询特征进行调整。例如，在高峰期计划增加S仓库的容量，如果检测到特别大的批处理量，可能会进一步提升至M级别。这种分层方法确保了计划提供性能的基准，而动态监控则根据实时情况对分配进行微调。
示例设置：
meta:
仓库优化器配置:
启用: true
操作类型:
在运行时:
CTAS:
仓库大小设置: xs
调度: # 仓库大小的调度配置
启用: true # 启用调度
调度计划: # 要应用的调度计划
- 名称: "工作日高峰时间" # 调度名称
周几: ["周一", "周二", "周三", "周四", "周五"] # 应用调度的周几
时间:
开始: "07:00" # 调度开始时间
结束: "22:00" # 调度结束时间
仓库大小设置: s # 调度使用的仓库大小
监控:
启用: true # 启用源行数监控
阈值:
- 行数阈值: 10000000
仓库大小设置: m # 若源行数超过阈值，则调度中使用的仓库大小无论是监控还是调度，都依赖于重复的查找，无论是检查上游行数、确定查询类型，还是解析仓库名。如果没有缓存，这些查找会导致重复计算，延长查询执行时间并增加不必要的负担。优化器中的缓存组件通过存储频繁访问的值，以便在多个操作中重用它们来消除这种低效。
一个关键的例子是时间戳查找。某些模型需要找到最新的时间戳以确定最新的可用数据。如果在同一个模型运行中多次执行此查找，则通常会触发多次数据库查询。使用缓存，第一次查找会获取并保存该值，之后的请求都会直接使用缓存中的数据，这样就避免了多余的数据库查询。
缓存对于仓库名称解析也至关重要。之前，仓库名称是直接在代码中写死的，并基于 dbt 目标环境，手动添加了大小相关的后缀。这种做法需要反复执行宏来确定正确的仓库。现在，WO 会缓存解析后的仓库名称，分配后，后续使用直接从缓存中获取名称，不再重新计算名称。在开发环境中，这特别有效，因为减少了编译所需的时间。
缓存获取宏：
{% macro 缓存值获取(cache_key) %}
{{ return(adapter.dispatch('get_cache_value', 'dbt_macro_polo')(cache_key)) }}
{% endmacro %}
{% macro default__缓存值获取(cache_key) %}
{% set macro_ctx = dbt_macro_polo.create_macro_context('get_cache_value') %} {# 创建宏上下文 #}
{% set macro_name = macro_ctx.macro_name %}
{% set model_id = macro_ctx.model_id %}
{% set macro_polo = var('macro_polo', {}) %}
{% set cache = macro_polo.get('cache', {}) %}
{% set cache_value = cache.get(cache_key, {}) %}
{{ dbt_macro_polo.logging(macro_name, message="缓存处理: " ~ {'缓存键': cache_key, '缓存值': cache_value}, level='DEBUG', model_id=model_id) }}
{{ return(cache_value) }}
{% endmacro %}WO的最后一环是仓库分配，它充当优化器的逻辑与底层Snowflake仓库配置之间的翻译层。监控、调度和缓存功能确定使用哪个仓库大小，而仓库分配确保这些决策正确地映射到实际的Snowflake仓库名称。
这一层抽象出了环境特定的差异，使得WO可以在不同的Snowflake实例中一致地运行。它没有硬编码仓库名称，而是强制执行一种结构化的命名规范，确保优化程序可以动态切换仓库，而无需手动调整。关键限制在于仓库名称必须遵循特定格式，即大小（例如xs、s、m、l、xl、2xl）作为下划线后的后缀，例如：xxx_xs。
例如，在一个仓库命名遵循DATATRANSFORM[大小]模式的环境中，Warehouse Optimiser 将会根据计算出的仓库大小无缝处理 DATA_TRANSFORM_XS、DATA_TRANSFORM_L 或 DATA_TRANSFORM_2XL。这确保了与生产环境和开发环境的兼容性，即使这些环境使用不同的命名约定。通过标准化仓库解析流程，WO 提供了一种灵活且结构化的动态仓库选择方案，使其能够适应各种 Snowflake 环境配置。
例如配置：
变量:
macro_polo:
cache: {} # 缓存功能所需
仓库设置:
仓库大小: ['xs', 's', 'm', 'l', 'xl', '2xl']
环境设置:
<target_name>:
仓库名前缀: <warehouse_name_prefix>仓库优化器工具已经成为了我们管理Snowflake计算成本方面的游戏规则改变者。通过根据实时工作负载监控、预定的覆盖和提高的缓存效率动态调整仓库大小，我们已经消除了不必要的计算成本，同时保持了（甚至提升了）性能。
我们在2024年11月部署了WO系统。与此同时，我们还对表聚类进行了彻底的回顾，并将其重新应用于许多模型。自那时起，我们的仓库支出已经减少了60%（声明：我们尚未进行分析以确定节省的成本中有多少来自WO，有多少来自重新聚类），这消除了2XL仓库的需求，并大幅减少了XL仓库的使用量。仅将其应用于单个仓库，就实现了每年超过50,000美元的节约。随着我们进一步扩大WO和重新聚类的使用，这种影响将会进一步扩大。
引入WO后单仓库每日Snowflake支出变化。（一种工具或软件）
而最棒的部分是？WO 是开源的。我们已经发布了 WO，你可以把它集成到你自己的 dbt 项目中，并根据你的 Snowflake 环境进行定制。
我们非常愿意听到您的反馈——无论是关于性能改进、未来改进的想法，还是边缘情况。告诉我们它对您来说使用起来怎么样，谢谢！
共同学习，写下你的评论
评论加载中...
作者其他优质文章
