postgresql数据库的神器 fdw-凯发app官方网站

pg的fdw的介绍


本篇简单介绍下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的

下载到本地解压缩
更改下文件的权限
准备安装：中间需要安装一个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

$ 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 * from hive;
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'
);