同构数据源访问

更新时间:

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

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

版本限制

  • 内核版本为v6.6.0.0及以上的AnalyticDB PostgreSQL 6.0实例。

  • 内核版本为v7.0.3.0及以上的AnalyticDB PostgreSQL 7.0实例。

  • 内核版本为v2.1.1.5及以上的AnalyticDB PostgreSQLServerless模式实例。

说明

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

前提条件

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

准备工作

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

  1. 为本地实例A和远程实例B创建初始账号

  2. 将客户端的IP地址或IP地址段分别添加到AnalyticDB for PostgreSQL本地实例A和远程实例B白名单中。

  3. 准备测试数据。

    • 为本地实例A创建数据库和Schema,用于后续创建外部表、访问远程实例B的数据。

      1. 连接本地实例A。

        psql -h gp-bp166cyrtr4p*****-master.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount
        如果您的ECSAnalyticDB 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. 在远程实例Bremote_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版控制台,找到本地实例A,单击实例ID。

  2. 在左侧导航栏,单击外部数据源管理

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

    参数

    说明

    本地实例Id

    自动填写。

    本地数据库名称

    选择本地实例Alocal_db数据库。

    本地初始账号

    如果已创建初始账号,无需填写。

    本地初始密码

    初始账号对应的密码。

    远程实例Id

    选择远程实例B的实例ID。

    远程数据库名称

    选择远程实例Bremote_db数据库。

    远程数据库账号

    远程实例B初始账号

    远程数据库密码

    初始账号对应的密码。

    数据源名称

    自定义数据源名称。例如:example_name。

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

    说明
    • 数据源创建后,状态会变为创建同构数据源访问中。该创建过程通常耗时小于一分钟,且对实例的运行、数据读写无影响。

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

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

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

步骤二:跨实例查询

重要

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

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

  2. 在本地实例ASchemas02中,为远程实例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查看更多语法详情。

  3. 在本地实例Alocal_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)

相关文档

跨库查询:如果您的数据存储在同一实例的不同数据库,可以实现跨库的联合查询。