怎么实现跨数据库查询 - 云原生数据仓库AnalyticDB

当需要查询分散在不同数据库中的数据时，您可以使用云原生数据仓库 AnalyticDB PostgreSQL 版的跨库查询功能方便地将数据聚合在一起，进行高效查询，提高数据访问效率。

功能介绍

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库中的表。

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

创建db01库和db02库。

CREATE DATABASE db01;
CREATE DATABASE db02;

在db01和db02库创建跨库查询插件greenplum_fdw和gp_parallel_retrieve_cursor。具体操作，请参见安装、升级与卸载插件。

切换到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');

切换到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

以上账号的密码。

在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。

在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：跨库查询

功能介绍

注意事项

操作步骤

相关文档

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