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

2. 内表删除分区, 若分区文件不存在会报错;

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启动参数

usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable substitution to apply to Hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

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

3. hive -v 打印出执行的语句

七、场景使用

https://blog.csdn.net/weixin_40829577/article/details/124872350https://blog.csdn.net/weixin_40829577/article/details/124872350

1. 清空表的空目录

-- 通过删除分区，清空oss上的空目录
-- 修复分区
msck repair table tbl_name;

-- 删除分区
alter table tbl_name drop partition(dt='20220220');

2. 获取所有UDF

# 先获取所有函数，再过滤出自定义函数（自定义函数都带库名）
hive -e "show functions;" | grep '\.'

九八、问题汇总

1. 删除分区过多

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

原因分析

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

解决办法

分批次删除分区。

2. msck repair 失败

msck repair table tba_name;

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

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

1. 权限问题
HDFS权限不足：执行命令的用户没有HDFS目录的读写权限
Metastore权限不足：用户没有对Hive Metastore的操作权限

2. 分区路径问题
分区目录不存在：HDFS上实际不存在对应的分区目录
分区路径格式错误：分区目录不符合 partition_column=value 的命名规范
分区路径嵌套过深：分区目录层级太深超出限制

3. 元数据问题
Metastore服务不可用：连接Hive Metastore服务失败
元数据不一致：HDFS目录与Metastore记录不一致
表非分区表：对非分区表执行此命令

4. 资源限制
内存不足：处理大量分区时内存耗尽
超时：操作执行时间超过配置的超时阈值

5. 配置问题
Hive版本兼容性：不同Hive版本对命令的支持有差异
HDFS问题：NameNode不可用或HDFS处于安全模式
动态分区未启用：hive.msck.repair.batch.size 设置不当

6. 数据问题
损坏的分区目录：HDFS上分区目录损坏
非法字符：分区值包含特殊字符导致解析失败

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;

7. 通过 hive 建视图报内存溢出OOM， 可以通过spark创建

# 建表语句
sql="
CREATE OR REPLACE VIEW ssjt.shy
COMMENT 'xxx'
AS
SELECT
    *
FROM
    db.tbl
WHERE
    (
        dt = '20250324'
     )
;
show create table ssjt.shy;
"

# 执行建表
echo "${sql}"
spark-sql \
    --name ssjt.shy \
    --master yarn \
    --deploy-mode client \
    --driver-memory 4G \
    --executor-memory 8G \
    --executor-cores 2 \
    --num-executors 1 \
    --conf spark.dynamicAllocation.enabled=false \
    --conf spark.default.parallelism=4 \
    --conf spark.sql.shuffle.partitions=4 \
    -S -e "${sql}"

4. 表格式转换

表无法重命名时核心思路

双表切换法：影子表继承 S3 历史数据，新表承接增量+历史迁移，实现零停机格式升级与存储解耦。

---

操作步骤

步骤 1：创建影子表 B

创建表结构同 A 的影子表 B，LOCATION 指向 A 的 S3 路径（如 s3://bucket/db/tableA/），然后执行 MSCK REPAIR TABLE 恢复分区元数据。这样 B 表成为历史数据的 S3 访问代理，相当于对原表做零拷贝的"软重命名"。

步骤 2：重置原表 A

首先执行 ALTER TABLE A SET TBLPROPERTIES('EXTERNAL'='TRUE') 将 A 转为外表，然后 DROP TABLE A 删除元数据（S3 数据保留），最后重建 A 表为 Parquet 格式 + Zstd 压缩，并指定新 S3 路径（如 s3://bucket/db/tableA_v2/）。这样释放表名并完成格式升级，新 A 表与旧数据物理隔离，避免路径冲突。

步骤 3：承接增量数据

新写入直接指向重建后的 A 表，新数据按 Parquet+Zstd 格式落盘到新 S3 路径。

步骤 4：迁移历史数据

从 B 表读取数据写入 A 表，建议优先导入最近 7 天的数据，其余历史数据分批补录，兼顾业务时效性。

步骤 5：清理下线

执行 DROP TABLE B 删除影子表，并清理对应的 S3 路径（可通过生命周期策略或手动删除），释放存储成本，完成升级。

九九、hive优化

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

---