Amazon AthenaによるS3上のファイルの解析 (CSV編)

# はじめに

Amazon Athena とは、AWSのS3上のデータをSQLでクエリできる機能です。

ELB(Elastic Load Balancing)のアクセスログの検索で使われることが多いですが、それ以外にも、データファイルやログの形式に沿ってテーブルを定義することで、検索することも可能です。

ELBのアクセスログの検索については、[公式ドキュメント(Application Load Balancer ログのクエリ)](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html)で記載されているので、そちらにお任せして、ここでは、CSVファイルの内容をAthenaで解析する方法をまとめます。


# データベース、テーブル作成方法

Athenaで解析するためのデータベースおよびテーブル作成について、公式ガイドが提供されています。

- [Amazon Athena ユーザーガイド](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/what-is.html)
- [データベースとテーブルの作成](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/work-with-data.html)
  - [Athena でのデータベース作成](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-databases.html)
  - [Athena でのテーブル作成](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-tables.html)


# データセットをダウンロード

今回は、KaggleのDatasetsから、CSVのサンプルとして使えそうなものとして、[COVID-19 dataset in Japan](https://www.kaggle.com/lisphilar/covid19-dataset-in-japan?select=covid_jpn_prefecture.csv)で提供されているデータを使用します。
そちらのサイトから、`covid_jpn_prefecture.csv`をダウンロードします。

更に、複数ファイルを一度にクエリで検索できることを確認するため、`covid_jpn_prefecture.csv`を年月ごとにファイルを分割し、
`covid_jpn_prefecture.YYYY-MM.csv`（`covid_jpn_prefecture.2020-03.csv`～`covid_jpn_prefecture.2021-09.csv`）を作成します。

# S3へCSVファイルをアップロード

まず、AWSのマネジメントコンソールにログインします。

![1000_aws_console_function_s3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/29a617fc-7eab-4c9d-2606-bafd43048261.png)
機能名の検索ボックスに`S3`と入力し、表示された中から`S3`を選択します。

![1010_s3_select_bucket.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/e84cc923-564d-4250-fdfe-101fdce2b225.png)
アップロードする先のバケット名を指定します。

※バケットが多い場合は、バケット名で絞り込みます。
※バケットがない場合は、`バケットを作成`ボタンでバケットを作ります。

![1020_s3_bucket_root.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/0ab6cc26-a0c1-8fde-142c-a5f7f6382a5f.png)
フォルダの作成へ移ります。

![1030_s3_create_folder.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/f2981c5d-3fad-6cd1-b5be-fb9373f47b69.png)
CSVデータを入れるフォルダを作成します。

ここでは、`covid19-prefecture-csv`というフォルダを作って、そこにCSVを入れることとしています。

![1040_s3_move_to_folder.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/6c1fb294-b50b-b93b-e0c9-e08ba9446f91.png)
作成したフォルダをクリックして移動します。

![1050_s3_upload.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/7fe55ecb-5936-fcbf-b43f-0d01ef8e9846.png)
アップロードするCSVファイルをドラッグ&ドロップしてS3へアップロードします。

![1060_s3_upload_exec.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/2564d475-0db4-9225-0dc6-89a5d44303d6.png)
アップロードするファイル、送信先を確認し、問題なければ`アップロード`ボタンをクリックし、アップロードを実行します。

![1070_s3_upload_completed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/e29de973-7241-1309-2985-7c129d6b5012.png)
しばらくしてアップロードが完了すると、成功した旨表示されるので、`閉じる`ボタンをクリックします。

# Athena起動、データベース作成

AWSのマネジメントコンソールの操作を続けます。

![2000_aws_console_function_athena.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/e0630b95-8b4d-dd1a-f9b9-4801fe9b03f6.png)
機能名の検索ボックスに`Athena`と入力し、表示された中から`Athena`を選択します。

![2010_athena_start.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/f0120229-7ae0-735d-6f67-77cedeeb6397.png)
初回はこのようにAthenaの機能の説明ページが表示されるので、`Get Started`ボタンをクリックします。
※初回でのアクセスでない場合など、この画面ではなく、次のQuery editorの画面が表示されることもあります。

![2020_athena_query_datasources.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/5e9ac7f9-59d8-a26f-843d-5efd796d5728.png)
`sampledb`というデータベースがありますが、専用にデータベースを作成します。
`Data sources`のリンクで先へ進みます。

![2030_athena_data_sources.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/4a287baa-1796-4e82-a5bc-0783cf38b52f.png)
`AwsDataCatalog`のデータソース名をクリックすると、新しいタブが開きます。

![2040_athena_database.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/b8a7aba2-d030-ec54-73fd-f9ade915fedc.png)
テーブル作成画面が開きますが、まず、データベースを作成しますので、左のペインから`データベース`をクリックします。

![2050_athena_database.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/5fac7a8d-3d57-aa19-9aef-ed051f7a4243.png)
`データベースの追加`をクリックします。

![2060_athena_create_database.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/abed0c66-efb6-c238-8ada-5fce1bf5cb04.png)
データベース名を入力して作成します。
※必要に応じて、他の項目も入力します。

![2070_athena_data_sources.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/5a2f6a60-21b3-1c34-9b35-262271e3bf4d.png)
AthenaのData sourcesを開いたタブに戻り、`Query editor`をクリックします。

![2080_athena_query_editor_select_db.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/e6d57452-3b2c-e089-0dc4-2ed7ee88be4e.png)
Databaseから、先ほど作成したデータベースを選択します。


# テーブル作成
## 作成クエリの例（ELBアクセスログ）

[Athena でのテーブル作成](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-tables.html) の公式ページによると、ELBアクセスログを検索するテーブルの定義として、下記のようなものが定義されています。

__なお、この記事では、CSVのクエリを行うため、この方法は使いませんが、__参考のため、どのようにテーブルを定義しているのか、見ていきましょう。（ほかの形式のログをクエリするときに、参考になると思います。）

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_logs (
    `Date` Date,
    Time STRING,
    Location STRING,
    Bytes INT,
    RequestIP STRING,
    Method STRING,
    Host STRING,
    Uri STRING,
    Status INT,
    Referrer STRING,
    OS String,
    Browser String,
    BrowserVersion String
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
"input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
) LOCATION 's3://athena-examples-MyRegion/cloudfront/plaintext/';
```

Athenaでの独自の仕様があるので、軽く説明します。

```sql
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
```

この部分は、SerDe（シリアライザー/デシリアライザー）として、正規表現を使用する旨、規定しています。

```sql
WITH SERDEPROPERTIES (
"input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
)
```

この部分が、正規表現で各項目の取得を定義している部分です。

- 先頭の`^(?!#)`・・・`#`で始まる行を除外
- `([^ ]+)`・・・各項目を取得する部分
- `\\s+`・・・スペース1文字以上（`\s`の`\`をエスケープ）

```sql
LOCATION 's3://athena-examples-MyRegion/cloudfront/plaintext/';
```

この部分が、ログファイルを置いているS3のパス（バケット、パス）を定義する部分です。


## SerDe（シリアライザー/デシリアライザー）とは？

Athenaでは、前述のアクセスログのような書式を正規表現で規定できるほか、CSVやTSVなど、様々なデータ形式に対応し、そのデータ形式をSerDe（シリアライザー/デシリアライザー）として定義しています。

詳しくは、[サポートされる SerDes とデータ形式](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/supported-serdes.html)のページをご参照ください。


## CSVファイル解析用テーブル作成

今回処理するCSVについては、引用符で囲まれた値がデータに含まれていないので、LazySimpleSerDeを使用します。

詳しくは、[CSV、TSV、およびカスタム区切りファイルの LazySimpleSerDe](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/lazy-simple-serde.html)のページをご参照ください。

こちらのページで、例が記載されているので、参考に、今回解析用のCSVファイルを解析するためのテーブルを作成します。

KaggleからダウンロードしたCSVの項目は

- Date
- Prefecture
- Positive
- Tested
- Discharged
- Fatal
- Hosp_require
- Hosp_severe

の8列となっています。
また、先頭1行はヘッダのため読み飛ばすこととします。

このデータを入れるテーブル名を`covid19_prefecture_csv`として、下記のようにテーブル定義のクエリを作成します。

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS covid19_prefecture_csv (
    `Date` Date,
    `Prefecture` STRING,
    `Positive` INT,
    `Tested` INT,
    `Discharged` INT,
    `Fatal` INT,
    `Hosp_require` INT,
    `Hosp_severe` INT
)
    ROW FORMAT DELIMITED
      FIELDS TERMINATED BY ','
      ESCAPED BY '\\'
      LINES TERMINATED BY '\n'
    LOCATION 's3://｛バケット名｝/covid19-prefecture-csv/'
    TBLPROPERTIES ("skip.header.line.count"="1");
```

この中で、

```sql
TBLPROPERTIES ("skip.header.line.count"="1");
```

が、先頭1行をヘッダとして読み飛ばすことを意味しています。

![](img/3010_athena_create_table.png)![3010_athena_create_table.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/fed57e9b-c6a5-ca3a-91d9-628883fe3f2c.png)
先ほどのテーブル定義のクエリを入力し、`Run query`ボタンをクリックします。

![3020_athena_create_table_completed.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/1d94e4fc-ada5-f679-b7c2-961924ec1e48.png)
テーブル作成に少し時間がかかるので、待ちます。

作成が完了すると、Resultsに`Query Successful.`と表示され、左のTablesの欄に作成したテーブル名のリンクが表示されます。

テーブル名のリンクをクリックすると、列名がリスト表示されます。

![](img/3030_athena_table_column_list.png)![3030_athena_table_column_list.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/31493a21-a9df-a0c7-2832-374e398517aa.png)


# クエリ実行

![](img/4010_athena_create_query_tab.png)![4010_athena_create_query_tab.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/11014fcc-096b-3991-ec58-97c8b10f3740.png)
（タブの部分の`+`部分）をクリックして新たなクエリのタブを作成します。

例1：まず、CSVのすべての内容を取得するSQLを作成します。

```sql
SELECT * FROM covid19_prefecture_csv;
```

このクエリを貼り付けて実行します。

![](img/5000_athena_query01.png)![5000_athena_query01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/38097280-9ded-bf80-6087-25d9b716947f.png)

例2：北海道のみに絞り、日付でソートします。

```sql
SELECT
    *
FROM covid19_prefecture_csv
WHERE prefecture = 'Hokkaido'
ORDER BY date
;
```

![5010_athena_query02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/a31c4082-cbb1-74b0-d349-b44e8050936f.png)

例3：大阪府、かつ、2021年以降の日付に絞り、日付でソートします。

```sql
SELECT
    *
FROM covid19_prefecture_csv
WHERE date >= CAST('2021-01-01' AS TIMESTAMP)
AND prefecture = 'Osaka'
ORDER BY date
;
```

※MySQLなどと違い、日付はCASTが必要です。

![](img/5020_athena_query03.png)![5020_athena_query03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/232077/40d6ed8c-dc35-7885-675f-972d78570fcc.png)


# Athenaの料金

[Amazon Athena の料金](https://aws.amazon.com/jp/athena/pricing/)をご参照ください。

こちらによると、クエリを実行して、スキャンされるデータ量によって課金されるとのことです。

東京リージョンでは、

```txt
スキャンされたデータ 1 TB あたり 5.00USD
```

とのことです。

ログの解析等程度では、せいぜいキロバイト、メガバイトくらいだと思うので、いきなり高額な請求が来ることはないと思いますが、念のため、

- 無駄にクエリを実行するのは控える
- LIMITでスキャンするデータの容量を抑えて実行する

といった注意は、しておいた方がいいと思います。


# まとめ

- AWSでELB(ロードバランサ)を使うときは、アクセスログをAthenaでテーブルを作っておくと検索しやすいです。
    - 確か、ELBの設定で、ログの出力先のS3を設定できたはず
- AthenaはELBのアクセスログ専用ではなく、たとえばApache HTTP Serverやnginxのアクセスログの解析にも使えるし、CSVみたいなファイルの解析にも使えます。
    - ほかのクラウド（さくら）とかで構築したサイトでも、アクセスログを定期的にS3に上げておくと、後から解析しやすくなると思います。
- Athenaでは、解析対象ファイルのアップロード先を、S3のフォルダで設定できるので便利です。
    - ファイルが増えても、勝手にいい感じに、追加されたファイルもクエリで引っ張ってきてくれます。
- Athenaでは、スキャンする容量によって課金されるので、無駄なクエリの実行を控えたり、LIMITでデータ量を抑えるなどの注意はしておいた方がいいです。