通过同构数据源访问实现跨实例查询_云原生数据仓库 AnalyticDB PostgreSQL版(AnalyticDB for PostgreSQL)-阿里云帮助中心

若您需要访问同阿里云账号下其他AnalyticDB PostgreSQL版实例数据，可以使用同构数据源访问功能，将不同实例以外部数据源的形式轻松快速地实现联合查询，在保证数据实时性的同时，有效减少数据冗余。

说明

本地实例：创建连接、使用数据的实例。

远程实例：接受连接、提供数据的实例。

注意事项

本功能仅支持以下数据库内核版本：
- AnalyticDB PostgreSQL 6.0版实例需为v6.6.0.0及以上版本。
- AnalyticDB PostgreSQL 7.0版实例需为v7.0.3.0及以上版本。
- AnalyticDB PostgreSQL版Serverless模式实例需为v2.1.1.5及以上版本。
若访问的远程实例为Serverless模式实例，当远程实例处于扩缩容状态时，扩缩容期间数据不可访问。

前提条件

本地实例与远程实例需要属于同一个阿里云账号，且位于同一个地域，同一个VPC下。
本地实例与远程实例已创建初始账号，具体操作，请参见创建数据库账号。

操作步骤

本文以开通本地实例A和远程实例B（两个实例位于同一地域、同一可用区、同一 VPC 下）跨实例查询为例。开通跨实例查询后，您可以在本地实例A的local_db库访问远程实例Bremote_db库中的表，并利用计算节点之间高速互联的特性，轻松实现联合查询。

步骤一：准备测试数据

使用psql连接实例，操作步骤，请参见客户端连接。
分别在本地实例A和远程实例B上创建数据库。
在本地实例A上创建local_db库，并切换至local_db库。
```
CREATE DATABASE local_db;
\c local_db
```
在远程实例B上创建remote_db库，并切换至remote_db库。
```
CREATE DATABASE remote_db;
\c remote_db
```

在远程实例B的remote_db库中准备测试数据。

CREATE SCHEMA s01;

CREATE TABLE s01.t1(a int, b int, c text); 
CREATE TABLE s01.t2(a int, b int, c text); 
CREATE TABLE s01.t3(a int, b int, c text); 

INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');
INSERT INTO s01.t2 VALUES(generate_series(11,20),generate_series(11,20),'t2');
INSERT INTO s01.t3 VALUES(generate_series(21,30),generate_series(11,20),'t3');

步骤二：新增数据源

登录云原生数据仓库AnalyticDB PostgreSQL版控制台。
在控制台左上角，选择实例所在地域。
找到目标实例，单击实例ID。
在左侧导航栏，单击外部数据源管理。

在同构数据源访问页签，单击新增数据源，并配置如下参数。

参数	说明
本地实例Id	本地实例A的ID。
本地数据库名称	本地实例A的数据库名称`local_db`。
本地初始账号	本地实例A的账号。
本地初始密码	账号对应的密码。
远程实例Id	远程实例B的ID。
远程数据库名称	远程实例B的数据库名称`remote_db`。
远程数据库账号	远程实例B的账号。该账号需要拥有`remote_db`库的读权限（如需执行INSERT操作，则还需要写权限）。
远程数据库密码	账号对应的密码。
数据源名称	可选参数，自定义数据源名称。

单击确定，等待数据源连接状态变为运行中，您可以进行跨实例查询操作。
说明
数据源创建完成后，您还可以进行以下操作。
- 编辑数据源：单击操作列的编辑，修改远程实例的账号和密码。
- 删除数据源：单击操作列的删除，删除数据源。

步骤三：跨实例查询

在本地实例A的local_db库中实现跨实例查询。
实现跨实例查询有如下两种方式：
- （推荐）方式一：导入源库下Schema中的所有表。
```
CREATE SCHEMA s01;
IMPORT FOREIGN SCHEMA s01 LIMIT TO (t1, t2, t3) 
	FROM SERVER <data_source_name> INTO s01;
```
  data_source_name为数据源名称，可在控制台同构数据源访问页签查看。
  通过IMPORT FOREIGN SCHEMA语句创建外表的语法，请参见IMPORT FOREIGN SCHEMA。
  上述方式的优缺点如下：
  - 优点：可以快速导入外表，而且不需要知道每个表的DDL。
  - 缺点：不够灵活，每个表的名称和字段需要与源库一致。
- 方式二：为源表创建外表。
```
CREATE SCHEMA s01;
CREATE FOREIGN TABLE s01.t1(a int, b int) 
	server <data_source_name> options(schema_name 's01', table_name 't1');
```
  data_source_name为数据源名称，可在控制台同构数据源访问页签查看。
  上述方式的优缺点如下：
  - 优点：可以灵活定制外表的DDL，例如remote_db库中t1有三个字段a，b，c，但目标库只需要a，b两个字段，因此可以在创建外表的时候指定字段。
  - 缺点：需要知道每一个表的DDL，一次性导入多个外表较为繁琐。
在实例A的local_db库中查询实例Bremote_db库中的数据。

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)