装载 创建外部表 =# CREATE EXTERNAL WEB TABLE ext_expenses (name text,
date date, amount float4, category text, description text)
LOCATION ( 'http://intranet.company.com/expenses/sales/file.csv',
'http://intranet.company.com/expenses/exec/file.csv',
'http://intranet.company.com/expenses/finance/file.csv',
'http://intranet.company.com/expenses/ops/file.csv',
'http://intranet.company.com/expenses/marketing/file.csv',
'http://intranet.company.com/expenses/eng/file.csv' )
FORMAT 'CSV' ( HEADER );
装载外部表数据 =# INSERT INTO expenses_travel
SELECT * from ext_expenses where category='travel';
或者想要快速装载全部数据到一个新的 数据库表中：
=# CREATE TABLE expenses AS SELECT * from ext_expenses;
测试： [root@mdw ~]# wget http://mirrors.aliyun.com/repo/Centos-6.repo
--2014-03-04 13:51:30-- http://mirrors.aliyun.com/repo/Centos-6.repo
正在解析主机 mirrors.aliyun.com... 115.28.122.210, 112.124.140.210
正在连接 mirrors.aliyun.com|115.28.122.210|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：2086 (2.0K) [application/octet-stream]
正在保存至: “Centos-6.repo”
100%[==============================================================================================================================>] 2,086 --.-K/s in 0s
2014-03-04 13:51:30 (194 MB/s) - 已保存 “Centos-6.repo” [2086/2086])
libo=# CREATE EXTERNAL WEB TABLE ext_expenses (name text)
libo-# location ('http://mirrors.aliyun.com/repo/Centos-6.repo')
libo-# FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
CREATE EXTERNAL TABLE libo=# CREATE TABLE expenses AS SELECT * from ext_expenses;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'colum' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ERROR: could not translate host name "mirrors.aliyun.com", port "80" to address: Temporary failure in name resolution (cdbutil.c:754) (seg0 slice1 sdw1:40000 pid=26261) (cdbdisp.c:1489)
libo=#
libo=# SELECT * from ext_expenses;
ERROR: could not translate host name "mirrors.aliyun.com", port "80" to address: Temporary failure in name resolution (cdbutil.c:754) (seg0 slice1 sdw1:40000 pid=26254) (cdbdisp.c:1489)
libo=# drop EXTERNAL WEB TABLE ext_expenses ;
DROP EXTERNAL TABLE
libo=# CREATE EXTERNAL WEB TABLE ext_expenses (colum text)
libo-# location ('http://115.28.122.210/repo/Centos-6.repo')
libo-# FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
CREATE EXTERNAL TABLE
libo=# select * from ext_expenses;
ERROR: connection with gpfdist failed for http://115.28.122.210/repo/Centos-6.repo. effective url: http://115.28.122.210/repo/Centos-6.repo. (seg0 slice1 sdw1:40000 pid=26296)
libo=#
[gpadmin@mdw data_tst]$ gpfdist -d /home/gpadmin/data_tst -p 8081 -l /home/gpadmin/log1 &
[1] 10321
[gpadmin@mdw data_tst]$ Serving HTTP on port 8081, directory /home/gpadmin/data_tst
[root@mdw ~]# wget http://192.168.100.101:8081/aaa
--2014-03-04 14:14:01-- http://192.168.100.101:8081/aaa
正在连接 192.168.100.101:8081... 已连接。
已发出 HTTP 请求，正在等待回应... 200 ok
长度：未指定 [text/plain]
正在保存至: “aaa”
[ <=> ] 17 --.-K/s in 0s
2014-03-04 14:14:01 (1.61 MB/s) - “aaa” 已保存 [17]
libo=# CREATE EXTERNAL WEB TABLE ext_expenses (colum text)
libo-# location (