第六章 Greenplum 高级应用
[TOC]
本章将介绍一些 Greenplum 的高级特性，主要是与其他关系型数据库有区别的地方。
当今的数据处理大致可以分成两大类：联机事务处理OLTP（On-Line Transaction Processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。
表6-1列出OLTP与OLAP之间的比较
6.1 Appendonly 表与压缩表
6.1.1 应用场景及语法介绍
压缩表必须是Appendonly表，是只能不断追加的表，不能进行更新和删除。
（1）压缩表的应用场景
- 业务上不需要对表进行更新和删除操作，用truncate + insert 就可以实现业务逻辑
- 访问表的时候基本上是全表扫描，不需要在表上建立索引
- 不能经常对表进行加字段或修改字段类型，对Appendonly表加字段比普通表慢很多
（2）语法介绍
建表的时候加上 with(appendonly=true) 就可以指定表是Appendonly表。如果需要建压缩表，则加上 with(appendonly=true,compresslevel=5)，其中compresslevel是压缩率，取值为1~9，一般选择5就足够。
6.1.2 压缩表的性能差异
由于数据仓库的大部分应用都是IO密集型的，每次的查询基本上都是全表扫描，大量的顺序读写。
一张简单的商品表，总数据量500万条，常见的数据类型有date、numeric、integer、varchar、text等，普通表与压缩表性能比较结果如表6-3：
6.1.3 Appendonly 表特性
对于每一张Appendonly表，创建的时候都附带创建一张pg_aoseg模式下的额表，表名一般是 pg_aoseg_ + 表的oid，这张表中保存了这张Appendonly表的一些原数据信息。
这个表的每个字段信息如表6-4：
在第四章中，介绍了一个函数 gp_dist_random，通过这个函数，可以查询子节点的信息，可以知道gp 底层每个数据节点的数据量、数据文件大小的情况，这样我们就能够分析表的数据分布情况、算出倾斜率等。
基于 pg_aoseg_ 这个表 ：
- get_ao_compression_ratio：查询压缩表的压缩率
- get_ao_distribution：查询每个子节点的数据量
基于 Appendonly 表，开发了两个 gp的函数：
- get_table_count：获取Appendonly表的行数，Appendonly表有一个字段用于保存这些信息，支持分区表。
- get_table_info ：用于获取更加详尽的表的信息，有表的行数、大小、数据倾斜率（最大节点数据量/平均节点数据量）、压缩率。对于非分区表，就只有一行数据；对于分区表，但一行则是所有分区的汇总数据，其他是每一个分区的具体信息，效果如图6-1：
6.1.4 相关数据字典
在gp3.* 中，aoseg表的信息记录在 pg_class 中的relaosegrelid 字典中，可用以下sql进行查询：
select relaosegrelid from pg_class where relname='test01';
在gp4.*中，pg_class的relaosegrelid 字段已经取消，aoseg表的信息保留在pg_appendonly 表中的segrelid字段中。
6.2 列存储
Appendonly表还有一种特殊的形式，就是列存储。
6.2.1 应用场景
列存储一般适用于宽表（即字段非常多的表）。在使用列存储时，同一个字段的数据都连续保存在一个物理文件中，所以列存储的压缩率已普通压缩表的压缩率要高的多，另外在多数字段中筛选其中几个字段时，需要扫描的数据量很小，扫描速度比较快。因此，列存储尤其适合在宽表中部分字段进行筛选的场景。
6.2.2 数据文件存储特性
列存储在物理上存储，一个列对应一个数据文件，文件名是原文件名加上 .n（n是一个数字）的形式来表示的，第一个字段，n=1，第二个字段n=129，以后每增加一个字段，n都会增加128.
为什么每增加一个字段，n不是递增的而是中间要间隔128个数字呢？这大概是由于，在gp中，每一个数据文件最大是1GB，如果一个字段在一个Segment中的数据量超过1GB，就要重新生成一个新的文件，每个字段中间剩余的这127个数字，就是预留给组队内容很大，单个文件1GB放不下，拆分成多个文件时，可以使用预留的127个数字为数据文件编号。
从这个物理的存储特性就可以很明显地看多列存储的缺点，那就是会产生大量的小文件，假设一个集群中有30个节点，我们建一张有100个字段宽表的，假设这张表是按照每天分区的一张分布表，保留了一年的数据，那么将会产生的文件数是：
即产生将近110万的文件，这样我们对一个表进行DDL操作就会比较慢，所以，列存储应该在合适的场景下才能使用，而不能滥用列存储，否则会得不偿失
6.2.3 如何使用列存储
列存储的表必须是appendonly表，创建一个列存储的表只需要在创建表的时候，在 with 子句中加入 appendonly=true,ORIENTATION=column 即可。一般 appendonly 表会结合压缩一起使用，在 with 子句中增加 compresslevel=5 启用压缩.
6.2.4 性能比较
简单比较一下列存储和普通压缩表在性能上的区别。由于列存储主要应用在宽表上，故这次测试都是针对字段比较多的表。
测试结果：
通过表6-6可以看出：
- 完全随机的数据压缩率比较低，使用两种方式的压缩结果差不多
- 当只查询表的数据量的时候，普通压缩表需要全表扫描，但是列存储只需要查询其中一个字段，速度非常快
- 随着查询字段的增加，普通压缩表查询的速度基本上变动不大，因为扫描的数据量没有变化。而列存储则随着查询的字段增加，消耗时间增长明显
- 在测试的时候瓶颈在于磁盘的IO，这是因为测试集群磁盘性能较差。在CPU消耗上，列存储的消耗略大于普通表的消耗。
采用这两种数据方式加载数据时都使用外部表，加载时的瓶颈在于文件服务器的网卡，所以使用这两种方式进行数据加载的速度都差不多
通过表6-7的结果可以看出，测试的结果与测试数据1的结果大致相同，区别主要是：
- 测试的数据是真实的数据，每个字段的内容都会有些相似，当每个字段的数据比较相似时，使用列存储，这样在压缩文件时，可以获得更高的压缩率，节省磁盘的空间占用。
- 瓶颈还是磁盘IO，由于列存储有更好的压缩率，所以在查询全部字段的情况下，列存储消耗的时间还是比普通压缩表小很多的。
6.3 外部表高级应用
6.3.1 外部表实现原理
gpfdist 可以看做一个http服务，当我们启动 gpfdist 时，可以用 wget 命令去下载 gpfdist 的文件，将创建外部表命令时使用的url地址中的gpfdist 换成 http 即可，如：
wget http://10.20.151.59:8081/inc/1.dat -o 2.dat
当查询外部表时，所有的Segment都会连上 gpfdist，然后 gpfdist 将数据随机分发给每个节点。
gpfdist 的架构如图6-2 所示：
1、gpfdist 的工作流程
- 启动 gpfdist，并在 Master 上建表。表建好之后并没有任何的数据流动，只是定义好了外部表的原数据信息。
- 将外部表插入到一张 Gp 的物理表中，开始导入数据。
- Segment 根据建表时定义的 gpfdist url 个数，启动相同的并发到 gpfdist 获取数据，其中每个 Segment 节点都会连接到 gpfdist 上获取数据。
- gpfdist 收到 Segment 的连接并要接收数据时开始读取文件，顺序读取文件，然后将文件拆分成多个块，随机抛给 Segment。
- 由于 gpfdist 并不知道数据库中有多少个 Segment，数据是按照哪个分布键拆分的，因此数据是随机发送到每个 Segment 上的，数据到达 Segment 的时间基本上是随机的，所以外部表可以看成是一张随机分布的表，将数据插入到物理表的时候，需要进行一次重分布。
- 为了提高性能，数据读取与重分布是同时进行的，当数据重分布完成后，整个数据导入流程结束。
2、gpfdist 最主要的功能
- 负载均衡：每个 Segment 分配到的数据都是随机的，所以每个节点的负载都非常均衡。
- 并发读取，性能高：每台 Segment 都同时通过网卡到文件服务器获取数据，并发读取，从而获取了比较高的性能。相对于 copy 命令，数据要通过 Master 流入，使用外部表就消除了 Master 这个单点问题。
3、如何提高 gpfdist 性能
想提高一个工具的性能，首先要了解这个工具使用的瓶颈在哪里。对于数据导入，衡量性能最重要的就是数据分发时的吞吐，其中有两个地方容易成为瓶颈。
（1）文件服务器
一般文件服务器比较容易出现瓶颈，因为所有的 Segment 都连接到这个节点上获取数据，所以如果文件服务器是单机的，那么就很容易在磁盘 IO 和网卡上出现瓶颈。
对于文件服务器，当磁盘 IO 出现瓶颈时：
- 使用磁盘阵列来提高磁盘的吞吐能力
- 采用分布式文件系统来提高整体的性能
对于网卡出现瓶颈：
- 更换网卡为万兆网卡，需要各环节的网络环节满足条件，如交换机支持等
- 通过多网卡机制来保证。如图6-3，将文件拆分成多个文件，并启动多个 gpfdist 分别读取不同的文件，然后将 gpfdist 绑定到不同的网卡上以提高性能。
在创建表的时候指定多个 gpfdist 的地址，例如：
CREATE EXTERNAL TABLE table_name
( column_name data_type [, ...] | LIKE other_table )
LOCATION ('gpfdist://filehostip1[:port1]/file_pattern1',
'gpfdist://filehostip2[:port2]/file_pattern2',
'gpfdist://filehostip3[:port3]/file_pattern3',
'gpfdist://filehostip4[:port4]/file_pattern4')
......
当然我们也可以只启动一个 gpfdist，但是通过不同的 ip 连接到 gpfdist 上，这样读取文件的 gpfdist 只有一个，不能实现 IO 的并发，但是网卡却可以使用多张，从而来消除单网卡的瓶颈。
6.3.2 可写外部表
前面介绍的是只读外部表，在 gp 中，还有一种可写外部表。
语法如下：
CREATE WRITABLE EXTERNAL TABLE table_name
( column_name data_type [, ...] | LIKE other_talbe )
LOCATION ('gpfdist://outputhost[:port]/fillename'[, ...]) | ('gpfdist://hdfs_host[:port]/path')
FORMAT 'TEXT'
[( [DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF'] )]
| 'CSV'
[([QUETE [AS] 'quote']
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE QUOTE column [, ...]]
[ESCAPE [AS] 'escape'] )]
[ ENCODING 'write_encoding' ]
[DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY]
在语法上可写外部表与普通外部表主要有两个地方不一样：
- 可写外部表没有错误表，不能指定允许有多少行数据错误。因为外部表的数据一般是从数据库的一张表中导出的，格式肯定是正确的，一般不会有异常数据。
- 可写外部表在建表时可以指定分布键，如果不指定分布键，默认为随机分布(distributed randomly)。尽量采用与原表一致的分布键。
使用可写外部表的注意事项：
- 可写外部表不能中断（truncate）。当我们将数据写入可写外部表时，如果可写外部表中途断开了要想重新运行必须手动将原有的文件删除，否则那一部分数据会重复。
- 可写外部表指定的分布键应该与原表的分布键一致，避免多余的数据重分布。
- gpfdist 必须使用 gp 4.x 之后的版本。可写外部表是 gp 4.x 之后加入的新功能，在其他机器上启动 gpfdist，只需要将 $GPHOME/bin/gpfdist 复制过去即可。
6.3.3 HDFS外部表
gp 可以直接读取 HDFS 文件，这样可以将 gp 和 HDFS 整合在一起，将 gp 作为一个计算节点，计算后的数据可以直接写入到 HDFS 中，提供对外的服务。HDFS 外部表的架构图如图6-4。
下面将对 gp4.2 以上版本的 HDFS外部表介绍。
在 gp 4.2 及以上版本安装、配置 HDFS外部表的步骤如下：
- 在所有 Segment 上安装 Java 1.6 或以上版本。
- gp 数据库包括一下与 Hadoop 相关版本：
- Provatal HD 1.0（gphd-2.0）：Hadoop 2.0 版本
- Greenplum HD(gphd-1.0,gphd-1.1 和 gphd-1.2)：默认版本，使用其他组件可以通过设置 gp_hadoop_targetversion 这个参数进行切换。
- Greenplum MR 组件（gpmr-1.0 和 gpmr-1.2）：Greenplum 版本的 Mapreduce。
- Cloudera Hadoop 版本连接（cdh3u2 和 cdh4.1）：Cloudera 版本的 Hadoop。
- 需要读者自己安装 Hadoop（Gp 只支持以上所列的版本），安装之后，需确保 gp 的系统用户有读和执行 Hadoop 相关 lib 库的权限。
- 设置环境变量 JAVA_HOME、HADOOP_HOME。
- 设置 gp_hadoop_target_version 和 gp_hadoop_home 参数如表6-9：
- 重启数据库：在使用 HDFS外部表的时候
- 有两层权限关系：
- 对 gp 的 HDFS 的使用用户赋予 SELECT 和 INSERT 权限：GRENT INSERT ON PROTOCOL gphdfs TO gpadmin;
- 确保 gp 对应的 OS用户有 Hadoop 上 HDFS 文件的读写权限。
- gp 在 HDFS 上支持三种文件格式：
- Text，文本格式，同时支持读写
- gphdfs_import，只支持只读外部表。
- gphdfs_export，只支持可写外部表。
- 有两层权限关系：
创建外部表进行读取：
CREATE readable EXTERNAL TABLE hdfs_test_read (id int,name varchar(128))
LOCATION ('gphdfs://10.20.151.7:9000/data/gpext/hdfs_test.dat')
FORMAT 'TEXT' (DELIMITER ',');
select * from hdfs_test_read;
id | name
---------------
104 | john
101 | tom
103 | marry
102 | cat
在 $GPHOME/lib/hadoop 目录下，gp 自带了各种 Hadoop 版本的 jar 包，这些包中定义了 gphdfs_import 跟 gphdfs_export 的数据格式。gp 在读写这种格式的时候，需要自己编写 MapReduce 任务。
6.3.4 可执行外部表
可执行外部表的语法如下：
CREATE [READABLE] EXTERNAL WEB TABLE table_name ( column_name data_type [, ...] | LIKE other_table )
LOCATION ('http://webhost[:port]/path/file' [, ...])
| EXECUTE 'command' [ON ALL
| MASTER
| number_of_segments
| HOST ['segment_hostname']
| SEGMENT segment_id ]
FORMAT 'TEXT'
[( [HERDER]
[DELIMITER [AS] 'delimiter' | 'OFF']
[NULL [AS] 'null string']
[ESCAPE [AS] 'escape' | 'OFF']
[NEWLINE [AS] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS])]
| 'CSV'
[( [HEADER]
[QUOTE [AS] 'delimiter']
[NULL [AS] 'null string']
[FORCE NOT NULL column [, ...]]
[ESCAPE [AS] 'null string']
[NEWLINE [AS] 'LF' | 'CR' | 'CRLF']
[FILL MISSING FIELDS])]
[ ENCODING 'encoding' ]
[ [LOG ERRORS INTO error_talbe] SEGMENT REJECT LIMIT count
[ROWS | PERCEND] ]
在使用外部表时，每个 Segment 的参数可能会略有不同，或者是在脚本中需要获取一些系统的参数。表6-10是 gp 自带的一些常见参数，可以供脚本使用。
6.4 自定义函数——各个编程接口
6.4.1 pl/pgsql
相比起 Oracle 的 pl/sql ，pl/pgsql 主要有如下限制：
- 最大的子事务个数只能是100，每一个异常捕获，都会造成一个子事务。
- 在函数执行的过程中不能执行 commit。
- 由于不能执行 commit，因此一个函数中不要处理太多的逻辑，否则容易导致程序出错，这一点与 oracle 不一样。如果有比较复杂的逻辑要以外部的 shell 调用来实现，不要在函数中实现。
- 所有的 pl/pgsql 都必须以函数的形式存在，没有 oracle 中存储过程的概念，所以每次使用都必须建立函数，然后再执行这个函数，使用起来不方便。
6.4.2 C 语言接口
6.4.2 plpython
6.5 Greenplum MapReduce
MapReduce 是 Google 提出的一种海量数据并发处理的一种编程思想，可以让程序员在多态机器上快速的编写出自己的代码。MapReduce 的基本思想是将大数据量拆分成一行一行 (key,value) 对，然后分发给 Map 模块，通过自定义的 Map 函数，生成新的 (key,value) 对，通过排序汇总，生成 (key,output_list) 发给自定义的 Reduce 函数处理，每一个 Reduce 函数处理一个唯一的 key 及这个 key 对应的所有 value 的列表，如果有需要，还可以将 Reduce 的结果作为下一个 Map 的数据再进行计算。每一个机器都可以启动多个 Map 和 Reduce 来计算结果。
gp MapReduce 允许用户自己编写 map 和 reduce 函数，然后提交给 gp 并发处理引擎处理，gp 在分布式的环境下允许 MapReduce 代码，并处理所有Segment 的报错信息。
gp 中的 MapReduce 基本框架如图：
MapReduce 的组成部分：
在 gp 中，执行 MapReduce 函数需要编写 yaml 文件。在 yaml 文件中，主要有以下几个部分需要定义：
1、INPUT
INPUT 有多种类型：可以是一个外部文本数据文件、一个数据库中的表或查询等。
（1）外部文件
- INPUT:
NAME: my_file_input
FILE: seghostname:/var/data/gpfiles/employees.txt
COLUMNS
- first_name text
- last_name text
- dept text
FORMAT: TEXT
DELIMITER: |
（2）数据库表
- INPUT:
NAME: my_table_input
TABLE: sales
（3）数据库查询
- INPUT:
NAME: my_query_input
QUERY: SELECT vendor, amt FROM sales WHERE region='usa';
（4）通过 gpfdist 导入外部文件
- INPUT:
NAME: my_distributed_input
# specifiles the host,port and the desired files served
# by gpfdist. /* denotes all files on the gpfdist server
GPFDIST:
- gpfdisthost:8080/*
COLUMNS
- first_name text
- last_name text
FORMAT: TEXT
DELIMITER: '|'
（5）操作系统命令
- INPUT:
NAME: my_query_input
EXEC: /var/load_scripts/get_log_data.sh
COLUMNS
- url text
- date timestamp
FORMAT: TEXT
DELIMITER: '|'
类型（4）和（5）也可以采用在数据库中建成外部表的形式，就是可以当成数据库中的表操作，跟（2）一样，这两个类型的功能跟外部表有点重复。
2、Map
Map 是输入一行数据，有 0 个或多个数据输出，可以用 python 或 perl 实现。
3、Reduce
也可以用 python 或 perl 实现。
gp 提供几个预定义 Reduce 函数：
IDENTITY - 返回没有标红的 (key,value) 对
SUM - 计算数字类型的和
AVG - 计算数字类型的平均值
COUNT - 计算输入数据的count数
MIN - 计算数字类型的最小值
MAX - 计算数字类型的最大值
（函数对 value 进行相应的操作）
4、OUTPUT
OUTPUT 可以是数据表也可以是文本文件：
（1）输出到文本文件
- OUTPUT:
NAME: gpmr_output
FILE: /var/data/mapreduce/wordcount.out
（2）输出到数据表
- OUTPUT
NAME: gpmr_output
TABLE: wordcount_out
KEYS:
- value
MODE: REPLACE(注：若是放此表并重建表，则用 REPLACE，若是想追加输出的数据，则用 APPEND)
5、Task
Task 的描述是选择性的，主要用在多级的 MapReduce 任务中，一个 Task 的输出可以作为下一个 Task 或 Map 的输入。
6、EXECUTE
EXECUTE 就是将上面定义的步骤串联起来
EXECUTE:
- RUN:
SOURCE: input_or_task_name
TARGET: output_name
MAP: map_function_name
REDUCE: reduce_function_name
6.6 小结
本章介绍了 gp 的一些高级的特性：
- 压缩表：对数据采用压缩存储的方法，可以利用 CPU 来节省存储空间，降低 IO 的消耗，对于数据库应用性能有着重要的影响。
- 列存储：一种特殊的压缩表，将数据按列存储，可以提升压缩率，对于数据库这种 IO 密集型的应用，可以大大提升性能。但是列存储同时也带来了大量的小文件，会对文件系统造成一定的压力，故不能大规模使用。
- 外部表：gp 通过外部表可以非常方便地进行数据的导入导出，通过并行处理，性能非常高。可执行外部表表提供了一种可拓展的方法，使得数据导入导出更加灵活。同时 gp 还通过提供与 HDFS 快速交互的方式，使数据库与 Hadoop 可以灵活地结合在一起使用。
- 自定义函数：要使数据库使用起来更加方便，自定义函数在拓展 sql 上有着很重要的作用，通过自定义函数，再结合 sql，可以实现很多功能。
- MapReduce：当下非常流行的一种编程思想，它简化了编程模型，通过这种编程思路，可以解决很多 sql 无法完成的功能。
