【AWS】Athenaでパーティション射影を使ってみた

# はじめに

この記事では以下について書きます。

- データベース、テーブルを作成し、SELECT クエリを実行する
- パーティション射影を利用する

# データベースとテーブルを作成する

事前にクエリ結果を保存するための S3 バケットを設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/621414/89e8ce47-daae-44d8-9999-4026cd34daaa.png)

まずデータベースを作成します。

```sql
CREATE DATABASE mydatabase
```

次にテーブルを作成します。ファイル形式に合わせたテーブル定義と検索先の S3 バケット・フォルダの指定が必要です。
ここでは `s3://athena-demo-ap-northeast-1/my_folder/` に以下のファイルを保存しておきます。

```test_1.csv
Alice,30
Bob,35
```

上記に対応するテーブルを作成します。

```sql
CREATE EXTERNAL TABLE `mytable` (
  `name` STRING COMMENT 'ユーザー名',
  `age` INT COMMENT '年齢'
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
LOCATION 's3://athena-demo-ap-northeast-1/my_folder/'
```

# SELECTクエリを実行する

データを検索するため、以下のクエリを実行します。

```sql
SELECT * FROM mytable
```

実行結果：

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/621414/ca57d917-f1e7-4331-84e2-dd6fbb859b19.png)

# SerDe

Athena にデータ形式を認識させるために SerDe（Serializer/Deserializer）を使用します。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/serde-reference.html

`ROW FORMAT DELIMITED` を指定すると、Athena がデフォルトで LazySimpleSerDe を使用します。
LazySimpleSerDe は Hive のライブラリです。

> Lazy Simple SerDe のシリアル化ライブラリ名は org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe です。ソースコード情報については、GitHub.com の「[LazySimpleSerDe.java](https://github.com/apache/hive/blob/master/serde/src/java/org/apache/hadoop/hive/serde2/lazy/LazySimpleSerDe.java)」を参照してください。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/lazy-simple-serde.html

カラムの区切り文字は `FIELDS TERMINATED BY` で指定します。この記事では CSV ファイルを使用しているので、`FIELDS TERMINATED BY ','` を指定します。

区切り文字に使用している文字をエスケープするため、`ESCAPED BY` を使用します。`ESCAPED BY '\\'` と指定することで、「Hello, I'm Alice!」の中にあるコンマを区切り文字として認識させないようにします。

```test_1.csv
Alice,30,Hello\, I'm Alice!
Bob,35,Hello\, I'm Bob!
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/621414/b4d2f0f8-1e85-4b9c-9f44-15e59e12ca76.png)

`LINES TERMINATED BY` は行の終わりの文字を指定します。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-table.html

カラムのデータ型は、ここでは STRING と INT しか使用していませんが、様々なデータ型を使用することができます。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/data-types.html

# パーティション射影

Athena でテーブルをパーティション化することで、スキャンするデータ量を抑えることができます。その結果、パフォーマンスが向上し、コストが削減されます。
具体的には、パーティションテーブルに対してそのパーティションキーを WHERE 句に指定することで、その条件に一致するデータ以外はスキャンしなくなります。
そのため、検索速度が向上し、S3 への GET リクエストが減少するためコスト削減につながります。

テーブルをパーティション化するためには `PARTITIONED BY` を使用します。
その後、パーティションを追加するには `MSCK REPAIR TABLE` または `ALTER TABLE ADD PARTITION` を実行します。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html

上記のパーティション追加を自動化するにはパーティション射影を使用します。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection.html

```sql
CREATE EXTERNAL TABLE `mytable` (
  `name` STRING COMMENT 'ユーザー名',
  `age` INT COMMENT '年齢',
  `message` STRING COMMENT 'メッセージ'
)
PARTITIONED BY (
  day STRING COMMENT '年月日'
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
LOCATION 's3://athena-demo-ap-northeast-1/my_folder/${day}/'
TBLPROPERTIES (
  "projection.enabled" = "true",
  "projection.day.type" = "date",
  "projection.day.format" = "yyyy/MM/dd",
  "projection.day.range" = "NOW-1YEARS,NOW",
  "projection.day.interval" = "1",
  "projection.day.interval.unit" = "DAYS",
  "storage.location.template" = "s3://athena-demo-ap-northeast-1/my_folder/${day}/"
)
```

パーティション化されたテーブルとパーティションキーは以下のように表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/621414/7befe340-3378-4e97-84dd-98c2a8306abd.png)

以下はパーティションキーを使用したクエリの例です。

```sql
SELECT * FROM mytable WHERE day > '2025/06/06'
```

パーティションの範囲を動的に指定するためには `projection.columnName.range` に相対日付文字列を渡します。上記のクエリでは `"projection.day.range" = "NOW-1YEARS,NOW"` と指定しています。

# つまづいたところ

## クエリ構文の順番が間違っている

`CREATE EXTERNAL TABLE` はクエリ構文の順番が重要です。 

```sql
CREATE EXTERNAL TABLE [IF NOT EXISTS]
 [db_name.]table_name [(col_name data_type [COMMENT col_comment] [, ...] )]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS]
 [ROW FORMAT row_format]
 [STORED AS file_format] 
 [WITH SERDEPROPERTIES (...)]
 [LOCATION 's3://amzn-s3-demo-bucket/[folder]/']
 [TBLPROPERTIES ( ['has_encrypted_data'='true | false',] ['classification'='aws_glue_classification',] property_name=property_value [, ...] ) ]
```

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-table.html

たとえば以下のクエリは失敗します。

```sql
CREATE EXTERNAL TABLE `mytable` (
  `name` STRING COMMENT 'ユーザー名',
  `age` INT COMMENT '年齢',
  `message` STRING COMMENT 'メッセージ'
)
PARTITIONED BY (
  day STRING COMMENT '年月日'
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
TBLPROPERTIES (
  "projection.enabled" = "true",
  "projection.day.type" = "date",
  "projection.day.format" = "yyyy/MM/dd",
  "projection.day.range" = "NOW-1YEARS,NOW",
  "projection.day.interval" = "1",
  "projection.day.interval.unit" = "DAYS",
  "storage.location.template" = "s3://athena-demo-ap-northeast-1/my_folder/${day}/"
)
LOCATION 's3://athena-demo-ap-northeast-1/my_folder/${day}/'
```

エラーメッセージ：

```
line 1:8: mismatched input 'EXTERNAL'. Expecting: 'MATERIALIZED', 'MULTI', 'OR', 'PROTECTED', 'ROLE', 'SCHEMA', 'TABLE', 'VIEW'
```

上記のクエリは `TBLPROPERTIES` と `LOCATION` が逆になっているため失敗します。正しくは以下です。

```sql
CREATE EXTERNAL TABLE `mytable` (
  `name` STRING COMMENT 'ユーザー名',
  `age` INT COMMENT '年齢',
  `message` STRING COMMENT 'メッセージ'
)
PARTITIONED BY (
  day STRING COMMENT '年月日'
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  ESCAPED BY '\\'
  LINES TERMINATED BY '\n'
LOCATION 's3://athena-demo-ap-northeast-1/my_folder/${day}/'
TBLPROPERTIES (
  "projection.enabled" = "true",
  "projection.day.type" = "date",
  "projection.day.format" = "yyyy/MM/dd",
  "projection.day.range" = "NOW-1YEARS,NOW",
  "projection.day.interval" = "1",
  "projection.day.interval.unit" = "DAYS",
  "storage.location.template" = "s3://athena-demo-ap-northeast-1/my_folder/${day}/"
)
```

## パーティションのパラメータが欠落している

動作検証を行っているとき、パーティションテーブル作成後、なぜかクエリ結果が 0 件になる事象が発生しました。

```sql
SELECT * FROM mytable -- この結果が 0 件になってしまう
```

原因は `storage.location.template` を指定していなかったことでした。修正後、うまくいきました。

# 参考

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/getting-started.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-table.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/serde-reference.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/lazy-simple-serde.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection-kinesis-firehose-example-choosing-partition-keys.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/data-types-examples.html

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection-supported-types.html