利用hive對微博數(shù)據(jù)統(tǒng)計分析案例

數(shù)據(jù)樣例：

[{"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 是否是轉(zhuǎn)發(fā)微博
catchTime 抓取時間
commentCount 評論次數(shù)
content 內(nèi)容
createTime 創(chuàng)建時間
info1 信息字段1
info2信息字段2
info3信息字段3
mlevel no sure
musicurl 音樂鏈接
pic_list 照片列表（可以有多個）
praiseCount 點贊人數(shù)
reportCount 轉(zhuǎn)發(fā)人數(shù)
source 數(shù)據(jù)來源
userId 用戶id
videourl 視頻鏈接
weiboId 微博id
weiboUrl 微博網(wǎng)址

題目

  1. 組織數(shù)據(jù)
    （創(chuàng)建Hive表weibo_json(json string)，表只有一個字段，導入所有數(shù)據(jù)，并驗證查詢前5條數(shù)據(jù)）
    （解析完weibo_json當中的json格式數(shù)據(jù)到擁有19個字段的weibo表中，寫出必要的SQL語句）

  2. 統(tǒng)計微博總量 和 獨立用戶數(shù)

  3. 統(tǒng)計用戶所有微博被轉(zhuǎn)發(fā)的次數(shù)之和，輸出top5用戶，并給出次數(shù)

4.統(tǒng)計帶圖片的微博數(shù)

  1. 統(tǒng)計使用iphone發(fā)微博的獨立用戶數(shù)

  2. 將微博的點贊人數(shù)和轉(zhuǎn)發(fā)人數(shù)相加求和，并將相加之和降序排列，取前10條記錄，輸出userid和總次數(shù)

  3. 統(tǒng)計微博中評論次數(shù)小于1000的用戶ID與數(shù)據(jù)來源信息，將其放入視圖，然后統(tǒng)計視圖中數(shù)據(jù)來源是”ipad客戶端”的用戶數(shù)目

  4. 統(tǒng)計微博內(nèi)容中出現(xiàn)”iphone”次數(shù)最多的用戶，最終結(jié)果輸出用戶id和次數(shù)（注意：該次數(shù)是”iphone”的出現(xiàn)次數(shù)，不是出現(xiàn)”iphone”的微博數(shù)目）

  5. 求每天發(fā)微博次數(shù)最多的那個家伙的ID和發(fā)微博的條數(shù)

  6. 求出所有被多次引用（同一張照片出現(xiàn)在多條微博中，超過1條就算多條）的照片的數(shù)目

解題

組織數(shù)據(jù)

// 創(chuàng)建庫：
create database weibo;
use weibo;

// 創(chuàng)建表：
create table weibo_json(json string);

// 導入數(shù)據(jù)：
load data local inpath '/home/hadoop/weibojson.data.json' into table weibo_json;

// 驗證：
select json from weibo_json limit 5;

// 創(chuàng)建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';

插入數(shù)據(jù)

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;

統(tǒng)計用戶所有微博被轉(zhuǎn)發(fā)的次數(shù)之和，輸出top5用戶，并給出次數(shù)。注意：一個用戶可能發(fā)過多個微博

思路：

  1. 以用戶id分組，求轉(zhuǎn)發(fā)和
  2. 按照轉(zhuǎn)發(fā)量排序
    select sum(reportCount) sumrep
    from weibo
    group by userId
    order by sumrep desc limit 5;

結(jié)果
2721667
518676
477742
430532
415424

5、統(tǒng)計帶圖片的微博數(shù)（7分）
圖片字段pic_list
select count(weiboId) total
from weibo
where instr(pic_list,'http')>0
結(jié)果：

5278

統(tǒng)計使用iphone發(fā)微博的獨立用戶數(shù)

數(shù)據(jù)來源字段：source
select count(distinct userId)
from weibo
where instr(lcase(source),'iphone')>0;

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

將微博的點贊人數(shù)和轉(zhuǎn)發(fā)人數(shù)相加求和，并將相加之和降序排列，取前10條記錄，輸出userid和總次數(shù)

思路：
以userid分組，統(tǒng)計

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

結(jié)果：
14328
620
516
472
428
340
308
226
210
188

統(tǒng)計微博中評論次數(shù)小于1000的用戶ID與數(shù)據(jù)來源信息，將其放入視圖，然后統(tǒng)計視圖中數(shù)據(jù)來源是”ipad客戶端”的用戶數(shù)目

思路：

  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 '%皮皮%';

統(tǒng)計微博內(nèi)容中出現(xiàn)”iphone”次數(shù)最多的用戶，最終結(jié)果輸出用戶id和次數(shù)（注意：該次數(shù)是”iphone”的出現(xiàn)次數(shù)，不是出現(xiàn)”iphone”的微博數(shù)目）

思路

  1. 以iPhone為分隔符使用split切分來源之后轉(zhuǎn)換為數(shù)組，統(tǒng)計數(shù)組size

  2. 以userid分組最后統(tǒng)計用戶所有出現(xiàn)的次數(shù)

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
也可以用一條實現(xiàn)
select userId,sum(size(split(lcase(content),'iphone'))-1) total
from weibo
group by userId
order by total desc limit 1;

求每天發(fā)微博次數(shù)最多的那個家伙的ID和發(fā)微博的條數(shù)

求解步驟：

  1. 以每天和userId分組統(tǒng)計每天之中用戶發(fā)送微博數(shù)
    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. 使用窗口函數(shù)生成以天數(shù)為分區(qū)，以發(fā)送微博數(shù)排序的列
    create table weibo10_2 as
    select dt,userId,total,
    row_number() over (distribute by dt sort by tota) as index
    from weibo10;

  3. 查詢出每天發(fā)送微博數(shù)排名第一的字段
    select * from weibo10_2 where index<2;

求出所有被多次引用（同一張照片出現(xiàn)在多條微博中，超過1條就算多條）的照片的數(shù)目

思路： 以照片的url分組，統(tǒng)計這個分組下的weiboid數(shù)。

難點：

  1. pic_list字段屬于字符串類型，但是被[]包括,要先去除這個括號，再把字符串按照逗號切分成一個數(shù)組。
  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;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 寂寞梧桐，深閨中。 黃銅鏡里的我，傾國花容上滿是憔悴，人比黃花瘦。王，就這樣霸占了我僅有的心靈。...
    拉菲噶地版納奇怪閱讀 520評論 3贊 0
  • 在大學里見到了許多有個性的老師，特別是文學院里有一群活在自己世界里的老師。向來不喜歡文鄒鄒的老師，也不喜歡詩詞歌賦...
    比很久還要久Q閱讀 189評論 0贊 0
  • 九成宮禮泉銘，我已經(jīng)臨摹了一年了，算一算練習學習書法也有快10年多了，書法沒有太大進步，生活習慣已經(jīng)離不開毛筆了。...
    愛孕安馨劉建民閱讀 344評論 0贊 0
  • 現(xiàn)在時間8:23，心可以跟隨身體坐下來休息一下了。 現(xiàn)在時間晚上10:57了，相隔了這么久才繼續(xù)簡...
    愛雯雯閱讀 242評論 0贊 0
  • 紫月砂雪閱讀 334評論 0贊 2

友情鏈接更多精彩內(nèi)容