注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.1新特性之三:基于文件访问的 SQL/MED  

2011-09-15 13:31:26|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

             SQL/MED((Management of External Data) )早在 PostgreSQL8.4 版本已谈到,但是到 9.1 版本
已经可以创建外部表( foreign tables), 并通过外部表访问外部数据,这也是 SQL/MED 的主要目的之一。
SQL/MED 可以访问多种类型的外部数据,可以是操作系统的文件,也可以是其它类型数据库如 oracle ,mysql
等,关于支持的外部数据类型,详见 http://wiki.postgresql.org/wiki/Foreign_data_wrappers  这篇 blog
主要讨论如何在数据库里直接访问文件系统上的 CSV 格式的数据文件。


--1 在测试库上 copy 表
skytf=# copy ( select * from skytf.activity limit 10000) to '/home/postgres/script/tf/skytf.activity.csv' ;
COPY 10000

  备注:在公司的测试库上取表 skytf.activity 少量数据,注意格式为 csv。
 
--2 传送文件
  将步骤1产生的文件上传到笔记本虚拟机,因为 PostgreSQL9.1 是装在笔记本虚拟机上。 

--3 加载 file_fdw 外部模块
mydb=# create extension file_fdw with schema mydb;
CREATE EXTENSION

mydb=# \dx
                        List of installed extensions
   Name   | Version |   Schema   |                Description               
----------+---------+------------+-------------------------------------------
 file_fdw | 1.0     | mydb       | foreign-data wrapper for flat file access
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
  
    备注:以 postgres 用户连接虚拟机上的库 mydb, 创建外部模块 file_fdw。
   
--4 创建 SERVER (外部服务器)
mydb=# create server ser_file FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

mydb=# \des
          List of foreign servers
   Name   |  Owner   | Foreign-data wrapper
----------+----------+----------------------
 ser_file | postgres | file_fdw
(1 row)

    注:以 postgres 用户连接虚拟机上的库 mydb, 并创建 SERVER;Server 可以理解为外部服务器,
           是本地库访问外部数据的桥梁。      

--5 创建外部表
mydb=# CREATE FOREIGN TABLE fr_activity (
mydb(#     id character varying(32) NOT NULL,
mydb(#     identity_code integer   NOT NULL,
mydb(#     skyid integer NOT NULL,
mydb(#     category character varying(50),
mydb(#     body character varying(500) NOT NULL,
mydb(#     create_time timestamp without time zone
mydb(# ) server ser_file options (filename '/home/postgres/script/tf/skytf.activity.csv', format 'csv') ;
CREATE FOREIGN TABLE

    备注:这里创建外部表,并指定外部 Server 为 ser_file,在测试过程中,发现外部表有很多限制,
              目前发现了以下:
              1 default 值不支持;
              2 索引不支持;
              到了这步,终于完成了外部表创建的所有步骤,接下来验证下是否真的可以访问操作系统文件数据。

--6 数据查询     
mydb=# select * from fr_activity limit 1;
                id                | identity_code |   skyid   |    category    |     body     |       create_time      
----------------------------------+---------------+-----------+----------------+--------------+-------------------------
 000000002a3554d5012a5518bf1f65d7 |    -472428551 | 133257042 | SKY_VISIT_FROM | ${fromSkyid} | 2010-08-09 12:25:39.106
(1 row)     
     
   备注: 果然文件'/home/postgres/script/tf/skytf.activity.csv' 数据可以直接访问了。 

--7 执行计划  
mydb=# explain select count(*) from fr_activity;
                                 QUERY PLAN                                
----------------------------------------------------------------------------
 Aggregate  (cost=6429.76..6429.77 rows=1 width=0)
   ->  Foreign Scan on fr_activity  (cost=0.00..6277.30 rows=60983 width=0)
         Foreign File: /home/postgres/script/tf/skytf.activity.csv
         Foreign File Size: 1463602
(4 rows)  
  
     备注:外部表的PLAN里显示了外部文件的信息,包括文件路径,大小等。
    

附一: CREATE SERVER 语法
CREATE SERVER
Name
CREATE SERVER -- define a new foreign server

Synopsis
CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

Description
CREATE SERVER defines a new foreign server. The user who defines the server becomes its owner.

A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource. Additional user-specific connection information may be specified by means of user mappings.

The server name must be unique within the database.

Creating a server requires USAGE privilege on the foreign-data wrapper being used.


附二:CREATE FOREIGN TABLE 语法
CREATE FOREIGN TABLE
Name
CREATE FOREIGN TABLE -- define a new foreign table

Synopsis
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ NULL | NOT NULL ] }
    [, ... ]
] )
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

Description
CREATE FOREIGN TABLE will create a new foreign table in the current database. The table will be owned by the user issuing the command.

  评论这张
 
阅读(26408)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016