DWS可以使用协同分析外表实现跨库查询，下面通过一个用例详细展示具体实现

1) 确认远端库的表和权限

在库ora_utf8中创建表 & 插入数据 & 并把表的读权限赋给用dfm

ora_utf8=# CREATE TABLE public.x(a int) DISTRIBUTE BY ROUNDROBIN;
CREATE TABLE
ora_utf8=# INSERT INTO public.x SELECT * FROM generate_series(1,100);
INSERT 0 100
ora_utf8=# GRANT SELECT ON public.x TO dfm;
GRANT

2）在查询发起的database中创建协同分析服务

假如查询发起端的database为postgres，则在postgres库中执行如下语句

postgres=# CREATE SERVER remote_db FOREIGN DATA WRAPPER GC_FDW OPTIONS (
postgres(#     address '127.0.0.1:13100',
postgres(#     dbname 'ora_utf8',
postgres(#     username 'dfm',
postgres(#     password 'Gauss@123'
postgres(# );
CREATE SERVER

其中address的“:”前必须为127.0.0.1， 13100为CN的端口号，需要根据实际情况更新，可以通过如下查询语句获取SELECT distinct node_port FROM pgxc_node WHERE node_type = 'C'

dbname为远端表所在的database

username 为远端表所在的database执行查询的时候用户

password 为远端表所在的database执行查询的用户的密码

3）创建协同分析外表

CREATE FOREIGN TABLE x_cp(
    a int
)
SERVER remote_db
OPTIONS(
    schema_name 'public',
    table_name 'x',
    encoding 'UTF8'
);

x_cp为外表的名字。 x_cp 和 关键字SERVER之间的列定义要和源表的列数据类型定义对应

OPTIONS后面属性定义了源表的名称以及源库的encoding

4）执行协同外表查询

直接查询外表可以获取源端数据，可以查询外表查询可以下推

postgres=# SELECT * FROM x_cp ORDER BY a LIMIT 10;
 a
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)
postgres=# EXPLAIN VERBOSE SELECT * FROM x_cp ORDER BY a LIMIT 10;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
  id |                  operation                  | E-rows | E-distinct | E-memory | E-width | E-costs
 ----+---------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Limit                                   |     10 |            |          |       4 | 144.13
   2 |    ->  Streaming (type: GATHER)             |     20 |            |          |       4 | 146.83
   3 |       ->  Limit                             |     20 |            | 1MB      |       4 | 140.83
   4 |          ->  Sort                           |   1000 |            | 16MB     |       4 | 142.05
   5 |             ->  Foreign Scan on public.x_cp |   1000 |            | 1MB      |       4 | 130.00

          Targetlist Information (identified by plan id)
 -----------------------------------------------------------------
   1 --Limit
         Output: a
   2 --Streaming (type: GATHER)
         Output: a
         Merge Sort Key: x_cp.a
         Node/s: All datanodes (group_version1, bucket:16384)
   3 --Limit
         Output: a
   4 --Sort
         Output: a
         Sort Key: x_cp.a
   5 --Foreign Scan on public.x_cp
         Output: a
         Remote SQL: SELECT a FROM public.x
         Verify SQL: SELECT COUNT(*) FROM (SELECT a FROM public.x)

              ====== Query Summary =====
 -----------------------------------------------------
 System available mem: 2990080KB
 Query Max mem: 2990080KB
 Query estimated mem: 1026KB
 Parser runtime: 0.032 ms
 Planner runtime: 90.033 ms
 Unique SQL Id: 3050404843
 Unique SQL Hash: sql_b2b5646ffdddd497fcc9d2e38e22c065
(35 rows)