Greenplum 实战

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

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

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

3.1.1 应用场景描述
拉链算法存储：每日只向历史表中添加新增和变化的数据量

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

1

2

3

如果要查询-新的数据，那么只要查询失效时间为 3000-12-31 的数据即可，如果要查询 12 月 1 号的历史数据，则筛选生效时间



2011-12-01 并且失效时间 > 2011-12-01 的数据即可。如果查询的是 12 月 2 号的数据，那么筛选条件则是生效时间 2-11-12-02 并且失效时间 > 2011-12-02.

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

4

首先介绍每个表的用途：

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 作为分区字段。建表语句如下：

5

2.增量表（member_delta）结构

建表语句如下：

6

3.临时表0（member_tmp0）结构

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

7

8

4.临时表1（member_tmp1）结构

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

9

3.1.4 Demo 数据准备
（1）增量表数据

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

10

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

11

（2）历史表初始数据

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

12

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 ，代码如下：

13

14

其次，执行数据加载：

15

后，验证数据：

16

5.可执行外部表

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

17

18

清空 member_delta 表并插入数据：

19

3.1.6 数据刷新
1.拉链表刷新

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

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

20

21

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

22

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

23

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

24

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

25

2.历史数据查询

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

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

26

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

27

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

28

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

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

29

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

30

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

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

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

1）创建可写外部表：

31

32

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

2）执行数据导出：

33

3）验证生成的文件：

34

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
建表语句及字段描述如下：

35

Demo 数据如下：

36

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;
这里只是较少的样例数据，结果如下：

37

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

testDB=# copy log_pv_uv_result to '/tmp/log_pv_uv.csv' csv;
COPY 8
在 Excel 中打开并画图，结果如下：

38

2.解析 URL 参数

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

39

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

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

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

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

40

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

41

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

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

42

结果如下：

43

3.3 数据分布
3.3.1 数据分散情况查看
首先，利用 generate_series 和 repeat 函数生成一些测试数据，代码如下：

44

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

45

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

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

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

46

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

47

3）执行数据导入：

48

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

49

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

50

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

51

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

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

52

2）执行数据导入：

53

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

54

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

55

3.3.3 数据查询速度影响
（1）数据倾斜状态下的数据查询

56

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

57

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

58

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

3.4 数据压缩
3.4.1 数据加载速度影响
基于 table_distribute_4 表创建一个普通的表。从 greenplum performance monitor 页面可看到，在 dell3 和 dell4 上有大量磁盘可操作，如图：

59

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

60

3.4.2 数据查询速度影响
（1） 普通表的数据查询

61

磁盘消耗较大，如图：

62

（2）压缩表的数据查询

63

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

64

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

65

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

66

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

67

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

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

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

本文来源：https://blog.csdn.net/m0_37253968/article/details/103278856