3,205 測定値

Google Cloud BigQuery を最適化し、費用を抑える方法

に Daw-Chih Liou6m2023/04/18

長すぎる; 読むには

🙈 BigQuery のコストに影響する要因について学びます。 🏛️ BigQuery のアーキテクチャについて詳しく説明します。 💰 次の Google Cloud 請求書で 99.97% 節約するための 3 つの簡単なステップを学びます。

featured image - Google Cloud BigQuery を最適化し、費用を抑える方法

Google Cloud BigQuery の 1 つのクエリで、知らず知らずのうちに 6 時間で $3,000 を吹き飛ばしてしまいました。その理由と、コストを最適化するための 3 つの簡単なステップを次に示します。

記事上で

🙈 BigQuery のコストに影響する要素について学びます。
🏛️ BigQuery のアーキテクチャについて詳しく説明します。
💰 次の Google Cloud 請求書で 99.97% 節約するための 3 つの簡単なステップを学びます。

さあ行こう。

どうやってそうなった？

私は、相談を受けた顧客のためにデータサンプルを準備するためのスクリプトを開発していました。サンプルは各ファイルに 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 のデータ

平均で 1 か国あたり 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()

ロケールごとに 1 つのサンプルファイルがCSV形式で生成されます。プロセスは簡単です。

ローカル、開始日、終了日、制限を使用して BigQuery テーブルをクエリします。

クエリ結果を CSV 形式に変換します。

ファイルシステムに CSV を書き込みます。

すべてのロケールに対してプロセスを繰り返します。

どうしたの？

クエリでいくつか間違ったことをしたことがわかりました。価格モデルをもう一度見てみると、コストは処理するデータの量にのみ関連していることがわかります。したがって、私のクエリが検索したデータが多すぎて 100 行を生成できないことは明らかです。

この洞察を使用して、クエリを段階的に最適化しましょう。

選択しないでください *

それは少し直感に反しています。 select ステートメントが処理するデータの量と関係があるのはなぜですか?選択した列に関係なく、同じリソースとデータから読み取る必要がありますよね?

これは、行指向のデータベースにのみ当てはまります。

BigQuery は、実際にはカラムナデータベースです。これは列指向であり、データが列で構造化されていることを意味します。 BigQuery は、基盤となるコンピューティングエンジンとしてDremelを使用します。データがコールドストレージから Dremel のアクティブストレージに移動されると、データはツリー構造で保存されます。

各リーフノードは、 Protobuf形式の列指向の「レコード」です。

BigQuery では、各ノードが VM です。クエリの実行は、ルートサーバー (ノード) から中間サーバーを介してリーフサーバーに伝達され、選択された列が取得されます。

個々の列を選択するようにクエリを変更できます。

 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 では、テーブルを日付で分割することを推奨しています。データのサブセットのみを照会できます。

クエリをさらに最適化するために、テーブルが「ts」列によって分割されているため、where ステートメントで日付範囲を絞り込むことができます。

 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;

日付範囲を 3 か月ではなく 1 日に絞り込みました。処理したデータを37.43 GBに削減できました。これは、元のクエリのほんの一部です。

マテリアライズされたクエリ結果を段階的に使用する

コストを削減するもう 1 つの方法は、クエリを実行するデータセットを減らすことです。 BigQuery は、クエリ結果を小さなデータセットとして保存する宛先テーブルを提供します。宛先テーブルには、一時テーブルと永続テーブルの 2 つの形式があります。

一時テーブルには有効期間があり、共有してクエリを実行するように設計されていないため、クエリ結果を具体化するために永続的な宛先テーブルを作成しました。

 // 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 はテーブルのデータを処理します。これにより、検索するデータサイズが大幅に削減されます。