全部产品
Search
文档中心

云原生数据仓库AnalyticDB PostgreSQL版:跨库查询

更新时间:May 17, 2024

当需要查询分散在不同数据库中的数据时,您可以使用云原生数据仓库 AnalyticDB PostgreSQL 版的跨库查询功能方便地将数据聚合在一起,进行高效查询,提高数据访问效率。

功能介绍

Database是PostgreSQL生态下数据库的最高级别,同一个实例中的不同Database之间无法直接互相查询数据,在保证了数据库业务隔离的同时,也带来了跨业务跨库之间联合查询的不便。

AnalyticDB PostgreSQL版中,您可以通过外部数据包装器FDW(foreign-data wrapper)进行跨库查询,阿里云提供的FDW基于AnalyticDB PostgreSQL版的MPP架构打造,能够充分发挥计算节点的性能优势,提高跨库查询的数据访问效率。

注意事项

  • 高速跨库查询仅支持以下数据库内核版本:

    • 存储弹性模式7.0版:V7.0.1.x及以上版本。

    • 存储弹性模式6.0版:V6.3.11.2及以上版本。

    • Serverless模式:V1.0.6.x以及上版本。

  • FDW外表仅支持SELECT和INSERT操作,不支持UPDATE和DELETE操作。

  • 仅存储弹性模式7.0版本支持JOIN下推和AGG下推功能。

  • 仅存储弹性模式7.0版本的ORCA优化器支持对FDW外表生成执行计划,存储弹性模式6.0版本和Serverless模式的ORCA优化器无法处理外表,会使用原生优化器生成执行计划。

操作步骤

以下内容将为实例的两个Database(db01和db02)之间开通数据互访。开通后,您可以在db01库访问db02库中的表。

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

  2. 创建db01库和db02库。

    CREATE DATABASE db01;
    CREATE DATABASE db02;
  3. 在db01和db02库创建跨库查询插件greenplum_fdw和gp_parallel_retrieve_cursor。具体操作,请参见安装、升级与卸载插件

  4. 切换到db02库,准备测试数据。

    \c db02
    CREATE SCHEMA s01;
    CREATE TABLE s01.t1(a int, b int, c text); 
    INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');
  5. 切换到db01库,创建SERVER和USER MAPPING。

    • 创建SERVER。

      \c db01
      CREATE SERVER db02 FOREIGN DATA WRAPPER greenplum_fdw
          OPTIONS (host 'localhost',
            			 dbname 'db02');

      参数说明。

      参数

      说明

      host

      跨库访问此处填写'localhost'

      dbname

      源库的名称,此处示例为db02

    • 创建USER MAPPING,更多关于USER MAPPING的介绍,请参见CREATE USER MAPPING

      CREATE USER MAPPING FOR CURRENT_USER SERVER db02
          OPTIONS (user 'report', password '******');

      参数说明。

      参数

      说明

      user

      数据库账号。

      该账号需要拥有db02库的读权限(如需执行INSERT操作,则还需要写权限),如何创建账号,请参见创建数据库账号

      password

      以上账号的密码。

  6. 在db01库实现跨库查询。

    实现跨库查询有如下两种方式:

    • 为源表创建外表。

      CREATE SCHEMA s01;
      CREATE FOREIGN TABLE s01.t1(a int, b int) 
      	server db02 options(schema_name 's01', table_name 't1');

      上述方式的优缺点如下:

      • 优点:可以灵活定制外表的DDL,例如db02库中t1有三个字段a,b,c,但目标库只需要a,b两个字段,因此可以在创建外表的时候指定字段。

      • 缺点:需要知道每一个表的DDL,一次性导入多个外表较为繁琐。

    • 导入源库下Schema中的所有表。

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

      上述方式的优缺点如下:

      • 优点:可以快速导入外表,而且不需要知道每一个表的DDL。

      • 缺点:不够灵活,每个表的名称和源库一致,字段也一致。

      更多介绍,请参见IMPORT FOREIGN SCHEMA

  7. 在db01库查询db02库的数据。

    查询db01库的数据。

    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)

相关文档

AnalyticDB PostgreSQL版还提供了跨实例查询功能,详情请参见跨实例查询