)
1. 項目背景與核心訴求解析做數(shù)據(jù)開發(fā)的同學十有八九都跟CSV打過交道。CSV這玩意兒說簡單也簡單就是個逗號分隔的純文本文件說麻煩也麻煩——編碼問題、引號轉義、換行符不一致每一樣都能折騰人半天。我自己在實際項目中接過不少這樣的需求業(yè)務方丟過來一個幾GB的CSV文件讓我塞進Hive表里做分析或者反過來讓我把Hive里跑完的結果導出成CSV發(fā)給下游系統(tǒng)或者交給產(chǎn)品同學做報表?？此苾蓚€方向的操作背后的細節(jié)其實多得超出大多數(shù)人的預期。這篇內(nèi)容我圍繞“Hive導入導出CSV全”這個主題把從建表、加載、導出到踩坑排查的完整鏈路都梳理一遍。無論你是剛接觸Hive的新手還是已經(jīng)寫了幾年HQL的老手只要跟CSV打過交道里面總有幾條經(jīng)驗能幫你省下半天排查時間。我會把每一步為什么要這么做、參數(shù)背后的原理講清楚而不是丟給你一堆命令讓你盲目復制。先說清楚這篇的核心覆蓋范圍包括Hive內(nèi)部表與外部表的選擇邏輯、CSV文件與Hive表字段的對應關系、LOAD DATA和INSERT OVERWRITE兩條主路徑、各種分隔符和轉義符的處理方式、以及導出時常見的文件合并、壓縮格式、編碼轉換等實操問題。每一條都是我在真實業(yè)務環(huán)境中驗證過的方案。2. 環(huán)境準備與基礎概念2.1 Hive表類型選擇內(nèi)部表還是外部表很多人一上來就建表導數(shù)據(jù)結果后面刪表的時候把原始CSV也連帶刪掉了哭都來不及。這里核心差別在于內(nèi)部表managed table的數(shù)據(jù)文件完全由Hive管理你DROP表的時候HDFS上的數(shù)據(jù)文件會一并刪除外部表external table則只是讓Hive“掛載”到某個HDFS目錄上DROP表只刪元數(shù)據(jù)原始文件還留在原處。我自己的習慣是凡是從CSV導入的數(shù)據(jù)一律建外部表。理由是CSV文件往往是業(yè)務方提供的原始資產(chǎn)可能還需要反復清洗、回溯一旦被內(nèi)部表連帶刪掉重新找業(yè)務方要文件的過程極其痛苦。建外部表只是多了一個關鍵字的事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 為什么優(yōu)先用OpenCSVSerde而不是默認的LazySimpleSerDe這是很多教程沒講透的地方。Hive對TEXTFILE格式的默認SerDe是LazySimpleSerDe它對CSV的解析能力其實很弱。默認情況下它只認分隔符不認引號——也就是說如果CSV里某個字段值本身包含了逗號比如北京,朝陽區(qū)默認SerDe會把這個字段從逗號處劈開導致列錯位、數(shù)據(jù)錯亂。而OpenCSVSerde是專門為CSV設計的SerDe它原生支持引號包圍的字段、字段內(nèi)部的逗號、引號轉義等標準CSV規(guī)則。換句話說你拿到的CSV只要是符合RFC 4180規(guī)范或者Excel導出的常見格式OpenCSVSerde都能正確解析。實測對比過同一個文件用兩種SerDe加載的結果默認SerDe解析出來的錯誤率在包含逗號字段的場景下能達到30%以上而OpenCSVSerde基本能做到零錯位。不用糾結直接選OpenCSVSerde。2.3 表字段類型映射CSV沒有類型Hive有CSV文件本身是沒有類型概念的所有字段本質(zhì)上都是字符串。到了Hive里你要給它聲明Int、Double、Date這些類型這里就埋著導入失敗最常見的雷——某一行某個字段是一個空字符串而它對應的Hive字段類型是INTLOAD的時候會直接返回NULL。我的建議是導入階段先用全STRING類型的“裸表”把數(shù)據(jù)原樣加載進來做完清洗和校驗之后再通過INSERT SELECT的方式轉成正式的分析表該CAST的CAST該過濾的過濾。這樣做的好處是導入流程本身不會因為類型轉換報錯而中斷你始終能在原表里看到原始數(shù)據(jù)。比如這樣建一張裸表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. 核心細節(jié)解析與實操要點3.1 分隔符、引號符、轉義符的搭配規(guī)則CSV的解析核心在于三個字符的配合分隔符separator、引號符quote、轉義符escape。很多導入異常都源于這三個字符配置不一致。先看標準CSV里最經(jīng)典的情況user_id,user_name,age,city,created_at 1001,張三,男,28,北京,朝陽區(qū),2024-06-01 12:00:00這里張三,男被雙引號包住了它內(nèi)部有逗號但整體是一個字段。OpenCSVSerde解析時只要看到quoteChar包裹的內(nèi)容就會把內(nèi)部的逗號當普通字符處理。而escapeChar的作用是處理字段內(nèi)部出現(xiàn)的引號本身比如某個字段值是他說你好CSV里會寫成他說\你好\解析器靠escapeChar識別出這里的引號是內(nèi)容而不是語法符號。實操心得如果你導出CSV是給Excel用的分隔符用逗號、引號符用雙引號、轉義符用反斜杠就是最穩(wěn)的組合。如果字段里既有逗號又有引號轉義符一定要配好否則導出再導入會出現(xiàn)列錯亂甚至數(shù)據(jù)丟失。3.2 跳過表頭行TBLPROPERTIES的妙用業(yè)務方給的CSV十有八九帶著表頭Hive默認不會自動跳過它會把表頭當成一條數(shù)據(jù)加載進去。你可以在建表的時候通過TBLPROPERTIES聲明跳過第一行TBLPROPERTIES (skip.header.line.count 1);這個屬性從Hive 2.3版本開始支持實測在3.x版本上完全穩(wěn)定。如果你的Hive版本比較老2.1及以下不支持這個屬性那就只能靠清洗腳本去掉表頭再上傳或者加載后再跑一條DELETE——Hive對DELETE的支持很弱所以最好的辦法是加載前用sed -i 1d file.csv這類命令處理一下。3.3 多分隔符場景怎么處理有些“偽CSV”其實不是逗號分隔的可能是豎線|、制表符\t、分號;。這種情況下不需要改代碼只需在建表時改掉separatorCharWITH SERDEPROPERTIES ( separatorChar |, quoteChar \, escapeChar \\ )給一個實際場景的對照參考數(shù)據(jù)來源常見分隔符注意事項標準CSV逗號 ,注意字段內(nèi)逗號需要引號包裹Excel另存為CSV逗號 ,注意中文編碼建議UTF-8日志系統(tǒng)導出豎線 |檢查字段內(nèi)是否包含豎線本身MySQL導出制表符 \t注意\t在HQL里的轉義寫法金融報文分號 ;字段內(nèi)分號的處理要提前確認注意改分隔符之前先確認你的數(shù)據(jù)源在生成文件時是怎么轉義的。如果源端沒做任何轉義那你加載的時候無論怎么配都是會錯位的。4. 實操過程與核心環(huán)節(jié)實現(xiàn)4.1 導入路徑一LOAD DATA導入本地或HDFS文件這是Hive導入CSV最直接的方式。核心命令是LOAD DATA它分兩種來源LOCAL表示從本地磁盤上傳不帶LOCAL表示文件已經(jīng)在HDFS上。-- 從本地文件系統(tǒng)導入文件會被復制到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的執(zhí)行任務。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是全量覆蓋——每次執(zhí)行都會清空目標表目錄下的所有文件然后寫入新數(shù)據(jù)。如果只想追加用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;這樣導出的文件是純逗號分隔的文本文件沒有表頭。很多下游同學拿到后發(fā)現(xiàn)列名對不上所以實際項目中我通常會自己拼一行表頭讓整個輸出變成帶表頭的完整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第二條分支把數(shù)字類型CAST成STRING這樣整個結果集的數(shù)據(jù)類型是統(tǒng)一的。4.4 導出路徑二用Hive CLI輸出重定向到本地文件如果你查詢的結果量不大幾千條到幾萬條可以直接用Hive CLI或Beeline執(zhí)行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對字段內(nèi)含逗號、換行符的場景處理得比csv格式更好。如果追求極致的標準化可以用tsv2輸出制表符分隔的結果。注意--silenttrue不加上這個參數(shù)的話Beeline會把一大堆Hive的運行日志、箭頭符號混進輸出文件里文件直接變臟。我自己第一次導出就踩了這個坑輸出文件前幾行全是INFO級別的日志還得用腳本清理非常麻煩。4.5 導出路徑三通過HDFS下載合并文件這個方法是生產(chǎn)環(huán)境最常用的。Hive跑完INSERT OVERWRITE DIRECTORY之后HDFS上往往會生成多個part文件數(shù)量取決于運行時的Reducer數(shù)量。如果需要給業(yè)務方一個單文件CSV需要把這些part文件合并。步驟是這樣的# 查看生成的文件 hdfs dfs -ls /tmp/user_export # 合并所有part文件到本地 hdfs dfs -getmerge /tmp/user_export /home/user/export/user_info_all.csv-getmerge是HDFS命令里非常好用的一個工具它會把目錄下的所有文件按順序拼接成一個本地文件。默認情況下它不做任何格式處理直接把內(nèi)容首尾相接。如果每個part文件末尾都有換行符合并后的文件行不會串。但如果你不確定可以在-getmerge后面加一個-nl參數(shù)讓它在每個文件之間自動追加換行hdfs dfs -getmerge -nl /tmp/user_export /home/user/export/user_info_all.csv這里有個經(jīng)驗點在寫導出SQL之前就可以提前控制Reducer數(shù)量。如果數(shù)據(jù)量不大通過SET mapred.reduce.tasks 1;強制只有一個Reducer生成的就是一個part文件連合并這一步都省了。但這個方法只適合幾GB以內(nèi)的數(shù)據(jù)量數(shù)據(jù)量大了單Reducer會成為性能瓶頸。5. 復雜場景與性能調(diào)優(yōu)5.1 大文件導入如何避免OOM和解析失敗曾經(jīng)接手過一個業(yè)務需求一份CSV文件接近20GB單行的字段長度最長超過2MB用戶畫像標簽串在一起導致。直接用LOAD DATA導入Hive任務跑十來分鐘后就開始報Java heap space或者OutOfMemory。排查下來問題出在兩點第一OpenCSVSerde在處理超長字段時會把整行數(shù)據(jù)讀入內(nèi)存進行解析如果單行數(shù)據(jù)太大Mapper所在的Container很容易OOM。解決思路是先看業(yè)務上能不能拆開這些超長字段如果不能就得在Container參數(shù)上加內(nèi)存SET mapreduce.map.memory.mb 4096; SET mapreduce.map.java.opts -Xmx3072m;第二CSV文件如果有壞行比如某一行少了一個逗號導致列數(shù)不匹配OpenCSVSerde默認是把缺失的列置為NULL不會報錯但如果某一行列數(shù)多出幾個則有可能觸發(fā)解析異常。我建議在導入前先用一個快速巡檢任務跑一遍文件統(tǒng)計行數(shù)和最大列數(shù)# 統(tǒng)計文件總行數(shù) wc -l user_info.csv # 統(tǒng)計最大列數(shù)用awk按逗號拆分 awk -F, {print NF} user_info.csv | sort -nr | head -1如果某個文件的列數(shù)比表字段多這批數(shù)據(jù)肯定有問題先別急著導入。5.2 分區(qū)表怎么導入和導出CSV實際業(yè)務中我們很少把CSV直接導入非分區(qū)表因為后續(xù)查詢、清理、生命周期管理都離不開分區(qū)。帶分區(qū)的建表語句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);靜態(tài)分區(qū)導入LOAD DATA LOCAL INPATH /home/user/data/user_info_20240601.csv INTO TABLE dws_user_info PARTITION (dt2024-06-01);動態(tài)分區(qū)導入從不帶分區(qū)的臨時表刷到分區(qū)表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;導出場景反過來查某個分區(qū)的數(shù)據(jù)寫成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時要不要順便壓縮取決于下游怎么用。如果是給數(shù)據(jù)倉庫或者分析平臺做后續(xù)處理gzip壓縮是很好的選擇——體積能縮小到原來的五分之一左右而且大多數(shù)系統(tǒng)支持直接讀取gzip格式的CSV。實現(xiàn)方式非常簡單在導出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給非技術的業(yè)務人員就別壓縮了。他們拿到一個.gz文件大概率不知道怎么打開反而增加溝通成本。給程序做自動化消費的才考慮壓縮。5.4 編碼問題的統(tǒng)一處理CSV最讓人頭痛的編碼坑在我做過的項目里出現(xiàn)頻率非常高。業(yè)務方從Excel導出的CSV默認是GBK或者GB2312編碼上傳到HDFS后Hive按UTF-8解析結果中文全部變成亂碼。處理方案有兩種第一種在導入前統(tǒng)一轉換編碼# 本地轉碼后再上傳 iconv -f GBK -t UTF-8 user_info_original.csv user_info_utf8.csv # 轉碼后的文件上傳HDFS hdfs dfs -put user_info_utf8.csv /input/第二種在導出時指定編碼給下游。Hive自身沒有編碼轉換函數(shù)但可以通過SELECT搭配普通字符串操作變通處理常見的做法是導出后在本地統(tǒng)一轉換。我的建議是源頭統(tǒng)一UTF-8。和業(yè)務方約定所有CSV文件必須提供UTF-8編碼版本。這個約定看起來簡單但在實操中能省掉一大半的亂碼麻煩。如果你控制不了源端那就在導入前例行執(zhí)行iconv轉碼步驟把它寫進定時調(diào)度里。6. 常見問題與排查技巧實錄6.1 表頭沒有被跳過第一條數(shù)據(jù)丟失現(xiàn)象建表時明明設置了skip.header.line.count1LOAD之后查詢發(fā)現(xiàn)第一行數(shù)據(jù)不見了。排查思路這個屬性是Hive讀取時候的控制參數(shù)它生效的前提是文件在表目錄下能被識別成數(shù)據(jù)文件。如果你用LOAD DATA加載的是整個CSV文件會發(fā)現(xiàn)表頭行被正確跳過但如果你先hdfs dfs -put把文件放進了外部表的LOCATION目錄然后建表時又加了skip.header.line.count1那么建表之前文件就已經(jīng)在那里了之后LOAD新文件時表頭跳過邏輯可能只作用于新增的文件而不是歷史文件。實操中我傾向于不用這個屬性而是在上傳之前用腳本統(tǒng)一去掉表頭。理由很簡單這個屬性在不同Hive版本上存在兼容差異而預處理去表頭是確定性最高的方案。6.2 字段內(nèi)含換行符導致行數(shù)變多CSV規(guī)范允許字段內(nèi)包含換行符比如一個備注字段里就有一個真正的換行這就導致Hive在按行讀取文件時誤以為出現(xiàn)了新紀錄。這種場景下OpenCSVSerde同樣能處理——RFC 4180規(guī)范要求含換行的字段必須用引號包住OpenCSVSerde解析時能識別出這是個字段內(nèi)的換行而不是行終結符。但要注意如果源端生成CSV時沒有用引號包住含換行的字段那任何解析器都沒法正確判斷數(shù)據(jù)必然被拆成兩行。而且Hive讀取文件是按行\(zhòng)n切分后再交給SerDe解析的這意味著如果字段內(nèi)有換行但沒有引號包裹必錯如果字段內(nèi)有換行且用引號包裹OpenCSVSerde可以正確還原換行實測過一個文件里包含大量多行地址字段的數(shù)據(jù)用OpenCSVSerde加載后文本字段內(nèi)的換行被還原成\n存儲字段整體是完整的查詢結果也能對上行數(shù)。前提條件就是源端CSV生成必須規(guī)范。6.3 導入后部分行顯示為NULL這個最常見的原因是CSV文件里本來就有空值。比如1001,張三,28,,2024-06-01city字段是空的導入后顯示NULL是正常的。但有一種情況很坑CSV里出現(xiàn)\N字符。在Hive中\(zhòng)N是文本文件中表達NULL的默認標記。如果你的CSV數(shù)據(jù)里恰好有字符串\N比如某個字段值是\N表示空導入后它會被自動識別成NULL而不是字符串\N。這是Hive的默認行為處理方法是建表時通過SERDEPROPERTIES修改WITH SERDEPROPERTIES ( serialization.null.format )設置成空字符串后只有空字段才是NULL\N會被當作普通字符串處理。6.4 導出文件亂碼問題導入時亂碼的原因在前面說過了。導出時亂碼的問題則往往出在文件后續(xù)流轉環(huán)節(jié)用INSERT OVERWRITE DIRECTORY導出的文件是UTF-8編碼的但用Excel打開的時候Excel會默認猜測編碼如果猜成GBK就會顯示亂碼。有一個不太優(yōu)雅但很實用的做法導出后帶一個BOM頭Byte Order Mark。實測帶BOM的UTF-8文件用Excel打開時編碼識別準確率幾乎100%。Hive本身不支持寫B(tài)OM所以通常在本地用命令處理# 給導出文件加上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或者正則處理掉。所以我只在面向業(yè)務人員的導出場景加上BOM面向系統(tǒng)對接的導出保持純UTF-8。6.5 常見問題排查速查表現(xiàn)象可能原因解決思路導入后中文亂碼源文件GBK編碼Hive按UTF-8解析上傳前用iconv轉UTF-8第一條正常數(shù)據(jù)丟失表頭未被過濾或過濾策略不對預處理去掉表頭重新LOAD列數(shù)錯位、字段串列字段內(nèi)含逗號但無引號包裹源端修復CSV或替換分隔符字段內(nèi)容被截斷字段內(nèi)含換行符且被解析成新行確認引號包裹或用OpenCSVSerdeLOAD后原文件消失從HDFS導入時文件被移動用外部表LOCATION方式或先復制導入后大量NULL類型轉換失敗或CSV空值先用全STRING裸表加載導出文件帶日志信息未加--silenttrue重跑并添加靜默參數(shù)導出文件有多個partReducer數(shù)量大于1用getmerge合并或設置reduce1帶表頭導出時UNION ALL類型不匹配第一個分支與后續(xù)分支字段類型不一致全部字段CAST成STRING7. 踩過的坑與實用經(jīng)驗總結我在做Hive數(shù)據(jù)導入導出項目時踩過不少坑有些坑的排查時間長達一整天。這里分享幾個最典型的經(jīng)驗。第一個經(jīng)驗從業(yè)務方接收CSV時先做數(shù)據(jù)質(zhì)檢再導入。一份20GB的CSV文件如果導入后又發(fā)現(xiàn)某個字段串列或者編碼有問題重新導入的時間成本非常高。我的習慣是先拿一個幾千行的采樣文件導入到臨時表跑幾個簡單的查詢行數(shù)、空值率、列數(shù)分布確認沒有問題后再全量導入。這個步驟只花幾分鐘但能避免幾小時的返工。第二個經(jīng)驗用外部表做中轉用內(nèi)部表做存儲。所有CSV原始數(shù)據(jù)都建外部表LOCATION指向固定的HDFS目錄。這樣做的好處是后續(xù)清洗出錯時原始數(shù)據(jù)還在DROP表也不會影響HDFS上的文件。清洗后的數(shù)據(jù)再用內(nèi)部表保存享受Hive的管理和生命周期功能。第三個經(jīng)驗導出CSV給業(yè)務方之前一定要跟對方確認打開方式。如果對方說“我用Excel打開”那么編碼用UTF-8帶BOM分隔符用逗號字段內(nèi)有逗號的情況必須加雙引號如果對方說“我用Python pandas讀”那么編碼用UTF-8不帶BOM加不加表頭取決于對方需求但字段內(nèi)最好不要有多余的轉義符否則pandas的read_csv參數(shù)配置會很繁瑣。上個項目里就遇到過這樣的事我導出的CSV字段里為了兼容Excel加了雙引號轉義結果下游Python腳本用默認參數(shù)讀取時引號也被讀進來了字段值變成了張三帶引號對方又花了大半天寫清洗邏輯。從那以后每次導出我都會問清楚下游消費方式再決定轉義策略。第四個經(jīng)驗CSV導入導出的調(diào)度任務一定要有監(jiān)控和告警。數(shù)據(jù)量大的時候LOAD DATA任務可能跑10分鐘以上如果中途失敗Hive不會自動清理半成品數(shù)據(jù)部分文件可能已經(jīng)被寫入。重跑之前先確保表目錄是干凈的或者用INSERT OVERWRITE而不是INSERT INTO否則可能舊新數(shù)據(jù)混在一起。8. 一個端到端的完整實戰(zhàn)案例為了把這些分散的點串起來我給出一個從我實際項目中抽象出來的完整流程。需求描述某公司的運營團隊每天會從業(yè)務系統(tǒng)導出當天的用戶行為CSV文件包含用戶ID、行為類型、行為時間、行為詳情等字段。文件格式為標準CSVUTF-8編碼第一行是表頭。需要每天自動導入Hive供數(shù)據(jù)分析團隊使用。8.1 第一步創(chuàng)建外部臨時表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立刻就能查看到數(shù)據(jù)不用再執(zhí)行LOAD DATA。文件不會被移動原始文件保留一份后續(xù)如果要回溯仍然找得到。8.3 第三步數(shù)據(jù)質(zhì)檢-- 查看總行數(shù) 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;如果行數(shù)、空值率、樣本內(nèi)容都符合預期繼續(xù)下一步不滿意就直接刪目錄重新上傳。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高很多而且還支持后續(xù)的向量化查詢、列裁剪等優(yōu)化。這一步重點是把STRING類型的event_time轉成TIMESTAMP過濾掉空user_id的臟數(shù)據(jù)。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直接可以分發(fā)給業(yè)務團隊使用。9. 寫在最后的補充我實際做這些導入導出工作時越來越覺得CSV這種格式雖然簡單但正因為簡單反而容易在細節(jié)上翻車。SerDe選型、編碼統(tǒng)一、表頭處理、文件合并每個環(huán)節(jié)單獨拿出來都不難合在一起卻需要一套完整的流程去保證穩(wěn)定可靠。建議你在自己的項目里先建立一套標準化的CSV導入導出流程模板包括建表語句模板、質(zhì)檢SQL模板、導出腳本模板。這樣新需求來了只需要替換表名和字段名就能快速復用整套流程不用每次從零開始摸索。這套流程沉淀得越完善你在處理數(shù)據(jù)對接上的時間成本就越低。如果后續(xù)有需要還可以在這個基礎上擴展成通用的數(shù)據(jù)交換模塊支持其他分隔符格式、壓縮格式、甚至自動識別表頭字段名的功能。先把基礎的CSV鏈路打磨扎實后面所有擴展都會順很多。 Hive導入導出CSV全攻略：從建表到踩坑實戰(zhàn)
1. 項目背景與核心訴求解析做數(shù)據(jù)開發(fā)的同學十有八九都跟CSV打過交道。CSV這玩意兒說簡單也簡單就是個逗號分隔的純文本文件說麻煩也麻煩——編碼問題、引號轉義、換行符不一致每一樣都能折騰人半天。我自己在實際項目中接過不少這樣的需求業(yè)務方丟過來一個幾GB的CSV文件讓我塞進Hive表里做分析或者反過來讓我把Hive里跑完的結果導出成CSV發(fā)給下游系統(tǒng)或者交給產(chǎn)品同學做報表?？此苾蓚€方向的操作背后的細節(jié)其實多得超出大多數(shù)人的預期。這篇內(nèi)容我圍繞“Hive導入導出CSV全”這個主題把從建表、加載、導出到踩坑排查的完整鏈路都梳理一遍。無論你是剛接觸Hive的新手還是已經(jīng)寫了幾年HQL的老手只要跟CSV打過交道里面總有幾條經(jīng)驗能幫你省下半天排查時間。我會把每一步為什么要這么做、參數(shù)背后的原理講清楚而不是丟給你一堆命令讓你盲目復制。先說清楚這篇的核心覆蓋范圍包括Hive內(nèi)部表與外部表的選擇邏輯、CSV文件與Hive表字段的對應關系、LOAD DATA和INSERT OVERWRITE兩條主路徑、各種分隔符和轉義符的處理方式、以及導出時常見的文件合并、壓縮格式、編碼轉換等實操問題。每一條都是我在真實業(yè)務環(huán)境中驗證過的方案。2. 環(huán)境準備與基礎概念2.1 Hive表類型選擇內(nèi)部表還是外部表很多人一上來就建表導數(shù)據(jù)結果后面刪表的時候把原始CSV也連帶刪掉了哭都來不及。這里核心差別在于內(nèi)部表managed table的數(shù)據(jù)文件完全由Hive管理你DROP表的時候HDFS上的數(shù)據(jù)文件會一并刪除外部表external table則只是讓Hive“掛載”到某個HDFS目錄上DROP表只刪元數(shù)據(jù)原始文件還留在原處。我自己的習慣是凡是從CSV導入的數(shù)據(jù)一律建外部表。理由是CSV文件往往是業(yè)務方提供的原始資產(chǎn)可能還需要反復清洗、回溯一旦被內(nèi)部表連帶刪掉重新找業(yè)務方要文件的過程極其痛苦。建外部表只是多了一個關鍵字的事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 為什么優(yōu)先用OpenCSVSerde而不是默認的LazySimpleSerDe這是很多教程沒講透的地方。Hive對TEXTFILE格式的默認SerDe是LazySimpleSerDe它對CSV的解析能力其實很弱。默認情況下它只認分隔符不認引號——也就是說如果CSV里某個字段值本身包含了逗號比如北京,朝陽區(qū)默認SerDe會把這個字段從逗號處劈開導致列錯位、數(shù)據(jù)錯亂。而OpenCSVSerde是專門為CSV設計的SerDe它原生支持引號包圍的字段、字段內(nèi)部的逗號、引號轉義等標準CSV規(guī)則。換句話說你拿到的CSV只要是符合RFC 4180規(guī)范或者Excel導出的常見格式OpenCSVSerde都能正確解析。實測對比過同一個文件用兩種SerDe加載的結果默認SerDe解析出來的錯誤率在包含逗號字段的場景下能達到30%以上而OpenCSVSerde基本能做到零錯位。不用糾結直接選OpenCSVSerde。2.3 表字段類型映射CSV沒有類型Hive有CSV文件本身是沒有類型概念的所有字段本質(zhì)上都是字符串。到了Hive里你要給它聲明Int、Double、Date這些類型這里就埋著導入失敗最常見的雷——某一行某個字段是一個空字符串而它對應的Hive字段類型是INTLOAD的時候會直接返回NULL。我的建議是導入階段先用全STRING類型的“裸表”把數(shù)據(jù)原樣加載進來做完清洗和校驗之后再通過INSERT SELECT的方式轉成正式的分析表該CAST的CAST該過濾的過濾。這樣做的好處是導入流程本身不會因為類型轉換報錯而中斷你始終能在原表里看到原始數(shù)據(jù)。比如這樣建一張裸表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. 核心細節(jié)解析與實操要點3.1 分隔符、引號符、轉義符的搭配規(guī)則CSV的解析核心在于三個字符的配合分隔符separator、引號符quote、轉義符escape。很多導入異常都源于這三個字符配置不一致。先看標準CSV里最經(jīng)典的情況user_id,user_name,age,city,created_at 1001,張三,男,28,北京,朝陽區(qū),2024-06-01 12:00:00這里張三,男被雙引號包住了它內(nèi)部有逗號但整體是一個字段。OpenCSVSerde解析時只要看到quoteChar包裹的內(nèi)容就會把內(nèi)部的逗號當普通字符處理。而escapeChar的作用是處理字段內(nèi)部出現(xiàn)的引號本身比如某個字段值是他說你好CSV里會寫成他說\你好\解析器靠escapeChar識別出這里的引號是內(nèi)容而不是語法符號。實操心得如果你導出CSV是給Excel用的分隔符用逗號、引號符用雙引號、轉義符用反斜杠就是最穩(wěn)的組合。如果字段里既有逗號又有引號轉義符一定要配好否則導出再導入會出現(xiàn)列錯亂甚至數(shù)據(jù)丟失。3.2 跳過表頭行TBLPROPERTIES的妙用業(yè)務方給的CSV十有八九帶著表頭Hive默認不會自動跳過它會把表頭當成一條數(shù)據(jù)加載進去。你可以在建表的時候通過TBLPROPERTIES聲明跳過第一行TBLPROPERTIES (skip.header.line.count 1);這個屬性從Hive 2.3版本開始支持實測在3.x版本上完全穩(wěn)定。如果你的Hive版本比較老2.1及以下不支持這個屬性那就只能靠清洗腳本去掉表頭再上傳或者加載后再跑一條DELETE——Hive對DELETE的支持很弱所以最好的辦法是加載前用sed -i 1d file.csv這類命令處理一下。3.3 多分隔符場景怎么處理有些“偽CSV”其實不是逗號分隔的可能是豎線|、制表符\t、分號;。這種情況下不需要改代碼只需在建表時改掉separatorCharWITH SERDEPROPERTIES ( separatorChar |, quoteChar \, escapeChar \\ )給一個實際場景的對照參考數(shù)據(jù)來源常見分隔符注意事項標準CSV逗號 ,注意字段內(nèi)逗號需要引號包裹Excel另存為CSV逗號 ,注意中文編碼建議UTF-8日志系統(tǒng)導出豎線 |檢查字段內(nèi)是否包含豎線本身MySQL導出制表符 \t注意\t在HQL里的轉義寫法金融報文分號 ;字段內(nèi)分號的處理要提前確認注意改分隔符之前先確認你的數(shù)據(jù)源在生成文件時是怎么轉義的。如果源端沒做任何轉義那你加載的時候無論怎么配都是會錯位的。4. 實操過程與核心環(huán)節(jié)實現(xiàn)4.1 導入路徑一LOAD DATA導入本地或HDFS文件這是Hive導入CSV最直接的方式。核心命令是LOAD DATA它分兩種來源LOCAL表示從本地磁盤上傳不帶LOCAL表示文件已經(jīng)在HDFS上。-- 從本地文件系統(tǒng)導入文件會被復制到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的執(zhí)行任務。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是全量覆蓋——每次執(zhí)行都會清空目標表目錄下的所有文件然后寫入新數(shù)據(jù)。如果只想追加用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;這樣導出的文件是純逗號分隔的文本文件沒有表頭。很多下游同學拿到后發(fā)現(xiàn)列名對不上所以實際項目中我通常會自己拼一行表頭讓整個輸出變成帶表頭的完整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第二條分支把數(shù)字類型CAST成STRING這樣整個結果集的數(shù)據(jù)類型是統(tǒng)一的。4.4 導出路徑二用Hive CLI輸出重定向到本地文件如果你查詢的結果量不大幾千條到幾萬條可以直接用Hive CLI或Beeline執(zhí)行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對字段內(nèi)含逗號、換行符的場景處理得比csv格式更好。如果追求極致的標準化可以用tsv2輸出制表符分隔的結果。注意--silenttrue不加上這個參數(shù)的話Beeline會把一大堆Hive的運行日志、箭頭符號混進輸出文件里文件直接變臟。我自己第一次導出就踩了這個坑輸出文件前幾行全是INFO級別的日志還得用腳本清理非常麻煩。4.5 導出路徑三通過HDFS下載合并文件這個方法是生產(chǎn)環(huán)境最常用的。Hive跑完INSERT OVERWRITE DIRECTORY之后HDFS上往往會生成多個part文件數(shù)量取決于運行時的Reducer數(shù)量。如果需要給業(yè)務方一個單文件CSV需要把這些part文件合并。步驟是這樣的# 查看生成的文件 hdfs dfs -ls /tmp/user_export # 合并所有part文件到本地 hdfs dfs -getmerge /tmp/user_export /home/user/export/user_info_all.csv-getmerge是HDFS命令里非常好用的一個工具它會把目錄下的所有文件按順序拼接成一個本地文件。默認情況下它不做任何格式處理直接把內(nèi)容首尾相接。如果每個part文件末尾都有換行符合并后的文件行不會串。但如果你不確定可以在-getmerge后面加一個-nl參數(shù)讓它在每個文件之間自動追加換行hdfs dfs -getmerge -nl /tmp/user_export /home/user/export/user_info_all.csv這里有個經(jīng)驗點在寫導出SQL之前就可以提前控制Reducer數(shù)量。如果數(shù)據(jù)量不大通過SET mapred.reduce.tasks 1;強制只有一個Reducer生成的就是一個part文件連合并這一步都省了。但這個方法只適合幾GB以內(nèi)的數(shù)據(jù)量數(shù)據(jù)量大了單Reducer會成為性能瓶頸。5. 復雜場景與性能調(diào)優(yōu)5.1 大文件導入如何避免OOM和解析失敗曾經(jīng)接手過一個業(yè)務需求一份CSV文件接近20GB單行的字段長度最長超過2MB用戶畫像標簽串在一起導致。直接用LOAD DATA導入Hive任務跑十來分鐘后就開始報Java heap space或者OutOfMemory。排查下來問題出在兩點第一OpenCSVSerde在處理超長字段時會把整行數(shù)據(jù)讀入內(nèi)存進行解析如果單行數(shù)據(jù)太大Mapper所在的Container很容易OOM。解決思路是先看業(yè)務上能不能拆開這些超長字段如果不能就得在Container參數(shù)上加內(nèi)存SET mapreduce.map.memory.mb 4096; SET mapreduce.map.java.opts -Xmx3072m;第二CSV文件如果有壞行比如某一行少了一個逗號導致列數(shù)不匹配OpenCSVSerde默認是把缺失的列置為NULL不會報錯但如果某一行列數(shù)多出幾個則有可能觸發(fā)解析異常。我建議在導入前先用一個快速巡檢任務跑一遍文件統(tǒng)計行數(shù)和最大列數(shù)# 統(tǒng)計文件總行數(shù) wc -l user_info.csv # 統(tǒng)計最大列數(shù)用awk按逗號拆分 awk -F, {print NF} user_info.csv | sort -nr | head -1如果某個文件的列數(shù)比表字段多這批數(shù)據(jù)肯定有問題先別急著導入。5.2 分區(qū)表怎么導入和導出CSV實際業(yè)務中我們很少把CSV直接導入非分區(qū)表因為后續(xù)查詢、清理、生命周期管理都離不開分區(qū)。帶分區(qū)的建表語句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);靜態(tài)分區(qū)導入LOAD DATA LOCAL INPATH /home/user/data/user_info_20240601.csv INTO TABLE dws_user_info PARTITION (dt2024-06-01);動態(tài)分區(qū)導入從不帶分區(qū)的臨時表刷到分區(qū)表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;導出場景反過來查某個分區(qū)的數(shù)據(jù)寫成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時要不要順便壓縮取決于下游怎么用。如果是給數(shù)據(jù)倉庫或者分析平臺做后續(xù)處理gzip壓縮是很好的選擇——體積能縮小到原來的五分之一左右而且大多數(shù)系統(tǒng)支持直接讀取gzip格式的CSV。實現(xiàn)方式非常簡單在導出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給非技術的業(yè)務人員就別壓縮了。他們拿到一個.gz文件大概率不知道怎么打開反而增加溝通成本。給程序做自動化消費的才考慮壓縮。5.4 編碼問題的統(tǒng)一處理CSV最讓人頭痛的編碼坑在我做過的項目里出現(xiàn)頻率非常高。業(yè)務方從Excel導出的CSV默認是GBK或者GB2312編碼上傳到HDFS后Hive按UTF-8解析結果中文全部變成亂碼。處理方案有兩種第一種在導入前統(tǒng)一轉換編碼# 本地轉碼后再上傳 iconv -f GBK -t UTF-8 user_info_original.csv user_info_utf8.csv # 轉碼后的文件上傳HDFS hdfs dfs -put user_info_utf8.csv /input/第二種在導出時指定編碼給下游。Hive自身沒有編碼轉換函數(shù)但可以通過SELECT搭配普通字符串操作變通處理常見的做法是導出后在本地統(tǒng)一轉換。我的建議是源頭統(tǒng)一UTF-8。和業(yè)務方約定所有CSV文件必須提供UTF-8編碼版本。這個約定看起來簡單但在實操中能省掉一大半的亂碼麻煩。如果你控制不了源端那就在導入前例行執(zhí)行iconv轉碼步驟把它寫進定時調(diào)度里。6. 常見問題與排查技巧實錄6.1 表頭沒有被跳過第一條數(shù)據(jù)丟失現(xiàn)象建表時明明設置了skip.header.line.count1LOAD之后查詢發(fā)現(xiàn)第一行數(shù)據(jù)不見了。排查思路這個屬性是Hive讀取時候的控制參數(shù)它生效的前提是文件在表目錄下能被識別成數(shù)據(jù)文件。如果你用LOAD DATA加載的是整個CSV文件會發(fā)現(xiàn)表頭行被正確跳過但如果你先hdfs dfs -put把文件放進了外部表的LOCATION目錄然后建表時又加了skip.header.line.count1那么建表之前文件就已經(jīng)在那里了之后LOAD新文件時表頭跳過邏輯可能只作用于新增的文件而不是歷史文件。實操中我傾向于不用這個屬性而是在上傳之前用腳本統(tǒng)一去掉表頭。理由很簡單這個屬性在不同Hive版本上存在兼容差異而預處理去表頭是確定性最高的方案。6.2 字段內(nèi)含換行符導致行數(shù)變多CSV規(guī)范允許字段內(nèi)包含換行符比如一個備注字段里就有一個真正的換行這就導致Hive在按行讀取文件時誤以為出現(xiàn)了新紀錄。這種場景下OpenCSVSerde同樣能處理——RFC 4180規(guī)范要求含換行的字段必須用引號包住OpenCSVSerde解析時能識別出這是個字段內(nèi)的換行而不是行終結符。但要注意如果源端生成CSV時沒有用引號包住含換行的字段那任何解析器都沒法正確判斷數(shù)據(jù)必然被拆成兩行。而且Hive讀取文件是按行\(zhòng)n切分后再交給SerDe解析的這意味著如果字段內(nèi)有換行但沒有引號包裹必錯如果字段內(nèi)有換行且用引號包裹OpenCSVSerde可以正確還原換行實測過一個文件里包含大量多行地址字段的數(shù)據(jù)用OpenCSVSerde加載后文本字段內(nèi)的換行被還原成\n存儲字段整體是完整的查詢結果也能對上行數(shù)。前提條件就是源端CSV生成必須規(guī)范。6.3 導入后部分行顯示為NULL這個最常見的原因是CSV文件里本來就有空值。比如1001,張三,28,,2024-06-01city字段是空的導入后顯示NULL是正常的。但有一種情況很坑CSV里出現(xiàn)\N字符。在Hive中\(zhòng)N是文本文件中表達NULL的默認標記。如果你的CSV數(shù)據(jù)里恰好有字符串\N比如某個字段值是\N表示空導入后它會被自動識別成NULL而不是字符串\N。這是Hive的默認行為處理方法是建表時通過SERDEPROPERTIES修改WITH SERDEPROPERTIES ( serialization.null.format )設置成空字符串后只有空字段才是NULL\N會被當作普通字符串處理。6.4 導出文件亂碼問題導入時亂碼的原因在前面說過了。導出時亂碼的問題則往往出在文件后續(xù)流轉環(huán)節(jié)用INSERT OVERWRITE DIRECTORY導出的文件是UTF-8編碼的但用Excel打開的時候Excel會默認猜測編碼如果猜成GBK就會顯示亂碼。有一個不太優(yōu)雅但很實用的做法導出后帶一個BOM頭Byte Order Mark。實測帶BOM的UTF-8文件用Excel打開時編碼識別準確率幾乎100%。Hive本身不支持寫B(tài)OM所以通常在本地用命令處理# 給導出文件加上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或者正則處理掉。所以我只在面向業(yè)務人員的導出場景加上BOM面向系統(tǒng)對接的導出保持純UTF-8。6.5 常見問題排查速查表現(xiàn)象可能原因解決思路導入后中文亂碼源文件GBK編碼Hive按UTF-8解析上傳前用iconv轉UTF-8第一條正常數(shù)據(jù)丟失表頭未被過濾或過濾策略不對預處理去掉表頭重新LOAD列數(shù)錯位、字段串列字段內(nèi)含逗號但無引號包裹源端修復CSV或替換分隔符字段內(nèi)容被截斷字段內(nèi)含換行符且被解析成新行確認引號包裹或用OpenCSVSerdeLOAD后原文件消失從HDFS導入時文件被移動用外部表LOCATION方式或先復制導入后大量NULL類型轉換失敗或CSV空值先用全STRING裸表加載導出文件帶日志信息未加--silenttrue重跑并添加靜默參數(shù)導出文件有多個partReducer數(shù)量大于1用getmerge合并或設置reduce1帶表頭導出時UNION ALL類型不匹配第一個分支與后續(xù)分支字段類型不一致全部字段CAST成STRING7. 踩過的坑與實用經(jīng)驗總結我在做Hive數(shù)據(jù)導入導出項目時踩過不少坑有些坑的排查時間長達一整天。這里分享幾個最典型的經(jīng)驗。第一個經(jīng)驗從業(yè)務方接收CSV時先做數(shù)據(jù)質(zhì)檢再導入。一份20GB的CSV文件如果導入后又發(fā)現(xiàn)某個字段串列或者編碼有問題重新導入的時間成本非常高。我的習慣是先拿一個幾千行的采樣文件導入到臨時表跑幾個簡單的查詢行數(shù)、空值率、列數(shù)分布確認沒有問題后再全量導入。這個步驟只花幾分鐘但能避免幾小時的返工。第二個經(jīng)驗用外部表做中轉用內(nèi)部表做存儲。所有CSV原始數(shù)據(jù)都建外部表LOCATION指向固定的HDFS目錄。這樣做的好處是后續(xù)清洗出錯時原始數(shù)據(jù)還在DROP表也不會影響HDFS上的文件。清洗后的數(shù)據(jù)再用內(nèi)部表保存享受Hive的管理和生命周期功能。第三個經(jīng)驗導出CSV給業(yè)務方之前一定要跟對方確認打開方式。如果對方說“我用Excel打開”那么編碼用UTF-8帶BOM分隔符用逗號字段內(nèi)有逗號的情況必須加雙引號如果對方說“我用Python pandas讀”那么編碼用UTF-8不帶BOM加不加表頭取決于對方需求但字段內(nèi)最好不要有多余的轉義符否則pandas的read_csv參數(shù)配置會很繁瑣。上個項目里就遇到過這樣的事我導出的CSV字段里為了兼容Excel加了雙引號轉義結果下游Python腳本用默認參數(shù)讀取時引號也被讀進來了字段值變成了張三帶引號對方又花了大半天寫清洗邏輯。從那以后每次導出我都會問清楚下游消費方式再決定轉義策略。第四個經(jīng)驗CSV導入導出的調(diào)度任務一定要有監(jiān)控和告警。數(shù)據(jù)量大的時候LOAD DATA任務可能跑10分鐘以上如果中途失敗Hive不會自動清理半成品數(shù)據(jù)部分文件可能已經(jīng)被寫入。重跑之前先確保表目錄是干凈的或者用INSERT OVERWRITE而不是INSERT INTO否則可能舊新數(shù)據(jù)混在一起。8. 一個端到端的完整實戰(zhàn)案例為了把這些分散的點串起來我給出一個從我實際項目中抽象出來的完整流程。需求描述某公司的運營團隊每天會從業(yè)務系統(tǒng)導出當天的用戶行為CSV文件包含用戶ID、行為類型、行為時間、行為詳情等字段。文件格式為標準CSVUTF-8編碼第一行是表頭。需要每天自動導入Hive供數(shù)據(jù)分析團隊使用。8.1 第一步創(chuàng)建外部臨時表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立刻就能查看到數(shù)據(jù)不用再執(zhí)行LOAD DATA。文件不會被移動原始文件保留一份后續(xù)如果要回溯仍然找得到。8.3 第三步數(shù)據(jù)質(zhì)檢-- 查看總行數(shù) 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;如果行數(shù)、空值率、樣本內(nèi)容都符合預期繼續(xù)下一步不滿意就直接刪目錄重新上傳。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高很多而且還支持后續(xù)的向量化查詢、列裁剪等優(yōu)化。這一步重點是把STRING類型的event_time轉成TIMESTAMP過濾掉空user_id的臟數(shù)據(jù)。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直接可以分發(fā)給業(yè)務團隊使用。9. 寫在最后的補充我實際做這些導入導出工作時越來越覺得CSV這種格式雖然簡單但正因為簡單反而容易在細節(jié)上翻車。SerDe選型、編碼統(tǒng)一、表頭處理、文件合并每個環(huán)節(jié)單獨拿出來都不難合在一起卻需要一套完整的流程去保證穩(wěn)定可靠。建議你在自己的項目里先建立一套標準化的CSV導入導出流程模板包括建表語句模板、質(zhì)檢SQL模板、導出腳本模板。這樣新需求來了只需要替換表名和字段名就能快速復用整套流程不用每次從零開始摸索。這套流程沉淀得越完善你在處理數(shù)據(jù)對接上的時間成本就越低。如果后續(xù)有需要還可以在這個基礎上擴展成通用的數(shù)據(jù)交換模塊支持其他分隔符格式、壓縮格式、甚至自動識別表頭字段名的功能。先把基礎的CSV鏈路打磨扎實后面所有擴展都會順很多。