Hive相关的基础部分整理,包含基础，分区，函数，开窗，典型案例分享

C_x_330

已于 2023-01-01 11:47:05 修改

阅读量192

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Hive 文章标签： hive hadoop 大数据

于 2023-01-01 11:22:28 首次发布

本文链接：https://blog.youkuaiyun.com/C_x_330/article/details/128511192

Hive 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了Hive的基础操作，包括设置本地模式，创建表结构，数据导入导出，以及使用各种函数如IF,CASE,LAG等进行数据分析。还详细讲解了如何进行分区管理，使用LateralView和UDTF进行行列转换，以及窗口函数的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Hive

基础

默认日志在 /tem/lianjie/hive.log
set hive.exec.mode.local.auto=true; 本地模式

create table cx002(
    id int,
    friends array<string>,
    children map<string,int>,
    addr struct<street:string,city:string>
)
row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ":"
lines terminated by '\n'
--访问
select
	friends[0],       --数组还是用下标访问
	children('key'),  --map通过中括号,里面是key,返回value
	addr.city 		 --struct通过 . 的方式访问
from cx002

create table s2 as select id,name from s1 where id%2=1 --根据数据创建表
craete table s2 like s1; --根据结构创建表

create external table xxx(); --创建外部表

alter table xxx set tblproperties('external'='true');--false

desc formatted tbl_Name --显示表的详细信息

hive既可支持string[默认2GB_可变字符串]也可支持varchar

load data [local] inpath '...' [overwrite] into table '...' [partition(partcol1,..)]
--导入数据

```sql
insert overwrite table '表名' select ....  --插入数据 覆盖hdfs原来表数据,只生成一个文件
create table '表名' as select ....  --创建表,通过查询添加数据
```

导出数据

/**

【将Hive的查询结果，导向linux或者hdfs】  覆盖
insert overwrite [local] directory '...linuxPath' 
row format delimited fields terminated by ',' 
select ...... (不加local往hdfs导)


【数据迁移,从hdfs(hive)导入另一个hdfs】
export table '' to 'hdfsPath'
import table  '' from 'hdfsPath'
/

A<=>B 同为NULL --》 true; 否则false

A<>B A或者B为NULL则返回NULL

A not between 。。 and 。。

xx is null

xx is not null

分区

命令

/**
1.msck repair 表名  修复分区(我们直接在hdfs上添加分区目录,但是Hive不知道,需要msck repair)
2.load data local inpath 'xxx' into tableName partition(day='20200503')
3.msck repair 表名
4.
/

函数

基础函数

desc function extended funName
if (a>0 , 1, 2)

case when then else end

case a
when b then c
when e then d
......(when xx then xx)
else f
end

行转列

deot_id	name
1	zhang
1	li

dept_id	name
1	[zhang,li]

/*


1.concat_ws('分隔符',单个元素/数组)
select concat_ws(',','1','2','3')  --> 1,2,3
PS:
	1.concat_ws(',',1,2,3,null)
	如果元素有null，hive自动去掉不会连接；空字符串 '' 也可以拼上
	2.concat_ws(NULL,1,2,3,4)
	如果连接符为NULL,则结果旧返回NULL
	3.参数只能是字符串或字符串数组



2.collect_set( 参数 )
PS
	1.参数可以为 普通类型参数/Array/Map/struct ...
	2.可以去重
     3.收集完之后为数组类型，可以搭配concat_ws使用	
	
3.collect_list(参数)
PS
	1.和collect_set差不多,只是元素可以重复
	2.收集完之后为数组类型，可以搭配concat_ws使用

*/

列转行(UDTF)

/*

1.explode( 参数 )
PS
	1.参数只能为  数组 / Map
	2.数组炸裂后变为1列
	3.Map炸裂后变为两列
	
2.split( '参数' , '分隔符' )
例
	select split( '1,2,3,4' , ',')  --> [1,2,3,4]
	select split( '1,2,3,4' , '2,' ) -->[ '1,' , '3,4' ]
	select split('adchbkj',[abc])  --> [ '', 'd', 'h', 'kj' ]
	<切割后变成数组>
	
3.lateral view[侧窗]
PS
	1.常配合UDTF使用
	2.可以实现这要的效果
	
	id name
	1  [a,b,c]
---> select name,temp_col from t lateral view explode(name) temp_table as temp_col
    1 a
    1 b
    1 c

`窗口函数`

/*
-------------
name  up    |
jack  2022  |
mart  2022  |
mart  2022  |
mart  2022  |
------------
加了开窗之后,是先分组,然后在分组的结果之上进行开窗
没开窗：
select 
	name,                    name  count
	count(1)        --->     jack   1
from t                        mart  4
group by name

开窗：
select 
	name,                         name  count
	count(1)  over()      --->     jack    2
from t                              mart   2
group by name

PS
	1.不加开窗是在 组内 count的
	2.加了开窗是在 分完组的结果之上进行count的

LAG(统计上N行)

/*
查看顾客上次购买时间
name date
li   10.01
li   10.02
li   10.03
--->效果
name date   preDate
li   10.01    ...
li   10.02    10.01
li   10.03    10.03

select
	name,
	date,
	lag(date,1,'1970.01.01') over(partition by name order by date) last_date
from t

Lead向下N行

Ntile(%)

前提：排序

Ntile( 参数 ) 只接受一个参数,一个正数,将数据分成几组

/*
name   date
jimi   2012.01.01
jack   2012.02.03
mami   2012.03.03
heimi  2012.02.04
jiji   2012.03.05
因为随着数据量的改变,所以我们不能 直接 排序然后limit  暴力求解,因为数据是不断变化的

select
	name,
	ntile(5) over(order by date) nt
from t

ntile(5) 会将数据按照我们指定的顺序分成5组,每组从1开始编号,到时候我们在通过where 过滤即可

Rank

/*
name score  rank  desc_rank  row_number
zz    5      1     1             1
aa    4      2     2			2
bb    4      2     2			3
cc    3      4     3			4

select
	name,
	rank() over(partition by name order by score desc) rank
from t

经典案例

1.统计部门男女人数在一行

/**
id name sex dept_id
1   a    男   1
2   b    女   1
3   c    男   2
4   d    女   1
5   e    男   2
/

select
	dept_id,
	sum(case sex when '男' then 1 else 0 end) '男'
    sum(case sex when '女' then 1 else 0  end)  '女'
from person
group by dept_id

2.星座和血型相同归一组(行转列练习)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mX4yClN6-1672543214035)(C:\Users\Cx_330\AppData\Roaming\Typora\typora-user-images\image-20221231180429555.png)]

/*
格式 ： 射手座,A  张三|李四  (星座血型之间用 , 分割;名字之间用 | 分割)

select
	concat_ws(',','星座','血型')，
	concat_ws('|',collect_list(name))
from t
group by 星座 , 血型

3.Lateral view练习

**2.****案例需求：**将表中的categroy列的数据展开。

movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

结果如下：

《疑犯追踪》悬疑

《疑犯追踪》动作

《疑犯追踪》科幻

《疑犯追踪》剧情

《Lie to me》悬疑

《Lie to me》警匪

《Lie to me》动作

《Lie to me》心理

《Lie to me》剧情

《战狼2》战争

《战狼2》动作

/*
select
	name,
	temp_col
from t
lateral view explode(split(category, ',' )) temp_t as temp_col