GSP414

概要

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

このラボでは、BigQuery でクエリを実行し、パーティション分割テーブルを作成して、クエリのパフォーマンスを向上させ、リソース使用量を削減する方法を学習します。このラボで使用するデータは、Google Merchandise Store に関する数百万件の Google アナリティクス レコードが格納された e コマース データセットで、BigQuery に読み込まれています。

演習内容

このラボでは、次の方法について学びます。

• パーティション分割テーブルに対してクエリを実行する。
• 独自のパーティション分割テーブルを作成する。

設定と要件

[ラボを開始] ボタンをクリックする前に

こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、Google Cloud のリソースを利用できる時間を示しています。

このハンズオンラボでは、シミュレーションやデモ環境ではなく、実際のクラウド環境を使ってご自身でラボのアクティビティを行うことができます。そのため、ラボの受講中に Google Cloud にログインおよびアクセスするための、新しい一時的な認証情報が提供されます。

このラボを完了するためには、下記が必要です。

• 標準的なインターネット ブラウザ（Chrome を推奨）

注: このラボの実行には、シークレット モードまたはシークレット ブラウジング ウィンドウを使用してください。これにより、個人アカウントと受講者アカウント間の競合を防ぎ、個人アカウントに追加料金が発生することを防ぎます。

• ラボを完了するために十分な時間を確保してください。ラボをいったん開始すると一時停止することはできません。

注: すでに個人の Google Cloud アカウントやプロジェクトをお持ちの場合でも、このラボでは使用しないでください。アカウントへの追加料金が発生する可能性があります。

ラボを開始して Google Cloud コンソールにログインする方法

1. [ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側の [ラボの詳細] パネルには、以下が表示されます。

   • [Google Cloud コンソールを開く] ボタン
   • 残り時間
   • このラボで使用する必要がある一時的な認証情報
   • このラボを行うために必要なその他の情報（ある場合）

2. [Google Cloud コンソールを開く] をクリックします（Chrome ブラウザを使用している場合は、右クリックして [シークレット ウィンドウでリンクを開く] を選択します）。

   ラボでリソースが起動し、別のタブで [ログイン] ページが表示されます。

   ヒント: タブをそれぞれ別のウィンドウで開き、並べて表示しておきましょう。

   注: [アカウントの選択] ダイアログが表示されたら、[別のアカウントを使用] をクリックします。

3. 必要に応じて、下のユーザー名をコピーして、[ログイン] ダイアログに貼り付けます。

   {{{user_0.username | "Username"}}}

   [ラボの詳細] パネルでも [ユーザー名] を確認できます。

4. [次へ] をクリックします。

5. 以下のパスワードをコピーして、[ようこそ] ダイアログに貼り付けます。

   {{{user_0.password | "Password"}}}

   [ラボの詳細] パネルでも [パスワード] を確認できます。

6. [次へ] をクリックします。

   重要: ラボで提供された認証情報を使用する必要があります。Google Cloud アカウントの認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。

7. その後次のように進みます。

   • 利用規約に同意してください。
   • 一時的なアカウントなので、復元オプションや 2 要素認証プロセスは設定しないでください。
   • 無料トライアルには登録しないでください。

その後、このタブで Google Cloud コンソールが開きます。

注: Google Cloud のプロダクトやサービスのリストを含むメニューを表示するには、左上のナビゲーション メニューをクリックします。

BigQuery コンソールを開く

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

[Cloud コンソールの BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスには、クイックスタート ガイドとリリースノートへのリンクが表示されます。

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

BigQuery コンソールが開きます。

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

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

2. [エクスプローラ] ペインで、プロジェクト ID の近くにある [アクションを表示] をクリックし、次に [データセットを作成] をクリックします。

3. [データセット ID] に 「ecommerce」と設定します。

その他のオプションはデフォルト値のままにします（[データのロケーション]、[デフォルトのテーブルの有効期限]）。

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

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

ecommerce という名前のデータセットを作成する

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

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

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

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

1. [+ クエリを新規作成] をクリックし、以下のクエリを追加します。

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

実行する前に、処理されるデータの総量を確認します。これは、クエリ バリデータのアイコンの横に「このクエリを実行すると、1.74 GB が処理されます。」のように表示されます。

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

このクエリは 5 件の結果を返します。

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

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

1. [クエリを新規作成] をクリックしてクエリエディタをクリアしてから、以下の新しいクエリを追加します。このクエリでは、WHERE date パラメータの値が 20180708 に変更されています。

#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 日間のすべてのユーザー インタラクション
• 先月販売したすべての商品

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

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

1. [クエリを新規作成] をクリックし、以下のクエリを追加して [実行] をクリックします。

#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 型に変換するために、PARSE_DATE 関数を使用しています。

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

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

次のようになっていることを確認します。

• パーティション分割の基準: Day
• パーティション分割の場所: date_formatted

注: ラボアカウントのパーティション分割テーブル内のパーティションは、日付列の値から 60 日後に自動的に期限切れになります。 課金を有効にした個人の Google Cloud アカウントでは、期限切れにならないパーティション分割テーブルを作成できます。このラボの以降のクエリは、すでに作成されているパーティション分割テーブルに対して実行します。

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

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

タスク 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 が処理されます。」と表示されます。

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

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

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

1. 左側のメニューの [エクスプローラ] で、[+ 追加] をクリックし、[一般公開データセット] を選択します。

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

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

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

ここでの目標は、次のようなテーブルを作成することです。

• 2018 年以降の気象データに対してクエリを実行する
• 降水量（雨、雪など）が観測された日のみを含める
• 各パーティションの保存期間を 90 日に制限する（ローリング ウィンドウ）

1. まず、以下のクエリをコピーして貼り付けます。

#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 >= '2018' ORDER BY date DESC -- Where has it rained/snowed recently LIMIT 10 注: TABLE_SUFFIX フィルタで参照されるテーブルの数を制限するために、FROM 句でテーブル ワイルドカード * が使用されています。 注: LIMIT 10 が追加されていますが、まだパーティションがないため、スキャンされるデータの総量（約 1.83 GB）は減りません。

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

3. 日付の形式が正しいこと、降水量フィールドの値が 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 >= '2018'

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

実習: パーティション分割テーブルを作成する

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

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

以下のクエリは、非常に降水量の多い和歌山市にある 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 注: 気象データとパーティションは継続的に更新されるため、今後このクエリを再実行したときには結果は変わります。

お疲れさまでした

ここでは、BigQuery で分割テーブルを作成してクエリを実行しました。

次のステップと詳細情報

• 特定の日付列またはタイムスタンプ列にバインドされていない、取り込み時間パーティション分割テーブルの作成方法にご興味がある場合は、BigQuery の分割テーブルのドキュメントと例をご覧ください。
• Google アナリティクス アカウントをお持ちで、BigQuery で独自のデータセットをクエリするには、こちらのエクスポート ガイドの手順を実施してください。
• その他の BigQuery ラボをご確認ください。
  • データ結合における問題のトラブルシューティングと解決
  • BigQuery での JSON、配列、構造体の操作
  • BigQuery ML 予測モデルによるタクシー運賃の予測

Google Cloud トレーニングと認定資格

Google Cloud トレーニングと認定資格を通して、Google Cloud 技術を最大限に活用できるようになります。必要な技術スキルとベスト プラクティスについて取り扱うクラスでは、学習を継続的に進めることができます。トレーニングは基礎レベルから上級レベルまであり、オンデマンド、ライブ、バーチャル参加など、多忙なスケジュールにも対応できるオプションが用意されています。認定資格を取得することで、Google Cloud テクノロジーに関するスキルと知識を証明できます。

マニュアルの最終更新日: 2024 年 2 月 3 日

ラボの最終テスト日: 2024 年 1 月 1 日

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