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.htmlrlike,like,not like使用详解https://blog.csdn.net/qq_26442553/article/details/79452221正则表达式https://www.runoob.com/regexp/regexp-metachar.html2. 进制转换语法: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 --version2. 表操作-- 查看表详情 show create table tab_name; -- spark不能获取内表路径 desc formatted tab_name ; -- 增加表名注释 alter table tab_name set tblproperties(comment 这是表注释!); -- 增加表属性 alter table tab_name set tblproperties(ownershy); -- 增加表生命周期 alter table tab_name set tblproperties(lifecycle-1); -- 内外表切换(属性值区分大小写) ALTER TABLE tab_name SET TBLPROPERTIES(EXTERNALtrue); -- 增加一列 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.size1000; set hive.msck.path.validationignore; msck repair table ssjt.shy; -- 直接添加 alter table tab_name add if not exists partition (dt20210830) location path; -- 删除分区 -- 精确删除(hive和spark都支持) alter table tab_name drop if exists partition(dt20220105); -- 区间删除(仅hive支持) alter table tab_name drop if exists partition(dt20220105,dt20220108); -- 批量删除(hive和spark都支持) alter table tab_name drop if exists partition (dt20220110),partition (dt20220111); -- 删除分区时不进回收站删除速度快 alter table tab_name drop if exists partition(dt20220105) purge; -- 查看分区信息 show partitions tab_name; show partitions tab_name partition(dt 20221205); desc formatted tab_name partition(dt20221205);4. 传入变量-- 设定变量 set tab_namessjt.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.loggerDEBUG,console -- 递归查询子目录 set hive.input.dir.recursivetrue; set hive.mapred.supports.subdirectoriestrue; set hive.supports.subdirectoriestrue; set mapred.input.dir.recursivetrue; -- 打开动态分区 set hive.exec.dynamic.partition.modenonstrict; -- 忽略错误继续执行 set hive.continue.on.failuretrue; set hive.cli.errors.ignoretrue;七、删除表或分区慢1. 对于分区较多或文件较多的表清空数据或删除表时速度很慢 可以先删元除数据再删除数据即内表转外表删除元数据后再删除底层数据2. 内表删除分区, 若分区文件不存在会报错;1. 删除表-- 转外表 alter table tbl_name set TBLPROPERTIES(EXTERNALtrue); -- 删除表 drop table tbl_name; -- 手动删除底层数据 ossutil rm -rf oss://base_path/aa/2. 删除表分区-- 转外表 alter table tbl_name set TBLPROPERTIES(EXTERNALtrue); -- 删除分区元数据 alter table tbl_name drop if exists partition(dt 20240713) purge; -- 确认分区是否删除 show partitions tbl_name; -- 转成内表 alter table tbl_name set TBLPROPERTIES(EXTERNALfalse); -- 确认是否转成内表 show partitions tbl_name; -- 手动删除底层数据 ossutil rm -rf oss://base_path/aa/六、hive启动参数usage: hive -d,--define keyvalue Variable substitution to apply to Hive commands. e.g. -d AB or --define AB --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 propertyvalue Use value for given property --hivevar keyvalue Variable substitution to apply to Hive commands. e.g. --hivevar AB -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.headerflase; show partitions tab_name partition(dt20221201); | sort | tail -12. 查看hive的日志# 当前用户下查看hive的日志 /tmp/hive/用户名/hive.log /tmp/hive/root/hive.log3. 打印出执行的语句hive -v -e ${sql}4. 忽略某个错误继续执行set hive.cli.errors.ignoretrue;七、场景使用https://blog.csdn.net/weixin_40829577/article/details/124872350https://blog.csdn.net/weixin_40829577/article/details/1248723501. 清空表的空目录-- 通过删除分区清空oss上的空目录 -- 修复分区 msck repair table tbl_name; -- 删除分区 alter table tbl_name drop partition(dt20220220);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_columnvalue 的命名规范分区路径嵌套过深分区目录层级太深超出限制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(dtxxx);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.enabledfalse \ --conf spark.default.parallelism4 \ --conf spark.sql.shuffle.partitions4 \ -S -e ${sql}4. 表格式转换表无法重命名时核心思路双表切换法影子表继承 S3 历史数据新表承接增量历史迁移实现零停机格式升级与存储解耦。操作步骤步骤 1创建影子表 B创建表结构同 A 的影子表 BLOCATION 指向 A 的 S3 路径如s3://bucket/db/tableA/然后执行 MSCK REPAIR TABLE 恢复分区元数据。这样 B 表成为历史数据的 S3 访问代理相当于对原表做零拷贝的软重命名。步骤 2重置原表 A首先执行 ALTER TABLE A SET TBLPROPERTIES(EXTERNALTRUE) 将 A 转为外表然后 DROP TABLE A 删除元数据S3 数据保留最后重建 A 表为 Parquet 格式 Zstd 压缩并指定新 S3 路径如s3://bucket/db/tableA_v2/。这样释放表名并完成格式升级新 A 表与旧数据物理隔离避免路径冲突。步骤 3承接增量数据新写入直接指向重建后的 A 表新数据按 ParquetZstd 格式落盘到新 S3 路径。步骤 4迁移历史数据从 B 表读取数据写入 A 表建议优先导入最近 7 天的数据其余历史数据分批补录兼顾业务时效性。步骤 5清理下线执行 DROP TABLE B 删除影子表并清理对应的 S3 路径可通过生命周期策略或手动删除释放存储成本完成升级。九九、hive优化类别描述链接Hive参数与性能企业级调优建议收藏