【小ネタ】 Amazon AthenaでのEXCEPTを使用したテーブルデータ差分比較

#Amazon Athena

#SQL

kasama

2025.04.22

はじめに

データ事業本部ビッグデータチームのkasamaです。
今回はAmazon Athenaで、EXCEPT演算子を用いてテーブルデータに差分が無いか比較したいと思います。。

普段、数万件データがある場合に数百件をサンプリングしてデータを比較していましたが、SQLでEXCEPTを実行し、結果が0件と出れば、データ差分が無いことを簡単に比較できるので、テストが楽になると思いました。
EXCEPT演算子を使用することで、2つのテーブル間の差異を簡単に特定できます。EXCEPTは英語で除くという意味で、最初のクエリ結果から、2番目のクエリ結果に存在する行を除くことができます。この操作はA - Bに相当します。

-- テーブルAのみに存在する行を検出
SELECT * FROM table_a
EXCEPT
SELECT * FROM table_b;

テストデータ準備

LOCATIONのS3 Bucketは手動で用意したものを設定します。

create database cm_kasama_except; 

-- Rawテーブルの作成
CREATE EXTERNAL TABLE raw_products (
  product_id INT,
  product_name STRING,
  price DECIMAL(10,2),
  last_updated TIMESTAMP
) 
LOCATION 's3://<your_s3_bucket>/raw/products/';

-- Icebergテーブルの作成
CREATE TABLE iceberg_products (
  product_id INT,
  product_name STRING,
  price DECIMAL(10,2),
  last_updated TIMESTAMP
)
LOCATION 's3://<your_s3_bucket>/iceberg/products/'
TBLPROPERTIES (
    'table_type'='iceberg',
    'write_compression' = 'zstd',
    'format'='PARQUET'
);

テストデータもSQL文でInsertします。

-- Rawテーブルへのデータ挿入
INSERT INTO raw_products VALUES
(101, 'Laptop', 999.99, TIMESTAMP '2023-06-01 10:15:30'),
(102, 'Headphones', 89.99, TIMESTAMP '2023-06-03 09:30:00'),
(103, 'Smartphone', 499.50, TIMESTAMP '2023-06-02 14:22:45'),
(104, 'Mouse', 24.95, TIMESTAMP '2023-06-04 16:45:12'),
(105, 'Keyboard', 49.99, TIMESTAMP '2023-06-05 11:20:35'); -- Rawにのみ存在

-- Icebergテーブルへのデータ挿入
INSERT INTO iceberg_products VALUES
(101, 'Laptop', 999.99, TIMESTAMP '2023-06-01 10:15:30'), -- 完全一致
(102, 'Wireless Headphones', 89.99, TIMESTAMP '2023-06-03 09:30:00'), -- 名前のみ変更
(103, 'Smartphone', 549.50, TIMESTAMP '2023-06-02 14:22:45'), -- 価格のみ変更
(104, 'Mouse', 24.95, TIMESTAMP '2023-06-12 09:30:45'), -- タイムスタンプのみ変更
(106, 'Monitor', 249.99, TIMESTAMP '2023-06-07 08:30:15'); -- Iceberg tableのみ存在

101以外は差分がある状態のデータとなっています。

101: 完全一致
102: STRING値差分
103: DECIMAL値差分
104: TIMESTAMP値差分
105: Raw tableのみ存在
106: Iceberg tableのみ存在

検証

パターン1

まずは、raw_productsをSELECTし、iceberg_productsに存在する行をEXCEPTするパターンを実行します。

SELECT * FROM raw_products
EXCEPT
SELECT * FROM iceberg_products;

101: 完全一致するので出力されない
102,103,104は一部差分があるので出力
105はraw_productsのみに存在するので出力
106はiceberg_productsのみに存在するので出力されない

Screenshot 2025-04-22 at 6.56.36

パターン2

次にiceberg_products tableをSELECTし、raw_products tableに存在する行をEXCEPTするパターンを実行します。

SELECT * FROM iceberg_products
EXCEPT
SELECT * FROM raw_products;

101: 完全一致するので出力されない
102,103,104は一部差分があるので出力
105はraw_productsのみに存在するので出力されない
106はiceberg_productsのみに存在するので出力

Screenshot 2025-04-22 at 6.58.05

パターン3

当たり前ですが、一度tableを空にして、全く同じデータを挿入し、完全一致で何も出力されないことを確認したエビデンスも残しておきます。

INSERT INTO raw_products VALUES
(101, 'Laptop', 999.99, TIMESTAMP '2023-06-01 10:15:30'),
(102, 'Headphones', 89.99, TIMESTAMP '2023-06-03 09:30:00'),
(103, 'Smartphone', 499.50, TIMESTAMP '2023-06-02 14:22:45'),
(104, 'Mouse', 24.95, TIMESTAMP '2023-06-04 16:45:12'),
(105, 'Keyboard', 49.99, TIMESTAMP '2023-06-05 11:20:35');

INSERT INTO iceberg_products VALUES
(101, 'Laptop', 999.99, TIMESTAMP '2023-06-01 10:15:30'),
(102, 'Headphones', 89.99, TIMESTAMP '2023-06-03 09:30:00'),
(103, 'Smartphone', 499.50, TIMESTAMP '2023-06-02 14:22:45'),
(104, 'Mouse', 24.95, TIMESTAMP '2023-06-04 16:45:12'),
(105, 'Keyboard', 49.99, TIMESTAMP '2023-06-05 11:20:35');