1. 为什么刷完100道题还是写不好Hive SQL我带过不少刚转数据开发的朋友发现一个特别普遍的现象收藏了几百道SQL练习题从简单查询刷到窗口函数每道题都能看懂答案但一进真实项目就卡壳。原因很简单——练习题和真实业务之间隔着一层数据思维的窗户纸而这层纸光靠刷题是捅不破的。Hive SQL和MySQL、SQL Server这些传统关系型数据库的SQL语法上看着像亲兄弟骨子里却是两套逻辑。MySQL跑一条查询背后是InnoDB的行式存储和B树索引在支撑Hive跑一条查询背后是HDFS的分布式文件系统和MapReduce/Tez/Spark的执行引擎。你在MySQL里随手写的SELECT * FROM t WHERE dt 2024-01-01到了Hive里如果表没做分区就是一次全表扫描几亿行数据能把集群拖垮。所以这篇内容不是简单地把100道题和答案罗列出来。我要做的是把每道题背后的Hive特性讲透把练习题和真实生产场景之间的桥搭起来。适合谁看刚接触Hive的数据分析新人、从MySQL转过来的开发、准备数据开发面试的求职者以及那些语法都会但写不出高效SQL的从业者。接下来的内容会围绕几个核心问题展开Hive SQL和标准SQL到底差在哪、100道题应该怎么分类刷才有效、每类题的核心考点是什么、真实项目里那些练习题不会告诉你的坑怎么避。我不会给你一个万能模板而是把每道题拆开揉碎让你看到题目背后的数据分布、执行计划和优化空间。2. Hive SQL和标准SQL的分水岭在哪里2.1 从一道简单的查询题说起先看一道最基础的练习题-- 题目查询2024年1月1日之后注册的用户中消费金额最高的前10名 SELECT user_id, SUM(amount) AS total_amount FROM user_orders WHERE register_date 2024-01-01 GROUP BY user_id ORDER BY total_amount DESC LIMIT 10;这道题在MySQL里跑如果register_date和user_id都有索引毫秒级出结果。但在Hive里如果user_orders是一张按天分区的表而register_date不是分区字段这条SQL会触发全表扫描。更关键的是ORDER BY在Hive里是全局排序所有数据会汇聚到一个Reducer上数据量大的时候这个Reducer会成为瓶颈跑几个小时甚至OOM都是常事。正确的写法应该是先过滤分区再用DISTRIBUTE BY加SORT BY做局部排序最后取Top NSELECT user_id, total_amount FROM ( SELECT user_id, SUM(amount) AS total_amount FROM user_orders WHERE dt 2024-01-01 -- 分区字段先裁剪数据 GROUP BY user_id DISTRIBUTE BY user_id SORT BY total_amount DESC ) t LIMIT 10;这里有个细节DISTRIBUTE BY user_id保证同一个用户的数据进入同一个ReducerSORT BY在每个Reducer内部排序。但这样取出来的Top 10是每个Reducer的Top 10不是全局Top 10。如果数据量真的很大更稳妥的做法是用窗口函数ROW_NUMBER()配合子查询或者先做聚合再排序。提示Hive里ORDER BY和SORT BY的区别是面试高频考点。ORDER BY全局有序但只有一个ReducerSORT BY局部有序但多个Reducer并行。数据量大时优先用SORT BY需要全局有序时再考虑ORDER BY。2.2 分区、分桶与数据裁剪的实战意义Hive SQL性能优化的第一原则是数据裁剪也就是尽可能少读数据。这跟MySQL的索引思路完全不同——MySQL靠索引快速定位行Hive靠分区和分桶快速定位文件。分区Partition是把表按某个字段通常是日期拆成不同目录。比如dt2024-01-01是一个目录dt2024-01-02是另一个目录。查询时指定WHERE dt 2024-01-01Hive就只读这一个目录其他目录直接跳过。这是Hive最基础也最有效的优化手段。分桶Bucket是把数据按某个字段的哈希值拆成固定数量的文件。比如按user_id分64个桶那么user_id % 64相同的数据会进入同一个文件。分桶的好处是采样和Join时可以做Bucket Map Join减少Shuffle。-- 创建分桶表 CREATE TABLE user_orders_bucketed ( user_id BIGINT, amount DECIMAL(10,2), order_time STRING ) CLUSTERED BY (user_id) INTO 64 BUCKETS STORED AS ORC;分桶表在Join时有个巨大优势如果两张表都按同一个字段分了相同数量的桶Hive可以直接做Bucket Map Join每个Map任务只处理对应的桶完全避免Shuffle。这在数据量上亿的时候性能差距可能是几十分钟和几分钟的区别。2.3 数据类型和函数的方言差异Hive SQL在数据类型上比MySQL宽松很多但也埋了不少坑。比如STRING类型在Hive里几乎万能日期、数字、文本都能往里塞但这也意味着类型转换的错误要到运行时才暴露。我见过太多人把日期存成STRING然后WHERE dt 2024-01-01结果因为字符串比较的字典序问题2024-1-1排在2024-01-01后面数据直接查错。Hive的函数也和标准SQL有差异。比如日期函数Hive用date_sub、date_add、datediff和MySQL的DATE_SUB、DATE_ADD、DATEDIFF功能类似但参数顺序不同。字符串拼接Hive用concat和concat_ws数组和Map类型有专门的explode、collect_list、collect_set等函数。-- Hive日期函数示例 SELECT date_sub(2024-01-10, 5) AS five_days_ago, -- 2024-01-05 datediff(2024-01-10, 2024-01-01) AS diff_days, -- 9 date_format(2024-01-10 12:30:00, yyyy-MM) AS month_str; -- 2024-01这些函数在100道练习题里会反复出现但练习题通常只考会不会用不考什么时候用哪个。比如collect_list和collect_set的区别前者保留重复值后者去重在行转列场景下选错了结果集大小可能差几倍。3. 100道题怎么分类刷才不是白刷3.1 按数据操作类型分四层而不是按难度很多人刷题喜欢按简单-中等-困难排序这在Hive SQL里效率很低。因为Hive的难点不在逻辑复杂度而在数据操作类型。我建议按四层来刷第一层单表查询与过滤。这层题目的核心是熟悉Hive的WHERE、GROUP BY、HAVING、ORDER BY、LIMIT以及分区裁剪。重点不是写出结果而是写出不触发全表扫描的SQL。比如一道查询每个城市订单量的题如果表按日期分区你就要养成先加WHERE dt ...的习惯。第二层多表Join与集合操作。这层要搞懂INNER JOIN、LEFT JOIN、FULL JOIN在Hive里的执行差异以及UNION和UNION ALL的性能区别。Hive的Join默认是Reduce Join数据量大时Shuffle很重。如果有一张表足够小比如维度表可以用Map Join把小表加载到内存里避免Shuffle。-- 开启Map Join自动转换 SET hive.auto.convert.join true; SET hive.mapjoin.smalltable.filesize 25000000; -- 25MB以下视为小表 -- 显式指定Map Join SELECT /* MAPJOIN(dim_city) */ o.user_id, d.city_name, SUM(o.amount) FROM user_orders o JOIN dim_city d ON o.city_id d.city_id GROUP BY o.user_id, d.city_name;第三层窗口函数与排名。这层是面试重灾区。ROW_NUMBER()、RANK()、DENSE_RANK()的区别LAG()、LEAD()的用法SUM() OVER()的累计计算这些在100道题里会反复出现。但练习题不会告诉你的是窗口函数在Hive里会触发Shuffle如果PARTITION BY的字段基数太大性能会很差。第四层行转列与列转行。这层是Hive的特色也是实际项目里最常用的。EXPLODE、LATERAL VIEW、COLLECT_LIST、COLLECT_SET这些函数在MySQL里没有对应实现但在Hive里处理JSON、数组、Map类型数据时必不可少。3.2 每道题都要问的三个为什么刷题不是做完对答案就完了。每道题做完至少要问自己三个问题为什么这样写比如一道求每个用户连续登录天数的题标准解法是用ROW_NUMBER()做差值分组。但为什么要用date_sub(login_date, rn)因为连续日期的差值相同这个差值就是分组依据。理解了这个逻辑下次遇到连续签到连续消费都能套用。有没有更优写法同一道题用JOIN能解用窗口函数也能解用GROUP BY加自连接还能解。哪种写法在Hive里跑得快通常窗口函数比自连接快因为自连接会产生笛卡尔积再过滤而窗口函数只扫描一次数据。数据量大了会怎样练习题的数据量通常很小几百行几千行怎么写都能出结果。但真实项目里一张表几亿行SELECT *和SELECT 指定列的差距可能是几十分钟。所以每道题都要想如果这张表有10亿行我的SQL还能跑吗哪里会成为瓶颈3.3 从练习题到生产SQL的四个跨越练习题和生产SQL之间有四个明显的跨越跨越一数据质量。练习题的数据是干净的生产数据有NULL、有脏值、有格式不一致。比如user_id字段练习题里都是数字生产里可能有空字符串、有null字符串、有前后空格。写SQL时要用COALESCE、TRIM、CASE WHEN做清洗。跨越二分区设计。练习题通常不关心分区生产表必须按日期分区。而且分区字段的选择有讲究按天分区适合增量数据按月分区适合历史归档按业务字段分区适合特定查询模式。跨越三执行计划。练习题不需要看执行计划生产SQL必须看。Hive的EXPLAIN命令能显示SQL被翻译成几个Stage、每个Stage有多少Map和Reduce、有没有数据倾斜。EXPLAIN SELECT city_id, COUNT(DISTINCT user_id) FROM user_orders WHERE dt 2024-01-01 GROUP BY city_id;看执行计划重点关注Stage数量越少越好、Reduce数量太多说明Shuffle重、有没有Cartesian Product笛卡尔积是灾难。跨越四资源调优。练习题不需要调参数生产SQL要调。比如SET hive.exec.reducers.bytes.per.reducer控制每个Reducer处理的数据量SET hive.exec.dynamic.partition.mode控制动态分区模式。这些参数在100道题里不会出现但实际工作中天天用。4. 窗口函数100道题里最容易会做但做错的部分4.1 ROW_NUMBER、RANK、DENSE_RANK的实战选择这三兄弟是窗口函数里最常考的但很多人只知道语法不知道什么时候用哪个。看一道典型题-- 题目查询每个班级成绩前三名的学生 SELECT class_id, student_name, score FROM ( SELECT class_id, student_name, score, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS rn FROM student_scores ) t WHERE rn 3;这里用ROW_NUMBER()是对的因为要取前三名即使有并列分数也只取三个。但如果题目改成查询每个班级成绩排名前三的分数就要用DENSE_RANK()因为并列分数算同一个名次。-- 查询每个班级排名前三的分数并列算同一名次 SELECT class_id, score FROM ( SELECT class_id, score, DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) AS dr FROM student_scores ) t WHERE dr 3;RANK()和DENSE_RANK()的区别在于RANK()遇到并列会跳号1,1,3DENSE_RANK()不跳号1,1,2。实际项目里ROW_NUMBER()用于去重和取Top NDENSE_RANK()用于排名场景RANK()用得相对少。注意窗口函数的PARTITION BY字段如果基数太大比如按user_id分区会导致大量数据进入同一个Reducer引发数据倾斜。这时候可以考虑加随机前缀打散或者改用其他写法。4.2 LAG和LEAD计算环比和同比的利器LAG()和LEAD()用于访问当前行之前或之后的行在计算环比、同比、差值时特别有用。-- 题目计算每个用户每次消费与上一次消费的间隔天数 SELECT user_id, order_date, LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_date, datediff( order_date, LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date) ) AS days_diff FROM user_orders;这道题的关键是LAG(order_date, 1)它取的是同一用户按日期排序后的前一行。如果用户只有一次消费prev_order_date为NULLdays_diff也为NULL这是符合预期的。实际项目里LAG()和LEAD()常用于计算留存率、复购率、页面跳转路径。但要注意如果ORDER BY的字段有重复值LAG()的结果可能不稳定。这时候需要加一个唯一的排序字段比如ORDER BY order_date, order_id。4.3 SUM OVER累计计算和滑动窗口SUM() OVER()可以做累计求和、滑动窗口求和这在计算GMV累计值、7日留存时非常常用。-- 题目计算每个用户截至当日的累计消费金额 SELECT user_id, order_date, amount, SUM(amount) OVER ( PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_amount FROM user_orders;ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从第一行到当前行这就是累计求和。如果改成ROWS BETWEEN 6 PRECEDING AND CURRENT ROW就是最近7行的滑动求和。这里有个性能陷阱如果PARTITION BY的字段基数很大每个分区内的数据都要排序内存消耗会很高。Hive会把每个分区的数据加载到内存里排序分区太大就会OOM。解决办法是先用GROUP BY做预聚合减少数据量再用窗口函数。5. 行转列与列转行Hive SQL的独门绝技5.1 EXPLODE和LATERAL VIEW的配合使用Hive处理数组和Map类型数据时EXPLODE和LATERAL VIEW是核心工具。看一道典型题-- 题目将用户标签数组拆成多行 -- 原始数据user_id | tags -- 1 | [a,b,c] -- 目标数据user_id | tag -- 1 | a -- 1 | b -- 1 | c SELECT user_id, tag FROM user_tags LATERAL VIEW EXPLODE(tags) t AS tag;LATERAL VIEW EXPLODE(tags) t AS tag的意思是把tags数组里的每个元素拆成一行别名t是虚拟表tag是列名。这是Hive里最常用的行转列写法。如果数组里还有嵌套结构比如[{name:a,score:1},{name:b,score:2}]可以这样写SELECT user_id, item.name, item.score FROM user_tags LATERAL VIEW EXPLODE(tags) t AS item;item是Map或Struct类型可以直接用.访问字段。5.2 COLLECT_LIST和COLLECT_SET的列转行反过来把多行合并成一行用COLLECT_LIST或COLLECT_SET-- 题目将每个用户的多个订单合并成一个数组 SELECT user_id, COLLECT_LIST(order_id) AS order_ids, COLLECT_SET(city) AS cities FROM user_orders GROUP BY user_id;COLLECT_LIST保留所有值包括重复COLLECT_SET去重。实际项目里COLLECT_SET常用于统计去重后的维度值比如一个用户访问过哪些城市。但要注意COLLECT_LIST和COLLECT_SET的结果是无序的如果需要有序要用SORT_ARRAYSELECT user_id, SORT_ARRAY(COLLECT_LIST(order_id)) AS sorted_order_ids FROM user_orders GROUP BY user_id;5.3 行转列在JSON解析中的实战应用实际项目里行转列最常用的场景是解析JSON日志。比如埋点日志里有一个events字段是JSON数组每个元素包含event_name和event_time。-- 解析JSON数组 SELECT user_id, event.event_name, event.event_time FROM user_logs LATERAL VIEW EXPLODE( FROM_JSON(events, arraystructevent_name:string,event_time:string) ) t AS event;FROM_JSON把JSON字符串转成Hive的数组结构EXPLODE再拆成多行。这个写法在日志分析里几乎天天用但100道练习题里很少涉及因为练习题的数据通常是结构化的。提示FROM_JSON的第二个参数是Schema必须和JSON结构完全匹配否则会返回NULL。建议先用SELECT events FROM user_logs LIMIT 1看一眼原始数据再写Schema。6. 数据倾斜练习题永远不会告诉你的性能杀手6.1 数据倾斜的典型表现和根因数据倾斜是Hive SQL性能问题的头号杀手。表现是一个SQL跑了几小时看日志发现大部分Reduce任务早就完成了但有几个Reduce卡在99%不动。根因是某个Key的数据量远大于其他Key导致处理这个Key的Reduce任务成了瓶颈。典型场景按user_id做GROUP BY但有几个大客户的数据量占了全表的80%。或者按city_id做Join但北上广深的数据量是其他城市的几百倍。-- 倾斜示例按城市统计订单量但一线城市数据量极大 SELECT city_id, COUNT(*) AS order_cnt FROM user_orders GROUP BY city_id;如果city_id分布极不均匀这个SQL就会倾斜。6.2 打散Key最常用的倾斜解决方案解决数据倾斜最常用的方法是给Key加随机前缀把一个大Key拆成多个小Key-- 第一步给倾斜Key加随机前缀 SELECT CONCAT(city_id, _, CAST(FLOOR(RAND() * 10) AS INT)) AS city_id_random, COUNT(*) AS order_cnt FROM user_orders GROUP BY CONCAT(city_id, _, CAST(FLOOR(RAND() * 10) AS INT)); -- 第二步去掉前缀汇总结果 SELECT SPLIT(city_id_random, _)[0] AS city_id, SUM(order_cnt) AS total_cnt FROM ( SELECT CONCAT(city_id, _, CAST(FLOOR(RAND() * 10) AS INT)) AS city_id_random, COUNT(*) AS order_cnt FROM user_orders GROUP BY CONCAT(city_id, _, CAST(FLOOR(RAND() * 10) AS INT)) ) t GROUP BY SPLIT(city_id_random, _)[0];这个方法的逻辑是把一个大Key拆成10个或更多小Key让多个Reduce并行处理最后再汇总。随机前缀的数量要根据倾斜程度调整倾斜越严重前缀越多。6.3 Map Join小表驱动的倾斜规避如果倾斜发生在Join阶段而且有一张表是小表可以用Map Join避免Shuffle-- 小表驱动大表避免Reduce Join的倾斜 SELECT /* MAPJOIN(dim_city) */ o.user_id, d.city_name, SUM(o.amount) FROM user_orders o JOIN dim_city d ON o.city_id d.city_id GROUP BY o.user_id, d.city_name;Map Join的原理是把小表加载到每个Map任务的内存里大表在Map阶段直接完成Join不需要Reduce。这样既避免了Shuffle也避免了倾斜。但Map Join有前提小表必须足够小默认阈值是25MB。如果小表超过阈值Hive会退化成Reduce Join。可以通过SET hive.mapjoin.smalltable.filesize调整阈值但不要调太大否则每个Map任务都要加载大表内存会爆。6.4 空值引发的倾斜及处理还有一种隐蔽的倾斜Join字段有大量NULL值。比如LEFT JOIN时左表的city_id有大量NULL这些NULL会被当成同一个Key全部进入一个Reduce。-- 处理NULL值倾斜给NULL赋随机值 SELECT o.user_id, d.city_name FROM user_orders o LEFT JOIN dim_city d ON COALESCE(o.city_id, CONCAT(null_, CAST(RAND() * 100 AS INT))) d.city_id;这样NULL值就被打散成100个随机Key不会集中到一个Reduce。但要注意如果业务上NULL有特殊含义不能随便打散需要根据实际情况处理。7. 从100道题到真实项目的最后一公里7.1 建表规范练习题不会教你的DDL细节练习题通常直接给一张现成的表但真实项目里你要自己建表。Hive建表有几个关键决策存储格式TextFile是默认格式但性能最差。ORC和Parquet是列式存储压缩率高、查询快。生产环境优先用ORC特别是需要更新和事务的场景。CREATE TABLE user_orders ( user_id BIGINT COMMENT 用户ID, order_id STRING COMMENT 订单ID, amount DECIMAL(10,2) COMMENT 订单金额, order_time STRING COMMENT 下单时间 ) COMMENT 用户订单表 PARTITIONED BY (dt STRING COMMENT 分区日期) STORED AS ORC TBLPROPERTIES (orc.compress SNAPPY);分区字段分区字段不能出现在普通列里它是独立的。分区字段的选择要看查询模式如果查询总是带日期条件就按日期分区。分桶如果经常按某个字段Join可以考虑分桶。但分桶会增加写入成本不是所有表都需要。7.2 动态分区批量导入数据的正确姿势实际项目里数据通常是按天批量导入的。用动态分区可以一次导入多天数据-- 开启动态分区 SET hive.exec.dynamic.partition true; SET hive.exec.dynamic.partition.mode nonstrict; -- 动态分区插入 INSERT OVERWRITE TABLE user_orders PARTITION (dt) SELECT user_id, order_id, amount, order_time, dt -- 最后一个字段是分区字段 FROM user_orders_staging;动态分区的坑在于如果dt字段有脏值比如NULL或空字符串会创建出奇怪的分区目录。建议在插入前先过滤WHERE dt IS NOT NULL AND dt ! 。7.3 小文件问题Hive的慢性病Hive的小文件问题很常见每次插入数据都生成一堆小文件时间长了NameNode压力大查询也慢。解决办法是在插入后做合并-- 合并小文件 SET hive.merge.mapfiles true; SET hive.merge.mapredfiles true; SET hive.merge.size.per.task 256000000; -- 256MB SET hive.merge.smallfiles.avgsize 16000000; -- 16MB -- 或者手动合并 INSERT OVERWRITE TABLE user_orders PARTITION (dt 2024-01-01) SELECT * FROM user_orders WHERE dt 2024-01-01;小文件问题的根因是Hive的写入并行度太高每个Reduce任务生成一个文件。如果Reduce数量是100就生成100个文件。可以通过SET hive.exec.reducers.max控制Reduce数量但更根本的解决办法是定期做合并。7.4 面试中那些超纲的Hive问题100道练习题能覆盖语法但面试官往往问得更深。比如Hive的Sort By和Order By有什么区别这是基础题但很多人只答一个全局一个局部面试官想听的是底层执行差异Order By只有一个ReduceSort By有多个Reduce。Hive怎么处理数据倾斜这是进阶题要答出至少三种方案加随机前缀、Map Join、空值处理。Hive的Map Join原理是什么这是原理题要答出小表加载到Distributed Cache每个Map任务从Cache读取小表数据在Map阶段完成Join。Hive的窗口函数和Group By有什么区别这是对比题要答出Group By改变行数窗口函数不改变行数Group By只能做聚合窗口函数可以做排名、累计、偏移。这些问题在100道练习题里不会出现但它们是区分会写SQL和懂Hive的关键。8. 我刷完100道题后总结的几条实战心得刷题这件事我自己的体会是前30道题建立手感中间40道题建立模式最后30道题建立直觉。手感是能写出语法正确的SQL模式是看到题目就知道用哪种解法直觉是看到数据分布就能预判性能瓶颈。几条具体的经验第一每道题至少写两种解法。比如求Top N用ORDER BY LIMIT能解用ROW_NUMBER()也能解用DISTRIBUTE BY SORT BY还能解。写两种以上你才能对比出哪种在Hive里更高效。第二养成看执行计划的习惯。练习题的数据量小怎么写都快。但你要假装数据量很大用EXPLAIN看Stage数量、Reduce数量、有没有笛卡尔积。这个习惯到了真实项目里能救命。第三把每道题的数据分布想清楚。比如求每个城市的订单量你要想城市字段的基数是多少有没有NULL分布均匀吗如果某个城市的数据量特别大会不会倾斜这些思考在练习题里没有但真实项目里天天遇到。第四不要背答案要背模式。100道题做完你应该能总结出十几类模式去重取最新、连续N天、Top N、行转列、列转行、累计求和、环比同比、留存计算、漏斗分析、同环比对比。每类模式记住核心写法遇到新题直接套。第五真实项目里先想数据量再想逻辑。练习题是给定数据写出SQL真实项目是给定需求设计数据流。先确认数据量级、分区设计、更新频率再动手写SQL。这个顺序反了写出来的SQL大概率要重写。最后说一个我踩过的坑有一次做用户留存分析用LEFT JOIN关联两张表结果跑了三个小时没出结果。后来发现是Join字段有大量NULL全部涌到一个Reduce。加了COALESCE打散之后十分钟就跑完了。这个坑在练习题里永远不会遇到但真实项目里一次就让你记住。