fdw pg数据库_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 * 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'

);

End.

作者：云开_sky(中国统计网特邀认证作者)

本文为中国统计网原创文章，需要转载请联系中国统计网(小编微信：itongjilove)，转载时请注明作者及出处，并保留本文链接。返回搜狐，查看更多

责任编辑：