4.3 数据表的高级应用_高效使用Greenplum：入门、进阶与数据中台-QQ阅读中文轻小说网

上QQ阅读APP看书，第一时间看更新

4.3　数据表的高级应用

前文只是简单地介绍了数据库表的创建，作为数据库数据存储的核心，表还有很多扩展属性，例如分布键、压缩参数、分区等。参照CSDN博主DataFlow范式的文章《Greenplum或DeepGreen数据库对象的使用和管理》和mavs41的《Greenplum中定义数据库对象之创建与管理表》，本节我们一起深入认识Greenplum表的高级属性。

执行CREATETABLE命令，如下所示。

Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (  -->指定表类型：全局|本地临时
[ { column_name data_type [ DEFAULT default_expr ]     [column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]                        -->指定表编码
]
   | table_constraint                                            -->指定表约束
   | LIKE other_table [{INCLUDING | EXCLUDING}
                      {DEFAULTS | CONSTRAINTS}] ...}
   [, ... ] ]
   [column_reference_storage_directive [, . ]
   )
   [ INHERITS ( parent_table [, ... ] ) ]                         -->指定表继承关系
   [ WITH ( storage_parameter=value [, ... ] )                    -->指定存储空间
   [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
   [ TABLESPACE tablespace ]                                      -->指定表空间
   [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]    -->指定分布列
   [ PARTITION BY partition_type (column)                          -->指定分区列
       [ SUBPARTITION BY partition_type (column) ]                 -->指定子分区列
          [ SUBPARTITION TEMPLATE ( template_spec ) ]
       [...]
    ( partition_spec )
        | [ SUBPARTITION BY partition_type (column) ]
          [...]
    ( partition_spec
      [ ( subpartition_spec
           [(...)]
         ) ]
    )
 
where storage_parameter is:                              -->指定创建表存在的参数：
   APPENDONLY={TRUE|FALSE}                                 -->指定是否可以只追加文件
   BLOCKSIZE={8192-2097152}                                 -->指定表块大小
   ORIENTATION={COLUMN|ROW}                                 -->指定表旋转方式
   COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}                -->指定表的压缩方式
   COMPRESSLEVEL={0-9}                                      -->指定表的压缩级别
   FILLFACTOR={10-100}                                      -->指定表的占空因数
   OIDS[=TRUE|FALSE]                                        -->指定表的对象标识符
 
where column_constraint is:                              -->指定列约束如下：
   [CONSTRAINT constraint_name]                             -->约束名称
   NOT NULL | NULL                                          -->是否为空
   | UNIQUE [USING INDEX TABLESPACE tablespace]             -->唯一[使用索引表空间]
            [WITH ( FILLFACTOR = value )]
   | PRIMARY KEY [USING INDEX TABLESPACE tablespace]        -->主键
                 [WITH ( FILLFACTOR = value )]
   | CHECK ( expression )                                   -->其他表达式约束
 
and table_constraint is:                                   -->指定表约束如下：
   [CONSTRAINT constraint_name]                               -->指定表约束名称
   UNIQUE ( column_name [, ... ] )                             -->指定唯一的列名等
          [USING INDEX TABLESPACE tablespace]            -->唯一[使用索引表空间]
          [WITH ( FILLFACTOR=value )]
   | PRIMARY KEY ( column_name [, ... ] )                -->主键
                 [USING INDEX TABLESPACE tablespace]
                 [WITH ( FILLFACTOR=value )]
   | CHECK ( expression )                                -->其他表达式约束
 
where partition_type is:                                 -->指定分区类型：LIST|RANGE
    LIST
  | RANGE
 
where partition_specification is:                        -->指定分区说明：包含分区元素
partition_element [, ...]
 
and partition_element is:                                -->指定分区元素说明：
   DEFAULT PARTITION name                                   -->默认分区名称
  | [PARTITION name] VALUES (list_value [,...] )
  | [PARTITION name]
     START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
     [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
     [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
  | [PARTITION name]
     END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
     [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[column_reference_storage_directive [, ...] ]
 
[ TABLESPACE tablespace ]
 
where subpartition_spec or template_spec is:       -->指定子分区说明或者模板分区说明
subpartition_element [, ...]
and subpartition_element is:
   DEFAULT SUBPARTITION name
  | [SUBPARTITION name] VALUES (list_value [,...] )
  | [SUBPARTITION name]
     START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
     [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
     [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
  | [SUBPARTITION name]
     END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
     [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[column_reference_storage_directive [, ...] ]
[ TABLESPACE tablespace ]
 
where storage_directive is:                               -->指定存储策略
   COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE}
 | COMPRESSLEVEL={0-9}
 | BLOCKSIZE={8192-2097152}
 
Where column_reference_storage_directive is:              -->指定列参考存储策略
 
   COLUMN column_name ENCODING ( storage_directive [, ... ] ), ...
 | DEFAULT COLUMN ENCODING ( storage_directive [, ... ] )

总的来说，创建一张表，需要考虑如下因素。

1. 选择字段的数据类型

字段的数据类型决定了其可以储存什么类型的数据值。通常我们都希望用最小的空间储存尽可能多的数据，具体来说，选择字段的数据类型有以下几个原则。

1）对于字符串，在多数情况下，应该选择使用TEXT或者varchar类型，而不是char类型。

2）对于numeric类型的数据来说，应该尽量选择更小的数据类型来适应数据。比如，选择bigint类型来存储smallint类型范围内的数值，会造成存储空间的大量浪费。

3）对于打算用来做表关联的字段来说，应该考虑选择相同的数据类型。

2. 设置表和字段的约束

表的约束主要有检查约束、非空约束、唯一约束、主键约束4种，具体语句如下。

#检查约束
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price 
    > 0) );
#非空约束
CREATE TABLE  products (product_no integer NOT NULL, name text NOT NULL, price 
    numeric );
#唯一约束
CREATE TABLE products (product_no integer UNIQUE, name text, price numeric)
    DISTRIBUTED BY (product_no);
#主键约束
CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric) 
    DISTRIBUTED BY (product_no);

注意

主键约束是唯一约束的一种特殊情况。在Greenplum数据中使用唯一约束存在强制条件，即表必须是哈希分布的（不能是DISTRIBUTED RANDOMLY随机分布的），并且唯一约束的字段集合必须完整包含所有的分布键字段。

3. 声明分布键

在创建表时有一个子句用于指明分布策略。如果在创建表时没有指明DISTRIBUTED BY或者DISTRIBUTED RANDOMLY子句，Greenplum数据库会依次考虑使用主键（如果该表存在主键）或者第一个字段作为哈希分布的分布键。

CREATE TABLE products (name varchar(40), prod_id integer, supplier_id integer)
    DISTRIBUTED BY (prod_id);

DISTRIBUTED RANDOMLY表是随机均匀分布的，数据会平均分布到各个节点上。对于任何查询都需要全部扫描的大表，DISTRIBUTED RANDOMLY是一种很合适的分布方式，可以保证数据分布绝对均匀。

CREATE TABLE random_stuff (things text, doodads text, etc text) DISTRIBUTED RANDOMLY;

4.3.1　数据表的存储特性

创建Greenplum数据库表对象，除了要考虑字段类型、约束和分布键，还需要考虑表的存储特性。

1. 堆存储和只追加存储

存储模式分为堆存储（Heap Storage）和只追加存储（Append-Optimized Storage）。

堆存储指的是默认情况下Greenplum数据库使用与PostgreSQL相同的存储模式。堆存储模式在OLTP类型工作负载的数据库中很常用，一般用于数据在初始装载后经常变化的场景。更新和删除操作需要对ROW级别做版本控制，以确保数据库事务处理的可靠性。堆存储更适合一些小表，比如维表，这种表可能在初始化装载后会经常更新数据。

需要经常进行更新、删除、单行插入操作或者并行更新、删除和插入操作的表，都适合堆存储。

行存堆表是默认的存储模式，如下所示。

CREATE TABLE test (id int, name text) DISTRIBUTED BY (id);

Greenplum数据库还提供了一种存储模式叫作只追加存储。只追加存储模式适合数据仓库中非规范化的事实表，这些表通常都是系统中最大的表。只追加存储模式实现了更精简和优化的页面存储结构。该存储模式强化了批量数据装载的性能，不推荐一行一行地使用插入语句来装载数据。

当前版本的只追加存储模式支持删除和更新操作，适合需要进行初始数据导入、批量插入操作和不频繁更新的表。虽然也支持并行的批量插入操作，但是不能执行并行的批量更新或删除操作。原因是AO表进行更新或删除操作后的row操作占用的空间不能有效地回收和重用。

创建只追加表的语句如下。

CREATE TABLE test (id int, name text) WITH (appendonly=true);

2. 行存储和列存储

堆存储和只追加存储是规定数据的物理存储方式，而行存储（Row-based Storage）和列存储（Column-based Storage）则限定了数据的逻辑存储方式。使用列存储的表必须是只追加储存表。在执行CREATE TABLE命令时，使用WITH子句来指明表的存储模式。如果没有指明，该表默认为行存堆表。选择行存储的情况如下。

1）表数据的更新。如果表在装载完之后一定有更新操作，那么选择行存储。

2）经常进行插入操作。如果经常插入数据，可以选择行存储。列存储对于写操作不是最优的，因为每条数据都需要被写到磁盘的多个位置（列存表的每列数据存储于不同的磁盘文件中，而行存表是将所有数据存储在同一个磁盘文件中）。

3）查询列数量。如果在SELECT或者WHERE子句中涉及表的全部或多数列，则考虑选择行存储。行存储适合在WHERE或HAVING子句中对单列做聚合操作。

SELECT SUM(salary)...
SELECT AVG(salary)... WHERE salary > 10000

或者在WHERE子句中使用单个列条件返回相对少量的行。

SELECT salary, dept ... WHERE state='CA'

选择列存储的情况如下。

列存储对读操作进行优化，对写操作没有优化，同一行不同列的数据被放在磁盘的不同位置。列存储模式的表在只访问部分列的查询操作中会表现出更好的性能。同时，列存表的每列都存储相同格式的数据值，压缩效率高，占用磁盘空间少，减少磁盘I/O。

CREATE TABLE test (id int, name text) WITH (appendonly=true, orientation=column)
    DISTRIBUTED BY (id);

3. 存储参数（只有AO表可以支持压缩参数）

在CREATE TABLE、ALTER TABLE和CREATE TYPE命令中包含对字段设置压缩类型、压缩级别和块尺寸（Block Size）的选项，这些参数统称为存储参数。存储参数只能用于行存储和列存储的AO表。AO表有两种库内压缩方式可选—表级压缩和列级压缩。前者应用于整个表，后者应用于指定的列。

使用库内压缩要求Datanode操作系统具备强劲的CPU来压缩和解压缩数据。如果Segment数据目录是压缩文件系统，则不建议在数据库内部重复使用压缩存储。

压缩算法有很多，较为通用的有zlib、QuickLZ、LZO、LZ4、Zstandard。前两者已经原生内嵌在Greenplum数据库系统中（因版权问题，QuickLZ在最新的开源版本中已被移除），可直接调用接口使用，而zlib的实际使用效果并不理想。LZO和LZ4凭借快速压缩解压的特点，广泛应用于Hive、Spark、Lucene等框架中，只是压缩率逊于zlib。Facebook在LZ4压缩算法的基础上发布并开源了Zstandard（简称Zstd），在资源占用和压缩效果方面都优于zlib。Greenplum从6.0版开始集成Zstandard算法，也是目前最推荐的算法。

表4-11、表4-12是博客园网友ArthurQin给出两组Zstd和LZ4的Benchmarks比较。

表4-11　对比组A实验结果

表4-12　对比组B实验结果

通过比较发现，在常见的压缩算法中，Zstd的压缩率最高，LZ4的压缩和解压缩时间最短。通过性能分析我们知道了在Greenplum数据库中数据落盘对性能的影响最大，我们优先选用Zstd算法。

创建压缩表语句如下。

CREATE TABLE foo (a int, b text) WITH (appendonly=true, compresstype=zstd, 
    compresslevel=5);

一般情况下，通过下面两个语句可以检查AO表的压缩与分布情况。

#查询并展示AO表的分布情况，每个Segment实例对应的行数量 
SELECT get_ao_distribution(name);
#计算AO表的压缩率，即非压缩存储空间/压缩后存储空间的大小
SELECT get_ao_compression_ratio(name);

AO表压缩存储有3个可选存储参数，具体取值范围和对应解释如表4-13所示。

表4-13　AO表存储参数取值范围

注意

blocksize的默认值为32768，即32KB，推荐设置为65535，即64KB。指定大的块尺寸可能会消耗大量的内存。块大小决定了存储层中的缓存，在面向列的表中，Greenplum为每个分区的每个列维护了一个缓存。具有许多分区或列的表将占用大量内存。

压缩表建表示例如下。

CREATE TABLE T2 (
    c1 int ENCODING (compresstype=zlib,compresslevel=5),
    c2 char ENCODING (compresstype=quicklz, blocksize=65536),
    c3 varchar ENCODING (compresstype=zstd,compresslevel=10, blocksize=65536),
    C4 char, COLUMN c3 ENCODING (RLE_TYPE) )
WITH (appendonly=true, orientation=column);

4.3.2　分区表详解

分区表用于解决数据量特别大的表的查询和更新问题，比如业务事实表，解决办法就是将表分成很多小且更容易管理的部分。

在创建表时，使用PARTITION BY子句以及可选的SUBPARTITION BY子句进行分区。在Greenplum数据库中对一张表做分区，实际上是创建了一张顶层（父级）表和多个底层（子级）表。Greenplum数据库在顶层表与底层表之间创建了继承关系，类似于PostgreSQL中的继承功能。

Greenplum数据库支持范围分区（根据数值型的范围分割数据，比如日期或价格）和列表分区（根据值列表分区，比如区域或生产线），也可以将两种类型结合使用。

表分区本身不会改变数据在Segment实例上的分布，数据分布依然取决于分布键字段。决定表是否分区的因素：表是否足够大；对目前的性能是否满意；查询条件是否匹配分区条件；数据仓库是否需要滚动历史数据；按照某个规则数据是否可以被均匀地分拆。

下面分几种情况详细介绍分区规则。

1）定义日期范围分区表。日期范围分区表使用单个date字段或者timestamp字段作为分区键，可以通过START值、END值和EVERY子句定义分区增量，让Greenplum数据库自动产生分区。默认情况下，START值总是被包含而END值总是被排除。

CREATE TABLE sales (id int, date date, amt decimal(10,2)) 
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2018-01-01') INCLUSIVE
END (date '2019-01-01') EXCLUSIVE
EVERY (INTERVAL '1 day') );

也可以为每个分区单独指定名称，如下所示。

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
(   PARTITION Jan08 START (date '2018-01-01') INCLUSIVE ,
    PARTITION Feb08 START (date '2018-02-01') INCLUSIVE ,
    PARTITION Mar08 START (date '2018-03-01') INCLUSIVE ,
    PARTITION Apr08 START (date '2018-04-01') INCLUSIVE ,
    PARTITION May08 START (date '2018-05-01') INCLUSIVE ,
    PARTITION Jun08 START (date '2018-06-01') INCLUSIVE ,
    PARTITION Jul08 START (date '2018-07-01') INCLUSIVE ,
    PARTITION Aug08 START (date '2018-08-01') INCLUSIVE ,
    PARTITION Sep08 START (date '2018-09-01') INCLUSIVE ,
    PARTITION Oct08 START (date '2018-10-01') INCLUSIVE ,
    PARTITION Nov08 START (date '2018-11-01') INCLUSIVE ,
    PARTITION Dec08 START (date '2018-12-01') INCLUSIVE END (date '2019-01-01') 
        EXCLUSIVE
);

上述分区范围是连续的，如果不希望连续，就需要指定END值。

2）定义数字范围分区表。数字范围分区表使用单个数字列作为分区键，示例如下。

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2011) END (2020) EVERY (1),
  DEFAULT PARTITION extra 
);

3）定义列表分区表。列表分区表可以使用任何数据类型的列作为分区键，分区规则使用等值进行比较。列表分区可以使用多个列（组合起来）作为分区键，而范围分区只允许使用单独列作为分区键。对于列表分区，必须为每个分区指定相应的值。

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int )
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
PARTITION boys VALUES ('M'),
DEFAULT PARTITION other );

4）定义多级分区表。使用SUBPARTITION TEMPLATE命令确保每个分区具有相同的子分区结构，尤其是那些后增加的分区。

CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE
  ( 
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe'),
    DEFAULT SUBPARTITION other_regions
  )
( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE
  EVERY (INTERVAL '1 month'), 
  DEFAULT PARTITION outlying_dates 
);

下面是一个3级分区表的例子，表sales被分为年、月、区域。通过SUBPARTITION TEMPLATE子句确保每个年分区有相同的子分区结构。另外，每个级别的分区都有一个默认分区，代码如下。

CREATE TABLE sales (id int, year int, month int, day int, region text) DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
SUBPARTITION BY RANGE (month)
  SUBPARTITION TEMPLATE 
  (
    START (1) END (13) EVERY (1),
    DEFAULT SUBPARTITION other_months 
  )
SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE 
  (
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION asia VALUES ('asia'),
    DEFAULT SUBPARTITION other_regions 
  )
( START (2002) END (2010) EVERY (1), 
  DEFAULT PARTITION outlying_years 
);

对已经创建的表是不能进行分区的，只能在创建表的时候分区。要想对现有的表做分区，只能重新创建一个分区表，然后重新装载数据到新的分区表中，删掉旧表后把新的分区表改为旧表的名称，还必须重新对表进行授权。

主键和唯一约束必须包含表上的所有分区键，虽然唯一索引可以不包含分区键，但是只对一个分区强制有效，并不是对整个分区表都有效。

一旦创建了分区表，顶层表总是空的。数据值储存在底层表中。在多级分区表中，仅在层级最低的子分区中有数据。在运行期间，查询规划器会扫描整个表的层级结构并使用CHECK子句约束适配查询条件来决定哪些子表需要被扫描。默认分区（只要该层级中存在）总是会被扫描的。如果默认分区中包含数据，就会延长整体的扫表时间。

如果有必要，可以直接把数据装载到子表中，也可以先创建一个中间表用于装载数据，然后与分区表进行分区交换。这种分区交换的性能高于直接复制和插入数据。

--通过pg_partitions视图查看分区表的设计情况
SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, 
    partitionrank
FROM pg_partitions WHERE tablename='sales2';

必须使用ALTER TABLE命令从顶层表开始维护分区。最常见的场景是根据日期范围维护数据时，删除旧的分区并添加一个新的分区，或者把旧的分区交换为压缩AO表以节省空间。若在父表中存在默认分区，添加分区的操作只能是从默认分区拆分出一个新的分区。

1）添加新分区。如果在创建表时使用了SUBPARTITION TEMPLATE子句，那么新增的分区将根据该模板创建子分区，如下所示。

ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END 
    (date '2009-03-01') EXCLUSIVE;

如果在创建表时没有使用SUBPARTITION TEMPLATE子句，那么在新增分区时需要定义子分区。

ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END 
    (date '2009-03-01') EXCLUSIVE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION 
    asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe') );

子表的名称虽然不能通过直接执行ALTER表名来创建，但是修改顶层表的名称，是会影响所有相关分区表的。

--添加默认分区
ALTER TABLE sales ADD DEFAULT PARTITION other;
--如果是多级分区表，同一层次中的每个分区都需要一个默认分区
ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other;
ALTER TABLE sales ALTER PARTITION FOR (RANK(2)) ADD DEFAULT PARTITION other;
ALTER TABLE sales ALTER PARTITION FOR (RANK(3)) ADD DEFAULT PARTITION other;

2）删除分区的语句如下。

ALTER TABLE sales DROP PARTITION FOR (RANK(1));

注意

在将RANK(1)的分区删除后，其余分区的PARTITION RANK值仍然从1开始按照分区字段的值由小到大开始排序。

3）清空分区数据。在清空一个包含子分区的分区时，所有相关子分区的数据都自动被清空。

ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

4）交换分区。交换分区是指将分区和另外一个分区或者表交换命名。

CREATE TABLE jan08 (LIKE sales) WITH (appendonly=true);
INSERT INTO jan08 SELECT * FROM sales_1_prt_1 ;
ALTER TABLE sales EXCHANGE PARTITION FOR (DATE '2008-01-01') WITH TABLE jan08

5）拆分分区。拆分分区是将现有的一个分区分成两个分区。使用ALTER TABLE命令来拆分分区，只能拆分最低层级的分区表（只有包含数据的分区可以拆分）。指定的分割值对应的数据将进入后面一个分区（就是START值为INCLUSIVE的情况）。

ALTER TABLE sales SPLIT PARTITION FOR ('2008-01-01')
AT ('2008-01-16') INTO (PARTITION jan081to15, PARTITION jan0816to31);

如果分区表有默认分区，要添加新的分区只能从默认分区开始拆分，而且只能拆分最低层级分区的默认分区。在使用INTO子句时，第2个分区名称必须是已经存在的默认分区。

ALTER TABLE sales SPLIT DEFAULT PARTITION
START ('2009-01-01') INCLUSIVE
END ('2009-02-01') EXCLUSIVE
INTO (PARTITION jan09, default partition);

6）修改子分区模版。使用ALTER TABLE SET SUBPARTITION TEMPLATE命令来修改现有分区表的子分区模板。修改子分区模板之后添加的分区，其子分区将按照新的模板产生，已经存在的分区不会被修改。

ALTER TABLE sales SET SUBPARTITION TEMPLATE
(   SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION asia VALUES ('asia'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION africa VALUES ('africa')
    DEFAULT SUBPARTITION other 
);
ALTER TABLE sales ADD PARTITION sales_prt_3
START ('2009-03-01') INCLUSIVE END ('2009-04-01') EXCLUSIVE;

4.3.3　外部表

所谓外部表，就是在数据库中只有表定义、没有数据的表，数据都存放在数据库之外的数据文件中。Greenplum可以对外部表执行正常的DML操作，当读取数据的时候，数据库从数据文件中加载数据。外部表支持在Segment实例上并发地高速从gpfdist中导入数据，效率很高。

Greenplum外部表架构如图4-1所示。

图4-1　Greenplum外部表架构图

外部表需要指定gpfdist的IP和端口，还要有详细的目录地址，文件名支持通配符匹配。可以编写多个gpfdist地址，总数不能超过Segment实例的数量，否则会报错。Greenplum数据库提供了两种外部表：可读外部表用于数据装载、可写外部表用于数据卸载。外部表可基于文件，亦可基于网页，这两种方式都能实现可读、可写。

如果一个查询任务使用了常规的外部表，则该外部表被认为是可重读的，这是因为在查询期间数据是静态的。而对于网页外部表，数据是不可重读的，因为在该查询的执行期间，数据可能会发生变化。

可写外部表用于从数据库表中选择记录并输出到文件、命名管道或其他可执行程序中。比如，可以从Greenplum中卸载数据并发送给可执行程序，该程序连接到其他数据库或者ETL工具并装载数据到其他地方。可写外部表被定义后，即可从数据库表中选择并插入数据到该可写外部表中。可写外部表还可以输出数据到可执行程序中，该程序要能够接受流输入数据。

在创建外部表的时候，可以指定分隔符、err表、允许出错的数据条数，以及源文件的编码等信息。

1. 外部表的创建和使用

Greenplum数据库在创建一个外部表时，需要声明外部数据的LOCATION字段和FORMAT字段。LOCATION字段指定外部数据URL，包含外部数据读写协议；FORMAT字段指定外部数据格式，如TEXT、CSV等，Greenplum会根据指定的格式，实现外部数据和数据库内部数组的转换。

创建外部表之后，可以与操作普通表一样，对其进行SELECT、INSERT等操作。外部表分为可读外部表和可写外部表，可读外部表可以执行SELECT操作，对可写外部表只能执行INSERT操作，不能对其进行SELECT、UPDATE、DELETE或TRUNCATE等操作。

（1）可读外部表

创建可读外部表时需要声明READABLE（可读），或者直接使用默认值。数据源可以是文件、gpfdist进程，或者可执行程序。

CREATE [READABLE] 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;

（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);

上面的代码创建了一个输出到gpflist的可写外部表sales_out。sales是Greenplum数据库中的一个普通表，作为外部表sales_out的内部数据源。LOCATION字段指定外部数据URL，通过gpfdist进程将数据写入sales.out文件。FORMAT字段指定外部数据格式为TEXT。

可写外部表创建成功后，可以使用INSERT命令从greenplum数据库中导出数据。比如将sales表中customer_id=123的数据写入上述sales.out文件，代码如下所示。

INSERT INTO sales_out SELECT * FROM sales WHERE customer_id=123;

2. 外部数据表读写实现机制

外部表的数据源分为如下4类。

1）file：本地文件。

2）execute：外部可执行程序。

3）gpfdist：实现了gp_proto协议的HTTP Server。

4）custom：预留的用于扩展外部表的存储类型接口。

Greenplum实现了在src/backend/access/external目录下读写各种类型数据源的代码。其中url.c是外部表数据读写的入口，url_file.c、url_execute.c、url_curl.c、url_custom.c实现了url.c中的接口，分别读写本地文件、外部可执行程序、gpfdist进程（HTTP Server）、扩展的外部数据存储类型的数据。