怎麼實現跨資料庫查詢 - AnalyticDB

當需要查詢分散在不同資料庫中的資料時，您可以使用AnalyticDB for 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庫中的表。

使用psql串連執行個體，操作步驟，請參見用戶端串連。

建立db01庫和db02庫。

CREATE DATABASE db01;
CREATE DATABASE db02;

在db01和db02庫建立跨庫查詢外掛程式greenplum_fdw和gp_parallel_retrieve_cursor。具體操作，請參見安裝、升級與卸載外掛程式。

切換到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');

切換到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

以上帳號的密碼。

在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。

在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：跨庫查詢

功能介紹

注意事項

操作步驟

相關文檔

參數	說明
host	跨庫訪問此處填寫`'localhost'`。
dbname	源庫的名稱，此處樣本為`db02`。