一、目的
1、 理解Greenplum中的数据分布策略(random 和 distribution),分析不同分布策略的优劣;
2、 理解查询执行中的数据广播和数据重分布,分析在何种情况下选择哪种策略,以具体实验验证;
3、借助explain指令,分析select、join、group等常用sql语句的执行流程;
4、查看相关文档理解SQL中窗口函数的语义,分析窗口函数的执行语义,以实验验证。
(复制)
二、 使用环境
主机数量:3台虚拟机,1台master单核2G内存,2台slave单核1.5G内存
虚拟机:VMware 10.0
操作系统:CentOS 6.7
Greenplum版本:4.3.99.00(PostgreSQL 8.3.23)
二、实验过程
1、Greenplum数据分布策略random与hash-distribution
首先,创建一张原始表,输入一些数据,不指定分布键,默认以第一列作为分布键。
可以修改分布键,按c3列分布。
插入一些数据,然后查看数据分布。
可以观察到,按c3列的值进行哈希分布。c3=3的值全部分布到seg0,因此可以看出,若分布键不均匀,以hash分布的方式分布数据,存在数据偏移。
修改分布策略为随机分布,查看数据分布。
咋一看,数据分布也不均匀,但是这可能是由于数据量过少,随机分布出现的偏差。重新组织数据,再次查看,发现数据分布比较均匀了。而且,可以看到,c3=3的行被分散到不同的seg中去。
总结GP的分布策略,采用随机的分布方式,数据分布较为均匀。按分布键的方式进行哈希分布,不一定能够保证数据不会存在偏移,但是对于读取数据应该较为友好。可以不必遍历所有的seg节点。
2、数据广播和数据重分布
关联数据在不同节点上的,需要通过网络流入到一个节点上执行连接操作,产生了数据迁移,GP中的数据迁移方式为数据广播和数据重分布。
数据重分布,使用上次作业所导入的表,进行连接操作,发现查询计划使用了数据重复布执行。
数据广播,创建一个小表oders_replay,使其与大表做连接,发现查询的执行方式变成数据广播方式。
因此,总结上面GP策略的选择上,若数据量相差比较大的情况下,将小表广播到各个seg节点上,会执行的更快。若数据量基本相当,则使用数据重分布的方式,通常代价比较小。
、sql执行流程
Select语句执行:
根据上图所示,”select *,cume_dist() over(order by grade) from stugrade;”语句是一个嵌套循环查询,循环内先做调用聚集函数。分布式执行过程采用Gather Motion(N:1)方式将所有子节点汇合数据到slice 1(master)上。顺序扫描全表,按grade合并,再排序。
语句执行:
上面的Join语句同样是采用Gather Motion(N:1)方式迁移数据,之后采用hash join的方式进行连接。
Group 语句执行:
Group语句以grade属性进行成组,统计各grade的人数,sql执行过程是,数据按grade键重分布,顺序扫描stugrade表,然后使用hash方式是以grade属性成组,最后迁移到一个节点进行统计返回结果。
4、窗口函数
窗口函数是只能在select语句中使用的一直函数类型,每一行返回一个值,结果按照当前行或行所对应的窗口分隔、框架来说明,使用时带有over语句。所有窗口函数都使用over()语句作为条件,可在括号中指定应用的函数。
窗口函数表:
函数 | 返回类型 | 语法 | 描述 |
cume_dist() | double precision | CUME_DIST() OVER ( [PARTITION BY expr ] ORDER BY expr ) | 计算一组只中值的累计分布。具有相同值的行有相同的累计分布值。 |
dense_rank() | bigint | DENSE_RANK () OVER ( [PARTITION BY expr ] ORDER BY expr ) | 计算在一个无跳过的有序组中的行的排行值。 |
first_value(expr) | same as input expr type | FIRST_VALUE( expr ) OVER ( [PARTITION BY expr ] ORDER BY expr [ROWS|RANGE frame_expr ] ) | 返回一个有序集合中的第一个元素的值 |
lag(expr[,offset] [,default]) | same as input expr type | LAG( expr [, offset ] [, default ]) OVER ( [PARTITION BY expr ] ORDER BY expr ) | 提供一个访问同一个表的多行数据,而不用做自连接。从查询中返回一系列行的游标。LAG提供在给定偏移上的一行的访问。默认偏移是1。如果偏移超过范围,讲返回默认值。若默认值没有指定,则默认值为空。 |
last_value(expr) | same as input expr type | LAST_VALUE(expr) OVER ( [PARTITION BY expr ] ORDER BY expr [ROWS|RANGE frame_expr ] ) | 返回一个有序集合中的最后一个元素的值 |
lead(expr [,offset] [,default]) | same as input expr type | LEAD(expr [,offset] [,exprdefault]) OVER ( [PARTITION BY expr ] ORDER BY expr ) | 提供一个访问同一个表的多行数据,而不用做自连接。从查询中返回一系列行的游标。Lead提供在给定偏移上的一行的访问。默认偏移是1。如果偏移超过范围,讲返回默认值。若默认值没有指定,则默认值为空。 |
ntile(expr) | bigint | NTILE(expr) OVER ( [PARTITION BY expr ] ORDER BY expr ) | 将一个有序的数据集划分成若干桶(由表达式定义),假设每一行放进一个桶中。 |
percent_rank() | double precision | PERCENT_RANK () OVER ( [PARTITION BY expr ] ORDER BY expr ) | 计算在数据集中的百分比。 |
rank() | bigint | RANK () OVER ( [PARTITION BY expr ] ORDER BY expr ) | 计算一个有序组中的排名,相同值的排名相同,所以排名可能不连续。 |
row_number() | bigint | ROW_NUMBER () OVER ( [PARTITION BY expr ] ORDER BY expr ) | 将一个唯一的数字分配给分区中的每一行或这个查询的每一行。 |
简单的对上面的几个窗口函数进行测试。
Row_number():
Rank():
Percent_rank(): 这里的百分比是指在之前n-1行的百分比累计值。相同行值的百分比值相同。
和first_value(expr):last取分区内最后一个元素,first区第一个元素。
Cume_dist():
5.附注
*