本文为您介绍如何在Hologres中使用跨库查询数据以及相关的使用示例。

背景信息

Hologres从V1.1版本开始,支持在不同地域、不同实例和不同数据库之间,通过创建外部表的方式查询数据,操作方便简单。Hologres兼容Postgres,通过外部表的方式跨库查询原理同Postgres,详情请参见FDW

使用限制

  • 仅Hologres V1.1及以上版本支持跨库查询数据,如果您的实例是V1.1以下版本,请您提交工单或加入在线支持钉钉群申请升级实例。
  • 仅支持跨库查询Hologres V1.1及以上版本的实例,例如您的Hologres实例是V1.1版本,想要跨库查询V0.10版本实例中的数据,执行会报错,需要将跨库查询的实例升级至V1.1及以上版本。
  • 仅支持跨库查询Hologres内部表中的数据,不支持查询Hologres外部表和Hologres的视图View。
  • 仅支持跨库查询Hologres分区父表,不支持查询Hologres分区子表。
  1. 创建extension
    在使用之前,需要Superuser在数据库中执行以下语句创建extension。创建extension需要实例的Superuser执行,该操作针对整个数据库生效,一个数据库只需执行一次。
    CREATE 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的名称,自定义设置。 hologres_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 CURRENT_USER 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。
  4. 创建外部表
    创建外部表有两种方式,分别如下。
    • (推荐)使用IMPORT FOREIGN SCHEMA语句创建外部表
      使用IMPORT FOREIGN SCHEMA语句创建外部表,操作更加简单方便,SQL语句如下。
      IMPORT FOREIGN SCHEMA <holo_old_schema> 
      LIMIT to (<old_table>)
      FROM SERVER <server_name>
      INTO <holo_new_schema>
      OPTIONS ( 
        <values>
      );
      参数 说明 示例
      holo_old_schema 要跨库查询的源表所在的schema名称。 public
      old_table 要跨库查询的源表名称,外部表创建成功,将会在新的数据库创建与源表同名的外部表。 lineitem
      server_name 创建的server的名称。 hologres_server
      holo_new_schema 创建的外部表所在的schema名称。 public
      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 <new_table> (
       col_name type,
        ......
      )SERVER <server_name>
      OPTIONS (schema_name '<schema_name>', table_name '<old_table>');
      参数 说明 示例
      new_table 创建的外部表名称。默认表放在public schema,若是有自定义schema,需要在表名前加schema名,为schema.table public.lineitem
      server_name 创建的server的名称。 hologres_server
      schema_name 要跨库查询的源表所在的schema名称。 public
      old_table 要跨库查询的源表名称。 holo_lineitem
  5. 查询外部表数据
    外部表创建完成后,可以直接查询外部表数据实现跨库查询,SQL语句如下。
    select * from holo_new_foreign_table;
  6. 数据导入内部表
    若是想要实现跨库、跨实例导入数据,或者外部表查询性能不满足预期,可以通过如下SQL语句,将数据导入至Hologres内部表。
    说明 在使用之前,需要先创建一张内部表用于接收数据,创建内部表请参见
    insert into holo_table select * from holo_new_foreign_table;

使用示例

本小节为您介绍跨库查询数据使用示例前的预置配置以及三个相关的完整示例。

  • 预置配置
    在示例之前,需要在Hologres里面已准备好一个实例,以及创建好数据库,以及准备好相关的内部表数据,具体内容如下。
    • 实例相关配置
      配置 说明
      源Hologres实例ID hgpostcn-cn-i7mxxxxxxxxx
      源Hologres数据库名称 remote_db
      源Hologres实例schema名称 holo_old
      源Hologres内部表名称 lineitem
    • 源Hologres内部表DDL
      BEGIN;
      CREATE TABLE holo_old.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 "holo_old"."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 "holo_old"."holo_dwd_product_movie_basic_info"."movie_name" is '电影名称';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."director" is '导演';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."scriptwriter" is '编剧';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."area" is '制片地区/国家';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."actors" is '主演';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."type" is '类型';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."movie_length" is '电影长度';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."movie_date" is '上映日期';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."movie_language" is '语言';
      comment on column "holo_old"."holo_dwd_product_movie_basic_info"."imdb_url" is 'imdb号';
      COMMIT;
      
      --创建20170122为分区的分区子表
      CREATE TABLE IF NOT EXISTS "holo_old".holo_dwd_product_movie_basic_info_20170122 PARTITION OF "holo_old".holo_dwd_product_movie_basic_info FOR VALUES IN ('20170122');
                                      
  • 示例1:跨库查询非分区表
    说明 以下所有代码实例,需要在跨库查询的数据库中执行。
    -- superuser创建extension
    CREATE EXTENSION hologres_fdw;
    
    --superuser创建server
    CREATE SERVER hologres_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 hologres_server 
    OPTIONS (access_id 'LTAIxxxxxx', access_key 'Trjgyyyyyyy');
    
    
    -- 创建外部表
    IMPORT FOREIGN SCHEMA holo_old 
    LIMIT to (lineitem)
    FROM SERVER hologres_server  
    INTO holo_new 
    OPTIONS ( 
      import_not_null 'true'
    );
    
    SELECT * FROM holo_new.lineitem
  • 示例2:跨库查询分区表
    CREATE EXTENSION hologres_fdw;
    
    CREATE SERVER hologres_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 hologres_server 
    OPTIONS (access_id 'LTAIxxxxxx', access_key 'Trjgyyyyyyy');
    -- 创建外部表
    IMPORT FOREIGN SCHEMA holo_old 
    LIMIT to (dwd_product_movie_basic_info)
    FROM SERVER hologres_server_2  
    INTO holo_new 
    OPTIONS ( 
      import_not_null 'true'
    );
    
    -- 直接查全表数据
    SELECT * FROM holo_new.dwd_product_movie_basic_info;
    -- 查某个分区子表
    SELECT * FROM holo_new.dwd_product_movie_basic_info_20120722;
    
                        
  • 示例3:将外部表数据导入内部表
    --创建内部表
    BEGIN;
    CREATE TABLE "holo_new"."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
    );
    COMMIT;
    
    --导入内部表
    insert into holo_new.holo_dwd_product_movie_basic_info select * from holo_new.dwd_product_movie_basic_info;