概要

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

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

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

演習内容

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

• 一般公開データセットの data-to-insights に対してクエリを実行する
• BigQuery のクエリエディタを使用して一般的な SQL エラーのトラブルシューティングを行う
• クエリ検証ツールを使用する
• SQL の構文エラーと論理エラーのトラブルシューティングを行う

設定と要件

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

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

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

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

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

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

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

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

BigQuery を開いてプロジェクトをリソースツリーに固定する

このセクションでは、data-to-insights プロジェクトを使用環境のリソースに追加します。

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

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

[Cloud Console の BigQuery へようこそ] メッセージ ボックスには、クイックスタート ガイドと UI アップデートへのリンクが含まれています。

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

BigQuery 公開データセットは、デフォルトでは BigQuery ウェブ UI に表示されません。公開データセット プロジェクトを開くには

3. [+ Add Data] をクリックします。
4. Pin project > プロジェクト名を入力するを選択します。
5. プロジェクト名には、data-to-insights を入力します。
6. [Pin] をクリックします。

7. 左側のペインの [Viewing pinned projects] の下に、固定された data-to-insights プロジェクトが表示されます。

BigQuery のコードエディタ

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

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

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

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

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

購入手続きを完了した顧客の合計数を調べる

このセクションの目標は、ウェブサイトの購入手続きを完了したユニーク ユーザー数を返すクエリを作成することです。このデータは、データ アナリスト チームから提供された 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

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

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

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

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

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

正解例

#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

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

正解例

#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

各商品カテゴリの商品の総数を調べる

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

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

#standardSQL SELECT hits_product_v2ProductName, hits_product_v2ProductCategory FROM `data-to-insights.ecommerce.rev_transactions` GROUP BY 1,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

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

正解例

#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

お疲れさまでした

ここでは、標準 SQL を使用した BigQuery クエリの問題点を調べて修正しました。クエリの構文の問題はクエリ検証ツールで見つけることができますが、クエリが正常に実行された場合も結果をよく吟味するようにしてください。

ラボを終了する

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

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

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

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

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

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

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