实时数仓Hologres：跨库查询（Beta）

使用限制

• 仅Hologres V1.1及以上版本支持跨库查询数据，如果您的实例是V1.1以下版本，请您通过搜索（钉钉群号：32314975）加入实时数仓Hologres交流群申请升级实例。
• 仅支持跨库查询Hologres V1.1及以上版本的实例，同时仅支持相同大版本的Hologres实例互相跨库查询，暂不支持跨大版本的实例间互相查询，例如不支持V1.3版本的实例查询V1.1版本的实例。
• 仅支持跨库查询Hologres内部表中的数据，不支持查询Hologres外部表和Hologres的视图View。
• 仅支持跨库查询Hologres分区父表，不支持查询Hologres分区子表。
• 仅支持INT、BIGINT、DATE等基本数据类型，JSON等复杂数据类型不支持。
• 不支持对外表执行UPDATE、DELETE及TRUNCATE等命令。

跨库查询

使用跨库查询功能的详细操作步骤如下。

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，您可以单击用户中心，获取AccessKey ID。
   access_key	当前访问账号的AccessKey Secret，您可以单击用户中心，获取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' [, ...])];

     说明 导入外表元数据需要读取较多外部数据库元数据，建议通过LITMIT 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;