我在 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 使用缓存的查询结果 ——谷歌云 编写查询结果 想要连接? 本文原载于 。 道志网站