PostgreSql資料庫的神器 FDW

PostgreSql資料庫的神器 FDW
PG的FDW的介紹
https://wiki.postgresql.org/wiki/Foreign_data_wrappers

本篇簡單介紹下PG到Mysql,PG到Hive的“透明網關” ^_^
首先：PG的安裝
安裝PostgreSQL用戶端。
sudo apt-get install postgresql-client
然後：安裝PostgreSQL伺服器。
sudo apt-get install postgresql
正常情況下，安裝完成後，PostgreSQL伺服器會自動在原生5432連接埠開啟
如果還想安裝圖形管理介面，可以運行下面命令
sudo apt-get install pgadmin3
下一步：建立超級許可權使用者
sudo -u postgres createuser --superuser syk
建立資料庫test
sudo -u postgres createdb -O syk test
最後：測試連接PG
psql -U syk -d test -h 127.0.0.1 -p 5432
到此PG的安裝完成。

開始配置FDW
先配置到mysql的
https://github.com/EnterpriseDB/mysql_fdw
下載到本地解壓縮
更改下檔案的許可權
準備安裝：中間需要安裝一個pg的擴充開發包dev-9.3
sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install

登入PG開始配置mysql（前提是mysql的使用者及庫表都已經建立完成）的fdw
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'
);
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'
);
SELECT * FROM syk_test2;
INFO: Successfully connected to MySQL database syk at server 127.0.0.1 via TCP/IP with cipher (server version: 5.5.49-0ubuntu0.14.04.1, protocol version: 10)
goods_id | goods_type | goods_id2
----------+------------+-----------
100 | 10010 | 0
100 | 10011 | 0
88 | 20010 | 0
86 | 20110 | 0
0 | 我我我特色 | 0
1 | 我我我曹操 | 0

下面這些內容基本是占篇幅的^_^
/*
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation #注意這裡
#mysql 庫上的表結構 沒有唯一約束
CREATE TABLE `syk_test2` (
`goods_id` INT (11) NOT NULL DEFAULT '0',
`goods_type` VARCHAR (10) DEFAULT NULL,
`goods_id2` BIGINT (20) NOT NULL DEFAULT '0'
) ENGINE = INNODB DEFAULT CHARSET = utf8;
#添加唯一約束
mysql > ALTER TABLE syk_test2 ADD CONSTRAINT idx_goods_id UNIQUE (goods_id);
ERROR 1142 (42000): ALTER command denied to user 'sky'@'localhost' for table 'syk_test2'
目前使用者還沒有對這個庫執行alter ddl的許可權 root使用者登入賦予許可權
靠沒許可權，賦予下許可權
mysql > GRANT ALTER ON syk.* TO sky@'%';
Query OK, 0 rows affected (0.00 sec)
切換回sky使用者再次添加唯一約束
songyunkui@syk_ubuntu:~$ mysql -usky -p -h127.0.0.1
mysql> use syk;
Database changed
mysql > ALTER TABLE syk_test2 ADD CONSTRAINT idx_goods_id UNIQUE (goods_id);
ERROR 1062 (23000): Duplicate entry '100' for key 'idx_goods_id'
汗：測試資料有不唯一的,刪除下
mysql > DELETE FROM syk_test2 WHERE goods_id = 100 AND goods_type = 10011;
Query OK, 1 row affected (0.06 sec)
再搞
mysql > ALTER TABLE syk_test2 ADD CONSTRAINT uni_goods_id UNIQUE (goods_id);
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
*/

最後在pg上操作Mysql的表 ^_^
test=# delete from syk_test2 where goods_id=0;
test=#
test=# select * from syk_test2;
goods_id | goods_type | goods_id2
----------+------------+-----------
1 | 我我我曹操 | 0
86 | 20110 | 0
88 | 20010 | 0
100 | 10010 | 0
(4 rows)

########## FDW to HIVE
https://github.com/youngwookim/hive-fdw-for-postgresql
$ python setup.py sdist
$ sudo python setup.py install

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

DROP FOREIGN TABLE hive;
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'
);

\! kinit - kt h_b2c_dc_r.keytab h_b2c_dc_r@XXX.HADOOP 這裡有一個kerberos的驗證

SELECT * FROMhive;
DROP FOREIGN TABLE hive_query;

CREATE FOREIGN TABLE hive_query (cat_id INT, cat_name VARCHAR) SERVER multicorn_hive OPTIONS (
HOST 'c3-hadoop-hive01.bj',
PORT '18XXX',
QUERY 'SELECT cat_id,cat_name from b2c_d.xm_config_cat'
);