Hive实战项目——影音网站数据分析

统计 谷粒视频 网站的常规指标，各种 TopN 指标：

对将要处理的数据先进行一次数据清洗，过滤掉不合格的脏数据，同时调整数据的格式

pomxml

ETLUtilMapperjava

ETLUtilDriverjava

处理前数据

处理后数据

gulivideo_ori

guli_user_ori

321 将表中category字段数组行转列

select views,hot from

gulivideo_orc lateral view explode (category) category_t as hot;t1

322 统计每个类别的观看总数

select hot,count()

from t1

group by hot;t2

323 获取观看前10的类别

select hot,total_view

from ()t2

order by total_view desc limit 10;

331 观看数top20视频

select views,category

from gulivideo_orc

order by views desc

limit 20;t1

332 所属类别

select views,category

from t1 lateral view explode(category)ct as category_name;

341 观看数top10,关联视频

select

videoid,views,category,relatedid

from

gulivideo_orc

order by

views desc

limit 50;t1

342 关联视频行转列

select distinct(r_id)

from

t1 lateral view explode(relatedid) relatedtable as r_id;t2

343 视频所属类别

select r_id,gcategory

from

t2join gulivideo_orc g on r_id = gvideoid;t3

select r_id,gcategory

from

t2 join gulivideo_orc g on r_id = gvideoid;t3

344 类别展开

select category_name

from ()t3 lateral view explode(category)t as category_name;t4

345 统计类别个数

select category_name,count() hot

from

t4 group by category_name,t_sum;t5

346 所属类别排名

select from

t5

order by hot desc;t6

1找出上传前10的用户

select uploader,

videos

from

guli_user_orc

order by videos desc

limit 10;t1

2找到上传的所有视频

select t1uploader,

videoid,

views

from

()t1 join gulivideo_orc g

on

tuploader=guploader

order by uploader,views desc; t2

1统计所有类别对应的视频

select

category_name,videoid,views

from

gulivideo_orc

lateral view explode(category) t as category_name;t1

2对每个类观看数排名

select ,rank() over(partition by category_name order by views desc) rank_no

from

()t1;t2

3取前十

select from

()t2

where rank_no<=10;

1．语法

（1）load data:表示加载数据

（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

2．实 *** 案例

（0）创建一张表

（1）加载本地文件到hive

（2）加载HDFS文件到hive中

上传文件到HDFS

加载HDFS上数据

（3）加载数据覆盖表中已有的数据

上传文件到HDFS

加载数据覆盖表中已有的数据

1．创建一张分区表

2．基本插入数据

3．基本模式插入（根据单张表查询结果）

4．多插入模式（根据多张表查询结果）（有问题，只是查询单表不同分区的）

根据查询结果创建表（查询的结果会添加到新创建的表中）

1．创建表，并指定在hdfs上的位置

2．上传数据到hdfs上

3．查询数据

注意：先用export导出后，再将数据导入。

1．将查询的结果导出到本地

2．将查询的结果格式化导出到本地

3．将查询的结果导出到HDFS上(没有local)

基本语法：（hive -f/-e 执行语句或者脚本 > file）

后续。

注意：Truncate只能删除管理表，不能删除外部表中数据

1．全表查询

2．选择特定列查询

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

1．重命名一个列

2．便于计算

3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4．案例实 ***

查询名称和部门

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

（2）where后面不能写分组函数，而having后面可以使用分组函数。

（3）having只用于group by分组统计语句。

空字段赋值

5CASE WHEN

需求

求出不同部门男女各多少人。结果如下：

创建本地emp_sextxt，导入数据

创建hive表并导入数据

按需求查询数据

Order By：全局排序，一个Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2．ORDER BY 子句在SELECT语句的结尾

3．案例实 ***

（1）查询员工信息按工资升序排列

（2）查询员工信息按工资降序排列

按照员工薪水的2倍排序

按照部门和工资升序排序

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。

1．设置reduce个数

2．查看设置reduce个数

3．根据部门编号降序查看员工信息

4．将查询结果导入到文件中（按照部门编号降序排序）

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实 *** ：

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

1）以下两种写法等价

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

hive字符串函数

1 字符串长度函数：length

语法: length(string A)

返回值: int

说明：返回字符串A的长度

举例：

hive> select length('abcedfg') from lxw_dual;

7

2 字符串反转函数：reverse

语法: reverse(string A)

返回值: string

说明：返回字符串A的反转结果

举例：

hive> select reverse(abcedfg') from lxw_dual;

gfdecba

3 字符串连接函数：concat

语法: concat(string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

举例：

hive> select concat('abc','def','gh') from lxw_dual;

abcdefgh

4 带分隔符字符串连接函数：concat_ws

语法: concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

举例：

hive> select concat_ws(',','abc','def','gh') from lxw_dual;

abc,def,gh

5 字符串截取函数：substr,substring

语法: substr(string A, int start),substring(string A, int start)

返回值: string

说明：返回字符串A从start位置到结尾的字符串

举例：

hive> select substr('abcde',3) from lxw_dual;

cde

hive> select substring('abcde',3) from lxw_dual;

cde

hive> selectsubstr('abcde',-1) from lxw_dual; （和ORACLE相同）

e

6 字符串截取函数：substr,substring

语法: substr(string A, int start, int len),substring(string A, intstart, int len)

返回值: string

说明：返回字符串A从start位置开始，长度为len的字符串

举例：

hive> select substr('abcde',3,2) from lxw_dual;

cd

hive> select substring('abcde',3,2) from lxw_dual;

cd

hive>select substring('abcde',-2,2) from lxw_dual;

de

7 字符串转大写函数：upper,ucase

语法: upper(string A) ucase(string A)

返回值: string

说明：返回字符串A的大写格式

举例：

hive> select upper('abSEd') from lxw_dual;

ABSED

hive> select ucase('abSEd') from lxw_dual;

ABSED

8 字符串转小写函数：lower,lcase

语法: lower(string A) lcase(string A)

返回值: string

说明：返回字符串A的小写格式

举例：

hive> select lower('abSEd') from lxw_dual;

absed

hive> select lcase('abSEd') from lxw_dual;

absed

9 去空格函数：trim

语法: trim(string A)

返回值: string

说明：去除字符串两边的空格

举例：

hive> select trim(' abc ') from lxw_dual;

abc

10 左边去空格函数：ltrim

语法: ltrim(string A)

返回值: string

说明：去除字符串左边的空格

举例：

hive> select ltrim(' abc ') from lxw_dual;

abc

11 右边去空格函数：rtrim

语法: rtrim(string A)

返回值: string

说明：去除字符串右边的空格

举例：

hive> select rtrim(' abc ') from lxw_dual;

abc

12 正则表达式替换函数：regexp_replace

语法: regexp_replace(string A, string B, string C)

返回值: string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

举例：

hive> select regexp_replace('foobar', 'oo|ar', '') from lxw_dual;

fb

13 正则表达式解析函数：regexp_extract

语法: regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。

举例：

hive> select regexp_extract('foothebar', 'foo()(bar)', 1) fromlxw_dual;

the

hive> select regexp_extract('foothebar', 'foo()(bar)', 2) fromlxw_dual;

bar

hive> select regexp_extract('foothebar', 'foo()(bar)', 0) fromlxw_dual;

foothebar

注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。

select data_field,

regexp_extract(data_field,'bgStart\\=([^&]+)',1) as aaa,

regexp_extract(data_field,'contentLoaded_headStart\\=([^&]+)',1) as bbb,

regexp_extract(data_field,'AppLoad2Req\\=([^&]+)',1) as ccc

from pt_nginx_loginlog_st

where pt = '2012-03-26'limit 2;

14 URL解析函数：parse_url

语法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])

返回值: string

说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO

举例：

hive> selectparse_url('>

#hive相关资料

#>

目标：

1、掌握hive基础语法、常用函数及其组合使用

2、掌握一些基本业务指标的分析思路与实现技巧

1）某次经营活动中，商家发起了“异性拼团购”，试着针对某个地区的用户进行推广，找出匹配用户。

注意：如果该表是一个分区表，则where条件中必须对分区字段进行限制

2）选出在2018年12月31日，购买的商品品类是food的用户名、购买数量、支付金额

3）试着对本公司2019年第一季度商品的热度与价值度进行分析。

"2019年一月到三月，每个品类有多少人购买，累计金额是多少"

GROUP BY HAVING（分类汇总过滤）

4）找出在2019年4月支付金额超过5万元的用户，给这些VIP用户赠送优惠券

5）2019年4月，支付金额最多的TOP5用户

查看Hive中的函数：show functions；

查看具体函数的用法：1）desc function 函数名；2）desc function extended函数名；

6）

7）用户的首次激活时间，与2019年5月1日的日期间隔。

datediff(string enddate,string startdate):结束日期减去开始日期的天数

拓展:日期增加函数（增加天数）、减少函数（减少天数） —— date_add、date_sub（类型要是string类型的）

date_add（string startdate,int days)、date_sub（string startdate,int days)

case when 函数

8）统计以下四个年龄段20岁以下、20-30岁、30-40岁、40岁以上的用户数

case when 的时候不用加group by，只有在聚合函数的时候才需要group by

if 函数

9）统计每个性别的用户等级高低分布情况（假设level大于5为高级，注意列名不能使用中文的）

10）分析每个月都拉新情况

substring(stringA,INT start,int len),substr(stringA,INT start,int len)，截取起始位置和截取长度

extra1需要解析json字段，然后用$key取出想要获取的value;

extra2使用的是中括号加引号的方式进行字段提取和分组；

两种写法要看字段类型的不同采取不同的方式

11）求不同手机品牌的用户数

12）ELLA用户的2018年的平均每次支付金额，以及2018年最大的支付日期和最小的支付日期的间隔

13）2018年购买的商品品类在两个以上的用户数

步骤总结：

1、先求出每个人购买的商品品类书

2、筛选出购买商品品类书大于2的用户

3、统计符合条件的用户有多少个

14）用户激活时间在2018年，年龄段在20-30岁和30-40岁的婚姻情况分布

步骤总结：

1、先选出激活时间在2018年的用户，并把他们所在的年龄段计算好，并提取出婚姻状况；

如何select后面没有进行聚合，则可以不用使用group by，直接使用where进行过滤就可以；

2、取出年龄段在20-30岁和30-40岁的用户，把他们的婚姻状况转义成可理解的说明；

3、聚合计算，针对年龄段，婚姻状况的聚合

    这一周主要学习了 Hive 的一些基础知识，学习了多个 Hive 窗口函数，虽然感觉这些窗口函数没有实际的应用意义，但还是都了解了一下。

：可以实现在窗口中实现逐行累加

其他 avg、count、min、max 的用法一样

#要先有一个统计出每个月总额的表，这里就是 t_access_amount 表，如下图一

# partition by uid：根据uid 分组，order by month ：根据月份排序，

rows between unbounded preceding and current_row：选择 无边界的前面的行和当前行之间的行，最后是求 sum 即和。得到下图二

# 是在窗口求和，而窗口的定义时按照 uid 分区 order by 排序得到的。得到一个字段

preceding：前面的，后来的，往序号变大的方向

following：往后

2 preceding ：表示前2行

3 following ：表示后3行

unbounded preceding：表示从第1行开始，从前面的起点

unbounded following：表示最后一行，从后面的终点

注意：使用 rows between 时，按order by 顺序编号（没有指定order by 会默认排序）需要左边是我号右边是大编号

注意：上面显示的排序结果不太对，所以看起来好像结果是相反的一样，实际上单独拿出来运行是对的，可以看以相同方式排序的 rn 编号。

注意：这上面这些都不支持使用 rows between 语句，row_number() 展示出来的排序会和查询字段的最后一个 over(order by)里面的顺序一样

：分组 TOPN，即可以分组后排序，便于找到最好的几条数据

eg：有如下数据，要查出每种性别中年龄最大的2条数据

1,18，a，male

2,19，b，male

3,22，c，female

4，16，d，female

5，30，e，male

6，26，f，female

分析：如果使用按性别分组，是可以分出两条数据，但是分组的缺点是只能查出聚合函数（聚合函数只能产生一组中的一个值）和分组依据。而这里是要求多条数据（2个）

：用于将分组数据按照顺序切分成 n 片（不一定是平均），然后每一份都编号为1-n，这样就可以拿到想要那一份数据。如果切片不均匀，默认增加第一个切片的分布，例如，14 条记录切3片就切不好，就会切成 6、4、4，其中6那份编号为1。

注意：可以不指定 order by

应用：

    统计各个 cookieid，pv 数最多的前1/3天的数据

：生成数据项再分组中的排名，排名相等会在名次中留下空位

：生成数据项再分组中的排名，排名相等不会再名次中留下空位

注意：上面两者都需要指定 order by，不然排名都是1

根据上图，可知区别：

    rank()：按顺序编号，相同分组排序有相同的排名，但是会占位，后面的排名隔一位，就是成绩一样名次相同，但是后面的名次得低两位。

    dense_rank()：按顺序编号，相同分组排序有相同的排名，后面排名顺序不边就是有并列第几名的情况。

    row_number() ：按顺序编号，不会有相同的编号，即使分组排序是相同的。

：小于等于当前值的行数/分组内总行数，注意这个要指定排序方式，不然全都是1

：分组内当前行的（rank 值-1）/（分组内总行数-1）

总结：排序、切片、编号、的都需要使用 order by，不然会全都是1，但是除了 row_number() 因为这个函数编号不重复且顺延，所以还是会有编号，但是不确定编号逻辑。

：用于获得窗口内往上第n行的值，n>=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

：与lag 相反，用于获取窗口内往下第n 行的值，n>=0

第一个参数为列名

第二个参数为往上第n行（可选，默认为1）

第三个参数为默认值（当往上第n行为NULL时，取默认值，如不指定，则为NULL）

：取分组内排序后，截止到当前行，最后一个值

：取分组内排序后，第一个值

注意：使用窗口分析函数时，要特别注意 order by 的使用，如果使用的不恰当会导致统计的不是我们想要的。 row_number() over() 的展示排序好像时根据最后一个字段的over（order by）来展示的 。

这几个分析函数通常用于 olap 中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如分 时、分、天、月的 UV 数

：表示结果属于哪一个分组集合，注意中间是两个下划线

第一列时按照 month 进行分组的，

第二列时按照 day 进行分组的

第三列时按照 对应month、day 分组统计出来的结果

第四列 grouping__id 表示这一组结果属于哪个分组集合

注意：grouping sets 里面就是说明以什么分组，上面的group by 是指定可以进行组合的分组字段sets 里面的只能使用这里指定的字段，

如，这里 grouping sets(month , day) 表示分别根据 month、day 字段分组，               grouping sets(month,day,(month,day)) 则表示分别根据 month、day、month和day 分组。  

这里group by 和 grouping sets 可以搭配使用，不是这里的专属，并且这里也可以不用 groupind sets

：根据group by 的维度的所有组合进行聚合，就是省略 grouping sets ，直接将group by 后面的字段以各种可能的形式分组，然后union all 得到结果。

等价于下面的语句

：是 cube 的子集，以最左侧的维度为主，从该维度进行层级聚合

上面可以实现一个叫上钻的效果：

    月天的uv==》月的uv==》总的uv

select orderid,fenjian,timee

from

(

select orderid,fenjian,timee,row_number(orderid,fenjian) rn

from (

select orderid,fenjian,timee from tableName

distribute by orderid,fenjian sort by orderid,fenjian,timee asc

) t1

) t2

where t2rn=1

以上就是关于Hive实战项目——影音网站数据分析全部的内容，包括:Hive实战项目——影音网站数据分析、hive使用教程（2）--数据导入导出、查询与排序、hive怎么将字符型转为数值型等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！