はじめに
こんにちは、京セラコミュニケーションシステム 川村（@kccs_takahiro-kawamura）です。
ドコモシェアバイクのデータをBigQueryで分析しよう！PART.2の記事になります。
前回はオープンデータを BigQuery へ定期的に取り込む方法について記載しました。
本記事では、BigQuery と Looker Studio を活用し、早く！確実に！シェアバイクを利用して会社から帰宅するためのダッシュボードを作成します。
本記事は、2022年12月ごろに作成しております。よって、引用している文章などはこの時点での最新となります。ご了承ください。
Looker Studioは、旧データポータルを指します。
本記事の対象者
- BigQuery で地理関数を用いた分析がしたい方
- BigQuery のデータを Looker Studio で表示したい方
ダッシュボードの要件
早く！確実に！シェアバイクを利用して会社から帰宅するためには、次の要件を満たすダッシュボードが欲しいと思いました。
- 利用可能な自転車の多いステーションがわかる
- 会社から近いステーションがわかる
これらの要件を満たすために、BigQuery でSQLを実行します。
利用可能な自転車の多いステーションの検索
まずは、利用可能な自転車の多いステーションを検索します。
利用可能な自転車の数は、station_statusテーブルのnum_bikes_availableカラムに格納されています。
station_statusテーブルだけではどこのステーションかわからないため、station_infomationテーブルと結合し、ステーション名も合わせて出力します。また、is_rentingカラムで貸し出し有効なステーションかどうか判別できるためWHERE句に含めます。
SELECT name, num_bikes_available FROM `docomo_cycle_tokyo.station_status` status
LEFT JOIN `docomo_cycle_tokyo.station_information` info ON status.station_id = info.station_id
WHERE is_renting
以下の結果が得られました。
バッチで定期的にデータを取り込んでいるため、最新のデータだけでなく過去のデータも表示されてしまいました。
BigQuery は、実行するSQLが検索するデータ量に応じて課金が発生するため、これでは不要なデータの検索を行いムダな課金を発生させてしまいます。BigQuery の課金についてはこちらの記事をご確認ください。
最新のデータを表示するために検索範囲を絞り込みます。 テーブル作成時にパーティショニングの取り込み時間による分割を有効にしているので、_PARTITIONTIMEというカラムをWHERE句に含めることで検索範囲を絞り込めます。
WITH status AS (SELECT * FROM `docomo_cycle_tokyo.station_status` WHERE _PARTITIONTIME =
(SELECT MAX(_PARTITIONTIME) FROM `docomo_cycle_tokyo.station_status`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)))
SELECT name AS Station, num_bikes_available FROM `docomo_cycle_tokyo.station_information` AS info
LEFT JOIN status ON info.station_id = status.station_id WHERE is_renting
以下の結果が得られました。
WITH句を用いて最新のデータのみに絞り込んだstation_statusテーブルを一時テーブルとして用意しています。WHERE句でサブクエリを用いて_PARTITIONTIMEカラムを絞り込むことで最新のデータのみを検索しています。
ただし、 単純に
SELECT MAX(_PARTITIONTIME) `docomo_cycle_tokyo.station_status`
としてしまうとサブクエリ内でstation_statusテーブルを全件検索してしまうことになるため、サブクエリ内のWHERE句で直近3時間のデータに絞り込むことで全件検索を避けています。
ここまでで、最新のデータを用いて利用可能な自転車の多いステーションがわかるようになりました。
会社から近いステーションの検索
続いて、会社から近いステーションを検索します。 BigQuery には地理関数が用意されており、そのうち今回はST_GeogPoint関数とST_DISTANCE関数を使用します。
ST_GeogPoint関数は、緯度経度からその地点のGEOGRAPHY値を求めます。ST_DISTANCE関数は、2点間の最短距離を計算します。これは、ほぼ直線距離に近いような値が求められます。
私が現在勤務している場所は、東京都港区三田にあるビルです。ここの緯度経度は（139.74194914694743, 35.64165246203347）のため、これを基準に会社から近いステーションを検索します。
SELECT name AS Station, lon, lat,
ST_DISTANCE(ST_GEOGPOINT(lon, lat), ST_GEOGPOINT(139.74194914694743, 35.64165246203347)) as EstimatedDistance_in_Meters
FROM `docomo_cycle_tokyo.station_information` as info
ORDER BY EstimatedDistance_in_Meters LIMIT 10
以下の結果が得られました。
EstimatedDistance_in_Metersカラムが会社からステーションまでの距離を表しています。
近いステーションだけで良いのでLIMIT句を用いて10件のみを取得しています。
LIMIT句は、結果件数を指定するだけです。検索範囲は絞り込まれないため、LIMIT句を用いてもSQLの実行にかかる費用は削減されません。
ダッシュボード用のビューを作成
ここまでで必要なデータを用意することができました。2つのSQLを合わせて実行してみます。
WITH status AS (SELECT * FROM `docomo_cycle_tokyo.station_status` WHERE _PARTITIONTIME =
(SELECT MAX(_PARTITIONTIME) FROM `docomo_cycle_tokyo.station_status`
WHERE _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)))
SELECT name AS Station, lon, lat,
ST_DISTANCE(ST_GEOGPOINT(lon, lat), ST_GEOGPOINT(139.74194914694743, 35.64165246203347)) AS EstimatedDistance_in_Meters,
num_bikes_available,
CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(CAST(last_reported AS INT64)), 'Asia/Tokyo') AS DATETIME) AS last_reported
FROM `docomo_cycle_tokyo.station_information` AS info
LEFT JOIN status ON info.station_id = status.station_id WHERE is_renting
ORDER BY EstimatedDistance_in_Meters LIMIT 10
以下の結果が得られました。
last_reportedカラムはstation_statusテーブルの更新時間です。ダッシュボードでは、表示しているデータがいつのものなのかわかるようにしたいため追加しておきます。last_reportedカラムはREST APIで取得したままだとエポック秒になっています。そのため、一度TIMESTAMP型に変換し、さらにタイムゾーンを東京にしたいためDATETIME型へ変換しています。
このSQLをビューとして保存します。保存→ビューを保存と選択し、必要な情報を入力し保存します。
左側のエクスプローラー内に保存したビューが作成されてます。Looker Studioからこのビューを参照することで上記のSQLの実行結果を得ることができます。
ダッシュボードの作成
Looker Studioにアクセスして空のレポートを作成します。
レポートに追加するデータソースの選択を要求されるので BigQuery を選択します。続けて、先ほど作成したビューを追加できるようにプロジェクト、データセット、表を選択します。
ダッシュボード作成者は、追加するデータソースに対しての読み取り権限が必要になります。
無事にデータを追加できると以下のようなダッシュボードが表示されます。
今回は地理データを活用しているので直感的にわかりやすいバブルマップを使用します。
ダッシュボード上の表を選択してから右上にあるグラフを選択するとビジュアリゼーションのタイプを選択できます。その中からバブルマップを選択します。以下の画像のように設定するとバブルマップ上にステーションの座標が描画されます。
どのステーションに利用可能な自転車が多いのか一目で把握できるようnum_bikes_availableが多いステーションほど大きな円で描画されています。
表示されているデータがいつ時点のデータなのかわかるように最終更新日時を表示します。上部にあるメニューからグラフを追加を選択してスコアカードを選択します。期間のディメンションをlast_reported(日付)にし、指標をlast_reportedにします。指標のlast_reportedはMAXを使用し、別名で最終更新日時とします。また、背景が透明だとわかりづらいのでスタイルで背景色と文字色を変更します。
表でバブルマップの情報も表示しておきます。先ほどと同様にグラフを追加を選択して表を選択します。期間のディメンションをlast_reported(日付)にし、ディメンションをStation、指標をnum_bikes_available、並び替えをnum_bikes_availableにします。また、ディメンションと指標はわかりやすいように別名でステーションと乗車可能バイク数にします。こちらもスタイルで背景色と文字色を変更します。
以上でダッシュボードの完成です。
最後に
前回と今回の記事で、オープンデータをREST API経由で定期的に BigQuery へ蓄積し、SQLで分析した結果を Looker Studio を用いてダッシュボードにすることができました。
BigQuery でデータ分析を行う際は必要なデータの範囲を検討し、パーティションを限定して実行することが重要です。
今回は最新データを活用したデータ分析でした。次回は、蓄積していることを活かしたデータ分析に取り組みたいと思います。