【小ネタ】 Amazon AthenaのMERGE INTOでIcebergテーブルへのソースデータ重複を検証

CREATE DATABASE cm_kasama_merge_test; 

CREATE TABLE merge_test_table (
    id INT,
    name STRING,
    value INT
)
LOCATION 's3://<your_s3_bucket>/merge_test_table/'
TBLPROPERTIES (
    'table_type'='iceberg',
    'write_compression' = 'zstd',
    'format'='PARQUET'
);

-- 初期データを挿入
INSERT INTO merge_test_table VALUES
(1, 'A', 100),
(2, 'B', 200),
(3, 'C', 300);

-- 初期状態確認
SELECT * FROM merge_test_table ORDER BY id;

MERGE INTO merge_test_table t
USING (
    SELECT 2 as id, 'B-updated1' as name, 250 as value
    UNION ALL SELECT 2 as id, 'B-updated2' as name, 280 as value
) s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET name = s.name, value = s.value;

MERGE INTO merge_test_table t
USING (
    SELECT 1 as id, 'A' as name, 100 as value
    UNION ALL SELECT 1 as id, 'A' as name, 100 as value
) s
ON t.id = s.id
WHEN MATCHED THEN
    DELETE;

MERGE INTO merge_test_table t
USING (
    SELECT 4 as id, 'I-new1' as name, 400 as value
    UNION ALL SELECT 4 as id, 'I-new2' as name, 450 as value
) s
ON t.id = s.id
WHEN NOT MATCHED THEN
    INSERT (id, name, value) VALUES (s.id, s.name, s.value);