概要

このラボでは、2 つの異なる一般公開データセットを分析します。それらに対して別々にクエリを実行したり、それらを組み合わせてクエリを実行したりして、興味深い分析情報を引き出します。

ラボの内容

このラボの内容:

• BigQuery コンソールで、インタラクティブ クエリを実行します。
• 複数のデータセットを組み合わせて分析を実行します。

前提条件

これは、BigQuery と SQL に関して一定の経験がある方を対象とした基礎レベルのラボです。

はじめに

このラボでは、BigQuery で 2 つの一般公開データセット（米国海洋大気庁（NOAA）の気象データとニューヨーク市の自転車レンタルデータ）を使用します。

データ サイエンティストにとって非常に便利な Google Cloud Platform の特徴を体験できます。

1. サーバーレス: データ処理のためにお使いのマシンにデータをダウンロードする必要はありません。データセットはクラウドに置いたままで作業します。
2. 使いやすさ: インデックス処理などのデータの準備を事前に行わなくても、データセットに対してアドホック SQL クエリを実行できます。これはデータ探索において非常に便利です。
3. スケーリング: 非常に大規模なデータセットに対してインタラクティブにデータ探索を行います。データをタイムリーに処理するためのサンプリングは必要ありません。
4. 共有機能: 異なるデータセットのデータに対して問題なくクエリを実行できます。BigQuery はデータセットを共有するための便利な手段です。もちろん、データを非公開にすることも特定の人物と共有することもでき、すべてのデータを一般公開する必要はありません。

最終的に、雨の日にはレンタル自転車の利用が減るかどうかを特定することが目標です。

設定と要件

ラボの設定

各ラボでは、新しい 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-public-data（具体的には new_york_citibike データセット）を探索します。citibike_trips テーブルのスキーマを調べ、クエリを実行してルートの所要時間を分析し、各自転車の合計走行距離を計算します。

1. Google Cloud コンソールのナビゲーション メニュー（）で、[BigQuery] をクリックします。

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

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

3. 左側のペインで [+ 追加] > [名前を指定してプロジェクトにスターを付ける] をクリックして、次にポップアップ ウィンドウに「bigquery-public-data」と入力し、最後に [スターを付ける] をクリックします。

4. BigQuery コンソールの左側のペインに、Qwiklabs プロジェクト ID のプロジェクトと bigquery-public-data プロジェクトの 2 つのプロジェクトが表示されています。

5. BigQuery コンソールの左側のペインで、[bigquery-public-data] > [new_york_citibike] > [citibike_trips] テーブルを選択します。

6. テーブル（citibike_trips）のウィンドウで [スキーマ] タブをクリックします。

7. 列名とデータ型を確認します。

8. 青色の + ボタンをクリックして、新しいクエリを作成します。

次のクエリを入力します。

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(tripduration, 10)[OFFSET (5)] AS typical_duration, COUNT(tripduration) AS num_trips FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

9. [実行] をクリックします。結果を確認し、このクエリによって何が行われたのかを検討してみましょう。

   ヒント: よく利用される片道レンタル 10 件の一般的な利用時間

10. さらに以下のクエリを実行して、興味深い事実をもう 1 つ特定します。このクエリは、データセット内の各自転車の総移動距離を調べます。ここでは結果を上位 5 件に制限しています。

WITH trip_distance AS ( SELECT bikeid, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data.new_york_citibike.citibike_trips`, `bigquery-public-data.new_york_citibike.citibike_stations` as s, `bigquery-public-data.new_york_citibike.citibike_stations` as e WHERE start_station_name = s.name AND end_station_name = e.name) SELECT bikeid, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bikeid ORDER BY total_distance DESC LIMIT 5 注: このクエリでは、自転車ステーションの情報を取得するために、データセットのもう 1 つのテーブル（citibike_stations という名前のテーブル）も使用しています。

タスク 2. 気象データセットを調べる

このタスクでは、ghcn_d データセットを探索します。ghcnd_2015 テーブルをプレビューし、クエリを実行してニューヨークの気象ステーションから降水量データを取得します。

1. BigQuery コンソールの左側のペインから、新しく追加した bigquery-public-data プロジェクトを選択し、[ghcn_d] > [ghcnd_2015] を選択します。

2. 次に [プレビュー] タブをクリックすると、コンソールには次のように表示されます。

列とデータの値を確認します。

3. 青色の + ボタンをクリックして新しいクエリを作成し、次のように入力します。

SELECT wx.date, wx.value/10.0 AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE id = 'USW00094728' AND qflag IS NULL AND element = 'PRCP' ORDER BY wx.date

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

このクエリは、ニューヨークにある気象観測所の ID（これは NEW YORK CNTRL PK TWR に対応します）を指定して、そこで観測された 2015 年の各日付の降水量（mm）を取得します。

タスク 3. 降雨と自転車レンタルの間の相関関係を見つける

このタスクでは、new_york_citibike.citibike_trips データセットと ghcn_d.ghcnd_2015 気象データセットを結合して、雨の日と雨でない日の自転車の平均利用回数を特定し、降雨と自転車レンタルの間の相関関係を分析します。

自転車レンタルのデータを気象データに結合して、雨の日にはレンタル自転車の利用が減るかどうかを調べてみましょう。

1. 青色の + ボタンをクリックして新しいクエリを作成し、次のように入力します。

WITH bicycle_rentals AS ( SELECT COUNT(starttime) as num_trips, EXTRACT(DATE from starttime) as trip_date FROM `bigquery-public-data.new_york_citibike.citibike_trips` GROUP BY trip_date ), rainy_days AS ( SELECT date, (MAX(prcp) > 5) AS rainy FROM ( SELECT wx.date AS date, IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE wx.id = 'USW00094728' ) GROUP BY date ) SELECT ROUND(AVG(bk.num_trips)) AS num_trips, wx.rainy FROM bicycle_rentals AS bk JOIN rainy_days AS wx ON wx.date = bk.trip_date GROUP BY wx.rainy

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

自転車レンタルのデータセットと気象のデータセットという、まったくソースの異なる 2 つのデータセットを結合した結果が表示されます。

このクエリの結果から、雨の日にはニューヨーク市のレンタル自転車の利用が 47% 減少することがわかります。

まとめ

このラボでは、2 つのデータセットに対してアドホック クエリを実行しました。クラスタを設定したりインデックスを作成したりすることなく、データに対するクエリを実行できました。また、2 つのデータセットを組み合わせて、興味深い分析情報を得ることもできました。また、これらをすべて、ブラウザから離れることなく行えました。

お疲れさまでした

BigQuery で非常に興味深いクエリを実行する方法を学習しました。

ラボを終了する

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

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

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

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

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

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

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