如何降低 BigQuery 费用?
BigQuery 成本优化的必要性
BigQuery 是一款强大的数据仓库工具，广受工程团队青睐，尤其适用于处理海量数据的场景。
然而，与所有云的服务一样，BigQuery 也是按使用量收费，如果管理不善，费用可能会迅速攀升，导致超支问题。根据 2023 年 Oomnitza 调查报告，有 53% 的受访者表示至少 10% 的年度预算被浪费在未充分利用、未管理或未使用的云资源上；19% 的受访者更是浪费了至少 20% 的预算。这意味着大量资金被浪费，本可以用于其他更重要的业务优先事项。随着 BigQuery 等云服务的广泛应用，实施成本优化策略变得尤为关键，以确保云资源的高效利用，避免不必要的开销。
在本文中，我们将探讨优化 BigQuery 费用的最佳实践，包括数据存储管理和查询优化策略。
了解 BigQuery 计费和账单体系
2023年3月29日，Google 宣布推出全新的 BigQuery 计费模式，其中包括一项新的“灵活”定价选项（Flex Pricing），旨在简化定价流程，提高可预测性。Flex 选项允许用户以更小的增量购买 BigQuery 计算资源，从而逐步适应平台，降低成本波动的风险。
🔗 参考文档：Google 官方博客：Introducing New BigQuery Pricing Editions
BigQuery 定价版本
Google 目前提供 三种 BigQuery 计费方案，以满足不同规模企业的需求：
| 版本 | 特点 | 适用对象 |
|---|---|---|
| 标准版（Standard Edition） | - 提供核心 BigQuery 功能，价格更具竞争力 - 适用于小型到中型企业，尤其是临时开发和测试工作负载 | 适合初创企业或中小企业 |
| 企业版（Enterprise Edition） | - 支持高级安全性与合规功能 - 专为大型企业设计，适用于处理敏感数据或需满足复杂监管要求的场景 - 提供强大的机器学习和数据管理能力 | 适合具有严格合规性要求的企业 |
| 企业增强版（Enterprise Plus Edition） | - 包含前两个版本的所有功能 - 适用于关键任务型工作负载，提供高级技术支持 - 适合对高可用性、高容错性和快速恢复有较高要求的客户 | 适合超大规模数据处理、金融或医疗等对可靠性要求极高的行业 |
BigQuery 计费组件解析
让我们用一个示例来更直观地了解 GCP Cloud Logging 的计费方式：
假设您的 Web 应用 每月生成 500GB 日志数据，则数据写入（Ingestion）费用计算如下：
- 计费标准为 $0.50/GB，因此 500GB 的日志写入费用为 $250。
- GCP 提供 50GB 免费额度，因此实际费用为 $250 - $25（50GB 免费额度）= $225。
如果您希望 保留数据 90 天，则存储费用计算如下：
- 前 30 天存储免费，之后 60 天按照 $0.01/GB 计算。
- 450GB × $0.01 × 60 天 = $45。
最终，GCP Cloud Logging 的总费用为 $225 + $45 = $295/月。
BigQuery 主要成本构成
在使用 BigQuery 时，有两个主要的成本驱动因素，需要纳入预算考虑：
1. 计算成本（Computing Costs）
BigQuery 提供两种计算计费模式：
| 计费模式 | 描述 | 适用场景 |
|---|---|---|
| 按需计费（On-Demand） | 按每个查询处理的数据量计费（四舍五入到最近的 MB）。$5/TB，最低 10MB 计费。每月 前 1TB 免费 | 查询量不稳定，使用模式不规律的业务 |
| 容量计费（Capacity） | 按查询计算资源（Slot, 即虚拟 CPU）的使用量计费，按时间计算 | 查询量稳定、使用模式可预测的业务 |
📌 选择建议：
- 小型团队或偶发查询：推荐 按需计费（前 1TB 免费）。
- 固定大规模查询：推荐 容量计费，可以 控制预算，避免高额查询费用。
2. 存储成本（Storage Costs）
BigQuery 存储费用 主要基于 逻辑存储（Logical Storage），分为两种类型：
| 存储类型 | 描述 | 费用 |
|---|---|---|
| 活跃存储（Active Storage） | 90 天内 修改的数据 | 标准价格 |
| 长期存储（Long-Term Storage） | 超过 90 天未修改的数据 | 活跃存储费用的 50% |
📌 存储计费的关键点：
- BigQuery 存储费用基于逻辑存储，而非物理存储，因此在 成本估算时需额外考虑压缩比率。
- 数据导入 BigQuery 后即开始计费，计费基于 存储表中的未压缩数据量。
🔗 更多详情：BigQuery 数据类型存储大小
成本优化最佳实践
虽然 Google 提供了一些减少 BigQuery 开销的推荐做法，但别担心，我们还将为您提供一些额外的建议，帮助您进一步优化开销。
1. 查询成本控制
减少 BigQuery 需要处理的数据量是降低成本和提高性能的最简单方法之一。假设您有一个包含客户交易数据的大数据集，并且您希望计算每位客户的平均交易额。您可能会使用如下查询：
SELECT customer_location, AVG(transaction_value)
FROM customer_transactions
GROUP BY customer_location但如果您的数据集非常庞大，这个查询可能会非常昂贵。为了降低开销，您可以修改查询，只选择必要的列，并按日期范围过滤数据：
SELECT customer_location, AVG(transaction_value)
FROM customer_transactions
WHERE transaction_date BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY customer_location通过指定日期范围，您只处理数据集的子集，这样就可以大大减少查询的数据量，从而降低成本。
2. 避免 SQL 反模式（Anti-Patterns）
反模式是与最佳实践相对的做法，它们可能导致查询性能低下、查询成本增加以及用户体验不佳。为了优化查询性能并降低成本，您应该识别并避免这些反模式。
避免使用 SELECT * 查询
其中一个常见的反模式是使用 SELECT * 查询。这种查询会增加扫描的数据量，负面影响查询性能。理想的做法是只选择您需要分析的必要列。
确保在运行查询之前检查处理的数据量
这有助于了解查询的资源消耗，避免不必要的开销。
3. 优化 JOIN 操作
在 BigQuery 中，JOIN 操作可能是一个昂贵的操作，尤其是在处理大表时。如果在进行连接操作时没有先优化查询，可能会导致大量无关数据被扫描。例如，连接一个 10亿行 的表和一个 5亿行 的表，如果没有优化，查询会扫描所有 15亿行，即便只需要其中的一个小数据集。
为了优化查询，您可以在连接的关键字段上对两个表进行排序或分区（Cluster），并且 仅使用较小的数据子集来进行连接操作。这样做可以显著减少扫描的数据量、提高查询性能并降低成本。
4. 正确使用 LIMIT 子句
每次使用 LIMIT 子句时，整个表仍然会被扫描，查询的计费是基于读取的总数据量，而不是 LIMIT 返回的行数。例如，如果您运行以下查询：
SELECT * FROM my_table LIMIT 100BigQuery 仍然会扫描整个 my_table 表，您将根据表中所有字节的读取量来收费，无论查询返回 100 行还是整个表。同理，使用 LIMIT 1 也会产生与 LIMIT 100 相同的费用。
5. 限制最大字节数
为了避免不必要的开销，您可以使用查询设置中的 “最大字节数” 功能来限制单个查询读取和计费的数据量。这能有效避免查询在未优化的情况下扫描过多的数据，帮助您节省成本。
查询性能优化
现在，您已经了解了需要避免的反模式，我们来看看如何分析和优化查询性能的一些方法。
1. 查询计划分析
查询计划分析是 BigQuery 的一项功能，它允许您查看并理解查询的执行计划。执行计划详细列出了 BigQuery 执行查询的步骤，包括操作的顺序、所需的资源和估算的费用。
要在 Google Cloud 控制台中访问已完成查询的查询计划详细信息，您需要点击 “执行详细信息” 按钮，这个按钮位于 结果按钮 附近。
2. 缓存预热
为了提高查询性能，BigQuery 会缓存频繁访问的数据。问题是，首次执行查询时，缓存可能会是冷缓存，从而导致查询时间变长。
缓存预热是一种技术，通过提前运行查询来预热缓存，确保在实际查询执行之前，数据已经被缓存。这种技术对于需要快速响应时间的仪表盘查询特别有用。例如，以下查询将预热给定查询的缓存：
SELECT COUNT(*) FROM mydataset.mytable WHERE date = '2022-01-01'3. 表抽样
数据抽样是评估查询运行时间的另一种方法。您只需在 SQL 查询中添加 TABLESAMPLE 子句，就可以测试数据集的一个子集。
SELECT * FROM mydataset.mytable TABLESAMPLE SYSTEM (10 PERCENT)这种方法有助于您评估查询在较小数据集上的运行效率，从而预测在完整数据集上执行查询时的表现。
4. 数据处理优化
数据处理是 BigQuery 性能的关键环节，下面是一些不影响性能的节省开支的方法。
分区表
分区是将大表按照某一列（称为分区列）划分成较小的、更易管理的部分。通过分区，BigQuery 可以只扫描相关的分区，而不是整个表，从而减少查询处理时间和成本。
例如，如果您有一个包含多年销售数据的表，您可以按年份分区。这样，当您运行查询获取某一年的销售数据时，BigQuery 只需要扫描该年的分区，而不是扫描整个表。
🔗 详情：BigQuery 分区表文档
优化数据加载
通过优化数据加载，您可以提高 BigQuery 的数据加载效率。以下是一些常见的技术：
- 使用流式插入：流式插入允许您实时将数据插入 BigQuery。
- 使用批量插入：批量插入将多个数据插入操作组合在一起，并一次性加载到 BigQuery。
- 使用加载作业：加载作业允许您将数据从 Google Cloud Storage、Google Drive 或本地文件系统加载到 BigQuery。
表过期时间
为表定义适当的过期时间可以帮助您设置数据在表中保存的时长限制，从而删除不再需要的数据，减少需要处理的数据量。例如，如果您有一个包含过去 5 年数据的表，您可以将过期时间设置为 6 年。这样，6 年前的数据将自动删除，从而减少需要处理或存储的数据量。
性价比高的存储选项
BigQuery 提供了三种存储层级：
- 多区域存储：最适合需要高可用性、经常访问的数据。
- 区域存储：成本较低，提供单一区域内的数据访问。
- Coldline 存储：最便宜，适合长期存储不常访问的数据，如归档或备份。
确保根据您的数据存储需求选择合适的存储层级，因为每个层级在耐久性、可用性和成本上都有所不同。
使用 FinOps 平台减少支出
像 MofCloud 这样的 FinOps 平台是云成本管理工具，可以帮助您优化跨多个云服务提供商（包括 BigQuery）的云支出。FinOps 平台的一些关键功能包括：
- 成本分配：FinOps 平台提供详细的成本分配报告，使用户能够了解云支出，并识别可以减少成本的领域。
- 资源优化：FinOps 平台使用机器学习算法识别未使用或未充分利用的云资源，并建议优化措施以减少成本。这些优化措施可能包括调整实例大小、关闭实例、改变存储选项或使用抢占实例。
- 预算制定和预测：FinOps 平台使用户能够根据历史使用情况和趋势设定预算并预测未来的云支出。这有助于用户更有效地规划和分配云支出，避免意外的费用。
- 自动化：FinOps 平台提供自动化功能，以优化云资源、跟踪支出，并实时生成警报，以便对改进领域采取措施。
通过使用 FinOps 平台，用户能够通过提供有价值的洞察、建议和自动化，优化 BigQuery 的使用并减少成本，从而更高效地管理云支出。
总结：降低 BigQuery 费用的关键策略
我们已经讨论了一些开始减少 BigQuery 支出的最简单方法。正如您所看到的，这些方法大多数都关注于减少存储或处理的数据量，从而避免不必要的费用。当然，还有其他一些最佳实践您可以进一步研究，更重要的是，您应该进行实验，看看哪些方法有效，哪些则不适合。
如果您正在寻找一款能够为您减轻负担的工具，不妨试试 mofcloud。我们通过 FinOps 认证的平台将为您提供详细的成本分析报告，并提供减少 BigQuery 支出的建议。借助 MofCloud 的智能账单，您可获得 100% 云成本可见性。立即 预约演示，开启您的节省之旅！
联系我们
有任何云成本管理的需求或问题？欢迎通过以下方式联系我们！
公众号
企业微信客服
业务咨询
技术社区
地址
北京市海淀区自主创新大厦 5层
