1. 为什么你需要一份趁手的Hive命令速查手册刚接触Hive那会儿我最怕的就是半夜被叫起来处理数据任务。日志里报了个错明明知道是某张表的分区出了问题可就是想不起来查看分区信息的命令是SHOW PARTITIONS还是DESCRIBE PARTITIONS只能手忙脚乱地翻文档。相信不少做数据开发、数据分析的朋友都有过类似的经历——Hive的命令不算特别多但架不住它杂DDL、DML、DQL、权限、元数据、调优参数混在一起没有一份趁手的速查表效率直接打对折。这篇内容就是把我这些年在一线做离线数仓、数据清洗、报表开发过程中高频用到的Hive命令做一次系统梳理。不管你是刚入行、还在纠结INSERT OVERWRITE和INSERT INTO区别的新人还是已经能独立搭建数仓、但偶尔会忘记某个窗口函数写法的老手都能从这里找到能直接复制粘贴的参考。我会按使用场景把命令分成几大类每一类都配上我实际踩过坑的注意事项而不是干巴巴地罗列语法。毕竟命令这东西光看语法记不住结合场景用一次才真正属于你。需要提前说明的是Hive的版本差异对命令行为影响很大。我这里默认以目前企业里存量最大的Hive 2.x和3.x为基准遇到版本差异明显的地方会单独标注。另外Hive on Tez、Hive on Spark这些执行引擎的差异主要体现在性能上命令语法层面基本一致所以不单独展开。2. 数据库与表的基础操作命令2.1 数据库层面的增删改查数据库在Hive里叫Database有的团队习惯叫Schema两者在Hive里是等价的。日常开发中我们通常按业务线或者按数仓分层来建库比如ods、dwd、dws、ads这种分层命名。创建数据库的标准写法是CREATE DATABASE IF NOT EXISTS dwd_user COMMENT 用户域明细层 LOCATION /warehouse/dwd/dwd_user;这里有几个点值得展开。IF NOT EXISTS几乎是必加的因为很多调度系统会重复执行建库语句不加这个一旦库已存在就会直接报错中断任务。LOCATION指定的是HDFS上的存储路径如果不指定Hive会默认放到hive.metastore.warehouse.dir配置的目录下。我个人的习惯是显式指定路径这样后期做数据迁移或者冷热分离时能清楚知道每个库的数据落在哪里。查看数据库列表用SHOW DATABASES;想看某个库的详细信息用DESCRIBE DATABASE EXTENDED dwd_user;这条命令会返回库的创建时间、位置、所有者、注释等元数据。删除库要特别小心DROP DATABASE IF EXISTS dwd_user CASCADE;CASCADE表示级联删除库下的所有表。如果你只是想删库但库里有表不加CASCADE会直接报错。我见过不止一个同事在测试环境误删了整库数据就是因为手快加了CASCADE却没确认库名。所以我的建议是生产环境执行DROP DATABASE之前先用SHOW TABLES IN dwd_user;确认一下里面到底有什么。切换当前库用USE dwd_user;这个和传统关系型数据库一样没什么好说的。2.2 表的创建与结构变更建表是Hive里最核心的操作之一因为表结构设计直接决定了后续查询的性能和存储成本。先看一个典型的内部表建表语句CREATE TABLE IF NOT EXISTS dwd_user.dim_user_info ( user_id BIGINT COMMENT 用户ID, user_name STRING COMMENT 用户名, phone STRING COMMENT 手机号, create_time TIMESTAMP COMMENT 注册时间, dt STRING COMMENT 分区字段 ) COMMENT 用户维度表 PARTITIONED BY (dt STRING) STORED AS ORC TBLPROPERTIES (orc.compressSNAPPY);这里面的门道不少。PARTITIONED BY是分区字段注意分区字段不能出现在前面的列定义里否则会报重复列的错误。STORED AS ORC指定存储格式ORC配合SNAPPY压缩是目前离线数仓最主流的组合压缩比和查询性能都很均衡。如果是对写入速度要求极高、查询频率低的场景可以考虑STORED AS TEXTFILE但存储成本会高很多。外部表和内部表的区别在于DROP TABLE时内部表会连数据一起删掉外部表只删元数据、保留HDFS上的文件。建外部表加EXTERNAL关键字CREATE EXTERNAL TABLE IF NOT EXISTS ods_user.log_user_login ( ... ) LOCATION /warehouse/ods/log_user_login;修改表结构用ALTER TABLE这是日常运维里用得最多的命令之一。加字段ALTER TABLE dwd_user.dim_user_info ADD COLUMNS (email STRING COMMENT 邮箱);注意加字段时默认加在最后而且对已存在的分区数据不会自动回填新字段查出来是NULL。改字段类型用ALTER TABLE ... CHANGE COLUMN但Hive对类型变更的支持很有限比如STRING改成INT这种基本都会失败所以建表时字段类型要尽量想清楚。改表名用ALTER TABLE old_name RENAME TO new_name;这个操作只改元数据不移动HDFS文件速度很快。但要注意如果有下游任务依赖旧表名改完记得同步更新。2.3 分区与分桶操作分区是Hive性能优化的第一道门槛。添加分区ALTER TABLE dwd_user.dim_user_info ADD IF NOT EXISTS PARTITION (dt2024-01-01) LOCATION /warehouse/dwd/dim_user_info/dt2024-01-01;如果表是外部表且数据已经存在于HDFS上用ADD PARTITION指定LOCATION就能把数据挂载进来。如果是内部表通常配合LOAD DATA或者INSERT来写入。批量修复分区是高频操作尤其是从上游同步数据后MSCK REPAIR TABLE ods_user.log_user_login;这条命令会扫描表对应的HDFS目录把符合分区目录格式的路径自动注册到元数据里。但要注意MSCK REPAIR在分区数量特别多的时候会很慢因为它是一个目录一个目录去扫。我遇到过一张表有几十万分区跑一次MSCK花了将近半小时。这种情况更推荐用ALTER TABLE ... ADD PARTITION批量添加或者直接写脚本从HDFS路径解析出分区值再拼SQL。删除分区ALTER TABLE dwd_user.dim_user_info DROP IF EXISTS PARTITION (dt2024-01-01);对于内部表删分区会连数据一起删外部表只删元数据。这个差异一定要记牢我见过有人以为外部表删分区也会删数据结果数据一直堆在HDFS上没人清理最后把集群存储撑爆了。分桶表用CLUSTERED BY创建CREATE TABLE dwd_user.user_bucket ( user_id BIGINT, ... ) CLUSTERED BY (user_id) INTO 32 BUCKETS STORED AS ORC;分桶的初衷是优化JOIN和采样但在实际生产中分桶表的使用频率远低于分区表。因为分桶数一旦设定后期扩容很麻烦而且分桶对数据倾斜的缓解效果有限。我的经验是除非明确要做bucket map join或者有采样需求否则优先用分区ORC别轻易上分桶。3. 数据查询与写入的核心命令3.1 SELECT查询的常用技巧Hive的SELECT语法和标准SQL大体一致但有几个Hive特有的点需要留意。首先是LIMIT的用法SELECT * FROM table LIMIT 10;会随机返回10行而不是按顺序取前10行。如果想稳定取前N行需要配合ORDER BY但ORDER BY是全局排序数据量大时只有一个Reducer会非常慢。这时候可以用SORT BY加LIMITSELECT * FROM dwd_user.dim_user_info SORT BY create_time DESC LIMIT 100;SORT BY是每个Reducer内部排序不保证全局有序但配合LIMIT能快速拿到大致靠前的数据性能比ORDER BY好很多。DISTRIBUTE BY和SORT BY经常一起用DISTRIBUTE BY控制数据分发到哪个ReducerSORT BY控制Reducer内排序。比如想按用户ID分组、组内按时间排序SELECT user_id, create_time FROM dwd_user.dim_user_info DISTRIBUTE BY user_id SORT BY user_id, create_time DESC;这个组合在去重取最新一条的场景里特别实用。窗口函数是Hive查询的进阶利器。ROW_NUMBER()、RANK()、DENSE_RANK()这三个的区别经常被问到。简单说ROW_NUMBER是连续不重复的序号RANK遇到相同值会跳号DENSE_RANK遇到相同值不跳号。实际去重取最新记录时我基本都用ROW_NUMBER()SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM dwd_user.dim_user_info ) t WHERE t.rn 1;这个写法几乎是数仓开发里的肌肉记忆配合子查询就能实现按主键去重保留最新。注意子查询必须起别名否则Hive会报语法错误。3.2 数据写入的几种方式Hive写入数据主要有三种方式LOAD DATA、INSERT和CREATE TABLE AS SELECT。LOAD DATA适合从本地或者HDFS批量导入原始文件LOAD DATA LOCAL INPATH /home/data/user_info.txt OVERWRITE INTO TABLE dwd_user.dim_user_info PARTITION (dt2024-01-01);加LOCAL表示从本地文件系统读不加则从HDFS读。OVERWRITE会覆盖目标分区已有数据不加则是追加。这里有个坑LOAD DATA只是把文件移动到Hive表目录下不会做任何格式转换。如果文件是CSV但表是ORC格式查出来会是乱码或者NULL。所以LOAD DATA通常只用于TEXTFILE格式的表。INSERT OVERWRITE是最常用的写入方式会覆盖目标表或分区的数据INSERT OVERWRITE TABLE dwd_user.dim_user_info PARTITION (dt2024-01-01) SELECT user_id, user_name, phone, create_time FROM ods_user.log_user_login WHERE dt 2024-01-01;INSERT INTO则是追加写入不覆盖已有数据。这两个命令的选择要非常谨慎因为INSERT OVERWRITE一旦执行目标分区原有数据就没了。我的习惯是日常增量任务用INSERT INTO全量刷新任务用INSERT OVERWRITE并且在调度系统里给INSERT OVERWRITE的任务加上二次确认或者备份机制。多表插入是Hive的一个特色一次扫描源表写入多个目标表FROM ods_user.log_user_login INSERT OVERWRITE TABLE dwd_user.dim_user_info PARTITION (dt2024-01-01) SELECT user_id, user_name, phone, create_time WHERE dt2024-01-01 INSERT OVERWRITE TABLE dwd_user.user_login_cnt PARTITION (dt2024-01-01) SELECT user_id, COUNT(1) WHERE dt2024-01-01 GROUP BY user_id;这个写法能显著减少对源表的扫描次数在源表数据量很大时效果明显。但要注意多表插入时每个INSERT子句的SELECT列顺序要和目标表对应写错了不会报错只会默默写入错误数据。CREATE TABLE AS SELECT适合快速复制表结构加数据CREATE TABLE dwd_user.dim_user_info_bak AS SELECT * FROM dwd_user.dim_user_info WHERE dt2024-01-01;但这种方式建出来的表不会继承原表的分区、分桶、存储格式等属性只是普通的TEXTFILE表。所以它更适合临时分析不适合正式的数仓表建设。3.3 JOIN操作的注意事项Hive的JOIN和传统数据库最大的区别在于它默认只支持等值JOIN非等值JOIN需要开启特定配置或者用其他方式绕。等值JOIN的写法SELECT a.user_id, a.user_name, b.order_cnt FROM dwd_user.dim_user_info a JOIN dwd_user.user_order_stat b ON a.user_id b.user_id WHERE a.dt 2024-01-01 AND b.dt 2024-01-01;小表JOIN大表时可以用MAPJOIN提示把小表加载到内存避免Reduce阶段SELECT /* MAPJOIN(b) */ a.user_id, a.user_name, b.order_cnt FROM dwd_user.dim_user_info a JOIN dwd_user.user_order_stat b ON a.user_id b.user_id;MAPJOIN的适用条件是小表数据量能放进内存默认阈值是25MB可以通过hive.mapjoin.smalltable.filesize调整。如果小表超过阈值还强行MAPJOIN会导致内存溢出。我一般会先DESC FORMATTED看一下表的统计信息确认大小后再决定。LEFT JOIN时要注意过滤条件的位置。如果把右表的过滤条件写在WHERE里LEFT JOIN会退化成INNER JOIN-- 错误写法会丢失左表未匹配的数据 SELECT a.user_id, b.order_cnt FROM dwd_user.dim_user_info a LEFT JOIN dwd_user.user_order_stat b ON a.user_id b.user_id WHERE b.dt 2024-01-01; -- 正确写法过滤条件放在ON里 SELECT a.user_id, b.order_cnt FROM dwd_user.dim_user_info a LEFT JOIN dwd_user.user_order_stat b ON a.user_id b.user_id AND b.dt 2024-01-01;这个坑我踩过不止一次尤其是写复杂查询时过滤条件一多就容易放错位置。后来养成的习惯是LEFT JOIN的右表过滤条件一律写在ON子句里WHERE里只放左表的过滤条件。4. 元数据查看与性能调优命令4.1 查看表信息的常用命令DESCRIBE FORMATTED是查看表详细信息最全的命令DESCRIBE FORMATTED dwd_user.dim_user_info;它会返回列信息、分区信息、存储格式、表属性、数据量统计等。其中numRows和totalSize这两个统计信息很有用但前提是表执行过ANALYZE TABLE收集统计信息否则可能是空或者过期的。收集统计信息的命令ANALYZE TABLE dwd_user.dim_user_info COMPUTE STATISTICS; ANALYZE TABLE dwd_user.dim_user_info PARTITION (dt2024-01-01) COMPUTE STATISTICS;第一条收集表级统计第二条收集分区级统计。统计信息主要影响执行计划的生成比如JOIN时判断哪边是小表。但ANALYZE本身也要跑任务对大表来说开销不小所以通常只在关键表上定期执行。查看分区列表用SHOW PARTITIONSSHOW PARTITIONS dwd_user.dim_user_info;如果分区很多可以加过滤条件SHOW PARTITIONS dwd_user.dim_user_info PARTITION (dt2024-01-01);查看建表语句用SHOW CREATE TABLESHOW CREATE TABLE dwd_user.dim_user_info;这个命令在复制表结构到另一个环境时特别方便直接复制输出的DDL改改表名就能用。查看函数用法用DESCRIBE FUNCTIONDESCRIBE FUNCTION EXTENDED get_json_object;EXTENDED会返回函数的详细说明和示例比只看函数名有用得多。4.2 执行计划与调优参数EXPLAIN是分析查询性能的第一步EXPLAIN SELECT a.user_id, b.order_cnt FROM dwd_user.dim_user_info a JOIN dwd_user.user_order_stat b ON a.user_id b.user_id;执行计划会显示查询被拆成了几个Stage每个Stage的依赖关系以及是否走了MapJoin。如果看到某个Stage的Reduce数量特别大或者有笛卡尔积的迹象就要警惕了。EXPLAIN EXTENDED会输出更详细的信息包括语法树和操作符。但输出内容非常长通常只在排查复杂问题时用。常用的调优参数我整理了一个表方便对照参数名默认值作用建议值hive.exec.dynamic.partitionfalse是否开启动态分区truehive.exec.dynamic.partition.modestrict动态分区模式nonstricthive.exec.max.dynamic.partitions1000最大动态分区数根据业务调整可到10000hive.auto.convert.jointrue是否自动转MapJointruehive.mapjoin.smalltable.filesize25000000MapJoin小表阈值根据内存调整hive.exec.reducers.bytes.per.reducer256000000每个Reducer处理数据量大集群可调大到512Mhive.exec.reducers.max1009最大Reducer数根据集群规模调整动态分区是日常写入最常用的功能但默认是关闭的而且模式是strict。strict模式下如果动态分区和静态分区混用必须至少有一个静态分区。我一般会统一设置成nonstrict这样用起来更灵活SET hive.exec.dynamic.partition true; SET hive.exec.dynamic.partition.mode nonstrict;这两个参数通常写在脚本开头或者配置在调度系统的全局参数里。数据倾斜是Hive查询最常见的性能问题。表现是某个Reduce任务卡在99%很久不动其他Reduce早就完成了。排查方法是看执行日志里各Reduce的处理数据量如果某个Reduce的数据量是其他的几十倍基本就是倾斜了。常见的解决思路有几种如果是JOIN导致的倾斜可以把倾斜的key单独拿出来处理再union回去如果是GROUP BY导致的可以开启负载均衡SET hive.groupby.skewindata true;这个参数会让Hive先做一次随机分发做部分聚合再做一次最终聚合能有效缓解GROUP BY倾斜。但会增加一轮MapReduce对本来就不倾斜的任务反而会变慢所以按需开启。4.3 小文件问题的处理小文件是Hive数仓的老大难问题。每个小文件在HDFS上都要占用NameNode的内存文件多了不仅影响查询性能还可能拖垮NameNode。小文件产生的原因通常是动态分区写入时每个分区产生大量小文件或者Reduce数量过多导致每个Reduce输出一个小文件。合并小文件的思路有两个方向。一是在写入时控制SET hive.merge.mapfiles true; SET hive.merge.mapredfiles true; SET hive.merge.size.per.task 256000000; SET hive.merge.smallfiles.avgsize 16000000;这几个参数的作用是在任务结束时如果输出文件的平均大小小于hive.merge.smallfiles.avgsize就启动一个额外的任务把小文件合并。hive.merge.size.per.task控制合并后每个文件的目标大小。二是事后合并用INSERT OVERWRITE重写一遍INSERT OVERWRITE TABLE dwd_user.dim_user_info PARTITION (dt2024-01-01) SELECT * FROM dwd_user.dim_user_info WHERE dt2024-01-01;这种方式简单粗暴但有效缺点是会重跑一遍任务。我通常是在发现某个分区小文件特别多、已经影响查询性能时才手动执行一次。5. 日常运维与问题排查实录5.1 常见报错与解决思路Hive的报错信息有时候很隐晦我整理了几个高频报错和对应的排查方向。第一个是FAILED: SemanticException Column xxx not found in table。这个通常是列名拼写错误或者表结构变更后没有刷新元数据。可以先DESCRIBE table_name确认列名如果列名没问题可能是元数据缓存的问题重连会话或者执行REFRESH TABLE table_name试试。第二个是java.lang.OutOfMemoryError: Java heap space。这个多半是MapJoin的小表太大或者数据倾斜导致某个任务内存不够。先检查是不是有MAPJOIN提示如果有确认小表大小是否超过阈值。如果是倾斜参考前面说的倾斜处理方案。第三个是FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask。这个报错太笼统了真正的错误信息在YARN的Container日志里。需要去YARN的ResourceManager页面找到对应的Application查看具体是哪个Container失败了再点进去看日志。我一般会先看有没有OutOfMemory或者GC overhead的关键字这两个是最常见的。第四个是动态分区写入时报FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column。这就是前面说的strict模式限制设置hive.exec.dynamic.partition.modenonstrict即可。5.2 权限与安全相关命令Hive的权限管理通常和Hadoop的权限体系配合使用。查看当前用户SELECT current_user();查看当前用户的所有角色SHOW ROLE current_user();授权和回收权限GRANT SELECT ON TABLE dwd_user.dim_user_info TO USER analyst_a; REVOKE SELECT ON TABLE dwd_user.dim_user_info FROM USER analyst_a;但实际生产中Hive的权限管理往往通过Ranger或者Sentry这类组件来做命令行的GRANT/REVOKE用得不多。不过了解这些命令在排查权限问题时还是有用的比如确认某个用户到底有没有某张表的SELECT权限。查看表的权限信息SHOW GRANT USER analyst_a ON TABLE dwd_user.dim_user_info;5.3 实用技巧与避坑经验第一个技巧是关于SET命令的。Hive的SET可以查看和修改配置SET -v会打印所有配置项但输出非常多。更实用的是查看某个具体配置SET hive.exec.dynamic.partition;这个命令会返回当前会话中该配置的值方便确认参数是否生效。第二个技巧是关于!命令的。在Hive CLI里可以用!执行Shell命令!ls /warehouse/dwd/dim_user_info;这个在排查HDFS文件是否存在时很方便不用退出Hive再执行。但注意这个只在Hive CLI里有效Beeline里不支持。第三个技巧是关于变量替换的。Hive支持用${}引用变量SET dt 2024-01-01; SELECT * FROM dwd_user.dim_user_info WHERE dt ${hiveconf:dt};这个在写脚本时特别有用可以把日期作为参数传入避免硬编码。但要注意hiveconf和hivevar的区别hiveconf是Hive的配置变量hivevar是用户自定义变量。我一般用hivevarSET hivevar:dt 2024-01-01; SELECT * FROM dwd_user.dim_user_info WHERE dt ${dt};第四个技巧是关于TRUNCATE TABLE的。TRUNCATE TABLE dwd_user.dim_user_info;会清空表的所有数据但保留表结构。对于分区表TRUNCATE会清空所有分区。如果只想清空某个分区还是得用ALTER TABLE ... DROP PARTITION再ADD PARTITION的方式。第五个技巧是关于SHOW TABLES的模糊匹配。SHOW TABLES LIKE dim_*;可以按模式匹配表名在表特别多的库里找表很方便。但注意LIKE后面的模式是大小写敏感的而且只支持*和|两种通配符。5.4 问题排查速查表问题现象可能原因排查命令解决方向查询卡在99%数据倾斜查看YARN日志各Reduce数据量开启skewindata或拆分倾斜key报错Column not found列名错误或元数据未刷新DESCRIBE table_name修正列名或REFRESH TABLE动态分区写入失败strict模式限制SET hive.exec.dynamic.partition.mode改为nonstrict小文件过多Reduce数过多或动态分区HDFS ls查看文件数开启merge或重写分区MapJoin内存溢出小表超过阈值DESC FORMATTED查看表大小调大阈值或去掉MAPJOIN提示任务执行特别慢资源不足或SQL低效EXPLAIN查看执行计划优化SQL或调整资源队列这张表是我自己排查问题时总结的基本覆盖了日常80%的场景。遇到新问题先对照这张表过一遍能省不少时间。6. 关于Hive命令学习的一点个人体会Hive命令这东西死记硬背效率很低最好的方式是在实际任务中反复用。我刚开始学的时候把常用命令抄在便签上贴在显示器旁边用一次看一次大概两周就形成肌肉记忆了。但命令只是工具真正决定你效率的是对数据模型的理解和对执行计划的分析能力。同样一个查询有人写出来跑十分钟有人写出来跑十秒差距就在对Hive底层机制的理解上。另外不同公司的Hive环境差异很大有的用CDH有的用HDP有的直接开源自建配置参数和可用功能都不太一样。所以看任何命令文档都要结合自己环境的实际情况别盲目照搬。遇到不确定的命令先在测试环境验证确认行为符合预期再上生产。这个习惯我保持了这么多年帮我避免了很多次生产事故。最后说一个我自己的小习惯我会在Hive里建一张个人用的笔记表专门记录那些不常用但关键时刻能救命的命令和参数。比如某些特定的窗口函数写法、某些冷门的元数据查询命令。时间长了这张表就成了我自己的知识库比任何官方文档都顺手。