CI 部の宮本です。今回は Snowflake に S3 からデータをロードする でインポートしたデータを使って、Redash 上で新型コロナウイルス感染動向を可視化してみます。
可視化する項目は以下、東京都のサイトを参考にします。
東京都 新型コロナウイルス感染症 対策サイト
データソースの追加
Redash のデータソースとして Snowflake を追加します。右上のメニューから Data Sources を選択します。
データソース一覧の画面に遷移します。New Data Source を選択します。
接続可能なデータソースの一覧が表示されます。検索ボックスにキーワードを入れるとマッチするデータソースが絞り込まれます。Snowflake を選択します。
接続情報の入力欄が表示されます。必要事項を入力して Create を選択します。
- Name: 任意の名前を入力します
- Account: アカウントを入力します。Web コンソールの URL
XXXXXXXX.ap-northeast-1.aws.snowflakecomputing.comのXXXXXXXX.ap-northeast-1.aws部分が相当します - User, Password: Snowflake の接続ユーザー、パスワードを入力します
- Warehouse: 仮想ウェアハウスを入力します。今回はデフォルトで作成されている
DEMO_WHを使用します - Database: データベース名を入力します
- Region:
ap-northeast-1などのリージョンを入力したくなるのですが、ここを入力すると何故か接続に失敗します。空欄のままにしておきましょう
最後に Test Connection を選択して接続できるかチェックします。Success が表示されれば OK です。
クエリ、チャートの作成
新規クエリはメニューバーの Create > Query から作成できます。
クエリの編集画面です。左側に利用可能なテーブル一覧が表示されているのが確認できます。クエリを実行する際は、画面上半分の編集エリアに SQL を入力し、Execute を選択します。結果は画面下半分に表示されます。
報告日別による陽性者数の推移
こちら を参考にチャートを作成します。SQL は以下のように作成しました。累計値は Redash で実現できないよう（私調べ）なので SQL で算出しています。
SELECT PUBLISHED_AT AS "日付", COUNT(NO) AS "報告日別による陽性者数の推移（日別）", SUM(COUNT(NO)) OVER (ORDER BY PUBLISHED_AT) AS "報告日別による陽性者数の推移（累計）" FROM TOKYO_COVID19_PATIENTS GROUP BY PUBLISHED_AT ORDER BY PUBLISHED_AT;
クエリを実行したら、+New Visualization を選択してチャートの編集画面を開きましょう。
東京都のサイトでは日別と累計を切り替えられるようになっていますが、Redash では難しそうなので日別と累計でそれぞれチャートを作成します。
チャートの設定値は以下を参照してください。ひとまず最低限のオプション設定です。他にも設定はあるのでお好みで試してみてください。
報告日別による陽性者数の推移（日別）
- Visualization Type: Chart
- Visualization Name: 報告日別による陽性者数の推移（日別）
- General
- Chart Type: Bar
- X Column: 日付
- Y Columns: 報告日別による陽性者数の推移（日別）
報告日別による陽性者数の推移（累計）
- Visualization Type: Chart
- Visualization Name: 報告日別による陽性者数の推移（累計）
- General
- Chart Type: Bar
- X Column: 日付
- Y Columns: 報告日別による陽性者数の推移（累計）
作成できたら Save と Publish ボタンを忘れずに押しておきましょう。また、クエリに名前をつけておくと後で扱いやすいです。完成形はこんな形です。
ダッシュボードの作成
作成したチャートをダッシュボード上に表示してみます。画面上部のメニューから Create > Dashboard を選択します。ダッシュボード名は 東京都コロナウイルス感染動向 としました。
右下の Add Widget を選択してチャートを追加します。
Choose Visualization で先程作成したものを選び、Add to Dashboard を選択します。
日別、累計両方をダッシュボードに追加しました。それらしくなってきましたね！Done Editing、Publish を選択すると、他の Redash ユーザーからも参照できる様になります。
ダッシュボードで可視化するまでの流れは以上です。他にもいくつかチャートを追加していきます。
クエリ、チャートの作成（追加分）
以下の設定で同様にダッシュボードに追加していきます。
新規陽性者数
こちら を参考にチャートを作成します。
SQL
SELECT PUBLISHED_AT AS "日付", COUNT(NO) AS "新規陽性者数", AVG("新規陽性者数") OVER (ORDER BY PUBLISHED_AT ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "新規陽性者数7日間移動平均" FROM TOKYO_COVID19_PATIENTS GROUP BY PUBLISHED_AT ORDER BY PUBLISHED_AT;
チャート設定
- Visualization Type: Chart
- Visualization Name: 新規陽性者数
- General
- Chart Type: Bar
- X Column: 日付
- Y Columns: 新規陽性者数、新規陽性者数 7 日間移動平均
- Series
- 新規陽性者数 7 日間移動平均の Type : Line
陽性者の属性
こちら を参考に作成します。
SQL
SELECT PUBLISHED_AT AS "公表日", IFF(RESIDENCE = '""', '調査中', RESIDENCE) AS "居住地", AGE AS "年代", SEX AS "性別", IFF(HAS_DISCHARGED, '○', '') AS "退院※" FROM TOKYO_COVID19_PATIENTS ORDER BY PUBLISHED_AT DESC;
チャート設定
テーブルをそのまま表示する為、設定不要
受診相談窓口における相談件数
こちら を参考にチャートを作成します。
SQL
SELECT ACCEPTED_AT AS "日付", NUMBER_OF_CONSULTATION AS "相談件数", AVG(NUMBER_OF_CONSULTATION) OVER (ORDER BY ACCEPTED_AT ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "相談件数7日間移動平均" FROM TOKYO_COVID19_COMBINED_TEL_ADVICE_CENTER ORDER BY ACCEPTED_AT;
チャート設定
- Visualization Type: Chart
- Visualization Name: 受診相談窓口における相談件数
- General
- Chart Type: Bar
- X Column: 日付
- Y Columns: 相談件数、相談件数 7 日間移動平均
- Series
- 相談件数 7 日間移動平均の Type : Line
新型コロナコールセンター相談件数（日別）
こちら を参考にチャートを作成します。
SQL
SELECT ACCEPTED_AT AS "日付", NUMBER_OF_CONSULTATION AS "相談件数" FROM TOKYO_COVID19_CALL_CENTER ORDER BY ACCEPTED_AT;
チャート設定
- Visualization Type: Chart
- Visualization Name: 新型コロナコールセンター相談件数
- General
- Chart Type: Bar
- X Column: 日付
- Y Columns: 相談件数
完成形
完成形がこちらです。見た目はもう少し整える余地がありそうですが、いい感じに可視化することができました。
まとめ
ダッシュボードの作成が簡単にできて便利ですね〜。Redash に興味が出てきた方はこちら のハンズオンが超絶わかりやすいのでオススメです。
