從入門到實戰(zhàn)：內(nèi)置函數(shù)、窗口函數(shù)與UDF自定義函數(shù)完整解析)
1. 先搞清楚 Hive 函數(shù)到底是怎么回事做了這么多年數(shù)倉開發(fā)我發(fā)現(xiàn)很多人在接觸 Hive 函數(shù)時習(xí)慣性地把它當成 SQL 函數(shù)的一個“方言版本”。這種理解不能說錯但會埋下不少坑。Hive 函數(shù)真正特殊的地方在于它運行在分布式計算引擎上處理的是“一批數(shù)據(jù)”而不是“一行數(shù)據(jù)”這就讓它的執(zhí)行邏輯、使用邊界和優(yōu)化手段都和傳統(tǒng)關(guān)系型數(shù)據(jù)庫里的函數(shù)有很大區(qū)別。Hive 函數(shù)解決的核心問題說白了就是在海量數(shù)據(jù)上完成清洗、轉(zhuǎn)換、聚合、分析這些動作而不用你去寫 MapReduce 程序。而且 Hive 函數(shù)體系本身也足夠龐大從最簡單的字符串截取到復(fù)雜的自定義聚合基本覆蓋了離線數(shù)倉開發(fā)里九成以上的數(shù)據(jù)處理訴求。這篇文章會從我實際項目的角度出發(fā)把 Hive 函數(shù)從內(nèi)置到自定義從常用姿勢到避坑經(jīng)驗完整拆一遍希望能給正在做數(shù)倉開發(fā)、數(shù)據(jù)分析和 ETL 的同學(xué)一些實在的參考。有一點得先說透只記住某個函數(shù)的語法是沒用的如果你不清楚它的觸發(fā)時機、NULL 值處理邏輯、以及它在分布式環(huán)境下的執(zhí)行特性那上線之后出問題的概率會非常大。所以這篇文章里我不會只列函數(shù)清單更重要的是把“為什么這樣用”“在什么場景下踩過坑”講清楚。適合看這篇內(nèi)容的人我大致分了三類剛?cè)腴T的數(shù)據(jù)分析同學(xué)需要掌握核心函數(shù)來寫日常取數(shù)邏輯做數(shù)倉 ETL 的開發(fā)需要理解自定義函數(shù)和窗口函數(shù)的底層行為以及所有被數(shù)據(jù)傾斜、NULL 值異常、UDF 性能問題折磨過的同學(xué)。1.1 Hive 函數(shù)的本質(zhì)與核心使用場景Hive 函數(shù)的本質(zhì)可以理解為把復(fù)雜的數(shù)據(jù)處理邏輯封裝成一個可復(fù)用的操作單元并交給底層的計算引擎去分布式執(zhí)行。比如你要對一億條用戶行為日志做正則匹配提取 URL 中的關(guān)鍵參數(shù)如果自己寫 MapReduce要處理輸入分片、map 階段的逐行處理、輸出序列化等等工作量不小而用regexp_extract這個 Hive 函數(shù)一行 SQL 就搞定了。所以 Hive 函數(shù)的核心價值就是把通用的數(shù)據(jù)處理邏輯做成標準化操作讓你把精力集中在業(yè)務(wù)問題上。在實際的項目里Hive 函數(shù)主要覆蓋這么幾類場景數(shù)據(jù)清洗場景字段拼接、字符串分割、正則替換、JSON 解析、空值兜底這些是數(shù)據(jù)入倉后最常做的事。統(tǒng)計分析場景按照維度分組后做求和、均值、去重計數(shù)以及更復(fù)雜的百分位估算。行轉(zhuǎn)列與列轉(zhuǎn)行場景把一條記錄展開成多行或者把多行數(shù)據(jù)合并成一個數(shù)組支撐寬表開發(fā)和特征加工。窗口分析場景排行、累計、同比環(huán)比、相鄰行對比這是數(shù)倉需求中占比很大的部分。我一直主張一個觀點看一個 Hive 開發(fā)者水平怎么樣不要看他會不會寫復(fù)雜的 join而是看他能不能把函數(shù)用得恰到好處。因為 join 的邏輯是固定的套路而函數(shù)的選擇往往決定了一個任務(wù)的性能上限和代碼的可維護性。1.2 Hive 函數(shù)的家族譜系從內(nèi)置函數(shù)到自定義函數(shù)Hive 函數(shù)大體上可以分成兩條主線內(nèi)置函數(shù)和自定義函數(shù)。內(nèi)置函數(shù)是 Hive 開箱即用的能力自定義函數(shù)則是當內(nèi)置函數(shù)滿足不了業(yè)務(wù)需求時你用 Java 或者其他語言自己擴展的實現(xiàn)。內(nèi)置函數(shù)按用途又可以細分成幾類我通常這樣歸類函數(shù)類別典型函數(shù)主要用途數(shù)值函數(shù)round, floor, ceil, abs, rand數(shù)值計算、取整、隨機抽樣字符串函數(shù)concat, substring, split, regexp_replace字符串加工、正則處理、字段拆分日期函數(shù)from_unixtime, unix_timestamp, datediff, date_add時間戳轉(zhuǎn)換、日期偏移、間隔計算條件函數(shù)if, case when, nvl, coalesce, nullif邏輯分支、空值兜底聚合函數(shù)count, sum, avg, collect_list, collect_set分組統(tǒng)計、列轉(zhuǎn)行窗口函數(shù)row_number, rank, lag, lead, sum over排行、同比環(huán)比、累計計算特殊函數(shù)explode, posexplode, lateral view行轉(zhuǎn)列、JSON 展開而自定義函數(shù)這邊按輸入輸出的關(guān)系分成三類。UDF 是一進一出比如給你一個字符串返回處理后的字符串UDAF 是多進一出做自定義聚合UDTF 是一進多出把一行展開成多行。這三類我在后面的第 4 節(jié)會詳細拆。2. 最常用的內(nèi)置函數(shù)數(shù)值、字符串與日期實操內(nèi)置函數(shù)雖然多但說句實話日常開發(fā)中真正高頻使用的也就那三四十個。我在多個項目里統(tǒng)計過開發(fā)人員寫的 Hive SQL 中超過八成的函數(shù)調(diào)用集中在字符串處理、日期轉(zhuǎn)換和條件判斷這三類。所以先把這批函數(shù)玩熟比死記硬背完整的函數(shù)列表有用得多。2.1 數(shù)值處理與字符串函數(shù)的使用細節(jié)數(shù)值函數(shù)里常用的無非就是round做四舍五入、floor向下取整、ceil向上取整、abs求絕對值以及rand做隨機抽樣。這里要說一個細節(jié)round在 Hive 里的行為是“四舍五入”但它的實現(xiàn)方式和很多編程語言里的有點差異。Hive 的round是基于Math.round實現(xiàn)的對于負數(shù)它遵循的是“四舍五入到最接近的整數(shù)”的規(guī)則。比如round(-2.5)的結(jié)果是 -3而不是 -2。這在做金額分攤或者比率計算的時候如果沒留意就會產(chǎn)生 1 分錢的誤差。別小看這個誤差對過賬系統(tǒng)來說1 分錢的對不上都會被當成數(shù)據(jù)事故。字符串函數(shù)這塊concat和concat_ws是出場率最高的兩個。很多人習(xí)慣用concat拼字段但一旦要拼的某個字段是 NULL結(jié)果就會變成 NULL整個字段就廢了。concat_ws就好用在這它會跳過 NULL 值而且支持指定分隔符。我寫過一句很典型的 SQLconcat_ws(-, year, month, day)用來拼分區(qū)字段這樣即使某一天的數(shù)據(jù)缺失也不會把整個分區(qū)路徑弄壞。split用來做字符串切分返回結(jié)果是數(shù)組。這里要注意的是Hive 的split第二個參數(shù)是正則表達式不是普通字符串。如果你要按點號切分 IP 地址直接寫split(ip, .)是切不動的因為點號在正則里是通配符必須寫成split(ip, \\.)。類似的還有按豎線切分要寫成split(str, \\|)。這個坑我見過不少同學(xué)踩過排查半天發(fā)現(xiàn)是正則的問題。regexp_replace和regexp_extract是正則處理里的黃金搭檔。前者是做替換后者是提取子串。regexp_extract提取的時候第二個參數(shù)里用括號圈出你要的部分第三個參數(shù)指定取第幾個括號里的內(nèi)容。比如從 URL 里提取 user_idregexp_extract(url, user_id(\\d), 1)。這里第三個參數(shù)寫 1 就表示取第一個括號匹配到的內(nèi)容。另外要留意正則的貪婪匹配問題有時候你會提取到超出預(yù)期的內(nèi)容。如果遇到這種情況可以在正則后面加問號改成非貪婪模式。JSON 解析函數(shù)在日志分析場景里是剛需。get_json_object可以從 JSON 字符串里取指定字段比如get_json_object(json_str, $.username)。但get_json_object的效率并不好尤其是對一個大 JSON 反復(fù)取多個字段時會執(zhí)行多次解析。這里更推薦的做法是用一次json_tuple配合lateral view把多個字段一次性拆出來解析只做一遍性能會好很多。2.2 日期函數(shù)與類型轉(zhuǎn)換的實戰(zhàn)要點日期函數(shù)這塊核心就圍繞三類操作時間戳與字符串互轉(zhuǎn)、日期加減、日期差計算。時間戳轉(zhuǎn)字符串用from_unixtime(unix_timestamp, pattern)比如from_unixtime(ts, yyyy-MM-dd HH:mm:ss)。反過來字符串轉(zhuǎn)時間戳用unix_timestamp(2024-06-01 12:00:00, yyyy-MM-dd HH:mm:ss)。這里的重點是格式模式里的字母區(qū)分大小寫yyyy表示四位的年份YYYY在 Hive 里有時候代表的是“周所在的年份”在跨年的時候可能和yyyy結(jié)果不一致。我建議統(tǒng)一用yyyy避免年底年初這種邊界時段出現(xiàn)意料之外的數(shù)據(jù)。日期加減是date_add和date_sub支持傳入負數(shù)實現(xiàn)相反方向的操作。比如想拿到 30 天前的日期可以寫date_sub(current_date(), 30)也可以寫date_add(current_date(), -30)。我個人更推薦用date_sub表達往前推的語義代碼讀起來直觀。求日期差用datediff(end_date, start_date)返回值是前者減后者的天數(shù)。這里有一個容易搞錯的地方datediff是按天計算的會被取整如果兩邊帶時分秒差值會被截斷不會四舍五入。所以你如果想要精確到小時的間隔不要用datediff直接用兩個時間戳相減再除以 3600 秒。類型轉(zhuǎn)換函數(shù)里cast是通用的轉(zhuǎn)換入口cast(x as int)、cast(x as decimal(10,2))。但cast有個非?？拥男袨檗D(zhuǎn)換失敗時不會報錯而是返回 NULL。比如cast(abc as int)的結(jié)果是 NULL如果你的下游邏輯沒有針對 NULL 做處理數(shù)據(jù)就悄悄丟了。所以我在寫類型轉(zhuǎn)換時幾乎都會配合case when先做合法性校驗。額外提一個函數(shù)try_cast這個不是 Hive 原生的是某些引擎擴展的功能是轉(zhuǎn)換失敗返回 NULL 而不是拋異常。如果你所在的環(huán)境支持可以放心用不支持就老實寫校驗邏輯。3. 聚合函數(shù)與窗口函數(shù)數(shù)倉開發(fā)的重頭戲聚合函數(shù)和窗口函數(shù)是 Hive 函數(shù)體系里最有分析深度的一塊。很多取數(shù)需求表面上看起來復(fù)雜拆解到最后基本都是聚合加窗口的組合拳。把這兩類函數(shù)吃透復(fù)雜報表開發(fā)起來會順手很多。3.1 聚合函數(shù)的行為細節(jié)與常見用法聚合函數(shù)的輸入是多行數(shù)據(jù)輸出是一行結(jié)果。count、sum、avg、max、min這五個是最基礎(chǔ)的。先說count的細節(jié)count(*)統(tǒng)計的是行數(shù)包括某列為 NULL 的行count(column)統(tǒng)計的則是該列非 NULL 的值的個數(shù)。很多新手在這里栽過跟頭統(tǒng)計用戶量的時候用count(user_id)如果 user_id 存在 NULL結(jié)果就會偏少。而count(distinct column)是去重計數(shù)這個好用但在數(shù)據(jù)量很大的時候要特別小心因為count(distinct)的底層實現(xiàn)容易引發(fā)數(shù)據(jù)傾斜。聚合函數(shù)里有兩個在數(shù)倉開發(fā)中出場率很高但很多人用不好的函數(shù)collect_list和collect_set。它們的作用是把分組內(nèi)的多行數(shù)據(jù)合并成一個數(shù)組前者保留重復(fù)元素后者自動去重。典型場景是行轉(zhuǎn)列比如把某個用戶的所有訂單號聚合成一個數(shù)組再配合concat_ws拼成字符串。但要注意collect_list得到的數(shù)組元素順序是不保證的如果你想控制順序先在子查詢里排好序再在外面聚合。雖然官方?jīng)]有嚴格保證這種方式必然生效但實際操作中大部分版本是可行的也算是一種廣為流傳的土辦法。avg在 Hive 里的返回值是 double 類型如果你需要精確的小數(shù)位控制建議先轉(zhuǎn)成 decimal 再求平均比如avg(cast(amount as decimal(10, 4)))。另外聚合函數(shù)處理 NULL 的規(guī)則是“忽略 NULL”比如sum遇到一組全是 NULL 的數(shù)據(jù)返回的是 NULL 而不是 0。這個和很多人的直覺相反所以在做指標計算時我習(xí)慣用coalesce(sum(x), 0)把 NULL 兜底成 0避免下游報表出現(xiàn)空值。百分位估算函數(shù)percentile和percentile_approx也值得單獨說。前者要求輸入是整數(shù)列后者可以接受浮點數(shù)列并且可以傳入一個精度參數(shù)控制估算質(zhì)量。在計算 UV 分布、耗時中位數(shù)這類指標時非常有用而且底層做了近似算法不會像count(distinct)那樣產(chǎn)生嚴重的傾斜問題。3.2 窗口函數(shù)排序、偏移與累計計算的完整拆解窗口函數(shù)的計算方式和普通聚合函數(shù)最大的區(qū)別在于聚合函數(shù)把多行結(jié)果壓縮成一行窗口函數(shù)保留每一行同時附加一個聚合結(jié)果在旁邊。這個特性讓窗口函數(shù)成了分析場景里的主力工具。排序相關(guān)的窗口函數(shù)有三個row_number、rank、dense_rank。它們的區(qū)別我在項目里驗證過很多次row_number不管并列情況給一個連續(xù)且唯一的編號rank遇到并列時會跳號dense_rank不跳號。舉個實際場景取每個用戶最近一次訂單直接用row_number() over(partition by user_id order by order_time desc)就能定位而做榜單排名希望并列名次占用相同位次就用rank。偏移函數(shù)lag和lead是做環(huán)比、同比分析的神器。lag(column, offset, default)取當前行往前數(shù)第 offset 行的值lead則往后取。比如計算日活環(huán)比增長可以先按日期排序再用lag(dau, 1)取前一行的日活值然后直接算增長率。需要注意lag在最前面幾行是沒有值可取的如果不傳第三個默認值參數(shù)返回的是 NULL下游要做過濾或兜底。累計計算是另一種高頻用法。sum(amount) over(partition by user_id order by order_time rows between unbounded preceding and current row)就是非常典型的累計求和的寫法。這里的關(guān)鍵是窗口子句rows between ... and ...它定義了累加的范圍。還有rows between 1 preceding and current row表示統(tǒng)計當前行和上一行兩行的合計常用于滑動計算。如果你不加窗口子句默認窗口是從分區(qū)起點到當前行也就是自然形成累計效果。這里要提醒一個很多人忽略的坑在使用排序相關(guān)的窗口函數(shù)時如果order by的字段存在大量重復(fù)值不同函數(shù)的執(zhí)行行為會產(chǎn)生差異。例如sum() over(order by x)在 x 重復(fù)時累計值會把所有相同 x 的行一起算進去而不是逐行累加。這個行為在不同引擎里可能不一致遇到需求明確要逐行累計的場景建議在 order by 后面加上唯一字段做次級排序。3.3 窗口函數(shù)與表生成函數(shù)的協(xié)同實戰(zhàn)窗口函數(shù)偶爾需要配合表生成函數(shù)使用典型場景是展開 JSON 數(shù)組后再對展開后的行做編號或排名。表生成函數(shù)的核心是explode它可以把一個數(shù)組或者 map 展開成多行。比如埋點日志里某個字段存的是商品 id 列表用explode就能把一條日志展開成多條商品記錄。但在 Hive 舊版本里explode不能直接在 select 語句里和其他字段一起查詢必須配合lateral view。lateral view的字面意思就是“側(cè)視圖”它把 explode 產(chǎn)生的虛擬表和原始行關(guān)聯(lián)起來。一個典型寫法是select user_id, order_id from orders lateral view explode(split(product_list, ,)) t as order_id;這種寫法把product_list字符串切成數(shù)組再展開成多行每一行都保留原來的user_id。posexplode是explode的增強版它會額外帶一個下標列可以用于記錄展開后元素的原始下標這在還原數(shù)組順序時很有用比如拼接的商品順序不能亂。在做用戶標簽場景時我經(jīng)常把lateral view explode和窗口函數(shù)放在一起用。先把每個用戶的標簽數(shù)組展開再用row_number給每個標簽做排序之后根據(jù)標簽優(yōu)先級取第一條整個處理鏈路非常流暢。4. 自定義函數(shù)系統(tǒng)函數(shù)不夠用時的突圍方案內(nèi)置函數(shù)再豐富也總有覆蓋不到的業(yè)務(wù)邏輯。比如解析一套內(nèi)部自定義的加密協(xié)議字段、計算兩個地理位置的距離、實現(xiàn)一套特殊的分組邏輯這些時候就需要自己寫函數(shù)擴展 Hive 的能力。4.1 UDF、UDAF、UDTF 的選型與核心原理自定義函數(shù)按輸入輸出關(guān)系分成三類UDF輸入一行輸出一行。這是最常用、最容易上手的一類。典型場景是字符串加工、字段解析、格式標準化。UDAF輸入多行輸出一行本質(zhì)是自定義聚合函數(shù)。典型場景是實現(xiàn)自定義統(tǒng)計指標比如加權(quán)平均、分組去重拼接。UDTF輸入一行輸出多行。典型場景是把一行數(shù)據(jù)展開成多行類似內(nèi)置的explode。選型的原則其實很簡單你在 SQL 里要替換的是一個普通字段轉(zhuǎn)換就用 UDF要替換的是group by里的聚合邏輯就考慮 UDAF要替換的是explode這種行轉(zhuǎn)列操作就寫 UDTF。UDF 的實現(xiàn)底層依賴 Hive 的反射機制。你在 SQL 里寫my_func(x)Hive 會去找到對應(yīng)的 Java 類實例化后把每一行的數(shù)據(jù)傳進去調(diào)用你實現(xiàn)的evaluate方法。這里的核心是同一個 UDF 實例會被多個計算節(jié)點并發(fā)使用所以你的實現(xiàn)里不能有可變的狀態(tài)變量。我見過有人把計數(shù)器寫在 UDF 的成員變量里以為能統(tǒng)計處理行數(shù)結(jié)果數(shù)據(jù)完全對不上原因就是每個節(jié)點各自維護了獨立的實例。4.2 一個 UDF 從開發(fā)到上線的完整流程下面用一個實際案例走一遍全流程。假設(shè)業(yè)務(wù)方要求從訂單號中解析出訂單來源渠道訂單號的規(guī)則是“渠道編碼 日期 序列號”拼接而成比如A01_20240601_000123A01 就是渠道編碼。內(nèi)置函數(shù)當然也能用split來解析但這類邏輯如果散落在幾十個 SQL 里維護成本會很高封裝成 UDF 更合理。第一步創(chuàng)建 Maven 工程引入 Hive 依賴。dependency groupIdorg.apache.hive/groupId artifactIdhive-exec/artifactId version3.1.2/version scopeprovided/scope /dependency第二步寫 UDF 實現(xiàn)類。繼承org.apache.hadoop.hive.ql.exec.UDF實現(xiàn)evaluate方法。import org.apache.hadoop.hive.ql.exec.UDF; public class OrderChannelParser extends UDF { public String evaluate(String orderId) { if (orderId null || orderId.isEmpty()) { return null; } String[] parts orderId.split(_); return parts.length 0 ? parts[0] : null; } }evaluate方法可以重載多個版本Hive 會根據(jù)調(diào)用時傳入的參數(shù)類型自動選擇。所以同一個 UDF 類可以實現(xiàn)支持多種輸入類型比如既有String evaluate(String)又有String evaluate(String, String)調(diào)用時的不同參數(shù)會路由到不同方法。第三步將項目打成 jar 包上傳到 Hive 能訪問到的目錄然后注冊函數(shù)。add jar hdfs://your-path/order-parser.jar; create temporary function parse_order_channel as com.example.OrderChannelParser;這里temporary關(guān)鍵字表示只在當前會話生效適合開發(fā)和測試。如果你要把函數(shù)做成永久能力可以執(zhí)行create function parse_order_channel as com.example.OrderChannelParser using jar hdfs://your-path/order-parser.jar;第四步在 SQL 里調(diào)用驗證結(jié)果。select parse_order_channel(order_id) as channel, count(*) as cnt from orders group by parse_order_channel(order_id);整個流程跑通之后業(yè)務(wù)方后續(xù)所有解析渠道的需求都只需要調(diào)用這一個函數(shù)代碼復(fù)用性大幅提升而且排查問題時只需要看一個類不用翻十幾個 SQL。4.3 UDAF 與 UDTF 的實現(xiàn)要點與注意事項UDAF 的實現(xiàn)會比 UDF 復(fù)雜一個量級。在 Hive 2.0 以上的版本里推薦使用org.apache.hadoop.hive.ql.udf.generic.AbstractGenericUDAFResolver配合GenericUDAFEvaluator的實現(xiàn)方式。核心邏輯是定義一個內(nèi)部狀態(tài)AggregationBuffer在iterate方法里接收每一行數(shù)據(jù)更新狀態(tài)在terminatePartial里輸出部分聚合結(jié)果最后在terminate里輸出最終結(jié)果。這里的關(guān)鍵是要理解 Hive 的聚合計算模型數(shù)據(jù)分散在多個節(jié)點上先做局部聚合再做全局聚合。所以你的聚合狀態(tài)必須支持合并這對應(yīng)merge方法。我見過不少 UDAF 實現(xiàn)錯誤把全局聚合和局部聚合搞混導(dǎo)致結(jié)果翻倍或者丟失。UDTF 的實現(xiàn)則是繼承GenericUDTF實現(xiàn)initialize、process和close三個方法。initialize里定義輸出列名和類型process里接收一行數(shù)據(jù)可以多次調(diào)用forward輸出多行。寫 UDTF 時有一個限制需要留意Hive 不允許 UDTF 的輸入?yún)?shù)和輸出列在同一張表里出現(xiàn)重名否則會報錯如果需要保留原字段必須配合lateral view使用。自定義函數(shù)上線前務(wù)必做三個層面的驗證單測驗證結(jié)果是必要條件造幾組邊界數(shù)據(jù)NULL、空串、超長字符串、特殊字符確認不會拋異常其次用一個小分區(qū)跑真實數(shù)據(jù)對比你預(yù)期的結(jié)果分布最后壓測確認執(zhí)行耗時不會拖垮整個任務(wù)。尤其是 UDF 里做了重量級操作比如在每一行都新建一個正則 Pattern 對象那在大表上就是災(zāi)難Pattern 對象應(yīng)該在構(gòu)造時初始化一次。5. 函數(shù)使用中的常見坑與排查實戰(zhàn)不管內(nèi)置函數(shù)還是自定義函數(shù)實際跑數(shù)時總會遇到各種問題。這一節(jié)我把自己在多個項目里踩過的坑集中整理一下。5.1 性能問題數(shù)據(jù)傾斜與函數(shù)副作用Hive 任務(wù)里最典型的性能問題就是數(shù)據(jù)傾斜。表現(xiàn)的樣貌是整個任務(wù)卡在某個 reduce 上其他都跑完了就它一個在那慢慢磨。函數(shù)觸發(fā)的傾斜有幾個常見原因。第一個是count(distinct)在超大基數(shù)場景下的歸并問題。比如統(tǒng)計上億用戶的去重設(shè)備數(shù)count(distinct device_id)會把所有設(shè)備的去重壓力打到同一個 reduce 上。解決思路一般是改成先group by device_id去重再外層count(1)。這個優(yōu)化的本質(zhì)是把壓力分散到多個 reduce 上內(nèi)核是兩階段聚合。第二個是 UDF 里做了耗時操作比如正則回溯、解析超大 JSON、循環(huán)里做字符串拼接。這類問題在數(shù)據(jù)量小的時候完全無感一旦遷移到百億級大表執(zhí)行時間就會被無限放大。我在項目里優(yōu)化過一個案例問題根源就是 UDF 的evaluate方法里調(diào)用了Pattern.compile做正則預(yù)處理后來改成在類初始化時把Pattern編譯緩存好任務(wù)耗時直接從 3 小時降到 20 分鐘。第三個是collect_list在大分組下的 OOM 風險。如果一個分組內(nèi)的數(shù)據(jù)量巨大collect 出來的數(shù)組會占用大量內(nèi)存。一個比較務(wù)實的策略是先對數(shù)據(jù)做抽樣評估如果某個 key 的數(shù)據(jù)量確實異常大就需要考慮是否真的需要全量 collect還是可以先做一層預(yù)過濾。5.2 數(shù)據(jù)正確性問題NULL、精度、時區(qū)與隱式轉(zhuǎn)換這類問題隱蔽性很強往往數(shù)據(jù)跑完了才發(fā)現(xiàn)不對重跑成本極高。NULL 值問題在前面反復(fù)出現(xiàn)這里再匯總一下。concat遇 NULL 返回 NULL、count(column)不統(tǒng)計 NULL、sum全 NULL 返回 NULL、concat_ws跳過 NULL、coalesce返回第一個非 NULL 值。我的習(xí)慣是數(shù)據(jù)清洗階段盡量把 NULL 處理掉統(tǒng)一轉(zhuǎn)成確定的默認值或者空串后續(xù)的分析查詢就省心很多。精度問題是金額類指標的重災(zāi)區(qū)。Hive 的 double 類型在sum計算時會產(chǎn)生精度丟失尤其是對金額做累加結(jié)果可能出現(xiàn) 0.1 的誤差。我在處理財務(wù)指標時一律要求上游把金額字段轉(zhuǎn)成decimal(18, 2)或者精度更高的類型再參與后續(xù)聚合。round的負數(shù)舍入問題前面已經(jīng)提過同樣值得注意。時區(qū)問題在日期處理函數(shù)里很常見表現(xiàn)形式是取數(shù)結(jié)果和預(yù)期相差 8 個小時。原因在于from_unixtime默認使用服務(wù)器的本地時區(qū)來格式化時間戳如果你的集群時區(qū)配置和業(yè)務(wù)方預(yù)期不一致就會出現(xiàn)偏移。排查這類問題時先確認集群的hive.execution.timezone配置再確認時間戳字段本身是否是 UTC 時間。最穩(wěn)妥的做法是統(tǒng)一約定所有時間戳存儲都用 UTC展示時顯式指定時區(qū)轉(zhuǎn)換。隱式轉(zhuǎn)換是另一個容易靜默出錯的點。比如一個字段被定義成字符串類型但你拿它和整數(shù)比較Hive 會做隱式轉(zhuǎn)換如果那個字段的內(nèi)容不是純數(shù)字比較結(jié)果就會偏離預(yù)期。我建議在表結(jié)構(gòu)定義階段就嚴格要求字段類型不要在 SQL 里依賴隱式轉(zhuǎn)換。舉一個典型的錯誤把分區(qū)字段dt存成 string然后用dt 20240601 and dt 20240630這種不帶引號的寫法去查看起來能跑通但如果有一天分區(qū)字段里出現(xiàn)非數(shù)字內(nèi)容這種行為就變得不可預(yù)測了。5.3 函數(shù)相關(guān)常見問題速查表我整理了一張日常排查速查表出現(xiàn)問題可以先對照排查?，F(xiàn)象可能原因解決方向split按點切分無效點號是正則通配符寫成split(ip, \\.)concat結(jié)果為 NULL某字段為 NULL改用concat_ws或nvl兜底count(distinct)任務(wù)卡死去重基數(shù)過大導(dǎo)致傾斜改成 group by count 兩階段日期結(jié)果差 8 小時時區(qū)配置不一致檢查集群時區(qū)統(tǒng)一 UTC 存儲cast(abc as int)無報錯Hive 轉(zhuǎn)換失敗返回 NULL配合 case when 做校驗金額求和結(jié)果有誤差double 精度丟失字段轉(zhuǎn) decimal 再聚合collect_list內(nèi)存溢出單分組數(shù)據(jù)過大預(yù)過濾、增大容器內(nèi)存或改方案UDF 任務(wù)整體很慢evaluate 里有重操作初始化時緩存 Pattern避免重復(fù)編譯row_number排名的結(jié)果和預(yù)期不同order by 字段重復(fù)增加唯一字段做次級排序datediff結(jié)果被取整按天計算截斷時分秒用時間戳相減精確計算這個表是我在幾個項目迭代中不斷沉淀下來的后續(xù)遇到新的問題還在持續(xù)補充。排查思路的本質(zhì)就一條先確認函數(shù)本身的行為再確認數(shù)據(jù)本身的特征。兩者分開排查問題定位就快很多。6. 從實際項目里沉淀的 Hive 函數(shù)使用經(jīng)驗寫到最后這部分分享一下我在實際項目中總結(jié)的一些使用經(jīng)驗。這里面沒有特別高深的技術(shù)都是實打?qū)嵦嵘_發(fā)和維護效率的做法。6.1 函數(shù)使用的基本原則第一條原則是能用內(nèi)置函數(shù)解決的就不要寫自定義函數(shù)。內(nèi)置函數(shù)經(jīng)過大量場景驗證穩(wěn)定性、性能都有保障。自定義函數(shù)的維護成本是隱性的后面換人接手要花時間理解函數(shù)邏輯一旦函數(shù)里有隱蔽的邊界 bug排查代價很大。第二條原則是邏輯盡量后置到 SQL 層而不是前置在 UDF 里。比如字符串標準化、金額單位轉(zhuǎn)換這類邏輯如果寫到 UDF 里后續(xù)調(diào)整要改代碼重新打包但如果寫在 SQL 里一個字段改一下重跑就行。我見過一個項目把匯率轉(zhuǎn)換寫在 UDF 里結(jié)果匯率基準更新時整個 UDF 要重新上線非常折騰。第三條原則是函數(shù)調(diào)用的可讀性要優(yōu)先。寫 SQL 不要炫技能用簡單的case when說清楚就不要寫一個嵌套三層的regexp_extract或者復(fù)雜的窗口函數(shù)表達式。數(shù)據(jù)開發(fā)最大的成本是別人讀你代碼的成本代碼寫得直白后面維護的人會感謝你。6.2 高價值函數(shù)組合與數(shù)據(jù)加工套路項目里有一種高頻套路清洗階段用case when加regexp_extract做字段規(guī)范化標準化階段用from_unixtime和date_format做時間規(guī)整加工階段用窗口函數(shù)做指標加工。這套組合打下來覆蓋了從明細數(shù)據(jù)到指標寬表的絕大多數(shù)需求。有一個典型的寬表加工例子統(tǒng)計每個用戶最近 7 天的下單金額和下單次數(shù)同時要算出和上一個自然周的同環(huán)比。實現(xiàn)思路是先用date_format(order_time, yyyy-ww)提取周維度再用datediff計算日期差值然后配合條件聚合把最近 7 天的數(shù)據(jù)框出來。這些函數(shù)單看都很簡單但組合起來就能解決很復(fù)雜的業(yè)務(wù)訴求。還有一個經(jīng)驗值得分享在寫窗口函數(shù)做指標加工時盡量把partition by的粒度控制在最小業(yè)務(wù)粒度。比如統(tǒng)計用戶的每日指標時partition by user_id, order_date一定比partition by user_id然后靠order by來限定更可控因為前者能清晰地表達“每組只算一天的數(shù)據(jù)”排查時思路更明確。6.3 最后再分享一個我一直在用的小技巧在寫任何一個帶聚合或窗口的 SQL 之前先在腦子里過一遍數(shù)據(jù)特征這個字段有沒有 NULL、這個維度有沒有可能產(chǎn)生大量的重復(fù)值、這個時間字段的時區(qū)是什么。這些問題過完之后再動手寫 SQL踩坑概率會下降一大半。很多看起來莫名其妙的數(shù)據(jù)異常本質(zhì)都是寫 SQL 之前沒有想清楚數(shù)據(jù)的分布特征。函數(shù)本身沒有錯錯的是你假設(shè)數(shù)據(jù)是干凈有序的。另外一個習(xí)慣是在函數(shù)選型時主動考慮表的數(shù)據(jù)量和文件格式。在 Parquet 這類列式存儲上某些函數(shù)在過濾條件下可以做到延遲物化整體執(zhí)行效率比在 Text 格式上高很多。因此做方案設(shè)計時函數(shù)選型和存儲格式需要通盤考慮不能只盯著一句 SQL 看。做 Hive 開發(fā)這么多年我的體會一直是函數(shù)只是工具真正拉開差距的是你對自己數(shù)據(jù)的理解程度。函數(shù)學(xué)得快慢不重要重要的是每一次使用函數(shù)時都能清楚知道它在這個數(shù)據(jù)集上會發(fā)生什么。這個意識建立起來之后你寫的每一段 SQL 都會比之前穩(wěn)得多。 Hive函數(shù)從入門到實戰(zhàn)：內(nèi)置函數(shù)、窗口函數(shù)與UDF自定義函數(shù)完整解析
1. 先搞清楚 Hive 函數(shù)到底是怎么回事做了這么多年數(shù)倉開發(fā)我發(fā)現(xiàn)很多人在接觸 Hive 函數(shù)時習(xí)慣性地把它當成 SQL 函數(shù)的一個“方言版本”。這種理解不能說錯但會埋下不少坑。Hive 函數(shù)真正特殊的地方在于它運行在分布式計算引擎上處理的是“一批數(shù)據(jù)”而不是“一行數(shù)據(jù)”這就讓它的執(zhí)行邏輯、使用邊界和優(yōu)化手段都和傳統(tǒng)關(guān)系型數(shù)據(jù)庫里的函數(shù)有很大區(qū)別。Hive 函數(shù)解決的核心問題說白了就是在海量數(shù)據(jù)上完成清洗、轉(zhuǎn)換、聚合、分析這些動作而不用你去寫 MapReduce 程序。而且 Hive 函數(shù)體系本身也足夠龐大從最簡單的字符串截取到復(fù)雜的自定義聚合基本覆蓋了離線數(shù)倉開發(fā)里九成以上的數(shù)據(jù)處理訴求。這篇文章會從我實際項目的角度出發(fā)把 Hive 函數(shù)從內(nèi)置到自定義從常用姿勢到避坑經(jīng)驗完整拆一遍希望能給正在做數(shù)倉開發(fā)、數(shù)據(jù)分析和 ETL 的同學(xué)一些實在的參考。有一點得先說透只記住某個函數(shù)的語法是沒用的如果你不清楚它的觸發(fā)時機、NULL 值處理邏輯、以及它在分布式環(huán)境下的執(zhí)行特性那上線之后出問題的概率會非常大。所以這篇文章里我不會只列函數(shù)清單更重要的是把“為什么這樣用”“在什么場景下踩過坑”講清楚。適合看這篇內(nèi)容的人我大致分了三類剛?cè)腴T的數(shù)據(jù)分析同學(xué)需要掌握核心函數(shù)來寫日常取數(shù)邏輯做數(shù)倉 ETL 的開發(fā)需要理解自定義函數(shù)和窗口函數(shù)的底層行為以及所有被數(shù)據(jù)傾斜、NULL 值異常、UDF 性能問題折磨過的同學(xué)。1.1 Hive 函數(shù)的本質(zhì)與核心使用場景Hive 函數(shù)的本質(zhì)可以理解為把復(fù)雜的數(shù)據(jù)處理邏輯封裝成一個可復(fù)用的操作單元并交給底層的計算引擎去分布式執(zhí)行。比如你要對一億條用戶行為日志做正則匹配提取 URL 中的關(guān)鍵參數(shù)如果自己寫 MapReduce要處理輸入分片、map 階段的逐行處理、輸出序列化等等工作量不小而用regexp_extract這個 Hive 函數(shù)一行 SQL 就搞定了。所以 Hive 函數(shù)的核心價值就是把通用的數(shù)據(jù)處理邏輯做成標準化操作讓你把精力集中在業(yè)務(wù)問題上。在實際的項目里Hive 函數(shù)主要覆蓋這么幾類場景數(shù)據(jù)清洗場景字段拼接、字符串分割、正則替換、JSON 解析、空值兜底這些是數(shù)據(jù)入倉后最常做的事。統(tǒng)計分析場景按照維度分組后做求和、均值、去重計數(shù)以及更復(fù)雜的百分位估算。行轉(zhuǎn)列與列轉(zhuǎn)行場景把一條記錄展開成多行或者把多行數(shù)據(jù)合并成一個數(shù)組支撐寬表開發(fā)和特征加工。窗口分析場景排行、累計、同比環(huán)比、相鄰行對比這是數(shù)倉需求中占比很大的部分。我一直主張一個觀點看一個 Hive 開發(fā)者水平怎么樣不要看他會不會寫復(fù)雜的 join而是看他能不能把函數(shù)用得恰到好處。因為 join 的邏輯是固定的套路而函數(shù)的選擇往往決定了一個任務(wù)的性能上限和代碼的可維護性。1.2 Hive 函數(shù)的家族譜系從內(nèi)置函數(shù)到自定義函數(shù)Hive 函數(shù)大體上可以分成兩條主線內(nèi)置函數(shù)和自定義函數(shù)。內(nèi)置函數(shù)是 Hive 開箱即用的能力自定義函數(shù)則是當內(nèi)置函數(shù)滿足不了業(yè)務(wù)需求時你用 Java 或者其他語言自己擴展的實現(xiàn)。內(nèi)置函數(shù)按用途又可以細分成幾類我通常這樣歸類函數(shù)類別典型函數(shù)主要用途數(shù)值函數(shù)round, floor, ceil, abs, rand數(shù)值計算、取整、隨機抽樣字符串函數(shù)concat, substring, split, regexp_replace字符串加工、正則處理、字段拆分日期函數(shù)from_unixtime, unix_timestamp, datediff, date_add時間戳轉(zhuǎn)換、日期偏移、間隔計算條件函數(shù)if, case when, nvl, coalesce, nullif邏輯分支、空值兜底聚合函數(shù)count, sum, avg, collect_list, collect_set分組統(tǒng)計、列轉(zhuǎn)行窗口函數(shù)row_number, rank, lag, lead, sum over排行、同比環(huán)比、累計計算特殊函數(shù)explode, posexplode, lateral view行轉(zhuǎn)列、JSON 展開而自定義函數(shù)這邊按輸入輸出的關(guān)系分成三類。UDF 是一進一出比如給你一個字符串返回處理后的字符串UDAF 是多進一出做自定義聚合UDTF 是一進多出把一行展開成多行。這三類我在后面的第 4 節(jié)會詳細拆。2. 最常用的內(nèi)置函數(shù)數(shù)值、字符串與日期實操內(nèi)置函數(shù)雖然多但說句實話日常開發(fā)中真正高頻使用的也就那三四十個。我在多個項目里統(tǒng)計過開發(fā)人員寫的 Hive SQL 中超過八成的函數(shù)調(diào)用集中在字符串處理、日期轉(zhuǎn)換和條件判斷這三類。所以先把這批函數(shù)玩熟比死記硬背完整的函數(shù)列表有用得多。2.1 數(shù)值處理與字符串函數(shù)的使用細節(jié)數(shù)值函數(shù)里常用的無非就是round做四舍五入、floor向下取整、ceil向上取整、abs求絕對值以及rand做隨機抽樣。這里要說一個細節(jié)round在 Hive 里的行為是“四舍五入”但它的實現(xiàn)方式和很多編程語言里的有點差異。Hive 的round是基于Math.round實現(xiàn)的對于負數(shù)它遵循的是“四舍五入到最接近的整數(shù)”的規(guī)則。比如round(-2.5)的結(jié)果是 -3而不是 -2。這在做金額分攤或者比率計算的時候如果沒留意就會產(chǎn)生 1 分錢的誤差。別小看這個誤差對過賬系統(tǒng)來說1 分錢的對不上都會被當成數(shù)據(jù)事故。字符串函數(shù)這塊concat和concat_ws是出場率最高的兩個。很多人習(xí)慣用concat拼字段但一旦要拼的某個字段是 NULL結(jié)果就會變成 NULL整個字段就廢了。concat_ws就好用在這它會跳過 NULL 值而且支持指定分隔符。我寫過一句很典型的 SQLconcat_ws(-, year, month, day)用來拼分區(qū)字段這樣即使某一天的數(shù)據(jù)缺失也不會把整個分區(qū)路徑弄壞。split用來做字符串切分返回結(jié)果是數(shù)組。這里要注意的是Hive 的split第二個參數(shù)是正則表達式不是普通字符串。如果你要按點號切分 IP 地址直接寫split(ip, .)是切不動的因為點號在正則里是通配符必須寫成split(ip, \\.)。類似的還有按豎線切分要寫成split(str, \\|)。這個坑我見過不少同學(xué)踩過排查半天發(fā)現(xiàn)是正則的問題。regexp_replace和regexp_extract是正則處理里的黃金搭檔。前者是做替換后者是提取子串。regexp_extract提取的時候第二個參數(shù)里用括號圈出你要的部分第三個參數(shù)指定取第幾個括號里的內(nèi)容。比如從 URL 里提取 user_idregexp_extract(url, user_id(\\d), 1)。這里第三個參數(shù)寫 1 就表示取第一個括號匹配到的內(nèi)容。另外要留意正則的貪婪匹配問題有時候你會提取到超出預(yù)期的內(nèi)容。如果遇到這種情況可以在正則后面加問號改成非貪婪模式。JSON 解析函數(shù)在日志分析場景里是剛需。get_json_object可以從 JSON 字符串里取指定字段比如get_json_object(json_str, $.username)。但get_json_object的效率并不好尤其是對一個大 JSON 反復(fù)取多個字段時會執(zhí)行多次解析。這里更推薦的做法是用一次json_tuple配合lateral view把多個字段一次性拆出來解析只做一遍性能會好很多。2.2 日期函數(shù)與類型轉(zhuǎn)換的實戰(zhàn)要點日期函數(shù)這塊核心就圍繞三類操作時間戳與字符串互轉(zhuǎn)、日期加減、日期差計算。時間戳轉(zhuǎn)字符串用from_unixtime(unix_timestamp, pattern)比如from_unixtime(ts, yyyy-MM-dd HH:mm:ss)。反過來字符串轉(zhuǎn)時間戳用unix_timestamp(2024-06-01 12:00:00, yyyy-MM-dd HH:mm:ss)。這里的重點是格式模式里的字母區(qū)分大小寫yyyy表示四位的年份YYYY在 Hive 里有時候代表的是“周所在的年份”在跨年的時候可能和yyyy結(jié)果不一致。我建議統(tǒng)一用yyyy避免年底年初這種邊界時段出現(xiàn)意料之外的數(shù)據(jù)。日期加減是date_add和date_sub支持傳入負數(shù)實現(xiàn)相反方向的操作。比如想拿到 30 天前的日期可以寫date_sub(current_date(), 30)也可以寫date_add(current_date(), -30)。我個人更推薦用date_sub表達往前推的語義代碼讀起來直觀。求日期差用datediff(end_date, start_date)返回值是前者減后者的天數(shù)。這里有一個容易搞錯的地方datediff是按天計算的會被取整如果兩邊帶時分秒差值會被截斷不會四舍五入。所以你如果想要精確到小時的間隔不要用datediff直接用兩個時間戳相減再除以 3600 秒。類型轉(zhuǎn)換函數(shù)里cast是通用的轉(zhuǎn)換入口cast(x as int)、cast(x as decimal(10,2))。但cast有個非?？拥男袨檗D(zhuǎn)換失敗時不會報錯而是返回 NULL。比如cast(abc as int)的結(jié)果是 NULL如果你的下游邏輯沒有針對 NULL 做處理數(shù)據(jù)就悄悄丟了。所以我在寫類型轉(zhuǎn)換時幾乎都會配合case when先做合法性校驗。額外提一個函數(shù)try_cast這個不是 Hive 原生的是某些引擎擴展的功能是轉(zhuǎn)換失敗返回 NULL 而不是拋異常。如果你所在的環(huán)境支持可以放心用不支持就老實寫校驗邏輯。3. 聚合函數(shù)與窗口函數(shù)數(shù)倉開發(fā)的重頭戲聚合函數(shù)和窗口函數(shù)是 Hive 函數(shù)體系里最有分析深度的一塊。很多取數(shù)需求表面上看起來復(fù)雜拆解到最后基本都是聚合加窗口的組合拳。把這兩類函數(shù)吃透復(fù)雜報表開發(fā)起來會順手很多。3.1 聚合函數(shù)的行為細節(jié)與常見用法聚合函數(shù)的輸入是多行數(shù)據(jù)輸出是一行結(jié)果。count、sum、avg、max、min這五個是最基礎(chǔ)的。先說count的細節(jié)count(*)統(tǒng)計的是行數(shù)包括某列為 NULL 的行count(column)統(tǒng)計的則是該列非 NULL 的值的個數(shù)。很多新手在這里栽過跟頭統(tǒng)計用戶量的時候用count(user_id)如果 user_id 存在 NULL結(jié)果就會偏少。而count(distinct column)是去重計數(shù)這個好用但在數(shù)據(jù)量很大的時候要特別小心因為count(distinct)的底層實現(xiàn)容易引發(fā)數(shù)據(jù)傾斜。聚合函數(shù)里有兩個在數(shù)倉開發(fā)中出場率很高但很多人用不好的函數(shù)collect_list和collect_set。它們的作用是把分組內(nèi)的多行數(shù)據(jù)合并成一個數(shù)組前者保留重復(fù)元素后者自動去重。典型場景是行轉(zhuǎn)列比如把某個用戶的所有訂單號聚合成一個數(shù)組再配合concat_ws拼成字符串。但要注意collect_list得到的數(shù)組元素順序是不保證的如果你想控制順序先在子查詢里排好序再在外面聚合。雖然官方?jīng)]有嚴格保證這種方式必然生效但實際操作中大部分版本是可行的也算是一種廣為流傳的土辦法。avg在 Hive 里的返回值是 double 類型如果你需要精確的小數(shù)位控制建議先轉(zhuǎn)成 decimal 再求平均比如avg(cast(amount as decimal(10, 4)))。另外聚合函數(shù)處理 NULL 的規(guī)則是“忽略 NULL”比如sum遇到一組全是 NULL 的數(shù)據(jù)返回的是 NULL 而不是 0。這個和很多人的直覺相反所以在做指標計算時我習(xí)慣用coalesce(sum(x), 0)把 NULL 兜底成 0避免下游報表出現(xiàn)空值。百分位估算函數(shù)percentile和percentile_approx也值得單獨說。前者要求輸入是整數(shù)列后者可以接受浮點數(shù)列并且可以傳入一個精度參數(shù)控制估算質(zhì)量。在計算 UV 分布、耗時中位數(shù)這類指標時非常有用而且底層做了近似算法不會像count(distinct)那樣產(chǎn)生嚴重的傾斜問題。3.2 窗口函數(shù)排序、偏移與累計計算的完整拆解窗口函數(shù)的計算方式和普通聚合函數(shù)最大的區(qū)別在于聚合函數(shù)把多行結(jié)果壓縮成一行窗口函數(shù)保留每一行同時附加一個聚合結(jié)果在旁邊。這個特性讓窗口函數(shù)成了分析場景里的主力工具。排序相關(guān)的窗口函數(shù)有三個row_number、rank、dense_rank。它們的區(qū)別我在項目里驗證過很多次row_number不管并列情況給一個連續(xù)且唯一的編號rank遇到并列時會跳號dense_rank不跳號。舉個實際場景取每個用戶最近一次訂單直接用row_number() over(partition by user_id order by order_time desc)就能定位而做榜單排名希望并列名次占用相同位次就用rank。偏移函數(shù)lag和lead是做環(huán)比、同比分析的神器。lag(column, offset, default)取當前行往前數(shù)第 offset 行的值lead則往后取。比如計算日活環(huán)比增長可以先按日期排序再用lag(dau, 1)取前一行的日活值然后直接算增長率。需要注意lag在最前面幾行是沒有值可取的如果不傳第三個默認值參數(shù)返回的是 NULL下游要做過濾或兜底。累計計算是另一種高頻用法。sum(amount) over(partition by user_id order by order_time rows between unbounded preceding and current row)就是非常典型的累計求和的寫法。這里的關(guān)鍵是窗口子句rows between ... and ...它定義了累加的范圍。還有rows between 1 preceding and current row表示統(tǒng)計當前行和上一行兩行的合計常用于滑動計算。如果你不加窗口子句默認窗口是從分區(qū)起點到當前行也就是自然形成累計效果。這里要提醒一個很多人忽略的坑在使用排序相關(guān)的窗口函數(shù)時如果order by的字段存在大量重復(fù)值不同函數(shù)的執(zhí)行行為會產(chǎn)生差異。例如sum() over(order by x)在 x 重復(fù)時累計值會把所有相同 x 的行一起算進去而不是逐行累加。這個行為在不同引擎里可能不一致遇到需求明確要逐行累計的場景建議在 order by 后面加上唯一字段做次級排序。3.3 窗口函數(shù)與表生成函數(shù)的協(xié)同實戰(zhàn)窗口函數(shù)偶爾需要配合表生成函數(shù)使用典型場景是展開 JSON 數(shù)組后再對展開后的行做編號或排名。表生成函數(shù)的核心是explode它可以把一個數(shù)組或者 map 展開成多行。比如埋點日志里某個字段存的是商品 id 列表用explode就能把一條日志展開成多條商品記錄。但在 Hive 舊版本里explode不能直接在 select 語句里和其他字段一起查詢必須配合lateral view。lateral view的字面意思就是“側(cè)視圖”它把 explode 產(chǎn)生的虛擬表和原始行關(guān)聯(lián)起來。一個典型寫法是select user_id, order_id from orders lateral view explode(split(product_list, ,)) t as order_id;這種寫法把product_list字符串切成數(shù)組再展開成多行每一行都保留原來的user_id。posexplode是explode的增強版它會額外帶一個下標列可以用于記錄展開后元素的原始下標這在還原數(shù)組順序時很有用比如拼接的商品順序不能亂。在做用戶標簽場景時我經(jīng)常把lateral view explode和窗口函數(shù)放在一起用。先把每個用戶的標簽數(shù)組展開再用row_number給每個標簽做排序之后根據(jù)標簽優(yōu)先級取第一條整個處理鏈路非常流暢。4. 自定義函數(shù)系統(tǒng)函數(shù)不夠用時的突圍方案內(nèi)置函數(shù)再豐富也總有覆蓋不到的業(yè)務(wù)邏輯。比如解析一套內(nèi)部自定義的加密協(xié)議字段、計算兩個地理位置的距離、實現(xiàn)一套特殊的分組邏輯這些時候就需要自己寫函數(shù)擴展 Hive 的能力。4.1 UDF、UDAF、UDTF 的選型與核心原理自定義函數(shù)按輸入輸出關(guān)系分成三類UDF輸入一行輸出一行。這是最常用、最容易上手的一類。典型場景是字符串加工、字段解析、格式標準化。UDAF輸入多行輸出一行本質(zhì)是自定義聚合函數(shù)。典型場景是實現(xiàn)自定義統(tǒng)計指標比如加權(quán)平均、分組去重拼接。UDTF輸入一行輸出多行。典型場景是把一行數(shù)據(jù)展開成多行類似內(nèi)置的explode。選型的原則其實很簡單你在 SQL 里要替換的是一個普通字段轉(zhuǎn)換就用 UDF要替換的是group by里的聚合邏輯就考慮 UDAF要替換的是explode這種行轉(zhuǎn)列操作就寫 UDTF。UDF 的實現(xiàn)底層依賴 Hive 的反射機制。你在 SQL 里寫my_func(x)Hive 會去找到對應(yīng)的 Java 類實例化后把每一行的數(shù)據(jù)傳進去調(diào)用你實現(xiàn)的evaluate方法。這里的核心是同一個 UDF 實例會被多個計算節(jié)點并發(fā)使用所以你的實現(xiàn)里不能有可變的狀態(tài)變量。我見過有人把計數(shù)器寫在 UDF 的成員變量里以為能統(tǒng)計處理行數(shù)結(jié)果數(shù)據(jù)完全對不上原因就是每個節(jié)點各自維護了獨立的實例。4.2 一個 UDF 從開發(fā)到上線的完整流程下面用一個實際案例走一遍全流程。假設(shè)業(yè)務(wù)方要求從訂單號中解析出訂單來源渠道訂單號的規(guī)則是“渠道編碼 日期 序列號”拼接而成比如A01_20240601_000123A01 就是渠道編碼。內(nèi)置函數(shù)當然也能用split來解析但這類邏輯如果散落在幾十個 SQL 里維護成本會很高封裝成 UDF 更合理。第一步創(chuàng)建 Maven 工程引入 Hive 依賴。dependency groupIdorg.apache.hive/groupId artifactIdhive-exec/artifactId version3.1.2/version scopeprovided/scope /dependency第二步寫 UDF 實現(xiàn)類。繼承org.apache.hadoop.hive.ql.exec.UDF實現(xiàn)evaluate方法。import org.apache.hadoop.hive.ql.exec.UDF; public class OrderChannelParser extends UDF { public String evaluate(String orderId) { if (orderId null || orderId.isEmpty()) { return null; } String[] parts orderId.split(_); return parts.length 0 ? parts[0] : null; } }evaluate方法可以重載多個版本Hive 會根據(jù)調(diào)用時傳入的參數(shù)類型自動選擇。所以同一個 UDF 類可以實現(xiàn)支持多種輸入類型比如既有String evaluate(String)又有String evaluate(String, String)調(diào)用時的不同參數(shù)會路由到不同方法。第三步將項目打成 jar 包上傳到 Hive 能訪問到的目錄然后注冊函數(shù)。add jar hdfs://your-path/order-parser.jar; create temporary function parse_order_channel as com.example.OrderChannelParser;這里temporary關(guān)鍵字表示只在當前會話生效適合開發(fā)和測試。如果你要把函數(shù)做成永久能力可以執(zhí)行create function parse_order_channel as com.example.OrderChannelParser using jar hdfs://your-path/order-parser.jar;第四步在 SQL 里調(diào)用驗證結(jié)果。select parse_order_channel(order_id) as channel, count(*) as cnt from orders group by parse_order_channel(order_id);整個流程跑通之后業(yè)務(wù)方后續(xù)所有解析渠道的需求都只需要調(diào)用這一個函數(shù)代碼復(fù)用性大幅提升而且排查問題時只需要看一個類不用翻十幾個 SQL。4.3 UDAF 與 UDTF 的實現(xiàn)要點與注意事項UDAF 的實現(xiàn)會比 UDF 復(fù)雜一個量級。在 Hive 2.0 以上的版本里推薦使用org.apache.hadoop.hive.ql.udf.generic.AbstractGenericUDAFResolver配合GenericUDAFEvaluator的實現(xiàn)方式。核心邏輯是定義一個內(nèi)部狀態(tài)AggregationBuffer在iterate方法里接收每一行數(shù)據(jù)更新狀態(tài)在terminatePartial里輸出部分聚合結(jié)果最后在terminate里輸出最終結(jié)果。這里的關(guān)鍵是要理解 Hive 的聚合計算模型數(shù)據(jù)分散在多個節(jié)點上先做局部聚合再做全局聚合。所以你的聚合狀態(tài)必須支持合并這對應(yīng)merge方法。我見過不少 UDAF 實現(xiàn)錯誤把全局聚合和局部聚合搞混導(dǎo)致結(jié)果翻倍或者丟失。UDTF 的實現(xiàn)則是繼承GenericUDTF實現(xiàn)initialize、process和close三個方法。initialize里定義輸出列名和類型process里接收一行數(shù)據(jù)可以多次調(diào)用forward輸出多行。寫 UDTF 時有一個限制需要留意Hive 不允許 UDTF 的輸入?yún)?shù)和輸出列在同一張表里出現(xiàn)重名否則會報錯如果需要保留原字段必須配合lateral view使用。自定義函數(shù)上線前務(wù)必做三個層面的驗證單測驗證結(jié)果是必要條件造幾組邊界數(shù)據(jù)NULL、空串、超長字符串、特殊字符確認不會拋異常其次用一個小分區(qū)跑真實數(shù)據(jù)對比你預(yù)期的結(jié)果分布最后壓測確認執(zhí)行耗時不會拖垮整個任務(wù)。尤其是 UDF 里做了重量級操作比如在每一行都新建一個正則 Pattern 對象那在大表上就是災(zāi)難Pattern 對象應(yīng)該在構(gòu)造時初始化一次。5. 函數(shù)使用中的常見坑與排查實戰(zhàn)不管內(nèi)置函數(shù)還是自定義函數(shù)實際跑數(shù)時總會遇到各種問題。這一節(jié)我把自己在多個項目里踩過的坑集中整理一下。5.1 性能問題數(shù)據(jù)傾斜與函數(shù)副作用Hive 任務(wù)里最典型的性能問題就是數(shù)據(jù)傾斜。表現(xiàn)的樣貌是整個任務(wù)卡在某個 reduce 上其他都跑完了就它一個在那慢慢磨。函數(shù)觸發(fā)的傾斜有幾個常見原因。第一個是count(distinct)在超大基數(shù)場景下的歸并問題。比如統(tǒng)計上億用戶的去重設(shè)備數(shù)count(distinct device_id)會把所有設(shè)備的去重壓力打到同一個 reduce 上。解決思路一般是改成先group by device_id去重再外層count(1)。這個優(yōu)化的本質(zhì)是把壓力分散到多個 reduce 上內(nèi)核是兩階段聚合。第二個是 UDF 里做了耗時操作比如正則回溯、解析超大 JSON、循環(huán)里做字符串拼接。這類問題在數(shù)據(jù)量小的時候完全無感一旦遷移到百億級大表執(zhí)行時間就會被無限放大。我在項目里優(yōu)化過一個案例問題根源就是 UDF 的evaluate方法里調(diào)用了Pattern.compile做正則預(yù)處理后來改成在類初始化時把Pattern編譯緩存好任務(wù)耗時直接從 3 小時降到 20 分鐘。第三個是collect_list在大分組下的 OOM 風險。如果一個分組內(nèi)的數(shù)據(jù)量巨大collect 出來的數(shù)組會占用大量內(nèi)存。一個比較務(wù)實的策略是先對數(shù)據(jù)做抽樣評估如果某個 key 的數(shù)據(jù)量確實異常大就需要考慮是否真的需要全量 collect還是可以先做一層預(yù)過濾。5.2 數(shù)據(jù)正確性問題NULL、精度、時區(qū)與隱式轉(zhuǎn)換這類問題隱蔽性很強往往數(shù)據(jù)跑完了才發(fā)現(xiàn)不對重跑成本極高。NULL 值問題在前面反復(fù)出現(xiàn)這里再匯總一下。concat遇 NULL 返回 NULL、count(column)不統(tǒng)計 NULL、sum全 NULL 返回 NULL、concat_ws跳過 NULL、coalesce返回第一個非 NULL 值。我的習(xí)慣是數(shù)據(jù)清洗階段盡量把 NULL 處理掉統(tǒng)一轉(zhuǎn)成確定的默認值或者空串后續(xù)的分析查詢就省心很多。精度問題是金額類指標的重災(zāi)區(qū)。Hive 的 double 類型在sum計算時會產(chǎn)生精度丟失尤其是對金額做累加結(jié)果可能出現(xiàn) 0.1 的誤差。我在處理財務(wù)指標時一律要求上游把金額字段轉(zhuǎn)成decimal(18, 2)或者精度更高的類型再參與后續(xù)聚合。round的負數(shù)舍入問題前面已經(jīng)提過同樣值得注意。時區(qū)問題在日期處理函數(shù)里很常見表現(xiàn)形式是取數(shù)結(jié)果和預(yù)期相差 8 個小時。原因在于from_unixtime默認使用服務(wù)器的本地時區(qū)來格式化時間戳如果你的集群時區(qū)配置和業(yè)務(wù)方預(yù)期不一致就會出現(xiàn)偏移。排查這類問題時先確認集群的hive.execution.timezone配置再確認時間戳字段本身是否是 UTC 時間。最穩(wěn)妥的做法是統(tǒng)一約定所有時間戳存儲都用 UTC展示時顯式指定時區(qū)轉(zhuǎn)換。隱式轉(zhuǎn)換是另一個容易靜默出錯的點。比如一個字段被定義成字符串類型但你拿它和整數(shù)比較Hive 會做隱式轉(zhuǎn)換如果那個字段的內(nèi)容不是純數(shù)字比較結(jié)果就會偏離預(yù)期。我建議在表結(jié)構(gòu)定義階段就嚴格要求字段類型不要在 SQL 里依賴隱式轉(zhuǎn)換。舉一個典型的錯誤把分區(qū)字段dt存成 string然后用dt 20240601 and dt 20240630這種不帶引號的寫法去查看起來能跑通但如果有一天分區(qū)字段里出現(xiàn)非數(shù)字內(nèi)容這種行為就變得不可預(yù)測了。5.3 函數(shù)相關(guān)常見問題速查表我整理了一張日常排查速查表出現(xiàn)問題可以先對照排查?，F(xiàn)象可能原因解決方向split按點切分無效點號是正則通配符寫成split(ip, \\.)concat結(jié)果為 NULL某字段為 NULL改用concat_ws或nvl兜底count(distinct)任務(wù)卡死去重基數(shù)過大導(dǎo)致傾斜改成 group by count 兩階段日期結(jié)果差 8 小時時區(qū)配置不一致檢查集群時區(qū)統(tǒng)一 UTC 存儲cast(abc as int)無報錯Hive 轉(zhuǎn)換失敗返回 NULL配合 case when 做校驗金額求和結(jié)果有誤差double 精度丟失字段轉(zhuǎn) decimal 再聚合collect_list內(nèi)存溢出單分組數(shù)據(jù)過大預(yù)過濾、增大容器內(nèi)存或改方案UDF 任務(wù)整體很慢evaluate 里有重操作初始化時緩存 Pattern避免重復(fù)編譯row_number排名的結(jié)果和預(yù)期不同order by 字段重復(fù)增加唯一字段做次級排序datediff結(jié)果被取整按天計算截斷時分秒用時間戳相減精確計算這個表是我在幾個項目迭代中不斷沉淀下來的后續(xù)遇到新的問題還在持續(xù)補充。排查思路的本質(zhì)就一條先確認函數(shù)本身的行為再確認數(shù)據(jù)本身的特征。兩者分開排查問題定位就快很多。6. 從實際項目里沉淀的 Hive 函數(shù)使用經(jīng)驗寫到最后這部分分享一下我在實際項目中總結(jié)的一些使用經(jīng)驗。這里面沒有特別高深的技術(shù)都是實打?qū)嵦嵘_發(fā)和維護效率的做法。6.1 函數(shù)使用的基本原則第一條原則是能用內(nèi)置函數(shù)解決的就不要寫自定義函數(shù)。內(nèi)置函數(shù)經(jīng)過大量場景驗證穩(wěn)定性、性能都有保障。自定義函數(shù)的維護成本是隱性的后面換人接手要花時間理解函數(shù)邏輯一旦函數(shù)里有隱蔽的邊界 bug排查代價很大。第二條原則是邏輯盡量后置到 SQL 層而不是前置在 UDF 里。比如字符串標準化、金額單位轉(zhuǎn)換這類邏輯如果寫到 UDF 里后續(xù)調(diào)整要改代碼重新打包但如果寫在 SQL 里一個字段改一下重跑就行。我見過一個項目把匯率轉(zhuǎn)換寫在 UDF 里結(jié)果匯率基準更新時整個 UDF 要重新上線非常折騰。第三條原則是函數(shù)調(diào)用的可讀性要優(yōu)先。寫 SQL 不要炫技能用簡單的case when說清楚就不要寫一個嵌套三層的regexp_extract或者復(fù)雜的窗口函數(shù)表達式。數(shù)據(jù)開發(fā)最大的成本是別人讀你代碼的成本代碼寫得直白后面維護的人會感謝你。6.2 高價值函數(shù)組合與數(shù)據(jù)加工套路項目里有一種高頻套路清洗階段用case when加regexp_extract做字段規(guī)范化標準化階段用from_unixtime和date_format做時間規(guī)整加工階段用窗口函數(shù)做指標加工。這套組合打下來覆蓋了從明細數(shù)據(jù)到指標寬表的絕大多數(shù)需求。有一個典型的寬表加工例子統(tǒng)計每個用戶最近 7 天的下單金額和下單次數(shù)同時要算出和上一個自然周的同環(huán)比。實現(xiàn)思路是先用date_format(order_time, yyyy-ww)提取周維度再用datediff計算日期差值然后配合條件聚合把最近 7 天的數(shù)據(jù)框出來。這些函數(shù)單看都很簡單但組合起來就能解決很復(fù)雜的業(yè)務(wù)訴求。還有一個經(jīng)驗值得分享在寫窗口函數(shù)做指標加工時盡量把partition by的粒度控制在最小業(yè)務(wù)粒度。比如統(tǒng)計用戶的每日指標時partition by user_id, order_date一定比partition by user_id然后靠order by來限定更可控因為前者能清晰地表達“每組只算一天的數(shù)據(jù)”排查時思路更明確。6.3 最后再分享一個我一直在用的小技巧在寫任何一個帶聚合或窗口的 SQL 之前先在腦子里過一遍數(shù)據(jù)特征這個字段有沒有 NULL、這個維度有沒有可能產(chǎn)生大量的重復(fù)值、這個時間字段的時區(qū)是什么。這些問題過完之后再動手寫 SQL踩坑概率會下降一大半。很多看起來莫名其妙的數(shù)據(jù)異常本質(zhì)都是寫 SQL 之前沒有想清楚數(shù)據(jù)的分布特征。函數(shù)本身沒有錯錯的是你假設(shè)數(shù)據(jù)是干凈有序的。另外一個習(xí)慣是在函數(shù)選型時主動考慮表的數(shù)據(jù)量和文件格式。在 Parquet 這類列式存儲上某些函數(shù)在過濾條件下可以做到延遲物化整體執(zhí)行效率比在 Text 格式上高很多。因此做方案設(shè)計時函數(shù)選型和存儲格式需要通盤考慮不能只盯著一句 SQL 看。做 Hive 開發(fā)這么多年我的體會一直是函數(shù)只是工具真正拉開差距的是你對自己數(shù)據(jù)的理解程度。函數(shù)學(xué)得快慢不重要重要的是每一次使用函數(shù)時都能清楚知道它在這個數(shù)據(jù)集上會發(fā)生什么。這個意識建立起來之后你寫的每一段 SQL 都會比之前穩(wěn)得多。