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

若您需要访问同阿里云账号下的其他AnalyticDB for PostgreSQL实例数据，可以使用同构数据源访问功能。该功能允许您将不同实例作为外部数据源做联合查询，在保证数据实时性的同时，有效减少数据冗余。

跨实例查询涉及至少两个实例。为了区分，本文将发起查询的实例称为本地实例，作为外部数据源的实例称为远程实例。

版本限制

内核版本为v6.6.0.0及以上的AnalyticDB PostgreSQL 6.0版实例。
内核版本为v7.0.3.0及以上的AnalyticDB PostgreSQL 7.0版实例。
内核版本为v2.1.1.5及以上的AnalyticDB PostgreSQL版Serverless模式实例。

说明

您可以查看实例的内核小版本。如您的实例不满足上述版本要求，建议您升级内核小版本。

前提条件

本地实例与远程实例须位于同一VPC。

准备工作

本文以本地实例A和远程实例B之间的跨实例查询为例。参考本文，您可以在本地实例A的local_db库访问远程实例Bremote_db库中的表，轻松实现联合查询。

为本地实例A和远程实例B创建初始账号。
将客户端的IP地址或IP地址段分别添加到AnalyticDB for PostgreSQL本地实例A和远程实例B的白名单中。
准备测试数据。
- 为本地实例A创建数据库和Schema，用于后续创建外部表、访问远程实例B的数据。
  1. 连接本地实例A。
```
psql -h gp-bp166cyrtr4p*****-master.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount
```
    如果您的ECS与AnalyticDB for PostgreSQL实例不属于同一VPC，或者您在本地机器上连接AnalyticDB for PostgreSQL实例，请使用外网地址。
  2. 在本地实例A上创建local_db库，并切换至local_db库。
```
CREATE DATABASE local_db;
\c local_db
```
  3. 在本地实例Alocal_db库中创建Schema。
```
CREATE SCHEMA s02;
```
- 为远程实例B创建库表，用于后续从本地实例A查询远程实例B的库表数据。
  1. 请参考连接实例A的方式，连接远程实例B。
  2. 在远程实例B上创建remote_db库，并切换至remote_db库。
```
CREATE DATABASE remote_db;
\c remote_db
```
  3. 在远程实例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版控制台，找到本地实例A，单击实例ID。
在左侧导航栏，单击外部数据源管理。

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

参数	说明
本地实例Id	自动填写。
本地数据库名称	选择本地实例A的`local_db`数据库。
本地初始账号	如果已创建初始账号，无需填写。
本地初始密码	初始账号对应的密码。
远程实例Id	选择远程实例B的实例ID。
远程数据库名称	选择远程实例B的`remote_db`数据库。
远程数据库账号	远程实例B的初始账号。
远程数据库密码	初始账号对应的密码。
数据源名称	自定义数据源名称。例如：example_name。

单击确定，等待数据源连接状态变为运行中，您可以进行跨实例查询操作。
说明
- 数据源创建后，状态会变为创建同构数据源访问中。该创建过程通常耗时小于一分钟，且对实例的运行、数据读写无影响。
- 数据源创建完成后，您还可以进行以下操作。
  - 编辑数据源：单击操作列的编辑，修改远程实例的账号和密码。
  - 删除数据源：单击操作列的删除，删除数据源。

步骤二：跨实例查询

重要

若访问的远程实例为Serverless模式，当远程实例处于扩缩容状态时，扩缩容期间数据不可访问。

连接本地实例A并切换至local_db库的Schemas02。

在本地实例A的Schemas02中，为远程实例B的表（t1、t2、t3）创建对应的外部表。

重要

本地实例的Schema中不能存在与远程表同名的表，否则，操作将失败。

多表批量导入

语法：

IMPORT FOREIGN SCHEMA remote_schema -- 远程实例的schema名称
    [LIMIT TO ( table_name [, ...] ) ] -- 远程实例的表名称
    FROM SERVER server_name -- 步骤一中配置的数据源名称
    INTO local_schema -- 本地实例的schema名称

示例：

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

说明

通过IMPORT FOREIGN SCHEMA查看更多语法详情。

单表导入

语法：

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ -- 远程实例的表名称
  { column_name data_type } ] -- 远程实例的表结构
    [, ... ] )
  SERVER server_name -- 步骤一配置的数据源名称
[ OPTIONS ( option 'value' [, ... ] ) ] -- 本地实例的schema名称和表名称

示例：

CREATE FOREIGN TABLE s01.t1(a int, b int) 
	SERVER example_name OPTIONS(schema_name 's02', table_name 't1');

说明

通过CREATE FOREIGN TABLE查看更多语法详情。

在本地实例A的local_db库中查询远程实例Bremote_db库中t1表的数据。
说明
云原生数据仓库 AnalyticDB PostgreSQL 版（AnalyticDB for PostgreSQL）不支持通过Database.Schema.Table的方式访问数据。
```
SELECT * FROM s02.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)
```