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,'Microsoft YaHei'; Box-sizing: border-Box;">goods_type VARCHAR (10),'Microsoft YaHei'; Box-sizing: border-Box;">goods_id2 BIGINT
) SERVER MysqL_server OPTIONS (
dbname 'syk','Microsoft YaHei'; Box-sizing: border-Box;">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','Microsoft YaHei'; Box-sizing: border-Box;">`goods_type` VARCHAR (10) DEFAULT NULL,'Microsoft YaHei'; Box-sizing: border-Box;">`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
ERROR 1062 (23000): Duplicate entry '100' for key 'idx_goods_id'
汗：测试数据有不唯一的,删除下
MysqL > DELETE FROM syk_test2 WHERE goods_id = 100 AND goods_type = 10011;
再搞
MysqL > ALTER TABLE syk_test2 ADD CONSTRAINT uni_goods_id UNIQUE (goods_id);
Records: 0 Duplicates: 0 Warnings: 0
*/
最后在pg上操作MysqL的表 ^_^
test=# delete from syk_test2 where goods_id=0;
test=#
test=# select * from syk_test2;
(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,'Microsoft YaHei'; Box-sizing: border-Box;">cat_name VARCHAR,'Microsoft YaHei'; Box-sizing: border-Box;">cat_name_en VARCHAR,'Microsoft YaHei'; Box-sizing: border-Box;">type INT,'Microsoft YaHei'; Box-sizing: border-Box;">p_id INT,'Microsoft YaHei'; Box-sizing: border-Box;">deep INT
) SERVER multicorn_hive OPTIONS (
HOST 'c3-hadoop-hive01.bj','Microsoft YaHei'; Box-sizing: border-Box;">PORT '18XXX','Microsoft YaHei'; Box-sizing: border-Box;">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 (
QUERY 'SELECT cat_id,cat_name from b2c_d.xm_config_cat'
);
End.
作者：云开_sky（中国统计网特邀认证作者）
本文为中国统计网原创文章，需要转载请联系中国统计网（小编微信：itongjilove），转载时请注明作者及出处，并保留本文链接。
