BigQueryの一般公開データセットにはどんなデータがあるのか調べてみた

クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はGoogle CloudのBigQueryについて、一般公開データセットにはどんなデータがあるのか調べてみました。その中で私が選んだデータセット4つと、付属のサンプルクエリまたは私が書いたクエリを実行した結果を紹介します。

BigQueryの一般公開データセットとは

以下公式ドキュメントからの引用です。

一般公開データセットは、BigQuery に保存され、Google Cloud 一般公開データセット プログラムを通じて一般提供されているデータセットです。この一般公開データセットは BigQuery でホストされ、ユーザーがアクセスして独自のアプリケーションに統合できます。Google では、これらのデータセットの保存費用を負担しており、プロジェクトを介してデータへの公開アクセスを提供しています。

• 引用元：BigQuery の一般公開データセット  |  Google Cloud

上記の通り、一般公開データセットとは、ユーザーが簡単にアクセスすることができる、Googleが用意したデータセットです。多種多様なデータセットがあり、興味深いデータセットも数多くあります。

また、BigQueryを用いたSQL文の練習で、自作した簡単なテーブルを用いることはあるかと思いますが、テーブルの作成は結構手間がかかります。この一般公開データセットは特に準備も必要とせず操作できるので、少しだけBigQueryを触ってみたいという方にも良いのではないでしょうか。

そしてこれらのデータセットには、操作したらどのようなデータが得られるのかすぐわかるようにサンプルクエリが記載されているものが多くあります。データセットの説明では、そのサンプルクエリまたは私が作成したクエリを用いた実例も紹介していきます。　　

注意

BigQueryの一般公開データセットは簡単にアクセスができますが、無料というわけではありません。 BigQueryでのクエリ実行については、毎月1TBまでは無料ですが、以降は料金が発生します。実行する際は気をつけましょう。

• 参照：料金  |  BigQuery: クラウド データ ウェアハウス  |  Google Cloud - オンデマンド分析の料金

実行する前にクエリ実行画面の右上をチェックしましょう。処理バイト数の目安が表示されます。

No.1 Google Trends

データセットについて

トップバッターは、BigQueryのトップ画面でもおすすめしてくれている、Google Trendsです。
こちらは米国または世界のトレンドワードのランキングについてのデータセットです。自分の興味のあるワードがあるか調べると割と出てくるので、調べていても楽しいのではないでしょうか。

• 参照：Google Trends（Google Cloudコンソールへアクセスします）
• 参照：Google Trends - International（Google Cloudコンソールへアクセスします）

テーブルが4つあるので、違いを以下に記します。

日本のトレンドワードを調べたい際は、international_top_rising_termsかinternational_top_termsを用いて、WHERE句でcountry_name = "Japan"を入れます。

クエリを実行した結果

私が作成した以下のクエリを実行してみます。
直近1ヶ月で、"ゼルダ"が含まれているワードと、その日付、順位を調べるクエリです。

SELECT
  refresh_date,
  rank,
  term
FROM
  `bigquery-public-data.google_trends.international_top_rising_terms`
WHERE
  refresh_date >= DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 MONTH)
  AND term LIKE "%ゼルダ%"
  AND country_name = "Japan"
GROUP BY
  refresh_date,
  rank,
  term
ORDER BY
  refresh_date DESC,
  rank

↓ 結果

| refresh_date | rank | term                                     | 
| ------------ | ---- | ---------------------------------------- | 
| 2023-05-14   | 6    | ゼルダ の 伝説 ティアーズ オブザ キングダム 攻略  | 
| 2023-05-14   | 20   | ゼルダ の 伝説 ティアーズ オブザ キングダム      | 
| 2023-05-13   | 24   | ゼルダ の 伝説 ティアーズ オブザ キングダム      |

こちらは前回の記事で用いたクエリを少しばかり変更したものです。
前回はトレンドワードが入ったテーブルinternational_top_termsを用いたので、今回は急上昇トレンドワードが入ったテーブルinternational_top_rising_termsを用いてみました。テーブルが違うことで結果も若干違うことがわかります。面白いですね。

また、このテーブルはパーティションが日付を基準として有効化されています。WHERE句で日付を指定すると処理バイト数が減るのでおすすめです（このクエリだと処理バイト数が結構かかってしまいます）。

No.2 NCAA Basketball

データセットについて

スポーツに関するデータは、実感が湧きやすくて良いのではないのでしょうか。
ということで、NCAA Basketballというデータセットを紹介します。NCAAバスケットボールの試合、チーム、選手に関するデータが含まれたデータセットです。

• 参照：NCAA Basketball（Google Cloudコンソールへアクセスします）

クエリを実行した結果

こちらは記載されているサンプルクエリが非常に良いので紹介させていただきます。
1試合40分中、最初の35分と最後の5分でスリーポイントシュートの割合はどのくらい変化するのか？ということを調べたクエリです。

#standardSQL
SELECT
  #first 35 minutes of regulation
  COUNTIF(event_type = "threepointmade"
    AND elapsed_time_sec < 2100) AS threes_made_first35,
  COUNTIF((event_type = "threepointmade"
      OR event_type = "threepointmiss")
    AND elapsed_time_sec < 2100) AS threes_att_first35,
  COUNTIF(event_type = "threepointmade"
    AND elapsed_time_sec < 2100) / COUNTIF((event_type = "threepointmade"
      OR event_type = "threepointmiss")
    AND elapsed_time_sec < 2100) AS three_pt_pct_first35,
  #last five minutes of regulation
  COUNTIF(event_type = "threepointmade"
    AND elapsed_time_sec >= 2100) AS threes_made_last5,
  COUNTIF((event_type = "threepointmade"
      OR event_type = "threepointmiss")
    AND elapsed_time_sec >= 2100) AS threes_att_last5,
  COUNTIF(event_type = "threepointmade"
    AND elapsed_time_sec >= 2100) / COUNTIF((event_type = "threepointmade"
      OR event_type = "threepointmiss")
    AND elapsed_time_sec >= 2100) AS three_pt_pct_last5
FROM
  `bigquery-public-data.ncaa_basketball.mbb_pbp_sr`
WHERE
  home_division_alias = "D1"
  AND away_division_alias = "D1"

• 引用元：NCAA Basketball（Google Cloudコンソールへアクセスします）

↓ 結果

| threes_made_first35 | threes_att_first35 | three_pt_pct_first35 | threes_made_last5 | threes_att_last5 | three_pt_pct_last5  | 
| ------------------- | ------------------ | -------------------- | ----------------- | ---------------- | ------------------- | 
| 132539              | 377798             | 0.3508197502368991   | 19775             | 63308            | 0.31236178681999116 |

スリーポイントシュートの成功率は前半35分は約35%、後半5分は約%31%ということがわかりました。バスケットボール好きにとって非常に興味深い結果となったのではないでしょうか。最後の5分では確率は下がるようですが、残り時間からくる焦りがあるのでしょうか。他のデータも気になるところです。

No.3 GitHub Activity Data

データセットについて

GitHubの情報をまとめたGitHub Activity Dataを紹介します。GitHubリポジトリの情報が含まれており、3TB以上の大容量データセットです。

• 参照：GitHub Activity Data（Google Cloudコンソールへアクセスします）

クエリを実行した結果

こちらは私が作成したクエリです。commit文で最も使われている言葉トップ10を調べました。
注意！記事の初めにも書きましたが、実行前に使用する容量は確認しましょう！GitHub Activity Dataは3TB以上の大容量データセットのため、クエリの内容によっては結構な容量を使います。無料枠以上に使用すると料金が発生しますので注意しましょう。

SELECT文も実行する前に確認!SELECT *だとものすごく容量を使うので、必要な行だけ抽出しましょう!

SELECT
  COUNT(*) as message_count,
  message
FROM `bigquery-public-data.github_repos.commits` 
GROUP BY
  message
ORDER BY
  message_count DESC
LIMIT 10

↓ 結果

| message_count | message                                  | 
| ------------- | ---------------------------------------- | 
| 2485347       | Update README.md                         | 
| 1372500       | Initial commit.                          | 
| 633427        | update.                                  | 
| 614205        | Mirroring from Micro.blog.               | 
| 548574        | Update data.json                         | 
| 548318        | Update data.js                           | 
| 540480        | Initial commit                           | 
| 399726        | Add files via upload                     | 
| 381382        |                                          | 
| 286863        | Can't you see I'm updating the time?     |

commit文トップ10が取得できました！　　

ちなみにこのGitHub Activity Dataには、元データからランダムにレコードを抽出したサンプルテーブルもあります。サンプルテーブルだとレコード数も抑えられてもっと気軽にクエリを実行できます。

No.4 NYC TLC Trips

データセットについて

最後に紹介するのはNYC TLC Tripsです。こちらはニューヨーク市のタクシー乗車に関する記録に関するデータセットです。

• 参照：NYC TLC Trips（Google Cloudコンソールへアクセスします）

ものすごく個人的なのですが、昔ニューヨークに旅行に行きまして、サンプルクエリでの出力元データに私の乗車記録もあるかも！と思って選びました。

クエリを実行した結果

こちらは記載されていたサンプルクエリです。
このクエリは、2015年でのタクシーの曜日別平均速度を出力しています。
私は2015年にニューヨークへ行っていたので、私が乗ったタクシーも含まれているかも!?

#standardSQL
SELECT
  EXTRACT(DAYOFWEEK
  FROM
    pickup_datetime) DAYOFWEEK,
  ROUND(AVG(trip_distance / TIMESTAMP_DIFF(dropoff_datetime,
        pickup_datetime,
        SECOND))*3600, 1) speed
FROM
  `bigquery-public-data.new_york.tlc_yellow_trips_2015`
WHERE
  trip_distance > 0
  AND fare_amount/trip_distance BETWEEN 2
  AND 10
  AND dropoff_datetime > pickup_datetime
GROUP BY
  1
ORDER BY
  1

↓ 結果

| DAYOFWEEK | speed | 
| --------- | ----- | 
| 1         | 14.7  | 
| 2         | 13.4  | 
| 3         | 12.3  | 
| 4         | 12.2  | 
| 5         | 12.2  | 
| 6         | 12.3  | 
| 7         | 13.1  |

• 引用元：NYC TLC Trips（Google Cloudコンソールへアクセスします）

2015年でのタクシーの曜日別平均速度を出力できました!
1は日曜日を表しますので、日曜がダントツで速度が早いですね。ニューヨークの交通事情が日本と違いすぎて驚いた記憶があります。クラクションがとんでもない頻度で鳴らされていました。

また、このテーブルのようにフィールドに説明文が記載されているテーブルもあります。おかげでデータの概要がつかめて助かりました。　　

最後に

一般公開データセットにどんなデータがあるのかつかめていただけていたら幸いです。公開データセットには他にも面白いデータセットがたくさんあったので、是非皆さんも調べてみてくださいね！

以上です。ここまでお読みいただきありがとうございました。

引用・参照まとめ

• BigQuery の一般公開データセット  |  Google Cloud
• 料金  |  BigQuery: クラウド データ ウェアハウス  |  Google Cloud - オンデマンド分析の料金
• Google Trends（Google Cloudコンソールへアクセスします）
• Google Trends - International（Google Cloudコンソールへアクセスします）
• NCAA Basketball（Google Cloudコンソールへアクセスします）
• GitHub Activity Data（Google Cloudコンソールへアクセスします）
• NYC TLC Trips（Google Cloudコンソールへアクセスします）