GSP009

概要

このラボでは、BigQuery を使用して NOAA の気象観測の履歴を分析し、気象データをニューヨーク市の 311 市民サービスコールセンター（NYC311）に寄せられた市民の苦情データと組み合わせて使用します。これは、データ変数が互いに相関する仕組みを示した Reto Meier のブログ投稿と非常によく似た内容です。

サイエンティストにとって非常に便利な、Google Cloud の以下のような特長を体験できます。

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

最終的に、天候と相関性がある苦情のタイプを見つけることが目標です。たとえば（当然ではありますが）外の気温が低い時期なら、住居の暖房についての苦情が最も多いことがわかります。

学習内容

このラボで行うことは以下のとおりです。

• BigQuery コンソールで、インタラクティブ クエリを実行する。
• 複数のデータセットを組み合わせて分析を行う。
• CORR 関数を使用して、データセット間の相関が正、負、または相関なしかを判断する。

前提条件

これは、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 データセットを検索して、311_service_requests テーブルを選択します。

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

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

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

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. [+]（SQL クエリの作成）アイコンをクリックして、次のクエリを実行します。

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 関数で測定される強い相関は、2 つの変数の間に密接で一貫した関係があることを示します。一方の変数の値が増加すると、他方の変数の値も予測可能な形で増加（正の相関）または減少（負の相関）する傾向があります。相関が強いと判断される値は、通常、絶対値で 0.7 以上です。つまり、一方の変数の変化によって、もう一方の変数の変化の少なくとも 49% を説明できるということです。

次に、CORR 関数を使用して、受信した苦情の数と 1 日の気温を対比します。

1. SQL クエリの作成 [+] をクリックして、次のクエリを実行します。

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. [+]（SQL クエリの作成）アイコンをクリックして、次のクエリを実行します。

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] 列は両方とも負です。風の強い日に騒音に関する苦情が少ない理由を推測できますか。係数は統計的に十分でしょうか。

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

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

お疲れさまでした

このラボでは、クラスタを設定したりインデックスを作成したりすることなく、データに対するクエリを実行できることを確認しました。また、2 つのデータセットを組み合わせて、結果の関連性を調べ、興味深い分析情報も引き出しました。

次のステップと詳細情報

• BigQuery の CORR() 関数についての詳細をご覧ください。

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

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

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

マニュアルの最終更新日: 2025 年 3 月 20 日

ラボの最終テスト日: 2025 年 3 月 20 日

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