同构数据源访问
若您需要访问同阿里云账号下的其他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的数据。
连接本地实例A。
psql -h gp-bp166cyrtr4p*****-master.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount
如果您的ECS与AnalyticDB for PostgreSQL实例不属于同一VPC,或者您在本地机器上连接AnalyticDB for PostgreSQL实例,请使用外网地址。
在本地实例A上创建
local_db
库,并切换至local_db
库。CREATE DATABASE local_db; \c local_db
在本地实例A
local_db
库中创建Schema。CREATE SCHEMA s02;
为远程实例B创建库表,用于后续从本地实例A查询远程实例B的库表数据。
请参考连接实例A的方式,连接远程实例B。
在远程实例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版控制台,找到本地实例A,单击实例ID。
在左侧导航栏,单击外部数据源管理。
在同构数据源访问页签,单击新增数据源,并配置如下参数。
参数
说明
本地实例Id
自动填写。
本地数据库名称
选择本地实例A的
local_db
数据库。本地初始账号
如果已创建初始账号,无需填写。
本地初始密码
初始账号对应的密码。
远程实例Id
选择远程实例B的实例ID。
远程数据库名称
选择远程实例B的
remote_db
数据库。远程数据库账号
远程实例B的初始账号。
远程数据库密码
初始账号对应的密码。
数据源名称
自定义数据源名称。例如:example_name。
单击确定,等待数据源连接状态变为运行中,您可以进行跨实例查询操作。
说明数据源创建后,状态会变为创建同构数据源访问中。该创建过程通常耗时小于一分钟,且对实例的运行、数据读写无影响。
数据源创建完成后,您还可以进行以下操作。
编辑数据源:单击操作列的编辑,修改远程实例的账号和密码。
删除数据源:单击操作列的删除,删除数据源。
步骤二:跨实例查询
若访问的远程实例为Serverless模式,当远程实例处于扩缩容状态时,扩缩容期间数据不可访问。
连接本地实例A并切换至
local_db
库的Schemas02
。在本地实例A的Schema
s02
中,为远程实例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)
相关文档
跨库查询:如果您的数据存储在同一实例的不同数据库,可以实现跨库的联合查询。