[Snowflake][Looker]東京都のCOVID19情報をcsvから自分で可視化してみた。

こんにちは、平野です。

先日Lookerを初めて触ってみて、可視化の流れがとりあえずわかったという感じがします。

初めてのBIツール。Lookerのマッププロット記事をトレースしつつ、流れをまとめてみた。

ということで、忘れないうちに別の可視化もやってみます。 Lookerでデータを可視化するための大枠の流れなどは上記の記事でご紹介していますので、 この記事で特に説明していない部分などは、お手数ですがそちらの記事をご参照ください。

題材として、最近は否応なしに目にする、東京都の新型コロナウイルス感染症（COVID19）陽性患者数のデータを使ってみます。 なお、COVID19のデータを扱う記事は以下のようなものもありますので、興味があれば併せてご参照ください。

AWSでコロナウィルス（COVID-19）のデータレイクが公開されたので試してみた

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

SnowflakeのシェアリングデータベースにCOVID-19関連データが公開されています

なお、 この記事の目的は可視化の方法をご紹介することで、 感染症に対する何らかの情報を提供するという意図はありません ので、ご理解のほどお願いいたします。

S3にデータファイルを準備する

まずはデータの準備です。 下記のサイトに、東京都からの公式情報が公開されていましたので、これを可視化してみたいと思います。

東京都 オープンデータカタログサイト - 東京都 新型コロナウイルス陽性患者発表詳細

ダウンロードを選択するとcsvが取得できます。 中を見てみるとこんな内容です。

No,全国地方公共団体コード,都道府県名,市区町村名,公表_年月日,曜日,発症_年月日,患者_居住地,患者_年代,患者_性別,患者_属性,患者_状態,患者_症状,患者_渡航歴の有無フラグ,備考,退院済フラグ
1,130001,東京都,,2020-01-24,金,,湖北省武漢市,40代,男性,,,,,,1
2,130001,東京都,,2020-01-25,土,,湖北省武漢市,30代,女性,,,,,,1
3,130001,東京都,,2020-01-30,木,,湖南省長沙市,30代,女性,,,,,,1
4,130001,東京都,,2020-02-13,木,,都内,70代,男性,,,,,,1
5,130001,東京都,,2020-02-14,金,,都内,50代,女性,,,,,,1
6,130001,東京都,,2020-02-14,金,,都内,70代,男性,,,,,,1
12,130001,東京都,,2020-02-15,土,,都内,40代,男性,,,,,,1
14,130001,東京都,,2020-02-15,土,,都内,40代,男性,,,,,,1
8,130001,東京都,,2020-02-15,土,,都内,50代,女性,,,,,,1

陽性患者一人が一行というわかりやすいデータになっています。 なお、このファイルの内容は1日1回更新され、新しい陽性患者情報がファイル末尾に追記されていくようです。

Snowflakeへの取り込み用に、これをS3にアップロードしておきます。 なお後で出てきますが、S3からSnowflakeに取り込む際にはAWSのアクセスキーとシークレットアクセスキーでファイルにアクセスできる必要があります。

aws s3 cp 130001_tokyo_covid19_partients.csv s3://xxxxxx-snowflake-import/covid19-20200625.csv

Snowflakeへ取り込み

S3にファイルが準備できたので、これをSnowflakeへ取り込みます。

データベースを作成します。

CREATE DATABASE SAMPLE_DB_01;

次にテーブルを作成。 各カラム名はcsvファイルの１行目に記載されているものをそのまま採用しました。

CREATE TABLE "SAMPLE_DB_01"."PUBLIC"."COVID19_TOKYO" (
  "No" int,
  "全国地方公共団体コード" int,
  "都道府県名" string,
  "市区町村名" string,
  "公表_年月日" date,
  "曜日" string,
  "発症_年月日" date,
  "患者_居住地" string,
  "患者_年代" string,
  "患者_性別" string,
  "患者_属性" string,
  "患者_状態" string,
  "患者_症状" string,
  "患者_渡航歴の有無フラグ" int,
  "備考" string,
  "退院済フラグ" string
);

退院済フラグをstringにしたのは、 フラグが立った状態が1、立っていない状態は""という表記になっていたので、 intではなくstringで定義する必要がありました。

次にテーブルへS3からファイルをロードする作業に入ります。 Snowflakeではこの先の作業もワークシートからコマンド群によって実行することも可能ですが、 まだそこまで慣れていないのと、この辺の作業はあまり繰り返して行わないため、 GUIからの操作の方がわかりやすいと思ったので、GUIのスクリーンショットを貼っていきます。

先ほど作成したデータベースを選択します。

「ステージ」の「作成」で

S3を選び

S3のパスとアクセスキー、シークレットアクセスキーを入力します。

以上でステージが出来上がりました。

次に、読み込むcsvファイルの形式を指定するフォーマットを作成します。 「ステージ」の隣の「ファイル形式」を選んで、「作成」を選択します。

幸いにも、デフォルト設定は今回のフォーマットにかなり近いので、変更点はあまり多くありません。 「スキップするヘッダー行」を「1」、 「オプションで囲まれたフィールド」を「二重引用符」にします。 二重引用符を選択することで、先ほどの退院済フラグのような""に対応できます。

これでフォーマットができたので、いよいよテーブルにファイルの読み込みを行います。 読み込みには下記のSQLを実行します。

copy into COVID19_TOKYO from @STAGE_COVID19_TOKYO file_format=IMPORT_COVID19_TOKYO;

ロードされたかどうか確かめてみます。

select * from "SAMPLE_DB_01"."PUBLIC"."COVID19_TOKYO";

きちんとロードされています！

Lookerで可視化する

Snowflakeにデータが入りましたので、これをLookerで可視化していきます。

SnowflakeへのConnectionの作成や、プロジェクトの作成についてはこの記事では割愛させて頂きます。

プロジェクトから「Create View From Table」を選択します。

ConnectionとLooker上のテーブルを選択します。

テーブルの定義からView定義が自動作成されますが、 カラム名が日本語であることからdimensionが自動的に作成されませんので、これを入力して行きます。 （これが面倒であれば、Snowflakeに取り込み時にカラム名を英数字にするのもアリですね）

完成形はこうなりました。

view: covid19_tokyo {
  sql_table_name: "PUBLIC"."COVID19_TOKYO"
    ;;

  dimension: 退院済フラグ {
    type: string
    sql: ${TABLE}."退院済フラグ" ;;
  }

  dimension: 曜日 {
    type: string
    sql: ${TABLE}."曜日" ;;
  }

  dimension: 全国地方公共団体コード {
    type: number
    sql: ${TABLE}."全国地方公共団体コード" ;;
  }

  dimension: 都道府県名 {
    type: string
    sql: ${TABLE}."都道府県名" ;;
  }

  dimension: 市区町村名 {
    type: string
    sql: ${TABLE}."市区町村名" ;;
  }

  dimension: 備考 {
    type: string
    sql: ${TABLE}."備考" ;;
  }

  dimension_group: 公表_年月日 {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}."公表_年月日" ;;
  }

  dimension: 患者_渡航歴の有無フラグ {
    type: number
    sql: ${TABLE}."患者_渡航歴の有無フラグ" ;;
  }

  dimension: 患者_属性 {
    type: string
    sql: ${TABLE}."患者_属性" ;;
  }

  dimension_group: 発症_年月日 {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}."発症_年月日" ;;
  }

  dimension: 患者_年代 {
    type: string
    sql: ${TABLE}."患者_年代" ;;
  }

  dimension: 患者_性別 {
    type: string
    sql: ${TABLE}."患者_性別" ;;
  }

  dimension: 患者_居住地 {
    type: string
    sql: ${TABLE}."患者_居住地" ;;
  }

  dimension: 患者_状態 {
    type: string
    sql: ${TABLE}."患者_状態" ;;
  }

  dimension: 患者_症状 {
    type: string
    sql: ${TABLE}."患者_症状" ;;
  }

  dimension: no_ {
    type: number
    sql: ${TABLE}."No" ;;
  }

  measure: count {
    type: count
    drill_fields: []
  }
}

続いて、Modelを作成します。

適当に名前をつけて

Connectionと参照するView（先ほど作成したもの）を指定します。

最後にダッシュボードを作成します。 タイルの追加から、目的のView「Covid19 Tokyo」を選択します。

まずは報道でも良く見る、日ごとの陽性患者数の棒グラフを表示してみます。 「公表 年月日」の中の「Date」と、MEASURESにある「Count」を選択し、右上の「Run」を実行します。

折れ線グラフが表示されるので、棒グラフを選択すれば、こんな図が表示されます。

なにはなくとも、これにてまずは可視化ができました！！

ここまでやってみてポイントだと思ったのは、 数え上げのような処理を書くことなく陽性患者数の数値が出せたことかと思います。 これはcountというmeasureがViewの中に記述されていることがミソのようです。 私は最初わざわざgroup byして数え上げ数値のカラムを作成してやってみたのですが、 この「Count」を選択してみたところ、なんかすんなりとグラフができてしまって驚きました。

グラフをいじってみる

さて、わざわざ自分でgroup byとかしなくてもいいという恩恵に浴するためにグラフをいじってみます。

まずは日ごとの数値だと少し細かすぎるという感じもするので、これを週ごとの集計にしてみます。 左のメニューで、「公表 年月日」の「Date」を選択解除して「Week」を選択します。 （「Count」はそのままで）「Run」を実行します。 これだけで週ごとの変化をみることができました。

自分でgroup byしなければいけないとすれば、 このような集計範囲の変更のためにカラムを１つ加えたりしなければならないですが、 集計は勝手にやってくれるので、数クリックで簡単に別のグラフが得られました。

続いて、もうちょっと実のあるグラフにすべく、 陽性患者の年代による内訳も表示させてみます。

「患者 年代」を追加で選択し、「PIVOT」の部分も選択します。 「Run」すると、年代ごとに集計したグラフに変わりました！

ただこれだとちょっと見にくいですね。 こういう情報は積み上げ棒グラフにするのが常套手段です。 グラフの設定から「Stacked」を選択すると簡単に積み上げ棒グラフになります。

年代ごとの構成がわかりやすいグラフになりました！

ひとまずこんなもんですかね。 タイトルを設定して「Save」してダッシュボードができました！！

まとめ

Snowflake+Lookerでcsvファイルを可視化する手順を駆け足で確認しました。 かなり最低限のことしか書いていませんが、 逆にこの少ない、一連の流れを追うことで色々なデータの可視化ができそうです！

Covid19のグラフは毎日見かけるものだけに、 それを自分の手で可視化してみたり、いくつかの切り口から見てみることで、 客観的なデータで物事を捉えるきっかけになるのかな、と感じました。

以上、1日でも早い収束＆終息を願って。