Athena でのテーブルの作成（Hive DDL）

## 初めに

Athena のテーブル作成には、次の方法がある。

- ①AWS Glue クローラ
- ②Athena のテーブル作成フォーム
- ③Hive DDL

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/creating-tables.html#to-create-a-table-using-the-wizard

ここでは、主に③で作成する場合の項目について理解を深めるために、要点を纏めてみようと思います。

## CREATE EXTERNAL

|パラメータ|説明|
|---|---|
|EXTERNAL|常に EXTERNAL キーワードが使用される。<br>EXTERNAL キーワードを指定せずに CREATE TABLE を使用すると、Athena でエラーが発生する。|
|[IF NOT EXISTS]|既に同じ名前のテーブルが存在している場合はテーブルの作成を行わないようにする。|
|[db_name.]table_name|Athena のテーブル名では大文字と小文字は区別されない。<br>Apache Spark を使用する場合、Spark ではテーブル名を小文字にする必要がある。|
|**[PARTITIONED BY (col_name data_type)**]|`col_name`、`data_type` が指定された 1 つ以上の列を持つ、パーティション分割されたテーブルを作成する。<br>[データのパーティション分割](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html)|
|[CLUSTERED BY (col_name) INTO num_buckets BUCKETS]|パーティション分割の有無にかかわらず、指定された `col_name` 列のデータをバケットと呼ばれるデータサブセットに分割する。<br>`num_buckets` パラメータは、作成するバケットの数を指定する。<br>バケット化は、大規模なデータセットでの一部のクエリのパフォーマンスを向上させることができる。|
|[ROW FORMAT row_format]|テーブルの行形式と基になるソースデータを指定する。<br>`DELIMITED` 句で 1 つ以上の区切り記号を指定できる。<br>`SERDE` 句を使用できる。<br>ROW FORMAT を省略するか、ROW FORMAT DELIMITED を指定すると、ネイティブ `SerDe` が使用されます。|
|[STORED AS file_format]|テーブルデータのファイル形式を指定する。<br>省略すると、デフォルトの `TEXTFILE` が使用される。|
|[LOCATION 's3://bucket_name/[folder]/']|S3 の場所を指定する。<br>globなどは使用しない。<br>フォルダやバケットの後にはスラッシュを使用する。<br>パーティションを使用する場合は、パーティション分割されたデータのルートを指定する。|
|**[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

https://qiita.com/yoshiyama_hana/items/3d532c7ecc5f08c0d040#partition-by%E3%81%A7%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B

---

# PARTITIONED BY と TBLPROPERTIES

CloudTrail のログを例に見てみます。

## 手動

CloudTrail のログは、
`s3://{bucket}/AWSLogs/{account-id}/CloudTrail/{region}/{year}/{month}/{day}`
に保存される。

次は、
`s3://{bucket}/AWSLogs/{account-id}/CloudTrail/`
までの、S3の場所を指定し、
`{region}/{year}/{month}/{day}`
は、パーティションにします。

```sql:
PARTITIONED BY (region string, year string, month string, day string)
LOCATION 's3://{bucket}/AWSLogs/{account-id}/CloudTrail/'
```

手動の場合、CloudTrail は、Hive標準では無いため、ALTER TABLE ADD PARTITION でパーティーションを読み込ませます。

```sql:
ALTER TABLE table_name ADD 
   PARTITION (region='ap-northeast-1',
              year='2023',
              month='01',
              day='01')
   LOCATION 's3://{bucket}/AWSLogs/{account-id}/CloudTrail/ap-northeast-1/2023/01/01/'
```

`WHERE`句で必要なデータのみに絞り込んで、データをロードすることができます。

```sql:
SELECT *
FROM cloudtrail_logs_table 
WHERE region='ap-northeast-1' AND year='2023' AND month='01' AND day='01'
```

## パーティション射影

- パーティション射影を使用することで、クエリの実行時間を短縮し、パーティション管理を自動化できる。
- 新しいデータが追加されると、パーティション投影は新しいパーティションを自動で追加する。このため、ALTER TABLE ADD PARTITION を使用してパーティションを手動で追加する必要がなくなる。

次の例では`PARTITIONED BY`でパーティションを指定し、設定は`TBLPROPERTIES`句で行い、パーティション射影を自動で追加します。

```sql
PARTITIONED BY (`timestamp` string)
LOCATION 's3://{bucket}/AWSLogs/{account-id}/CloudTrail/{region}'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2020/01/01,NOW', 
  'projection.timestamp.type'='date',
  'storage.location.template'='s3://{bucket}/AWSLogs/{account-id}/CloudTrail/{region}/${timestamp}')
```

パーティション射影にサポートされている型は次の通りです。

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