チェックポイント
Create a new dataset to store the tables
/ 10
Create a table
/ 10
Working as intended
/ 20
Create a table
/ 20
Create view
/ 10
Create a new view to returns recent 10 transactions
/ 20
Run a query with session_user in views for limiting data access
/ 10
BigQuery で永続テーブルとアクセス制御ビューを作成する
GSP410
概要
BigQuery は、Google が低価格で提供する NoOps、フルマネージドの分析データベースです。BigQuery では、インフラストラクチャを所有して管理したりデータベース管理者を置いたりすることなく、テラバイト単位の大規模なデータでクエリを実行できます。また、SQL が採用されており、従量課金制というメリットもあります。このような特長を活かし、有用な情報を得るためのデータ分析に専念できます。
ここでは、ecommerce データセットを使用します。このデータセットには、Google Merchandise Store に関する数百万件の Google アナリティクス レコードが含まれており、BigQuery に読み込まれています。このデータセットのコピーを使用して、フィールドや行からどのような分析情報が得られるのかを確認します。
このラボでは、既存の ecommerce データセットから新しい永続的なレポート テーブルと論理ビューを作成する方法について学びます。
設定と要件
[ラボを開始] ボタンをクリックする前に
こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、Google Cloud のリソースを利用できる時間を示しています。
このハンズオンラボでは、シミュレーションやデモ環境ではなく、実際のクラウド環境を使ってご自身でラボのアクティビティを行うことができます。そのため、ラボの受講中に Google Cloud にログインおよびアクセスするための、新しい一時的な認証情報が提供されます。
このラボを完了するためには、下記が必要です。
- 標準的なインターネット ブラウザ(Chrome を推奨)
- ラボを完了するために十分な時間を確保してください。ラボをいったん開始すると一時停止することはできません。
ラボを開始して Google Cloud コンソールにログインする方法
-
[ラボを開始] ボタンをクリックします。ラボの料金をお支払いいただく必要がある場合は、表示されるポップアップでお支払い方法を選択してください。 左側の [ラボの詳細] パネルには、以下が表示されます。
- [Google コンソールを開く] ボタン
- 残り時間
- このラボで使用する必要がある一時的な認証情報
- このラボを行うために必要なその他の情報(ある場合)
-
[Google コンソールを開く] をクリックします。 ラボでリソースが起動し、別のタブで [ログイン] ページが表示されます。
ヒント: タブをそれぞれ別のウィンドウで開き、並べて表示しておきましょう。
注: [アカウントの選択] ダイアログが表示されたら、[別のアカウントを使用] をクリックします。 -
必要に応じて、[ラボの詳細] パネルから [ユーザー名] をコピーして [ログイン] ダイアログに貼り付けます。[次へ] をクリックします。
-
[ラボの詳細] パネルから [パスワード] をコピーして [ようこそ] ダイアログに貼り付けます。[次へ] をクリックします。
重要: 認証情報は左側のパネルに表示されたものを使用してください。Google Cloud Skills Boost の認証情報は使用しないでください。 注: このラボでご自身の Google Cloud アカウントを使用すると、追加料金が発生する場合があります。 -
その後次のように進みます。
- 利用規約に同意してください。
- 一時的なアカウントなので、復元オプションや 2 要素認証プロセスは設定しないでください。
- 無料トライアルには登録しないでください。
その後このタブで Cloud Console が開きます。
BigQuery コンソールを開く
- Google Cloud コンソールで、ナビゲーション メニュー > [BigQuery] を選択します。
[Cloud コンソールの BigQuery へようこそ] メッセージ ボックスが開きます。このメッセージ ボックスには、クイックスタート ガイドとリリースノートへのリンクが表示されます。
- [完了] をクリックします。
BigQuery コンソールが開きます。
タスク 1. テーブルを保存するための新しいデータセットを作成する
-
BigQuery で、プロジェクト ID の横にある [アクションを表示] アイコンをクリックし、[データセットを作成] を選択します。
-
[データセット ID] に「ecommerce」と入力します。他のオプションはすべてデフォルト値のままにします([データのロケーション]、[デフォルトのテーブルの有効期限])。
-
[データセットを作成] をクリックします。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
タスク 2. CREATE TABLE ステートメントのトラブルシューティング
あなたは、データ アナリスト チームから新しい ecommerce データセットに永続テーブルを作成するためのクエリ ステートメントを受け取りましたが、正常に機能しません。
各クエリが機能しない理由を診断して、問題を解決してください。
BigQuery で SQL を使用してテーブルを作成する際のルール
以下のテーブル作成ルールを読み、正常に機能しないクエリを修正するためのガイドとして活用してください。
- 指定した列リスト、query_statement から推定される列の、いずれかまたは両方が存在する必要がある。
- 列リストと as query_statement 句の両方が存在する場合、BigQuery は、as query_statement 句内の名前を無視し、位置をもとにして列を列リストに一致させる。
- as query_statement 句が存在し、列リストが存在しない場合、BigQuery は、列の名前と型を as query_statement 句から判断する。
- 列リスト、as query_statement 句のいずれかで列名が指定されている必要がある。
- 列名を重複させることはできない。
クエリ 1: 列、列、列
- 以下のクエリを BigQuery エディタに追加して [実行] をクリックし、エラーを診断して次の質問に答えてください。
Error: CREATE TABLE has columns with duplicate name fullVisitorId at [7:2]
上のクエリはどのテーブル作成ルールに違反していますか。
クエリ 2: 列の再確認
- 以下のクエリを BigQuery エディタに追加して [実行] をクリックし、エラーを診断して次の質問に答えてください。
Error: The number of columns in the column definition list does not match the number of columns produced by the query at [5:1]
上のクエリはどのテーブル作成ルールに違反していますか。
fullVisitorId
と channelGrouping
の 2 つの列を含むスキーマが指定されていますが、クエリ ステートメントでは、返されるすべての列(*)が指定されています。クエリ 3: 本当に有効か
- 以下のクエリを BigQuery エディタに追加して [実行] をクリックし、エラーを診断して次の質問に答えてください。
Valid: This query will process 1.1 GiB when run.
ルール 2 を思い出してください。列リストと as
query_statement 句の両方が存在する場合、BigQuery は、as
query_statement 句内の名前を無視し、位置をもとにして列を列リストに一致させます。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
クエリ 4: ゲートキーパー
- 以下のクエリを BigQuery エディタで実行し、エラーを診断して次の質問に答えてください。
Valid: This query will process 907.52 MiB when run.
エラーを修正してクエリを再実行し、正常に実行されることを確認します。
クエリ 5: 正常に機能
- 以下のクエリを BigQuery エディタで実行し、次の質問に答えてください。
-
ecommerce データセットのパネルを参照して、
all_sessions_raw_(1)
が存在することを確認します。
完全なテーブル名が表示されないのはなぜですか。
答え: サフィックス 20170801 のテーブルは、日付によって自動的に分割されます。別の日のテーブルを作成すると、all_sessions_raw_(N)
の N の値がその日数分だけ増加します。データテーブルを分割するさまざまな方法については、別のラボで取り扱っています。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
クエリ 6: 実習
目標: クエリエディタで、2017 年 8 月 1 日の収益があったトランザクションをすべて保存する新しい永続テーブルを作成します。
以下のルールを参考にしてください。
- ecommerce データセットに revenue_transactions_20170801 という名前の新しいテーブルを作成します。このテーブルがすでに存在する場合は置き換えます。
- 元データのソースに data-to-insights.ecommerce.all_sessions_raw テーブルを使用します。
- 収益フィールドを 1,000,000 で割り、INTEGER ではなく FLOAT64 として保存します。
- 収益があったトランザクションのみを最終的なテーブルに含めます(ヒント: WHERE 句を使用します)。
- 20170801 のトランザクションのみを含めます。
- 以下のフィールドを含めます。
- fullVisitorId(必須の文字列フィールドとして)
- visitId(必須の文字列フィールドとして。ヒント: 型変換が必要です)
- channelGrouping(必須の文字列フィールドとして)
- totalTransactionRevenue(FLOAT64 フィールドとして)
- 上の 4 つのフィールドの簡単な説明を追加します(スキーマを参照してください)。
-
fullVisitorId
とvisitId
の値が同じレコードの重複除去を忘れないようにしてください(ヒント: DISTINCT を使用します)。
- BigQuery で上記に対する解答を記述し、下記の正解例と比較します。
正解例:
-
クエリが正常に実行されたら、ecommerce データセットに revenue_transactions_20170801 という名前の新しいテーブルがあることを確認して、そのテーブルを選択します。
-
スキーマが以下の例のようになっていることを確認します。フィールドのタイプ、モード、説明(任意)に注目します。
上流のソースデータの更新に対処する
データが古くならないようにするには
レポート テーブルのデータが古くならないようにするには次の 2 つの方法があります。
- 新しいレコードを挿入するクエリを再実行して永続テーブルを定期的に更新する。これを行うには、BigQuery でクエリをスケジュールするか、Cloud Dataprep または Cloud Dataflow のワークフローを使用します。
- 論理ビューを使用して、ビューが選択されるたびに保存したクエリを再実行する。
以降では、論理ビューの作成方法について学びます。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
タスク 3. ビューを作成する
ビューとは保存されたクエリのことで、ビューが呼び出されるたびにそれらのクエリが実行されます。BigQuery のビューは論理ビューであり、実体化されたビューではありません。つまり、ビューの構成要素として保存されるのはクエリのみで、基になるデータは保存されません。
トランザクションを新しい順に 100 件表示する
- 以下のクエリをコピーして貼り付け、BigQuery で実行します。
- 結果に目を通してフィルタします。2,000 ドルを超えている最も新しいトランザクションはどれですか。
答え:
date |
fullVisitorId |
visitId |
channelGrouping |
totalTransactionRevenue |
20170801 |
9947542428111966715 |
1501608078 |
Referral |
2934.61 |
この ecommerce 一般公開データセットに新しいレコードが追加された場合、最も新しいトランザクションも更新されることになります。
- 時間を節約し、整理や共同作業をしやすくするために、よく使うレポートクエリをビューとして保存できます。以下に例を示します。
vw_
を付けたり、末尾に _vw
や _view
を付けたりするのが慣例になっています。
OPTIONS を使用してビューに説明とラベルを付けることもできます。
- 以下のクエリをコピーして貼り付け、BigQuery で実行します。
-
ecommerce データセットで、新たに作成された
vw_latest_transactions
テーブルを見つけて選択します。 -
[詳細] タブを選択します。
-
ビューに割り当てた [説明] と [ラベル] が BigQuery の UI に正しく表示されていることを確認します。
[詳細] ページでは、ビューを定義しているクエリを表示することもできます。自分またはチームが作成したビューのロジックを把握するのに便利です。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
- では、以下のクエリを実行して新しいビューを作成してみましょう。
Error: Already Exists: Table project-name:ecommerce.vw_latest_transactions
このビューがすでに作成されている場合、おそらくエラーが発生します。なぜでしょうか。
答え: ビュー作成ステートメントが更新され、CREATE OR REPLACE ではなく単に CREATE となっているため、テーブルやビューがすでに存在する場合はそれらを上書きできません。第 3 の選択肢である CREATE VIEW IF NOT EXISTS を使用すると、テーブルやビューが存在しない場合にのみ作成され、存在する場合はエラーなしでスキップされます。
ビューの作成: 実習
シナリオ: あなたは不正防止を担当するチームから、注文金額が 1,000 以上のトランザクションを新しい順に 10 件表示するレポートを作成して、それらを手動で確認できるようにして欲しいと依頼されました。
タスク: 収益が 1,000 以上である 2017 年 1 月 1 日以降のトランザクションを、新しい順に 10 件返す新しいビューを作成します。
以下のルールを参考にしてください。
-
ecommerce データセットに「vw_large_transactions」という名前の新しいビューを作成します。このビューがすでに存在する場合は置き換えます。
-
そのビューに「large transactions for review」という説明を割り当てます。
-
そのビューに「[("org_unit", "loss_prevention")]」というラベルを割り当てます。
-
元データのソースに
data-to-insights.ecommerce.all_sessions_raw
テーブルを使用します。 -
収益フィールドを 1,000,000 で割ります。
-
収益が 1,000 以上のトランザクションのみを含めます。
-
トランザクションを新しい順に並べて 20170101 以降のトランザクションのみを含めます。
-
currencyCode = 'USD' のトランザクションのみを含めます。
-
以下のフィールドを返します。
- date
- fullVisitorId
- visitId
- channelGrouping
- totalTransactionRevenue AS revenue
- currencyCode
- v2ProductName
-
レコードの重複除去を忘れないようにしてください(ヒント: DISTINCT を使用します)。
-
解答:
正解例:
フィールド名のエイリアスをフィルタとして使用することはできないため、WHERE 句で除算を繰り返す必要があります。
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
追加の実習
シナリオ: 作成したクエリは不正防止担当チームに喜ばれ、疑わしい注文を見つけるために日々活用されています。そこで今度は、各注文に含まれる商品のサンプルをクエリの結果に含めるように依頼されました。
BigQuery の文字列集計関数 STRING_AGG と v2ProductName
フィールドを使用して前のクエリを変更し、各注文に含まれる商品の名前をアルファベット順に 10 件返します。
正解例:
ここでは 2 つの要素が追加されています。1 つは、各注文の商品を集計するための STRING_AGG() です。もう 1 つは、集計を実行するために必要な、その他のフィールドの GROUP BY です。
ビューで SESSION_USER() を使用してデータアクセスを制限する
シナリオ: データチームのリーダーから、前述のビューによって返されるデータを表示できる組織内の人物を制限できるようにして欲しいと依頼されました。注文情報は特に機密性が高いため、必要なユーザー以外には共有されないようにする必要があります。
タスク: 先に作成したビューを変更して、セッション ドメインが qwiklabs.net のログイン ユーザーのみが基になるビューのデータを表示できるようにします(注: 特定のユーザー グループの許可リストは、アクセスに関する別のラボで作成します。ここでは、セッション ユーザーのドメインに基づいてユーザーを選別します)。
- 自分のセッションのログイン情報を表示するには、SESSION_USER() を使用する以下のクエリを実行します。
xxxx@qwiklabs.net と表示されます。
- 以下のクエリを変更して、
qwiklabs.net
ドメインのユーザーにのみビューの結果の表示を許可するフィルタを追加します。
正解例:
- 上のクエリを実行して、返されたレコードが表示されることを確認します。
REGEXP_EXTRACT(SESSION_USER(), r'@(.+)') IN ('')
の IN フィルタからすべてのドメインを削除してクエリをもう一度実行し、返されたレコードが 1 つも表示されないことを確認します。
-
vw_large_transactions ビューを再作成して、上の新しいクエリで置き換えます。さらに、OPTIONS のパラメータに
expiration_timestamp
を追加して、ビュー全体の有効期限を今から 90 日に設定します。
正解例:
[進行状況を確認] をクリックして、目標に沿って進んでいることを確認します。
- 以下の SELECT ステートメントを使用して、ビューで返されるデータを(現在のドメイン アクセス権で)表示できること、ビューの詳細に有効期限のタイムスタンプが表示されることを確認します。
お疲れさまでした
ここでは、BigQuery で SQL DDL(データ定義言語)を使用してテーブルとアクセス制御ビューを作成しました。
クエストを完了する
このセルフペース ラボは、「BigQuery for Marketing Analysts」クエストの一部です。クエストとは学習プログラムを構成する一連のラボのことで、完了すると成果が認められてバッジが贈られます。バッジは公開して、オンライン レジュメやソーシャル メディア アカウントにリンクできます。こちらのクエストに登録すると、すぐにクレジットを受け取ることができます。受講可能な全クエストについては、Google Cloud Skills Boost カタログをご覧ください。
次のラボを受講する
「BigQuery に新しいデータセットを取り込む」に進んでクエストを続けるか、以下のおすすめをご確認ください。
次のステップと詳細情報
すでに Google アナリティクス アカウントをお持ちで、BigQuery で独自のデータセットに対してクエリを実行されたい場合は、こちらのエクスポート ガイドに沿って行ってください。
マニュアルの最終更新日: 2023 年 1 月 24 日
ラボの最終テスト日: 2023 年 1 月 24 日
Copyright 2024 Google LLC All rights reserved. Google および Google のロゴは Google LLC の商標です。その他すべての企業名および商品名はそれぞれ各社の商標または登録商標です。