クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はGoogle CloudのBigQueryについて、一般公開データセットにはどんなデータがあるのか調べてみました。
記事の概要
BigQueryの一般公開データセットについて、どんなデータがあるのか調べてみました。
その中で私が興味を惹かれたデータセット5つと、付属のサンプルクエリまたは私が書いたクエリを実行した結果を書きました。
BigQueryの一般公開データセットとは
以下公式ドキュメントからの引用です。
一般公開データセットは、BigQuery に保存され、Google Cloud 一般公開データセット プログラムを通じて一般提供されているデータセットです。この一般公開データセットは BigQuery でホストされ、ユーザーがアクセスして独自のアプリケーションに統合できます。Google では、これらのデータセットの保存費用を負担しており、プロジェクトを介してデータへの公開アクセスを提供しています。データで実行したクエリにのみ料金が発生します。毎月 1 TB まで無料です。
- 引用元 : BigQueryの一般公開データセット
上記の通り、"一般公開データセット"とは、ユーザーが簡単にアクセスすることができる、Googleが用意したデータセットです。多種多様なデータセットがあり、触っているだけでもなかなか面白いです。
- Marketplace データセット
↑ 画像はこちらのリンクのスクリーンショットを引用しています。こちらから皆さんも一般公開データセットを見ることができます。
また、BigQueryを用いたSQL文の練習で、自作した簡単なテーブルを用いることはあるかと思いますが、テーブルの作成は結構手間がかかります。この"一般公開データセット"は特に準備も必要とせず操作できるので、少しだけBigQueryを触ってみたいという方にも良いのではないでしょうか。
そしてこれらのデータセットには、操作したらどのようなデータが得られるのかすぐわかるようにサンプルクエリが記載されているものが多くあります。データセットの説明では、そのサンプルクエリまたは私が作成したクエリを用いた実例も紹介していきます。
注意
BigQueryの一般公開データセットは簡単にアクセスができますが、無料というわけではありません。 BigQueryでのクエリ実行については、毎月1TBまでは無料ですが、以降は料金が発生します。実行する際は気をつけましょう。
- 参照 : オンデマンド分析の料金
実行する前にクエリ実行画面の右上をチェック！処理バイト数の目安が表示されます。
また、私が興味を持ったものを紹介する記事ですので、紹介するデータセットに明確な選定基準はありません。ご承知おきください。
では、以下からBigQueryの一般公開データセットの紹介です。
No.1 Google Trends
データセットについて
トップバッターは、BigQueryのトップ画面でもおすすめしてくれている、Google Trendsです。
- 参照 : Google Trends
- 参照 : Google Trends - International
米国または世界のトレンドワードのランキングについてのデータセットです。自分の興味のあるワードがあるか調べると割と出てくるので、調べていても楽しいのではないでしょうか。
テーブルが4つあるので、違いを以下に記します。
| テーブル名 | 内容 |
|---|---|
| top_rising_terms | 米国の急上昇トレンドワード |
| top_terms | 米国のトレンドワード |
| international_top_rising_terms | 世界の急上昇トレンドワード |
| international_top_terms | 世界のトレンドワード |
日本のトレンドワードを調べたい際は、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
クエリを実行した結果
こちらは記載されているサンプルクエリが非常に良いので紹介させていただきます。
1試合40分中、最初の35分と最後の5分でスリーポイントシュートの割合はどのくらい変化するのか？ということを調べたクエリです。
- 引用元 : NCAA Basketball
#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"↓ 結果
| 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
クエリを実行した結果
こちらは私が作成したクエリです。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 ListenBrainz
データセットについて
音楽関係のデータセットとして、ListenBrainzを挙げておきます。ユーザーの楽曲視聴履歴をまとめたデータセットです。
- 参照 : ListenBrainz
クエリを実行した結果
適当にクエリを実行していたら、私が高校生の頃よく聞いていたアーティストであるSystem of a Downが出てきたのでで調べてみます。 こちらはサンプルクエリを参考に私が作成したクエリで、System of a Downの楽曲で再生回数が多い曲トップ10を調べるクエリです。
SELECT
COUNT(*) as cnt,
track_name,
artist_name,
FROM
`listenbrainz.listenbrainz.listen`
WHERE
artist_name = "System of a Down"
GROUP BY
track_name,
artist_name
ORDER BY
cnt DESC
LIMIT 10↓ 結果
| cnt | track_name | artist_name |
| ----- | ----------- | ---------------- |
| 11147 | Chop Suey! | System of a Down |
| 8169 | Toxicity | System of a Down |
| 5584 | B.Y.O.B. | System of a Down |
| 5538 | Aerials | System of a Down |
| 5410 | Lonely Day | System of a Down |
| 4329 | Prison Song | System of a Down |
| 4224 | ATWA | System of a Down |
| 3732 | Radio/Video | System of a Down |
| 3707 | Deer Dance | System of a Down |
| 3688 | Hypnotize | System of a Down |System of a Downトップ10が取得できました!これがきっかけで久しぶりに聴いたのですが、やはりかっこいい!
こちら2018年までのデータしかないのが残念ですが、音楽が好きな方は是非触ってみてください！
No.5 NYC TLC Trips
データセットについて
最後に紹介するのはNYC TLC Tripsです。こちらはニューヨーク市のタクシー乗車に関する記録に関するデータセットです。
- 参照 : NYC TLC Trips
ものすごく個人的なのですが、昔ニューヨークに旅行に行きまして、サンプルクエリでの出力元データに私の乗車記録もあるかも、、と思って選びました。
クエリを実行した結果
こちらは記載されていたサンプルクエリです。
このクエリは、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 |2015年でのタクシーの曜日別平均速度を出力できました!
1は日曜日を表しますので、日曜がダントツで速度が早いですね。こちらは思い出話ですが、ニューヨークの交通事情が日本と違いすぎて驚いた記憶があります。クラクションがとんでもない頻度で鳴らされていました。。
また、このテーブルのようにフィールドに説明文が記載されているテーブルもあります。おかげでデータの概要がつかめて助かりました。
最後に
一般公開データセットにどんなデータがあるのかつかめていただけていたら幸いです。公開データセットには他にも面白いデータセットがたくさんあったので、是非皆さんも調べてみてくださいね！
以上です。ここまでお読みいただきありがとうございました。
10
