paint-brush
Google Cloud BigQuery を最適化し、費用を抑える方法@dawchihliou
3,205 測定値
3,205 測定値

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

Daw-Chih Liou6m2023/04/18
Read on Terminal Reader

長すぎる; 読むには

🙈 BigQuery のコストに影響する要因について学びます。 🏛️ BigQuery のアーキテクチャについて詳しく説明します。 💰 次の Google Cloud 請求書で 99.97% 節約するための 3 つの簡単なステップを学びます。
featured image - Google Cloud BigQuery を最適化し、費用を抑える方法
Daw-Chih Liou HackerNoon profile picture

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形式の列指向の「レコード」です。


Dremel データ構造の概要


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

最終的な考え

BigQuery でコストを削減することは非常に興味深い研究です。わずか 3 つの簡単な手順で:


  • 使用しないでください *


  • 分割テーブルを使用し、データのサブセットのみをクエリする


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


処理したデータ サイズを3 TB から 37.5 GB に減らすことができました。これにより、総コストが 3,000 ドルから 30 ドルに大幅に削減されます。


BigQuery アーキテクチャについて詳しく知りたい場合は、次の参考文献を参考にしてください。






BigQuery の費用の最適化について詳しくは、Google Cloud のドキュメントをご覧ください。


ケーススタディで協力し、BigQuery のアーキテクチャを理解するのに役立つ貴重な洞察を提供してくれたAbu Nashirに特に感謝します。

参考文献


接続したいですか?

この記事は、もともとDaw-Chih の Web サイトに投稿されたものです。