Hive 常用函数详细总结

Hive 常用函数详细总结本文汇总了 Hive 开发与面试中最常用、最实用的内置函数每个函数均附有语法说明和代码示例。内容涵盖字符串处理、日期时间、条件判断、聚合统计、开窗分析、集合操作、类型转换、JSON 解析等。目录一、字符串函数concat/concat_wssubstr/substringlengthupper/lowertrim/ltrim/rtrimregexp_replaceregexp_extractsplitinstr/locateget_json_object二、日期时间函数current_date/current_timestampyear/month/day/hour/minute/seconddatediffdate_add/date_subto_datefrom_unixtime/unix_timestampdate_formattrunc/last_day/next_day三、条件函数ifcase whencoalescenvlnullif/isnull/isnotnull四、聚合函数count/sum/avg/max/mincollect_set/collect_listarray_distinct/sizepercentile/percentile_approxhistogram_numeric五、开窗函数分析函数row_number/rank/dense_ranklag/leadfirst_value/last_valuesum/avg等聚合函数配合overntile六、集合与复杂类型函数array_containssort_arraystruct/named_structmap相关map_keys/map_values/map_contains_key七、类型转换与数学函数castround/floor/ceilrandabs/pow/sqrt八、其他实用函数explodeLateral Viewposexplodereflect调用 Java 方法hashmd5/sha1九、UDF 与 宏临时函数TEMPORARY FUNCTION宏CREATE TEMPORARY MACRO一、字符串函数1.concat/concat_ws功能连接字符串。concat(str1, str2, ...)直接拼接若任一为NULL则结果为NULL。concat_ws(separator, str1, str2, ...)用分隔符拼接自动跳过NULL。SELECTconcat(Hello, ,Hive);-- Hello HiveSELECTconcat_ws(-,2025,04,18);-- 2025-04-18SELECTconcat_ws(,,a,NULL,b);-- a,b2.substr/substring功能截取子串。substr(string, start[, length])start从 1 开始负数表示从末尾倒数。SELECTsubstr(Apache Hive,8,4);-- HiveSELECTsubstr(Apache Hive,-4);-- Hive3.length功能返回字符串长度字符数。SELECTlength(Hive);-- 44.upper/lower功能大小写转换。SELECTupper(hive),lower(HIVE);-- HIVE, hive5.trim/ltrim/rtrim功能去除首尾空格默认或指定字符。SELECTtrim( Hive );-- HiveSELECTltrim( Hive);-- HiveSELECTrtrim(Hive );-- Hive6.regexp_replace功能正则替换。regexp_replace(string, pattern, replacement)SELECTregexp_replace(2025-04-18,-,/);-- 2025/04/18-- 去掉数字外的所有字符SELECTregexp_replace(abc123def,[^0-9],);-- 1237.regexp_extract功能正则提取第n个捕获组。regexp_extract(string, pattern, n)SELECTregexp_extract(Hive 3.1.2,(\\d)\\.(\\d)\\.(\\d),2);-- 18.split功能按正则分割字符串返回数组。split(string, pattern)SELECTsplit(a,b,c,,)[1];-- b数组下标从0开始9.instr/locate功能返回子串第一次出现的位置从1开始。instr(str, substr)locate(substr, str[, pos])SELECTinstr(Hive is great,is);-- 6SELECTlocate(great,Hive is great);-- 1010.get_json_object功能从 JSON 字符串中提取指定字段。get_json_object(json_string, path)SELECTget_json_object({name:Alice,age:25},$.name);-- Alice二、日期时间函数11.current_date/current_timestampSELECTcurrent_date;-- 2026-04-18SELECTcurrent_timestamp;-- 2026-04-18 10:30:00.12312.year/month/day/hour/minute/secondSELECTyear(2025-04-18),month(2025-04-18),day(2025-04-18);-- 2025, 4, 18SELECThour(2025-04-18 14:30:00),minute(...),second(...);13.datediff功能计算两个日期相差的天数。datediff(endDate, startDate)SELECTdatediff(2025-04-18,2025-04-10);-- 814.date_add/date_sub功能日期加减天数。date_add(date, days)/date_sub(date, days)SELECTdate_add(2025-04-18,5);-- 2025-04-23SELECTdate_sub(2025-04-18,3);-- 2025-04-1515.to_date功能从时间戳中提取日期部分。SELECTto_date(2025-04-18 13:20:00);-- 2025-04-1816.from_unixtime/unix_timestampunix_timestamp([string[, pattern]])日期转 Unix 时间戳秒。from_unixtime(bigint[, pattern])时间戳转日期字符串。SELECTunix_timestamp(2025-04-18 10:00:00);-- 1744956000SELECTfrom_unixtime(1744956000,yyyy-MM-dd);-- 2025-04-1817.date_format功能格式化日期。date_format(date, format)SELECTdate_format(2025-04-18,yyyy年MM月dd日);-- 2025年04月18日18.trunc/last_day/next_daytrunc(date, MM)返回当月第一天。last_day(date)返回当月最后一天。next_day(date, Monday)返回下一个周一。SELECTtrunc(2025-04-18,MM);-- 2025-04-01SELECTlast_day(2025-04-18);-- 2025-04-30SELECTnext_day(2025-04-18,SUNDAY);-- 下一个周日三、条件函数19.if功能if(condition, true_value, false_value)SELECTif(11,Yes,No);-- Yes20.case when功能多条件判断。SELECTcasewhenscore90thenAwhenscore60thenBelseCendasgradeFROMscores;21.coalesce功能返回第一个非NULL值。coalesce(v1, v2, ...)SELECTcoalesce(NULL,NULL,Hive,Spark);-- Hive22.nvl功能nvl(value, default)若value为NULL则返回default。SELECTnvl(NULL,default);-- default23.nullif/isnull/isnotnullnullif(a, b)若a b返回NULL否则返回a。isnull(a)等价于a is null。SELECTnullif(5,5);-- NULLSELECTisnull(NULL);-- true四、聚合函数24.count/sum/avg/max/minSELECTcount(1),sum(sales),avg(price),max(price),min(price)FROMorders;25.collect_set/collect_list功能将分组内某列的值收集成数组collect_set去重collect_list不去重。SELECTdept,collect_set(name)ASdistinct_namesFROMemployeesGROUPBYdept;26.array_distinct/sizearray_distinct(array)数组去重。size(array|map)返回元素个数。SELECTarray_distinct(collect_list(city))FROMtable;SELECTsize(collect_set(uid))FROMlogs;27.percentile/percentile_approxpercentile(col, p)精确计算百分位数仅整数列。percentile_approx(col, p[, B])近似计算p为 0~1 或数组。SELECTpercentile_approx(salary,0.5)ASmedianFROMemp;-- 中位数SELECTpercentile_approx(salary,array(0.25,0.5,0.75))FROMemp;28.histogram_numeric功能生成直方图近似数据。histogram_numeric(col, nbins)SELECThistogram_numeric(age,10)FROMusers;-- 返回结构数组五、开窗函数分析函数29.row_number/rank/dense_rankrow_number()从 1 开始连续编号不并列。rank()并列时跳过后续序号1,2,2,4。dense_rank()并列不跳号1,2,2,3。SELECTname,dept,salary,row_number()over(partitionbydeptorderbysalarydesc)asrnFROMemp;30.lag/leadlag(col, n, default)取当前行前n行的值。lead(col, n, default)取后n行的值。SELECTdt,amount,lag(amount,1,0)over(orderbydt)asprev_amountFROMsales;31.first_value/last_value取窗口内第一个或最后一个值注意last_value默认窗口范围。SELECTname,dept,first_value(salary)over(partitionbydeptorderbysalary)asmin_salaryFROMemp;32. 聚合函数配合oversum(salary) over (partition by dept order by hire_date rows between unbounded preceding and current row)累积和。SELECTname,dept,salary,sum(salary)over(partitionbydeptorderbyhire_date)asrunning_totalFROMemp;33.ntile功能将分组数据分成n个桶返回桶编号1~n。SELECTname,score,ntile(4)over(orderbyscore)asquartileFROMstudents;六、集合与复杂类型函数34.array_contains功能判断数组是否包含某元素。SELECTarray_contains(array(1,2,3),2);-- true35.sort_array功能对数组排序升序。SELECTsort_array(array(3,1,2));-- [1,2,3]36.struct/named_struct创建结构体。SELECTnamed_struct(name,Alice,age,25)asperson;37.map相关map_keys(map)返回所有键的数组。map_values(map)返回所有值的数组。map_contains_key(map, key)是否包含键。SELECTmap_keys(map(a,1,b,2));-- [a,b]七、类型转换与数学函数38.cast功能显式类型转换。cast(value AS type)SELECTcast(123ASINT);-- 123SELECTcast(2025-04-18ASDATE);-- 2025-04-1839.round/floor/ceilround(col, d)四舍五入保留 d 位小数。floor/ceil向下/向上取整。SELECTround(3.14159,2);-- 3.14SELECTfloor(3.9);-- 340.rand功能返回 0~1 之间的随机数。rand([seed])SELECTrand();-- 随机小数41.abs/pow/sqrtSELECTabs(-5),pow(2,3),sqrt(9);-- 5, 8, 3八、其他实用函数42.explode配合 Lateral View功能将数组或 map 展开成多行。常与LATERAL VIEW联用。SELECTid,hobbyFROMuser_hobbies LATERALVIEWexplode(hobbies)tAShobby;43.posexplode功能展开数组同时返回位置索引。SELECTpos,valFROM(SELECTarray(a,b,c)ASarr)t LATERALVIEWposexplode(arr)ASpos,val;-- 结果(0,a), (1,b), (2,c)44.reflect功能调用 Java 静态方法。reflect(class, method, arg1, ...)SELECTreflect(java.util.UUID,randomUUID);-- 生成随机 UUID45.hash功能计算哈希值int。SELECThash(Hive);-- 返回整数46.md5/sha1/sha2加密哈希函数。SELECTmd5(password);-- 5f4dcc3b5aa765d61d8327deb882cf99九、UDF 与 宏47. 临时函数注册自定义函数当前会话有效。ADDJAR/path/to/my-udf.jar;CREATETEMPORARYFUNCTIONmy_lenAScom.example.MyLengthUDF;SELECTmy_len(hello);48. 宏功能创建可重用的表达式片段。CREATE TEMPORARY MACRO macro_name(参数) 表达式CREATETEMPORARYMACRO square(x)x*x;SELECTsquare(5);-- 25