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