当前位置:数据库 > SQLServer >>

PostgreSQL的外部表使用

PostgreSQL的外部表使用

postgresql从9.1开始增加了外部表访问的功能,这个功能就是数据库直接读取数据库以外的文件,比如csv或者text等类型的文件,暂时不支持DML。postgresql有各种插件能直连各种异构DB,如oracle_fdw,mysql_fdw,file_fdw等,对数据的迁移是很方便的,这是PG的扩展性较强的一个表现。这里介绍foreign data wrapper的file_fdw使用。

一、环境: 
OS :CentOS 6.3 
DB :PostgreSQL 9.3 

二、使用过程 
1.创建扩展,因为默认不安装,安装需要手工创建
[postgres@kenyon ~]$ psql
psql (9.3.0)
Type "help" for help.
postgres=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
postgres=# \dx
  List of installed extensions
  Name  | Version |  Schema  |  Description 
----------+---------+------------+-------------------------------------------
file_fdw | 1.0  | public  | foreign-data wrapper for flat file access
plpgsql  | 1.0  | pg_catalog | PL/pgSQL procedural language
(2 rows)
2.创建外部表服务接口,查看
postgres=# create server pg_file_server foreign data wrapper file_fdw;
CREATE SERVER
postgres=# \des
  List of foreign servers
  Name  |  Owner  | Foreign-data wrapper
----------------+----------+----------------------
pg_file_server | postgres | file_fdw
(1 row)
3.建立一个外部表,与外部文件结构一致
postgres=# create foreign table tab_area(id int,cname varchar(80),ename varchar(80),create_time varchar(30)) server pg_file_server options(filename '/home/postgres/data1.csv',format 'csv',header 'true',delimiter ',',null '1'); 
--后面options里面参数的说明
--filename后面是文件名和绝对路径
--format是格式,csv是逗号分隔,text表示是tab分隔的方式
--delimiter是分隔符
--header表示第一行数据是否需要
--null表示空数据的转化处理,例子中字段1将转化为null
4.上传外部文件
[postgres@kenyon ~]$ more data1.csv
?ID,cname,ename,create_time
1,浙江,zhejiang,2013-01-01
2,杭州,hangzhou,2013-01-02
3,北京,beijing,2012-09-12
4,默认,,2013-09-10
5,四川,sichuan,2012-12-12
6,,nanjing,
7,甘肃,,1998-12-12
5.查询
postgres=# select * from tab_area;
id | cname |  ename  | create_time
----+-------+----------+-------------
  | 浙江  | zhejiang | 2013-01-01
  2 | 杭州  | hangzhou | 2013-01-02
  3 | 北京  | beijing  | 2012-09-12
  4 | 默认  |  | 2013-09-10
  5 | 四川  | sichuan  | 2012-12-12
  6 |  | nanjing  |
  7 | 甘肃  |  | 1998-12-12
(7 rows)
6.查看外部表
postgres=# \d+ tab_area
  Foreign table "public.tab_area"
 Column  |   Type  | Modifiers | FDW Options | Storage  | Stats target | Description 
-------------+-----------------------+-----------+-------------+----------+--------------+-------------
 id  | integer   |   |   | plain  |  | 
 cname   | character varying(80) |   |   | extended |  | 
 ename   | character varying(80) |   |   | extended |  | 
 create_time | character varying(10) |   |   | extended |  | 
Server: pg_file_server
FDW Options: (filename '/home/postgres/data1.csv', format 'csv', delimiter ',', header 'true', "null" '1')
Has OIDs: no
7.与实际普通表的结合
postgres=# create table tab_act_area as select * from tab_area;
SELECT 7
postgres=# insert into tab_act_area select * from tab_area where id>2;
INSERT 0 5
postgres=# \d+
  List of relations
Schema |  Name  |  Type  |  Owner  |  Size  | Description
--------+--------------+---------------+----------+------------+-------------
public | empsalary  | table  | postgres | 16 kB  |
public | tab_act_area | table  | postgres | 8192 bytes |
public | tab_area  | foreign table | postgres | 0 bytes  |
public | test  | table  | postgres | 5096 kB  |
(4 rows)
8.修改外部表
postgres=# alter foreign table tab_area options(set null '^null*');
ALTER FOREIGN TABLE
postgres=# select * from tab_area;
id | cname |  ename  | create_time
----+-------+----------+-------------
  1 | 浙江  | zhejiang | 2013-01-01
  2 | 杭州  | hangzhou | 2013-01-02
  3 | 北京  | beijing  | 2012-09-12
  4 | 默认  |  | 2013-09-10
  5 | 四川  | sichuan  | 2012-12-12
  6 |  | nanjing  |
  7 | 甘肃  |  | 1998-12-12
(7 rows)
9.工具的使用


三总结: 
1.外部表实际不存储数据,只是一张表结构; 
2.9.2版本比9.1版本多了option的选择,9.3又多了collate一些排序的补充,功能进一步完善,现在已有约束、default普通表的应有功能; 
3.实用性,对定期的数据抽取但无需入库检查是很方便的,既减少了数据存储,像基于postgresql的greenplum的数据导入导出就非常实用
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,