1. 项目背景与核心诉求解析做数据开发的同学十有八九都跟CSV打过交道。CSV这玩意儿说简单也简单就是个逗号分隔的纯文本文件说麻烦也麻烦——编码问题、引号转义、换行符不一致每一样都能折腾人半天。我自己在实际项目中接过不少这样的需求业务方丢过来一个几GB的CSV文件让我塞进Hive表里做分析或者反过来让我把Hive里跑完的结果导出成CSV发给下游系统或者交给产品同学做报表。看似两个方向的操作背后的细节其实多得超出大多数人的预期。这篇内容我围绕“Hive导入导出CSV全”这个主题把从建表、加载、导出到踩坑排查的完整链路都梳理一遍。无论你是刚接触Hive的新手还是已经写了几年HQL的老手只要跟CSV打过交道里面总有几条经验能帮你省下半天排查时间。我会把每一步为什么要这么做、参数背后的原理讲清楚而不是丢给你一堆命令让你盲目复制。先说清楚这篇的核心覆盖范围包括Hive内部表与外部表的选择逻辑、CSV文件与Hive表字段的对应关系、LOAD DATA和INSERT OVERWRITE两条主路径、各种分隔符和转义符的处理方式、以及导出时常见的文件合并、压缩格式、编码转换等实操问题。每一条都是我在真实业务环境中验证过的方案。2. 环境准备与基础概念2.1 Hive表类型选择内部表还是外部表很多人一上来就建表导数据结果后面删表的时候把原始CSV也连带删掉了哭都来不及。这里核心差别在于内部表managed table的数据文件完全由Hive管理你DROP表的时候HDFS上的数据文件会一并删除外部表external table则只是让Hive“挂载”到某个HDFS目录上DROP表只删元数据原始文件还留在原处。我自己的习惯是凡是从CSV导入的数据一律建外部表。理由是CSV文件往往是业务方提供的原始资产可能还需要反复清洗、回溯一旦被内部表连带删掉重新找业务方要文件的过程极其痛苦。建外部表只是多了一个关键字的事CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_info ( user_id STRING, user_name STRING, age INT, city STRING, created_at STRING ) COMMENT 用户信息原始表 ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/ods/user_info TBLPROPERTIES (skip.header.line.count 1);2.2 为什么优先用OpenCSVSerde而不是默认的LazySimpleSerDe这是很多教程没讲透的地方。Hive对TEXTFILE格式的默认SerDe是LazySimpleSerDe它对CSV的解析能力其实很弱。默认情况下它只认分隔符不认引号——也就是说如果CSV里某个字段值本身包含了逗号比如北京,朝阳区默认SerDe会把这个字段从逗号处劈开导致列错位、数据错乱。而OpenCSVSerde是专门为CSV设计的SerDe它原生支持引号包围的字段、字段内部的逗号、引号转义等标准CSV规则。换句话说你拿到的CSV只要是符合RFC 4180规范或者Excel导出的常见格式OpenCSVSerde都能正确解析。实测对比过同一个文件用两种SerDe加载的结果默认SerDe解析出来的错误率在包含逗号字段的场景下能达到30%以上而OpenCSVSerde基本能做到零错位。不用纠结直接选OpenCSVSerde。2.3 表字段类型映射CSV没有类型Hive有CSV文件本身是没有类型概念的所有字段本质上都是字符串。到了Hive里你要给它声明Int、Double、Date这些类型这里就埋着导入失败最常见的雷——某一行某个字段是一个空字符串而它对应的Hive字段类型是INTLOAD的时候会直接返回NULL。我的建议是导入阶段先用全STRING类型的“裸表”把数据原样加载进来做完清洗和校验之后再通过INSERT SELECT的方式转成正式的分析表该CAST的CAST该过滤的过滤。这样做的好处是导入流程本身不会因为类型转换报错而中断你始终能在原表里看到原始数据。比如这样建一张裸表CREATE EXTERNAL TABLE IF NOT EXISTS tmp_user_info_raw ( user_id STRING, user_name STRING, age STRING, city STRING, created_at STRING ) ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/tmp/user_info_raw TBLPROPERTIES (skip.header.line.count 1);3. 核心细节解析与实操要点3.1 分隔符、引号符、转义符的搭配规则CSV的解析核心在于三个字符的配合分隔符separator、引号符quote、转义符escape。很多导入异常都源于这三个字符配置不一致。先看标准CSV里最经典的情况user_id,user_name,age,city,created_at 1001,张三,男,28,北京,朝阳区,2024-06-01 12:00:00这里张三,男被双引号包住了它内部有逗号但整体是一个字段。OpenCSVSerde解析时只要看到quoteChar包裹的内容就会把内部的逗号当普通字符处理。而escapeChar的作用是处理字段内部出现的引号本身比如某个字段值是他说你好CSV里会写成他说\你好\解析器靠escapeChar识别出这里的引号是内容而不是语法符号。实操心得如果你导出CSV是给Excel用的分隔符用逗号、引号符用双引号、转义符用反斜杠就是最稳的组合。如果字段里既有逗号又有引号转义符一定要配好否则导出再导入会出现列错乱甚至数据丢失。3.2 跳过表头行TBLPROPERTIES的妙用业务方给的CSV十有八九带着表头Hive默认不会自动跳过它会把表头当成一条数据加载进去。你可以在建表的时候通过TBLPROPERTIES声明跳过第一行TBLPROPERTIES (skip.header.line.count 1);这个属性从Hive 2.3版本开始支持实测在3.x版本上完全稳定。如果你的Hive版本比较老2.1及以下不支持这个属性那就只能靠清洗脚本去掉表头再上传或者加载后再跑一条DELETE——Hive对DELETE的支持很弱所以最好的办法是加载前用sed -i 1d file.csv这类命令处理一下。3.3 多分隔符场景怎么处理有些“伪CSV”其实不是逗号分隔的可能是竖线|、制表符\t、分号;。这种情况下不需要改代码只需在建表时改掉separatorCharWITH SERDEPROPERTIES ( separatorChar |, quoteChar \, escapeChar \\ )给一个实际场景的对照参考数据来源常见分隔符注意事项标准CSV逗号 ,注意字段内逗号需要引号包裹Excel另存为CSV逗号 ,注意中文编码建议UTF-8日志系统导出竖线 |检查字段内是否包含竖线本身MySQL导出制表符 \t注意\t在HQL里的转义写法金融报文分号 ;字段内分号的处理要提前确认注意改分隔符之前先确认你的数据源在生成文件时是怎么转义的。如果源端没做任何转义那你加载的时候无论怎么配都是会错位的。4. 实操过程与核心环节实现4.1 导入路径一LOAD DATA导入本地或HDFS文件这是Hive导入CSV最直接的方式。核心命令是LOAD DATA它分两种来源LOCAL表示从本地磁盘上传不带LOCAL表示文件已经在HDFS上。-- 从本地文件系统导入文件会被复制到Hive表的HDFS目录 LOAD DATA LOCAL INPATH /home/user/data/user_info.csv INTO TABLE tmp_user_info_raw; -- 从HDFS目录导入注意HDFS上的文件会被移动到表目录原路径不再有文件 LOAD DATA INPATH /input/user_info.csv INTO TABLE tmp_user_info_raw;这里有一个新手必踩的坑从HDFS导入时LOAD DATA不是复制而是移动mv。如果你的原始文件在HDFS的/input目录下LOAD完之后原文件就没了。如果不想动原始文件有两个替代方案先把文件cp到另一个目录再LOAD那个目录不走LOAD直接把文件放到外部表指定的LOCATION目录下因为外部表的LOCATION就等同于表的存储目录放进去之后立刻就能查询到第二种方法我用得更多因为省掉了一次HDFS操作而且不依赖HiveServer2的执行任务。4.2 导入路径二INSERT OVERWRITE写SELECT查询结果有时候“导入”不是从文件直接加载而是需要从另一张表加工后写入。这种情况用INSERT OVERWRITE或者INSERT INTOINSERT OVERWRITE TABLE dws_user_info SELECT user_id, user_name, CAST(age AS INT) AS age, city, created_at FROM tmp_user_info_raw WHERE user_id IS NOT NULL AND user_id ! ;INSERT OVERWRITE是全量覆盖——每次执行都会清空目标表目录下的所有文件然后写入新数据。如果只想追加用INSERT INTO。但注意Hive的表文件可能会因此变得很碎如果频繁INSERT INTO建议定期做ALTER TABLE ... CONCATENATE只适用于ORC格式或者重建表做一次合并。4.3 导出路径一INSERT OVERWRITE DIRECTORY导出结果到HDFS把Hive查询结果写出去最基础的方式是INSERT OVERWRITE DIRECTORY /tmp/user_export ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT * FROM dws_user_info;这样导出的文件是纯逗号分隔的文本文件没有表头。很多下游同学拿到后发现列名对不上所以实际项目中我通常会自己拼一行表头让整个输出变成带表头的完整CSVINSERT OVERWRITE DIRECTORY /tmp/user_export ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id,user_name,age,city,created_at UNION ALL SELECT user_id, user_name, CAST(age AS STRING), city, created_at FROM dws_user_info;UNION ALL的方式在Hive里需要注意类型一致性所有SELECT分支的列类型要对得上。第一条分支里所有字段都是STRING第二条分支把数字类型CAST成STRING这样整个结果集的数据类型是统一的。4.4 导出路径二用Hive CLI输出重定向到本地文件如果你查询的结果量不大几千条到几万条可以直接用Hive CLI或Beeline执行SQL把标准输出重定向到本地文件beeline -u jdbc:hive2://your-host:10000/default \ --silenttrue \ --outputformatcsv2 \ -e SELECT user_id, user_name, age, city FROM dws_user_info WHERE dt2024-06-01 \ /home/user/export/user_info_20240601.csv--outputformatcsv2是Beeline自带的一种CSV输出格式它会把查询结果显示成带引号的CSV对字段内含逗号、换行符的场景处理得比csv格式更好。如果追求极致的标准化可以用tsv2输出制表符分隔的结果。注意--silenttrue不加上这个参数的话Beeline会把一大堆Hive的运行日志、箭头符号混进输出文件里文件直接变脏。我自己第一次导出就踩了这个坑输出文件前几行全是INFO级别的日志还得用脚本清理非常麻烦。4.5 导出路径三通过HDFS下载合并文件这个方法是生产环境最常用的。Hive跑完INSERT OVERWRITE DIRECTORY之后HDFS上往往会生成多个part文件数量取决于运行时的Reducer数量。如果需要给业务方一个单文件CSV需要把这些part文件合并。步骤是这样的# 查看生成的文件 hdfs dfs -ls /tmp/user_export # 合并所有part文件到本地 hdfs dfs -getmerge /tmp/user_export /home/user/export/user_info_all.csv-getmerge是HDFS命令里非常好用的一个工具它会把目录下的所有文件按顺序拼接成一个本地文件。默认情况下它不做任何格式处理直接把内容首尾相接。如果每个part文件末尾都有换行符合并后的文件行不会串。但如果你不确定可以在-getmerge后面加一个-nl参数让它在每个文件之间自动追加换行hdfs dfs -getmerge -nl /tmp/user_export /home/user/export/user_info_all.csv这里有个经验点在写导出SQL之前就可以提前控制Reducer数量。如果数据量不大通过SET mapred.reduce.tasks 1;强制只有一个Reducer生成的就是一个part文件连合并这一步都省了。但这个方法只适合几GB以内的数据量数据量大了单Reducer会成为性能瓶颈。5. 复杂场景与性能调优5.1 大文件导入如何避免OOM和解析失败曾经接手过一个业务需求一份CSV文件接近20GB单行的字段长度最长超过2MB用户画像标签串在一起导致。直接用LOAD DATA导入Hive任务跑十来分钟后就开始报Java heap space或者OutOfMemory。排查下来问题出在两点第一OpenCSVSerde在处理超长字段时会把整行数据读入内存进行解析如果单行数据太大Mapper所在的Container很容易OOM。解决思路是先看业务上能不能拆开这些超长字段如果不能就得在Container参数上加内存SET mapreduce.map.memory.mb 4096; SET mapreduce.map.java.opts -Xmx3072m;第二CSV文件如果有坏行比如某一行少了一个逗号导致列数不匹配OpenCSVSerde默认是把缺失的列置为NULL不会报错但如果某一行列数多出几个则有可能触发解析异常。我建议在导入前先用一个快速巡检任务跑一遍文件统计行数和最大列数# 统计文件总行数 wc -l user_info.csv # 统计最大列数用awk按逗号拆分 awk -F, {print NF} user_info.csv | sort -nr | head -1如果某个文件的列数比表字段多这批数据肯定有问题先别急着导入。5.2 分区表怎么导入和导出CSV实际业务中我们很少把CSV直接导入非分区表因为后续查询、清理、生命周期管理都离不开分区。带分区的建表语句CREATE EXTERNAL TABLE IF NOT EXISTS dws_user_info ( user_id STRING, user_name STRING, age INT, city STRING ) PARTITIONED BY (dt STRING) ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/dws/user_info TBLPROPERTIES (skip.header.line.count 1);静态分区导入LOAD DATA LOCAL INPATH /home/user/data/user_info_20240601.csv INTO TABLE dws_user_info PARTITION (dt2024-06-01);动态分区导入从不带分区的临时表刷到分区表SET hive.exec.dynamic.partition.mode nonstrict; INSERT OVERWRITE TABLE dws_user_info PARTITION (dt) SELECT user_id, user_name, age, city, dt FROM tmp_user_info_raw;导出场景反过来查某个分区的数据写成CSVINSERT OVERWRITE DIRECTORY /tmp/user_export_20240601 ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id, user_name, age, city FROM dws_user_info WHERE dt 2024-06-01;5.3 压缩格式的选择导出gzip还是纯文本导出CSV时要不要顺便压缩取决于下游怎么用。如果是给数据仓库或者分析平台做后续处理gzip压缩是很好的选择——体积能缩小到原来的五分之一左右而且大多数系统支持直接读取gzip格式的CSV。实现方式非常简单在导出SQL里加一句存储压缩配置SET mapreduce.output.fileoutputformat.compress true; SET mapreduce.output.fileoutputformat.compress.codec org.apache.hadoop.io.compress.GzipCodec; INSERT OVERWRITE DIRECTORY /tmp/user_export ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT * FROM dws_user_info WHERE dt2024-06-01;如果你要拿这个CSV给非技术的业务人员就别压缩了。他们拿到一个.gz文件大概率不知道怎么打开反而增加沟通成本。给程序做自动化消费的才考虑压缩。5.4 编码问题的统一处理CSV最让人头痛的编码坑在我做过的项目里出现频率非常高。业务方从Excel导出的CSV默认是GBK或者GB2312编码上传到HDFS后Hive按UTF-8解析结果中文全部变成乱码。处理方案有两种第一种在导入前统一转换编码# 本地转码后再上传 iconv -f GBK -t UTF-8 user_info_original.csv user_info_utf8.csv # 转码后的文件上传HDFS hdfs dfs -put user_info_utf8.csv /input/第二种在导出时指定编码给下游。Hive自身没有编码转换函数但可以通过SELECT搭配普通字符串操作变通处理常见的做法是导出后在本地统一转换。我的建议是源头统一UTF-8。和业务方约定所有CSV文件必须提供UTF-8编码版本。这个约定看起来简单但在实操中能省掉一大半的乱码麻烦。如果你控制不了源端那就在导入前例行执行iconv转码步骤把它写进定时调度里。6. 常见问题与排查技巧实录6.1 表头没有被跳过第一条数据丢失现象建表时明明设置了skip.header.line.count1LOAD之后查询发现第一行数据不见了。排查思路这个属性是Hive读取时候的控制参数它生效的前提是文件在表目录下能被识别成数据文件。如果你用LOAD DATA加载的是整个CSV文件会发现表头行被正确跳过但如果你先hdfs dfs -put把文件放进了外部表的LOCATION目录然后建表时又加了skip.header.line.count1那么建表之前文件就已经在那里了之后LOAD新文件时表头跳过逻辑可能只作用于新增的文件而不是历史文件。实操中我倾向于不用这个属性而是在上传之前用脚本统一去掉表头。理由很简单这个属性在不同Hive版本上存在兼容差异而预处理去表头是确定性最高的方案。6.2 字段内含换行符导致行数变多CSV规范允许字段内包含换行符比如一个备注字段里就有一个真正的换行这就导致Hive在按行读取文件时误以为出现了新纪录。这种场景下OpenCSVSerde同样能处理——RFC 4180规范要求含换行的字段必须用引号包住OpenCSVSerde解析时能识别出这是个字段内的换行而不是行终结符。但要注意如果源端生成CSV时没有用引号包住含换行的字段那任何解析器都没法正确判断数据必然被拆成两行。而且Hive读取文件是按行\n切分后再交给SerDe解析的这意味着如果字段内有换行但没有引号包裹必错如果字段内有换行且用引号包裹OpenCSVSerde可以正确还原换行实测过一个文件里包含大量多行地址字段的数据用OpenCSVSerde加载后文本字段内的换行被还原成\n存储字段整体是完整的查询结果也能对上行数。前提条件就是源端CSV生成必须规范。6.3 导入后部分行显示为NULL这个最常见的原因是CSV文件里本来就有空值。比如1001,张三,28,,2024-06-01city字段是空的导入后显示NULL是正常的。但有一种情况很坑CSV里出现\N字符。在Hive中\N是文本文件中表达NULL的默认标记。如果你的CSV数据里恰好有字符串\N比如某个字段值是\N表示空导入后它会被自动识别成NULL而不是字符串\N。这是Hive的默认行为处理方法是建表时通过SERDEPROPERTIES修改WITH SERDEPROPERTIES ( serialization.null.format )设置成空字符串后只有空字段才是NULL\N会被当作普通字符串处理。6.4 导出文件乱码问题导入时乱码的原因在前面说过了。导出时乱码的问题则往往出在文件后续流转环节用INSERT OVERWRITE DIRECTORY导出的文件是UTF-8编码的但用Excel打开的时候Excel会默认猜测编码如果猜成GBK就会显示乱码。有一个不太优雅但很实用的做法导出后带一个BOM头Byte Order Mark。实测带BOM的UTF-8文件用Excel打开时编码识别准确率几乎100%。Hive本身不支持写BOM所以通常在本地用命令处理# 给导出文件加上UTF-8 BOM printf \xEF\xBB\xBF temp_with_bom.csv cat user_info_all.csv temp_with_bom.csv mv temp_with_bom.csv user_info_all.csv不过这也会带来新问题如果这个文件还要再导回HiveBOM头会变成第一个字段值的一部分需要在导入时用TRIM或者正则处理掉。所以我只在面向业务人员的导出场景加上BOM面向系统对接的导出保持纯UTF-8。6.5 常见问题排查速查表现象可能原因解决思路导入后中文乱码源文件GBK编码Hive按UTF-8解析上传前用iconv转UTF-8第一条正常数据丢失表头未被过滤或过滤策略不对预处理去掉表头重新LOAD列数错位、字段串列字段内含逗号但无引号包裹源端修复CSV或替换分隔符字段内容被截断字段内含换行符且被解析成新行确认引号包裹或用OpenCSVSerdeLOAD后原文件消失从HDFS导入时文件被移动用外部表LOCATION方式或先复制导入后大量NULL类型转换失败或CSV空值先用全STRING裸表加载导出文件带日志信息未加--silenttrue重跑并添加静默参数导出文件有多个partReducer数量大于1用getmerge合并或设置reduce1带表头导出时UNION ALL类型不匹配第一个分支与后续分支字段类型不一致全部字段CAST成STRING7. 踩过的坑与实用经验总结我在做Hive数据导入导出项目时踩过不少坑有些坑的排查时间长达一整天。这里分享几个最典型的经验。第一个经验从业务方接收CSV时先做数据质检再导入。一份20GB的CSV文件如果导入后又发现某个字段串列或者编码有问题重新导入的时间成本非常高。我的习惯是先拿一个几千行的采样文件导入到临时表跑几个简单的查询行数、空值率、列数分布确认没有问题后再全量导入。这个步骤只花几分钟但能避免几小时的返工。第二个经验用外部表做中转用内部表做存储。所有CSV原始数据都建外部表LOCATION指向固定的HDFS目录。这样做的好处是后续清洗出错时原始数据还在DROP表也不会影响HDFS上的文件。清洗后的数据再用内部表保存享受Hive的管理和生命周期功能。第三个经验导出CSV给业务方之前一定要跟对方确认打开方式。如果对方说“我用Excel打开”那么编码用UTF-8带BOM分隔符用逗号字段内有逗号的情况必须加双引号如果对方说“我用Python pandas读”那么编码用UTF-8不带BOM加不加表头取决于对方需求但字段内最好不要有多余的转义符否则pandas的read_csv参数配置会很繁琐。上个项目里就遇到过这样的事我导出的CSV字段里为了兼容Excel加了双引号转义结果下游Python脚本用默认参数读取时引号也被读进来了字段值变成了张三带引号对方又花了大半天写清洗逻辑。从那以后每次导出我都会问清楚下游消费方式再决定转义策略。第四个经验CSV导入导出的调度任务一定要有监控和告警。数据量大的时候LOAD DATA任务可能跑10分钟以上如果中途失败Hive不会自动清理半成品数据部分文件可能已经被写入。重跑之前先确保表目录是干净的或者用INSERT OVERWRITE而不是INSERT INTO否则可能旧新数据混在一起。8. 一个端到端的完整实战案例为了把这些分散的点串起来我给出一个从我实际项目中抽象出来的完整流程。需求描述某公司的运营团队每天会从业务系统导出当天的用户行为CSV文件包含用户ID、行为类型、行为时间、行为详情等字段。文件格式为标准CSVUTF-8编码第一行是表头。需要每天自动导入Hive供数据分析团队使用。8.1 第一步创建外部临时表CREATE EXTERNAL TABLE IF NOT EXISTS tmp_user_behavior_raw ( user_id STRING, behavior STRING, event_time STRING, detail STRING ) ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/tmp/user_behavior_raw TBLPROPERTIES (skip.header.line.count 1);8.2 第二步上传CSV文件# 删除历史遗留文件避免新旧混存 hdfs dfs -rm -f /data/tmp/user_behavior_raw/*.csv # 上传当天的CSV文件 hdfs dfs -put /home/user/data/user_behavior_20240601.csv /data/tmp/user_behavior_raw/因为建了外部表文件放到LOCATION目录后Hive立刻就能查看到数据不用再执行LOAD DATA。文件不会被移动原始文件保留一份后续如果要回溯仍然找得到。8.3 第三步数据质检-- 查看总行数 SELECT COUNT(*) FROM tmp_user_behavior_raw; -- 查看每条记录各字段为NULL的情况 SELECT SUM(CASE WHEN user_id IS NULL OR user_id THEN 1 ELSE 0 END) AS user_id_null_cnt, SUM(CASE WHEN behavior IS NULL THEN 1 ELSE 0 END) AS behavior_null_cnt, SUM(CASE WHEN event_time IS NULL THEN 1 ELSE 0 END) AS event_time_null_cnt FROM tmp_user_behavior_raw; -- 抽查几条样本 SELECT * FROM tmp_user_behavior_raw LIMIT 10;如果行数、空值率、样本内容都符合预期继续下一步不满意就直接删目录重新上传。8.4 第四步清洗并写入正式表CREATE TABLE IF NOT EXISTS dws_user_behavior ( user_id STRING, behavior STRING, event_time TIMESTAMP, detail STRING ) PARTITIONED BY (dt STRING) STORED AS ORC; SET hive.exec.dynamic.partition.mode nonstrict; INSERT OVERWRITE TABLE dws_user_behavior PARTITION (dt) SELECT user_id, behavior, CAST(event_time AS TIMESTAMP) AS event_time, detail, 2024-06-01 AS dt FROM tmp_user_behavior_raw WHERE user_id IS NOT NULL AND user_id ! ;正式表用ORC存储查询性能比TEXTFILE高很多而且还支持后续的向量化查询、列裁剪等优化。这一步重点是把STRING类型的event_time转成TIMESTAMP过滤掉空user_id的脏数据。8.5 第五步导出当日分析结果INSERT OVERWRITE DIRECTORY /tmp/behavior_export/20240601 ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id, behavior, event_time, detail FROM dws_user_behavior WHERE dt 2024-06-01;然后合并下载hdfs dfs -getmerge -nl /tmp/behavior_export/20240601 /home/user/export/behavior_20240601.csv这样给到需求方的是一个干净、单文件、UTF-8无BOM的CSV直接可以分发给业务团队使用。9. 写在最后的补充我实际做这些导入导出工作时越来越觉得CSV这种格式虽然简单但正因为简单反而容易在细节上翻车。SerDe选型、编码统一、表头处理、文件合并每个环节单独拿出来都不难合在一起却需要一套完整的流程去保证稳定可靠。建议你在自己的项目里先建立一套标准化的CSV导入导出流程模板包括建表语句模板、质检SQL模板、导出脚本模板。这样新需求来了只需要替换表名和字段名就能快速复用整套流程不用每次从零开始摸索。这套流程沉淀得越完善你在处理数据对接上的时间成本就越低。如果后续有需要还可以在这个基础上扩展成通用的数据交换模块支持其他分隔符格式、压缩格式、甚至自动识别表头字段名的功能。先把基础的CSV链路打磨扎实后面所有扩展都会顺很多。
1. 项目背景与核心诉求解析做数据开发的同学十有八九都跟CSV打过交道。CSV这玩意儿说简单也简单就是个逗号分隔的纯文本文件说麻烦也麻烦——编码问题、引号转义、换行符不一致每一样都能折腾人半天。我自己在实际项目中接过不少这样的需求业务方丢过来一个几GB的CSV文件让我塞进Hive表里做分析或者反过来让我把Hive里跑完的结果导出成CSV发给下游系统或者交给产品同学做报表。看似两个方向的操作背后的细节其实多得超出大多数人的预期。这篇内容我围绕“Hive导入导出CSV全”这个主题把从建表、加载、导出到踩坑排查的完整链路都梳理一遍。无论你是刚接触Hive的新手还是已经写了几年HQL的老手只要跟CSV打过交道里面总有几条经验能帮你省下半天排查时间。我会把每一步为什么要这么做、参数背后的原理讲清楚而不是丢给你一堆命令让你盲目复制。先说清楚这篇的核心覆盖范围包括Hive内部表与外部表的选择逻辑、CSV文件与Hive表字段的对应关系、LOAD DATA和INSERT OVERWRITE两条主路径、各种分隔符和转义符的处理方式、以及导出时常见的文件合并、压缩格式、编码转换等实操问题。每一条都是我在真实业务环境中验证过的方案。2. 环境准备与基础概念2.1 Hive表类型选择内部表还是外部表很多人一上来就建表导数据结果后面删表的时候把原始CSV也连带删掉了哭都来不及。这里核心差别在于内部表managed table的数据文件完全由Hive管理你DROP表的时候HDFS上的数据文件会一并删除外部表external table则只是让Hive“挂载”到某个HDFS目录上DROP表只删元数据原始文件还留在原处。我自己的习惯是凡是从CSV导入的数据一律建外部表。理由是CSV文件往往是业务方提供的原始资产可能还需要反复清洗、回溯一旦被内部表连带删掉重新找业务方要文件的过程极其痛苦。建外部表只是多了一个关键字的事CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_info ( user_id STRING, user_name STRING, age INT, city STRING, created_at STRING ) COMMENT 用户信息原始表 ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/ods/user_info TBLPROPERTIES (skip.header.line.count 1);2.2 为什么优先用OpenCSVSerde而不是默认的LazySimpleSerDe这是很多教程没讲透的地方。Hive对TEXTFILE格式的默认SerDe是LazySimpleSerDe它对CSV的解析能力其实很弱。默认情况下它只认分隔符不认引号——也就是说如果CSV里某个字段值本身包含了逗号比如北京,朝阳区默认SerDe会把这个字段从逗号处劈开导致列错位、数据错乱。而OpenCSVSerde是专门为CSV设计的SerDe它原生支持引号包围的字段、字段内部的逗号、引号转义等标准CSV规则。换句话说你拿到的CSV只要是符合RFC 4180规范或者Excel导出的常见格式OpenCSVSerde都能正确解析。实测对比过同一个文件用两种SerDe加载的结果默认SerDe解析出来的错误率在包含逗号字段的场景下能达到30%以上而OpenCSVSerde基本能做到零错位。不用纠结直接选OpenCSVSerde。2.3 表字段类型映射CSV没有类型Hive有CSV文件本身是没有类型概念的所有字段本质上都是字符串。到了Hive里你要给它声明Int、Double、Date这些类型这里就埋着导入失败最常见的雷——某一行某个字段是一个空字符串而它对应的Hive字段类型是INTLOAD的时候会直接返回NULL。我的建议是导入阶段先用全STRING类型的“裸表”把数据原样加载进来做完清洗和校验之后再通过INSERT SELECT的方式转成正式的分析表该CAST的CAST该过滤的过滤。这样做的好处是导入流程本身不会因为类型转换报错而中断你始终能在原表里看到原始数据。比如这样建一张裸表CREATE EXTERNAL TABLE IF NOT EXISTS tmp_user_info_raw ( user_id STRING, user_name STRING, age STRING, city STRING, created_at STRING ) ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/tmp/user_info_raw TBLPROPERTIES (skip.header.line.count 1);3. 核心细节解析与实操要点3.1 分隔符、引号符、转义符的搭配规则CSV的解析核心在于三个字符的配合分隔符separator、引号符quote、转义符escape。很多导入异常都源于这三个字符配置不一致。先看标准CSV里最经典的情况user_id,user_name,age,city,created_at 1001,张三,男,28,北京,朝阳区,2024-06-01 12:00:00这里张三,男被双引号包住了它内部有逗号但整体是一个字段。OpenCSVSerde解析时只要看到quoteChar包裹的内容就会把内部的逗号当普通字符处理。而escapeChar的作用是处理字段内部出现的引号本身比如某个字段值是他说你好CSV里会写成他说\你好\解析器靠escapeChar识别出这里的引号是内容而不是语法符号。实操心得如果你导出CSV是给Excel用的分隔符用逗号、引号符用双引号、转义符用反斜杠就是最稳的组合。如果字段里既有逗号又有引号转义符一定要配好否则导出再导入会出现列错乱甚至数据丢失。3.2 跳过表头行TBLPROPERTIES的妙用业务方给的CSV十有八九带着表头Hive默认不会自动跳过它会把表头当成一条数据加载进去。你可以在建表的时候通过TBLPROPERTIES声明跳过第一行TBLPROPERTIES (skip.header.line.count 1);这个属性从Hive 2.3版本开始支持实测在3.x版本上完全稳定。如果你的Hive版本比较老2.1及以下不支持这个属性那就只能靠清洗脚本去掉表头再上传或者加载后再跑一条DELETE——Hive对DELETE的支持很弱所以最好的办法是加载前用sed -i 1d file.csv这类命令处理一下。3.3 多分隔符场景怎么处理有些“伪CSV”其实不是逗号分隔的可能是竖线|、制表符\t、分号;。这种情况下不需要改代码只需在建表时改掉separatorCharWITH SERDEPROPERTIES ( separatorChar |, quoteChar \, escapeChar \\ )给一个实际场景的对照参考数据来源常见分隔符注意事项标准CSV逗号 ,注意字段内逗号需要引号包裹Excel另存为CSV逗号 ,注意中文编码建议UTF-8日志系统导出竖线 |检查字段内是否包含竖线本身MySQL导出制表符 \t注意\t在HQL里的转义写法金融报文分号 ;字段内分号的处理要提前确认注意改分隔符之前先确认你的数据源在生成文件时是怎么转义的。如果源端没做任何转义那你加载的时候无论怎么配都是会错位的。4. 实操过程与核心环节实现4.1 导入路径一LOAD DATA导入本地或HDFS文件这是Hive导入CSV最直接的方式。核心命令是LOAD DATA它分两种来源LOCAL表示从本地磁盘上传不带LOCAL表示文件已经在HDFS上。-- 从本地文件系统导入文件会被复制到Hive表的HDFS目录 LOAD DATA LOCAL INPATH /home/user/data/user_info.csv INTO TABLE tmp_user_info_raw; -- 从HDFS目录导入注意HDFS上的文件会被移动到表目录原路径不再有文件 LOAD DATA INPATH /input/user_info.csv INTO TABLE tmp_user_info_raw;这里有一个新手必踩的坑从HDFS导入时LOAD DATA不是复制而是移动mv。如果你的原始文件在HDFS的/input目录下LOAD完之后原文件就没了。如果不想动原始文件有两个替代方案先把文件cp到另一个目录再LOAD那个目录不走LOAD直接把文件放到外部表指定的LOCATION目录下因为外部表的LOCATION就等同于表的存储目录放进去之后立刻就能查询到第二种方法我用得更多因为省掉了一次HDFS操作而且不依赖HiveServer2的执行任务。4.2 导入路径二INSERT OVERWRITE写SELECT查询结果有时候“导入”不是从文件直接加载而是需要从另一张表加工后写入。这种情况用INSERT OVERWRITE或者INSERT INTOINSERT OVERWRITE TABLE dws_user_info SELECT user_id, user_name, CAST(age AS INT) AS age, city, created_at FROM tmp_user_info_raw WHERE user_id IS NOT NULL AND user_id ! ;INSERT OVERWRITE是全量覆盖——每次执行都会清空目标表目录下的所有文件然后写入新数据。如果只想追加用INSERT INTO。但注意Hive的表文件可能会因此变得很碎如果频繁INSERT INTO建议定期做ALTER TABLE ... CONCATENATE只适用于ORC格式或者重建表做一次合并。4.3 导出路径一INSERT OVERWRITE DIRECTORY导出结果到HDFS把Hive查询结果写出去最基础的方式是INSERT OVERWRITE DIRECTORY /tmp/user_export ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT * FROM dws_user_info;这样导出的文件是纯逗号分隔的文本文件没有表头。很多下游同学拿到后发现列名对不上所以实际项目中我通常会自己拼一行表头让整个输出变成带表头的完整CSVINSERT OVERWRITE DIRECTORY /tmp/user_export ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id,user_name,age,city,created_at UNION ALL SELECT user_id, user_name, CAST(age AS STRING), city, created_at FROM dws_user_info;UNION ALL的方式在Hive里需要注意类型一致性所有SELECT分支的列类型要对得上。第一条分支里所有字段都是STRING第二条分支把数字类型CAST成STRING这样整个结果集的数据类型是统一的。4.4 导出路径二用Hive CLI输出重定向到本地文件如果你查询的结果量不大几千条到几万条可以直接用Hive CLI或Beeline执行SQL把标准输出重定向到本地文件beeline -u jdbc:hive2://your-host:10000/default \ --silenttrue \ --outputformatcsv2 \ -e SELECT user_id, user_name, age, city FROM dws_user_info WHERE dt2024-06-01 \ /home/user/export/user_info_20240601.csv--outputformatcsv2是Beeline自带的一种CSV输出格式它会把查询结果显示成带引号的CSV对字段内含逗号、换行符的场景处理得比csv格式更好。如果追求极致的标准化可以用tsv2输出制表符分隔的结果。注意--silenttrue不加上这个参数的话Beeline会把一大堆Hive的运行日志、箭头符号混进输出文件里文件直接变脏。我自己第一次导出就踩了这个坑输出文件前几行全是INFO级别的日志还得用脚本清理非常麻烦。4.5 导出路径三通过HDFS下载合并文件这个方法是生产环境最常用的。Hive跑完INSERT OVERWRITE DIRECTORY之后HDFS上往往会生成多个part文件数量取决于运行时的Reducer数量。如果需要给业务方一个单文件CSV需要把这些part文件合并。步骤是这样的# 查看生成的文件 hdfs dfs -ls /tmp/user_export # 合并所有part文件到本地 hdfs dfs -getmerge /tmp/user_export /home/user/export/user_info_all.csv-getmerge是HDFS命令里非常好用的一个工具它会把目录下的所有文件按顺序拼接成一个本地文件。默认情况下它不做任何格式处理直接把内容首尾相接。如果每个part文件末尾都有换行符合并后的文件行不会串。但如果你不确定可以在-getmerge后面加一个-nl参数让它在每个文件之间自动追加换行hdfs dfs -getmerge -nl /tmp/user_export /home/user/export/user_info_all.csv这里有个经验点在写导出SQL之前就可以提前控制Reducer数量。如果数据量不大通过SET mapred.reduce.tasks 1;强制只有一个Reducer生成的就是一个part文件连合并这一步都省了。但这个方法只适合几GB以内的数据量数据量大了单Reducer会成为性能瓶颈。5. 复杂场景与性能调优5.1 大文件导入如何避免OOM和解析失败曾经接手过一个业务需求一份CSV文件接近20GB单行的字段长度最长超过2MB用户画像标签串在一起导致。直接用LOAD DATA导入Hive任务跑十来分钟后就开始报Java heap space或者OutOfMemory。排查下来问题出在两点第一OpenCSVSerde在处理超长字段时会把整行数据读入内存进行解析如果单行数据太大Mapper所在的Container很容易OOM。解决思路是先看业务上能不能拆开这些超长字段如果不能就得在Container参数上加内存SET mapreduce.map.memory.mb 4096; SET mapreduce.map.java.opts -Xmx3072m;第二CSV文件如果有坏行比如某一行少了一个逗号导致列数不匹配OpenCSVSerde默认是把缺失的列置为NULL不会报错但如果某一行列数多出几个则有可能触发解析异常。我建议在导入前先用一个快速巡检任务跑一遍文件统计行数和最大列数# 统计文件总行数 wc -l user_info.csv # 统计最大列数用awk按逗号拆分 awk -F, {print NF} user_info.csv | sort -nr | head -1如果某个文件的列数比表字段多这批数据肯定有问题先别急着导入。5.2 分区表怎么导入和导出CSV实际业务中我们很少把CSV直接导入非分区表因为后续查询、清理、生命周期管理都离不开分区。带分区的建表语句CREATE EXTERNAL TABLE IF NOT EXISTS dws_user_info ( user_id STRING, user_name STRING, age INT, city STRING ) PARTITIONED BY (dt STRING) ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/dws/user_info TBLPROPERTIES (skip.header.line.count 1);静态分区导入LOAD DATA LOCAL INPATH /home/user/data/user_info_20240601.csv INTO TABLE dws_user_info PARTITION (dt2024-06-01);动态分区导入从不带分区的临时表刷到分区表SET hive.exec.dynamic.partition.mode nonstrict; INSERT OVERWRITE TABLE dws_user_info PARTITION (dt) SELECT user_id, user_name, age, city, dt FROM tmp_user_info_raw;导出场景反过来查某个分区的数据写成CSVINSERT OVERWRITE DIRECTORY /tmp/user_export_20240601 ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id, user_name, age, city FROM dws_user_info WHERE dt 2024-06-01;5.3 压缩格式的选择导出gzip还是纯文本导出CSV时要不要顺便压缩取决于下游怎么用。如果是给数据仓库或者分析平台做后续处理gzip压缩是很好的选择——体积能缩小到原来的五分之一左右而且大多数系统支持直接读取gzip格式的CSV。实现方式非常简单在导出SQL里加一句存储压缩配置SET mapreduce.output.fileoutputformat.compress true; SET mapreduce.output.fileoutputformat.compress.codec org.apache.hadoop.io.compress.GzipCodec; INSERT OVERWRITE DIRECTORY /tmp/user_export ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT * FROM dws_user_info WHERE dt2024-06-01;如果你要拿这个CSV给非技术的业务人员就别压缩了。他们拿到一个.gz文件大概率不知道怎么打开反而增加沟通成本。给程序做自动化消费的才考虑压缩。5.4 编码问题的统一处理CSV最让人头痛的编码坑在我做过的项目里出现频率非常高。业务方从Excel导出的CSV默认是GBK或者GB2312编码上传到HDFS后Hive按UTF-8解析结果中文全部变成乱码。处理方案有两种第一种在导入前统一转换编码# 本地转码后再上传 iconv -f GBK -t UTF-8 user_info_original.csv user_info_utf8.csv # 转码后的文件上传HDFS hdfs dfs -put user_info_utf8.csv /input/第二种在导出时指定编码给下游。Hive自身没有编码转换函数但可以通过SELECT搭配普通字符串操作变通处理常见的做法是导出后在本地统一转换。我的建议是源头统一UTF-8。和业务方约定所有CSV文件必须提供UTF-8编码版本。这个约定看起来简单但在实操中能省掉一大半的乱码麻烦。如果你控制不了源端那就在导入前例行执行iconv转码步骤把它写进定时调度里。6. 常见问题与排查技巧实录6.1 表头没有被跳过第一条数据丢失现象建表时明明设置了skip.header.line.count1LOAD之后查询发现第一行数据不见了。排查思路这个属性是Hive读取时候的控制参数它生效的前提是文件在表目录下能被识别成数据文件。如果你用LOAD DATA加载的是整个CSV文件会发现表头行被正确跳过但如果你先hdfs dfs -put把文件放进了外部表的LOCATION目录然后建表时又加了skip.header.line.count1那么建表之前文件就已经在那里了之后LOAD新文件时表头跳过逻辑可能只作用于新增的文件而不是历史文件。实操中我倾向于不用这个属性而是在上传之前用脚本统一去掉表头。理由很简单这个属性在不同Hive版本上存在兼容差异而预处理去表头是确定性最高的方案。6.2 字段内含换行符导致行数变多CSV规范允许字段内包含换行符比如一个备注字段里就有一个真正的换行这就导致Hive在按行读取文件时误以为出现了新纪录。这种场景下OpenCSVSerde同样能处理——RFC 4180规范要求含换行的字段必须用引号包住OpenCSVSerde解析时能识别出这是个字段内的换行而不是行终结符。但要注意如果源端生成CSV时没有用引号包住含换行的字段那任何解析器都没法正确判断数据必然被拆成两行。而且Hive读取文件是按行\n切分后再交给SerDe解析的这意味着如果字段内有换行但没有引号包裹必错如果字段内有换行且用引号包裹OpenCSVSerde可以正确还原换行实测过一个文件里包含大量多行地址字段的数据用OpenCSVSerde加载后文本字段内的换行被还原成\n存储字段整体是完整的查询结果也能对上行数。前提条件就是源端CSV生成必须规范。6.3 导入后部分行显示为NULL这个最常见的原因是CSV文件里本来就有空值。比如1001,张三,28,,2024-06-01city字段是空的导入后显示NULL是正常的。但有一种情况很坑CSV里出现\N字符。在Hive中\N是文本文件中表达NULL的默认标记。如果你的CSV数据里恰好有字符串\N比如某个字段值是\N表示空导入后它会被自动识别成NULL而不是字符串\N。这是Hive的默认行为处理方法是建表时通过SERDEPROPERTIES修改WITH SERDEPROPERTIES ( serialization.null.format )设置成空字符串后只有空字段才是NULL\N会被当作普通字符串处理。6.4 导出文件乱码问题导入时乱码的原因在前面说过了。导出时乱码的问题则往往出在文件后续流转环节用INSERT OVERWRITE DIRECTORY导出的文件是UTF-8编码的但用Excel打开的时候Excel会默认猜测编码如果猜成GBK就会显示乱码。有一个不太优雅但很实用的做法导出后带一个BOM头Byte Order Mark。实测带BOM的UTF-8文件用Excel打开时编码识别准确率几乎100%。Hive本身不支持写BOM所以通常在本地用命令处理# 给导出文件加上UTF-8 BOM printf \xEF\xBB\xBF temp_with_bom.csv cat user_info_all.csv temp_with_bom.csv mv temp_with_bom.csv user_info_all.csv不过这也会带来新问题如果这个文件还要再导回HiveBOM头会变成第一个字段值的一部分需要在导入时用TRIM或者正则处理掉。所以我只在面向业务人员的导出场景加上BOM面向系统对接的导出保持纯UTF-8。6.5 常见问题排查速查表现象可能原因解决思路导入后中文乱码源文件GBK编码Hive按UTF-8解析上传前用iconv转UTF-8第一条正常数据丢失表头未被过滤或过滤策略不对预处理去掉表头重新LOAD列数错位、字段串列字段内含逗号但无引号包裹源端修复CSV或替换分隔符字段内容被截断字段内含换行符且被解析成新行确认引号包裹或用OpenCSVSerdeLOAD后原文件消失从HDFS导入时文件被移动用外部表LOCATION方式或先复制导入后大量NULL类型转换失败或CSV空值先用全STRING裸表加载导出文件带日志信息未加--silenttrue重跑并添加静默参数导出文件有多个partReducer数量大于1用getmerge合并或设置reduce1带表头导出时UNION ALL类型不匹配第一个分支与后续分支字段类型不一致全部字段CAST成STRING7. 踩过的坑与实用经验总结我在做Hive数据导入导出项目时踩过不少坑有些坑的排查时间长达一整天。这里分享几个最典型的经验。第一个经验从业务方接收CSV时先做数据质检再导入。一份20GB的CSV文件如果导入后又发现某个字段串列或者编码有问题重新导入的时间成本非常高。我的习惯是先拿一个几千行的采样文件导入到临时表跑几个简单的查询行数、空值率、列数分布确认没有问题后再全量导入。这个步骤只花几分钟但能避免几小时的返工。第二个经验用外部表做中转用内部表做存储。所有CSV原始数据都建外部表LOCATION指向固定的HDFS目录。这样做的好处是后续清洗出错时原始数据还在DROP表也不会影响HDFS上的文件。清洗后的数据再用内部表保存享受Hive的管理和生命周期功能。第三个经验导出CSV给业务方之前一定要跟对方确认打开方式。如果对方说“我用Excel打开”那么编码用UTF-8带BOM分隔符用逗号字段内有逗号的情况必须加双引号如果对方说“我用Python pandas读”那么编码用UTF-8不带BOM加不加表头取决于对方需求但字段内最好不要有多余的转义符否则pandas的read_csv参数配置会很繁琐。上个项目里就遇到过这样的事我导出的CSV字段里为了兼容Excel加了双引号转义结果下游Python脚本用默认参数读取时引号也被读进来了字段值变成了张三带引号对方又花了大半天写清洗逻辑。从那以后每次导出我都会问清楚下游消费方式再决定转义策略。第四个经验CSV导入导出的调度任务一定要有监控和告警。数据量大的时候LOAD DATA任务可能跑10分钟以上如果中途失败Hive不会自动清理半成品数据部分文件可能已经被写入。重跑之前先确保表目录是干净的或者用INSERT OVERWRITE而不是INSERT INTO否则可能旧新数据混在一起。8. 一个端到端的完整实战案例为了把这些分散的点串起来我给出一个从我实际项目中抽象出来的完整流程。需求描述某公司的运营团队每天会从业务系统导出当天的用户行为CSV文件包含用户ID、行为类型、行为时间、行为详情等字段。文件格式为标准CSVUTF-8编码第一行是表头。需要每天自动导入Hive供数据分析团队使用。8.1 第一步创建外部临时表CREATE EXTERNAL TABLE IF NOT EXISTS tmp_user_behavior_raw ( user_id STRING, behavior STRING, event_time STRING, detail STRING ) ROW FORMAT SERDE org.apache.hadoop.hive.serde2.OpenCSVSerde WITH SERDEPROPERTIES ( separatorChar ,, quoteChar \, escapeChar \\ ) STORED AS TEXTFILE LOCATION /data/tmp/user_behavior_raw TBLPROPERTIES (skip.header.line.count 1);8.2 第二步上传CSV文件# 删除历史遗留文件避免新旧混存 hdfs dfs -rm -f /data/tmp/user_behavior_raw/*.csv # 上传当天的CSV文件 hdfs dfs -put /home/user/data/user_behavior_20240601.csv /data/tmp/user_behavior_raw/因为建了外部表文件放到LOCATION目录后Hive立刻就能查看到数据不用再执行LOAD DATA。文件不会被移动原始文件保留一份后续如果要回溯仍然找得到。8.3 第三步数据质检-- 查看总行数 SELECT COUNT(*) FROM tmp_user_behavior_raw; -- 查看每条记录各字段为NULL的情况 SELECT SUM(CASE WHEN user_id IS NULL OR user_id THEN 1 ELSE 0 END) AS user_id_null_cnt, SUM(CASE WHEN behavior IS NULL THEN 1 ELSE 0 END) AS behavior_null_cnt, SUM(CASE WHEN event_time IS NULL THEN 1 ELSE 0 END) AS event_time_null_cnt FROM tmp_user_behavior_raw; -- 抽查几条样本 SELECT * FROM tmp_user_behavior_raw LIMIT 10;如果行数、空值率、样本内容都符合预期继续下一步不满意就直接删目录重新上传。8.4 第四步清洗并写入正式表CREATE TABLE IF NOT EXISTS dws_user_behavior ( user_id STRING, behavior STRING, event_time TIMESTAMP, detail STRING ) PARTITIONED BY (dt STRING) STORED AS ORC; SET hive.exec.dynamic.partition.mode nonstrict; INSERT OVERWRITE TABLE dws_user_behavior PARTITION (dt) SELECT user_id, behavior, CAST(event_time AS TIMESTAMP) AS event_time, detail, 2024-06-01 AS dt FROM tmp_user_behavior_raw WHERE user_id IS NOT NULL AND user_id ! ;正式表用ORC存储查询性能比TEXTFILE高很多而且还支持后续的向量化查询、列裁剪等优化。这一步重点是把STRING类型的event_time转成TIMESTAMP过滤掉空user_id的脏数据。8.5 第五步导出当日分析结果INSERT OVERWRITE DIRECTORY /tmp/behavior_export/20240601 ROW FORMAT DELIMITED FIELDS TERMINATED BY , SELECT user_id, behavior, event_time, detail FROM dws_user_behavior WHERE dt 2024-06-01;然后合并下载hdfs dfs -getmerge -nl /tmp/behavior_export/20240601 /home/user/export/behavior_20240601.csv这样给到需求方的是一个干净、单文件、UTF-8无BOM的CSV直接可以分发给业务团队使用。9. 写在最后的补充我实际做这些导入导出工作时越来越觉得CSV这种格式虽然简单但正因为简单反而容易在细节上翻车。SerDe选型、编码统一、表头处理、文件合并每个环节单独拿出来都不难合在一起却需要一套完整的流程去保证稳定可靠。建议你在自己的项目里先建立一套标准化的CSV导入导出流程模板包括建表语句模板、质检SQL模板、导出脚本模板。这样新需求来了只需要替换表名和字段名就能快速复用整套流程不用每次从零开始摸索。这套流程沉淀得越完善你在处理数据对接上的时间成本就越低。如果后续有需要还可以在这个基础上扩展成通用的数据交换模块支持其他分隔符格式、压缩格式、甚至自动识别表头字段名的功能。先把基础的CSV链路打磨扎实后面所有扩展都会顺很多。
