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;

Hive的insert语句能够从查询语句中获取数据，并同时将数据Load到目标表中。现在假定有一个已有数据的表staged_employees（雇员信息全量表），所属国家cnty和所属州st是该表的两个属性，我们做个试验将该表中的数据查询出来插入到另一个表employees中。

INSERT OVERWRITE TABLE employees

PARTITION (country = '中国', state = '北京')

SELECT FROM staged_employees se

WHERE secnty = '中国' AND sest = '北京';

由于使用了OVERWRITE关键字，目标表中原来相同partition中的所有数据被覆盖，如果目标表中没有partition，则整个表会被覆盖。

如果把OVERWRITE关键字删掉，或者替换成INTO，则hive会追加而不是替代原分区或原表中的数据，这个特性在Hive v080之后才支持。

当数据已经存在于hdfs上但不是我们想要的格式的时候，当进行的计算需要分好多步骤有必要存储中间数据的时候，或者原数据没有分区、有很多无效列需要过滤的时候，可以使用insertselect句型来完成这一转换过程。

由于一个国家有很多个省份，如果想根据（国家country，地区partition）两个维度对数据进行分区的话，这条SQL语句的执行个数应该等于地区的数目，比如中国有23个省就要对该SQL语句执行23次。因此hive对这个SQL语句进行了改造，只需要扫描一次原表就可以生成不同的输出（多路输出）。比如下面的SQL语句扫描了一次原始数据表，但是同时生成了3个省份的结果数据：

FROM staged_employees se

INSERT OVERWRITE TABLE employees

PARTITION (country = '中国', state = '河北省')

SELECT WHERE secnty = '中国' AND sest = '河北省'

INSERT OVERWRITE TABLE employees

PARTITION (country = '中国', state = '陕西省')

SELECT WHERE secnty = '中国' AND sest = '陕西省'

INSERT OVERWRITE TABLE employees

PARTITION (country = '中国', state = '河南省')

SELECT WHERE secnty = 'US' AND sest = '河南省';

通过缩进可以很清楚的看到，我们扫描了一次staged_employees表但是执行了3次不同的insert语句，这条大SQL语句是这么执行的：先通过from staged_employees表获取一条记录，然后执行每一个select子句，如果select子句验证通过则执行相应的insert语句。注意这里的三条select子句是完全独立执行的，并不是if then else的关系，这就意味着这3条select子句在某种情况下可能同时通过where检测。

通过这种结构，原始表的数据能被拆分到目标表的不同partition中去。

如果原表的一条记录满足于其中一个给定的select where 子句，则该记录将被写到目标表的固定分区中。其实更进一步，每条Insert语句能将数据写到不同的数据表中，不管这个表是否分区都一样。

于是，就像一个过滤器一样，原表的一些数据被写到了很多输出地址，而剩下的数据会被丢弃。

当然，你也可以混用Insert overwrite和insert into两种不同的方法写出数据。

向动态分区插入数据

但是问题还是没有解决，中国有23个省，那么我们就需要写23个insert into select where子句，这非常不现实。于是hive的一种叫做动态分区的特性就出现了，它能够根据select出来的参数自动推断将数据插入到那个分区中去。本文上面的两种SQL语句设定分区的方式都叫做静态分区插入。

将上一个SQL语句进行改动，会得到以下简洁的新SQL语句：

INSERT OVERWRITE TABLE employees

PARTITION (country, state)

SELECT , secnty, sest

FROM staged_employees se;

hive先获取select的最后两个位置的secnty和sest参数值，然后将这两个值填写到Insert语句partition中的两个country和state变量中，即动态分区是通过位置来对应分区值的。原始表select出来的值和输出partition的值的关系仅仅是通过位置来确定的，和名字并没有关系，比如这里secnty和county的名称完全没有关系。

上面的这条SQL语句是对两个分区同时进行了动态设定，如果staged_employees表中有100个国家，每个国家有100个地区，那么该SQL语句自动对每个国家和地区建立相应的partition并插入数据，如果用手写的话不现实。

只要位置正确，你可以混用动态分区和静态分区值设定，比如下面这个例子，你可以静态指定一个country值，但是state值采用动态的方法设定：

INSERT OVERWRITE TABLE employees

PARTITION (country = 'US', state)

SELECT , secnty, sest

FROM staged_employees se

WHERE secnty = 'US';

注意：静态分区值必须在动态分区值的前面！

目标：

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、聚合计算，针对年龄段，婚姻状况的聚合

1、使用Tez引擎

Apache Tez Engine是一个可扩展的框架，用于构建高性能批处理和交互式数据处理。它由YARN在Hadoop中 调度。Tez通过提高处理速度和保持MapReduce扩展到数PB数据的能力来改进MapReduce job。

通过设置hiveexecutionengine 为tez：可以在环境中启用Tez引擎：

set hiveexecutionengine=tez;

2、使用向量化

向量化通过在单个 *** 作中获取 1024 行而不是 每次只获取单行来改善 scans, aggregations, filters 和 join 这类 *** 作的性能。

我们可以通过执行以下命令在环境中启用向量化：

set hivevectorizedexecutionenabled=true;

set hivevectorizedexecutionreduceenabled=true;

3、使用ORCFile

Hive 支持 ORCfile，这是一种新的表存储格式，在读取，写入和处理数据时，ORCFile格式优于Hive文件格式，它通过 predicate push-down, compression 等技术来提高查询速度。

在 HIVE 表中使用 ORCFile，将有益于获得 HIVE 快速响应的查询。

ORCFile 格式通过对原始数据存储量压缩75％，提供了高效的存储 Hive 数据的方法。

举例，考虑两个大表 A 和 B（存储为 TextFIle，这里没有指定一些列），使用一个简单的查询，如：

SELECT AcustomerID,

Aname,

Aage,

Aaddress

JOIN Brole,

Bdepartment,

Bsalary ON AcustomerID=BcustomerID;

由于表 A 和表 B 都存储为 TextFile，因此执行此查询可能需要很长时间。

将这些表存储格式转换为 ORCFile 格式通常会明显减少查询时间：

CREATE TABLE A_ORC (

customerID int,

name string,

age int,

address string

) STORED AS ORC tblproperties (“orccompress" = “SNAPPY”)

;

INSERT INTO TABLE A_ORC

SELECT

FROM A

;

CREATE TABLE B_ORC (

customerID int,

ROLE string,

salary float,

department string

) STORED AS ORC tblproperties (“orccompress" = “SNAPPY”)

;

INSERT INTO TABLE B_ORC

SELECT

FROM B

;

SELECT A_ORCcustomerID,

A_ORCname,

A_ORCage,

A_ORCaddress

JOIN B_ORCrole,

B_ORCdepartment,

B_ORCsalary ON A_ORCcustomerID=B_ORCcustomerID

;

ORC 支持压缩存储（使用 ZLIB 或如上所示使用 SNAPPY），但也支持不压缩存储。

4、使用分区

通过分区，数据存储在 HDFS 上的单独单个文件夹中。Hive 将查询分区数据集，而不是 扫描表的所有数据集。

创建临时表并将数据加载到临时表中

CREATE TABLE Employee_Temp(

EmloyeeID int,

EmployeeName Varchar(100),

Address Varchar(100),

STATE Varchar(100),

City Varchar(100),

Zipcode Varchar(100)

) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

LOAD DATA INPATH '/home/hadoop/hive' INTO TABLE Employee_Temp;

创建分区表

Create Table Employee_Part(

EmloyeeID int,

EmployeeName Varchar(100),

Address Varchar(100),

State Varchar(100),

Zipcode Varchar(100))

PARTITIONED BY (City Varchar(100))

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORED AS TEXTFILE;

启用动态分区的命令

SET hiveexecdynamicpartition = true;

SET hiveexecdynamicpartitionmode = nonstrict;

从临时表导入数据到分区表

INSERT Overwrite TABLE Employee_Part Partition(City)

SELECT EmployeeID,

EmployeeName,

Address,

STATE,

City,

Zipcode

FROM Emloyee_Temp;

5、使用 分桶

桶表介绍：>

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

在过去几年中，主要受到围绕Stinger计划的Hive社区创新的推动，Hive查询时间得到了显着改善，使Hive能够以速度和规模支持批量和交互式工作负载。

但是，许多使用者仍然不熟悉以最快速度运行Hive查询的基本技术和最佳实践。本文中，将重点介绍一些常使用的简单技术，以提高HIVE查询的性能。

Hive可以使用Apache Tez执行引擎而不是Map-reduce引擎。不会详细介绍这里提到的使用Tez的许多好处; 相反，提出一个简单的建议：如果在您的环境中默认情况下没有打开它，请在Hive查询的开头使用Tez设置为“true”

Hive支持ORCfile，这是一种新的表存储格式，通过谓词下推，压缩等技术实现极佳的速度提升。

对每个HIVE表使用ORCFile应该是一个明智的选择，对于获得HIVE查询的快速响应时间非常有益。

作为一个例子，考虑两个大表A和B（存储为文本文件，这里没有指定一些列），以及一个简单的查询 :

此查询可能需要很长时间才能执行，因为表A和B都存储为TEXT。将这些表转换为ORCFile格式通常会显着缩短查询时间：

ORC支持压缩存储（使用ZLIB或如上所示使用SNAPPY），但也支持未压缩存储。

将基表转换为ORC通常是取决于所在团队获取数据的职责，由于其他优先级，可能需要一些时间来更改完整的获取数据过程。ORCFile的好处是如此明显，以至于推荐如上所示的自助式方法 - 将A转换为A_ORC，将B转换为B_ORC并以此方式进行连接，以便立即从更快的查询中受益，而不依赖于其他团队。

矢量化查询执行通过一次批量执行1024行而不是每行一行来提高扫描，聚合，过滤器和连接等 *** 作的性能。

这个功能在Hive 013中引入，显着缩短了查询执行时间，并且可以通过两个参数设置轻松启用：

在提交最终执行之前，Hive会优化每个查询的逻辑和物理执行计划。这些优化不是基于查询的成本 - 也就是说，直到运行时。

最近添加到Hive，基于成本的优化，基于查询成本执行进一步优化，从而导致可能不同的决策：如何订购联接，执行哪种类型的联接，并行度等。

要使用基于成本的优化（也称为CBO），请在查询开头设置以下参数

然后，通过运行Hive的“analyze”命令为CBO准备数据，以收集我们想要使用CBO的表的各种统计信息。

例如，在tweet数据表中，希望收集有关该表的统计信息以及大约2列：“sender”和“topic”：

使用HIVE 014（在HDP 22上），analyze命令的工作速度要快得多，而且您不需要指定每一列，因此只需如下：

现在使用此表执行查询应该会导致不同的执行计划由于成本计算和Hive创建的不同执行计划而更快。

SQL是一种强大的声明性语言。与其他声明性语言一样，编写SQL语句的方法不止一种。尽管每个语句的功能都相同，但它可能具有截然不同的性能特征

每条记录代表一次点击事件，希望找到每个sessionID的最新网址。

有人使用如下方式：

在上面的查询中，构建一个子查询来收集每个会话中最新事件的时间戳，然后使用内部联接来过滤掉其余的事件。

虽然查询是一个合理的解决方案 - 从功能的角度来看 - 事实证明，有一种更好的方法来重写这个查询，如下所示

在这里，使用Hive的OLAP功能（OVER和RANK）来实现相同的功能，但没有使用表连接。

显然，删除不必要的连接几乎总能带来更好的性能，而且当使用大数据时，这比以往任何时候都更重要。在很多情况下查询不是最优的 - 所以仔细查看每个查询并考虑重写是否可以使它更好更快。

更多内容信息 >

1、粘贴就能用。获取基础时间数据(注意我的数据是导出到桌面上，你可以导出到HDFS上也可以请细看代码注释)。

2、上传文件到Linux建表转存到Hive中。

3、建时间维度表。

4、经过Hsql时间函数转换得到各种各样的时间导入数据到Hive的时间维表中。

5、检查数据。

6、Mysql建表，使用sqoop导出数据。

7、检查Mysql数据即可导出时间字段string。

#hive相关资料

#>

以上就是关于Hive实战项目——影音网站数据分析全部的内容，包括:Hive实战项目——影音网站数据分析、如何用数字 取hive表某列值、HiveSQL核心技能之常用函数等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！