同构数据源访问

若您需要访问同阿里云账号下其他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库中的表,并利用计算节点之间高速互联的特性,轻松实现联合查询。

步骤一:准备测试数据

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

  2. 分别在本地实例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
  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');

步骤二:新增数据源

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

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

    参数

    说明

    本地实例Id

    本地实例A的ID。

    本地数据库名称

    本地实例A的数据库名称local_db

    本地初始账号

    本地实例A的账号。

    本地初始密码

    账号对应的密码。

    远程实例Id

    远程实例B的ID。

    远程数据库名称

    远程实例B的数据库名称remote_db

    远程数据库账号

    远程实例B的账号。

    该账号需要拥有remote_db库的读权限(如需执行INSERT操作,则还需要写权限)。

    远程数据库密码

    账号对应的密码。

    数据源名称

    可选参数,自定义数据源名称。

  6. 单击确定,等待数据源连接状态变为运行中,您可以进行跨实例查询操作。

    说明

    数据源创建完成后,您还可以进行以下操作。

    • 编辑数据源:单击操作列的编辑,修改远程实例的账号和密码。

    • 删除数据源:单击操作列的删除,删除数据源。

步骤三:跨实例查询

  1. 在本地实例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,一次性导入多个外表较为繁琐。

  2. 在实例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)

相关文档

您也可以通过greenplum_fdw和gp_parallel_retrieve_cursor插件实现跨实例查询,详情请参见跨实例查询