Redshift内のデータの活⽤を Athenaにオフロードしてみた 2021/11/12(⾦) AKIBA.AWS Online #7 データアナリティクス事業部 鈴⽊ 那由太 1

2 ⾃⼰紹介 鈴⽊ 那由太 - nayuts クラスメソッド 株式会社 データアナリティクス事業本部 ⼊社: 2021/05 好きなサービス:Athena、Kinesis、Glue

3 本⽇お話しすること

4 本⽇お話しすること LTの技術的な要点 • RedshiftからS3へのアンロード時にCLEANPATHオプションが便利 • アンロード後にAthenaからデータを検索する際、パーティション射影が便利 想定視聴者 • Redshiftのデータに対して分析処理を⾏いたい。 • Athenaへのオフロードを考えている。

5 LTのテーマ Redshiftのデータを Athenaで処理したい

6 はじめの構成 BIツールなど Amazon Redshift RedshiftにBIツールなどがアクセスするような構成から…

7 ⽬標の構成 BIツールなど Amazon Athena AWS Glue Data Catalog Amazon S3 Amazon Redshift アンロード 検索 S3にアンロードしたデータにAthenaを通してアクセスするような構成にしたい。

8 ⽬標の構成 BIツールなど Amazon Athena AWS Glue Data Catalog Amazon S3 Amazon Redshift アンロード 検索 <ϝϦοτ> "UIFOBͷϑΣσϨʔςουɾ ΫΤϦͰ༷ʑͳαʔϏεʹԣஅ తʹݕࡧͰ͖ΔͳͲ S3にアンロードしたデータにAthenaを通してアクセスするような構成にしたい。

9 ⽬標の構成 ① ② BIツールなど Amazon Athena AWS Glue Data Catalog Amazon S3 Amazon Redshift アンロード 検索 <ϝϦοτ> "UIFOBͷϑΣσϨʔςουɾ ΫΤϦͰ༷ʑͳαʔϏεʹԣஅ తʹݕࡧͰ͖ΔͳͲ S3にアンロードしたデータにAthenaを通してアクセスするような構成にしたい。

10 Redshift内のデータ ※各カラムの説明 year：年（2021または2022） month：年⽉（ 112021から012022 ） type：AまたはBまたはC ほかは省略… 以下のようなデータをRedshiftにロードしておく。

11 “⽬標の構成"のしくみ

12 ①アンロード Amazon Redshift cm-nayuts-unload/month_example ├── month=012022 │ └── 0000_part_00.parquet ├── month=112021 │ └── 0000_part_00.parquet └── month=122021 └── 0000_part_00.parquet Amazon S3 UNLOADコマンドにより、SQLでRedshiftのデータをS3にエクスポートできる。

13 ②検索 ※パーティション分割するケース例> • 積み上げられるデータ（トランザクションデータなど） • パーティションを作成する AthenaからS3のデータに対してSQLでクエリを実⾏できる。スキャン量で課⾦。 データを 1. パーティション分割し、2. 列指向データフォーマットにして、コストを改善できる。 cm-nayuts-unload ├── month=012022 │ └── 0000_part_00.parquet ├── month=112021 │ └── 0000_part_00.parquet └── month=122021 └── 0000_part_00.parquet SELECT col1 , col2 FROM table1 WHERE month = xxxx AWS Glue Data Catalog など Amazon S3 Amazon Athena <1について>

14 基本的な考え⽅のポイント Amazon Athena AWS Glue Data Catalog Amazon S3 Amazon Redshift UNLOADで実現する。 オプションにより、 パーティション分割済 み・列指向な形式でエ クスポートできる。 パーティションごとに検 索してコスト削減できる。

15 基本的な考え⽅のポイント Amazon Athena AWS Glue Data Catalog Amazon S3 Amazon Redshift UNLOADで実現する。 オプションにより、 パーティション分割済 み・列志向な形式でエ クスポートできる。 パーティションごとに検 索してコスト削減できる。 再アンロードしたく なったときに、そのま ま再実⾏してしまって いいの？ どうやって追加する の？ 無料で⾃動でやってく れるの？

16 アンロードのポイント

17 CLEANPATHオプション Amazon Redshift cm-nayuts-unload ├── month=012022 │ ├──0000_part_00.parquet (旧) │ └──0000_part_01.parquet (旧) └── month=022022 └── 0000_part_00.parquet Amazon S3 cm-nayuts-unload ├── month=012022 │ └── 0000_part_00.parquet (新) └── month=022022 └── 0000_part_00.parquet Amazon S3 2022/1のパーティションに アンロード 〜イメージ〜 TO句で指定したS3パスにある既存のファイルを削除してから、指定した場所にファイルをアンロードできる。 PARTITION BY句と組み合わせると、指定したパーティションのみファイルが削除される。 WHERE句を変えるだけで、容易に差分変更ができる。 ⽐較）ALLOWOVERWRITE

18 CLEANPATHオプションのメリット ■2回⽬のアンロード処理 ■ 2回⽬の結果 ■1回⽬のアンロード処理 ■ 1回⽬の結果 アンロード対象のパーティションに関しては、Redshift内のデータと⼀致する。

19 検索のポイント

20 検索するパーティションの決め⽅ ① Athena側で計算する • パーティション射影 ② Glueデータカタログに登録・参照する • SQLから追加する • ADD PARTITION • MSCK REPAIR TABLE • Glueクローラーを実⾏する など… Athenaから検索対象のパーティションを決めるには、以下のような⽅法がある。 AWS Glue Data Catalog ① ② 検索

21 パーティション射影 <メリット> • 検索前にGlueデータカタログに問い合わせしないので⾼速化が期待できる。 • Glueデータカタログにパーティションメタデータを追加する必要がない。 <注意点> • AWS Glue Data Catalog または Hive メタストア内のテーブルに登録された既存のパーティションメタデータ は無視される。 テーブルにパーティションキーのルールやフォーマットを設定することで、Athenaで パーティション値を計算できる。

22 パーティション射影の型 型 概要 ⽇付型 ⽇付の範囲とフォーマットを定義し、値を⽇付として解釈する。 整数型 整数の範囲を定義し、値を整数として解釈する。 列挙型 列挙のメンバーを定義し、値を解釈する。 挿⼊型 クエリの WHERE 句で単⼀の値を指定する。 パーティション射影の型には各々の特徴がある。

23 パーティション射影~⽇付型~ <テーブル定義> <検索結果> ⽇付の範囲とフォーマットを定義し、値を⽇付として解釈する。

24 パーティション射影 ~列挙型~ <テーブル定義> <検索結果> ※データは事前にtypeをパーティションキーにしてUNLOADする 列挙のメンバーを定義し、値を解釈する。

25 パーティション射影 ~挿⼊型~ <テーブル定義> <検索結果> ※データは事前にtypeをパーティションキーにしてUNLOADする クエリの WHERE 句で単⼀の値を指定する。

26 列挙型と挿⼊型の違い 挿⼊型はWHERE句で指定される値が単⼀である必要がある。 列挙型は単⼀である必要はないが、メンバーが増えるとDDLを修正する必要がある。 パーティション射影の型の特徴を考慮して設計する。

27 まとめ

28 まとめ • Redshiftでのアンロード時にCLEANPATHオプションを指定すると アンロード先のパーティションのデータが、Redshift のデータと整 合する。 • Athenaでアンロードされたデータを検索する際はパーティション射 影を使うとメタデータを管理する必要がなくなる。 • パーティション射影の型には各々の特徴があり、設計時に考慮する 必要がある。

29