PostgreSQL 神器 FDW 實戰：構建 MySQL 與 Hive 的透明網關

PostgreSQL 神器 FDW 實戰：構建 MySQL 與 Hive 的透明網關

Foreign Data Wrapper (FDW) 讓 PostgreSQL 能像操作本地表一樣查詢、寫入異構數據源。本文以 Ubuntu 為例，示範 mysql_fdw 連接 MySQL、hive_fdw (Multicorn) 連接 Hive 的完整流程。

環境準備

# 安裝 PostgreSQL 客戶端/服務端/圖形工具
sudo apt-get install postgresql-client postgresql pgadmin3

# 建立超級用戶與測試庫
sudo -u postgres createuser --superuser syk
sudo -u postgres createdb -O syk test
psql -U syk -d test -h 127.0.0.1 -p 5432

案例一：PG ↔ MySQL (mysql_fdw)

1. 編譯安裝擴展（需 postgresql-server-dev-9.3、MySQL 開發包）：

   git clone https://github.com/EnterpriseDB/mysql_fdw
   cd mysql_fdw
   sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install
   

2. PG 內建立擴展與外部伺服器：

   CREATE EXTENSION mysql_fdw;
   CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw 
     OPTIONS (HOST '127.0.0.1', PORT '3306');
   CREATE USER MAPPING FOR syk SERVER mysql_server 
     OPTIONS (username 'sky', password 'sky');
   

3. 映射遠端表（注意：遠端表第一欄必須有唯一約束，否則無法 INSERT/UPDATE/DELETE）：

   CREATE FOREIGN TABLE syk_test2 (
     goods_id INT,
     goods_type VARCHAR(10),
     goods_id2 BIGINT
   ) SERVER mysql_server OPTIONS (dbname 'syk', table_name 'syk_test2');
   

4. 驗證：SELECT * FROM syk_test2; 即可看到 MySQL 數據；DELETE FROM syk_test2 WHERE goods_id=0; 可直接寫入。

案例二：PG ↔ Hive (hive_fdw + Multicorn + Kerberos)

1. 安裝 Multicorn 並部署 hive_fdw：sudo python setup.py install
2. 建立 Multicorn 伺服器與用戶映射：

   CREATE EXTENSION multicorn;
   CREATE SERVER multicorn_hive FOREIGN DATA WRAPPER multicorn 
     OPTIONS (WRAPPER 'hivefdw.HiveForeignDataWrapper');
   CREATE USER MAPPING FOR PUBLIC SERVER multicorn_hive;
   

3. 定義外部表（支援 TABLE 直連或 QUERY 自定義 SQL）：

   CREATE FOREIGN TABLE hive (
     cat_id INT, cat_name VARCHAR, cat_name_en VARCHAR,
     type INT, p_id INT, deep INT
   ) SERVER multicorn_hive OPTIONS (
     HOST 'c3-hadoop-hive01.bj', PORT '18XXX', TABLE 'b2c_d.xm_config_cat'
   );
   

4. Kerberos 認證（查詢前執行）：\! kinit -kt h_b2c_dc_r.keytab h_b2c_dc_r@XXX.HADOOP
5. 查詢：SELECT * FROM hive;

關鍵避坑指南

• MySQL 表必須有主鍵/唯一鍵，否則寫入報錯 first column of remote table must be unique。
• 權限不足時，需 Root 授權 GRANT ALTER ON syk.* TO sky@'%'; 才能加唯一鍵。
• Hive 端若啟用 Kerberos，務必在查詢前 kinit，否則報認證失敗。