Hologres：跨庫查詢（Beta）

使用限制

• 僅Hologres V1.1及以上版本支援跨庫查詢資料，如果您的執行個體是V1.1以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。

• 僅支援跨庫查詢Hologres V1.1及以上版本的執行個體，同時僅支援相同大版本的Hologres執行個體互相跨庫查詢，暫不支援跨大版本的執行個體間互相查詢，例如不支援V1.3版本的執行個體查詢V1.1版本的執行個體。

• 僅支援跨庫查詢Hologres內部表中的資料，不支援查詢Hologres外部表格和Hologres的視圖View。

• 僅支援跨庫查詢Hologres分區父表，不支援查詢Hologres分區子表。

• 僅支援INT、BIGINT、DATE等基礎資料型別 (Elementary Data Type)，JSON等複雜資料類型不支援。

• 不支援對外表執行UPDATE、DELETE及TRUNCATE等命令。

• 由於Hologres執行個體IP不固定，可能會受IP白名單限制，使用跨庫查詢功能時暫不建議配置IP白名單。

跨庫查詢

使用跨庫查詢功能的詳細操作步驟如下。

1. 建立extension

   在使用之前，需要Superuser在資料庫中執行以下語句建立extension。建立extension需要執行個體的Superuser執行，該操作針對整個資料庫生效，一個資料庫只需執行一次。

   --建立extension
   CREATE EXTENSION hologres_fdw;

   說明

   如需卸載extension請執行如下命令。

   DROP EXTENSION hologres_fdw;

2. 建立Server

   建立extension成功後，執行以下語句建立Server，用於串連跨庫查詢的執行個體。

   說明

   同一個資料庫，可以建立多個Server。

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

   參數	說明	樣本
   server_name	Server的名稱，自訂設定。	holo_fdw_server
   host	Hologres執行個體的傳統網路地址。您可以進入Hologres管理主控台的執行個體詳情頁，從執行個體配置頁簽擷取傳統網路（內網）地址。	hgpostcn-cn-xxx-cn-hangzhou-internal.hologres.aliyuncs.com
   port	Hologres執行個體的連接埠。您可以進入Hologres管理主控台的執行個體詳情頁，從執行個體配置頁簽擷取執行個體連接埠。	80
   dbname	需要跨庫查詢的來源資料庫名稱。	testdb

3. 建立使用者映射

   建立完server之後，使用如下語句建立使用者映射，用於查詢資料，需要保證建立的使用者映射有對應的原資料查詢許可權。

   說明

   同一個資料庫，可以建立多個使用者映射。

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

   參數	說明
   server_name	Server的名稱，上一步驟自訂設定的名稱。
   access_id	當前訪問帳號的AccessKey ID，您可以單擊RAM控制台，擷取AccessKey ID。
   access_key	當前訪問帳號的AccessKey Secret，您可以單擊RAM控制台，擷取AccessKey Secret。

   使用樣本

   --為目前使用者建立使用者映射
   create user mapping for current_user server holo_fdw_server options
   (
       access_id 'LTAI5txxx', access_key 'y8LUUyyy'
   );
   
   --為RAM使用者123xxx建立使用者映射
   create user mapping for "p4_123xxx" server holo_fdw_server options
   (
       access_id 'LIlY5txxx', access_key 'KsjkXKyyy'
   );
   
   --刪除使用者映射
   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語句建立外部表格，操作更加簡單方便，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	要跨庫查詢的源表所在的schema名稱。	remote
     remote_table	要跨庫查詢的源表名稱，外部表格建立成功，將會在新的資料庫建立與源表同名的外部表格。	lineitem
     server_name	建立的Server的名稱。	holo_fdw_server
     holo_local_schema	建立的外部表格所在的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 schema，若是有自訂schema，需要在表名前加schema名，為schema.table。	public.lineitem
     server_name	建立的Server的名稱。	holo_fdw_server
     remote_schema_name	要跨庫查詢的源表所在的Schema名稱。	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>;

其他相關操作

• 查詢Server。

  您可以使用如下SQL查詢已經建立的Server。

  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 <帳號uid> SERVER <server_name>;

  server_name為Server的名稱。

• 刪除Server。

  您可以使用如下SQL刪除某個Server。

  重要

  刪除Server前需要刪除相關的使用者映射和外部表格。

  DROP SERVER <server_name>;

  server_name為Server的名稱。

使用樣本

本小節為您介紹跨庫查詢資料使用樣本前的預置配置以及三個相關的完整樣本。

• 預置配置

  在樣本之前，需要在Hologres裡面已準備好一個執行個體，以及建立好資料庫，以及準備好相關的內部表資料，具體內容如下。

  • 執行個體相關配置

    配置	說明
    源Hologres執行個體ID	hgpostcn-cn-i7mxxxxxxxxx
    源Hologres資料庫名稱	remote_db
    源Hologres執行個體schema名稱	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號';
    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：跨庫查詢非分區表

  說明

  以下所有代碼執行個體，需要在跨庫查詢的資料庫中執行。

  -- superuser建立extension
  CREATE EXTENSION hologres_fdw;
  
  --superuser建立server
  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 'LTAIxxxxxx', access_key 'Trjgyyyyyyy');
  
  --建立schema（使用fdw功能的執行個體，local這個schema是非必需建立，可以換成業務的schema）
  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 'LTAIxxxxxx', access_key 'Trjgyyyyyyy');
  
  -- 建立schema（使用fdw功能的執行個體，local這個schema是非必需建立，可以換成業務的schema）
  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：將外部表格資料匯入內部表

  -- 建立schema（使用fdw功能的執行個體，local這個schema是非必需建立，可以換成業務的schema）
  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;
                      

常見報錯

建立Server時，推薦使用主執行個體作為被查詢執行個體。如果出現以下報錯情境，請參見解決方案。

• 報錯情境：使用唯讀從執行個體作為被查詢執行個體出現類似如下報錯資訊。

  internal error: Failed to get available shards for query[xxxxx], please retry later.

• 解決方案：請在被查詢唯讀從執行個體的主執行個體和跨庫查詢的發起端執行個體內執行如下SQL命令。

  ALTER DATABASE <database> SET hg_experimental_enable_dml_read_replica=ON;