如何优化 Google Cloud BigQuery 并控制成本

我在 Google Cloud BigQuery 中的一个查询上不知不觉地在 6 小时内花费了 3,000 美元。以下是优化成本的原因和 3 个简单步骤。

在本文中

• 🙈 您将了解导致 BigQuery 成本的因素。
• 🏛️ 我们将深入探讨 BigQuery 的架构。
• 💰 您将学习 3 个简单的步骤，在您的下一份 Google Cloud 账单上节省 99.97%。

我们走吧。

它是怎么发生的？

我正在开发一个脚本，为前来咨询的客户准备数据样本。示例在每个文件中有 100 行，它们被分成 55 个语言环境。我的查询看起来像这样

SELECT * FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;

数据存储在“europe-west-4”中，查询价格为每 TB 6 美元。因此，通过运行脚本，我处理了：

• 总共 500 TB 的数据

• 每个国家平均 3 TB 的数据

• 平均每个数据样本文件 54 美元

非常贵。

花费 3,000 美元的剧本

该脚本是用JavaScript 模块编写的。

 // bq-samples.mjs import { BigQuery } from "@google-cloud/bigquery"; import { Parser } from "@json2csv/plainjs"; import makeDir from "make-dir"; import { write } from "./write.mjs"; import { locales } from "./locales.mjs"; import { perf } from "./performance.mjs"; const q = (locale, start, end, limit) => `SELECT * FROM \`project.dataset.table\` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "${locale}" LIMIT ${limit}` async function main() { const timer = perf() const dir = await makeDir('samples') const bigquery = new BigQuery() const csvParser = new Parser({}) try { const jobs = locales.map((locale) => async () => { // get query result from BigQuery const [job] = await bigquery.createQueryJob({ query: q(locale, "2022-12-01", "2023-02-28", 100), }) const [rows] = await job.getQueryResults() // parse rows into csv format const csv = parse(csvParser, rows) // write data into csv files and store in the file system await write(csv, dir, locale, "2022-12-01", "2023-02-28", 100) }) await Promise.all(jobs.map((job) => job())) console.log(`✨ Done in ${timer.stop()} seconds.`) } catch (error) { console.error('❌ Failed to create sample file', error) } } await main()

它为每个语言环境生成一个CSV格式的示例文件。这个过程很简单：

• 使用本地、开始日期、结束日期和限制查询 BigQuery 表。

• 将查询结果转换为 CSV 格式。

• 在文件系统中写入 CSV。

• 对所有语言环境重复该过程。

有什么问题？

事实证明，我在查询中做错了几件事。如果您再次查看定价模型，您会发现成本仅与您处理的数据量有关。所以很明显，我的查询查找了太多数据，无法生成 100 行。

有了这个洞察力，让我们逐步优化查询。

不要选择 *

这有点违反直觉。为什么我的 select 语句与其处理的数据量有关系？无论我选择哪一列，我都应该从相同的资源和数据中读取，对吗？

这仅适用于面向行的数据库。

BigQuery 实际上是一个列式数据库。它是面向列的，这意味着数据按列结构化。 BigQuery 使用Dremel作为其底层计算引擎。当数据从冷存储移动到 Dremel 中的活动存储时，它以树结构存储数据。

每个叶节点都是Protobuf格式的面向列的“记录”。

在 BigQuery 中，每个节点都是一个虚拟机。查询执行从根服务器（节点）通过中间服务器传播到叶服务器以检索选定的列。

我们可以修改查询以选择单个列：

 SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts BETWEEN TIMESTAMP("2022-12-01") AND TIMESTAMP("2023-02-28") AND locale = "US" LIMIT 100;

只需明确选择所有列，我就能将处理的数据从 3.08 TB 减少到 2.94 TB。减少了 100 GB 。

使用分区表并仅查询数据子集

Google Cloud 建议我们按日期对表进行分区。它让我们只查询数据的一个子集。

为了进一步优化查询，我们可以缩小 where 语句中的日期范围，因为该表是由“ts”列分区的。

 SELECT session_info_1, session_info_2, session_info_3, user_info_1, user_info_2, user_info_3, query_info_1, query_info_2, query_info_3, impression_info_1, impression_info_2, impression_info_3, ts FROM `project.dataset.table` WHERE ts = TIMESTAMP("2022-12-01") AND locale = "US" LIMIT 100;

我将日期范围缩小到一天而不是三个月。我能够将处理后的数据减少到37.43 GB 。它只是原始查询的一小部分。

分阶段使用物化查询结果

另一种降低成本的方法是减少您查询的数据集。 BigQuery 提供目标表以将查询结果存储为较小的数据集。目标表有两种形式：临时的和永久的。

因为临时表是有生命周期的，并不是为了共享和查询而设计的，所以我创建了一个永久目标表来具体化查询结果：

 // bq-samples.mjs const dataset = bigquery.dataset('materialized_dataset') const materialzedTable = dataset.table('materialized_table') // ... const [job] = await bigquery.createQueryJob({ query: q(locale, '2022-12-01', '2023-02-28', 100), destination: materialzedTable, })

查询结果将存储在目标表中。它将作为以后查询的参考。只要可以从目标表进行查询，BigQuery 就会处理该表中的数据。它将大大减少我们查找的数据大小。

最后的想法

在 BigQuery 中降低成本是一项非常有趣的研究。只需三个简单的步骤：

• 不要使用 *

• 使用分区表并仅查询数据子集

• 分阶段使用物化查询结果

我能够将处理后的数据大小从 3 TB 减少到 37.5 GB 。它将总成本从 3,000 美元显着降低到 30 美元。

如果您有兴趣了解有关 BigQuery 架构的更多信息，以下是对我有帮助的参考资料：

• BigQuery 解释：BigQuery 架构概述

• 看看Dremel

• 引擎盖下的巨像：一窥谷歌的可扩展存储系统

• 木星进化：反思谷歌的数据中心网络转型

您可以在 Google Cloud 文档中阅读有关BigQuery 成本优化的更多信息。

特别感谢Abu Nashir在案例研究方面与我合作并提供了宝贵的见解，帮助我了解 BigQuery 的架构。

参考

• Abu Nashir ——LinkedIn
• 看看 Dremel ——Peter Goldsborough
• BigQuery——谷歌云
• BigQuery 解释：BigQuery 架构概述— Google Cloud
• 引擎盖下的巨人：一窥谷歌的可扩展存储系统——谷歌云
• 面向列的 DBMS — 维基百科
• 逗号分隔值——维基百科
• 创建分区表— Google Cloud
• JavaScript 模块— MDN
• Jupiter 进化：反思 Google 的数据中心网络转型— Google Cloud
• 方法：jobs.query——谷歌云
• Protocol_Buffers——维基百科
• 运行交互式和批量查询作业— Google Cloud
• 使用缓存的查询结果— Google Cloud
• 编写查询结果——谷歌云

想要连接？

本文原载于道志网站。