跨库查询

功能介绍

Database是PostgreSQL生态下数据库的最高级别，同一个实例中的不同Database之间无法直接互相查询数据，在保证了数据库业务隔离的同时，也带来了跨业务跨库之间联合查询的不便。

在AnalyticDB PostgreSQL版中，您可以通过外部数据包装器FDW（foreign-data wrapper）进行跨库查询，阿里云提供的FDW基于AnalyticDB PostgreSQL版的MPP架构打造，能够充分发挥计算节点的性能优势，提高跨库查询的数据访问效率。

注意事项

• 高速跨库查询仅支持以下数据库内核版本：

  • 存储弹性模式7.0版：V7.0.1.x及以上版本。

  • 存储弹性模式6.0版：V6.3.11.2及以上版本。

  • Serverless模式：V1.0.6.x以及上版本。

• FDW外表仅支持SELECT和INSERT操作，不支持UPDATE和DELETE操作。

• 仅存储弹性模式7.0版本支持JOIN下推和AGG下推功能。

• 仅存储弹性模式7.0版本的ORCA优化器支持对FDW外表生成执行计划，存储弹性模式6.0版本和Serverless模式的ORCA优化器无法处理外表，会使用原生优化器生成执行计划。

操作步骤

以下内容将为实例的两个Database（db01和db02）之间开通数据互访。开通后，您可以在db01库访问db02库中的表。

1. 使用psql连接实例，操作步骤，请参见客户端连接。

2. 创建db01库和db02库。

   CREATE DATABASE db01;
   CREATE DATABASE db02;

3. 在db01和db02库创建跨库查询插件greenplum_fdw和gp_parallel_retrieve_cursor。

   CREATE EXTENSION IF NOT EXISTS gp_parallel_retrieve_cursor;
   CREATE EXTENSION IF NOT EXISTS greenplum_fdw;
   GRANT USAGE ON FOREIGN DATA WRAPPER greenplum_fdw TO PUBLIC;

   说明

   • AnalyticDB PostgreSQL版实例暂不支持自行安装插件，如果您的实例未安装上述插件，请提交工单联系技术支持进行安装。

   • 7.0版实例无需创建gp_parallel_retrieve_cursor插件。

4. 切换到db02库，准备测试数据。

   \c db02
   CREATE SCHEMA s01;
   CREATE TABLE s01.t1(a int, b int, c text); 
   INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');

5. 切换到db01库，创建SERVER和USER MAPPING。

   • 创建SERVER。

     \c db01
     CREATE SERVER db02 FOREIGN DATA WRAPPER greenplum_fdw
         OPTIONS (host 'localhost',
           			 dbname 'db02');

     参数说明。

     参数	说明
     host	跨库访问此处填写'localhost'。
     dbname	源库的名称，此处示例为db02。

   • 创建USER MAPPING，更多关于USER MAPPING的介绍，请参见CREATE USER MAPPING。

     CREATE USER MAPPING FOR CURRENT_USER SERVER db02
         OPTIONS (user 'report', password '******');

     参数说明。

     参数	说明
     user	数据库账号。 该账号需要拥有db02库的读权限（如需执行INSERT操作，则还需要写权限），如何创建账号，请参见创建数据库账号。
     password	以上账号的密码。

6. 在db01库实现跨库查询。

   实现跨库查询有如下两种方式：

   • 为源表创建外表。

     CREATE SCHEMA s01;
     CREATE FOREIGN TABLE s01.t1(a int, b int) 
     	server db02 options(schema_name 's01', table_name 't1');

     上述方式的优缺点如下：

     • 优点：可以灵活定制外表的DDL，例如db02库中t1有三个字段a，b，c，但目标库只需要a，b两个字段，因此可以在创建外表的时候指定字段。

     • 缺点：需要知道每一个表的DDL，一次性导入多个外表较为繁琐。

   • 导入源库下Schema中的所有表。

     CREATE SCHEMA s01;
     IMPORT FOREIGN SCHEMA s01 LIMIT TO (t1, t2, t3) 
     	FROM SERVER db02 INTO s01;

     上述方式的优缺点如下：

     • 优点：可以快速导入外表，而且不需要知道每一个表的DDL。

     • 缺点：不够灵活，每个表的名称和源库一致，字段也一致。

     更多介绍，请参见IMPORT FOREIGN SCHEMA。

7. 在db01库查询db02库的数据。

   查询db01库的数据。

   SELECT * FROM s01.t1;

   返回示例如下。

    a  | b  | c
   ----+----+----
     2 | 12 | t1
     3 | 13 | t1
     4 | 14 | t1
     7 | 17 | t1
     8 | 18 | t1
     1 | 11 | t1
     5 | 15 | t1
     6 | 16 | t1
     9 | 19 | t1
    10 | 20 | t1
   (10 rows)

相关文档

AnalyticDB PostgreSQL版还提供了跨实例查询功能，详情请参见跨实例查询。