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,'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），转载时请注明作者及出处，并保留本文链接。