パーティションテーブルに新規のパーティション列を追加する

## やりたいこと
現在year,month,dayまでのパーティション粒度で作成しているテーブルを、year,month,day,hourのパーティション粒度に変換する。

しばらく前にAthenaがGlueパーティションインデックスに[対応した](https://aws.amazon.com/jp/about-aws/whats-new/2021/11/amazon-athena-queries-aws-glue-data-catalog-partition-indexes/)ので、より細かいパーティションを作ってもGetPartitionで遅くなる問題は回避できそう、じゃあ細かいパーティションを切って取得データ量を減らせるのでは、という発想による。

## やること
CTASを使って別テーブルを作る。

## ステップバイステップ

### 1. 現行テーブルの定義を確認

サンプルのソーステーブルは、[動的パーティショニング検証](https://qiita.com/itsuki3/items/84778d7e436dac618f02)で作ったDNS Resolverクエリーのものを、dayまでのパーティション粒度に変更して作成。

Glueでテーブル定義を確認してみる。PARTITIONED BYセクションがyear,month,dayまでになっていることがわかる。
![スクリーンショット 2022-04-07 午前1.20.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609876/a2663543-83f1-8745-8167-97f54b750c24.png)


### 2. CTASの実行

以下を実行する。
なおここでは、全件ではなく試しに4月6日分だけを対象としている。また、本筋に関係ないので、列も絞っている。
```sql
CREATE TABLE plain_daily_migrated
WITH (
	format = 'JSON',
	external_location = 's3://kfh-dynamic-test/plain_migrated/',
	partitioned_by = ARRAY['year','month','day','hour']
) AS
SELECT version, region, query_timestamp, query_type, year, month, day, substr(query_timestamp,12,2) as hour
FROM plain_daily
where year = '2022'
	and month = '04' and day='06';
```

ポイントは、**timestamp相当列（このテーブルの場合はquery_timestamp）の、時間（HH）に相当する文字をsubstrで取り出し、hourパーティション列として定義している**こと。これがないと、partitioned_byで指定したパーティション列が存在しないと怒られることになる（`Partition columns [hour] not present in schema.`）。

### 3. 確認

S3を確認すると、ちゃんとdayの下にhourパーティションが作られていることがわかる（hour=00〜05がないのは、hour=05以前のデータがないため。`plain_daily`テーブルは日本時間の15時頃、UTCの06時頃に作成している）。
![スクリーンショット 2022-04-07 午後12.39.18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609876/780cc776-83c8-40d8-a141-73a0ab88955c.png)

Glueテーブル定義（抜粋）。
![スクリーンショット 2022-04-07 午後12.41.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609876/b9adeed2-a033-4e94-e9c3-0b98351e0c6f.png)

最後にAthenaで実データを確認する。

```sql
SELECT *
FROM plain_daily_migrated
where hour = '23'
order by query_timestamp asc;
```
23時のデータを検索してみると、timestamp列の時刻と、新たにパーティションとして追加されたhour列が一致していることが確認できる。

![スクリーンショット 2022-04-07 午後12.45.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609876/d0fd16ef-1e0e-f8df-88c4-035ffb0ed288.png)

## まとめ

少なくともtimestampの場合、CTASで簡単にパーティション列を追加できることが分かった。

なお今回はたまたまtimestamp相当の列があったために細かい設定をせずに済んだが（~~[マニュアル](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/ctas-insert-into-etl.html#ctas-insert-into-etl-step-2-use-ctas-to-partition-convert-and-compress-the-data)上はsubstrやcastを駆使する必要がありそうな記載だが今回は要らなかった~~※）、テーブル定義や追加したいパーティション列の内容によっては、あれこれと工夫しなければならないかも知れない。
また、AthenaのCTASには一度に100パーティションしか追加できない[制約がある](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/ctas-insert-into.html)こと（対処するには[100件未満のIASを繰り返す](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/ctas-insert-into.html)必要がある）や、Firehose等でリアルタイム/準リアルタイムに生成している場合の新旧データ統合、CTASに伴うクエリーコストなど、実際にこの方法を採る上での課題は多いように感じた。

また、ここでhourの元となっているtimestampは、あくまでデータ発生時刻である点にも注意する必要がある。
元テーブルのパーティションが処理時刻ベースな場合（例えばFirehoseは、動的パーティショニングを使わない限りそうなる）に整合性が取れなくなるし、timestampはUTCだがパーティションはJSTベースとしたいといったケースもあり得る。

...と、要件やデータ量に左右される要素はまあ大きいものの、CTAS一発で簡単にパーティション列を追加できる点は、素直に便利だなと思った次第。

※2022年4月7日追記：
ソーステーブルを、間違えてhourパーティションが既にあるもの（`plain`）を選んでいたためにその列が使われただけでした。dayパーティションのみのソーステーブル（`plain_daily`）に指定し直したところ、やはりsubstrは必要でしたので、お詫びして訂正します。