複数のファイル（データ）を効率よく可視化するためには、Athena を経由して QuickSight に連携させる方法が一般的です。今回は S3 に保存した複数の CSV ファイルを QuickSight で可視化するまでの方法を検証し、その過程を備忘録としてまとめました。
Inventory icons created by Freepik - Flaticon
学習背景
最近はデータの分析して洞察を得るために QuickSight を駆使しています。下の図でいう分析に該当する作業は業務で携わるのですが、管理者に該当する作業は別の部門にお任せしており触る機会がありませんでした。そろそろデータセット作りからはじめないと求めるデータが集められないことになりそうなので学んでみました。
私の業務ではデータ分析と洞察の得るのために QuickSight を頻繁に利用しています。下の図に示すように分析作業は私の主な作業範囲であり、これまでは管理者の役割は別の部門が担当していたため、直接触る機会がありませんでした。しかし、自分でデータセットを作成しなければ、求めるデータが手に入らない状況が増えること予想されため、学習に取り組みました。
引用: 20200204 AWS Black Belt Online Seminar Amazon QuickSight アップデート | PPT
通常、テスト用のサンプルデータは QuickSight の公式ドキュメントやワークショップで提供されます。私はすでに QuickSight の操作に慣れているため、お膳立てされたデータを利用しても学習効果が少ないと感じました。 そこで、個人的な趣味で網走市が公開しているデータを利用し、データセット作成と可視化を試してみました。
サンプルデータの準備
北海道網走市がオープンデータとして公開している「地域別世帯数及び人口」の CSV ファイルを利用します。
地域別世帯数及び人口（オープンデータ)｜網走市組織一覧｜網走市
ダウンロードしてきたファイルは各月毎にデータが分けられていました。2023 年 11 月時点で 2023 年 1 月から 10 月までの合計 10 ファイルが提供されています。
$ ll
total 400
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:12 012114_population_20230131.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:12 012114_population_20230228.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:12 012114_population_20230331.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 20:58 012114_population_20230430.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:13 012114_population_20230531.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:13 012114_population_20230630.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:13 012114_population_20230731.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:13 012114_population_20230831.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:13 012114_population_20230930.csv
-rw-r--r--@ 1 ohmura.yasutaka staff 17K 11 3 21:13 012114_population_20231031.csv個々の CSV ファイルを Athena と組み合わせ QuickSight 上では 1 つのデータソースとして扱えるようにします。
ファイル加工
ShiftーJIS から UTF-8 へ変換
公開されている CSV ファイルは Shift-JIS 形式でした。後に Athena でクエリした結果、文字化けの原因になりました。そのため、Excel や VSCode などのエンコードできるエディタで開き、UTF-8 へ変換して保存し直しました。
日付を付与
オリジナルの CSV ファイル内に年月を示す日付情報がありませんでした。各月のファイルを取り込んだときにいつのデータか判別がつきません。末尾に「計」という値が空のカラムがあり不要だったので、日付のカラムとしてヘッダーの名前を変更し末尾に日付を追加しました。
Athena で検索可能かテスト
10 個の CSV ファイルを S3 バケットへアップロードし、S3 へ保存だけしました。Athena からアップロードしたファイルへアクセスできるか環境を作成します。
データベースを作成します。
CREATE DATABASE abashiri_population_db;次に テーブル 作成しました。以下のブログを参考に CSV ファイルのヘッダーをスキップする設定をしました。
CREATE EXTERNAL TABLE population2023 (
Adiministrative_District_Name string,
Number_of_Households_Japanese int,
Number_of_Households_Foreigner int,
Number_of_Households_Multiple int,
Total_Number_of_Households int,
Population_Men_Japanese int,
Population_Men_Foreigner int,
Total_Population_Men int,
Population_Women_Japanese int,
Population_Women_Foreigner int,
Total_Population_Women int,
Total_Population_Japanese int,
Total_Population_Foreigner int,
Total_Population int,
Survey_Date date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
LOCATION
's3://qs-sample-date-abashiri/population/'
TBLPROPERTIES (
'has_encrypted_data'='false',
'skip.header.line.count'='1'
);テーブル作成まで完了したら CSV ファイルの内容を検索できるかテストします。
SELECT *
FROM abashiri_population_db.population2023
limit 10;S3 バケットに保存した 10 個のファイルを対象に検索できました。
QuickSight データセット作成
新しいデータセットの作成から Athena を選択。
データソース名を入力し作成。
Athena で作成したデータソース名、テーブル名を指定し選択。
Visualize を選択すると QuickSight の分析画面へ遷移します。
データセットの作成から可視化を行う分析までの作業は以上でした。
遭遇したトラブル
データソース名を入力した IAM ロールの権限エラーが発生しデータソースの作成に失敗しました。原因は 3 年ぶりに QuickSight から Athena へ接続したため、必要なポリシーに変更あったようで権限不足となったようです。既存の IAM ロールがあると新規作成や、ポリシー追加は行われないようです。
以下のブログを参考に既存のカスタマー管理 IAM ロール、ポリシーを削除しました。
Amazon QuickSightでAthenaのDatasetを作成する際にエラーとなる場合の対処 | DevelopersIO
改めてデータソースを作成すると新規に IAM ロール、ポリシーが新規作成され、権限エラーは解消されました。
QuickSight 分析
網走市の人口データから作成したデータセットを利用してグラフを作成してみました。 元のファイルは月毎にファイルが分割されていましたが、ファイルを多少加工し Athena 経由して QuickSight 可視化することで簡単に毎月の人口推移を確認できました。以降は網走を知らないとまったく意味がわからない考察を綴っていきます。
地域別の人口分布と人口の多い地域の推移のグラフです。パッと見て南何条とかの下町に比べ、駒場や潮見などの上町の人気の高さが伺えます。そうです、駒場が発展し、南 4 条の商店街はシャッター街となりました。
呼人は呼人全体で一括りなので人口が多いのはなんとなく理解できます。以外にも向陽ケ丘も上位でした。旧中央小学校跡地にできた新興住宅地も含まれているのかと調べたのですが、そこは含まれていませんでした。南 14 条や、錦町に比べるとかなり密集している地域なので人が多いのはわかりますが、ここまでは上位だとは想像していませんでした。
藻琴に外国籍の方が多いことに気づいたので人口の推移と、地域毎の人数を円グラフにしました。1 月から 10 月の間で 150 人近く増えており、年間ですと 50％成長ペースです。藻琴に多いところをみると第一次産業の労働者不足で外国人労働者ではないかと推測しています。
気になったので個人的に調査しました。身近なところでオンライン英会話のフィリピン人講師の方に訪ねてみたところ、国内にエージェントがいて日本へ酪農などの出稼ぎ労働を斡旋しているとのことでした。北海道も就業地として候補があるとのことで、藻琴は農業、酪農、畜産が有名ですから諸外国から働きにきている方はいてもおかしくないかなと思いました。
非常に簡単な考察ではありましたが、網走市が公開しているデータを可視化してみると見えてくるものもあったのではないでしょうか。データストアとしての S3 と、データをいい感じにするための Athena、可視化を実現する QuickSight の組み合わせも簡単に実現できました。
あまり整理されていないデータだと Athena でクエリしてデータセットを作成するための SQL 力が問われそうだなというのが私の肌感です。
おわりに
私が住んでいた 20 年前は 40,000 人だった人口も今では 32,000 人まで減少し過疎化の一途をたどっています。さいたまスーパーアリーナの収容人数が 37,000 人でしたので、すべての網走市民を余裕をもって収容できる事実に少し寂しさを覚えます。
3
