概要

BigQuery のパフォーマンス チューニングは通常、クエリ実行時間の短縮やコストの削減を目的として行われます。このラボでは、各種のユースケースに適したさまざまなパフォーマンス最適化方法について説明します。パフォーマンス チューニングは、通常のクエリでは時間がかかりすぎることが判明した場合にのみ、開発段階の最後に行います。

一般的には、わずかなパフォーマンス向上のためにわかりづらいテーブル レイアウトとクエリにするよりも、柔軟なテーブル スキーマと洗練され読みやすく保守性の高いクエリを維持するほうが、はるかに大きなメリットがあります。ただし、実行頻度が高くわずかな改善でも効果があるクエリの場合など、一部のケースでは、パフォーマンスの向上が必要になることがあります。また、パフォーマンス上のトレードオフを理解しておくことで、複数の設計方法から最適なものを見極めることができます。

目標

このラボでは、BigQuery の実行時間を短縮し、コストを削減するための以下のテクニックについて学びます。

• I/O の最小化
• 過去のクエリ結果のキャッシュ
• 効率的な結合の実行
• 単一ワーカーの過負荷の回避
• 近似集計関数の使用

設定と要件

各ラボでは、新しい Google Cloud プロジェクトとリソースセットを一定時間無料で利用できます。

1. Qwiklabs にシークレット ウィンドウでログインします。

2. ラボのアクセス時間（例: 1:15:00）に注意し、時間内に完了できるようにしてください。
   一時停止機能はありません。必要な場合はやり直せますが、最初からになります。

3. 準備ができたら、[ラボを開始] をクリックします。

4. ラボの認証情報（ユーザー名とパスワード）をメモしておきます。この情報は、Google Cloud Console にログインする際に使用します。

5. [Google Console を開く] をクリックします。

6. [別のアカウントを使用] をクリックし、このラボの認証情報をコピーしてプロンプトに貼り付けます。
   他の認証情報を使用すると、エラーが発生したり、料金の請求が発生したりします。

7. 利用規約に同意し、再設定用のリソースページをスキップします。

BigQuery コンソールを開く

1. Google Cloud Console で、ナビゲーション メニュー > [BigQuery] を選択します。

[Cloud Console の BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスにはクイックスタート ガイドへのリンクと、UI の更新情報が表示されます。

2. [完了] をクリックします。

タスク 1. I/O の最小化

3 つの列の合計を計算するクエリは、2 つの列の合計を計算するクエリよりも時間がかかりますが、パフォーマンスの差異のほとんどは加算処理が多いことよりも、読み取るデータが増えることに起因します。したがって、列の平均を計算するクエリと、データを集計して分散を計算するクエリの速度はほとんど変わりません。分散を計算するには BigQuery で合計と二乗の合計の両方が必要になるとはいえ、単純なクエリのオーバーヘッドのほとんどは計算ではなく I/O によるものだからです。

目的を明確にして SELECT を使用する

BigQuery は列指向のファイル形式を使用するため、SELECT ステートメントで読み取る列の数が少ないほど、読み取るデータ量を減らすことができます。特に、SELECT * を使用すると、テーブルのすべての行のすべての列が読み取られるため、実行に長い時間がかかり、コストが高くなります。

例外として、サブクエリで SELECT * を使用し、外側のクエリで一部のフィールドのみを参照するようにすると、BigQuery オプティマイザーは処理に必要な列のみを読み取ります。

1. BigQuery エディタ ウィンドウで以下のクエリを実行します。

SELECT bike_id, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

[クエリ結果] ウィンドウを見ると、クエリの所要時間は約 1.2 秒で、約 372 MB のデータが処理されていることがわかります。

2. BigQuery エディタ ウィンドウで以下のクエリを実行します。

SELECT * FROM `bigquery-public-data`.london_bicycles.cycle_hire ORDER BY duration DESC LIMIT 1

[クエリ結果] ウィンドウを見ると、このクエリの所要時間は約 4.5 秒で、約 2.6 GB のデータが処理されていることがわかります。先ほどよりもかなり長い時間がかかっています。

テーブルのほぼすべての列が必要な場合は、不要な列を読み取らないように SELECT * EXCEPT を使用すると効果的です。

注: BigQuery は繰り返し実行するクエリの速度を上げるために、クエリ結果をキャッシュに保存します。[展開] > [クエリを設定] をクリックし、[キャッシュされた結果を使用] チェックボックスをオフにしてこのキャッシュを無効にすると、クエリ処理の実際のパフォーマンスを確認できます。

読み取るデータ量を減らす

クエリのチューニングを行うときにはまず、読み取るデータ量を減らすことができないか検討することが重要です。よく利用される片道レンタル自転車の一般的な利用時間を調べるとします。

1. BigQuery エディタ ウィンドウで以下のクエリを実行します。

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

2. [クエリ結果] ウィンドウの [実行の詳細] タブをクリックします。

クエリの詳細から、（各ステーション ペアの近似分位数の）並べ替えに入力ステージの出力の再パーティショニングが必要だったことがわかりますが、ほとんどの時間はコンピューティングに費やされています。

3. ステーション ID ではなくステーション名を使用してフィルタリングとグループ化を行うと、読み取る列の数が少なくなるため、クエリの I/O のオーバーヘッドを減らすことができます。以下のクエリを実行します。

SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET(5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE start_station_name != end_station_name GROUP BY start_station_name, end_station_name ORDER BY num_trips DESC LIMIT 10

上のクエリは、2 つの ID 列を読み取る必要がなくなったため、約 10.8 秒で実行できました。この時間の短縮は、データ読み取り量を減らしたことによるものです。

ステーション名とステーション ID は 1:1 の関係になっているため、クエリ結果は同じものになります。

コストの高い計算の数を減らす

データセット内の各自転車の合計走行距離を計算したいとします。

1. 単純にこれを計算する場合は、各自転車がレンタル 1 回ごとに走った距離を照会して合計します。

WITH trip_distance AS ( SELECT bike_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e WHERE start_station_id = s.id AND end_station_id = e.id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

上のクエリには約 9.8 秒かかり（スロット時間は約 55 秒）、約 1.22 MB のデータがシャッフルされています。一部の自転車は 6,000 km 近く走行していることがわかりました。

2. 距離の計算はコストのかかるオペレーションであるため、すべてのステーション ペア間の距離をあらかじめ計算しておくと、cycle_stations テーブルと cycle_hire テーブルに結合せずに済みます。

WITH stations AS ( SELECT s.id AS start_id, e.id AS end_id, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations s, `bigquery-public-data`.london_bicycles.cycle_stations e ), trip_distance AS ( SELECT bike_id, distance FROM `bigquery-public-data`.london_bicycles.cycle_hire, stations WHERE start_station_id = start_id AND end_station_id = end_id ) SELECT bike_id, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bike_id ORDER BY total_distance DESC LIMIT 5

このクエリでは、60 万件の地理的距離の計算のみを行います（以前は 2,400 万件）。今度は、約 33.05 MB のデータをシャッフルしたにもかかわらず、スロット時間は約 31.5 秒となりました（30% の高速化）。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。

I/O の最小化

タスク 2. 過去のクエリ結果のキャッシュ

BigQuery サービスは、クエリ結果を一時テーブルに自動的にキャッシュします。およそ 24 時間以内に同一のクエリが送信されると、この一時テーブルから結果が返され、再計算は行われません。キャッシュされた結果の取得は非常に高速で料金もかかりません。

ただし、いくつか注意点もあります。クエリのキャッシュは、文字列を厳密に比較することで行われます。そのため、空白文字の違いだけでもキャッシュミスとなることがあります。クエリ結果が変動する場合（CURRENT_TIMESTAMP や RAND を使用している場合など）、クエリ対象のテーブルやビューが変更された場合（クエリ対象の列または行が変更されていない場合を含む）、テーブルがストリーミング バッファに関連付けられている場合（新しい行がない場合を含む）、クエリが DML ステートメントを使用している場合、または外部データソースに対してクエリが行われる場合、クエリはキャッシュされません。

中間の結果をキャッシュする

一時テーブルとマテリアライズド ビューを利用すると、I/O の増加というトレードオフはあるものの、全体的なパフォーマンスを高めることができます。

たとえば、ステーション ペア間の一般的なレンタル利用時間を冒頭で求める形式のクエリが多数あるとします。WITH 句（共通テーブル式とも呼ばれます）を使用すると読みやすくなりますが、結果がキャッシュされないためクエリの速度向上やコスト低下にはつながりません。同じことがビューやサブクエリにもあてはまります。WITH 句、ビュー、サブクエリを多用している場合は、結果をテーブル（またはマテリアライズド ビュー）に保存するとパフォーマンスが向上する可能性があります。

1. まず、BigQuery のプロジェクトの下にある [eu (EU の複数のリージョン)] リージョン（自転車のデータがある場所）に mydataset という名前のデータセットを作成する必要があります。

• [エクスプローラ] の左側のペインで、BigQuery プロジェクト（qwiklabs-gcp-xxxx）の近くにある [アクションを表示] アイコン（3 つの点）をクリックして [データセットを作成] を選択します。

[データセットを作成] ダイアログで、次の操作を行います。

• [データセット ID] に「mydataset」と入力します。

• [ロケーション タイプ] に [eu (EU の複数のリージョン)] を設定します。

• 他のオプションはすべてデフォルト値のままにします。

• 作成を完了するには、青色の [データセットを作成] ボタンをクリックします。

• これで、以下のクエリを実行できます。

  CREATE OR REPLACE TABLE mydataset.typical_trip AS SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name

2. 作成されたテーブルを使用して、自転車のレンタル時間が通常よりも大幅に長い日付を調べます。

SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[OFFSET(5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN mydataset.typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

3. WITH 句を使用して、自転車のレンタル時間が通常よりも大幅に長い日付を調べます。

WITH typical_trip AS ( SELECT start_station_name, end_station_name, APPROX_QUANTILES(duration, 10)[OFFSET (5)] AS typical_duration, COUNT(duration) AS num_trips FROM `bigquery-public-data`.london_bicycles.cycle_hire GROUP BY start_station_name, end_station_name ) SELECT EXTRACT (DATE FROM start_date) AS trip_date, APPROX_QUANTILES(duration / typical_duration, 10)[ OFFSET (5)] AS ratio, COUNT(*) AS num_trips_on_day FROM `bigquery-public-data`.london_bicycles.cycle_hire AS hire JOIN typical_trip AS trip ON hire.start_station_name = trip.start_station_name AND hire.end_station_name = trip.end_station_name AND num_trips > 10 GROUP BY trip_date HAVING num_trips_on_day > 10 ORDER BY ratio DESC LIMIT 10

平均レンタル時間の計算を省略できたため、約 50% の高速化となりました。いずれのクエリでも、クリスマスに通常よりもレンタル時間が長くなるという同じ結果が得られます。cycle_hire テーブルに新しいデータが追加された場合でも、mydataset.typical_trip テーブルは更新されないことに注意してください。

古いデータが残るこの問題を解決する方法として、マテリアライズド ビューを使用するか、クエリをスケジュールしてテーブルを定期的に更新できます。テーブルまたはマテリアライズド ビューを最新に保つための追加コストに見合うクエリ パフォーマンスの向上が得られるかどうかを確認するために、こうした更新に伴うコストを測定する必要があります。

BI Engine を使用してクエリを高速化する

集計やフィルタの機能を備えたダッシュボードなどのビジネス インテリジェンス（BI）環境で頻繁にアクセスするテーブルがある場合は、BI Engine を使用するとクエリを高速化できます。BI Engine は、関連性のあるデータ（テーブルの実際の列または取得された結果）をメモリ内に自動的に保存します。また、主にインメモリ データを処理するという目的に合わせて調整された特別なクエリ プロセッサを使用します。BigQuery の管理コンソールにある [BI Engine] で、BigQuery がキャッシュに使用するメモリ量を予約できます（現在予約できる最大サイズは 10 GB です）。

クエリ実行対象となるデータセットと同じリージョンにこのメモリを予約してください。これで、BigQuery はテーブル、テーブルの一部、集計結果をメモリにキャッシュし、結果を高速に返すようになります。

BI Engine の主な用途は、Google データポータルなどのダッシュボード ツールからアクセスするテーブルです。BI Engine の予約でメモリを割り当てると、BigQuery バックエンドを利用するダッシュボードの応答時間が非常に短くなります。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。

過去のクエリ結果のキャッシュ

タスク 3. 効率的な結合

2 つのテーブルを結合するには、データ調整が必要となり、スロット間の通信帯域幅による制限が発生することがあります。可能であれば、結合の使用を避けるか、結合するデータの量を減らすようにしてください。

非正規化

読み取りパフォーマンスを向上させ、結合を避ける方法として、効率的なデータの保存をあきらめてデータの冗長コピーを追加できます。この方法は非正規化と呼ばれます。

• 自転車ステーションの緯度と経度を自転車のレンタル情報とは別に保存する代わりに、非正規化したテーブルを作成することができます。

  CREATE OR REPLACE TABLE mydataset.london_bicycles_denorm AS SELECT start_station_id, s.latitude AS start_latitude, s.longitude AS start_longitude, end_station_id, e.latitude AS end_latitude, e.longitude AS end_longitude FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s ON h.start_station_id = s.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS e ON h.end_station_id = e.id

  このテーブルにはすべてのレンタルに関する必要な位置情報が含まれることになるため、後続のすべてのクエリで結合を行う必要がなくなります。

  この場合、追加のデータを保存して読み取るコストと、結合の計算コストのトレードオフが発生します。ディスクから追加のデータを読み取るコストが、結合のコストよりも大きくなることも十分あり得るため、非正規化によってパフォーマンス上のメリットがあるかどうかを測定する必要があります。

  [進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。

  非正規化

大きなテーブルの自己結合を使用しない

テーブルをそのテーブル自体と結合するのが自己結合です。BigQuery は自己結合をサポートしていますが、自己結合するテーブルのサイズが非常に大きいとパフォーマンス低下につながることがあります。多くの場合、集計関数やウィンドウ関数などの SQL の機能を使用すると、自己結合を避けることができます。

例を見てみましょう。BigQuery 一般公開データセットの一つに、米国社会保障局が公表している新生児の名前のデータセットがあります。

1. データセットに対してクエリを実行して、2015 年にマサチューセッツ州で最も人気のあった男児の名前を検索することができます。クエリは米国（[米国の複数のリージョン]）で実行されるようにします。[+SQL クエリを作成] をクリックしてから [展開] > [クエリ設定] > [詳細オプション] を選択し、[自動ロケーション選択] のチェックボックスをオフにして [マルチリージョン] を選択し、[US (米国の複数のリージョン)] を選択するか、クエリを終了して [自動ロケーション選択] を選択します。[保存] をクリックして保存します。

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

2. 同様に、データセットに対してクエリを実行し、2015 年にマサチューセッツ州で最も人気のあった女児の名前を検索します。

SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' AND year = 2015 AND state = 'MA' ORDER BY num_babies DESC LIMIT 5

3. データセットに含まれるすべての年度を見たときに、全国で男児と女児に付けられた最も人気のある名前は何でしょう。この問題を解決するための単純な方法は、入力テーブルを 2 回読み取って、自己結合を行うことです。

WITH male_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'M' ), female_babies AS ( SELECT name, number AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = 'F' ), both_genders AS ( SELECT name, SUM(m.num_babies) + SUM(f.num_babies) AS num_babies, SUM(m.num_babies) / (SUM(m.num_babies) + SUM(f.num_babies)) AS frac_male FROM male_babies AS m JOIN female_babies AS f USING (name) GROUP BY name ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

4. より高速かつ洗練された、かつ正確な結果を得られる解決策として、入力を一度だけ読み取る、自己結合を行わないクエリを作り直します。

WITH all_babies AS ( SELECT name, SUM( IF (gender = 'M', number, 0)) AS male_babies, SUM( IF (gender = 'F', number, 0)) AS female_babies FROM `bigquery-public-data.usa_names.usa_1910_current` GROUP BY name ), both_genders AS ( SELECT name, (male_babies + female_babies) AS num_babies, SAFE_DIVIDE(male_babies, male_babies + female_babies) AS frac_male FROM all_babies WHERE male_babies > 0 AND female_babies > 0 ) SELECT * FROM both_genders WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

このクエリにかかった時間は約 2.4 秒で、約 30 倍の向上を示しています。

結合するデータを減らす

最初にデータを名前と性別でグループ化し、結合されるデータ量を減らすと、効率的な結合を使って上のクエリを実行できます。

• 以下のクエリを試します。

  WITH all_names AS ( SELECT name, gender, SUM(number) AS num_babies FROM `bigquery-public-data`.usa_names.usa_1910_current GROUP BY name, gender ), male_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'M' ), female_names AS ( SELECT name, num_babies FROM all_names WHERE gender = 'F' ), ratio AS ( SELECT name, (f.num_babies + m.num_babies) AS num_babies, m.num_babies / (f.num_babies + m.num_babies) AS frac_male FROM male_names AS m JOIN female_names AS f USING (name) ) SELECT * FROM ratio WHERE frac_male BETWEEN 0.3 AND 0.7 ORDER BY num_babies DESC LIMIT 5

  クエリで最初にグループ化を行うことで、データ量を減らしてから結合を実行できます。これで、シャッフルなどの複雑な操作が少ないデータに対してだけ行われるようになり、クエリが効率化されます。上のクエリは約 2 秒で完了し、正しい結果が返されました。

自己結合の代わりにウィンドウ関数を使用する

自転車が返却されてから再度レンタルされるまでの時間、つまり自転車がステーションに置かれている時間を計算したいとします。これは、各行の間に依存関係が生じる例です。テーブルを自己結合して、1 件のレンタルの end_date と次のレンタルの start_date を照合するしか方法がないように思えるかもしれません。クエリは [eu（EU の複数のリージョン）] で実行されるようにします。[+SQL クエリを作成] をクリックしてから [展開] > [クエリ設定] > [詳細オプション] を選択し、自動ロケーション選択がオンになっていることを確認します。

1. しかし、ウィンドウ関数を使用すると、自己結合を回避できます。

SELECT bike_id, start_date, end_date, TIMESTAMP_DIFF( start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire LIMIT 5

2. この情報を使用して、各ステーションで自転車がレンタルされていない時間の平均を計算し、その指標に基づいてステーションをランク付けできます。

WITH unused AS ( SELECT bike_id, start_station_name, start_date, end_date, TIMESTAMP_DIFF(start_date, LAG(end_date) OVER (PARTITION BY bike_id ORDER BY start_date), SECOND) AS time_at_station FROM `bigquery-public-data`.london_bicycles.cycle_hire ) SELECT start_station_name, AVG(time_at_station) AS unused_seconds FROM unused GROUP BY start_station_name ORDER BY unused_seconds ASC LIMIT 5

計算済みの値と結合する

小さいテーブルに対して事前に計算を行い、計算済みの値と結合を行うと、毎回コストの高い計算を繰り返すよりも高速になる場合があります。

たとえば、どのステーション ペア間で顧客が最速ペースで自転車を運転したのかを求めたいとします。自転車のペース（1 km を何分で走ったか）を計算するには、自転車に乗っている時間をステーション間の距離で除算する必要があります。

1. ステーション間の距離を含む非正規化したテーブルを作成して、平均ペースを計算できます。

WITH denormalized_table AS ( SELECT start_station_name, end_station_name, ST_DISTANCE(ST_GeogPoint(s1.longitude, s1.latitude), ST_GeogPoint(s2.longitude, s2.latitude)) AS distance, duration FROM `bigquery-public-data`.london_bicycles.cycle_hire AS h JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s1 ON h.start_station_id = s1.id JOIN `bigquery-public-data`.london_bicycles.cycle_stations AS s2 ON h.end_station_id = s2.id ), durations AS ( SELECT start_station_name, end_station_name, MIN(distance) AS distance, AVG(duration) AS duration, COUNT(*) AS num_rides FROM denormalized_table WHERE duration > 0 AND distance > 0 GROUP BY start_station_name, end_station_name HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM durations ORDER BY pace ASC LIMIT 5

上のクエリでは、cycle_hire テーブルの各行に対して地理空間関数 ST_DISTANCE が一度ずつ呼び出されます（2,400 万回）。所要時間は約 14.7 秒で、処理されたデータは約 1.9 GB です。

2. この代わりとして、cycle_stations テーブルを使用してステーション ペア間の距離を事前に計算しておき（自己結合）、その後ステーション間の平均利用時間を含むサイズの小さいテーブルと結合することもできます。

WITH distances AS ( SELECT a.id AS start_station_id, a.name AS start_station_name, b.id AS end_station_id, b.name AS end_station_name, ST_DISTANCE(ST_GeogPoint(a.longitude, a.latitude), ST_GeogPoint(b.longitude, b.latitude)) AS distance FROM `bigquery-public-data`.london_bicycles.cycle_stations a CROSS JOIN `bigquery-public-data`.london_bicycles.cycle_stations b WHERE a.id != b.id ), durations AS ( SELECT start_station_id, end_station_id, AVG(duration) AS duration, COUNT(*) AS num_rides FROM `bigquery-public-data`.london_bicycles.cycle_hire WHERE duration > 0 GROUP BY start_station_id, end_station_id HAVING num_rides > 100 ) SELECT start_station_name, end_station_name, distance, duration, duration/distance AS pace FROM distances JOIN durations USING (start_station_id, end_station_id) ORDER BY pace ASC LIMIT 5

より効率的な結合を使用して作り直したクエリの所要時間はわずか約 8.2 秒（約 1.8 倍の高速化）で、処理されたデータは約 554 MB です（コストを約 4 分の 1 に削減）。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。

効果的な結合の実行

タスク 4. 単一ワーカーの過負荷の回避

並べ替えなど一部の操作は、単一ワーカーで実行する必要があります。大量のデータを並べ替えるとワーカーのメモリを圧迫し、「Resources exceeded」エラーが発生する可能性があります。単一ワーカーで大量のデータの処理が生じないようにしましょう。Google データセンターのハードウェアは常にアップグレードされているため、ここで言う「大量」の基準は時間とともに変化します。現在は、約 1 GB です。

大量の並べ替えを行わない

1. レンタルのすべてのレコードに対し、レンタルが終了した順番に 1、2、3 と番号を付けるとします。ROW_NUMBER() 関数を使用してこのクエリを実行できます。

SELECT rental_id, ROW_NUMBER() OVER(ORDER BY end_date) AS rental_number FROM `bigquery-public-data.london_bicycles.cycle_hire` ORDER BY rental_number ASC LIMIT 5

このクエリでは、単一ワーカーでロンドンの自転車レンタルのデータセット全体を並べ替える必要があるため、わずか 372 MB の処理に 34.5 秒かかっています。データセットがさらに大きくなると、ワーカーが過負荷となっていた可能性があります。

2. データの並べ替えサイズを小さくし、分散させることができないか検討した方がよいでしょう。レンタルの日付を抽出し、各日付内でレンタルのデータを並べ替えることができます。

WITH rentals_on_day AS ( SELECT rental_id, end_date, EXTRACT(DATE FROM end_date) AS rental_date FROM `bigquery-public-data.london_bicycles.cycle_hire` ) SELECT rental_id, rental_date, ROW_NUMBER() OVER(PARTITION BY rental_date ORDER BY end_date) AS rental_number_on_day FROM rentals_on_day ORDER BY rental_date ASC, rental_number_on_day ASC LIMIT 5

一度に 1 日分のデータに対してのみ並べ替えを行うため、約 15.1 秒で終了します（2 倍の高速化）。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。

単一ワーカーの過負荷の回避

データスキュー

あるキーが他のキーに比べて極端に多く使用される場合、ARRAY_AGG と GROUP BY を同時に実行するとき、同じようにワーカーの過負荷（この場合はワーカーのメモリの過負荷）の問題が生じることがあります。

1. GitHub リポジトリは 300 万以上あり、コミットはリポジトリ間で分散して行われるため、以下のクエリは成功します（[us (米国の複数のリージョン)] の処理センターでクエリを実行してください）。

SELECT repo_name, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name

このクエリは成功しますが、実行に 30 分以上かかる場合があります。このクエリについて理解したら、ラボを進めてください。

2. GitHub を使用するほとんどのユーザーは、いくつかのタイムゾーンに集中しています。そのため、タイムゾーンでのグループ化はうまくいきません。単一のワーカーに 750 GB のデータの大部分の並べ替えを要求することになるためです。

SELECT author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits` GROUP BY author.tz_offset

3. すべてのデータを並べ替える必要がある場合は、（グループのデータが多数のワーカーに分散されるように）より細かくグループ化されるキーを使用し、その結果を必要なキーに応じて集計します。たとえば、タイムゾーンのみでグループ化するのではなく、timezone と repo_name の両方でグループ化し、その次にすべてのリポジトリを集計すると、各タイムゾーンの結果を得ることができます。

SELECT repo_name, author.tz_offset, ARRAY_AGG(STRUCT(author, committer, subject, message, trailer, difference, encoding) ORDER BY author.date.seconds) FROM `bigquery-public-data.github_repos.commits`, UNNEST(repo_name) AS repo_name GROUP BY repo_name, author.tz_offset

このクエリは成功しますが、実行には 15 分以上かかることがあります。このクエリについて理解したら、ラボを進めてください。

タスク 5. 近似集計関数の使用

BigQuery は高速でメモリ使用量の少ない近似集計関数を備えています。大きなデータ ストリームでは、結果に多少の統計的不確実性があったとしても許容される場合、COUNT(DISTINCT …) を使用する代わりに、APPROX_COUNT_DISTINCT を使用できます。

近似カウント

1. 以下のクエリを使用して、一意の GitHub リポジトリの数を求めることができます。

SELECT COUNT(DISTINCT repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

上のクエリでは、8.3 秒で正しい結果である 3,347,770 を算出できます。

2. 近似関数を使用すると以下のようになります。

SELECT APPROX_COUNT_DISTINCT(repo_name) AS num_repos FROM `bigquery-public-data`.github_repos.commits, UNNEST(repo_name) AS repo_name

このクエリは約 3.9 秒で実行され（2 倍の高速化）、正しい結果よりも約 1.5% 大きくなっているものの、3,399,473 という近似した結果が返されます。

近似アルゴリズムの効率が正確なアルゴリズムよりも大幅に良くなるのは、データセットのサイズが大きい場合のみです。およそ 1% の誤差が許容される場合に使用することをおすすめします。近似関数を使用する前に、用途に適しているか確認してください。

他にも、パーセンタイルを計算する APPROX_QUANTILES、トップ要素を探す APPROX_TOP_COUNT、要素の合計に基づいてトップ要素を探す APPROX_TOP_SUM などの近似関数があります。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。

近似集計関数の使用

お疲れさまでした

クエリのパフォーマンスを向上させるためのさまざまなテクニックについて学習しました。こうしたテクニックの使用を検討する際には、著名なコンピュータ科学者 Donald Knuth 氏の「早計な最適化は諸悪の根源である」という言葉を忘れないようにしましょう。

次のステップと詳細情報

• Google Cloud Platform のクエリ パフォーマンスの最適化についてのドキュメント
• BigQuery のコストを抑えるためのベスト プラクティス

ラボを終了する

ラボが完了したら、[ラボを終了] をクリックします。ラボで使用したリソースが Google Cloud Skills Boost から削除され、アカウントの情報も消去されます。

ラボの評価を求めるダイアログが表示されたら、星の数を選択してコメントを入力し、[送信] をクリックします。

星の数は、それぞれ次の評価を表します。

• 星 1 つ = 非常に不満
• 星 2 つ = 不満
• 星 3 つ = どちらともいえない
• 星 4 つ = 満足
• 星 5 つ = 非常に満足

フィードバックを送信しない場合は、ダイアログ ボックスを閉じてください。

フィードバックやご提案の送信、修正が必要な箇所をご報告いただく際は、[サポート] タブをご利用ください。

Copyright 2020 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。