hive使用笔记

一、 相关参考

类别	描述	链接
官方	官方文档	https://cwiki.apache.org/confluence/display/Hive/LanguageManual
	内置函数	LanguageManual UDF - Apache Hive - Apache Software Foundation
华为	故障排错	https://doc.hcs.huawei.com/zh-cn/usermanual/mrs/mrs_03_0147.html

二、 内置函数

1.  字符串操作

描述	类别
字符串函数	https://www.iteblog.com/archives/1639.html
rlike,like,not like使用详解	https://blog.csdn.net/qq_26442553/article/details/79452221
正则表达式	https://www.runoob.com/regexp/regexp-metachar.html

2.  进制转换

语法: conv(bigint num, int from_base, int to_base), conv(string num, int from_base, int to_base)
返回值: string
说明: Converts a number from a given base to another 

-- 十进制转十六进制
hive> select conv(17,10,16);
11
hive> select conv(‘17’,10,16);
11


-- 十进制转二进制
hive> select conv(17,10,2);
10001
hive> select conv(‘17’,10,2);
10001

三、 多表指向同一份数据

1） 创建外表，指向同一个数据源，修复分区（或添加分区）；
2） 创建视图；

四、 hive常用命令

1. 查看hive版本

hive --version

2. 表操作

-- 查看表详情
show create table tab_name; -- spark不能获取内表路径
desc formatted tab_name ;

-- 增加表名注释
alter table tab_name set tblproperties('comment' = '这是表注释!');

-- 增加表属性
alter table `tab_name` set tblproperties('owner'='shy');

-- 增加表生命周期
alter table `tab_name` set tblproperties('lifecycle'='-1');

-- 内外表切换(属性值区分大小写)
ALTER TABLE tab_name SET TBLPROPERTIES('EXTERNAL'='true');

-- 增加一列
alter table tab_name add columns(status string comment '注释') cascade;

-- 更改列名及注释
alter table tab_name change column status status string comment 'tid 状态:init/active';

3. 分区操作

-- 添加分区
-- 修复表
set hive.msck.repair.batch.size=1000;
set hive.msck.path.validation=ignore;
msck repair table ssjt.shy;

-- 直接添加
alter table tab_name add if not exists partition (dt='20210830') location 'path';


-- 删除分区
-- 精确删除(hive和spark都支持)
alter table tab_name drop if exists partition(dt='20220105');

-- 区间删除(仅hive支持)
alter table tab_name drop if exists partition(dt>='20220105',dt<='20220108');

-- 批量删除(hive和spark都支持)
alter table tab_name drop if exists partition (dt='20220110'),partition (dt='20220111');

-- 删除分区时不进回收站，删除速度快
alter table tab_name drop if exists partition(dt='20220105') purge;

-- 查看分区信息
show partitions tab_name;
show partitions tab_name partition(dt >= '20221205');
desc formatted tab_name partition(dt='20221205');

4. 传入变量

-- 设定变量
set tab_name=ssjt.shy;
-- 使用变量
show create table ${hiveconf:tab_name};

5. list库和表

使用正则方法查找库名或表名

-- 罗列库名
show databases;
show databases like '*ods*';

-- 罗列表名
show tables in tranadm;
show tables in tranadm  like '*sensor*';

五、 hive参数设置

调节hive的日志级别
hive --hiveconf hive.root.logger=DEBUG,console

-- 递归查询子目录
set hive.input.dir.recursive=true;
set hive.mapred.supports.subdirectories=true;
set hive.supports.subdirectories=true;
set mapred.input.dir.recursive=true;

-- 打开动态分区
set hive.exec.dynamic.partition.mode=nonstrict;

-- 忽略错误继续执行
set hive.continue.on.failure=true;
set hive.cli.errors.ignore=true;

七、删除表或分区慢

对于分区较多或文件较多的表，清空数据或删除表时速度很慢， 可以先删元除数据再删除数据，即内表转外表删除元数据后再删除底层数据；

1. 删除表

-- 转外表
alter table tbl_name set TBLPROPERTIES('EXTERNAL'='true');

-- 删除表
drop table tbl_name;

-- 手动删除底层数据
ossutil rm -rf oss://base_path/aa/

2. 删除表分区

-- 转外表
alter table tbl_name set TBLPROPERTIES('EXTERNAL'='true');

-- 删除分区元数据
alter table tbl_name drop if exists partition(dt < '20240713') purge;

-- 确认分区是否删除
show partitions tbl_name;

-- 转成内表
alter table tbl_name set TBLPROPERTIES('EXTERNAL'='false');

-- 确认是否转成内表
show partitions tbl_name;

-- 手动删除底层数据
ossutil rm -rf oss://base_path/aa/

六、hive日志

1. hive -e 不输出日志

hive -S -e "set hive.cli.print.header=flase; show partitions tab_name partition(dt>='20221201');" | sort | tail -1

2. 查看hive的日志

# 当前用户下查看hive的日志
/tmp/hive/用户名/hive.log
/tmp/hive/root/hive.log

九八、问题汇总

1. 删除分区过多

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. null

原因分析

drop partition的处理逻辑是将找到所有满足条件的分区，将其拼接起来，最后统一删除。由于分区数过多，拼删元数据堆栈较深，出现StackOverFlow异常。

解决办法

分批次删除分区。

2. 修复表分区失败

msck repair table tba_name;

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

表路径存在不规范的目录, 路径下存在文件, 或不符合分区的规范.

3. 删除表或分区报路径不存在错误

drop table tbl_name;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:No such file or directory '')
alter table tbl_name drop partition(dt='xxx');
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. No such file or directory ''

解决方式

msck repair table tbl_name;
drop table tbl_name;

4. 多级分区内表删除分区, 删除缓慢且对oss产生大量get请求

问题原因: 多级分区内表删除分区时会对oss产生大量的get请求，目前已确认是开源hive 4.0以前版本的一个Feature（删除分区后会进一步查看父路径下是否为空，如果为空则进行删除，这在HDFS下没有问题，但在对象存储下会导致API调用次数暴涨）, 可参考: [HIVE-22054] Avoid recursive listing to check if a directory is empty - ASF JIRA

解决方案: 把内表转成外表, 分别单独删除分区元数据和数据;

5. 先drop表再create table as报路径已存在

问题现象：
Error in query: Can not create the managed table('`db_name`.`tbl_name`'). The associated location('tbl_path') already exists.

问题原因：

该问题具有偶发性，未定位到原因怀疑是元数据压力大导致的误判；

6. 使用子查询时必须起别名

NoViableAltException(276@[96:1: atomjoinSource : ( tableSource ( lateralView ^)* | virtualTableSource ( lateralView ^)* | ( subQuerySource )=> subQuerySource ( lateralView ^)* | partitionedTableFunction ( lateralView ^)* | LPAREN ! joinSource RPAREN !);])

-- 运行报错
select * from (select 1 as id);


-- 运行OK的
select * from (select 1 as id) aa;

九九、hive优化

类别	描述	链接
		Hive参数与性能企业级调优（建议收藏）

---