1. 从MySQL转过来的人第一步最容易摔在哪Hive这东西说白了就是一个“把SQL翻译成分布式计算任务”的翻译官。很多从传统关系型数据库转过来的同学拿着写MySQL的思维直接上手Hive SQL结果第一个星期就各种怀疑人生明明语法看起来差不多为什么这也不能做那也不能做先说清楚一个最核心的认知Hive SQL不是标准SQL它是“类SQL”。Hive最初的设计目标是让熟悉SQL的数据分析师能操作HDFS上的海量数据但它底层跑的是MapReduce或者Tez、Spark这类离线计算引擎。这意味着它天生就是给“批处理”场景用的不是给在线事务用的。我第一次用Hive的时候踩过这么个坑原来在MySQL里特别自然的写法——UPDATE tablename SET column value WHERE id 1在Hive里直接给我报错。后来才明白Hive的定位是数据仓库工具它的核心操作是“批量导入、批量查询、批量覆盖写入”而不是单行级别的增删改。Hive从0.14版本开始支持基于ACID的事务但那个限制条件一大堆实际生产里基本没人拿它做频繁的update和delete这压根就不是它的主场。再有一个本质差异是“Schema on Read”和“Schema on Write”的区别。传统数据库是写入时校验数据写进去的时候就得符合表结构不符合直接拒绝。Hive不一样Hive是读的时候才做校验——你往一个表里load一份数据即使数据格式和表结构对不上它也不一定会拦你但等你查询的时候那个字段解析出来可能就是NULL。这点特别容易坑新手数据明明导进去了select的时候看到的全是NULL第一反应是数据有问题实际上多半是分隔符没对上或者字段数和表结构不匹配。还有一个绕不开的概念Hive的表其实就是HDFS上的目录。建一张表本质是在Hive的数据仓库目录下创建一个文件夹分区表就是文件夹下面再套子文件夹。理解了这一层后面理解分区裁剪、外部表、load数据的原理就全都通了。这句话我在带人的时候一定会强调理解了它Hive至少懂了一半。所以这篇文章我打算按这个思路来讲先讲讲建表和存储模型再讲查询语法里几个Hive特有的关键字然后是窗口函数在数仓场景中的实战姿势接着是数据处理中最容易踩的坑最后聊一聊慢SQL的优化思路。内容定位是基础但基础不等于肤浅——很多工作五六年的人对distribute by和partition by这两个东西的区别都还含糊其辞这个我会重点讲明白。如果你是刚接触大数据、准备面试大数据开发岗位或者已经在公司里用Hive做日常取数但一直“知其然不知其所以然”这篇应该能帮你把底层的逻辑理顺。2. 建表是Hive的必修课内部表、外部表、分区表、分桶表很多Hive教程上来就直接讲select怎么写、join怎么写但我坚持认为建表才是Hive SQL的第一课。因为Hive的很多“灵性”都在建表这个环节决定了后面查询写得好不好、跑得快不快其实在建表那一刻就埋下了伏笔。2.1 内部表和外部表的本质区别内部表Managed Table和外部表External Table是Hive里最基础也是最容易混淆的概念。一句话记忆法内部表的生命周期受Hive控制外部表的生命周期不受Hive控制。这句话怎么理解我举个例子。你建一个内部表然后执行DROP TABLEHive会把表结构和HDFS上对应的数据目录一起删掉。但如果你是外部表执行DROP TABLE之后删掉的只是元数据——也就是MySQL里存的那张表结构信息——HDFS上的数据文件原封不动还在那里。那什么时候用内部表什么时候用外部表说句大白话数据不是Hive“亲生”的时候用外部表。比如公司里有一个数据管道上游用Flume或者DataX把日志文件、业务库数据同步到了HDFS某个目录下然后你想用Hive来查这些数据这种情况必须建外部表。因为数据是别人的Hive只是过来“借用”的哪天你把这个表删了如果把底层数据也误删了那个责任谁也担不起。我见过不止一次这样的生产事故有同事把清洗后的中间结果表建成了内部表后来因为表重建的需求执行了drop结果整个数据目录连带所有历史分区一起没了最后只能从源头重新补数。所以我的习惯是ODS层和DWD层的数据表一律外部表因为底层是上游同步过来的原始数据文件ADS层和应用层的临时结果表可以用内部表反正数据也是Hive自己算出来的删了还能重算。建表语句的对比给你贴出来-- 内部表 CREATE TABLE IF NOT EXISTS dwd_order_detail ( order_id BIGINT, user_id BIGINT, product_id BIGINT, amount DECIMAL(10,2), create_time STRING ) STORED AS ORC; -- 外部表location指向HDFS上已有的数据目录 CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_log ( user_id BIGINT, action STRING, log_time STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE LOCATION /data/ods/user_log;注意看外部表多了一个LOCATION这个就是告诉Hive“数据在那儿你去读吧”。内部表不写location的话数据默认放在Hive数仓目录下也就是/user/hive/warehouse/库名.db/表名这个路径。2.2 分区表不只是为了组织数据更是为了救命分区表这个概念我打个比方。你有一整个屋子堆满了杂物找一样东西得把整个屋子翻一遍但如果你把东西分门别类放进不同抽屉每个抽屉上贴个标签找东西的时候只需要打开对应的抽屉就行。分区表干的就是这个事。最常见的就是按日期分区。一张每天新增好几亿条日志的表如果不分区每次查询的时候哪怕只要某一天的数据Hive也得把全表扫描一遍——这在几TB甚至几十TB的数据量下跑一次好几个小时谁也扛不住。按日期分区之后查询条件里带一个where dt 2024-06-01Hive通过元数据定位到你想要的那个分区目录只扫那个目录下的文件速度差了几十上百倍。建分区表有两种方式一种是静态分区一种是动态分区。静态分区就是在插入数据的时候手动指定分区值-- 建表 CREATE TABLE dwd_order_detail ( order_id BIGINT, user_id BIGINT, amount DECIMAL(10,2) ) PARTITIONED BY (dt STRING) STORED AS ORC; -- 静态分区插入 INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt 2024-06-01) SELECT order_id, user_id, amount FROM ods_order WHERE create_time 2024-06-01;动态分区适合一次性导入大量分区的场景比如你要把近一年的历史数据刷进来不可能写365个静态分区语句吧。这个时候开启动态分区让Hive根据select出来的字段值自动创建分区-- 开启动态分区写SQL的时候需要先设置这几个参数 SET hive.exec.dynamic.partitiontrue; SET hive.exec.dynamic.partition.modenonstrict; INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt) SELECT order_id, user_id, amount, substr(create_time, 1, 10) AS dt FROM ods_order WHERE create_time 2023-06-01;注意动态分区这里有个细节PARTITION (dt)括号里只写分区字段名不写值值从select的最后一个字段里取。我见过有人把分区字段名和select出来的字段名搞混结果运行时一直报错找不到列。其实select出来的那个AS dt别名只要跟分区字段名一致就行但它本质上是“字段位置”的映射——select的最后一个字段对应最后一个分区字段位置错了数据就乱套了。2.3 分桶表抽样和join优化的利器分桶Bucket和分区是两码事。分区是按“业务维度”切目录分桶是按“哈希值”切文件。分桶表的原理是对指定的分桶字段做哈希计算然后对桶数取模数据根据取模结果散落到不同的文件里。CREATE TABLE user_info_bucketed ( user_id BIGINT, user_name STRING, age INT ) CLUSTERED BY (user_id) INTO 16 BUCKETS STORED AS ORC;这里CLUSTERED BY (user_id)指定了分桶字段INTO 16 BUCKETS指定了桶数。分桶有什么实际好处第一是抽样查询快用TABLESAMPLE可以快速取一部分数据做测试第二是分桶join效率高如果两张表的分桶字段和桶数一致join的时候可以只join对应的桶文件不用全表笛卡尔积式地去搞这在Hive里叫Bucket Join。不过我得说句实话分桶表在生产环境中的使用频率远没有分区表高。因为它对文件数量的控制需要你预先估算好桶数桶数设太少了文件大小不均匀设太多了每个文件太小、产生大量小文件反而不利于查询性能。如果你刚开始接触Hive先把分区表玩明白分桶表可以先理解原理等真正碰到亿级大表join的场景再深入研究。2.4 文件格式怎么选TEXTFILE、ORC还是Parquet建表时还有一个关键参数是文件格式。很多初学者不管三七二十一默认TEXTFILE就建了。但你要知道TEXTFILE只是方便人眼查看它有几个致命的缺点存储空间大、压缩率低、查询性能差。我现在的习惯是计算层的表统一用ORC如果后续要对接Spark或者Presto/Trino跨引擎查询就选Parquet。ORC和Parquet都属于列式存储格式。列式存储的好处是查询的时候可以只读取需要的列跳过无关数据。举个例子一张表有100个字段但你只需要查其中两个字段列式存储下只需要读两列的数据文件行式存储则必须把每一行的完整数据都读一遍才能筛选出来这个差距在宽表场景下非常明显。建表的时候用ORC格式还可以指定压缩方式CREATE TABLE dwd_order_detail ( order_id BIGINT, user_id BIGINT, amount DECIMAL(10,2) ) PARTITIONED BY (dt STRING) STORED AS ORC TBLPROPERTIES (orc.compress SNAPPY);Snappy压缩是压缩速度和压缩比比较均衡的一个选择。Zlib压缩率更高但CPU开销大LZO和Snappy比较快但压缩率一般。生产环境里Snappy基本是首选我还没见过哪个公司的Hive数仓主力表不用Snappy的。3. 查询语法里的几个“Hive专属”关键字搞懂它们才算入门Hive的SQL语法里有一组特别容易被混淆的关键字组合ORDER BY、SORT BY、DISTRIBUTE BY、CLUSTER BY。这四个东西可以说是Hive面试题里的常青树也是实际写数仓SQL时最影响结果正确性的几个关键字。3.1 ORDER BY和SORT BY的区别一个全局排序一个局部排序在MySQL里ORDER BY就是排序没什么好说的。但在Hive里ORDER BY是全局排序——它会把所有数据集中到一个Reducer里去做排序。这样做的问题在于数据量大到一定程度一个Reducer根本扛不住跑很久都出不来结果。所以Hive对ORDER BY有一个限制条件在严格模式下ORDER BY必须配合LIMIT使用。为什么必须加LIMIT因为加了LIMIT之后Hive可以做一些优化比如在Map端就先做一次局部的TopN排序最后到Reducer那边只需要归并少量结果就行。如果你不加LIMIT就对一张上亿条记录的表做全局排序那基本等于让一个Reducer处理全部数据除非你很有耐心否则等到的多半是任务超时。SORT BY是每个Reducer内部排序。换句话说每一个Reducer收到的数据在Reducer内部是有序的但多个Reducer之间的数据并没有整体上的先后顺序。SORT BY不会把所有数据集中到一个Reducer所以它的执行效率比ORDER BY高得多但结果不是全局有序。那什么场景下用SORT BY典型场景是做“分组排序输出”。比如你有一份用户日志按用户ID哈希到不同Reducer你想在每个Reducer内部按时间排好序方便下游做session拼接这种场景用SORT BY就非常合适。3.2 DISTRIBUTE BY控制数据怎么分发的关键DISTRIBUTE BY控制的是数据按什么字段分发到不同的Reducer。它本身不排序只负责把相同字段值的数据路由到同一个Reducer。看到这里你应该反应过来了DISTRIBUTE BY就是Hive版的“按照某个key做哈希分发”。它跟GROUP BY的底层逻辑有点像但两者不在一个层面——GROUP BY是做聚合运算DISTRIBUTE BY只是控制数据分发规则不做任何聚合。PARTITION BY和DISTRIBUTE BY的区别在哪里PARTITION BY是窗口函数里的关键字它是在一个已经算出来的结果集上做逻辑上的分组这个分组只影响窗口函数计算的窗口范围不改变数据实际的物理分布。我们通常说的“分组”指的是GROUP BY这种物理聚合而PARTITION BY更像是在一个集合上画了几条分界线每条分界线内的数据参与各自的窗口计算。DISTRIBUTE BY则是直接干预Shuffle阶段的分发规则——它决定了一行数据到底被MapReduc的Shuffle环节送到哪个Reducer上去。我举一个非常经典的例子感受一下两者怎么配合-- 场景按user_id分发数据到不同的Reducer每个Reducer内部按access_time排序 SELECT user_id, page_url, access_time FROM user_access_log DISTRIBUTE BY user_id SORT BY user_id, access_time;这条SQL的逻辑是先按user_id做哈希分发保证同一个用户的数据进入同一个Reducer然后在每个Reducer内部按access_time排序。注意这里先写DISTRIBUTE BY再写SORT BY顺序不能反。这样最终产出的多份文件里每一个文件都对应一个用户群体的有序日志非常适合下游做用户行为路径分析。3.3 CLUSTER BY当DISTRIBUTE BY和SORT BY的字段一致时CLUSTER BY是DISTRIBUTE BY和SORT BY的“合体版”前提是两者的字段必须完全一致。比如SELECT user_id, page_url FROM user_access_log CLUSTER BY user_id;等价于SELECT user_id, page_url FROM user_access_log DISTRIBUTE BY user_id SORT BY user_id;但注意容错CLUSTER BY只支持升序排序如果你要倒序还是得老老实实分开写DISTRIBUTE BY加SORT BY。此外分桶表的CLUSTERED BY跟CLUSTER BY不是一个东西前者建表时定义分桶规则后者是查询时的控制语句别搞混了。3.4 JOIN的几种坑与LEFT SEMI JOIN的神奇功效Hive里的JOIN类型除了我们熟悉的INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN还有一个大数据场景特别常用的LEFT SEMI JOIN。LEFT SEMI JOIN相当于SQL里的IN操作。比如你要找出所有下过单的用户信息SELECT u.user_id, u.user_name FROM dim_user u LEFT SEMI JOIN dwd_order o ON u.user_id o.user_id;这跟WHERE u.user_id IN (SELECT user_id FROM dwd_order)的执行效果类似但Hive对IN子查询的支持在早期版本很弱LEFT SEMI JOIN是更可靠的写法。注意一个关键点LEFT SEMI JOIN的子查询表中select列表里只能出现ON条件中用到的字段不能像普通join那样直接select右表的其他字段。因为它本质上只是“左表的数据是否在右表中有匹配”不会返回右表的任何字段。JOIN里最常见的坑是数据倾斜。比如你拿用户维表和一张订单事实表做join订单表里可能有某个“超级用户”贡献了几千万条订单他的user_id在分发时会全部进入同一个Reducer那个Reducer直接被打爆别的Reducer都跑完了它还在慢慢磨整个任务卡在这里。后面我专门讲优化的时候会详细说怎么处理。还有一个坑是关于NULL值的join。如果join的key有NULL所有NULL值会全部进入同一个Reducer——因为哈希值一样同样会触发数据倾斜。所以做关联之前通常得先把key为NULL的数据过滤掉或者把NULL统一替换成一个随机字符串来打散。3.5 行转列lateral view explode的组合拳做数仓ETL的时候几乎绕不开“一行展开成多行”的需求。比如某个表里有一个字段存的是一串逗号分隔的标签标签1,标签2,标签3你想把它拆成三行输出。这个操作在Hive里靠的是EXPLODE配合LATERAL VIEW。SELECT order_id, tag FROM dwd_order LATERAL VIEW EXPLODE(SPLIT(tag_list, ,)) t AS tag;这里SPLIT把字符串拆成数组EXPLODE把数组展开成多行LATERAL VIEW把展开后的每一行和原表的那一行关联起来。反过来如果你想多行合并成一列用CONCAT_WS加COLLECT_LIST或者COLLECT_SETSELECT user_id, CONCAT_WS(,, COLLECT_LIST(order_id)) AS order_id_list FROM dwd_order GROUP BY user_id;COLLECT_SET会自动去重COLLECT_LIST保留所有值按需选择。这个组合技在数据清洗、标签加工的场景里太常用了一定要记牢。4. 窗口函数数仓SQL的灵魂也是面试必考点如果说前面讲的是Hive SQL的“骨架”那窗口函数就是Hive SQL的“灵魂”。在数仓的分析场景里分组TopN、同比环比、累计求和、连续登录天数之类的需求用窗口函数写会非常优雅而且性能远比自关联要好。4.1 窗口函数的“窗口”到底是什么意思窗口函数的基本结构长这样函数() OVER ( PARTITION BY 字段1, 字段2 ORDER BY 字段3 [ROWS BETWEEN 边界规则] )PARTITION BY决定窗口怎么划分它和GROUP BY看起来很相似但区别在于GROUP BY会把多行压缩成一行而PARTITION BY保持行数不变每一行都保留原样只是每行多了一个“窗口计算结果”。你可以把窗口理解成“以当前行为基准往四周划定一个范围”这个范围内的行数据参与计算。ROWS BETWEEN就是用来定义这个范围边界的常见的规则有ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区起点到当前行用于累计值ROWS BETWEEN 3 PRECEDING AND CURRENT ROW从当前行往前数3行到当前行用于滑动计算ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING整个分区相当于分区内的全局计算我最早学的时候老是记不住这些边界后来拿“看窗口”打了个比方窗口函数就是隔着一扇窗户看数据你可以把窗户开到“从分区第一行到当前行”也可以把窗户开到“前后各三行”。这个窗户开多大就由ROWS BETWEEN决定。4.2 三大排名函数ROW_NUMBER、RANK、DENSE_RANK这三个函数特别像但结果差一点。我直接用一组数据感受一下分数ROW_NUMBERRANKDENSE_RANK100111982229832295443ROW_NUMBER()不管有没有重复就是给每行编一个不重复的序号1、2、3、4…一路排下去RANK()遇到相同值排名相同但后面的名次会跳比如1、1、3、4DENSE_RANK()排名相同但名次不跳比如1、1、2、3最常见的应用是“每组取TopN”SELECT user_id, order_amount FROM ( SELECT user_id, order_amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS rn FROM dwd_order ) t WHERE rn 3;注意这里必须先在一个子查询里生成排名然后在外面用WHERE rn 3过滤不能直接在同一个查询层里写WHERE ROW_NUMBER() OVER (...) 1因为窗口函数是在WHERE之后才计算的。4.3 SUM/AVG等聚合函数窗口化SUM() OVER()这个组合在日常取数里用得极其频繁。比如计算“每天的累计销售额”SELECT dt, daily_amount, SUM(daily_amount) OVER (ORDER BY dt) AS cum_amount FROM dwd_sales_daily WHERE dt 2024-01-01;这里没有写PARTITION BY表示整个结果集是一个大窗口按dt排序后逐行累加。运行结果大概是这样的dtdaily_amountcum_amount2024-01-011001002024-01-021502502024-01-03120370如果再搭配PARTITION BY还可以做分组内累计。比如每个类目下的累计销售额SELECT category, dt, daily_amount, SUM(daily_amount) OVER (PARTITION BY category ORDER BY dt) AS cat_cum_amount FROM dwd_sales_daily;4.4 LAG和LEAD跟上一行、下一行对话LAG()和LEAD()用来取同一分区内当前行之前或之后某一行某个字段的值。典型的应用场景是计算同比环比以及与上一笔订单的时间差。SELECT dt, daily_amount, LAG(daily_amount, 1) OVER (ORDER BY dt) AS prev_amount, daily_amount - LAG(daily_amount, 1) OVER (ORDER BY dt) AS diff_amount FROM dwd_sales_daily;LAG(字段, 偏移量, 默认值)里的偏移量表示往上取几行默认是1。如果当前行没有上一行会返回NULL也可以通过第三个参数给个默认值。实际做用户行为分析时“相邻两次点击的时间差”这个需求也可以用它SELECT user_id, click_time, LAG(click_time, 1) OVER (PARTITION BY user_id ORDER BY click_time) AS prev_click_time, UNIX_TIMESTAMP(click_time) - UNIX_TIMESTAMP(LAG(click_time, 1) OVER (PARTITION BY user_id ORDER BY click_time)) AS interval_seconds FROM user_click_log;4.5 经典面试题连续登录天数怎么算这个题目在Hive面试里出现的频率非常高“求每个用户连续登录的最大天数”。核心思路是用ROW_NUMBER()给每个用户登录日期编号然后用“登录日期减去编号”这个差值来分组。同一个人如果登录日期是连续的那么日期减去编号的一定是同一个值一旦断开了差值就会改变。WITH login_data AS ( SELECT user_id, login_date, DATE_SUB(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM ( SELECT user_id, login_date FROM user_login_log WHERE login_date 2024-01-01 GROUP BY user_id, login_date ) t1 ) SELECT user_id, COUNT(*) AS continuous_days FROM login_data GROUP BY user_id, grp;这个写法我建议你亲手跑一遍。第一次看可能有点绕但琢磨明白之后你会对窗口函数和“分组”有了更立体的理解这里的“组”不是表结构里的分区而是通过计算构造出来的一个逻辑分组。5. 数据导入导出与ETL实操这些坑我替你们踩过了查询写得再花哨数据进不来、出不去都是白搭。Hive SQL的数据导入导出这块表面上语法很简单真正跑起来的时候坑不少。5.1 LOAD DATA和INSERT OVERWRITE的边界LOAD DATA用于把HDFS上的文件“搬”进Hive表。注意这个“搬”字的含义默认情况下它是把文件移动过去不是复制。如果你在load之后去看原路径文件已经没了。-- 从HDFS本地目录加载数据到Hive表 LOAD DATA INPATH /tmp/user_data.txt OVERWRITE INTO TABLE ods_user; -- 从本地文件系统加载数据注意是本地文件系统不是HDFS LOAD DATA LOCAL INPATH /home/hadoop/user_data.txt INTO TABLE ods_user;LOCAL关键字表示文件在提交Hive语句的那个节点本地磁盘上不加LOCAL则表示文件在HDFS上。有同学把这个搞反了在本地文件路径上写了HDFS路径结果报错“文件不存在”检查半天才发现是这个问题。INSERT OVERWRITE是Hive里“覆盖写”的标准姿势。跟普通数据库的INSERT INTO语义不一样INSERT OVERWRITE会先把目标分区或目标表的数据清空再写入新数据。所以它天然适合数仓场景里的“全量重算”和“分区重刷”。INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt 2024-06-01) SELECT order_id, user_id, amount FROM ods_order WHERE dt 2024-06-01;注意如果不指定分区直接对整表INSERT OVERWRITE那会清空整个表的数据。操作前一定要确认你是在重刷哪个分区别一激动把全表数据冲了。5.2 动态分区写入和小文件问题上一章我们提到过的动态分区在ETL里特别常用。但动态分区开启后有一个很现实的问题它会根据select出来的分区字段值来生成分区如果你select出来的分区字段有很多个不同的值就会生成很多个分区目录如果数据量分布不均匀某些分区可能只有很小的一坨数据却也要单独占一个文件。这就引出了另一个大问题小文件过多。HDFS上的每个文件在NameNode内存里都有一份元数据记录小文件太多会占满NameNode内存而且查询时每个文件都需要启动一个Map任务去读文件越多Map数越大调度开销甚至超过计算本身。缓解小文件问题有几个实用手段设置hive.merge.mapfilestrue、hive.merge.size.per.task256000000256MB让Hive在Map阶段结束后自动合并小文件。动态分区写入的时候设置hive.exec.max.dynamic.partitions1000防止一次性创建过多分区把元数据压垮。用DISTRIBUTE BY对分区字段做一次哈希分发让同一个分区的数据尽量进入同一个Reducer从而生成更大的文件。第三个手段值得展开说说。动态分区默认情况下Reducer个数可能很多每个Reducer都会为它接收到的数据创建文件这些文件散落到各自所属的分区目录。如果你写的是INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt) SELECT ..., dt FROM ods_order DISTRIBUTE BY dt;这样数据会先按dt哈希分发同一个dt的数据集中到少数几个Reducer最终每个分区目录下的文件数量就少很多文件大小也更大。这条SQL看起来只是多了一行DISTRIBUTE BY dt实际对生产环境的影响非常大。5.3 NULL和分隔符的那些事Hive读数据的时候默认的分隔符是\001CtrlA这在很多数据集里都是默认格式。但实际生产中经常对接外部系统导出的数据这些系统用的可能是逗号、制表符或者竖线。建表的时候ROW FORMAT DELIMITED FIELDS TERMINATED BY ,这样指定就行。这里有一个非常隐蔽的坑如果字段值是文本类型而文本内容里恰好包含分隔符会怎么样比如你用逗号做分隔符但某个字段的值是hello,worldHive不会像传统数据库那样识别引号转义它会直接把逗号当成字段分隔符把这个值一刀切成两个字段后面的字段全部错位。这是Hive读文本文件的天然缺陷处理这种数据必须在上游清洗阶段就做好转义或者干脆用Parquet/ORC这类自带schema的格式来规避。NULL值在Hive里默认用什么表示写入的时候用\N表示NULL读的时候默认也把\N识别为NULL。但如果你从外部导入的数据里空字段是用空字符串表示的那个字段查出来不是NULL而是空字符串。这在实际取数的时候会带来问题——比如你写WHERE column IS NOT NULL但空字符串的行照样会被查出来因为 IS NOT NULL在Hive里结果是true。处理方式往往是WHERE column ! AND column IS NOT NULL或者在建表时把空字符串替换成\N。5.4 数据导出从Hive表到本地文件INSERT OVERWRITE LOCAL DIRECTORY可以把查询结果导出到本地文件系统INSERT OVERWRITE LOCAL DIRECTORY /tmp/export_result ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id, order_amount FROM dwd_order WHERE dt 2024-06-01;有一点要注意导出到本地目录时本地指的是提交SQL的客户端所在机器不是HiveServer2所在的服务器的本地目录。很多刚入门的朋友在这里栽过跟头从DataGrip或者DBeaver连Hive执行导出语句结果找遍了自己电脑都没找到文件——因为在HiveServer2那台机器上。如果是走HiveServer2执行的SQL导出目录写/tmp/xxx实际上文件落在HiveServer2节点的/tmp/xxx下。如果要把数据导出成文件发给业务方我更推荐用hive -e命令配合重定向到本地文件或者直接写个Scala/Java程序用JDBC查出来再生成CSV这样路径可控、格式可控不会出现上面这种路径理解偏差。6. Hive SQL优化从慢SQL到“能跑的快SQL”说实话很多人的Hive SQL都能跑通但“能跑”和“跑得快”之间差着十万八千里。一条烂SQL可能让几万个Map任务空转几小时一条好SQL同样的数据量十分钟跑完。下面这几个优化点是我在实际业务里反复用到、且性价比极高的。6.1 分区裁剪和列裁剪最基础的优化这两点与其说是优化不如说是基本素养。分区裁剪就是查询时尽量带分区字段的过滤条件。前面建分区表时已经说过原因这里不再重复。有一点提醒写WHERE dt 2024-06-01 AND dt 2024-06-07是一回事但如果你在子查询里先全表扫描、外面再过滤分区裁剪就失效了。比如-- 不推荐子查询先全量查外层再过滤 SELECT * FROM ( SELECT * FROM dwd_order_detail ) t WHERE dt 2024-06-01; -- 推荐过滤条件下推到内层 SELECT * FROM ( SELECT * FROM dwd_order_detail WHERE dt 2024-06-01 ) t;Hive的谓词下推在某些条件下能自动优化掉第一种写法但依赖优化器不如自己写对。列裁剪就是select的时候只拿需要的字段别动不动SELECT *。列式存储下少select一个字段可能就少读一个文件块性能差异在宽表上尤其明显。我见过有人对着一张80个字段的表做SELECT *然后只取其中两列白白多读了78列的数据。6.2 大表Join小表MAPJOINHive最典型的join性能问题就是数据倾斜和Reduce阶段的压力。如果一个超大表和一个小表做join比如几亿行的订单表join一个几千行的商品维表常规做法是全部数据发到Reducer端做匹配代价非常高。MAPJOIN的思路是把小表加载到每个Map任务的内存里Map阶段直接读大表数据、在内存里和小表匹配完全跳过Reduce阶段。这样既避免了Reducer压力也减少了Shuffle的网络开销。-- 自动开启MapJoin SET hive.auto.convert.jointrue; SET hive.mapjoin.smalltable.filesize25000000; -- 25MB以内的小表自动走MapJoin SELECT /* MAPJOIN(dim_product) */ o.order_id, p.product_name FROM dwd_order o JOIN dim_product p ON o.product_id p.product_id;那个/* MAPJOIN(dim_product) */是Hive的Hint语法可以强制指定哪个表作为小表加载到内存。hive.mapjoin.smalltable.filesize可以调大但如果小表太大内存溢出的风险也会增加一般设置在25MB到100MB之间。6.3 数据倾斜的三种解法套路数据倾斜是最常见的Hive性能杀手。它的典型表现是任务卡在99%一直跑不完查看YARN日志发现某个Reducer处理的数据量是其他Reducer的几十倍。产生原因和解法可以分成三类第一类join key有大量NULL。所有NULL值进入同一个Reducer。解法是过滤NULL或者给NULL一个随机值打散SELECT * FROM dwd_order o LEFT JOIN dim_user u ON NVL(o.user_id, CONCAT(random_, RAND())) u.user_id;这样NULL值会被打散到不同的Reducer不会集中在同一个。第二类热点key。比如某个商品是爆款订单量占全表80%join的时候所有这个商品的数据都进入同一个Reducer。解法是把大表的热点key单独拆出来处理拆成“热点数据”和“非热点数据”两条路径最后union all合并。第三类count distinct group by。COUNT(DISTINCT column)这种写法在数据量大的时候会特别慢因为它要去重再统计。一个常见的优化是先用子查询去重再统计-- 慢的写法 SELECT COUNT(DISTINCT user_id) FROM dwd_order WHERE dt 2024-06-01; -- 优化写法 SELECT COUNT(*) FROM ( SELECT user_id FROM dwd_order WHERE dt 2024-06-01 GROUP BY user_id ) t;虽然底层原理类似但显式group by可以让优化器更好地分配Reducer资源实际跑起来往往快不少。6.4 执行引擎的选择MapReduce、Tez还是SparkHive的执行引擎有三种MapReduce默认、Tez、Spark。Hive on Spark现在越来越主流它的性能比MapReduce快好几倍Tez则以DAG优化见长很多CDH发行版默认就是Tez。如果你用的是Hive on Spark记得几个关键参数SET hive.execution.enginespark; SET spark.executor.memory4g; SET spark.executor.cores4;不同引擎对同一份SQL的执行计划差异很大同一个Hive版本下ORDER BY分别在MR和Spark里的表现可以差很多倍。所以如果你发现自己写的Hive SQL在公司的集群上跑得特别慢先看一眼执行引擎是不是配对了。6.5 严格模式关键时刻能救命Hive有一个严格模式strict mode开启之后会限制一些高危操作防止你写出那种能把整个集群拖垮的SQLSET hive.mapred.modestrict;严格模式下以下操作会被禁止对分区表执行查询但没有使用分区字段过滤ORDER BY语句没有带LIMIT笛卡尔积查询没有加WHERE条件这三点写得很合理基本就是Hive SQL最容易出事故的几个场景。我建议在开发环境开启严格模式强制自己养成好习惯生产环境查询一般走调度平台平台侧也可以统一设置。严格模式一开始可能会让你觉得烦比如你只是想快速看一眼某个分区表有哪些数据随手SELECT * FROM table LIMIT 10——在严格模式下是允许的但如果你没带分区过滤条件去扫全表它就会拒绝执行。被拦几次之后你就记住了查分区表必须先带分区条件这个肌肉记忆在关键时刻能替你挡掉不少坑。最后分享一个排查慢SQL的经验我最后想分享一个真实的排查案例正好把这几个知识点串起来。有次线上有个报表任务突然从半小时变成三个小时跑不完我上去排查。先看执行计划发现有一个大步骤是两张千万级事实表做join等值条件是user_id。再看数据分布发现dwd_order表里有个历史遗留问题——早期数据同步的时候某些user_id是空字符串不是NULL。所有空字符串的user_id哈希之后都进了同一个Reducer这个Reducer处理几百万条垃圾数据其他Reducer早就干完了。当时的修复很简单先过滤掉无效user_id等join完成后再把关联不上的数据单独找出来处理。改动就一行WHERE条件任务从三小时降回二十分钟。这个例子说明什么问题Hive SQL的优化很大程度上就是在理解底层执行机制的基础上回到数据本身找问题。你建表时想清楚文件格式和分区策略写SQL时想清楚数据怎么分发、怎么排序排查问题时先看执行计划和数据分布这套方法论走到哪儿都不会过时。Hive这个工具这些年在各种新引擎的冲击下已经不是最时髦的技术了但它的核心思想——用SQL描述分布式数据处理逻辑——依然贯穿在Spark SQL、StarRocks、ClickHouse这些新工具里。把Hive SQL的基础打扎实了你后面学任何SQL-on-XX引擎都会觉得似曾相识上手快得不是一点半点。