Greenplum日志分析案例（GreenPlum企业级应用实战书）

3.2 日志分析

是网站分析的基础，通过对网站浏览的日志进行分析，可以为网站优化提供数据支持，了解用户群以及用户浏览特性。

3.2.1 应用场景描述

分析全站每分钟的PV、UV，并导出到Excel中，画出折线图
解析uRL，获取URL中的参数列表。
通过URL取得member_id，然后统计当天浏览次数的用户分布，如分布在某个区间的用户分别有多少人。

3.2.2 数据demo

建表：

drop table if exists log_path;
create table log_path(
	log_time timestamp(0)		--浏览时间
	,cookie_id	varchar(256)	--浏览的cookie_id
	,url		varchar(1024)	--浏览页面的url
	,ip			varchar(64)		--用户ip
	,refer_url	varchar(1024)	--来源的url，这里只保留域名
	
)distributed by(cookie_id);

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
	,vount(distinct cookie_id) uv
from log_path
group by 1
order by 1;

将数据导出成csv‘格式，在Excel中展现，copy命令如下：

copy log_pv_uv_result to '/tmp/log_pv_uv.csv' csv;

2. 解析URL参数
   通过substring对URL进行正则匹配，取出域名。
   split_part函数用于将字符串按照某个字符串进行分割，然后获取其中一个子串。
   regexp_split_to_array函数可以将字符串按照某个字符串分割，然后转换为数组变量。

drop table if exists log_path_tmp1;
create table log_path_tmp1 as
select
	log_time
	,cookie_id
	,substring(url,E'\\w+://([\\w.]+)') AS host
	,split_part(url,'?',1) as url
	,substring(url,E'member[_]?[i|I]d=(\\w+)') AS member_id
	,regexp_split_to_array(split_part(url,'?',2),'&') AS paras
	,ip
	,refer_url
FROM log_path
DISTRIBUTED BY (cookie_id);

3. 用户浏览次数区间分析
   首先按照cookie_id做聚合，计算出每个cookie_id的浏览次数，之后再用case when对数据进行分区，在聚合，sql：

select case when cnt > 100 then '100+'
			when cnt>50    then '50-100'
			when cnt > 10  then '11-50'
			when cnt > 5   then '6-10'
			else '<5' end tab
			, count(1) as number
from
(
	select cookie_id,count(1) cnt
	from log_path_tmp1
	group by 1
	
) t
group by 1;

3.3 数据分布

了解Greenplum数据是如何分散在各个数据节点上的，有必要了解数据倾斜对数据加载、数据分析、数据导出的影响。

3.3.1 数据分散情况查看

测试数据生成

create table test_distribute_1
as 
select a as id
,round(random()) as flag
,repeat('a',1024) as value
form generate_series(1,5000000)a;

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

select gp_segment_id,count(*)
from test_distribute_1
group by 1;

3.3.2 数据加载速度影响

测试在分布键不同的情况下数据加载的速度
（1）数据倾斜状态下的数据加载
1)测试数据准备，将测试数据导出：

copy test_distribute_1 to '/home/gpadmin/data/test_distribute.dat' with delimiter '|';

2)建立测试表，以flag字段为分布键

create table test_distribute_2 as select * form test_distribute_1 limit 0 distributed by(flag);

3)执行数据导入

time psql -h localhost -d testDB -c "copy test_distribute_2 from stdin with delimiter '|' < /home/gpadmin/data/test_distribute.dat"

3.3.3 数据查询速度影响

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

select gp_segment_id,count(*),max(length(value)) from test_distribute_2 group by 1;

(2)数据分布均匀状态下的数据查询

select gp_segment_id,count(*) ,max(length(value)) from test_distribute_3 group by 1;

3.4 数据压缩

3.4.1 数据加载速度的影响

加快

3.4.2 数据查询速度影响

加快

3.5 索引

Greenplum支持B-tree、bitmap、函数索引等，这里介绍一下B-tree索引

create table test_index_1 as select * from test_distribute_1;

select id,flag from test_index_1 where id = 100;

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

create table test_index_2_idx on test_index_1 (id);

再次查看执行计划，采用了索引扫描

explain select id , flag from test_index_1 where id=100;

时间从2606ms减少到23ms。