Federated Queries, 外部テーブル, データのエクスポート/インポート：それぞれの利点と使い方

2024-05-23

PostgreSQL で 2 つの異なるデータベースからの結果を結合する方法

Federated Queries を使用する

PostgreSQL 9.4 以降では、federated queries 機能を使用して、他のデータベースサーバーにあるデータに直接アクセスできます。これにより、あたかも別のデータベースが PostgreSQL サーバーの一部であるかのように、他のデータベースのテーブルに対してクエリを実行することができます。

例:

SELECT *
FROM federated_table("remote_db", "public", "customers");

このクエリは、remote_db データベースの public スキーマにある customers テーブルからすべてのデータを選択します。

外部テーブルを使用する

PostgreSQL 9.5 以降では、external tables 機能を使用して、他のデータベース形式のデータを PostgreSQL テーブルとして定義できます。これにより、PostgreSQL クエリを使用して、外部データに対してクエリを実行することができます。

CREATE EXTERNAL TABLE customers
  LOCATION 'csv://remote_server/path/to/customers.csv'
  FORMAT 'csv';

SELECT * FROM customers;

このクエリは、remote_server 上の customers.csv ファイルにあるデータを customers テーブルとして定義し、そのテーブルからすべてのデータを選択します。

データをエクスポートしてインポートする

最も単純な方法は、1 つのデータベースからデータをエクスポートし、それを別のデータベースにインポートすることです。これは、pg_dump と pg_restore ユーティリティを使用して実行できます。

pg_dump -d source_db > data.dump
psql -d target_db < data.dump

このコマンドは、source_db データベースを data.dump ファイルにダンプし、そのファイルを target_db データベースに復元します。

どの方法を使用するかは、状況によって異なります。 Federated queries は、他のデータベースサーバーにあるデータに直接アクセスする必要がある場合に適しています。外部テーブルは、他のデータベース形式のデータを頻繁にクエリする必要がある場合に適しています。データのエクスポートとインポートは、単純な方法が必要な場合や、他の方法が使用できない場合に適しています。

PostgreSQL で 2 つの異なるデータベースから結果を結合するためのサンプルコード

目標:

2つのデータベース db1 と db2 からそれぞれ customers と orders テーブルにアクセスし、結合
顧客の名前、注文 ID、注文日を表示

前提条件:

PostgreSQL 9.4 以降が両方のデータベースにインストールされている
db1 と db2 には、それぞれ customers と orders という名前のテーブルがある
customers テーブルには、customer_id (主キー)、name の列がある
orders テーブルには、order_id (主キー)、customer_id (外部キー)、order_date の列がある
customer_id 列は両方のテーブルで同じデータ型である

方法:

CREATE SERVER db1_server
  FOREIGN DATA WRAPPER postgresql
  OPTIONS (host 'localhost' port '5432' user 'postgres' password 'password' dbname 'db1');

CREATE SERVER db2_server
  FOREIGN DATA WRAPPER postgresql
  OPTIONS (host 'localhost' port '5432' user 'postgres' password 'password' dbname 'db2');

CREATE EXTERNAL TABLE db1_customers
  SERVER db1_server
  LOCATION (table customers);

CREATE EXTERNAL TABLE db2_orders
  SERVER db2_server
  LOCATION (table orders);

2 つのデータベースのテーブルを結合する

SELECT c.name, o.order_id, o.order_date
FROM db1_customers AS c
JOIN db2_orders AS o ON c.customer_id = o.customer_id;

説明:

上記の例では、CREATE SERVER ステートメントを使用して、db1_server と db2_server という 2 つの federated server を作成しています。
CREATE EXTERNAL TABLE ステートメントを使用して、db1_customers と db2_orders という 2 つの federated table を作成しています。これらのテーブルは、それぞれ db1 と db2 データベースの customers と orders テーブルを参照します。
最後に、SELECT ステートメントを使用して、2 つの federated table を結合し、顧客の名前、注文 ID、注文日を表示しています。

注:

この例では、両方のデータベースが同じマシン上にあり、同じポートを使用していることを想定しています。
データベースが異なるマシンにある場合、または異なるポートを使用している場合は、CREATE SERVER ステートメントの OPTIONS ク clause で接続情報 accordingly を更新する必要があります。

UNION オペレータを使用して、2 つの SELECT クエリの結果を結合することができます。この方法は、両方のクエリが同じ列構造を持っている場合にのみ使用できます。

SELECT * FROM db1_customers
UNION
SELECT c.customer_id, o.order_id, o.order_date
FROM db2_customers AS c
JOIN db2_orders AS o ON c.customer_id = o.customer_id;

上記の例では、最初の SELECT クエリは db1_customers テーブルからすべてのデータを選択します。
2 番目の SELECT クエリは、db2_customers と db2_orders テーブルを結合し、顧客 ID、注文 ID、注文日を選択します。
UNION オペレータは、この 2 つのクエリの結果を結合します。

WITH 句を使用して、中間結果セットを定義し、その結果セットを使用して別のクエリを実行することができます。この方法は、より複雑な結合を実行する場合に役立ちます。

WITH customer_data AS (
  SELECT * FROM db1_customers
  UNION
  SELECT c.customer_id, o.order_id, o.order_date
  FROM db2_customers AS c
  JOIN db2_orders AS o ON c.customer_id = o.customer_id
)
SELECT * FROM customer_data;

上記の例では、WITH customer_data AS (...) 句を使用して、customer_data という名前の中間結果セットを定義しています。この結果セットは、UNION オペレータを使用して、db1_customers テーブルと db2_customers および db2_orders テーブルの結合結果を組み合わせたものです。
SELECT * FROM customer_data; ステートメントは、customer_data 結果セットからすべてのデータを選択します。

PL/pgSQL は、PostgreSQL に組み込まれた拡張言語です。 PL/pgSQL を使用して、2 つのデータベースからデータを動的にフェッチし、結合することができます。この方法は、最も柔軟性がありますが、同時に最も複雑でもあります。

CREATE OR REPLACE FUNCTION get_customer_data()
RETURNS TABLE AS $$
DECLARE
  customer_record RECORD;
  order_record RECORD;
BEGIN
  -- db1_customers テーブルからすべてのデータを取得する
  FOR customer_record IN
    SELECT * FROM db1_customers
  LOOP
    -- db2_orders テーブルから対応する注文データを取得する
    SELECT * INTO order_record
    FROM db2_orders
    WHERE order_record.customer_id = customer_record.customer_id;

    -- 結果を返す
    RETURN ROW (
      customer_record.customer_id,
      customer_record.name,
      order_record.order_id,
      order_record.order_date
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_customer_data();