GSP408

概要

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

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

このラボでは、クエリのトラブルシューティングの手順について、実際のシナリオを想定したアクティビティを行いながら学びます。ラボ全体を通じて、あなたはチームの新しいデータ アナリストから提供されたクエリを使用して、e コマース データセットに関するいくつかの問いに答えます。答えに基づいてクエリを修正し、意味のある結果が得られるようにしてください。

学習内容

このラボでは、次のタスクを行う方法について学びます。

• BigQuery のリソースツリーにプロジェクトを固定する
• BigQuery のクエリエディタとクエリ バリデータを使用して SQL 構文や論理のエラーの特定とトラブルシューティングを行う

設定と要件

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

こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、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 のプロダクトやサービスのリストを含むメニューを表示するには、左上のナビゲーション メニューをクリックします。

タスク 1. BigQuery のリソースツリーにプロジェクトを固定する

1. ナビゲーション メニュー > [BigQuery] の順にクリックします。

[Cloud Console の BigQuery へようこそ] メッセージ ボックスが開きます。

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

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

3. BigQuery の一般公開データセットは、デフォルトでは表示されません。一般公開データセット プロジェクトを開くには、「data-to-insights」をコピーします。

4. [+ 追加] > [名前を指定してプロジェクトにスターを付ける] の順にクリックしたら、「data-to-insights」名を貼り付けます。

5. [スターを付ける] をクリックします。

data-to-insights プロジェクトが [エクスプローラ] に表示されます。

BigQuery のクエリエディタとクエリ バリデータ

以降のセクションの各アクティビティでは、一般的なエラーが含まれるクエリのトラブルシューティングを行います。どこにエラーがあり、どのように構文を修正すれば意味のある結果が得られるのかを示唆するアドバイスが提示されます。

それらのアドバイスに沿ってトラブルシューティングを行うには、クエリをコピーして BigQuery のクエリエディタに貼り付けます。エラーがある場合は、エラーを含む行とクエリ バリデータ（下隅）に赤い感嘆符が表示されます。

エラーがあるクエリを実行すると、クエリは失敗し、[ジョブ情報] にエラーが表示されます。

クエリにエラーがない場合は、クエリ バリデータに緑色のチェックマークが表示されます。緑色のチェックマークが表示されたら、[実行] をクリックします。クエリが実行されて、結果が表示されます。

注: 構文の詳細については、標準 SQL クエリの構文をご覧ください。

タスク 2. 購入手続きを完了した顧客の合計数を調べる

このセクションの目標は、ウェブサイトの購入手続きを完了したユニーク ユーザー数を返すクエリを作成することです。このデータは、データ アナリスト チームから提供された rev_transactions テーブルにあります。すぐに分析を始められるようにサンプルクエリも提供されていますが、それらが正しく記述されているかどうかはわかりません。

クエリ バリデータ、エイリアス、カンマのエラーを含むクエリのトラブルシューティング

• 以下のクエリを確認し、その後の質問に答えてください。

#standardSQL SELECT FROM `data-to-inghts.ecommerce.rev_transactions` LIMIT 1000

• クエリを次のように更新すると、正常に機能しますか。

#standardSQL SELECT * FROM [data-to-insights:ecommerce.rev_transactions] LIMIT 1000

• 標準 SQL を使用する次のクエリは正常に機能しますか。

#standardSQL SELECT FROM `data-to-insights.ecommerce.rev_transactions`

• 次のクエリは正常に機能しますか。このクエリには列があります。

#standardSQL SELECT fullVisitorId FROM `data-to-insights.ecommerce.rev_transactions`

• 次のクエリは正常に機能しますか。このクエリにはページのタイトルがあります。

#standardSQL SELECT fullVisitorId hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• 次のクエリは正常に機能しますか。カンマが抜けていた問題は修正されました。

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

答え: このクエリで結果は返ってきますが、同じユーザーが 2 回カウントされていないか確認する必要があります。また、購入手続きに進んだユニーク ユーザー数の問いに答えるには、返される行を 1 行のみにする必要があります。次のセクションでは、結果を集計できるようにします。

論理エラー、GROUP BY ステートメント、ワイルドカード フィルタを含むクエリのトラブルシューティング

• 次のクエリを集計して、購入手続きに進んだユニーク ユーザー数を確認します。

#standardSQL SELECT fullVisitorId , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` LIMIT 1000

• 次のクエリは正常に機能しますか。集計関数の COUNT() が追加されています。

#standardSQL SELECT COUNT(fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions`

• 次のクエリでは、GROUP BY ステートメントと DISTINCT ステートメントが追加されています。

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY hits_page_pageTitle

結果

これで、正しい結果が得られるようになりました。ただし、まだ余計なものが含まれています。

• フィルタを追加して「Checkout Confirmation」のみが結果に含まれるようにします。

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS visitor_count , hits_page_pageTitle FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_page_pageTitle = "Checkout Confirmation" GROUP BY hits_page_pageTitle

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 購入手続きを完了した顧客の合計数を調べる

タスク 3. e コマース サイトのトランザクション数が多い都市を表示する

並べ替え、計算フィールド、集計後のフィルタに関するエラーのトラブルシューティング

1. 部分的に作成済みの次のクエリを完成させてください。

SELECT geoNetwork_city, totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY

解答例:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city

2. 前のクエリを更新して、都市をトランザクション数の多い順に並べます。

解答例:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS totals_transactions, COUNT( DISTINCT fullVisitorId) AS distinct_visitors FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY distinct_visitors DESC

3. クエリを更新して新しい計算フィールドを作成し、注文あたりの平均商品数を都市別に返します。

解答例:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

結果

集計結果をフィルタして、avg_products_ordered が 20 を超えている都市のみを返します。

• 次のクエリにはどのような問題がありますか。

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` WHERE avg_products_ordered > 20 GROUP BY geoNetwork_city ORDER BY avg_products_ordered DESC

解答例:

#standardSQL SELECT geoNetwork_city, SUM(totals_transactions) AS total_products_ordered, COUNT( DISTINCT fullVisitorId) AS distinct_visitors, SUM(totals_transactions) / COUNT( DISTINCT fullVisitorId) AS avg_products_ordered FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY geoNetwork_city HAVING avg_products_ordered > 20 ORDER BY avg_products_ordered DESC

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 e コマース サイトのトランザクション数が多い都市を表示する

タスク 4. 各商品カテゴリの商品の総数を調べる

NULL 値でフィルタして人気の商品を調べる

1. 次のクエリにはどのような問題がありますか。どうすれば修正できますか。

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,2

2. 次のクエリにはどのような問題がありますか。

#standardSQL SELECT COUNT(hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC

3. 前のクエリを更新して、各商品カテゴリの重複する商品がカウントされないようにします。

解答例:

#standardSQL SELECT COUNT(DISTINCT hits_product_v2ProductName) as number_of_products, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` WHERE hits_product_v2ProductName IS NOT NULL GROUP BY hits_product_v2ProductCategory ORDER BY number_of_products DESC LIMIT 5

注:

(not set) は、商品にカテゴリが設定されていないことを示します。

${productitem.product.origCatName} は、カテゴリを表示するためのフロントエンド コードです。これは、ページが完全に表示される前に Google アナリティクスのトラッキング スクリプトが呼び出されたことを意味します。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 各商品カテゴリの商品の総数を調べる

お疲れさまでした

BigQuery で標準 SQL を使用したクエリの問題点を調べて修正することができました。クエリの構文の間違いはクエリ バリデータで見つけることができますが、クエリが正常に実行された場合も結果を注意して確認するようにしてください。

次のステップと詳細情報

• BigQuery の一般公開データセットをご確認ください。
• Google アナリティクス アカウントをお持ちで、BigQuery で独自のデータセットをクエリするには、こちらのエクスポート ガイドの手順を実施してください。
• BigQuery に関して知っておくべき 15 の重要なことをご確認ください。
• BigQuery についてさらに詳しくは、以下のラボをご確認ください。
  • BigQuery で SQL を使用して e コマース データセットを操作する
  • BigQuery の気象データ
  • BigQuery で日付分割テーブルを作成する

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

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

マニュアルの最終更新日: 2024 年 1 月 19 日

ラボの最終テスト日: 2023 年 8 月 28 日

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