Aurora MySQL と Redshift の zero-ETL 統合のフィルター機能を試してみた #24 JAWS-UG 主催 週刊 AWS キャッチアップ （2024/03/18 週）2024/03/29 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 名古屋で Web インフラのお守り係をしています ● 普段は JAWS-UG 名古屋・浜松で DB ネタを中心に 話しています（主に RDS / Aurora・たまに DynamoDB） ● 全国各地の JAWS イベントを巡っています ○ 岩手→お遍路（愛媛）→FESTA（福岡）→カーニバル（北海道）→DAYS（東京） ○ 佐賀（3/23-24）→今後は金沢（福井開催）→山形→FESTA（広島）の予定 2

本日のネタは ● https://aws.amazon.com/jp/blogs/news/aws-weekly-20240318/ 3 どなたか勇気のある方に 試してほしい…じゃなくて

本日のネタは ● https://aws.amazon.com/jp/blogs/news/aws-weekly-20240318/ 4 こちらの前半部分が 本日のネタ

本日のネタは ● https://aws.amazon.com/jp/blogs/news/aws-weekly-20240318/ 5 これ

ところで、zero-ETL 統合って？ ● DB と DWH などを細かい設定なしに連携する機能 ● 簡単にいってしまえば ETL ではなくて ELT ○ ETL：Extract（抽出）→Transform（変換）→Load（ロード） ■ 必要なデータを抽出し、形式などを変換してから DWH などにロード ○ ELT：Extract（抽出）→Load（ロード）→Transform（変換） ■ 必要なデータを抽出し、DWH などにロードしてから形式などを変換 6

こんなイメージ（DB と DWH が多対多の連携も可能） 7 https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/zero-etl.html

従来の Aurora zero-ETL 統合の問題点 ● DWH が連携対応していない型の列があると連携不可 ○ 移行不可能な型を持つデータ列がテーブルに存在する DB からは 連携ができない 8

念願？のフィルター機能が追加された→試してみた ● Aurora MySQL 3.06 から Redshift Serverless へ ○ zero-ETL 統合を設定してデータをニアリアルタイム連携 ■ フィルタリングで特定のテーブルを除外 ■ 列フィルタリングの代わりにトリガーを使って別テーブル経由で連携 ● なおブログ記事はまだ書いていません（間に合わず） ○ クラメソさんには CFn を含めて記事が出ていました https://dev.classmethod.jp/articles/aurora-zero-etl-integration-redshift-data-filtering-cloudformation/ 9

大まかな流れ ● enhanced binlog を有効にした Aurora Cluster を作成 ● Redshift Serverless ワークスペース・名前空間を作成 ● zero-ETL 統合を作成 ○ include / exclude フィルターを有効化 ● DB データを登録して連携を確認 ○ exclude フィルター対象テーブルが除外されているのを確認 ○ トリガーを使ったテーブルが連携できているのを確認 10

Aurora クラスターパラメータグループ作成 11 enhanced binlog を有効化 （ストレージ層でデータ転送するため）

Aurora クラスター作成 12 3.05 以降 先ほど作成したパラメータグループを指定

Redshift Serverless ワークグループ作成 ● CloudShell から大文字・小文字識別有効化 13 aws redshift-serverless update-workgroup \ --workgroup-name zeroetl-workgroup \ --config-parameters parameterKey=enable_case_sensitive_identifier,parameterValue=true

Redshift Serverless 名前空間作成 14

Aurora・Redshift 作成完了後 zero-ETL 統合作成 15

zero-ETL 統合作成 16

フィルタリング設定 17 testdb.excludetable 以外をすべて含め る設定に （正規表現も使用可能）

ターゲット設定 18 先に作成しておいた名前空間を指定 不足ポリシーを自動追加

待ち時間にサンプルデータ投入 mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.00 sec) mysql> USE testdb; Database changed mysql> CREATE TABLE includetable (id INT PRIMARY KEY AUTO_INCREMENT, val INT NOT NULL, str VARCHAR(100)); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO includetable SET val = 100, str = 'abc'; Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO includetable SET val = 110, str = 'xyz'; Query OK, 1 row affected (0.00 sec) 19

連携から除外するテーブルも作成してデータ投入 mysql> CREATE TABLE excludetable (id INT PRIMARY KEY AUTO_INCREMENT, val INT NOT NULL, str VARCHAR(100), bin BLOB); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO excludetable SET val = 1000, str = 'abcde', bin = RANDOM_BYTES(100); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO excludetable SET val = 1010, str = 'vwxyz', bin = RANDOM_BYTES(100); Query OK, 1 row affected (0.01 sec) 20

zero-ETL 統合作成完了後に統合用の DB を作成 21

zero-ETL 統合のメトリクス確認 22 　「1」になった （「2」ではない）

Redshift クエリエディタで確認 23 1 テーブルのみロード（excludetable は存在しない）

除外テーブルの BLOB 列を除いた連携テーブル作成 ● 連携用のトリガーを作成 ※必要に応じて UPDATE / DELETE のトリガーも作成 mysql> CREATE TABLE includetable_from_exclude (id INT PRIMARY KEY AUTO_INCREMENT, val INT NOT NULL, str VARCHAR(100)); Query OK, 0 rows affected (0.03 sec) 24 mysql> CREATE TRIGGER ins_rec BEFORE INSERT ON excludetable -> FOR EACH ROW INSERT INTO includetable_from_exclude VALUES(NEW.id, NEW.val, NEW.str); Query OK, 0 rows affected (0.01 sec)

除外テーブルにデータを再投入 mysql> TRUNCATE TABLE excludetable; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO excludetable SET val = 1000, str = 'abcde', bin = RANDOM_BYTES(100); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO excludetable SET val = 1010, str = 'vwxyz', bin = RANDOM_BYTES(100); Query OK, 1 row affected (0.00 sec) 25

zero-ETL 統合のメトリクス再確認 26 　「2」になった

Redshift クエリエディタで再確認 27 　BLOB 列を除外して連携できた 2 テーブルロード済み（includetable_from_exclude が追加された）

ポイント ● DB（スキーマ）・テーブルレベルでフィルタリング可能に ● 列（カラム）レベルのフィルタリングには非対応 ○ トリガーと連携用テーブルで代用するなどの工夫が必要 28