GSP009

概要

このラボでは、BigQuery を使用して気象観測の履歴を分析し、気象データを他のデータセットと組み合わせて使用します。

学習内容

このラボでは以下を行います。

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

はじめに

このラボでは、BigQuery で 2 つの一般公開データセット（NOAA の気象データとニューヨーク市の市民の苦情データ）を使用します。

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

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

最終結果は、どのタイプの苦情が天候と相関性があるかを見つけることです。たとえば（さして驚きではありませんが）外の気温が低いとき、住居の暖房についての苦情が最も多いことがわかります。

前提条件

これは、BigQuery と SQL に関して一定の経験がある方を対象とした基礎レベルのラボです。BigQuery や MySQL を使用したことがない場合は、セルフペース ラボの BigQuery: Qwik Start - コンソールで、これらの Google Cloud サービスについてすぐに理解することができます。

設定と要件

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

こちらの手順をお読みください。ラボの時間は記録されており、一時停止することはできません。[ラボを開始] をクリックするとスタートするタイマーは、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. Google Cloud コンソールで、ナビゲーション メニュー > [BigQuery] を選択します。

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

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

BigQuery コンソールが開きます。

3. [エクスプローラ] ペインで、[+追加] をクリックします。

[追加] ウィンドウが開きます。

4. [その他のソース] の下で [名前を指定してプロジェクトにスターを付ける] をクリックします。

5. 「bigquery-public-data」と入力して [スターを付ける] をクリックします。

BigQuery コンソールの [エクスプローラ] ペインに、ラボのプロジェクト ID のプロジェクトと bigquery-public-data プロジェクトの 2 つのプロジェクトが表示されます。

6. BigQuery コンソールの [エクスプローラ] ペインで、bigquery-public-data > noaa_gsod を展開し、gsod2014 テーブルを選択します。

7. テーブル（gsod2014）のウィンドウで [プレビュー] タブをクリックします。

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

9. クエリエディタに次の内容を貼り付けます。

SELECT -- Create a timestamp from the date components. stn, TIMESTAMP(CONCAT(year,"-",mo,"-",da)) AS timestamp, -- Replace numerical null values with actual null AVG(IF (temp=9999.9, null, temp)) AS temperature, AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed, AVG(IF (prcp=99.99, 0, prcp)) AS precipitation FROM `bigquery-public-data.noaa_gsod.gsod20*` WHERE CAST(YEAR AS INT64) > 2010 AND CAST(MO AS INT64) = 6 AND CAST(DA AS INT64) = 12 AND (stn="725030" OR -- La Guardia stn="744860") -- JFK GROUP BY stn, timestamp ORDER BY timestamp DESC, stn ASC

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

下の [進行状況を確認] をクリックして、このラボの進捗状況を確認します。

気象データを調べる

タスク 2. ニューヨーク市民の苦情データを調べる

1. BigQuery コンソールの [エクスプローラ] ペインで、新たに追加された bigquery-public-data プロジェクトを選択し、new_york データセットを展開して 311_service_requests テーブルを選択します。

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

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

4. エディタが閉じている場合は、「+」（クエリを新規作成）アイコンをクリックします。

5. 以下をクエリエディタに貼り付けます。

SELECT EXTRACT(YEAR FROM created_date) AS year, complaint_type, COUNT(1) AS num_complaints FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY year, complaint_type ORDER BY num_complaints DESC

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

7. 結果を確認し、数多く寄せられている苦情を見つけます。このラボの後半部分では、こうした苦情に天候と相関関係があるかどうかを判断します。

下の [進行状況を確認] をクリックして、このラボの進捗状況を確認します。

ニューヨーク市民の苦情データを調べる

タスク 3. 気象データの新しいテーブルを保存する

1. BigQuery コンソールの [エクスプローラ] ペインで、プロジェクト ID の横にある 3 つ並んだ点をクリックし、[データセットを作成] をクリックします。

2. [データセットを作成する] ダイアログの [データセット ID] に「demos」と設定し、他のオプションはすべてデフォルト値のままにします。

3. [データセットを作成] をクリックします。これで、プロジェクトには「demos」というデータセットが設定されました。

4. 「+」（クエリを新規作成）アイコンをクリックして、次のクエリを実行します。

SELECT -- Create a timestamp from the date components. timestamp(concat(year,"-",mo,"-",da)) as timestamp, -- Replace numerical null values with actual nulls AVG(IF (temp=9999.9, null, temp)) AS temperature, AVG(IF (visib=999.9, null, visib)) AS visibility, AVG(IF (wdsp="999.9", null, CAST(wdsp AS Float64))) AS wind_speed, AVG(IF (gust=999.9, null, gust)) AS wind_gust, AVG(IF (prcp=99.99, null, prcp)) AS precipitation, AVG(IF (sndp=999.9, null, sndp)) AS snow_depth FROM `bigquery-public-data.noaa_gsod.gsod20*` WHERE CAST(YEAR AS INT64) > 2008 AND (stn="725030" OR -- La Guardia stn="744860") -- JFK GROUP BY timestamp

5. クエリエディタ セクションで、[展開] > [クエリ設定] の順にクリックします。

6. [クエリの設定] ダイアログで次のフィールドを設定します。他のフィールドはデフォルト値のままにします。

[送信先]: [クエリ結果の宛先テーブルを設定する] を選択します

[データセット]: 「demos」と入力し、データセットを選択します

[テーブル ID]: 「nyc_weather」と入力します

[結果サイズ]: [大容量の結果を許可する（サイズ上限なし）] をオンにします

7. [保存] をクリックします。

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

作成したデータセット（demos）に結果が保存されます。

9. [展開] > [クエリ設定] に戻り、[送信先] フィールドで [一時テーブルにクエリ結果を保存] を選択します。これで demos データセットは今後のクエリの宛先ではなくなります。

10. [保存] をクリックしてクエリを閉じます。

下の [進行状況を確認] をクリックして、このラボの進捗状況を確認します。

気象データの新しいテーブルを保存する

タスク 4. 天候と苦情の間の相関関係を見つける

CORR 関数を使用して、苦情の数と気温を比較します。

1. クエリエディタに戻り、次のクエリを実行します。

SELECT descriptor, sum(complaint_count) as total_complaint_count, count(temperature) as data_count, ROUND(corr(temperature, avg_count),3) AS corr_count, ROUND(corr(temperature, avg_pct_count),3) AS corr_pct From ( SELECT avg(pct_count) as avg_pct_count, avg(day_count) as avg_count, sum(day_count) as complaint_count, descriptor, temperature FROM ( SELECT DATE(timestamp) AS date, temperature FROM demos.nyc_weather) a JOIN ( SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count FROM (SELECT DATE(created_date) AS date, concat(complaint_type, ": ", descriptor) as descriptor, COUNT(*) AS day_count FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY date, descriptor)x JOIN ( SELECT DATE(timestamp) AS date, COUNT(*) AS all_calls_count FROM `demos.nyc_weather` GROUP BY date )y ON x.date=y.date )b ON a.date = b.date GROUP BY descriptor, temperature ) GROUP BY descriptor HAVING total_complaint_count > 5000 AND ABS(corr_pct) > 0.5 AND data_count > 5 ORDER BY ABS(corr_pct) DESC

この結果は、暖房に関する苦情と気温との間には負の相関関係があり（つまり、暖房に関する苦情は寒い日に多い）、枯れ木に関する苦情と気温との間には正の相関関係がある（つまり、枯れ木に関する苦情は暑い日に多い）ことを示しています。

次に、CORR 関数を使用して、苦情の数と風速を比較します。

2. 「+」（クエリを新規作成）アイコンをクリックして、次のクエリを実行します。

SELECT descriptor, sum(complaint_count) as total_complaint_count, count(wind_speed) as data_count, ROUND(corr(wind_speed, avg_count),3) AS corr_count, ROUND(corr(wind_speed, avg_pct_count),3) AS corr_pct From ( SELECT avg(pct_count) as avg_pct_count, avg(day_count) as avg_count, sum(day_count) as complaint_count, descriptor, wind_speed FROM ( SELECT DATE(timestamp) AS date, wind_speed FROM demos.nyc_weather) a JOIN ( SELECT x.date, descriptor, day_count, day_count / all_calls_count as pct_count FROM (SELECT DATE(created_date) AS date, concat(complaint_type, ": ", descriptor) as descriptor, COUNT(*) AS day_count FROM `bigquery-public-data.new_york.311_service_requests` GROUP BY date, descriptor)x JOIN ( SELECT DATE(timestamp) AS date, COUNT(*) AS all_calls_count FROM `demos.nyc_weather` GROUP BY date )y ON x.date=y.date )b ON a.date = b.date GROUP BY descriptor, wind_speed ) GROUP BY descriptor HAVING total_complaint_count > 5000 AND ABS(corr_pct) > 0.5 AND data_count > 5 ORDER BY ABS(corr_pct) DESC

3. 騒音に関する苦情についての [Corr] 列は両方とも負です。風の強い日に騒音に関する苦情が少ない理由を推測できますか。係数は統計的に十分でしょうか。

このように、BigQuery ではさまざまな角度から各種の問題を分析することができます。

下の [進行状況を確認] をクリックして、このラボの進捗状況を確認します。

天候と苦情の間の相関関係を見つける

概要

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

お疲れさまでした

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

クエストを完了する

このセルフペース ラボは、「Scientific Data Processing」クエストの一部です。クエストとは学習プログラムを構成する一連のラボのことで、完了すると成果が認められて上のようなバッジが贈られます。バッジは公開して、オンライン レジュメやソーシャル メディア アカウントにリンクできます。このラボの修了後、次のクエストに登録すれば、すぐにクレジットを受け取ることができます。受講可能なその他のクエストもご確認ください。

次のラボを受講する

「Cloud Dataproc での分散画像処理」に進んでクエストを続けるか、以下のいずれかをお試しください。

• Vertex AI での TensorFlow による赤ちゃんの体重予測
• Vertex AI と BigQuery による出生率データの分析

次のステップと詳細情報

• ニューヨーク市のデータのさらなる分析と、それが天候とどのように相関しているかについては、Reto Meier のブログ投稿をご覧ください。

• BigQuery の一般公開データセットの詳細をご覧ください。

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

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

マニュアルの最終更新日: 2023 年 6 月 27 日

ラボの最終テスト日: 2023 年 6 月 27 日

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