Greenplum企业应用实战（笔记）：第三章 Greenplu

2018-02-03 本文已影响1421人凉秋_不见春暖

第三章 Greenplum 实战

本章叫介绍两个完整的例子：数据仓库拉链记历史和网页浏览日志分析。在这两个例子中，会结合 Greenplum 的一些特性加以描述，之后会介绍使用 Greenplum

中要注意的一些特性，以及这些特性对性能的影响。

[TOC]

3.1 历史拉链表

数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。优于需要反映历史变化，数据仓库中的数据通常包含历史信息，系统记录了企业从过去某一时点（如开始应用数据仓库的时点）到目前的各个阶段的信息，通过这些信息，可以对企业的发展历程和未来趋势做出定量分析和预测。

历史拉链表是一种数据模型，主要是针对数据仓库设计中标存储数据的方式而定义的，就是记录一个事务从开始一直到当前状态的所有变化的信息。

拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是出了缓慢变化数据的一种常见方式。

3.1.1 应用场景描述

拉链算法存储：每日只向历史表中添加新增和变化的数据量

3.1.2 原理及步骤

在拉链表中，每一条数据都有一个生效日期（dw_beg_date）和失效日期（dw_end_date）

如果要查询最新的数据，那么只要查询失效时间为 3000-12-31 的数据即可，如果要查询 12 月 1 号的历史数据，则筛选生效时间 $\leq$ 2011-12-01 并且失效时间 > 2011-12-01 的数据即可。如果查询的是 12 月 2 号的数据，那么筛选条件则是生效时间 $\leq$ 2-11-12-02 并且失效时间 > 2011-12-02.

在gp中，则可以利用分区表按照 dw_end_date 保存时间，这样在查询的时候可以利用gp的分区裁剪，从而减少IO消耗。下面讲解拉链表刷新的步骤，连线代表数据流向，线上的编号就是步骤编号：

首先介绍每个表的用途：

member_fatdt0 ：表示 member 的事实表，其中 P30001231 保存的是最新数据，每个分区保留的都是历史已失效的数据
member_delta：当天的数据库变更数据，action 字段表示该数据为新增（I），更新（U）、删除（D）
member_tmp0：刷新过程中的临时表，这个表有两个分区，分别记录历史数据，即当天失效数据，另一个分区例句的是当前数据
member_tmp1：同样是刷新过程中的临时表，主要是在交换分区的时候使用

刷新过程简单来说，就是讲前一天的全量数据（分区 P30001231）与当前的增量数据进行关联，并对不同的变更类型（action）进行相应的处理，最终生成最新数据，以及当天发生变更的历史数据。

3.1.3 表结构

1. 拉链表（member_fatdt0）结构

member——fatdt0 使用 member_id 作为分部件，使数据尽量打散在每个机器上，通过 with(appendonly=true,compresslevel=5) 指定该表为压缩表，可以减少 IO 操作，将 dw_end_date 作为分区字段。建表语句如下：

2.增量表（member_delta）结构

建表语句如下：

3.临时表0（member_tmp0）结构

dtype 为分区字段，H 表示历史数据，C 表示当前数据，建表语句如下：

4.临时表1（member_tmp1）结构

表结构与 member_tmp0、member_fatdt0 一模一样，建表语句如下：

3.1.4 Demo 数据准备

（1）增量表数据

12 月 2 号增量数据，新增、删除、更新各有一条记录：

12 月 3 号增量数据，新增、删除、更新各有一条数据：

（2）历史表初始数据

初始数据为 12 月 1 号，失效日期为 3000 年 12 月 31 号：

3.1.5 数据加载

gp数据加载主要包括标准sql的 insert 、copy、外部表、gpload、web external table 等

1.insert

效率最差，只适合加载极少量数据

2.copy

copy 源于 PostgreSQL，较 SQL 的insert 方式效率大大提升，但数据仍然需通过 Master 节点，无法实现并行高效数据加载

3.外部表

4.gpload

gpload 是对外部表的一层封装。语法：

首先，编写 gpload 控制文件 member.yml ，代码如下：

其次，执行数据加载：

最后，验证数据：

5.可执行外部表

（6.3.4）基于操作系统命令读取数据文件的方式如下，用法跟普通外部表类似，不用启动 gpfdist 服务，下面的外部表只在 Master 上执行：

清空 member_delta 表并插入数据：

3.1.6 数据刷新

1.拉链表刷新

Step1：对事实表中最新数据（分区 P30001231）与 member_delta 表中的更新、删除数据进行左外连接，关联上则说明该数据已发生过变更，需要将该数据的失效时间更新为当天，并插入到 member_tmp0 表中的历史数据分区中，关联不上则说明没有发生过变更，需要将该数据插入到 member_tmp0 表中的当前数据分区中。gp 会根据 dtype 的数据自动学则额对应的分区。

初始全量数据为 2011-12-01 号，在 12 月 3 号刷新 12 月 2 号增量数据，代码如下：

Step2：将 member_delta 的新增、更新数据插入到 member_tmp0 表的当前数据分区中：

Step3：将 member_fatdt0 表中的对应分区（P20121201）与 member_tmp0 表的历史数据分区交换：

Step4：将 member_fatdt0 表中的当前数据分区（P30001231）与 member_tmp0 表的当前数据分区交换：

至此，拉链表数据刷新完成，数据验证如下：

2.历史数据查询

基于拉链表，可以回溯到历史上任意一天的数据状态。

（1）12 月 1 号数据，如图：

（2）12 月 2 号数据，如图：

（3）12 月 3 号数据，如图：

3.1.7 分区裁剪

下面通过查看执行计划来介绍gp的分区表的功能。

全表扫描的执行计划如下：

通过执行计划可以看出，gp扫描了所有的分区，当加入筛选条件 dw_end_date=‘3000-12-31‘ 时，执行计划如下：

这时，分区裁剪发生了作用，只扫描了 P30001231 这个分区

3.1.8 数据导出

gp 在处理大数据量数据导出时常用的方式主要有

并行导出（可写外部表）
非并行导出（COPY）

可写外部表导出：

1）创建可写外部表：

WRITABLE 关键字表示该外部表是可写外部表；Like 语句表示创建的外部表的表结构与 member_tmp1 表结构一样；LOCATION 指定 gpfdist 的机器名跟端口，还有保存的文件名；FORMAT 为导出文件格式定义。

2）执行数据导出：

3）验证生成的文件：

3.2 日志分析

3.2.1 应用场景描述

分析全网站每分钟的PV、UV，并导出到 Excel 中，画出折线图
解析 URL，获取 URL 中的参数列表
通过 URL 取得 member_id，然后统计当前浏览次数的用户分布，如浏览次数在 1-5,6-10,11-50,51-100 及 100 次以上的这五个区间段分别有多少个用户

3.2.1 数据 Demo

建表语句及字段描述如下：

Demo 数据如下：

3.2.3 日志分析实战

1.PV、UV 分布

cookie_id 可以视为唯一的用户表示，故 UV 可视为去重后的 cookie_id 数。sql 如下：

SELECT TO_CHAR(log_time,'yyyy-mm-dd HH24:mi:00')
      ,COUNT(1) pv
      ,COUNT(DISTINCT cookie_id) uv
FROM log_path
GROUP BY 1
ORDER BY 1;

这里只是较少的样例数据，结果如下：

将数据导出成csv格式，在 excel 中展现， copy 命令的语法如下：

testDB=# copy log_pv_uv_result to '/tmp/log_pv_uv.csv' csv;
COPY 8

在 Excel 中打开并画图，结果如下：

2.解析 URL 参数

解析 URL ，是指通过 substring 对 URL 进行正则表达式匹配，将域名取出，例如对于下面的URL：

正则表达式 \w+://([\w.]+) 可以将域名匹配出来

同样的，可以将参数后面的关键字（member_id 或 memberId）的值获取出来，作为字段 member_id。

split_part 函数可以将字符串按照某个字符串分割，然后获取其中一个子串

regexp_split_to_array 函数可以将字符串按照某个字符串分割，然后转换成数组变量：

数据 Demo 样例数据解析后结果如下：

3.用户浏览次数区间分析

要计算浏览次数的分布，首先按照 cookie_id 做聚合，计算出每个 cookie_id 的浏览次数，之后再用 case when 对数据进行分区，再聚合，sql如下：

结果如下：

3.3 数据分布

3.3.1 数据分散情况查看

首先，利用 generate_series 和 repeat 函数生成一些测试数据，代码如下：

500 万数据分散在 6 个数据节点，利用下面这个 sql 可以查询数据的分布情况

3.3.2 数据加载速度影响

接下来将通过实验来测试在分布键不同的情况下数据加载的速度

（1）数据倾斜状态下的数据加载

1）测试数据准备，将测试数据导出

2）简历测试表，以 flag 字段为分布键：

3）执行数据导入：

4）由于分布键 flag 取值只有 0 和 1，因此数据只能分散到两个数据节点，如下：

5）由于数据分布在 2 和 3 节点，对应 Primary Segment 在 dell3 Mirror 节点 dell4 上，可通过以下 sql 查询 gp_segment_configuration 获得：

在执行数据导入期间，greenplum performance monitor 页面可监控到；仅有 dell3 和 dell4 两台服务器有磁盘和 cpu 消耗，如图：

（2）数据分布均匀状态下的数据加载

1）建立测试表，以 id 字段为分布键：

2）执行数据导入：

3）由于分布键 id 取值顺序分布，因此数据可均匀分散至所有数据节点，如下：

在执行数据导入期间，greenplum performance monitor 页面可监控到：3台服务器的所有节点都有磁盘和 cpu 消耗，可见，在数据均匀的情况下，可以利用更多的机器进行工作，性能也比较高，如图：

3.3.3 数据查询速度影响

（1）数据倾斜状态下的数据查询

由于数据分布在 2 和 3 节点上，即对应 dell3 和相应的 mirror 节点 dell4 上，但是数据查询只需要 primary 节点，故只有 dell3 节点有磁盘消耗，如图：

（2）数据分布均匀状态下的数据查询

由于数据分布在所有节点上，故所有服务器都有磁盘消耗，从而大大提升了数据查询的性能

3.4 数据压缩

3.4.1 数据加载速度影响

基于 table_distribute_4 表创建一个普通的表。从 greenplum performance monitor 页面可看到，在 dell3 和 dell4 上有大量磁盘可操作，如图：

基于 table_distribute_4 表创建一个压缩表。由于数据压缩比很大，从 greenplum performance monitor 页面可看到，在 dell3 和 dell4 上基本没有磁盘写操作，只有读操作，如图，（建表语句）

3.4.2 数据查询速度影响

（1）普通表的数据查询

磁盘消耗较大，如图：

（2）压缩表的数据查询

由于数据经过压缩，占用存储空间很小，从greenplum performance monitor页面可看出，几乎没有磁盘读操作，

3.5 索引

gp 支持 b-tree、bitmap、函数索引等，简单介绍一下 b-tree 索引：

接下来我们在 flag 字段上创建 bitmap 索引：

在此查看执行计划，采用了索引扫描，如下：

剑豪索引后，在此执行上面的查询语句，有索引的情况下，用了23毫秒，相比未创建索引时2606毫秒，有了质的提升。

另外，表关联字段上的索引和 appen-only 压缩表上的索引都能带来较大的性能提升。

3.6 小结

本章简单介绍了基于 gp 数据库实现数据库数据模型刷新的过程，包括典型的需求场景分析、物理模型定义、数据加载、数据刷新、数据访问、数据导出等。另外也讲解了 gp 典型的特性，比如数据分布策略、数据压缩、统计信息、表分区、列存储、索引等。