| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
jopen
11年前发布

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 on,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)
其他语法和普通表类似,除了option的三个选项(drop、add和set) ,具体参考:

http://www.postgresql.org/docs/9.3/static/sql-alterforeigntable.html

9.工具的使用
PostgreSQL的外部表使用


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

 本文由用户 jopen 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1380594167635.html
PostgreSQL 数据库服务器