Amazon Athenaの練習

12067 ワード
Athena AWS AWS テキストリンク

はじめに

Amazon AthenaはS3に保存したファイルにクエリを実行するサービスです。データベースに入れて本格的にDWHする前にお試し分析をしたり、使用頻度の少ないデータをコスパに優れたS3で分析するために利用します。

内容

CSVのファイルを用意してAthenaでクエリを作ります。

元データの準備

厚生労働省のサイトにある下記のようなPCR検査実績人数データをダウンロードして利用することにします。

pcr_tested_daily.csv
日付,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![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/940466/6b775c16-ae3e-ec36-3b1b-5405ee622be3.png)

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型で扱えるといいのですが。

参考文献

おまけ

あとあと気づいたのですがGlueのデータカタログの中にAthenaで作ったデータセットが入っていました。AthenaはS3向けのGlueということか。