Posted inデータベース

FDWを使用してPostgreSQLからMySQL、MongoDB、CSVを直接クエリする

Database tutorial - IT technology blog
Database tutorial - IT technology blog

データサイロという頭痛の種

6ヶ月前、私たちのチームは壁に突き当たりました。トランザクションデータはPostgreSQLにありましたが、レガシーなユーザープロファイルはMySQLに閉じ込められていました。マーケティング部門はリードデータを500MBもの巨大なCSVファイルにダンプし続け、アプリのログはMongoDBに蓄積されていました。単一の統合レポートを作成するには、データの分析よりもメモリ上でのマージに時間を費やす400行のPythonスクリプトが必要でした。

これらを「孤島」として管理するのは非常に疲れる作業です。大規模な移行という苦労を伴わずに、PostgreSQLをセントラルハブにする方法が必要でした。そこで、本番環境に外部データラッパー（Foreign Data Wrappers: FDW）を導入しました。これがすべてを変えたのです。

PostgreSQL FDWとは一体何か？

FDWを「架け橋」と考えてください。これは SQL/MED（SQL Management of External Data）標準に準拠しており、Postgresが外部ソース（SQL、NoSQL、フラットファイルなど）をローカルテーブルとして扱えるようにします。外部テーブルをクエリすると、Postgresはリモートソースにリクエストを送信し、結果を取得して、ローカルで結合（JOIN）やフィルタリングを処理します。

本番環境で6ヶ月運用した結果、この構成により日次のレポート作成のための重いETLパイプラインは事実上不要になりました。これにより、データベースがフェデレーテッドクエリ（連邦クエリ）エンジンへと変わります。

環境のセットアップ

外部ソースに接続する前に、特定の拡張機能をインストールする必要があります。file_fdwは通常組み込まれていますが、mysql_fdwやmongo_fdwなどは、通常OSのパッケージマネージャー経由でのインストールが必要です。

-- Ubuntu/Debianの場合
sudo apt-get install postgresql-15-mysql-fdw
-- その後Postgres内部で実行
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION file_fdw;

MySQLへの接続

mysql_fdw拡張機能は非常に強力です。私たちの経験では、10,000行以上の結合も無視できるほどのオーバーヘッドで処理できます。セットアップは、サーバーの定義、ユーザーのマッピング、テーブルのリンクという3つの簡単なステップで行えます。

1. 外部サーバーの定義

これにより、MySQLインスタンスの場所をPostgreSQLに教えます。レイテンシを低く抑えるために、内部IPを使用するようにしてください。

CREATE SERVER mysql_legacy_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '192.168.1.50', port '3306');

2. ユーザーマッピングの作成

PostgreSQLがMySQLにアクセスするための認証情報が必要です。セキュリティリスクを抑えるために、MySQL側に専用の読み取り専用ユーザーを作成することをお勧めします。

CREATE USER MAPPING FOR postgres
SERVER mysql_legacy_server
OPTIONS (username 'fdw_user', password 'secure_password');

3. 外部テーブルの定義

スキーマ全体を一度に取り込むこともできますが、私は特定のテーブルを定義することを好みます。これにより、ローカルスキーマをクリーンかつ意図的な状態に保つことができます。

CREATE FOREIGN TABLE mysql_users (
    id INT,
    username TEXT,
    email TEXT
)
SERVER mysql_legacy_server
OPTIONS (dbname 'user_db', table_name 'users');

MongoDBデータの統合

スキーマレスなBSONドキュメントを構造化されたSQLテーブルにマッピングするのは少し複雑です。私たちはこれを使用して、不正検出のためにPostgresの「orders」テーブルとMongoDBの「activity_logs」をリンクさせました。

CREATE EXTENSION mongo_fdw;

CREATE SERVER mongo_server
FOREIGN DATA WRAPPER mongo_fdw
OPTIONS (address '127.0.0.1', port '27017');

CREATE USER MAPPING FOR postgres
SERVER mongo_server
OPTIONS (username 'admin', password 'password');

CREATE FOREIGN TABLE mongo_logs (
    _id NAME,
    user_id INT,
    action TEXT,
    timestamp TIMESTAMP
)
SERVER mongo_server
OPTIONS (db 'logs_db', collection 'user_actions');

パフォーマンスのヒント：複雑なMongoDBの集計（Aggregation）は、必ずしも完全にプッシュダウンされるわけではありません。クエリに数秒以上かかる場合は、Mongo側で重い処理が行われているか、あるいはPostgresがコレクション全体を取得してローカルでフィルタリングしていないかを確認してください。

CSVファイルを直接読み込む

データアナリストはfile_fdwを好みます。新しいスプレッドシートが届くたびに手動でCOPY FROMを実行する代わりに、外部テーブルでファイルパスを指定するだけです。CSVが更新されれば、SQLの結果も即座に更新されます。

CREATE SERVER csv_files_server FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE marketing_leads (
    lead_name TEXT,
    lead_email TEXT,
    source TEXT
)
SERVER csv_files_server
OPTIONS (filename '/var/lib/postgresql/data/leads.csv', format 'csv', header 'true');

クロスデータベースJOINの威力

真のメリットは「メガジョイン」にあります。MySQLのユーザー、CSVのリードリストをリンクさせ、MongoDBのログでフィルタリングすることを,単一のクエリで行えるようになりました。次のようになります。

SELECT 
    u.username, 
    m.lead_name, 
    l.action
FROM mysql_users u
JOIN marketing_leads m ON u.email = m.lead_email
JOIN mongo_logs l ON u.id = l.user_id
WHERE l.timestamp > NOW() - INTERVAL '1 day';

このクエリは、3つの異なる環境から同時にデータを取得します。PythonもSparkも不要です。わずか10行のSQLで完結します。

実環境でのパフォーマンスと教訓

FDWを本番環境で6ヶ月間運用して得られた、厳しい現実をお伝えします。ネットワークレイテンシは「静かなる殺し屋」です。MySQLインスタンスが別のリージョンにある場合、リクエストごとに100msのレイテンシを覚悟してください。可能な限り、データベースは同じVPC内に配置しましょう。

常にEXPLAINプランを確認してください。PostgresはWHERE id = 10のようなフィルターをリモートサーバーに「プッシュダウン」しようとします。これは高速です。しかし、関数を変換できない場合、ローカルでフィルタリングするためだけに100万行のデータをネットワーク経由で取得してしまう可能性があります。

セキュリティには細心の注意が必要です。CREATE USER MAPPINGに認証情報を保存すると、システムカタログ内で可視化されます。本番環境では、シークレット管理ツールや環境変数を使用してください。最後に、リモートサーバーがダウンするとPostgresのクエリがハングすることを忘れないでください。一つのシステムの遅延がダッシュボード全体をクラッシュさせないよう、CREATE SERVERのオプションで厳格なタイムアウトを設定してください。

FDWはペタバイト規模のデータウェアハウスの代わりになるものではありません。しかし、運用レポートの作成や、煩雑なETLスクリプトの回避には不可欠なツールです。インフラ全体でSQLのフルパワーを、ほぼゼロのセットアップコストで活用できるようになります。

Share:
Tags: