DuckDBでS3のCSVとBigQueryデータの差分を比較してみた

2025.10.04

はじめに

データ事業本部ビッグデータチームのkasamaです。

今回はDuckDBを使って、S3に格納されているCSVファイルとBigQueryのテーブルデータをExcept句で比較してみたいと思います。例えば、S3にデータレイクとしてCSVファイルを格納しており、何かしらのデータロードサービスでBigQueryにデータをロードした際のデータ比較に有効かと思います。DuckDBを使用することで、S3とBigQueryという異なるクラウドサービスのデータを、ローカルにダウンロードすることなく直接比較できます。

前提条件

必要なツール

DuckDB
AWS CLI

前提としてDuckDBやAWS CLIコマンドの設定が完了していることとします。DuckDBのVersionは1.4.0で実施しました。

Google Cloud CLIのインストールと認証設定

Google Cloud CLIを初めて使用するので手順を記載しておきます。
MacOSの公式の手順に沿って進めます。基本的には、公式情報を参照して進めていただければ問題ないです。

手順に記載されている通り、tarファイルをdownloadし、展開し、スクリプトを実行します。

			
			tar -xf google-cloud-cli-darwin-arm.tar.gz
./google-cloud-sdk/install.sh

gcloud CLI を初期化します。

			
			source ~/.zshrc
gcloud init

次にApplication Default Credentialsを設定します。アプリケーション(今回で言うとDuckDB)が Google Cloud 上のリソースにアクセスする際のクライアント認証情報を取得するための仕組みです。

			
			gcloud auth application-default login

インストールが完了したら以下のコマンドで設定情報を確認できます。

					
			# 現在のアカウント確認
gcloud auth list

# 現在のプロジェクト確認
gcloud config get-value project

# アクセス可能なプロジェクト一覧
gcloud projects list

# 認証ファイルの確認
cat ~/.config/gcloud/application_default_credentials.json

テストデータの準備

テスト用CSVファイルの作成

ローカルのVS CodeなどでCSVファイルを生成します。

test_data.csv

			id,name,age,email
1,Taro Yamada,25,taro@example.com
2,Hanako Sato,30,hanako@example.com
3,Jiro Tanaka,28,jiro@example.com

このCSVファイルを任意のS3バケットにアップロードします。

					
			aws s3 cp test_data.csv s3://<your-bucket>/bq_test/test_data.csv

BigQueryのテストデータセットとテーブル作成

BigQueryコンソールからSQLを実行します。

					
			-- データセット作成
CREATE SCHEMA IF NOT EXISTS `your-project-id.test_dataset`
OPTIONS(
  description="テスト用データセット"
);

-- テーブル作成
CREATE OR REPLACE TABLE `your-project-id.test_dataset.test_table` (
  id INT64,
  name STRING,
  age INT64,
  email STRING
);

-- テストデータ挿入
INSERT INTO `your-project-id.test_dataset.test_table` VALUES
  (1, 'Taro Yamada', 25, 'taro@example.com'),
  (2, 'Hanako Sato', 30, 'hanako@example.com'),
  (3, 'Jiro Tanaka', 28, 'jiro@example.com');

		
		

Screenshot 2025-10-04 at 8.21.04

DuckDBでの比較手順

接続設定

まず、以下のコマンドでAccessKeyId, SecretAccessKey, SessionTokenを取得します。

					
			aws configure export-credentials --profile <your-profile>

次にDuckDB Local UIを立ち上げます。

			
			duckdb --ui

立ち上がったら、セルで以下のコマンドを実行します。
trueで返ってきたらS3の接続成功です。

					
			CREATE OR REPLACE SECRET secret_c (
              TYPE s3,
              PROVIDER credential_chain,
              CHAIN 'config',
              KEY_ID '<取得したAccessKeyId>',
              SECRET '<取得したSecretAccessKey>',
              SESSION_TOKEN '<取得したSessionToken>'
);

		
		

S3データを参照できる状態です。

					
			select * FROM 's3://<your-bucket>/bq_test/test_data.csv'

Screenshot 2025-10-04 at 8.36.35

次にBigQueryの接続です。以下を参考に実施します。

セルで以下のコマンドを実行します。

					
			-- BigQuery拡張機能のインストール
INSTALL bigquery FROM community;
LOAD bigquery;

Screenshot 2025-10-04 at 8.34.01

BigQueryプロジェクトに接続します。

					
			ATTACH 'project=your-project-id' AS bq (TYPE bigquery, READ_ONLY);

Screenshot 2025-10-04 at 8.34.33

BigQueryを参照できる状態です。

					
			select * from bq.test_dataset.test_table

Screenshot 2025-10-04 at 8.35.15

EXCEPT句でデータ比較

S3にあってBigQueryにないデータを抽出します。今回は0件です。

					
			select * FROM 's3://<your-bucket>/bq_test/test_data.csv'
except
select * from bq.test_dataset.test_table

Screenshot 2025-10-04 at 8.47.15

BigQueryにあってS3にないデータを抽出します。今回は0件です。

					
			select * from bq.test_dataset.test_table
except
select * FROM 's3://<your-bucket>/bq_test/test_data.csv'

Screenshot 2025-10-04 at 8.47.22

両方のクエリ結果が0件であれば、データは完全に一致しています。

試しにBigQueryのみにデータを挿入して再度確認してみます。

			
			INSERT INTO `your-project-id.test_dataset.test_table` VALUES
  (4, 'Keisuke Honda', 10, 'keisuke@example.com');

これは想定通り、BigQueryにあってS3にないデータとして出力されました。
Screenshot 2025-10-04 at 8.54.39

ちなみにスキーマ情報は一致していました。

Screenshot 2025-10-04 at 8.57.06

最後に

DuckDBを使用することでテストが楽になる場面もあるかと思いますので、参考になれば幸いです。

追記

後から以下のブログを拝見していて、UNION ALLで一回のSQLで良さそうだったのでメモとして残しておきます。

					
			SELECT 'S3にのみ存在' as source, * 
FROM 's3://<your-bucket>/bq_test/test_data.csv'
EXCEPT
SELECT 'S3にのみ存在' as source, * 
FROM bq.test_dataset.test_table

UNION ALL

SELECT 'BigQueryにのみ存在' as source, * 
FROM bq.test_dataset.test_table
EXCEPT
SELECT 'BigQueryにのみ存在' as source, * 
FROM 's3://<your-bucket>/bq_test/test_data.csv'

		
		