1. 为什么我建议你用100道练习题来啃Hive SQL刚接触Hive SQL那会儿我走了不少弯路。抱着官方文档啃了三天语法规则背得滚瓜烂熟结果一上集群写查询就卡壳——不是忘了分区裁剪就是join写反了导致数据量爆炸。后来带我的老哥扔过来一句话“SQL这东西看一百遍不如写十遍。”这句话点醒了我也让我开始有意识地收集和设计练习题从最基础的select到窗口函数、行列转换、数据倾斜处理一道一道死磕。今天回头看那100道题基本覆盖了Hive SQL日常开发中80%以上的场景从零基础到能独立处理ETL任务大概也就两个月的时间。这篇文章就是把我自己整理和踩过坑的这套练习题体系完整拆解出来。不管你是刚转行做数据开发的新人还是从MySQL转Hive想补齐分布式SQL思维的老人这套内容都能直接用。我会把每类题型的考察点、常见错误、优化思路都讲透让你不仅会写还知道为什么这么写。核心关键词就三个Hive、SQL、练习题全文围绕它们展开不扯虚的。2. 整体练习体系设计与思路拆解2.1 为什么是100道题而不是50道或200道这个数字不是拍脑袋定的。我试过给新人安排50道题结果发现覆盖不了窗口函数和行列转换这些高频难点也试过200道的版本大部分人做到120道左右就疲了后面纯粹为了完成任务而写效果反而差。100道是一个比较舒服的区间按难度和知识点分成五个模块每个模块20道左右每天做3到5道一个月内能完整过一遍。具体模块划分是这样的基础查询与过滤占15道聚合与分组占20道多表关联占20道窗口函数占25道行列转换与复杂场景占20道。这个配比是根据实际工作中各知识点的使用频率来定的。窗口函数之所以占比最高是因为它在Hive里既能做排名又能做累计还能替代部分自关联逻辑是区分SQL水平的分水岭。2.2 练习题的数据集怎么选才贴近真实场景很多教程用的数据集太干净了比如经典的emp和dept表字段少、数据量小、没有脏数据。这种题做起来很顺但一到真实环境就懵。我的建议是至少准备三套数据集一套是电商订单场景包含用户表、商品表、订单表、订单明细表字段有几十个数据量在百万级一套是日志场景包含访问日志、用户行为日志字段有嵌套结构还有一套是金融交易场景包含账户表、交易流水表涉及金额计算和状态流转。为什么要这么麻烦因为Hive SQL的很多坑只有在真实数据量下才会暴露。比如小文件问题你在几百行数据上根本感觉不到但数据量上千万后一个没加分区的查询就能跑半小时。再比如数据倾斜小数据集上所有key都均匀分布你永远遇不到某个key占了80%数据的情况。所以练习题的数据集必须有一定规模和“脏”度才能练出真本事。2.3 从零基础到精通的四个阶段划分我把这100道题按学习路径分成四个阶段每个阶段的目标和验收标准都不一样。第一阶段是“能跑通”大概前30道题。目标是看到需求能写出语法正确的SQL不报错、能出结果。这个阶段不追求性能也不追求写法优雅先建立信心。常见错误集中在字段类型不匹配、分区字段没加、group by和select字段不一致这些基础问题上。第二阶段是“写对”第31到60道题。目标是处理多表关联和复杂条件结果要准确。这个阶段开始引入窗口函数很多人在这里卡住因为窗口函数的执行顺序和普通聚合不一样where里不能直接用窗口函数的结果需要用子查询包一层。第三阶段是“写好”第61到85道题。目标是优化查询性能理解Hive的执行计划。这个阶段要开始关注mapjoin、分区分桶、数据倾斜处理这些进阶话题。同一道题可能有三种写法你要能说出哪种最快、为什么。第四阶段是“写巧”第86到100道题。目标是处理行列转换、复杂嵌套、多维度聚合这些偏门但实用的场景。这个阶段做完基本可以独立负责一个中等规模的ETL任务了。3. 核心细节解析与实操要点3.1 基础查询与过滤的五个易错点基础题看似简单但错误率并不低。我统计过身边二十多个新人的做题情况发现五个高频错误反复出现。第一个是分区字段的使用。Hive表通常按日期分区查询时如果不加分区条件会全表扫描。比如一张订单表按dt分区你写select * from orders where order_date 2024-01-01如果order_date不是分区字段这个查询会扫所有分区。正确写法是where dt 2024-01-01。这个错误在小数据量下只是慢几秒在生产环境可能就是几十分钟的差距。第二个是null值的处理。Hive里null和空字符串是两回事where col 和where col is null结果完全不同。更坑的是null参与运算结果还是null比如select null 1返回null。所以在做聚合时如果某列有nullsum和avg的结果可能和预期不一致。建议在ETL层就把null转成默认值比如nvl(col, 0)。第三个是like和rlike的区别。like只支持%和_两个通配符rlike支持正则。很多人用like做复杂匹配写不出来就放弃其实换成rlike一行就搞定。比如匹配手机号where phone rlike ^1[3-9]\\d{9}$用like是做不到的。第四个是in和exists的选择。在Hive里in子查询在数据量大时性能很差因为会转成left semi join。exists同理。更好的做法是用join或者left semi join显式写出来让优化器有更多发挥空间。第五个是limit的位置。select * from table limit 10和select * from (select * from table limit 10) t看起来一样但在有order by的情况下完全不同。前者是先取10行再排序后者是先排序再取10行。如果要取排序后的前10必须用子查询。3.2 聚合与分组group by背后的执行逻辑group by是SQL里最常用的操作之一但很多人不清楚它在Hive里是怎么执行的。简单说group by会触发一次shuffle把相同key的数据分发到同一个reducer。这个过程有两个关键点一是shuffle的量二是reducer的数据倾斜。先讲shuffle量。如果你写select user_id, count(*) from orders group by user_idHive会把所有user_id和对应的记录数传到reducer。如果user_id有1亿个shuffle的数据量就很大。优化思路是先在map端做部分聚合也就是开启hive.map.aggrtrue这样每个map先算本地count再传给reducer汇总能减少不少网络传输。再讲数据倾斜。如果某个user_id特别活跃占了总数据量的30%那这个reducer就会成为瓶颈其他reducer早就跑完了它还在慢慢磨。解决办法有两个一是加随机前缀打散比如concat(user_id, _, cast(rand()*10 as int))先按打散后的key聚合再在外层去掉前缀聚合一次二是如果业务允许单独处理这个大key比如把它过滤出来单独算再union回去。还有一个常见问题是group by和select字段不一致。Hive默认要求select的字段要么在group by里要么在聚合函数里。但有些版本允许关闭这个检查导致结果不可预期。我的建议是永远保持严格模式不要依赖这种“便利”。3.3 多表关联join的三种姿势和选择依据Hive里的join主要有三种common join、mapjoin和bucket mapjoin。每种适用的场景不同选错了性能差十倍不止。common join是默认方式适合两张表都很大的情况。它会在reduce端完成join所以需要shuffle。如果两张表都超过1亿行shuffle的量会非常可观。这时候要考虑能不能用bucket mapjoin也就是两张表都按join key分桶且桶数成倍数关系这样可以在map端完成join省掉shuffle。mapjoin适合一张大表和一张小表的情况。小表会被加载到每个map的内存里大表在map端直接匹配。小表的阈值由hive.mapjoin.smalltable.filesize控制默认25MB。如果你的小表有100MB可以调大这个参数但要确保map端内存够用。我一般建议小表不要超过500MB否则容易OOM。写join时还有几个细节要注意。第一join的key类型要一致如果一边是string一边是intHive会做隐式转换可能导致数据丢失或倾斜。第二join的顺序有讲究大表放后面小表放前面虽然优化器会调整但显式写清楚更稳妥。第三outer join和inner join的选择要基于业务不要为了“保险”全用outer join那样会引入大量null行反而增加计算量。3.4 窗口函数从row_number到lead/lag的实战用法窗口函数是Hive SQL里最强大的工具之一但也是最容易用错的。我见过有人用row_number做去重结果没加partition by导致全局排名数据全乱。先讲最常用的row_number、rank和dense_rank。三者的区别在于处理并列排名的方式。row_number是1、2、3、4不管是否并列rank是1、2、2、4并列后跳号dense_rank是1、2、2、3并列后不跳号。去重场景用row_number最合适因为每个分组内序号唯一。再讲sum over。这个可以算累计值比如sum(amount) over (partition by user_id order by dt rows between unbounded preceding and current row)就是算每个用户截止到当天的累计消费。这里的关键是rows between子句不写的话默认是range between在遇到相同排序值时结果会不一样。我建议永远显式写rows between避免歧义。lead和lag是取前后行的值常用于算同比环比。比如lag(amount, 1) over (partition by user_id order by dt)就是取用户前一天的数据。注意如果前一天不存在lag返回null需要配合coalesce处理。还有一个容易忽略的点是窗口函数的执行顺序。它在group by之后、order by之前执行。所以你不能在where里直接用窗口函数的结果必须用子查询包一层。这个限制经常让新手困惑记住执行顺序就明白了。3.5 行列转换collect_list和explode的配合使用行列转换是Hive面试和实际工作中都高频的考点。行转列用collect_list或collect_set列转行用explode。行转列的场景比如把用户的多条订单合并成一行用collect_list(order_id)得到一个数组。如果要去重用collect_set。注意collect_list不保证顺序如果需要按时间排序要先在子查询里order by再collect_list但Hive不保证这个顺序一定保留更稳妥的做法是用sort_array对结果排序。列转行用explode把数组或map拆成多行。比如select explode(split(tags, ,)) from table可以把逗号分隔的标签拆成多行。explode有个限制不能和其他字段一起select必须用lateral view。写法是select id, tag from table lateral view explode(split(tags, ,)) t as tag。实际工作中经常需要行列转换配合使用。比如先把多行合并成数组做某种计算后再拆开。这时候要注意数据量collect_list如果数组太大会导致单行数据过大影响性能甚至OOM。我的经验是如果数组元素超过1万个就要考虑分批次处理或者换其他方案。4. 实操过程与核心环节实现4.1 环境准备从零搭建Hive练习环境练习Hive SQL首先得有环境。如果你在公司有现成的集群直接申请一个开发库就行。如果没有本地搭建也不复杂。我推荐用Docker跑一个单机版省去配置Hadoop的麻烦。具体步骤是这样的先拉取一个包含Hadoop和Hive的镜像启动容器后进入命令行初始化元数据库然后启动Hive服务。这个过程大概十分钟。需要注意的是单机版Hive默认用Derby做元数据库不支持多会话练习够用但多人协作不行。如果要多人用换成MySQL做元数据库。环境搭好后用beeline或hive命令行连接。我习惯用beeline因为支持更多现代特性。连接命令是beeline -u jdbc:hive2://localhost:10000默认用户名和密码都是空。建表时要注意文件格式。练习阶段用textfile就行方便查看数据。生产环境建议用orc或parquet压缩比高、查询快。分区字段不要放在建表语句的普通字段里要单独用partitioned by声明。4.2 数据集导入把CSV文件加载到Hive表数据集准备好后导入Hive有三种方式。第一种是load data适合本地文件或HDFS文件速度快但不做任何转换。第二种是insert into select适合从其他表导入可以做字段映射和清洗。第三种是外部表建表时指定location数据不动Hive只读。我练习时常用第一种。先把CSV文件放到容器里然后load data local inpath /path/to/file.csv into table orders partition (dt2024-01-01)。注意如果CSV有表头load进去后表头也会成为一行数据需要在查询时过滤掉或者提前用sed命令删掉表头。如果字段分隔符不是逗号建表时要指定row format delimited fields terminated by \t。如果字段里有逗号用逗号分隔会错位这时候要么换分隔符要么用OpenCSVSerde。我踩过这个坑一个商品名称里带逗号导致后面所有字段都偏移了排查了半天。4.3 第一批练习题从select到where的十个必做题前10道题我设计得很基础但每道都有明确考察点。比如第一题是“查询2024年1月1日的所有订单”考察分区过滤。第二题是“查询金额大于100的订单”考察where条件。第三题是“查询金额在100到500之间的订单”考察between。第四题是“查询用户名为空的订单”考察is null。第五题是“查询用户名不为空的订单”考察is not null。第六到第十题开始引入简单函数。比如“查询订单金额的绝对值”考察abs。“查询订单日期的年份”考察year。“查询用户名的长度”考察length。“查询用户名的大写形式”考察upper。“查询订单金额四舍五入到两位小数”考察round。这些题看起来简单但新手经常在null和空字符串上栽跟头。比如“查询用户名为空的订单”如果写成where username 只能查到空字符串查不到null。正确写法是where username is null or username 。这个细节在实际数据清洗中非常重要。4.4 第二批练习题group by和having的配合第11到30题聚焦聚合。典型题目比如“统计每个用户的订单总数”写法是select user_id, count(*) from orders group by user_id。“统计每个用户的订单总金额”写法是select user_id, sum(amount) from orders group by user_id。“统计每个用户订单金额大于100的订单数”这里就要用having了select user_id, count(*) from orders where amount 100 group by user_id。注意where和having的区别。where在group by之前过滤having在group by之后过滤。如果过滤条件不涉及聚合函数放where里性能更好因为可以减少参与分组的数据量。如果涉及聚合函数比如“统计订单数大于5的用户”就必须用havingselect user_id, count(*) as cnt from orders group by user_id having cnt 5。还有一个常见需求是“统计每个用户每个月的订单总额”。这需要按user_id和月份两个字段分组。月份可以用substr(order_date, 1, 7)或者date_format(order_date, yyyy-MM)提取。我建议用后者更直观。4.5 第三批练习题join的四种典型场景第31到50题专门练join。我设计了四种场景inner join、left join、full join和cross join。inner join的题目比如“查询每个订单对应的用户名”需要orders表和users表关联。left join的题目比如“查询所有用户及其订单没有订单的用户也要显示”这时候用left joinorders表在右边没有匹配的行显示null。full join的题目比如“查询所有用户和所有订单包括没有用户的订单和没有订单的用户”用full join。cross join的题目比如“查询所有用户和所有商品的组合”用cross join但要注意数据量用户数乘以商品数可能很大。join题最容易错的是on条件写错。比如“查询每个订单对应的用户名”如果写成select * from orders join users on orders.user_id users.user_id结果是对的。但如果users表里有重复的user_id结果会翻倍。所以join之前最好确认关联键的唯一性或者用distinct去重。还有一个坑是join的字段类型。如果orders.user_id是stringusers.user_id是intHive会做隐式转换可能导致数据丢失。我遇到过user_id前面有空格的情况string和int比较时转换失败结果join不上。解决办法是提前用trim清洗数据。4.6 第四批练习题窗口函数的五个实战案例第51到75题是窗口函数专项。我挑了五个最有代表性的案例。案例一每个用户按订单金额降序排名。写法是select user_id, order_id, amount, row_number() over (partition by user_id order by amount desc) as rn from orders。这个常用于取每个用户金额最高的订单。案例二每个用户截止到当天的累计消费。写法是select user_id, dt, amount, sum(amount) over (partition by user_id order by dt rows between unbounded preceding and current row) as cumulative from orders。案例三每个用户相邻两笔订单的时间间隔。写法是select user_id, dt, lag(dt, 1) over (partition by user_id order by dt) as prev_dt, datediff(dt, lag(dt, 1) over (partition by user_id order by dt)) as diff from orders。案例四每个用户金额最高的前三个订单。写法是先用row_number排名再在外层过滤rn 3。案例五每个用户订单金额的移动平均。写法是avg(amount) over (partition by user_id order by dt rows between 2 preceding and current row)算最近三笔订单的平均值。窗口函数的难点在于理解partition by和order by的作用范围。partition by是分组order by是组内排序。如果不写partition by就是全局排序。如果不写order by就是整个分组。这两个组合不同结果差异很大。4.7 第五批练习题行列转换的复杂场景第76到100题是综合题行列转换是重点。我设计了一个典型场景用户标签分析。原始数据是每个用户一行标签用逗号分隔。需求是统计每个标签的用户数。第一步是列转行把标签拆开select user_id, tag from user_tags lateral view explode(split(tags, ,)) t as tag。第二步是按标签分组统计select tag, count(distinct user_id) from (上一步的结果) group by tag。反过来行转列的场景比如把用户的多条标签合并成一行。写法是select user_id, concat_ws(,, collect_list(tag)) from user_tags group by user_id。注意collect_list不保证顺序如果需要按标签字母排序用sort_array(collect_list(tag))。还有一个复杂场景是多维度聚合。比如同时按日期、地区、品类统计销售额可以用grouping sets或者cube。grouping sets是指定几个维度组合cube是全组合。比如select dt, region, category, sum(amount) from sales group by dt, region, category grouping sets ((dt), (region), (category), ())。这个在报表场景很常用但要注意结果里会有null表示汇总行需要用grouping__id区分。5. 常见问题与排查技巧实录5.1 查询报错从报错信息快速定位问题Hive的报错信息有时候很晦涩但常见的就那么几类。我整理了一个速查表覆盖80%以上的报错场景。报错关键词可能原因解决办法SemanticException字段名写错、表不存在、语法错误检查字段拼写和表名用desc table确认字段ClassNotFoundException缺少依赖jar包检查Hive auxlib目录添加对应jarOutOfMemoryErrormap或reduce内存不足调大mapreduce.map.memory.mb和reduce.memory.mbData truncation字段长度不够检查表定义扩大varchar长度或改用stringInvalid partition分区字段值格式不对检查分区字段类型和值确保匹配Too many counters计数器超限调大hive.max.counters或减少不必要的计数GC overhead limit内存回收频繁调大堆内存检查是否有数据倾斜我遇到最多的是SemanticException基本都是字段名写错。Hive对大小写不敏感但字段名拼错不会自动纠正只会报错。建议写SQL时先用desc formatted table确认字段列表。5.2 性能问题查询跑得慢的六个排查方向查询慢是Hive最常见的痛点。我一般按六个方向排查。第一看有没有走分区。用explain命令查看执行计划如果partition列没有出现在过滤条件里就是全表扫描。解决办法是加分区过滤。第二看有没有数据倾斜。在yarn的web界面看各个reduce任务的耗时如果某个reduce特别慢就是倾斜。解决办法是加随机前缀打散或者单独处理大key。第三看join方式。如果两张表都很大common join的shuffle量会很大。考虑能不能改成bucket mapjoin或者提前过滤减少数据量。第四看小文件数量。如果表目录下有大量小文件map任务数会很多启动开销大。解决办法是用insert overwrite重写表或者用alter table concatenate合并小文件。第五看有没有不必要的distinct。count(distinct)会触发一次shuffle如果数据量大很慢。可以改成先group by再去重计数或者用approx_distinct近似。第六看资源分配。如果队列资源紧张任务排队时间长。可以调大并行度或者错峰执行。5.3 数据倾斜识别、定位和三种解决方案数据倾斜是Hive性能的头号杀手。识别方法很简单看reduce任务的耗时分布如果最长和最短差几倍以上基本就是倾斜。定位倾斜key的方法是在查询里加distribute by把数据按key分发然后看哪个key的数据量特别大。或者用select key, count(*) from table group by key order by count(*) desc limit 10找出top key。解决方案有三种。第一种是加随机前缀适合group by场景。比如select concat(key, _, cast(rand()*10 as int)) as new_key, count(*) from table group by new_key然后再外层去掉前缀汇总。第二种是mapjoin适合小表join大表把小表加载到内存避免shuffle。第三种是拆分大key把大key的数据单独拿出来处理再union回去。我实际用下来第一种最通用但要注意随机前缀的数量太少打散效果不好太多会增加reduce任务数。一般10到100之间比较合适。5.4 小文件问题产生原因和合并策略小文件是Hive的另一个顽疾。产生原因主要有三个一是频繁insert每次insert产生一个文件二是分区太多每个分区数据量小三是reduce任务数太多每个reduce输出一个文件。小文件的危害是增加namenode压力同时每个小文件对应一个map任务启动开销大。解决办法分事前和事后。事前是调整参数比如hive.merge.mapfilestrue在map端合并hive.merge.mapredfilestrue在reduce端合并。事后是用alter table table_name partition(dt2024-01-01) concatenate合并已有文件。还有一个技巧是控制reduce任务数。默认是hive.exec.reducers.bytes.per.reducer256MB如果数据量小reduce数会很多产生很多小文件。可以调大这个值比如调到1GB减少reduce数。5.5 实操心得我踩过的五个坑第一个坑是分区字段类型。我建表时把dt定义成string但查询时写where dt 20240101没有引号Hive把20240101当成数字和string比较时转换失败结果查不到数据。后来养成习惯分区字段永远加引号。第二个坑是join的null值。如果join key有nullinner join会过滤掉这些行left join会保留但右边字段全是null。我一开始没注意导致统计结果少了数据。后来在join前先用where key is not null过滤或者用nvl(key, unknown)填充。第三个坑是collect_list的顺序。我以为子查询里order by了collect_list就会按顺序结果发现Hive不保证。后来改用sort_array或者用collect_list(concat(lpad(seq, 10, 0), value))再截取确保顺序。第四个坑是窗口函数的性能。我在一个大表上用了row_number没加partition by导致全局排序跑了两个小时。后来加了partition by降到十分钟。窗口函数一定要加partition by除非你真的需要全局排序。第五个坑是动态分区。我开启动态分区后一次insert产生了上万个分区每个分区一个小文件namenode差点崩了。后来限制动态分区的最大数量hive.exec.max.dynamic.partitions1000并且先按分区字段distribute by减少同时打开的文件数。6. 从练习题到实战的进阶路线6.1 如何把练习题转化为工作能力做完100道题只是开始关键是怎么用到工作中。我的建议是每做完一个模块就找一个实际场景去套。比如做完窗口函数模块就去看看公司现有的报表SQL能不能用窗口函数简化。做完行列转换模块就去看看用户标签系统能不能优化。还有一个方法是自己给自己出题。比如看到一张表先想“如果我要统计每个用户的复购率怎么写”然后动手写写完再优化。这种主动练习比被动做题效果好得多。6.2 进阶学习从Hive SQL到Spark SQLHive SQL和Spark SQL语法大部分兼容但有些差异。比如Spark SQL支持更丰富的函数窗口函数的性能也更好。如果你已经掌握了Hive SQL转Spark SQL大概一周就能上手。重点看差异部分比如数据类型、函数名、执行计划。我个人的经验是Hive SQL打基础Spark SQL做进阶。Hive的语法更严格练出来的基本功扎实。Spark SQL更灵活适合做复杂计算。两者结合基本能覆盖所有离线数仓场景。6.3 面试准备Hive SQL高频考点梳理如果你在准备数据开发的面试Hive SQL是必考项。高频考点包括窗口函数的几种用法、行列转换的写法、数据倾斜的处理、join的优化、分区分桶的原理。我建议把100道题里的窗口函数和行列转换部分反复做三遍做到不看答案能默写。面试时还会问一些原理性问题比如“Hive的group by底层是怎么实现的”、“mapjoin的原理是什么”、“分桶表的作用是什么”。这些问题需要理解执行计划不能只背语法。我的方法是多看explain的输出理解每个stage在做什么。6.4 持续提升建立自己的SQL题库最后分享一个习惯建立自己的SQL题库。每次遇到新的业务场景就把SQL保存下来加上注释说明考察点和易错点。时间长了这就是你个人的知识库。我现在的题库有300多道题覆盖了电商、金融、物流等多个行业换工作时直接拿出来复习效率很高。题库的整理也有技巧。按知识点分类每个知识点下列出典型题目和变体。比如窗口函数下面再分排名、累计、前后行、移动平均等子类。每个子类挑一道最典型的题作为代表其他题作为变体。这样复习时先看代表题回忆不起来再看变体。这套100道题的体系我用了三年带过十几个新人反馈都不错。关键不是题量而是每道题都要吃透知道为什么这么写、还能怎么写、哪种写法最快。SQL这东西入门容易精通难但只要有系统的方法和足够的练习两个月从零到能独立干活是完全可行的。