我在 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,000 美元显着降低到 30 美元。 从 3 TB 减少到 37.5 GB 如果您有兴趣了解有关 BigQuery 架构的更多信息，以下是对我有帮助的参考资料： BigQuery 解释：BigQuery 架构概述 看看Dremel 引擎盖下的巨像：一窥谷歌的可扩展存储系统 木星进化：反思谷歌的数据中心网络转型 您可以在 Google Cloud 文档中阅读有关 更多信息。 BigQuery 成本优化的 特别感谢 在案例研究方面与我合作并提供了宝贵的见解，帮助我了解 BigQuery 的架构。 Abu Nashir 参考 ——LinkedIn Abu Nashir ——Peter Goldsborough 看看 Dremel 谷歌云 BigQuery—— — Google Cloud BigQuery 解释：BigQuery 架构概述 ——谷歌云 引擎盖下的巨人：一窥谷歌的可扩展存储系统 — 维基百科 面向列的 DBMS ——维基百科 逗号分隔值 — Google Cloud 创建分区表 — MDN JavaScript 模块 — Google Cloud Jupiter 进化：反思 Google 的数据中心网络转型 谷歌云 方法：jobs.query—— 维基百科 Protocol_Buffers—— — Google Cloud 运行交互式和批量查询作业 — Google Cloud 使用缓存的查询结果 ——谷歌云 编写查询结果 想要连接？ 本文原载于 。 道志网站

2022 - HackerNoon Contributor of the Year - Leadership

2022 - HackerNoon Contributor of the Year - Learning

2022 - HackerNoon Contributor of the Year - Product Management

2022 - HackerNoon Contributor of the Year - Software Engineering

2022 - HackerNoon Contributor of the Year - Typescript

Coding Guru of 2022

Check out my website!

Nominated for 2022 - HackerNoon Contributor of the Year - Product Management

Nominated for 2022 - HackerNoon Contributor of the Year - Typescript

Nominated for 2022 - HackerNoon Contributor of the Year - Leadership

Nominated for 2022 - HackerNoon Contributor of the Year - Software Engineering

Nominated for Coding Guru of 2022

Nominated for 2022 - HackerNoon Contributor of the Year - Learning

該音頻是用故事的原始語言製作的！

太長; 讀書

Making security fun one episode at a time

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

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

太長; 讀書

Daw-Chih Liou

About Author

標籤

Languages

这篇文章刊登在...

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

太長; 讀書

Daw-Chih Liou

About Author

標籤

Languages

这篇文章刊登在...

相關故事