Hologres:クロスデータベースクエリ (Beta)

制限事項

• クロスデータベースクエリは、Hologres V1.1 以降のバージョンでのみサポートされます。ご利用のインスタンスが V1.1 より前のバージョンの場合、「一般的なアップグレード準備失敗エラー」をご参照いただくか、Hologres DingTalk グループに参加してフィードバックをお願いします。詳細については、「オンラインサポートを受けるには？」をご参照ください。

• クエリ可能なのは、Hologres V1.1 以降のインスタンスのみです。クロスデータベースクエリは、同一メジャーバージョンの Hologres インスタンス間でのみサポートされます。異なるメジャーバージョン間のクエリ（例：V1.3 インスタンスから V1.1 インスタンスへのクエリ）はサポートされていません。

• クエリ可能なのは、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. サーバーの作成

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

   説明

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

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

   パラメーター	説明	例
   server_name	サーバーの名前です。任意のカスタム名を指定できます。	holo_fdw_server
   host	Hologres インスタンスのクラシックネットワークエンドポイントです。Hologres 管理コンソールのインスタンス詳細ページにアクセスし、「インスタンス構成」タブからクラシックネットワーク（イントラネット）エンドポイントを取得できます。	hgpostcn-cn-xxx-cn-hangzhou-internal.hologres.aliyuncs.com
   port	Hologres インスタンスのポートです。Hologres 管理コンソールのインスタンス詳細ページにアクセスし、「インスタンス構成」タブからポートを取得できます。	80
   dbname	クエリ対象のソースデータベースの名前です。	testdb

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

   サーバーを作成した後、次の文を実行して、データのクエリ用のユーザー マッピングを作成します。ユーザー マッピングには、ソースデータをクエリするのに必要な権限があることを確認してください。

   説明

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

   CREATE USER MAPPING FOR <account_uid> SERVER <server_name> 
   OPTIONS (
     access_id '<access_id>', 
     access_key '<access_key>'
   );

   パラメーター	説明
   server_name	前ステップで指定したサーバーの名前です。
   access_id	現在のアカウントの AccessKey ID です。AccessKey ID を取得するには、RAM コンソール に移動します。
   access_key	現在のアカウントの AccessKey Secret です。

   使用例

   -- 現在のユーザー用のユーザー マッピングを作成します。
   CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server OPTIONS
   (
       access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret'
   );
   
   -- RAM ユーザー 123xxx 用のユーザー マッピングを作成します。
   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. 外部テーブルの作成

   外部テーブルは、以下の 2 つの方法で作成できます。

   • （推奨）IMPORT FOREIGN SCHEMA 文を使用して外部テーブルを作成

     IMPORT FOREIGN SCHEMA 文を使用すると、外部テーブルの作成がよりシンプルかつ便利になります。SQL 文は以下のとおりです。

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

     説明

     外部テーブルのメタデータをインポートするには、外部データベースから大量のメタデータを読み込む必要があります。そのため、必要なテーブルのみをインポートするために LIMIT TO パラメーターの使用を推奨します。これにより、データベース全体をインポートすることを避け、外部テーブル作成の効率を確保できます。

     パラメーター	説明	例
     holo_remote_schema	ソーステーブルが存在するスキーマの名前です。	remote
     remote_table	クエリ対象のソーステーブルの名前です。外部テーブルが作成されると、ソーステーブルと同じ名前の外部テーブルが新しいデータベース内に作成されます。	lineitem
     server_name	作成済みのサーバーの名前です。	holo_fdw_server
     holo_local_schema	外部テーブルを作成するスキーマの名前です。	local
     OPTION 'values'	外部テーブル作成時の競合ポリシーです。以下のパラメーターが含まれます。 import_collate：列の照合構成を含めるかどうかを指定します。デフォルト値は true です。 import_default：列のデフォルト値を含めるかどうかを指定します。デフォルト値は false です。 import_not_null：列の非 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 スキーマに配置されます。カスタムスキーマを使用する場合は、schema.table 形式で、テーブル名の前にスキーマ名を指定します。	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. （オプション）内部テーブルへのデータインポート

   データベースおよびインスタンス間でデータをインポートしたい場合、または外部テーブルのクエリパフォーマンスが期待通りでない場合は、次の SQL 文を使用してデータを 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 <account_uid> SERVER <server_name>;

  server_name はサーバーの名前です。

• サーバーの削除

  サーバーを削除するには、次の SQL 文を使用します。

  重要

  サーバーを削除する前に、関連するユーザー マッピングおよび外部テーブルを削除する必要があります。

  DROP SERVER <server_name>;

  server_name はサーバーの名前です。

使用例

このセクションでは、前提となる構成と、クロスデータベースデータクエリの完全な実行例を 3 つ紹介します。

• プロビジョニング構成

  実行例を開始する前に、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 'movie name';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."director" is 'director';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."scriptwriter" is 'scriptwriter';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."area" is 'production area/country';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."actors" is 'actors';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."type" is 'type';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_length" is 'movie length';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_date" is 'release date';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_language" is 'language';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."imdb_url" is 'IMDb number';
    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;