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)