クラウドワークスのSREチームに所属しています@ciloholicです。
2023年11月にAurora MySQLとRedshiftのゼロETL統合がGAされました。この度、ゼロETL統合が本番導入可能かを検証する機会があったので、その検証結果を記載します。
2024年7月時点での検証結果ですので、時間経過によって内容が変わっている可能性があります。その点は十分ご注意ください。
背景
まず、ゼロETL統合の検証しようと考えた背景について軽く説明したいと思います。クラウドワークスでは、MySQLのテーブルをDMS経由でRedshiftにニアリアルタイムで同期し、データ分析を行なっています。3年前は約30億レコードでしたが、現在では古いレコードの削減を行なったため、約25億レコードになりました。
時折ですが、Redshiftのレコードに不整合が発生すると、テーブル全体やテーブル単体を再同期してレコードを洗い替えたいときが存在します。レコード件数やデータサイズが大きいテーブルを再同期すると、テーブル単体で最大約8時間、テーブル全体で半日以上かかっていました。データ分析基盤を平日に半日以上止めることはできないため、土日に再同期するという作業が発生していました。再同期を頻繁にするわけではないのですが、気軽に再同期ができないことは運用上の課題になっていました。
DMS以外の同期方法として、RDS MySQLのスナップショットからS3へエクスポートし、Redshiftに取り込む方法も存在しました。この方法では、1回のエクスポートごとに復元スナップショットサイズ1GB単位での課金が発生します。1日に何度も同期する場合には料金が高額になるため、この同期方法は断念しました。DMS以外の同期方法を探しているときに見かけたのが、冒頭に記載したAurora MySQLとRedshiftのゼロETL統合がGAされた記事でした。
検証項目
現在、本番運用しているDMSと比較して、ゼロETL統合の機能は十分か、パフォーマンスはどの程度かを検証しました。
- DMSと同様にAurora MySQLからRedshiftにデータ型が変換できること
- DMSと同様にAurora MySQLからRedshiftにテーブル定義変更が反映できること
- DMS以上のパフォーマンスでAurora MySQLからRedshiftに全データを同期できること
検証環境
データ型変換の検証
まずは、DMSと同様にMySQLからRedshiftにデータ型が変換できているかを確認しました。検証用テーブルとして、各種データ型を用意しました。
CREATE TABLE sync_test1 ( id BIGINT(20) NOT NULL AUTO_INCREMENT, col_int INT DEFAULT '9', col_tinyint TINYINT DEFAULT '9', col_smallint SMALLINT DEFAULT '9', col_mediumint MEDIUMINT DEFAULT '9', col_bigint BIGINT DEFAULT '9', col_decimal DECIMAL(10, 2) DEFAULT '9.99', col_float FLOAT DEFAULT '9.99', col_double DOUBLE DEFAULT '9.99', col_char CHAR(255) DEFAULT 'z', col_varchar VARCHAR(255) DEFAULT 'z', col_text TEXT DEFAULT NULL, col_tinytext TINYTEXT DEFAULT NULL, col_mediumtext MEDIUMTEXT DEFAULT NULL, col_longtext LONGTEXT DEFAULT NULL, col_date DATE DEFAULT '1999-12-31', col_datetime DATETIME DEFAULT '1999-12-31 23:59:59', col_timestamp TIMESTAMP DEFAULT '1999-12-31 23:59:59', PRIMARY KEY (`id`) ) ENGINE=InnoDB;
NULL値、デフォルト値、ゼロ値、文字列長の確認
下記の4ケースについての挙動を確認しました。
- NULL値を設定した場合、RedshiftにもNULL値が設定されるか
- デフォルト値を設定した場合、Redshiftにもデフォルト値が設定されるか
- ゼロ値を設定した場合、Redshiftにもゼロ値が設定されるか
- 文字列長を超過した場合、超過分を切り捨ててRedshiftに設定されるか
検証用のINSERT文
下記に検証用のINSERT文を記載します。
-- NULL値を設定した場合 INSERT sync_test1 (col_int, col_tinyint, col_smallint, col_mediumint, col_bigint, col_decimal, col_float, col_double, col_char, col_varchar, col_text, col_tinytext, col_mediumtext, col_longtext, col_date, col_datetime, col_timestamp) VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); -- デフォルト値を設定した場合 INSERT sync_test1 VALUES(); -- ゼロ値を設定した場合 INSERT sync_test1 (col_int, col_tinyint, col_smallint, col_mediumint, col_bigint, col_decimal, col_float, col_double, col_char, col_varchar, col_text, col_tinytext, col_mediumtext, col_longtext, col_date, col_datetime, col_timestamp) VALUES (0, 0, 0, 0, 0, 0.00, 0.0, 0.0, '', '', '', '', '', '', '1970-01-01', '1970-01-01 00:00:00', '1970-01-01 00:00:01'); -- 文字列長を超過した場合 INSERT sync_test1 VALUES(); UPDATE sync_test1 SET col_mediumtext = REPEAT('x', 65535 + 1) WHERE id = 4;
MySQLでレコードを確認
下記にMySQLでレコードを確認した結果を記載します。
-- NULL値を設定した場合 MySQL [crowdworks]> SELECT * FROM sync_test1 WHERE id = 1\G *************************** 1. row *************************** id: 1 col_int: NULL col_tinyint: NULL col_smallint: NULL col_mediumint: NULL col_bigint: NULL col_decimal: NULL col_float: NULL col_double: NULL col_char: NULL col_varchar: NULL col_text: NULL col_tinytext: NULL col_mediumtext: NULL col_longtext: NULL col_date: NULL col_datetime: NULL col_timestamp: NULL -- デフォルト値を設定した場合 MySQL [crowdworks]> SELECT * FROM sync_test1 WHERE id = 2\G *************************** 1. row *************************** id: 2 col_int: 9 col_tinyint: 9 col_smallint: 9 col_mediumint: 9 col_bigint: 9 col_decimal: 9.99 col_float: 9.99 col_double: 9.99 col_char: z col_varchar: z col_text: NULL col_tinytext: NULL col_mediumtext: NULL col_longtext: NULL col_date: 1999-12-31 col_datetime: 1999-12-31 23:59:59 col_timestamp: 1999-12-31 23:59:59 -- ゼロ値を設定した場合 MySQL [crowdworks]> SELECT * FROM sync_test1 WHERE id = 3\G *************************** 1. row *************************** id: 3 col_int: 0 col_tinyint: 0 col_smallint: 0 col_mediumint: 0 col_bigint: 0 col_decimal: 0.00 col_float: 0 col_double: 0 col_char: col_varchar: col_text: col_tinytext: col_mediumtext: col_longtext: col_date: 1970-01-01 col_datetime: 1970-01-01 00:00:00 col_timestamp: 1970-01-01 00:00:01 -- 文字列長を超過した場合 MySQL [crowdworks]> SELECT * FROM sync_test1 WHERE id = 4\G *************************** 1. row *************************** id: 4 col_int: 9 col_tinyint: 9 col_smallint: 9 col_mediumint: 9 col_bigint: 9 col_decimal: 9.99 col_float: 9.99 col_double: 9.99 col_char: z col_varchar: z col_text: NULL col_tinytext: NULL col_mediumtext: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx... col_longtext: NULL col_date: 1999-12-31 col_datetime: 1999-12-31 23:59:59 col_timestamp: 1999-12-31 23:59:59
Redshiftでレコードを確認
下記にRedshiftでレコードを確認した結果を記載します。
-- NULL値を設定した場合 crowdworks=> select * from sync_test1 where id = 1; -[ RECORD 1 ]--+------- id | 1 col_int | (null) col_tinyint | (null) col_smallint | (null) col_mediumint | (null) col_bigint | (null) col_decimal | (null) col_float | (null) col_double | (null) col_char | (null) col_varchar | (null) col_text | (null) col_tinytext | (null) col_mediumtext | (null) col_longtext | (null) col_date | (null) col_datetime | (null) col_timestamp | (null) -- デフォルト値を設定した場合 crowdworks=> select * from sync_test1 where id = 2; -[ RECORD 1 ]--+-------------------- id | 2 col_int | 9 col_tinyint | 9 col_smallint | 9 col_mediumint | 9 col_bigint | 9 col_decimal | 9.99 col_float | 9.99 col_double | 9.99 col_char | z col_varchar | z col_text | (null) col_tinytext | (null) col_mediumtext | (null) col_longtext | (null) col_date | 1999-12-31 col_datetime | 1999-12-31 23:59:59 col_timestamp | 1999-12-31 23:59:59 -- ゼロ値を設定した場合 crowdworks=> select * from sync_test1 where id = 3; -[ RECORD 1 ]--+-------------------- id | 3 col_int | 0 col_tinyint | 0 col_smallint | 0 col_mediumint | 0 col_bigint | 0 col_decimal | 0.00 col_float | 0 col_double | 0 col_char | col_varchar | col_text | col_tinytext | col_mediumtext | col_longtext | col_date | 1970-01-01 col_datetime | 1970-01-01 00:00:00 col_timestamp | 1970-01-01 00:00:01 -- 文字列長を超過した場合 -- 同期エラーが発生したため、テーブル自体が同期されなかった
文字列長を超過した場合のみ、同期エラーが発生し、テーブル自体が同期されませんでした。文字列長を65,535バイト以下に更新したところ、再同期に成功しました。
一度同期エラーが発生すると、下記SQLを手動で実行して再同期を行なう必要があります。DMSのようにAWSコンソールからGUIで再同期を行なうことはできません。一応、RedshiftのクエリエディタからSQLを実行するという方法もあります。
-- 全テーブルを再同期する ALTER DATABASE crowdworks INTEGRATION REFRESH ALL tables; -- 特定のテーブルを再同期する ALTER DATABASE crowdworks INTEGRATION REFRESH TABLE sync_test1;
テーブル定義変更の検証
DMSと同様にMySQLからRedshiftにテーブル定義変更がどこまで自動で反映されるかを確認しました。検証用テーブルとして、シンプルなテーブルを用意しました。
CREATE TABLE sync_test1 ( id BIGINT(20) NOT NULL AUTO_INCREMENT, col_int INT DEFAULT '9', PRIMARY KEY (`id`) ) ENGINE=InnoDB;
テーブル・カラム操作の確認
下記の5ケースについての挙動を確認しました。
- テーブルを追加した場合、Redshiftにもテーブルが追加されるか
- 末尾にカラムを追加した場合、Redshiftにもカラムが追加されるか
- 途中にカラムを追加した場合、Redshiftにもカラムが追加されるか
- カラムを削除した場合、Redshiftからもカラムが削除されるか
- テーブルを削除した場合、Redshiftからもテーブルが削除されるか
検証用のSQL文
下記に検証用のSQL文を記載します。
-- 末尾にカラムを追加した場合 ALTER TABLE sync_test1 ADD col_tinyint TINYINT DEFAULT '9'; -- 途中にカラムを追加した場合 ALTER TABLE sync_test1 ADD col_smallint SMALLINT DEFAULT '9' AFTER col_int; -- カラムを削除した場合 ALTER TABLE sync_test1 DROP col_int; -- テーブルを削除した場合 DROP TABLE sync_test1;
MySQLでテーブルを確認
下記にMySQLでテーブルを確認した結果を記載します。
-- テーブルを追加した場合 MySQL [crowdworks]> DESC sync_test1; +---------+--------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | col_int | int | YES | | 9 | | +---------+--------+------+-----+---------+----------------+ -- 末尾にカラムを追加した場合 MySQL [crowdworks]> DESC sync_test1; +-------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | col_int | int | YES | | 9 | | | col_tinyint | tinyint | YES | | 9 | | +-------------+---------+------+-----+---------+----------------+ -- 途中にカラムを追加した場合 MySQL [crowdworks]> DESC sync_test1; +--------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | col_int | int | YES | | 9 | | | col_smallint | smallint | YES | | 9 | | | col_tinyint | tinyint | YES | | 9 | | +--------------+----------+------+-----+---------+----------------+ -- カラムを削除した場合 MySQL [crowdworks]> DESC sync_test1; +--------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+----------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | col_smallint | smallint | YES | | 9 | | | col_tinyint | tinyint | YES | | 9 | | +--------------+----------+------+-----+---------+----------------+ -- テーブルを削除した場合 MySQL [crowdworks]> DROP TABLE sync_test1;
Redshiftでテーブルを確認
下記にRedshiftでテーブルを確認した結果を記載します。
-- テーブルを追加した場合 zero_etl=# \d crowdworks.sync_test1; Table "crowdworks.sync_test1" Column | Type | Collation | Nullable | Default ---------------------------+---------+-----------+----------+----------- id | bigint | | not null | 0::bigint col_int | integer | | | padb_internal_txn_seq_col | bigint | | | padb_internal_txn_id_col | bigint | | | Indexes: "sync_test1_pkey" PRIMARY KEY, btree (id) -- 末尾にカラムを追加した場合 zero_etl=# \d crowdworks.sync_test1; Table "crowdworks.sync_test1" Column | Type | Collation | Nullable | Default ---------------------------+----------+-----------+----------+------------- id | bigint | | not null | 0::bigint col_int | integer | | | col_tinyint | smallint | | | 9::smallint padb_internal_txn_seq_col | bigint | | | padb_internal_txn_id_col | bigint | | | Indexes: "sync_test1_pkey" PRIMARY KEY, btree (id) -- 途中にカラムを追加した場合 zero_etl=# \d crowdworks.sync_test1; Table "crowdworks.sync_test1" Column | Type | Collation | Nullable | Default ---------------------------+----------+-----------+----------+------------- id | bigint | | not null | 0::bigint col_int | integer | | | col_tinyint | smallint | | | 9::smallint padb_internal_txn_seq_col | bigint | | | padb_internal_txn_id_col | bigint | | | Indexes: "sync_test1_pkey" PRIMARY KEY, btree (id) -- カラムを削除した場合 zero_etl=# \d crowdworks.sync_test1; Table "crowdworks.sync_test1" Column | Type | Collation | Nullable | Default ---------------------------+----------+-----------+----------+------------- id | bigint | | not null | 0::bigint col_int | integer | | | col_tinyint | smallint | | | 9::smallint padb_internal_txn_seq_col | bigint | | | padb_internal_txn_id_col | bigint | | | Indexes: "sync_test1_pkey" PRIMARY KEY, btree (id) -- テーブルを削除した場合 zero_etl=# SHOW TABLES FROM SCHEMA zero_etl.crowdworks LIKE 'sync_test1'; database_name | schema_name | table_name | table_type | table_acl | remarks ---------------+-------------+------------+------------+-----------+---------
基本的には想定通りの挙動でしたが、いくつか気になる点がありました。
1. 初回テーブル同期時のNULL制約とデフォルト値の挙動
初回にテーブルを同期した際は、id以外のNULL制約とデフォルト値が設定されませんでした。DMSも同じような挙動なので、特に問題はなさそうです。ただ、カラムを追加したときにデフォルト値が設定される点が異なるようです。大体はMySQL側でデフォルト値や何かしらの値が設定されるはずなので、あまり影響はなさそうです。
2. 管理用のカラムが自動追加
カラムの末尾にpadb_internal_txn_*という見慣れないカラムが追加されていました。ドキュメントに記載はありませんが、ゼロETL統合用の内部カラムだと思われます。SELECT *だと表示されないので、あまり影響はなさそうです。
3. ゼロETL統合がサポートしない操作時の挙動
ゼロETL統合はカラム位置指定をサポートしていないため、サポート対象外の操作をしたときにどのようになるのか追加で検証しました。検証したところ、ゼロETL統合の同期が失敗して再同期をし始めました。再同期するということは、テーブルをDROP/CREATEするということを意味します。Redshiftのテーブルを見たところ、デフォルト値が消えていたり、インデックスキーの名前が変わっていました。
zero_etl=# \d crowdworks.sync_test1;
Table "crowdworks.sync_test1"
Column | Type | Collation | Nullable | Default
---------------------------+----------+-----------+----------+---------
id | bigint | | not null |
col_int | integer | | |
col_smallint | smallint | | |
col_tinyint | smallint | | |
padb_internal_txn_seq_col | bigint | | |
padb_internal_txn_id_col | bigint | | |
Indexes:
"pg_tmp_table_resyn_sync_test1_pkey" PRIMARY KEY, btree (id)
データサイズが大きなテーブルで再同期が発生すると、再同期中はテーブルにアクセスできなくなるので注意が必要です。
パフォーマンスの検証
本番環境と同等のデータ量(約25億レコード)でゼロETL統合のパフォーマンスを検証しました。以前、DMSで全テーブルを再同期した際は半日以上かかっており、一番テーブルサイズが大きいテーブルで約8時間かかっていました。
ゼロETL統合で全テーブルを同期した結果、1時間半で完了しました。一番データサイズが大きいテーブルでも約15分程で完了しました。
ゼロETL統合の気になる点
ゼロETL統合を設定する上で、気になった点を記載します。
Redshiftのデスティネーションデータベースは、ゼロETL統合を再作成するたびに新規作成する必要がある
ゼロETL統合の送信先データベースをデスティネーションデータベースと呼びます。ドキュメントでは、下記のようにCREATE DATABASEでデスティネーションデータベースを作成します。
CREATE DATABASE destination_db_name FROM INTEGRATION 'integration_id';
ゼロETL統合を再作成すると統合IDが変わるので、デスティネーションデータベースを新規作成する必要があります。デスティネーションデータベースを再作成せずに、ALTER DATABASEで統合IDを変更できれば良かったんですが、現時点では対応していないようです。
余談ですが、ゼロETL統合はBlue/Greenデプロイメントに対応していません。Blue/Greenデプロイメントをするたび、ゼロETL統合の再作成とデスティネーションデータベースの新規作成が必要になります。頻繁にBlue/Greenデプロイメントをすることはないとは思いますが、その度にデスティネーションデータベースを新規作成するのは手間がかかりますね。
Redshiftのデスティネーションデータベース作成時にオーナーが指定できない
管理者ユーザーでデスティネーションデータベースを作成したくないので、ゼロETL統合用のRedshiftユーザーを作成しました。管理者ユーザーでCREATE DATABASE OWNER [=] zero_etlを行ない、デスティネーションデータベース作成時にオーナーを指定したかったのですが、現時点では対応していないようです。オーナーが指定できないので、ゼロETL統合用のユーザーにCREATEDB権限を付与してCREATE DATABASEを実行する必要があります。
MySQLのMEDIUMTEXT/LONGTEXT型を含むテーブルは、ゼロETL統合で同期エラーになる
ドキュメントを見ると、MEDIUMTEXT/LONGTEXT型はVARCHAR(65,535)にマッピングされています。MEDIUMTEXT/LONGTEXT型は、65,535バイト以上の文字列を格納できるので、RedshiftのVARCHAR(65,535)を超過する文字列があると同期エラーになります。DMSのように文字列の超過分を切り捨ててくれる設定があれば良いんですが、現時点だと対応していないようです。
現状の回避策としては、MEDIUMTEXT/LONGTEXT型をTEXT型に切り捨てた新規テーブルを用意し、そこに定期バッチか、MySQLのトリガーで複製する方法しかなさそうです。
無効なUTF8文字コードが含まれていると同期エラーになる
文字列内に無効なUTF8文字コードが含まれており、同期エラーとなりました。ドキュメントを見ると、マルチバイト文字でサポートしていないものがあるようです。調査してみると文字列内に非文字が含まれており、その非文字が原因で同期エラーになっているようでした。
余談ですが、ゼロETL統合の同期エラーの詳細は、SYS_LOAD_ERROR_DETAILから確認できます。しかし、エラーメッセージが512文字で切り捨てられており、対象レコードとエラーになった文字が特定できずに四苦八苦しました。対象テーブルを作成日ごとに分割する作業を繰り返し行なった結果、エラーになったレコードを特定できました。
Spectrum Scan Error. Column 'test' contains invalid or unsupported UTF8 codepoints. Filename: https://s3.ap-northeast-1.amazonaws.com/prod-ap-northeast-1-tidal-buffer/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/channel-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx-xxxxxxxx/data_files/crowdworks/crowdworks.test/1/part-xxxxx-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx-xxxx.gz.parquet (s3://prod-ap-northeast-1-tidal-buffer/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/channel-xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx-xxxxxxxx/data_files/crowd
まとめ
本記事では、ゼロETL統合が本番導入可能かを検証していきました。
まだ、ゼロETL統合の機能が足りない部分があったり、注意すべき点はあるものの、現状のDMSと同じように利用できそうなことがわかりました。また、全テーブルを再同期しても1時間半程で完了するので、日中にメンテ時間を設けて再同期するような運用もできそうです。検証が終わったので、これから本番導入に向けて作業していきたいと思います。
ゼロETL統合はGAされて間もないので、今後の機能改修でより使いやすくなることを期待しています。
