新浪微博大数据分析
**1、组织数据 (需要处理每条数据开头和结尾的中括号)
(1)创建Hive表weibo_json(json string),表只有一个字段,导入所有数据,并验证查询前5条数据 **
create table weibo_json(json string);
load data local inpath '/root/weibo.txt' into table weibo_json;
select * from weibo_json limit 5;
(2)解析完weibo_json当中的json格式数据到拥有19个字段的weibo表中,写出必要的SQL语句
create table weibo as
select json_tuple(json,
'beCommentWeiboId'
,'beForwardWeiboId'
,'catchTime'
,'commentCount'
,'content'
,'createTime'
,'info1'
,'info2'
,'info3'
,'mlevel'
,'musicurl'
,'pic_list'
,'praiseCount'
,'reportCount'
,'source'
,'userId'
,'videourl'
,'weiboId'
,'weiboUrl'
) as
(beCommentWeiboId
,beForwardWeiboId
,catchTime
,commentCount
,content
,createTime
,info1
,info2
,info3
,mlevel
,musicurl
,pic_list
,praiseCount
,reportCount
,source
,userId
,videourl
,weiboId
,weiboUrl
)
from weibo_json;
2、统计微博总量 和 独立用户数
select count(*) from weibo;
+----------+--+
| _c0 |
+----------+--+
| 1451868 |
+----------+--+
select count(*) as Independent from
(select count(*) from weibo group by userId) tmp;
+--------------+--+
| independent |
+--------------+--+
| 78540 |
+--------------+--+
3、统计用户所有微博被转发的次数之和,输出top5用户,并给出次数
#select source from weibo limit 10;
reportCount
select userId,sum(reportCount) as total from weibo group by userId order by total desc limit 5;
+-------------+--------------+--+
| userid | total |
+-------------+--------------+--+
| 1793285524 | 7.6454805E7 |
| 1629810574 | 7.3656898E7 |
| 2803301701 | 6.8176008E7 |
| 1266286555 | 5.5111054E7 |
| 1191258123 | 5.4808042E7 |
+-------------+--------------+--+
4、统计带图片的微博数
select count(*) as total from weibo where pic_list='[]';
+---------+--+
| total |
+---------+--+
| 701356 |
+---------+--+
5、统计使用iphone发微博的独立用户数
select count(*) as total from
(select count(*) as total from weibo where source='iPhone客户端' group by userId) tmp;
+------+--+
| total |
+------+--+
| 921 |
+------+--+
6、将用户所有微博的点赞人数和转发人数相加求和,并将相加之和降序排列,取前10条记录,输出userid和总次数
select userId,(sum(praiseCount)+sum(reportCount)) as total from weibo group by userId order by total desc limit 10;
+-------------+---------------+--+
| userid | total |
+-------------+---------------+--+
| 1793285524 | 1.14941096E8 |
| 1629810574 | 9.761207E7 |
| 1266286555 | 8.3789422E7 |
| 2803301701 | 7.4208822E7 |
| 1195242865 | 6.9292231E7 |
| 1191258123 | 6.1985742E7 |
| 1197161814 | 5.9093308E7 |
| 2656274875 | 5.2380775E7 |
| 2202387347 | 5.1623117E7 |
| 1195230310 | 4.8321083E7 |
+-------------+---------------+--+
7、统计微博中评论次数小于1000的用户ID与数据来源信息,将其放入视图,然后统计视图中数据来源是”ipad客户端”的用户数目
#创建表接收微博中评论次数小于1000的用户ID与数据来源信息
create table small1000 as
select userId,source from weibo where commentCount<1000 group by userId,source;
#查询数据来源是”ipad客户端”的用户数目
select count(*) as total from small1000 where source='iPad客户端';
+--------+--+
| total |
+--------+--+
| 537 |
+--------+--+
**8、统计微博内容中出现”iphone”次数最多的用户,
最终结果输出用户id和次数(注意:该次数是”iphone”的出现次数,
不是出现”iphone”的微博数目) **
select userId,count(userid) from weibo where content like '%iphone%' group by userId;
9、求每天发微博次数最多的那个家伙的ID和发微博的条数
#时间函数,原表存储的数据的时间是字符串的时间戳,所以需要将其转成长整型bigint
from_unixtime(cast(createTime as bigint)
#首先获取数据的userId,以及时间并转换为"yyyy-MM-dd"格式
select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo;
#然后按照时间,用户进行分组查询,获取每日用户发布微博数量,这里定义该查询结果为a
select tmp.Release_time as Release_time,
tmp.userId as userId,
count(tmp.userId) as total
from
(select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
group by tmp.userId,tmp.Release_time;
#从a中按照获取的时间再进行分组查询,获取当日发布微博最大值,这里将查询结果定义为b
select tmp1.Release_time as Release_time,max(tmp1.total) as big from
(select tmp.Release_time as Release_time,
tmp.userId as userId,
count(tmp.userId) as total
from
(select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
group by tmp.userId,tmp.Release_time) tmp1
group by tmp1.Release_time;
#最后b与a进行内连接,以获取导userId
select a.Release_time as Release_time,b.userId as userId,a.big as total from
(select tmp1.Release_time as Release_time,max(tmp1.total) as big from
(select tmp.Release_time as Release_time,
tmp.userId as userId,
count(tmp.userId) as total
from
(select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
group by tmp.userId,tmp.Release_time) tmp1
group by tmp1.Release_time) a
join
(select tmp.Release_time as Release_time,
tmp.userId as userId,
count(tmp.userId) as total
from
(select userId,from_unixtime(cast(createTime as bigint),"yyyy-MM-dd") as Release_time from weibo) tmp
group by tmp.userId,tmp.Release_time) b
on a.Release_time=b.Release_time and a.big=b.total;