9.7.3 dblink的使用
9.7.3 dblink的使用
dblink函数可以方便的访问当前集群或其他集群数据库中的数据，
9.7.3.1 安装dblink函数
进入到GP安装目录下
# cd $GPHOME/share/postgresql/contrib
$ psql -d chinadaas -f dblink.sql
chinadaas : 制定的数据库
dblink默认的安装在制定数据库的public下
9.7.3.2 查看安装后的dblink函数
每个函数的含义请参考:
https://www.postgresql.org/docs/9.6/dblink.html
9.7.3.3 使用dblink读取不同数据库中的数据
9.7.3.3.1 在数据库中创建测试表
$ psql -d stagging
psql (8.3.23)
Type "help" for help.
stagging=# CREATE TABLE testdblink (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
stagging=# INSERT INTO testdblink VALUES (1, 'Cheese');
INSERT 0 1
stagging=# INSERT INTO testdblink VALUES (2, 'Fish');
INSERT 0 1
以上是在stagging数据库中创建了testdblink 表
9.7.3.3.2 使用dblink读取数据
以下登录的是chinadaas数据库，在以上中已经在chinadaas安装上了dblink函数
$ psql -d chinadaas
psql (8.3.23)
Type "help" for help.
在本地Greenplum数据库系统上创建一个到stagging数据库的命名连接
chinadaas=# SELECT dblink_connect('mylocalconn', 'dbname=stagging');
dblink_connect
----------------
OK
(1 row)
建立一个到远程数据库系统的连接
chinadaas=# SELECT dblink_connect('host=192.168.31.50 port=5432 dbname=stagging');
dblink_connect
----------------
OK
(1 row)
读取其他数据库中的数据，注意必须as一个表来映射其他数据库中的表的字段，字段类型最好一致
chinadaas=# SELECT * FROM dblink('mylocalconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
id | product
----+---------
2 | Fish
1 | Cheese
(2 rows)
按照制定条件查询数据
chinadaas=# SELECT * FROM dblink('mylocalconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text) where id='3';
id | product
----+---------
3 | sd
3 | sd
(2 rows)
把其他数据库表中的数据保存到本地
保存方式一
chinadaas=# CREATE TABLE testdbllocal (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
chinadaas=# INSERT INTO testdbllocal select * FROM dblink('dbname=stagging', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
INSERT 0 2
保存方式二
chinadaas=# create table testdbllocal2 as select * FROM dblink('dbname=stagging', 'SELECT * FROM testdblink') AS dbltab(id int, product text) DISTRIBUTED BY (id);
SELECT 2
9.7.3.3.3 销毁当前的dblink链接
查看当前的链接
chinadaas=# select dblink_get_connections();
dblink_get_connections
------------------------
{mylocalconn}
(1 row)
销毁当前的链接
chinadaas=# SELECT dblink_disconnect('mylocalconn');
dblink_disconnect
-------------------
OK
(1 row)
9.7.3.4 使用dblink更新数据
更新表示最好现开启事务
-- 先执行dblink_connect保持连接
SELECT dblink_connect('mylocalconn','host=192.168.31.50 dbname=stagging');
-- 执行BEGIN命令
SELECT dblink_exec('mylocalconn', 'BEGIN');
-- 执行数据操作(update，insert，create等命令)
SELECT dblink_exec('mylocalconn', 'insert into testdblink(a,b) values(3,''sd'') ');
-- 执行事务提交
SELECT dblink_exec('mylocalconn', 'COMMIT');
-- 解除连接
SELECT dblink_disconnect('mylocalconn');
9.7.3.5 使用视图查询数据
chinadaas=# create view dblink_view_test as select * from dblink('mylocalconn', 'select * from testdblink') as dbltab(id int, product text) where id='3';
CREATE VIEW
chinadaas=# select * from dblink_view_test;
id | product
----+---------
3 | sd
3 | sd
(2 rows)
9.7.3.6 dblink使用注意事项
9.7.3.6.1 superuser用户访问dblink
superuser用户可以随意访问dblink的dblink_connect与dblink_connect_u链接,例如:
chinadaas=# SELECT dblink_connect('dbname=stagging');
dblink_connect
----------------
OK
(1 row)
chinadaas=# SELECT * FROM dblink('SELECT * FROM testdblink') AS dbltab(id int, product text) where id='3';
id | product
----+---------
3 | sd
3 | sd
(2 rows)
9.7.3.6.2 非superuser用户访问dblink会提示让填写链接信息
chinadaas=> SELECT dblink_connect('dbname=stagging');
ERROR: host is required
DETAIL: Non-superusers must provide a host in the connection string.
链接方式一
chinadaas=> SELECT dblink_connect('mylocalconn','host=192.168.31.50 user=xiaoxu password=gpadmin dbname=stagging');
dblink_connect
----------------
OK
(1 row)
链接方式二
chinadaas=> SELECT dblink_connect_u('mylocalconn','dbname=stagging');
ERROR: permission denied for function dblink_connect_u
需要先给用户赋予函数的执行权限，否则回报以上的错误
chinadaas=> SELECT dblink_connect_u('mylocalconn','dbname=stagging');
dblink_connect_u
------------------
OK
(1 row)
或使用以下链接
chinadaas=> SELECT dblink_connect_u('dbname=stagging');
dblink_connect_u
------------------
OK
(1 row)