Hive SQL

Bertha 。 2021-10-13 02:02 398阅读 0赞

拼接函数(行转列):  
collect\_set():把多个值去重拼接成一个数组  
collect\_list():同上，不去重  
concat\_ws():用指定分隔符，把数组拼成一个字符串  
–：concat\_ws(分隔符，数组)  
字符串拼接:  
concat(v1,v2,v3)

分割函数（列转行）：  
split(字符串，分隔符)：切割字符串  
split(字符串，起始索引)：字符串部分截取  
lateral view explde（数组或者map）：数组炸成多行

使用sort by 求topN  
select \* from gift sort by id limit 10

抽样固定条数  
select \* from gift sort by rand() limit 200

hive的count(distinct value)效率低于group by再sum(1) --:1代表行数  
因为前者只在一个进程中跑，后者在分多个reduce跑

hive sql不支持union只有union all（非去重）

left semi join(左半开连接)  
显示满足条件的左表内容，（右表不显示）  
hive sql 对 in exists的优化

窗口函数：  
特点：可以显示聚集前的数据

格式： 聚合函数 over（partition by 字句，order by字句，window字句）

常用分析函数：  
RANK() ：在分组中排名，相同排名时会留下空位；  
DENSE\_RANK() ：在分组中排名，相同排名时不会留下空位；  
FIRST\_VALUE() ：分组内排序取第一个值；  
LAST\_VALUE() ：分组内排序取最后一个值；  
NTILE(n) ：将分组数据按顺序切分成n份，返回当前所在切片；  
ROW\_NUMGBER() ：在分组中从1开始按序记录序列；  
CUME\_DIST() ：小于等于当前值的行数 / 分组总行数（百分比）；  
PERCENT\_RANK() ：（分组内的RANK值-1） / （分组内总数-1）；  
LAG(col, n, DEFAULT) ：在统计窗口内从下往上取第n行的值；  
LEAD(col, n, DEFAULT)：在统计窗口内从上往下取第n行的值；

数据的写入：  
建表并写入：  
create table 表名 as select \* from 依赖表名  
追加写入：  
insert into table 表名 select \* from 依赖表名  
覆盖写入：  
insert overwrite table 表名 select \* from 依赖表名  
插入分区表：  
insert overwrite table 表名 partition（分区字段=……）select \* from 依赖表名 where 分区字段 = ……

返回第一个不为null的值：  
coalesce( value1,value2,… )

类型转换函数:  
CAST(var1 AS 数据类型)

字符串替换：  
regexp\_replace(‘string’,‘原字符’,‘替换后的字符’)

获取json对象中的值  
get\_json\_object(json, . k e y 1 ) g e t j s o n o b j e c t ( j s o n , .key1) get\_json\_object(json, .key1)getjsonobject(json,.key1.key2)

聚合函数count  
count(1):每一行都取  
count(字段名)：有null的不取  
count（distinct 字段名）：带去重

mapjoin设置参数：  
通常无用，因为只有inner join有效，left join是无效的

优化：  
set hive.optimize.ppd = true 外层过滤条件提前在内层就执行，缩短数据量，默认开启  
set hive.exec.parallel = true 开启并发执行（例如left join的两个查询，可以同时执行，互不影响）  
set hive.groupby.mapaggr.checkinterval = 1000000 map端聚合（超过指定值，进行map端聚合）

（工作）常见问题：  
锁冲突:  
hive本身通过读写锁保证数据一致性，写锁互斥，读写锁互斥  
解决：  
查看锁：show locks 表名 （分区）  
解锁：unlock table 表名 （分区）  
忽略锁：set hive.support.concurrency = false;

map/reduce task长尾  
reduce：join、group by造成数据倾斜  
建议：优先join、过滤  
map：小文件过多  
建议：set mapreduce.input.fileinputtormat.split.maxsize = xxx  
set hive.exereduces.max = xxx