すべてのプロダクト
Search

このプロダクト

    Hologres:データベース間のデータのクエリ (ベータ)

    ドキュメントセンター

    Hologres:データベース間のデータのクエリ (ベータ)

    最終更新日:Apr 01, 2025

    このトピックでは、Hologres でデータベース間のデータをクエリする方法について説明し、関連する例を示します。

    背景情報

    Hologres V1.1 以降では、外部テーブルを使用して、リージョン、インスタンス、およびデータベースを跨いで便利かつ簡単にデータをクエリできます。Hologres は PostgreSQL と互換性があります。外部テーブルを使用してデータベース間のデータをクエリする原則は、PostgreSQL の場合と同じです。詳細については、「postgres_fdw」をご参照ください。

    制限事項

    • データベース間のデータのクエリは、Hologres V1.1 以降でのみ可能です。Hologres インスタンスのバージョンが V1.1 より前の場合、Hologres コンソールで Hologres インスタンスを手動でアップグレードするか、Hologres DingTalk グループに参加してインスタンスのアップグレードを申請してください。Hologres インスタンスを手動でアップグレードする方法の詳細については、「インスタンスのアップグレード」をご参照ください。Hologres DingTalk グループへの参加方法の詳細については、「Hologres のオンラインサポートを受ける」をご参照ください。

    • クロスデータベースクエリは、同じメジャーバージョンで、かつ Hologres V1.1 以降のインスタンスでのみサポートされています。たとえば、Hologres インスタンス V1.1 のデータベースのデータを Hologres インスタンス V1.3 のデータベースからクエリすることはできません。

    • データベース間でクエリできるのは、Hologres 内部テーブルのデータのみです。Hologres 外部テーブルまたは Hologres ビューのデータをデータベース間でクエリすることはできません。

    • データベース間でクエリできるのは、Hologres 親テーブルのデータのみです。Hologres 子テーブルのデータをデータベース間でクエリすることはできません。

    • INT、BIGINT、DATE などの基本的なデータ型のみがサポートされています。JSON などの複合データ型はサポートされていません。

    • UPDATE、DELETE、TRUNCATE などの文は、外部テーブルでは実行できません。

    • Hologres インスタンスの IP アドレスは固定ではなく、IP アドレスのホワイトリストの影響を受ける可能性があります。データベース間でデータをクエリする場合は、IP アドレスのホワイトリストを構成しないことをお勧めします。

    手順

    データベース間でデータをクエリするには、次の手順を実行します。

    1. 拡張機能を作成する。

      データベース間でデータをクエリする前に、スーパーユーザーとしてデータベースに拡張機能を作成するために、次の文を実行する必要があります。拡張機能は、インスタンスのスーパーユーザーによってデータベースレベルで作成されます。各データベースについて、拡張機能を作成する必要があるのは 1 回だけです。

      -- 拡張機能を作成します。
CREATE EXTENSION hologres_fdw;
      説明

      拡張機能を削除する場合は、次の文を実行します。

      DROP EXTENSION hologres_fdw;

    2. サーバーを作成する。

      拡張機能を作成した後、次の文を実行してサーバーを作成します。サーバーを使用して、データベース間でクエリするデータを持つ Hologres インスタンスに接続できます。

      説明

      データベースに複数のサーバーを作成できます。

      CREATE SERVER <server_name> FOREIGN DATA WRAPPER hologres_fdw OPTIONS (
    host '<endpoint>',
    port '<port>',
    dbname '<dbname>'
);

      パラメーター

      説明

      例

      server_name

      サーバーのカスタム名。

      holo_fdw_server

      host

      Hologres インスタンスのクラシックネットワークエンドポイント。Hologres コンソール のインスタンス詳細ページで、Hologres インスタンスのクラシックネットワークエンドポイントを表示できます。

      hgpostcn-cn-xxx-cn-hangzhou-internal.hologres.aliyuncs.com

      port

      Hologres インスタンスのポート番号。Hologres コンソール のインスタンス詳細ページで、Hologres インスタンスのポート番号を表示できます。

      80

      dbname

      データベース間でクエリするデータのソースデータベースの名前。

      testdb

    3. ユーザーマッピングを作成する。

      サーバーを作成した後、次の文を実行して、データクエリに使用されるユーザーマッピングを作成します。作成されるユーザーマッピングで指定されたユーザーは、ソースデータベースのデータにアクセスするための権限を持っている必要があります。

      説明

      データベースに複数のユーザーマッピングを作成できます。

      CREATE USER MAPPING FOR <Alibaba Cloud アカウントの一意の ID (UID)> SERVER <server_name> 
OPTIONS (
  access_id '<access_id>', 
  access_key '<access_key>'
);

      パラメーター

      説明

      server_name

      手順 2 で作成したサーバーの名前。

      access_id

      Hologres への接続に使用するアカウントの AccessKey ID。AccessKey ID は、Resource Access Management (RAM) コンソールの アクセスキーのペア ページで取得できます。

      access_key

      Hologres への接続に使用するアカウントの AccessKey シークレット。

      例:

      -- 現在のユーザーのユーザーマッピングを作成します。
CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server OPTIONS
(
    access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret'
);

-- UID が 123xxx の RAM ユーザーのユーザーマッピングを作成します。
CREATE USER MAPPING FOR "p4_123xxx" SERVER holo_fdw_server OPTIONS
(
    access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret'
);

-- ユーザーマッピングを削除します。
Drop USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server;
Drop USER MAPPING FOR "p4_123xxx" SERVER holo_fdw_server;

    4. 外部テーブルを作成する。

      次のいずれかの方法を使用して、外部テーブルを作成できます。

      • (推奨) IMPORT FOREIGN SCHEMA 文を実行して外部テーブルを作成する。

        IMPORT FOREIGN SCHEMA 文は使いやすいです。文の例:

        IMPORT FOREIGN SCHEMA <holo_remote_schema> 
[{ LIMIT TO EXCEPT }| (remote_table [, ...])]
FROM SERVER <server_name>
INTO <holo_local_schema>
[ OPTIONS ( OPTION 'values' [, ...])];
        説明

        ソーステーブルのメタデータを外部テーブルに同期する場合、ソーステーブルが存在する外部データベースから大量のメタデータが読み取られます。必要なメタデータを持つソーステーブルを指定するために、LITMIT TO パラメーターを構成することをお勧めします。これにより、同期の範囲が絞り込まれ、効率が向上します。

        パラメーター

        説明

        例

        holo_remote_schema

        データベース間でクエリされるソーステーブルが存在するスキーマの名前。

        remote

        remote_table

        データベース間でクエリするソーステーブルの名前。文が実行されると、ソーステーブルと同じ名前の外部テーブルが現在のデータベースに作成されます。

        lineitem

        server_name

        サーバーの名前。

        holo_fdw_server

        holo_local_schema

        外部テーブルが存在するスキーマの名前。

        local

        OPTION 'values'

        外部テーブルを作成するための競合を処理するために使用されるポリシー。有効な値:

        • import_collate: 列に COLLATE オプションを含めるかどうかを指定します。デフォルト値: true。

        • import_default: 列に DEFAULT 式を含めるかどうかを指定します。デフォルト値: false。

        • import_not_null: 列に NOT NULL 制約を含めるかどうかを指定します。デフォルト値: true。

        import_not_null 'true'

      • CREATE FOREIGN TABLE 文を実行して外部テーブルを作成する。

        次の SQL 文は例を示しています。

        CREATE FOREIGN TABLE <local_table> (
 col_name type,
  ......
)SERVER <server_name>
OPTIONS (schema_name '<remote_schema_name>', table_name '<remote_table>');

        パラメーター

        説明

        例

        local_table

        外部テーブルの名前。デフォルトでは、外部テーブルは public スキーマに作成されます。スキーマを指定する場合は、スキーマ名.テーブル名 の形式でテーブル名の前にスキーマ名を追加します。

        public.lineitem

        server_name

        サーバーの名前。

        holo_fdw_server

        remote_schema_name

        データベース間でクエリされるソーステーブルが存在するスキーマの名前。

        public

        remote_table

        データベース間でクエリするソーステーブルの名前。

        holo_lineitem

    5. 外部テーブルを使用してデータをクエリする。

      外部テーブルを作成した後、外部テーブルを使用してデータをクエリし、クロスデータベースクエリを実装できます。次の SQL 文は例を示しています。

      SELECT * FROM <holo_local_table> LIMIT 10;

    6. (オプション) Hologres 内部テーブルにデータをインポートする。

      データベースまたはインスタンス間でデータをインポートする場合、または外部テーブルのクエリパフォーマンスが期待どおりでない場合は、次の SQL 文を実行してデータを Hologres 内部テーブルにインポートできます。

      説明

      データをインポートする前に、宛先テーブルとして Hologres 内部テーブルを作成する必要があります。内部テーブルの作成方法の詳細については、「内部テーブルを管理する」をご参照ください。

      INSERT INTO <holo_table> SELECT * FROM <holo_local_table>;

    その他の操作

    • サーバーをクエリする。

      次の SQL 文を実行して、作成されたサーバーをクエリできます。

      SELECT
    s.srvname AS "Name",
    pg_catalog.pg_get_userbyid(s.srvowner) AS "Owner",
    f.fdwname AS "Foreign-data wrapper",
    pg_catalog.array_to_string(s.srvacl, E'\n') AS "Access privileges",
    s.srvtype AS "Type",
    s.srvversion AS "Version",
    CASE WHEN srvoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')'
    END AS "FDW options",
    d.description AS "Description"
FROM
    pg_catalog.pg_foreign_server s
    JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid = s.srvfdw
    LEFT JOIN pg_catalog.pg_description d ON d.classoid = s.tableoid
        AND d.objoid = s.oid
        AND d.objsubid = 0
WHERE
    f.fdwname = 'hologres_fdw';

    • ユーザーマッピングをクエリする。

      次の SQL 文を実行して、作成されたユーザーマッピングをクエリできます。

      SELECT
    um.srvname AS "Server",
    um.usename AS "User name",
    CASE WHEN umoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(umoptions)), ', ') || ')'
    END AS "FDW options"
FROM
    pg_catalog.pg_user_mappings um
WHERE
    um.srvname != 'query_log_store_server';

    • ユーザーマッピングを削除する。

      次の SQL 文を実行して、作成されたユーザーマッピングを削除できます。

      DROP USER MAPPING FOR <Alibaba Cloud アカウントの UID> SERVER <server_name>;

      server_name パラメーターは、サーバーの名前を指定します。

    • サーバーを削除する。

      次の SQL 文を実行して、作成されたサーバーを削除できます。

      重要

      サーバーを削除する前に、サーバー上に作成されたユーザーマッピングと外部テーブルを削除する必要があります。

      DROP SERVER <server_name>;

      server_name パラメーターは、サーバーの名前を指定します。

    例

    このセクションでは、データベース間でデータをクエリする方法に関する関連例を示します。これには、例の事前設定と完全なサンプルコードが含まれます。

    • 事前設定

      データベース間でデータをクエリする前に、Hologres インスタンスを準備し、Hologres データベースを作成し、Hologres 内部テーブルに関連データを準備する必要があります。

      • インスタンス関連の設定

        パラメーター

        例

        ソース Hologres インスタンスの ID

        hgpostcn-cn-i7mxxxxxxxxx

        ソース Hologres データベースの名前

        remote_db

        ソース Hologres データベースのスキーマの名前

        remote

        ソース Hologres データベースの内部テーブルの名前

        lineitem

        ソース Hologres データベースの親テーブルの名前

        holo_dwd_product_movie_basic_info

      • Hologres 内部テーブルを作成するために使用される DDL 文

        BEGIN;
CREATE SCHEMA remote;
CREATE TABLE "remote"."lineitem" (
 "l_orderkey" int8 NOT NULL,
 "l_linenumber" int8 NOT NULL,
 "l_suppkey" int8 NOT NULL,
 "l_partkey" int8 NOT NULL,
 "l_quantity" int8 NOT NULL,
 "l_extendedprice" int8 NOT NULL,
 "l_discount" int8 NOT NULL,
 "l_tax" int8 NOT NULL,
 "l_returnflag" text NOT NULL,
 "l_linestatus" text NOT NULL,
 "l_shipdate" timestamptz NOT NULL,
 "l_commitdate" timestamptz NOT NULL,
 "l_receiptdate" timestamptz NOT NULL,
 "l_shipinstruct" text NOT NULL,
 "l_shipmode" text NOT NULL,
 "l_comment" text NOT NULL
);
COMMIT;

      • Hologres パーティションテーブルを作成するために使用される DDL 文

        -- 親テーブルを作成します。
BEGIN;
CREATE TABLE "remote"."holo_dwd_product_movie_basic_info" (
 "movie_name" text,
 "director" text,
 "scriptwriter" text,
 "area" text,
 "actors" text,
 "type" text,
 "movie_length" text,
 "movie_date" text,
 "movie_language" text,
 "imdb_url" text,
 "ds" text
)
PARTITION BY LIST (ds);
comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_name" is '映画名';
comment on column "remote"."holo_dwd_product_movie_basic_info"."director" is '監督';
comment on column "remote"."holo_dwd_product_movie_basic_info"."scriptwriter" is '脚本家';
comment on column "remote"."holo_dwd_product_movie_basic_info"."area" is '地域/国';
comment on column "remote"."holo_dwd_product_movie_basic_info"."actors" is '俳優';
comment on column "remote"."holo_dwd_product_movie_basic_info"."type" is 'タイプ';
comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_length" is '映画の長さ';
comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_date" is '公開日';
comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_language" is '映画の言語';
comment on column "remote"."holo_dwd_product_movie_basic_info"."imdb_url" is 'IMDb URL';
COMMIT;

-- 20170122 パーティションの子テーブルを作成します。
CREATE TABLE IF NOT EXISTS "remote".holo_dwd_product_movie_basic_info_20170122 PARTITION OF "remote".holo_dwd_product_movie_basic_info FOR VALUES IN ('20170122');

    • 例 1: パーティション化されていないテーブルのデータをデータベース間でクエリする

      説明

      クエリをデータベース間で開始するデータベースで、次の文をすべて実行する必要があります。

      -- スーパーユーザーとして拡張機能を作成します。
CREATE EXTENSION hologres_fdw;

-- スーパーユーザーとしてサーバーを作成します。
CREATE SERVER holo_fdw_server FOREIGN DATA WRAPPER hologres_fdw OPTIONS (
    host 'hgpostcn-cn-i7mxxxxxxxxx-cn-hangzhou-internal.hologres.aliyuncs.com',
    port '80',
    dbname 'remote_db'
);

-- 現在のユーザーのユーザーマッピングを作成します。
CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server 
OPTIONS (access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret');

-- スキーマを作成します。ローカルスキーマは、外部データラッパー (FDW) 機能を使用するインスタンスではオプションです。ビジネススキーマを使用できます。
CREATE SCHEMA local;

-- 外部テーブルを作成します。
IMPORT FOREIGN SCHEMA remote 
LIMIT to (lineitem)
FROM SERVER holo_fdw_server  
INTO local
OPTIONS ( 
  import_not_null 'true'
);

SELECT * FROM local.lineitem limit 10;

    • 例 2: パーティションテーブルのデータをデータベース間でクエリする

      CREATE EXTENSION hologres_fdw;

CREATE SERVER holo_fdw_server FOREIGN DATA WRAPPER hologres_fdw OPTIONS (
    host 'hgpostcn-cn-i7mxxxxxxxxx-cn-hangzhou-internal.hologres.aliyuncs.com',
    port '80',
    dbname 'remote_db'
);

-- 現在のユーザーのユーザーマッピングを作成します。
CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server 
OPTIONS (access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret');

-- スキーマを作成します。ローカルスキーマは、FDW 機能を使用するインスタンスではオプションです。ビジネススキーマを使用できます。
CREATE SCHEMA local;

-- FDW 機能を使用するインスタンスであるローカルインスタンスに切り替えます。
IMPORT FOREIGN SCHEMA remote
LIMIT to (holo_dwd_product_movie_basic_info)
FROM SERVER holo_fdw_server
INTO local
OPTIONS (
  import_not_null 'true'
);

-- テーブル全体のデータをクエリします。
SELECT * FROM local.holo_dwd_product_movie_basic_info limit 10;

    • 例 3: 外部テーブルから内部テーブルにデータをインポートする

      -- スキーマを作成します。ローカルスキーマは、FDW 機能を使用するインスタンスではオプションです。ビジネススキーマを使用できます。
CREATE SCHEMA local;

-- 内部テーブルを作成します。
BEGIN;
CREATE TABLE "local"."dwd_product_movie_basic_info" (
 "movie_name" text,
 "director" text,
 "scriptwriter" text,
 "area" text,
 "actors" text,
 "type" text,
 "movie_length" text,
 "movie_date" text,
 "movie_language" text,
 "imdb_url" text,
 "ds" text
);
COMMIT;

-- 内部テーブルにデータをインポートします。
insert into local.dwd_product_movie_basic_info select * from local.holo_dwd_product_movie_basic_info;

    一般的なエラーとトラブルシューティング

    サーバーを作成するときは、クエリするデータを持つインスタンスとしてプライマリインスタンスを使用することをお勧めします。次のエラーが発生した場合は、解決策を参照してエラーをトラブルシューティングしてください。

    • 問題の説明: 読み取り専用のセカンダリインスタンスをクエリするデータを持つインスタンスとして使用すると、次のようなエラーメッセージが報告されます。

      内部エラー: クエリ [xxxxx] の使用可能なシャードを取得できませんでした。後でもう一度試してください。

    • 解決策: クエリするデータを持つ読み取り専用のセカンダリインスタンスに対応するプライマリインスタンスと、データベース間でクエリを開始するインスタンスで、次の SQL 文を実行します。

      ALTER DATABASE <database> SET hg_experimental_enable_dml_read_replica=ON;