GSP413

概要

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

ここで使用するデータセットは、Google Merchandise Store から送られる数百万件の Google アナリティクス レコードで構成された e コマースのデータセットです。フィールドや行からどのような分析情報が得られるのかを確認します。

このラボでは、SQL の JOIN と UNION を使用して新しいレポート テーブルを作成する方法について詳しく学習します。

シナリオ: マーケティング チームから、e コマース ウェブサイトのすべての商品レビューがあなたとデータ サイエンス チームに提供されました。あなたは、データ サイエンス チームと協力して、以下の 3 つのソースのデータを結合するデータ ウェアハウスを BigQuery で構築します。

• ウェブサイトの e コマースデータ
• 商品在庫のストックレベルとリードタイム
• 商品レビューの感情分析

演習内容

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

• 新しい e コマースデータで感情分析を行う
• データセットを結合して新しいテーブルを作成する
• UNION とテーブル ワイルドカードを使用して履歴データを連結する

設定と要件

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

こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、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. テーブルを保存するための新しいデータセットを作成する

まず、BigQuery で「ecommerce」という名前の新しいデータセットを作成してテーブルを保存します。

1. 左側のペインで BigQuery プロジェクトの名前（qwiklabs-gcp-xxxx）をクリックします。

2. プロジェクト名の横にあるその他アイコンをクリックし、[データセットを作成] を選択します。

[データセットを作成] ダイアログが開きます。

3. [データセット ID] に「ecommerce」と入力します。他のオプションはすべてデフォルト値のままにします。

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

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 テーブルを保存するための新しいデータセットを作成する

タスク 2. 商品に対する感情のデータセットを調べる

データ サイエンス チームは、すべての商品レビューに対して感情分析 API を実行し、各商品の感情スコア（score）と感情強度（magnitude）の平均値を調べました。

マーケティング チームのデータセットを含むプロジェクトは、data-to-insights です。BigQuery の一般公開データセットは、デフォルトでは BigQuery には表示されません。このラボのクエリでは data-to-insights を使用しますが、このデータセットは表示されません。

1. まず、データ サイエンス チームが作成したテーブルを読めるように、コピーを作成します。

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` 注: このコピーは確認を行うためだけのもので、ラボのクエリでは data-to-insights プロジェクトを使用します。

2. ecommerce データセットをクリックして、products テーブルを表示します。

[プレビュー] タブと [スキーマ] タブを使用してデータを検証する

1. [ecommerce] > [products] データセットに移動し、[プレビュー] タブをクリックしてデータを確認します。

2. [スキーマ] タブをクリックします。

レビューの感情がポジティブだった商品をスコアの高い順に 5 つ表示するクエリを作成する

1. クエリエディタに SQL クエリを入力します。

解答例:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. 上で入力したクエリを、レビューの感情がネガティブだった商品をスコアの低い順に 5 つ表示して、NULL 値を除外するように変更します。

解答例:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

感情スコアが最も低い商品は何ですか。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 商品に対する感情のデータセットを調べる

タスク 3. データセットを結合して有用な情報を得る

シナリオ: あなたは、月初めに在庫管理チームから、商品在庫データセットの orderedQuantity フィールドの値が古くなっているという連絡を受けました。2017 年 8 月 1 日の商品別の合計販売数を調べて現在のストックレベルと照合し、どの商品から補充すればよいかわかるようにする必要があります。

productSKU 別の日次販売数を計算する

1. ecommerce データセットに新しいテーブルを作成します。要件は次のとおりです。

• sales_by_sku_20170801 という名前を付ける
• データのソースに data-to-insights.ecommerce.all_sessions_raw を使用する
• 重複する結果は含めない
• productSKU を返す
• 合計注文数（productQuantity）を返す（ヒント: SUM() で IFNULL 条件を使用します）
• 20170801 の販売数のみを含める
• ORDER BY で SKU を注文数の多い順に並べる

解答例:

# 2017 年 8 月 1 日の販売数を pull する CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 個 の SKU を販売

2. sales_by_sku テーブルをクリックし、[プレビュー] タブをクリックします。

販売された商品の SKU の数はいくつですか（重複したものはカウントしません）。

答え: 462

次に、この販売データを商品の在庫情報で拡充するために、2 つのデータセットを結合します。

販売データと在庫データを結合する

1. 結合を使用して、ウェブサイトの e コマースデータを商品在庫データセットの以下のフィールドで拡充します。

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

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

# 商品在庫に対して結合を実行し、名前を取得する SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

解答例:

# 商品在庫に対して結合を実行し、名前を取得する SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. 上で記述したクエリをさらに次のように変更します。

• 「total_ordered / stockLevel」の計算フィールドを追加して「ratio」というエイリアスを割り当てます。ヒント: ストックレベルが 0 の場合に 0 除算エラーが発生しないように SAFE_DIVIDE(field1,field2) を使用します。
• 結果をフィルタして、月初めにすでに在庫が 50% を切っている商品のみを含めます。

解答例:

# 割合を計算して、フィルタする SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # 当月用の在庫が 50% を切っているもの WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 データセットを結合して有用な情報を得る

タスク 4. 追加のレコードを連結する

海外のチームの 2017 年 8 月 2 日の店舗販売データがすでにあり、それを日次販売テーブルに記録する必要があります。

2017 年 8 月 2 日の productSKU 別販売数を保存するための新しい空のテーブルを作成する

1. スキーマで以下のフィールドを指定します。

• テーブル名: ecommerce.sales_by_sku_20170802
• productSKU STRING
• total_ordered を INT64 フィールドとして指定

解答例:

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. 日付を共有する販売テーブルが 2 つになったことを確認します。テーブル結果に表示された Sales_by_sku テーブル名の横にあるプルダウン メニューを使用するか、ブラウザを更新して左側のメニューにあるリストを確認します。

3. セールスチームから提供された販売レコードを挿入します。

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. テーブルをプレビューして、このレコードが表示されることを確認します。テーブル名をクリックして、結果を確認します。

履歴データを連結する

同じスキーマを持つデータを連結するにはさまざまな方法があります。中でもよく使われるのは、UNION を使用する方法と、テーブル ワイルドカードを使用する方法の 2 つです。

• UNION は SQL 演算子のひとつで、複数の結果セットの行を連結します。
• テーブル ワイルドカードを使用すると、簡潔な SQL ステートメントを使用して複数のテーブルをクエリできます。ワイルドカード テーブルは標準 SQL でのみ使用できます。

1. 以下の 2 つのテーブルのすべてのレコードを返す UNION クエリを記述します。

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 注: UNION と UNION ALL の違いは、UNION では重複レコードが含まれないことです。

日次販売テーブルの数が増えると、互いに連結された多数の UNION ステートメントを記述する必要が出てきます。

この問題を解決するには、テーブル ワイルドカード フィルタと _TABLE_SUFFIX フィルタを使用します。

2. テーブル ワイルドカード（*）を使用して、2017 年の ecommerce.sales_by_sku_ テーブルのすべてのレコードを選択するクエリを記述します。

解答例:

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. 前のクエリを変更して、結果を 2017 年 8 月 2 日のレコードのみに制限するフィルタを追加します。

解答例:

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' 注: パーティション分割テーブルを作成して、日次販売データが自動的に正しいパーティションに取り込まれるようにする方法もあります。

[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。 追加のレコードを連結する

お疲れさまでした

ここでは、サンプル e コマースデータを調べるためにレポート テーブルを作成し、SQL の JOIN と UNION を使用してビューを操作しました。

次のステップと詳細情報

• Google アナリティクス アカウントをお持ちで、BigQuery で独自のデータセットをクエリするには、こちらのエクスポート ガイドの手順を実施してください。
• JOINS の SQL 構文の詳細を確認する場合は、BigQuery の JOIN に関するドキュメントをご覧ください。
• Google データセット検索もリソースとしてぜひご利用ください。
• 以下に示すその他のラボをご確認ください。
  • Exploring NCAA Data with BigQuery
  • Cloud Composer: Copying BigQuery Tables Across Different Locations

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

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

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

ラボの最終テスト日: 2023 年 10 月 31 日

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