はじめに
Amazon AthenaはS3に保存したファイルにクエリを実行するサービスです。データベースに入れて本格的にDWHする前にお試し分析をしたり、使用頻度の少ないデータをコスパに優れたS3で分析するために利用します。
内容
CSVのファイルを用意してAthenaでクエリを作ります。
元データの準備
厚生労働省のサイトにある下記のようなPCR検査実績人数データをダウンロードして利用することにします。
日付,PCR 検査実施件数(単日)
2020/2/5,4
2020/2/6,19
2020/2/7,9
2020/2/8,4
2020/2/9,10
2020/2/10,12
以下省略
ダウンロードしたcsvファイルをそのままS3に作成したバケットのフォルダにアップロードします。今回はs3://lake-talomina/pcr_tested_daily/というフォルダを利用することにします。
Athenaの設定
- AWSでAthenaを開くと下記のような画面が出てくるので「今すぐ始める」をクリックします。
- Athenaのクエリエディタの画面が開くので、まずは「設定」をクリックします。
- クエリ結果の場所を指定します。ここは適当なS3のフォルダを指定します。
テーブルの作成
-
クエリエディタの左側ペインから「テーブルの作成」をクリックして、データソース「S3バケットデータから」を選択します。
-
ステップ1で新しいデータベースを選択し、[データベース名]と[テーブル名]を入力します。[入力データセットの場所]にはS3バケットの対象フォルダのURIを入れます。バケットを暗号化している場合は[暗号化]のチェックを入れます。
-
ステップ2は今回のデータ形式である「CSV」を選択します。
-
ステップ3はカラムの定義です。CSVのカラム定義を入れます。列名と列のタイプを設定します。
-
ステップ4でパーティションの設定ができますが、今回は行わず、「テーブルの作成」をクリックします。ただ、実際にデータを扱う際にはデータの読込量で課金されるため、しっかりパーティション設定をして必要な行だけ読みに行くようにしたほうがパフォーマンスもコストも良いと思われます。
-
クエリエディタで下記のようなSQLが表示されて「クエリは成功しました。」と表示されれば成功です。（最後の一行だけヘッダ行を読み飛ばすように書き換えてあります。）
CREATE EXTERNAL TABLE IF NOT EXISTS covid19datas.pcr_daily (
`tested_date` string,
`tested_number` bigint
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ','
) LOCATION 's3://lake-talomina/pcr_tested_daily/'
TBLPROPERTIES ('has_encrypted_data'='true'
,'skip.header.line.count'='1');
- クエリエディタでテーブル名の横の「・・・」をクリックしてテーブルのプレビューを押すとテーブルの中身を見ることができます。
ビューの作成
日付の部分がstring型では扱い辛いので、/で項目を分割するビューを作ってみます。
- まずクエリエディタで下記のようなクエリを作成して実行します。
SELECT
split_part(tested_date, '/', 1) as tested_year
,split_part(tested_date, '/', 2) as tested_month
,split_part(tested_date, '/', 3) as tested_day
,tested_number
FROM "covid19datas"."pcr_daily";
-
うまくクエリが実行できたら、下にある[作成]ボタンから[クエリからビューを作成]
-
ビュー名をインプットするとビューが作成されます。
データの利用例 PCR検査数を月ごとに集計する
- 下記のようなクエリを作成しました。
SELECT
tested_year
,tested_month
,sum(tested_number) as pcr_number_sum
FROM "covid19datas"."pcr_dairy_ymd"
group by tested_year,tested_month
order by tested_year,cast(tested_month as bigint);
- 表示されました
| tested_year | tested_month | pcr_number_sum |
|---|---|---|
| 2020 | 2 | 1560 |
| 2020 | 3 | 30314 |
| 2020 | 4 | 114333 |
| 2020 | 5 | 123017 |
| 2020 | 6 | 120995 |
| 2020 | 7 | 314162 |
| 2020 | 8 | 617602 |
| 2020 | 9 | 567450 |
| 2020 | 10 | 549725 |
| 2020 | 11 | 773617 |
| 2020 | 12 | 1273942 |
| 2021 | 1 | 1883481 |
| 2021 | 2 | 1364325 |
| 2021 | 3 | 1551196 |
| 2021 | 4 | 1933709 |
| 2021 | 5 | 2497052 |
| 2021 | 6 | 1803057 |
おわりに
とりあえずCSVに対して直接SQLを実行することはできるようになりました。
AthenaのテーブルやビューはQuicksightのソースに使ったりGlueでさらにETLすることもできるようです。日付をもっと簡単にDate型で扱えるといいのですが。
参考文献
- https://dev.classmethod.jp/articles/tsnote-support-athena-error-query-launch-001/
- https://qiita.com/yoshiyama_hana/items/9b2f26b214de92a0f36c
- https://qiita.com/sh_tomato/items/97c33cea9bed5a23dd9e
おまけ
あとあと気づいたのですがGlueのデータカタログの中にAthenaで作ったデータセットが入っていました。AthenaはS3向けのGlueということか。
