Open4
Amazon AuroraのLOAD DATAを検証する
knakazawa検証用のデータを作成
複合ユニークのキーがあることを条件に、何かしらの日毎で集計するテーブルを作成 (Claudeに作ってもらった)
CREATE TABLE daily_aggregations (
id INT AUTO_INCREMENT PRIMARY KEY,
entity_id INT NOT NULL,
aggregation_date DATE NOT NULL,
total_count INT NOT NULL DEFAULT 0,
total_value DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
average_value DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
max_value DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
min_value DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY unique_entity_date (entity_id, aggregation_date)
);
初期データを入れる
INSERT INTO daily_aggregations
(entity_id, aggregation_date, total_count, total_value, average_value, max_value, min_value)
VALUES
(1, '2023-07-09', 100, 5000.00, 50.00, 100.00, 10.00),
(1, '2023-07-10', 100, 5000.00, 50.00, 100.00, 10.00),
(1, '2023-07-11', 100, 5000.00, 50.00, 100.00, 10.00)
S3に適当なバケットを作成し、初期データから created_atとupdated_atを抜いてエクスポートしたCSVをアップロードしておく。
knakazawaLOAD DATA FROM S3の検証
※ こちらの通り、IAMの紐付けとクラスターパラメータの変更が必要
クラスターパラメータは aws_default_s3_role を対象のIAM RoleのARNに設定する。
既存の置き換え(REPLACE)
LOAD DATA FROM S3 FILE 's3://${bucket}/daily_aggregations.csv' REPLACE INTO TABLE daily_aggregations
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(
id,
entity_id,
aggregation_date,
total_count,
total_value,
average_value,
max_value,
min_value
);
auto incrementなものは無視するようにする
LOAD DATA FROM S3 FILE 's3://${bucket}/daily_aggregations_v2.csv' REPLACE INTO TABLE daily_aggregations
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(
@dummy_id,
entity_id,
aggregation_date,
total_count,
total_value,
average_value,
max_value,
min_value
)
→ これを行うと 主キーが increment されたので、内部的には一度DELETEがされている？
不正なデータが混入した場合
CSVにintであるべきところを文字列にして入れた場合、Rollbackはされない。
Prefix 配下をLOAD
LOAD DATA FROM S3 PREFIX 's3://${bucket}/' REPLACE INTO TABLE daily_aggregations
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(
@dummy_id,
entity_id,
aggregation_date,
total_count,
total_value,
average_value,
max_value,
min_value
)
トランザクション
普通に使用可能
BEGIN;
LOAD DATA FROM S3 's3://${bucket}/daily_aggregations_v2.csv' REPLACE INTO TABLE daily_aggregations
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(
@dummy_id,
entity_id,
aggregation_date,
total_count,
total_value,
average_value,
max_value,
min_value
)
ROLLBACK;
COMMIT;