利用hive对微博数据统计分析案例

数据样例：

[{"beCommentWeiboId":"","beForwardWeiboId":"","catchTime":"1387157643","commentCount":"682","content":"喂！2014。。。2014！喂。。。","createTime":"1387086483","info1":"","info2":"","info3":"","mlevel":"","musicurl":[],"pic_list":["http://ww1.sinaimg.cn/square/47119b17jw1ebkc9b07x9j218g0xcair.jpg","http://ww4.sinaimg.cn/square/47119b17jw1ebkc9ebakij218g0xc113.jpg","http://ww2.sinaimg.cn/square/47119b17jw1ebkc9hml7dj218g0xcgt6.jpg","http://ww3.sinaimg.cn/square/47119b17jw1ebkc9kyakyj218g0xcqb3.jpg"],"praiseCount":"1122","reportCount":"671","source":"iPhone客户端","userId":"1192336151","videourl":[],"weiboId":"3655768039404271","weiboUrl":"http://weibo.com/1192336151/AnoMrDstN"}]

字段描述

总共19个字段
beCommentWeiboId 是否评论
beForwardWeiboId 是否是转发微博
catchTime 抓取时间
commentCount 评论次数
content 内容
createTime 创建时间
info1 信息字段1
info2信息字段2
info3信息字段3
mlevel no sure
musicurl 音乐链接
pic_list 照片列表（可以有多个）
praiseCount 点赞人数
reportCount 转发人数
source 数据来源
userId 用户id
videourl 视频链接
weiboId 微博id
weiboUrl 微博网址

题目

1. 组织数据
   （创建Hive表weibo_json(json string)，表只有一个字段，导入所有数据，并验证查询前5条数据）
   （解析完weibo_json当中的json格式数据到拥有19个字段的weibo表中，写出必要的SQL语句）

2. 统计微博总量 和 独立用户数

3. 统计用户所有微博被转发的次数之和，输出top5用户，并给出次数

4.统计带图片的微博数

5. 统计使用iphone发微博的独立用户数

6. 将微博的点赞人数和转发人数相加求和，并将相加之和降序排列，取前10条记录，输出userid和总次数

7. 统计微博中评论次数小于1000的用户ID与数据来源信息，将其放入视图，然后统计视图中数据来源是”ipad客户端”的用户数目

8. 统计微博内容中出现”iphone”次数最多的用户，最终结果输出用户id和次数（注意：该次数是”iphone”的出现次数，不是出现”iphone”的微博数目）

9. 求每天发微博次数最多的那个家伙的ID和发微博的条数

10. 求出所有被多次引用（同一张照片出现在多条微博中，超过1条就算多条）的照片的数目

解题

组织数据

// 创建库：
create database weibo;
use weibo;

// 创建表：
create table weibo_json(json string);

// 导入数据：
load data local inpath '/home/hadoop/weibojson.data.json' into table weibo_json;

// 验证：
select json from weibo_json limit 5;

// 创建19个字段的weibo表：
create table weibo(
beCommentWeiboId string,
beForwardWeiboId string,
catchTime string,
commentCount int,
content string,
createTime string,
info1 string,
info2 string,
info3 string,
mlevel string,
musicurl string,
pic_list string,
praiseCount int,
reportCount int,
source string,
userId string,
videourl string,
weiboId string,
weiboUrl string
) row format delimited fields terminated by '\t';

插入数据

insert into table weibo 
select 
get_json_object(json,'$[0].beCommentWeiboId') beCommentWeiboId,
get_json_object(json,'$[0].beForwardWeiboId') beForwardWeiboId,
get_json_object(json,'$[0].catchTime') catchTime,
get_json_object(json,'$[0].commentCount') commentCount,
get_json_object(json,'$[0].content') content, 
get_json_object(json,'$[0].createTime') createTime,
get_json_object(json,'$[0].info1') info1, 
get_json_object(json,'$[0].info2') info2,
get_json_object(json,'$[0].info3') info3,
get_json_object(json,'$[0].mlevel') mlevel,
get_json_object(json,'$[0].musicurl') musicurl,
get_json_object(json,'$[0].pic_list') pic_list,
get_json_object(json,'$[0].praiseCount') praiseCount,
get_json_object(json,'$[0].reportCount') reportCount,
get_json_object(json,'$[0].source') source,
get_json_object(json,'$[0].userId') userId,
get_json_object(json,'$[0].videourl') videourl,
get_json_object(json,'$[0].weiboId') weiboId,
get_json_object(json,'$[0].weiboUrl') weiboUrl
from weibo_json;

统计用户所有微博被转发的次数之和，输出top5用户，并给出次数。注意：一个用户可能发过多个微博

思路：

1. 以用户id分组，求转发和
2. 按照转发量排序
   select sum(reportCount) sumrep
   from weibo
   group by userId
   order by sumrep desc limit 5;

结果
2721667
518676
477742
430532
415424

5、统计带图片的微博数（7分）
图片字段pic_list
select count(weiboId) total
from weibo
where instr(pic_list,'http')>0
结果：

5278

统计使用iphone发微博的独立用户数

数据来源字段：source
select count(distinct userId)
from weibo
where instr(lcase(source),'iphone')>0;

或者使用
select count(distinct userId)
from weibo
where lcase(source) like '%iphone%';

将微博的点赞人数和转发人数相加求和，并将相加之和降序排列，取前10条记录，输出userid和总次数

思路：
以userid分组，统计

select count(praiseCount)+count(reportCount) total
from weibo
group by userId
order by total desc limit 10;

结果：
14328
620
516
472
428
340
308
226
210
188

统计微博中评论次数小于1000的用户ID与数据来源信息，将其放入视图，然后统计视图中数据来源是”ipad客户端”的用户数目

思路：

1. commentCount<1000
2. select userId,source

create view weibo8_view as
select userId,source
from weibo where commentCount<1000;

select count(userId)
from weibo8_view where source like '%皮皮%';

统计微博内容中出现”iphone”次数最多的用户，最终结果输出用户id和次数（注意：该次数是”iphone”的出现次数，不是出现”iphone”的微博数目）

思路

1. 以iPhone为分隔符使用split切分来源之后转换为数组，统计数组size

2. 以userid分组最后统计用户所有出现的次数

create view weibo9_view as
select userId,size(split(lcase(content),'iphone'))-1 total
from weibo where size(split(lcase(content),'iphone'))-1>0;

select userId, sum(total) total
from weibo9_view
group by userId order by total desc limit 1;

1640601392 3
也可以用一条实现
select userId,sum(size(split(lcase(content),'iphone'))-1) total
from weibo
group by userId
order by total desc limit 1;

求每天发微博次数最多的那个家伙的ID和发微博的条数

求解步骤：

1. 以每天和userId分组统计每天之中用户发送微博数
   create table weibo10 as
   select from_unixtime(cast(createTime as int), 'yyyy-MM-dd') dt,userId, count(weiboId) total
   from weibo
   group by from_unixtime(cast(createTime as int), 'yyyy-MM-dd'),userId;

2. 使用窗口函数生成以天数为分区，以发送微博数排序的列
   create table weibo10_2 as
   select dt,userId,total,
   row_number() over (distribute by dt sort by tota) as index
   from weibo10;

3. 查询出每天发送微博数排名第一的字段
   select * from weibo10_2 where index<2;

求出所有被多次引用（同一张照片出现在多条微博中，超过1条就算多条）的照片的数目

思路： 以照片的url分组，统计这个分组下的weiboid数。

难点：

1. pic_list字段属于字符串类型，但是被[]包括,要先去除这个括号，再把字符串按照逗号切分成一个数组。
2. 要把照片列表中多个链接分裂之后，才能分组。

create table weibo11 as
select explode(split(substring(pic_list,2,length(pic_list)-2),',')) url
from weibo where pic_list!='[]';

select count(*) total
from weibo11
group by url having total >= 2 order by total;