从只会单表查询到应用hive完成日常数据需求：hive入门到应用实战

---

前言

笔者刚刚完成了将近半年的实习，从学校中只会单表查询，很少写复杂sql到现在可以接日常数据需求，到现在完成多条上千行的HiveSQL数据需求，中途学习了很多hive的用法。本篇博客作为所有hive相关的笔记整理，以作未来复习所用，或为和我一样的大数据入门选手作为参考，或为大数据老手们提供一个参考。
本文整理了多篇hive相关博客的内容，希望各位看完有所收获。
本文不涉及架构和hive原理，这些内容以后再写博客来记录，只做应用层面的分析和记录。

---

1. 语法补充

hive查看表信息

hive查看表结构： desc table_name;

查看表详细属性： desc formatted test;

hive查看某个参数的设置

举例来说我想看mapreduce.input.fileinputformat.split.maxsize目前设置的默认值

set mapreduce.input.fileinputformat.split.maxsize;即可

hive grouping_ID

等价于将不同维度的GROUP BY结果集进行UNION ALL 原文地址 https://www.cnblogs.com/qingyunzong/p/8798987.html

select   month,  day,  count(distinct cookieid) as uv,  GROUPING__ID 
from cookie.cookie5  
group by month,day  grouping sets (month,day)  
order by GROUPING__ID; 

等价于 

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID 
FROM cookie5 
GROUP BY month  
UNION ALL  
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID 
FROM cookie5 
GROUP BY day

SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b), a)

等价于

SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM tab1 GROUP BY a

hive qube

根据GROUP BY的维度的所有组合进行聚合，其实就是相当于把grouping_ID的所有可能都列出来

原文地址 https://www.cnblogs.com/qingyunzong/p/8798987.html

SELECT  month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID  
FROM cookie5  
GROUP BY month,day  WITH CUBE  
ORDER BY GROUPING__ID; 

等价于 

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID 
FROM cookie5 
UNION ALL  
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID 
FROM cookie5 
GROUP BY month  
UNION ALL  
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID 
FROM cookie5 
GROUP BY day 
UNION ALL  
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID 
FROM cookie5 
GROUP BY month,day

hive rollup

是CUBE的子集，以最左侧的维度为主（group by后的第一个元素），从该维度进行层级聚合；

原文地址 https://www.cnblogs.com/qingyunzong/p/8798987.html

SELECT  month, day, COUNT(DISTINCT cookieid) AS uv, GROUPING__ID   
FROM cookie5  
GROUP BY month,day WITH ROLLUP  
ORDER BY GROUPING__ID;

hive left semi join （左半连接）

是 IN/EXISTS 子查询的一种更高效的实现。本质上就是通过join的B表来筛选A，而不引入B表中的数据，同时可以提高效率。

示例

SELECT A.KEY,A.VALUE FROM A WHERE A.KEY IN (SELECT B.KEY FROM B )

可以改写为

SLECT A.KEY,A.VALUE FROM A LEFT SEMI JOIN B ON A.KEY=B.KEY 

注意：

1. 只能在 ON 子句中设置过滤条件

2. 因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。

hive row_number() over()

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

select id,name,age,salary,row_number()over(order by salary desc) rn
from TEST_ROW_NUMBER_OVER t

select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t

结合实例： https://blog.csdn.net/qq_25221835/article/details/82762416

注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。

hive cast函数切换字段的格式

这种方法经常用在转化日期格式等方面

select cast(date as char) as date from table1;

hive lateral view explode

本质上就是一个列转行的操作，一个字段对应一堆字段：逃出绝命镇：剧情，悬疑，惊悚，这种情况下，我想拆分成逃出绝命镇：剧情 逃出绝命镇：悬疑 逃出绝命镇：惊悚，就需要使用这个方法了

select a.uid, t.user_channel_id as user_channel_id                

from qube_dw.dm_d_mp_user_snap a              

LATERAL VIEW explode (split(a.user_channel_id,',')) t as user_channel_id               

where a.dt = '2021-07-01'

LATERAL VIEW 写在where之前

更多详细参考：

https://zhuanlan.zhihu.com/p/115913870

https://blog.csdn.net/bitcarmanlee/article/details/51926530

hive concat拼接字符串

concat

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

返回值: AB

这个函数会对所有参数进行拼接，但是遇到其中一个参数是null，concat的结果也会为null；其中一个参数为 ‘’ 则concat结果会不为null但是会为 ‘’ 。所以如果有空值或 ‘’ 需要提前处理。

举例：Hive> select concat(‘abc’,'def’,'gh’) from lxw_dual;

返回值：abcdefgh

concat_ws

CONCAT_WS(separator,str1,str2,…)：这个函数会实现使用第一个参数作为分隔，连接后面的参数，这个函数不会忽略 ‘’ 但是会忽略null，所以cancat_ws的结果不会返回空值。

hive nvl相关函数

NVL(expr1,expr2)：第一个参数不为null则显示第一个，否则显示第二个（如果第二个也为null仍然会出现显示null的情况）

NVL2(expr1,expr2, expr3)：第一个参数为null则显示第二个值，第一个参数不为null则显示第三个值

Coalesce(expr1, expr2, expr3….. exprn)：第一个参数为null则显示第二个值，第二个为null显示第三个，第三个为null显示第四个…

hive日期比较函数

1.日期比较函数:

datediff语法: datediff(string enddate,string startdate)

返回值: int 说明: 返回结束日期减去开始日期的天数。

举例：hive> select datediff('2016-12-30','2016-12-29');

2.日期增加函数:

date_add语法: date_add(string startdate, intdays)

返回值: string 说明: 返回开始日期startdate增加days天后的日期。

举例：hive>select date_add('2016-12-29',10); 返回值：2017-01-08

3.日期减少函数:

date_sub语法: date_sub (string startdate,int days)

返回值: string 说明: 返回开始日期startdate减少days天后的日期。

举例： hive>select date_sub('2016-12-29',10); 返回值：2016-12-19

4.查询近30天的数据

select * from table where datediff(current_timestamp,create_time)<=30；

create_time 为table里的字段，current_timestamp 返回当前时间 2021-06-01 11:00:00

---

2. hive表操作

Hive列操作（增加列，删除列，修改列，移动列）

1. 向Hive表中添加某个字段

格式：alter table 表名 add columns (字段名 字段类型 comment ‘字段描述’)；

例1：alter table table_name add columns (now_time string comment '当前时间');

例2：alter table table_name add columns (now_time varchar(300) comment '当前时间');

2. 在Hive表中指定位置添加字段

分两步，先添加字段到最后（add columns），然后再移动到指定位置（change）

alter table table_name add columns (c_time string comment '当前时间'); – 正确，添加在最后

alter table table_name change c_time c_time string after address ; – 正确，移动到指定位置,address字段的后面

如果表是一个分区表，必须使用alter table tb add columns(col1 string) cascade;来增加字段

否则会出现旧分区中的col1将为空且无法更新，即便insert overwrite该分区也不会生效的问题

3. Hive中修改表中列的名称

ALTER TABLE table_name CHANGE 旧列名 新列名 字段类型;

例1：alter table DWD_ORC_ENTRY_XF change id taskid varchar(300);

注意：这里字段和字段类型可以一起修改。

4. 修改Hive表的名字

alter table name rename to new_Name;

5. 修改表的备注

ALTER TABLE 数据库名.表名 SET TBLPROPERTIES(‘comment’ = ‘新的表备注’);

例1、ALTER TABLE curtis.comment_table_test SET TBLPROPERTIES('comment' = '新的表备注');

6. 删除Hive表中某个字段

CREATE TABLE test ( a STRING, b BIGINT, c STRING, d STRING, e BIGINT, f BIGINT );

如果需要删除 column f 列，可以使用以下语句：

ALTER TABLE test REPLACE COLUMNS ( a STRING, b BIGINT, c STRING, d STRING, e BIGINT );

hive insert插入

重写： insert overwrite table test_insert select * from test_table;

追加： insert into table test_insert select * from test_table;

hive删除各种信息

1、hive删除表内容

truncate table 表名;

2、hive删除表

drop table if exists 表名;

3、hive删除数据库

drop database if exists 库名;

4、删除hive分区

alter table table_name drop partition (partition_name='分区名')

5、删除hive所有分区

ALTER TABLE schedule_events drop if exists partition (year>'0');

hive外部表

hive 外部表

hive删除外部表和内部表： drop table t1;

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；

区别：

内部表数据由Hive自身管理，外部表数据由HDFS管理；

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；

删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复（MSCK REPAIR TABLE table_name;）

hive内部表外部表相互转换

hive 外部表 内部表外部表相互转换：

alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE'); //内部表转外部表

alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE'); //外部表转内部表

详细内容参考博客：

https://blog.csdn.net/qq_36743482/article/details/78393678?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultBlogCommendFromMachineLearnPai2_{default-2.base&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2}default_{BlogCommendFromMachineLearnPai2}default-2.base

知识补充：sql中的视图,with as和临时表

定义视图可以将表与表之间的复杂的操作连接和搜索条件对用户不可见，用户只需要简单地对一个视图进行查询即可，故增加了数据的安全性，但不能提高查询效率。Hive视图是一种无关底层存储的逻辑对象。视图中的数据是SELECT查询返回的结果。在视图选定后才会开始执行SELECT查询。需要注意的是，视图是只读的，不能向视图中插入或是加载数据。 视图是一个虚表。数据库中只存放视图的定义，而不存放视图包含的数据，这些数据仍存放在原来的基表中。所以基表中的数据如果发生改变，从视图中查询出的数据也随之改变。

临时表存在于tempdb中，临时表有两种类型：本地表和全局表。在与首次创建或引用表时相同的 SQL Server 实例连接期间，本地临时表只对于创建者是可见的。当用户与 SQL Server 实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与 SQL Server 实例断开连接后，将删除全局临时表。 临时表是一种并不存储在数据库当中的基表。与之相反的是，临时表只存在于创建该临时表的数据库会话被激活的情况下。 临时表存储在数据库会话中，不在数据库中，当使用show tables 时没有显示，但是可以查询，当关闭数据库（quit,exit）然后在打开在去数据库中查询的时候就不存在了。

with as 只是把子查询的语句当作了一个表，但是真实的数据并没有插入到数据库，它的好处是增加了代码的可读性和进行维护。用with as ,其实跟直接用子查询效率上没有什么区别；而用临时表与永久表相似，数据是真是跑入到数据库里面去的，相当于第二次直接关联的是一个小表，查询效率大大提高。

---

3. HDFS操作

hdfs 常用命令

建议直接参考博客

https://blog.csdn.net/WQY992/article/details/89002269

hdfs 利用模糊匹配批量删除文件

牢记*是模糊查询字符，匹配任意字符

hdfs dfs -rm /user/mp_qube/done/hive/qube_dw/app_d_suike_user_growth_pull_new_suabel_var_mode}/2021-*

---

4. hive优化相关

hive where join on顺序问题

正常来说顺序应该是on是最先执行，where次之，having最后，但是在hive中有一个谓词下推优化的配置项hive.optimize.ppd，默认值true，这一项开启的时候可能会先进行where的操作，这也是优化hiveSQL的一种方法，举例来看

select xxx from test_table_1 a join test_table_2 b on a.dt = b.dt where a.dt = '2021-01-01'

这段sql就会先将a表的内容限制在dt = '2021-01-01’然后再进行a表和b表的匹配，这在很多时候是巨大的性能提升，因为a可能是一张dt的分区表，限定了a的dt分区极大地节省了搜索花费的时间，如果不限制where，只在on中限制条件，可能会让mapreduce无法运行

hive自带优化

当a表join b表 join c表，on字段为a.id = b.id,a.id = c.id时，Correlation Optimizer优化器会帮我们把多个mapreduce任务合并为一个

hive解决数据倾斜

首先如果不需要空值或-1或异常值，需要提前过滤掉以提高效率

这种情况很常见，比如当事实表是日志类数据时，往往会有一些项没有记录到，我们视情况会将它置为null，或者空字符串、-1等。如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度。 因此，若不需要空值数据，就提前写where语句过滤掉。

需要保留的话，将空值key用随机方式打散，例如将用户ID为null的记录随机改为负值：

select a.uid,a.event_type,b.nickname,b.age 
from 
	(select  (case when uid is null then cast(rand()*-10240 as int) else uid end) as uid,  event_type 
 	from calendar_record_log 
 	where pt_date >= 20190201 ) a 
left outer join 
	( select uid,nickname,age from user_info where status = 4 ) b 
on a.uid = b.uid;

hive优化 join

小表叫build table，大表叫probe table Hive在解析带join的SQL语句时，会默认将最后一个表作为probe table，将前面的表作为build table并试图将它们读进内存。如果表顺序写反，probe table在前面，引发OOM的风险就高了

hive控制reduce的数量

hive.exec.reducers.bytes.per.reducer

这个参数控制一个job会有多少个reducer来处理，依据的是输入文件的总大小。默认1GB。

hive.exec.reducers.max

这个参数控制最大的reducer的数量， 如果 input / bytes per reduce > max 则会启动这个参数所指定的reduce个数。 这个并不会影响mapre.reduce.tasks参数的设置。默认的max是999。

mapred.reduce.tasks

这个参数如果指定了，hive就不会用它的estimation函数来自动计算reduce的个数，而是用这个参数来启动reducer。默认是-1.

如果我们不指定mapred.reduce.tasks， hive会自动计算需要多少个reducer。 计算的公式： reduce个数 = InputFileSize / bytes per reducer

hive控制map的数量

可以直接通过参数mapred.map.tasks（默认值2）来设定mapper数的期望值，但它不一定会生效

设输入文件的总大小为total_input_size。HDFS中，一个块的大小由参数dfs.block.size指定，默认值64MB或128MB。在默认情况下，mapper数就是：

default_mapper_num = total_input_size / dfs.block.size。

参数mapred.min.split.size（默认值1B）和mapred.max.split.size（默认值64MB）分别用来指定split的最小和最大大小。

split大小和split数计算规则是： split_size = MAX(mapred.min.split.size, MIN(mapred.max.split.size, dfs.block.size))；

split_num = total_input_size / split_size。

得出mapper数：mapper_num = MIN(split_num, MAX(default_num, mapred.map.tasks))。

可见，如果想减少mapper数，就适当调高mapred.min.split.size，split数就减少了。如果想增大mapper数，除了降低mapred.min.split.size之外，也可以调高mapred.map.tasks。

---

5. hive骚操作

hive 所有行去重

方法1：

select distinct * from table(表名) where (条件)

方法2：

group by 所有字段

select a,b,c 
from table_tmp 
group by a,b,c

hive 不使用count()快速查某个表中的行数

explain select * from test_tab where dt = '2021-07-18';

这个方法不仅仅可以检查某个表行数，还可以展示根据当前的hive语句生成的任务，我们也可以通过explain来检查我们的sql是否有语法错误，以及我们的mapreduce任务哪里会出问题，以进行优化

hive 时间戳与日期相互转换方法

注意尽量不要使用带格式的写法，否则可能会出现不存在的日期

代码： select from_unixtime(cast(first_enter_pool_time as int),'YYYY-MM-DD HH-mm-ss') from table_name where dt='2021-07-18' ;

修改后： select from_unixtime(cast(first_enter_pool_time as int)) from table_name where dt='2021-07-18' ;

相关参考资料：https://blog.csdn.net/HappyRocking/article/details/80854778

Hive中COUNT的高级用法(条件过滤等)

SELECT    type  , 
			count(*), 
			count(DISTINCT u), 
			count(CASE WHEN plat=1 THEN u ELSE NULL END), 
			count(DISTINCT CASE WHEN plat=1 THEN u ELSE NULL END), 
			count(CASE WHEN (type=2 OR type=6) THEN u ELSE NULL END), 
			count(DISTINCT CASE WHEN (type=2 OR type=6) THEN u ELSE NULL END) 
FROM t 
WHERE dt in ("2012-1-12-02", "2012-1-12-03")
GROUP BY type 
ORDER BY type;

关于count(*)和count(1)

对表中行数进行统计计算，包含null值。 count(某字段)：对表中该字段的行数进行统计，不包含null值。如果出现空字符串，同样会进行统计。

hive保存计算结果到本地/新hive表/已存在hive表

hive 保存计算结果到本地

insert overwrite local directory "/tmp/out/"    
row format delimited fields terminated by "\t"    

select user, login_time from user_login;  

hive 保存计算结果到新的hive表

create table query_result    
as
select user, login_time 
from user_login;  

hive保存计算结果到已存在的hive表（overwrite和into决定是全量同步还是增量同步）

insert overwrite/into table query_result
select user, login_time 
from user_login;

Xshell实现超级方便的上传和下载功能sz/rz

sz 下载

从Linux下载文件到本机 , 在Linux终端输入命令回车后，选择本地存储路径即可。

命令格式：

sz filename 下载文件filename

sz file1 file2 下载多个文件

sz dir/*　　　下载dir目录下所有文件

rz 上传

从本地上传文件到Linux，在Linux终端输入命令回车后，选择本地要上传的文件即可，可一次指定多个文件

命令格式：

rz

---

结语

未来学习计划会关注更多工作中遇到的问题的根源分析，不能仅仅停留在表面的解决问题，要关注问题发生背后的原因是什么，等阅读完《hive性能调优实战》，《hive编程指南》之后，我会再写关于hive的博客。