PostgreSQL的外部表使用

原文链接：http://my.oschina.net/Kenyon/blog/165432

　　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" @H_502_37@for help.

postgres=# @H_502_37@CREATE EXTENSION file_fdw;
@H_502_37@CREATE EXTENSION postgres=# \dx List @H_502_37@of installed extensions Name | Version | @H_502_37@Schema | Description ----------+---------+------------+---------------------------------------- file_fdw | 1.0 | @H_502_37@public | @H_502_37@foreign-data wrapper @H_502_37@for flat file access plpgsql | 1.0 | pg_catalog | PL/pgsql procedural @H_502_37@language (2 @H_502_37@rows)

2. 创建外部表服务接口，查看

postgres=# create server pg_file_server @H_502_37@foreign data wrapper file_fdw;
CREATE SERVER
postgres=# \des
             List @H_502_37@of @H_502_37@foreign servers
      Name      |  Owner   | Foreign-@H_502_37@data wrapper 
----------------+----------+----------------------
 pg_file_server | postgres | file_fdw
(1 row)

3. 建立一个外部表，与外部文件结构一致

postgres=# @H_502_37@create @H_502_37@foreign @H_502_37@table tab_area( id @H_502_37@int,cname @H_502_37@varchar(80),create_time @H_502_37@varchar(30) ) server pg_file_server options(filename '/home/postgres/data1.csv',format 'csv',header 'true',delimiter ',',@H_502_37@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',"null" '1')
Has OIDs: no

7.与实际普通表的结合

postgres=# @H_502_37@create @H_502_37@table tab_act_area @H_502_37@as @H_502_37@select * @H_502_37@from tab_area;
@H_502_37@SELECT 7 postgres=# @H_502_37@insert @H_502_37@into tab_act_area @H_502_37@select * @H_502_37@from tab_area @H_502_37@where id>2;
@H_502_37@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=# @H_502_37@alter @H_502_37@foreign @H_502_37@table tab_area options(@H_502_37@set @H_502_37@null '^null*');
@H_502_37@ALTER @H_502_37@FOREIGN @H_502_37@TABLE postgres=# @H_502_37@select * @H_502_37@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)

其他语法和普通表类似，除了option的三个选项(drop、add和set),具体参考:
http://www.postgresql.org/docs/9.3/static/sql-alterforeigntable.html

9.工具的使用

三、总结：
1. 外部表实际不存储数据，只是一张表结构;
2. 9.2版本比9.1版本多了option的选择,9.3又多了collate一些排序的补充，功能进一步完善，现在已有约束、default普通表的应有功能;
3. 实用性，对定期的数据抽取但无需入库检查是很方便的，既减少了数据存储，像基于postgresql的greenplum的数据导入导出就非常实用

原文链接：https://www.f2er.com/postgresql/195242.html

PostgreSQL的外部表使用

猜你在找的Postgre SQL相关文章