了解更多Greenplum技术干货,欢迎访问Greenplum中文社区网站
外部表是greenplum的一种数据表,它与普通表不同的地方是:外部表是用来访问存储在greenplum数据库之外的数据。如普通表一样,可使用SQL对外部表进行查询和插入操作。外部表主要用于Greenplum数据的导入及导出。
本文按照以下顺序介绍外部表:外部表创建和使用,外部表读写实现机制,外部数据转换,外部表的查询计划,外部表的事务,使用可读外部表加载数据。
一 外部表创建和使用
Greenplum数据库在创建一个外部表时,需要声明外部数据的LOCATION和FORMAT。LOCATION指定外部数据URL,包含外部数据读写协议;FORMAT指定外部数据格式,如TEXT、CSV等,greenplum会根据指定的格式,实现外部数据和数据库内部tuple的转换。
创建外部表之后,可以与操作普通表一样,对其进行select、insert等操作。外部表分为可读外部表和可写外部表,可读外部表可以执行select 操作,对可写外部表只能执行insert操作,不能对其进行update和delete。
1.1 可读外部表
创建可读外部表时声明READABLE,或者直接使用缺省值。数据源可以是文件,gpfdist 进程(后面介绍),或者可执行程序。例如:
CREATE EXTERNAL TABLE ext\_expenses (name text, date date, amount float4, category text, desc1 text )
LOCATION ('file://filehost/data/international/\*',
'file://filehost/data/regional/\*',
'file://filehost/data/supplement/\*.csv')
FORMAT 'CSV';
上面的例子从多个位置的文件创建一个可读外部表ext_expenses。
-
LOCATION 指定外部数据URL, 数据源地址是’file://filehost/data/international/*’, ‘file://filehost/data/regional/*’, ‘file://filehost/data/supplement/*.csv’ (其中file是外部数据读写协议, filehost是文件所在的机器hostname)。
-
FORMAT指定外部数据格式是csv。
可读外部表创建成功后,可以使用select做查询操作。比如从外部表ext_expenses查询上述外部数据源(文件)中所有amount大于10000的记录:
select \* from ext\_expenses where amount>10000;
1.2 可写外部表
创建可写外部表时需要声明WRITABLE。数据可以写入到gpfdist或者可执行程序,不支持写入本地文件。例:
CREATE WRITABLE EXTERNAL TABLE sales\_out (LIKE sales)
LOCATION ('gpfdist://etl1:8081/sales.out')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
DISTRIBUTED BY (txn\_id);
上面的例子创建了一个输出到gpfdist的可写外部表sales_out。
-
sales是greenplum数据库中的一个普通表,作为外部表sales_out的内部数据源。
-
LOCATION指定外部数据URL,通过gpfdist进程将数据写入sales.out文件。
-
FORMAT指定外部数据格式是text。
可写外部