概要

BigQuery は、Google が提供する低コストで NoOps のフルマネージド分析データベースで、インフラストラクチャを所有して管理したり、データベース管理者を置いたりすることなく、テラバイト単位の大規模なデータでクエリを実行できます。また、SQL が採用されており、従量課金制というメリットもあります。そのため、BigQuery を使用すると、有用な情報を得るためのデータ分析に専念することができます。

ここで使用するのは、Google Merchandise Store に関する数百万件の Google アナリティクス レコードで構成される ecommerce データセットで、すでに BigQuery に読み込まれています。ラボではこのデータセットのコピーを使用して、利用可能なフィールドや行からどのような分析情報が得られるかを確認します。

このラボでは、パーティション分割されたデータセットに対してクエリを実行するほか、クエリのパフォーマンスを高めてコストを削減するために独自のデータセット パーティションを作成します。

設定

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

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

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

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

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

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

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

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

BigQuery コンソールを開く

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

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

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

タスク 1. 新しいデータセットを作成する

まず、テーブルを保存するためのデータセットを作成します。

1. [エクスプローラ] セクションでご自分のプロジェクト ID の横にあるアクションを表示アイコンをクリックし、[データセットを作成] を選択してプロジェクト内に新しいデータセットを作成します。

2. [データセット ID] に「ecommerce」と入力します。その他のオプションはデフォルト値のままにします（[データのロケーション]、[デフォルトのテーブルの有効期限]）。

3. [データセットを作成] をクリックします。

タスク 2. 日付パーティションを持つテーブルを作成する

パーティション分割テーブルは、パーティションと呼ばれるセグメントに分割されたテーブルで、使用するとデータの管理やクエリが容易になります。大きなテーブルを小さなパーティションに分割することで、クエリのパフォーマンスを高めたり、クエリが読み取るバイト数を減らしてコストを抑えたりすることができます。

ここでは、新しいテーブルを作成し、日付またはタイムスタンプの列をパーティションとしてバインドします。その前に、まずはパーティション分割されていないテーブルのデータを調べてみましょう。

ウェブページ分析データで 2017 年の訪問者のサンプルをクエリする

1. [無題のクエリ] に以下のクエリを追加します。実行する前に、処理される合計データ量を確認します。これは、クエリ検証ツール アイコンの横に「このクエリを実行すると、1.74 GB が処理されます」のように表示されます。

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170708' LIMIT 5

2. [実行] をクリックします。

このクエリでは 5 件の結果が返されます。

ウェブページ分析データで 2018 年の訪問者のサンプルをクエリする

次に、このクエリを変更して 2018 年の訪問者を調べてみましょう。

1. [無題のクエリ] に以下のクエリを追加します。

#standardSQL SELECT DISTINCT fullVisitorId, date, city, pageTitle FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20180708' LIMIT 5

クエリで処理されるデータの量が [クエリ結果] に表示されます。

2. [実行] をクリックします。

返される結果が 0 件でも、1.74 GB のデータが処理されています。これは、クエリエンジンがデータセット内のすべてのレコードをスキャンして、WHERE 句の日付の条件と一致しているかどうかを確認する必要があるからです。ここでは、レコードのそれぞれの日付を「20180708」という条件と照合しなければなりません。

なお、よくある誤解ですが、LIMIT 5 を追加しても処理される合計データ量が減ることはありません。

日付パーティション分割テーブルの一般的なユースケース

行を WHERE 条件と照合するために毎回データセット全体をスキャンするのは、無駄の多い作業です。以下のように、特定の期間のレコードのみを対象とする場合は特に当てはまります。

• 昨年のすべてのトランザクション
• 過去 7 日間のすべてのユーザー インタラクション
• 先月販売したすべての商品

今度は、前のクエリのようにデータセット全体をスキャンして date フィールドでフィルタする代わりに、日付パーティション分割テーブルを作成します。これにより、クエリに関係のないパーティションのレコードはスキャンする必要がなくなります。

日付に基づいてく新しいパーティション分割テーブルを作成する

1. [+ SQL クエリ] をクリックして以下のクエリを追加し、[実行] をクリックします。

#standardSQL CREATE OR REPLACE TABLE ecommerce.partition_by_day PARTITION BY date_formatted OPTIONS( description="a table partitioned by date" ) AS SELECT DISTINCT PARSE_DATE("%Y%m%d", date) AS date_formatted, fullvisitorId FROM `data-to-insights.ecommerce.all_sessions_raw`

このクエリには、PARTITION BY <フィールド> という新しいオプションがあります。パーティション分割に使用できるオプションは DATE と TIMESTAMP の 2 つです。ここでは、文字列として保存されている date フィールドをパーティショニングに適した DATE 型に変換するために、PARSE_DATE 関数を使用しています。

2. ecommerce データセットをクリックし、新しい partiton_by_day テーブルを選択します。

3. [詳細] タブをクリックします。

4. [テーブル情報] セクションに次の内容が表示されていることを確認します。

• パーティショニングの基準: Day
• パーティショニングのフィールド: date_formatted

注: Qwiklabs アカウントのパーティション分割テーブル内のパーティションは、日付列の値から 60 日後に自動的に期限切れになります。課金が有効になっている個人の GCP アカウントでは、期限切れにならないパーティション分割テーブルを作成できます。

このラボの以降のクエリは、すでに作成されているパーティション分割テーブルに対して実行します。

タスク 3. パーティション分割テーブルを使用して、処理されたデータを表示する

1. 以下のクエリを実行し、処理される合計バイト数を確認します。

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2016-08-01'

処理されるバイト数が最大で 25 KB（0.025 MB）になりました。これは、前のクエリに比べるとごくわずかです。

2. 次に以下のクエリを実行し、処理される合計バイト数を確認します。

#standardSQL SELECT * FROM `data-to-insights.ecommerce.partition_by_day` WHERE date_formatted = '2018-07-08'

「このクエリを実行すると、0 B が処理されます。」と表示されます。

処理されるのが 0 バイトになるのはなぜでしょうか。

タスク 4. 自動的に期限切れになるパーティション分割テーブルを作成する

自動的に期限切れになるパーティション分割テーブルは、データ プライバシーに関する法令を遵守するために使用します。また、ストレージの不要な浪費を防ぐためにも役立ちます（本番環境ではコストの節約になります）。データのローリング ウィンドウを作成する場合は、使い終わったパーティションが消去されるように有効期限を追加します。

公開されている NOAA 気象データのテーブルを調べる

1. 左側のパネルで [+ 追加] をクリックし、[公開データセット] を選択します。

2. 「GSOD NOAA」を検索し、該当するデータセットを選択します。

3. [データセットを表示] をクリックします。

4. noaa_gsod データセットのテーブルのリストをスクロールします（手動でシャーディングされていて、パーティション分割されていません）。

5. 次に、以下のクエリをコピーして [無題のクエリ] に貼り付けます。

#standardSQL SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter stations/days with no precipitation AND _TABLE_SUFFIX >= '2021' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10

6. TABLE_SUFFIX フィルタで参照されるテーブルの数を制限するために、FROM 句でテーブル ワイルドカード（*）が使用されています。

7. LIMIT 10 が追加されているものの、まだパーティションがないため、スキャンされる合計データ量（約 141.6 MB）は減りません。

8. [実行] をクリックします。

9. 日付の形式が正しいことと、precipitation フィールドの値が 0 でないことを確認します。

タスク 5. 実習: パーティション分割テーブルを作成する

• 前のクエリを変更してテーブルを作成します。次のように指定してください。

  • テーブル名: ecommerce.days_with_rain
  • PARTITION BY に date フィールドを使用
  • OPTIONS に partition_expiration_days=60 を指定
  • description="weather stations with precipitation, partitioned by day" を追加

クエリは次のようになります。

#standardSQL CREATE OR REPLACE TABLE ecommerce.days_with_rain PARTITION BY date OPTIONS ( partition_expiration_days=60, description="weather stations with precipitation, partitioned by day" ) AS SELECT DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date, (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names prcp FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather WHERE prcp < 99.9 -- Filter unknown values AND prcp > 0 -- Filter AND _TABLE_SUFFIX >= '2021'

データ パーティションの有効期限が機能していることを確認する

過去60 日間のデータのみが保存されていることを確認するために、60 日後に期限切れになるように設定されているパーティションの経過日数を DATE_DIFF クエリを実行して取得します。

以下のクエリでは、非常に降水量の多い和歌山県にある NOAA の気象観測所の平均降水量を追跡します。

• 次のクエリを追加して実行します。

#standardSQL # 毎月の平均降水量 SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #日本 GROUP BY station_name, date, today, month, partition_age ORDER BY date DESC; # 日付が新しい順

タスク 6. partition_age が 60 日以内であることを確認する

ORDER BY 句を更新して、パーティションを古い順に表示します。日付は表示されているとおりです。

• 次のクエリを追加して実行します。

#standardSQL # 毎月の平均降水量 SELECT AVG(prcp) AS average, station_name, date, CURRENT_DATE() AS today, DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age, EXTRACT(MONTH FROM date) AS month FROM ecommerce.days_with_rain WHERE station_name = 'WAKAYAMA' #日本 GROUP BY station_name, date, today, month, partition_age ORDER BY partition_age DESC Note: 気象データとパーティションは継続的に更新されるため、今後このクエリを再実行すると結果が変わります。

お疲れさまでした

BigQuery でパーティション分割テーブルを作成してクエリを実行しました。

ラボを終了する

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

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

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

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

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

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

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