LookerでCOVID-19関連のデータを可視化してみた

コロナウィルスに対する分析や対策等は専門家に任せましょう
特集

たまちゃん

2020.04.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

奈良県でリモートワークしている玉井です。

2020年4月現在、世界中で猛威を振るっている新型コロナウイルス感染症 （COVID-19）ですが、これに立ち向かうべく、医療関係者をはじめ、色々な人・企業が様々な取組を実施しています。

私が業務で扱うBIツールの企業も下記のような取組を行っています。

Tableau

GCP（Looker）

※ダッシュボード部分にLookerが使用されている

正式な分析や対策等は上記の取組等に託したいと思いますが、Lookerの学習を兼ねて、自分でもCOVID-19関連のデータを可視化してみようと思ったので、本記事ではそれについて書きます。

注意事項

  • 本記事はあくまで「可視化」までを実施するものです。
  • その先の「分析」は一切行いません（私は医療従事者でも何でもない）。
    • 「もうすぐこの地域で感染爆発が起こりそう」みたいな予測等はしない
    • 「ウイルス対策として○○をすればいい」みたいな事を立案する等はしない

作ったダッシュボード

先に、今回作成したダッシュボードの完成形を載せます。日本でCOVID-19に感染した人等に関するダッシュボードです。

可視化の流れ

  • データを準備する
  • Connectionを設定
    • 上記のSnowflake上のシェアリングデータベースとLookerを接続する
  • Projectを作成する
  • SQL Runnerでデータを確認する
  • 色々可視化してみる

データを準備する（使用するデータを確認）

今回使用するデータはこちら。

詳細は記事を読んでいただきたいところですが、別企業が用意してくれているSnowflakeのデータシェアリングを使用します。だから、データを準備してテーブルにロードする時間はZEROです。

Connectionを設定する

基本的に下記と同じです。

微妙に異なる部分を下記にまとめます。

補足事項など

  • Snowflake上に作成したLooker用の仮想ウェアハウスに、COVID-19関連のデータに対する権限を付与する必要があるが、通常のDBではなくデータシェアリングされたDBなので、若干手順が異なる。
    • Snowflakeの話なので今回は詳述しない
    • 別途ブログにしたい
  • 今回のデータはUTCなので、JSTにコンバートする設定をした。
    • 日付のフィルタを考える時に日本時間で考えたかったため

Projectを作成する

作成したConnectionを元に、新しいProjectを作成します。

SQL Runnerで確認

「LookerからDBに対して正常に接続できているかどうか」は、Connectionを作成する際にも「テスト接続」で確認できます。ただ、ここでは、データの中身を概観するという意味でも、SQL Runnerを使って、SQLでデータを問い合わせることができるかどうか見てみましょう。

問題なさそうですね。

日本の感染者数と死亡者数を確認できるダッシュボード

今回のCOVID-19関連のデータはアメリカやイタリアのデータが豊富なのですが、せっかくなので、ここは日本のデータを可視化してみたいと思います。

データの中身をもう少し見てみる

先程から引き続いて、SQL Runnerでもう少しデータを見てみました。今回は日本の感染者数と死亡者数を可視化したいので、下記のテーブルを使用することにしました。

WHO_SITUATION_REPORTS

日ベースの、各国のコロナウィルス感染者数と死亡者数がまとめられているデータです。

Viewファイルにmeasureを追記

Project作成時に、ある程度LookMLが自動生成されています。1テーブル1Viewになっています。そして、今回使用するViewはwho_situation_reports.viewです。各カラムがdimensionとして自動生成されています。

ただし、Lookerの仕様上、measureについては自動生成されません（レコード数だけある）。今回可視化したい物の中でmeasureとして扱うと思われる「感染者数」「死亡者数」について、measureを追加で定義します。measureのtypeは、作成したいダッシュボード（グラフ）によって決めます。今回は日別の感染者数の推移などを可視化したいとぼんやり考えていました。日別に集計するため、typeはsumです（まあ、後からいつでも変更・追記できるので、あまり深く考えなくてもいいかもしれません）。

  measure: sum_total_cases {
    type: sum
    sql: ${TABLE}."TOTAL_CASES" ;;
  }

  measure: sum_cases_new {
    type: sum
    sql: ${TABLE}."CASES_NEW" ;;
  }

  measure: sum_deaths {
    type: sum
    sql: ${TABLE}."DEATHS" ;;
  }

  measure: sum_deaths_new {
    type: sum
    sql: ${TABLE}."DEATHS_NEW" ;;
  }

Modelを確認（ちょっと変更）

modelファイルを確認します。View毎にExploreが自動生成されています。

このままでもいいのですが、modelファイルの記述の練習も兼ねて、ちょっと変更してみました。

explore: who_situation_reports {
  group_label: "covid19"
  description: "WHO situation reportsと渡航制限データを結合"
  join: hum_restrictions_country {
    type: left_outer
    sql_on: ${iso3166_1} = ${hum_restrictions_country.iso3166_1} and ${country} = ${hum_restrictions_country.country} ;;
    relationship: many_to_many
  }

使用するテーブルはWHO_SITUATION_REPORTSだけなのですが、ちょっとjoinの練習がしたかったので、結合できそうなテーブルを探してjoinをしてみました。

この2つのテーブルは、どうやら渡航制限に関するデータ（国バージョンと航空会社バージョン）のようです。とりあえず国名で結合できそうなテーブルだったので結合してみました。

今回使用しているデータは、キレイにリレーションシップが貼られるような、「いわゆるDB設計」がされているものではありません。Covid19関連のデータがドドッとテーブル毎にある感じです。ですので、結合キー及びrelationshipについては、私が独断（データを見て）で決めてます。

もちろんユニークキーもありません。しかし、Lookerは「ユニークキーが無い場合は、カラムを組み合わせて作れ」というベストプラクティスがあるので、who_situation_reports.viewを追加しました。

dimension: manual_primary_key {
  type: string
  primary_key: yes
  sql: CONCAT(${TABLE}."COUNTRY",${TABLE}."COUNTRY_REGION", ${TABLE}."DATE")  ;;
 }

ユニークになるまで適当にカラムをくっつけています。

Lookerにおけるプライマリーキーの話をすると長くなるので、ひとまず下記をどうぞ。

日本の最新感染者数（Single Value）を作る

exploreが定義できたので、これを使ってダッシュボードのタイル（Look）を作っていきます。まず、日本の最新感染者数を表示するLookを作ります。

各項目の配置

  • 感染者数を表示したいのでsum_total_casesを配置する
  • 日本のデータを表示したいのでCountryをフィルタに配置する
    • Japanという値のみにフィルタリング
  • 最新のデータを表示したいので、DateをDate形式でフィルタに配置する
    • 今回の「COVID-19関連のデータ」が国外のものなので、最新データは日本時間の1日前
    • Advanced設定で 1 days agoとする

Lookerのフィルタ式（Advancedで書けるやつ）の詳細は下記を参照。

フィルタはこんな感じ。

文言とか入れて完成。

数字が間違っていないか確認

念の為、数値が間違っていないか確認します。上記は2020年4月6日時点のデータです。

あってますね。

量産する

同形式のLookをあと3つ量産しました（死亡者数とか1日前の新規感染者数など）。

日本の感染者数の推移

次に、感染者数の日別の推移を折れ線グラフとして作成します（ついでにその日に確認された新規感染者数も棒グラフとしてプロットします）。

配置

  • 日別で感染者数を集計したいのでDateをDate形式で配置する
  • 感染者数を集計したいのでsum_total_casesを配置する
  • その日の新規感染者数も集計したいのでsum_cases_newを配置する
  • 日本のデータを表示したいのでCountryをフィルタに配置する

下記の通りになりました。

日付によってはデータが無いため、そこだけ目立ってしまっています。

NULLな月は除去

NULLな部分は無視して線で繋ぐようにします。

まず、表計算で下記の計算を行います。

NOT is_null(${who_situation_reports.sum_total_cases})

表計算は下記のメニューからいけます。

この表計算はNULLだったらNOを返します。ですので、設定でNOという値は非表示にすることで、値がNULLの部分は可視化しないようにできます。

この方法の詳細は下記参照。

キレイなグラフができました。

2つ目のmeasureの表示形式を変える

sum_cases_newは折れ線じゃなくて棒にします。その設定はVISUALIZATIONのSeries設定から可能です。

おまけ

CountryをPivot配置して、国をフィルタでいくつか選択すると、各国の感染者数の推移グラフを出すことも。

量産する

同形式のグラフを複製し、死亡者数バージョンも作成します。

ダッシュボードを作成する

ダッシュボードに必要なLookを一通り用意したら、それらをがっちゃんこしてダッシュボードのできあがり。

結合した渡航制限のテーブルについて

せっかく結合したので、「国名をクリックしたら渡航制限情報のページに飛ぶ」っていうのをやりたかったのですが…。

なんかURLが変…。

そもそもデータ自体が、完璧にキレイなURLとしては格納されていなかったため、そのままURL遷移先として使えるカラムではありませんでした。

ちなみに、国名に別カラムのURLリンクを用意する方法は下記の通りです。

dimension: country {
    type: string
    map_layer_name: countries
    sql: ${TABLE}."COUNTRY" ;;
    link: {
      label: "restrict_source"
      url: "{{ sources._value }}"
    }

{{ sources._value }}っていうのは、同じViewに定義されているsourcesというDimensionの値を参照する…という感じです。

おわりに

実は、このダッシュボードの真髄は、参照しているデータがSnowflakeのデータシェアリングであることなのです。この「COVID-19関連のデータ」は、他企業が運用しているデータをシェアしてもらっているので、運用元がデータを更新してくれれば、それを参照している今回のダッシュボードのデータも自動で更新されます。明日このダッシュボードを見ると、また新しいデータが更新されているはずです。

気になる方は、ぜひSnowflakeのデータシェアリング×Lookerをチェックしてみてください。

*

関連記事

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

渡部晃季

2023.05.02