さあ行こう。
私は、相談を受けた顧客のためにデータ サンプルを準備するためのスクリプトを開発していました。サンプルは各ファイルに 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 ドルです。そのため、スクリプトを実行して、次の処理を行いました。
非常に高価です。
スクリプトは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形式で生成されます。プロセスは簡単です。
クエリでいくつか間違ったことをしたことがわかりました。価格モデルをもう一度見てみると、コストは処理するデータの量にのみ関連していることがわかります。したがって、私のクエリが検索したデータが多すぎて 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 はテーブルのデータを処理します。これにより、検索するデータ サイズが大幅に削減されます。
BigQuery でコストを削減することは非常に興味深い研究です。わずか 3 つの簡単な手順で:
処理したデータ サイズを3 TB から 37.5 GB に減らすことができました。これにより、総コストが 3,000 ドルから 30 ドルに大幅に削減されます。
BigQuery アーキテクチャについて詳しく知りたい場合は、次の参考文献を参考にしてください。
BigQuery の費用の最適化について詳しくは、Google Cloud のドキュメントをご覧ください。
ケーススタディで協力し、BigQuery のアーキテクチャを理解するのに役立つ貴重な洞察を提供してくれたAbu Nashirに特に感謝します。
接続したいですか?
この記事は、もともとDaw-Chih の Web サイトに投稿されたものです。