思维导图：
Hive函数全解
1. 窗口函数
作用：扩展列，而行不会变少
语法总览：
func(F|func|expr) over(
[partition by F1[,F2,...] 确定分区的边界(范围)
[order by Fa [asc|desc][,Fb [asc|desc]]] 确定分区内行的排列顺序(只有指定了ORDER BY才可以使用Window_Clause)
rows|range between ... and ... rows：物理行号(无重复) range：排序号(可能重复，会将重复行的数据视为一个整体)
unbounded preceding
N preceding
current row
N following
unbounded following
)[as] ALIA(别名)
** 注意：
对窗口函数的结果筛选必须在外层。
排序：
ROW_NUMBER() 行号
RANK() 排名(并列跳号)
DENSE_RANK() 排名(并列不跳号)✔
NTILE(N) 桶号(将数据[在每个分组内]按ORDER BY排序后的顺序分成N个连续区间)
作用：抽样 | 倾斜处理
PERCENT_RANK() 百分比排名 (当前排名-1)/(窗口总行数-1)
(排名越高越接近0，排名越低越接近1)
** 数据倾斜现象
group_id data
1 ...
1 ...
2 ...
2 ...
2 ...
2 ...
2 ...
3 ...
3 ...
4 ...
4 ...
定义
键值对(通常是键)分布不均匀
影响
计算资源分配不均 影响整体性能
如何处理数据倾斜现象？
1.识别数据倾斜
1 3 4 数据量为2
2 数据量为5
5 >> 2
2.计算分割因子
split_factor = floor(skewed_data_count/AVG(non-skewed_data_count))
3.应用分割逻辑
对于倾斜的数据，对每行数据添加一个额外的字段f1，f1的值从0到split_factor-1
对于非倾斜的数据，f1可以保持为一个固定值，如0
- 调整分组键
f与f1结合，形成新的分组键。根据新的分组键进行数据处理。
在处理完毕后，将f+f1还原为原始的分组键f
NTILE 如何自动化该数据倾斜处理过程？
- 获取数据倾斜的group_id(假设为2)
SELECT group_id,COUNT(*) AS cnt
FROM example_table
GROUP BY group_id
ORDER BY cnt DESC;
- 计算分割因子
SELECT FLOOR(COUNT(*)/t1.avg_cnt) AS split_factor
FROM example_table
CROSS JOIN(
SELECT AVG(cnt) AS avg_cnt
FROM(
SELECT COUNT(*) AS cnt
FROM example_table
WHERE group_id <> 2
GROUP BY group_id
)t
)t1
WHERE group_id = 2;
- 形成新分组键
SELECT CONCAT_WS('_', group_id, bucket_id) as new_group_key, data
FROM (
SELECT group_id, data,
CASE
WHEN group_id = 2 THEN NTILE(split_factor) OVER (PARTITION BY group_id ORDER BY data)
ELSE 0
END as bucket_id
FROM example_table,
(SELECT FLOOR(COUNT(*) / AVG_COUNT) as split_factor
FROM example_table
CROSS JOIN (
SELECT AVG(cnt) as AVG_COUNT
FROM (
SELECT COUNT(*) as cnt
FROM example_table
WHERE group_id != 2
GROUP BY group_id
) t
) t2
WHERE group_id = 2) t3
) result;
分析：不支持ROWS|RANGE BETWEEN，需要考虑F是否为NULL(IF(F IS NULL,NULL,...) AS ...)
LAG(F,N) 当前窗口内当前行的上N行的字段F值
LEAD(F,N) 当前窗口内当前行的下N行的字段F值
FIRST_VALUE(F) 当前窗口内第一行的字段F值
LAST_VALUE(F) 当前窗口内最后一行的字段F值
CUME_DIST() `<=`当前行值的所有行占窗口总行数的比例
PERCENTILE(F,V)
F：BIGINT V：DECIMAL|ARRAY<DECIMAL>
PERCENTILE(F,0.5) 获取中位数
PERCENTILE(F,array(0.25,0.56,0.9)) 获取四分位数
CORR(F1,F2) 获取皮尔逊相关系数(-1~+1，两变量相关的强度和方向)
COVAR_POP(F1,F2) 获取总体协方差(+|-，两变量是否同方向变化)
VAR_POP(F) 获取方差(衡量数据稳定性)
STDDEV_POP(F) 标准差
2. 数学函数
-----------------------正负-------------------------
abs(N) 绝对值
positive(N) 正数
negative(N) 负数
sign(N) 符号，正数返回+1，负数返回-1
-----------------------度数-------------------------
degrees(pi()/2) 弧转角
radians(90) 角转弧
sin(pi()/2) 求sin值
cos(pi()/2) 求cos值
tan(pi()/4) 求tan值
asin(N) 求arcsin值
acos(N) 求arccos值
atan(N) 求arctan值
-----------------------精度-------------------------
round(N,M) 四舍五入
bround(N,M) 四舍六入五凑偶(偶舍奇入)
ceil(N) 向上取整
floor(N) 向下取整
trunc(N,M) 截断操作
trunc(12345.678,2) => 12345.670000...
trunc(12345.678,-2) => 12300.000000...
format_number(N,FORMAT)
FORMAT = N => 等同于round
FORMAT = '###,###.#' =>
如果#的数量>或<数字的数量，都显示原数字，再根据.和,分隔数字。
-----------------------计算-------------------------
pow(N,M) 求幂
log(N,M) 求对数
factorial(N) 求N的阶乘
mod(N,M) N%M
shiftleft(N,M) 十进制数N对应的二进制数左移M位
shiftleft(cast(conv(1001,2,10) as int),1)
shiftright(N,M) 十进制数N对应的二进制数右移M位
shiftrightunsigned(N,M) 无符号右移
greatest(N1,N2,...) 求多列的最大值
least(N1,N2,...) 求多列的最小值
width_bucket(77,0,100,5) 区间(P2、P3)分桶(P4)定值(P1)的桶号
percentile_approx(expr,pc,[nb]) [超大]数据近似百分位数(n bins from histogram)
expr 将计算百分位数的列或表达式
pc 要计算的百分位数(0~1 0.5表示中位数，也可以用数组的形式表示)
nb 近似算法的桶的数量，通常在超大数据的时候进行使用
percentile_approx(salary,0.5,100) 计算salary列的近似中位数，并且在计算过程中使用了100个桶进行近似计算。
percentile_approx(salary,array(0.25,0.5,0.75),100)
histogram_numeric(F,N) 获取数据区间分布，将其分布为N个区。
[{"x":277797.38999999996,"y":3.0},{"x":313823.77111111116,"y":9.0},{"x":334791.79142857145,"y":7.0},{"x":352004.46400000004,"y":5.0},{"x":364576.2504545455,"y":22.0},{"x":383282.47500000003,"y":6.0},{"x":397107.64571428567,"y":7.0},{"x":417563.0433333332,"y":15.0},{"x":438436.93000000005,"y":3.0},{"x":457320.16000000003,"y":3.0},{"x":475004.04,"y":3.0},{"x":501651.47,"y":1.0}]
x表示数值边界，y表示前一个边界到当前边界出现的数据频次
----------------------进制转换----------------------
conv(N,FROM_BINARY,TO_BINARY) 返回字符串类型
------------------------常量------------------------
pi() 获取pi
e() 获取E
hash(N) 获取哈希值(数组 字符串 ✔ )
rand() 无参数为0~1，有参数为伪随机(固定参数的rand值相同)
日期函数
SELECT year(`current_date`()); -- 年
SELECT quarter(`current_date`()); -- 季
SELECT month(`current_date`()); -- 月
SELECT day(`current_date`()); -- 日
SELECT hour(`current_timestamp`()); -- 时
SELECT minute(`current_timestamp`()); -- 分
SELECT second(`current_timestamp`()); -- 秒
SELECT dayofweek(`2023-11-11`) -- 周日~周六 1~7
SELECT weekofyear(`current_date`()); -- 年周
SELECT date_format(`current_date`(),'yyyy'); -- 日期格式化 ✔(yyyy-MM-dd HH:mm:ss.SSS 部分或全部)
2021-05-13 11:22:33.545
SELECT floor_second(`current_timestamp`()); -- 向下取整到零毫秒
SELECT floor_minute(`current_timestamp`()); -- 向下取整到零秒
SELECT floor_hour(`current_timestamp`()); -- 向下取整到零分
SELECT floor_day(`current_timestamp`()); -- 向下取整到零时：年月日 <=> current_date()
SELECT floor_week(`current_timestamp`()); -- 向下取整到当周第一天
SELECT floor_month(`current_timestamp`()); -- 向下取整到当月第一天 <=> trunc(`current_timestamp`(),'MM')
SELECT floor_quarter(`current_timestamp`()); -- 向下取整到当季第一天 <=> trunc(`current_timestamp`(),'Q')
SELECT floor_year(`current_timestamp`()); -- 向下取整到当年第一天 <=> trunc(`current_timestamp`(),'YYYY')
SELECT last_day(`current_date`()); -- 向下取整到当月最后一天
取整日期函数的应用场景：
将其作为分组字段，统计不同粒度下的结果。
SELECT date_add(`current_date`(),-2); -- 日期计算：天±
SELECT add_months(`current_date`(),-2); -- 日期计算：月±
SELECT datediff(`current_date`(),'2021-10-18'); -- 日期计算：两个日期天数差(前-后)
SELECT months_between(date1,date2); -- 日期计算：两个日期月数差(浮点数：表示日期之间的完整月数加上剩余天数的小数部分)
-- date1在date2之后，为正数；反之则为负数。
SELECT next_day(`current_date`(),'MON'); -- 下一个星期几（未至返回本周，已过返回下周）
SELECT `current_date`(); -- 获取当前日期：年月日
SELECT `current_timestamp`(); -- 获取当前时间：年月日时分秒
SELECT unix_timestamp(); -- 获取当前日期时间戳
SELECT unix_timestamp('2021-10-18 11:12:13','yyyy-MM'); -- 获取指定日期指定格式的时间戳 <=> to_unix_timestamp，格式参数可选
SELECT from_unixtime(1634515200); -- 将时间戳转化为日期
SELECT to_utc_timestamp('2021-10-18 11:12:13','GMT+8'); -- 按指定时区转化日期格式：UTC
`GMT+8`表示当地时间比格林威治时间早了8个小时，结果为'2021-02-18 11:12:13'
将每条交易记录的时间戳从当地失去转换为UTC时区，并进行统一的数据分析。
SELECT to_date(`current_timestamp`()); -- 将日期时间值转化为日期(年月日)
SELECT to_date('2021-01-15 15:32:08'); => 2021-01-15
3. 字符函数
SELECT encode('hello你好','UTF-8'); -- 编码
SELECT decode(encode('hello你好','GBK'),'GBK'); -- 解码
SELECT base64(binary('abc')); -- 简单对称加密(二进制编码：将二进制数据转换为ASCII字符集)
base64进行对称加密前，需要将数据转化为二进制形式
SELECT base64(USER_NAME) FROM TABLE_NAME; 对识别信息(例如名字)进行匿名化处理
SELECT unbase64('YWJj'); -- 简单对称解密
SELECT base64(aes_encrypt('henry','1234567812345678')) -- AES对称加密：16 24 32
AES加密之后的结果是二进制数据，AES加密需要一个密钥，该密钥的长度通常为16，24，32字符长度。
base64(aes_encrypt(data,'AES_KEY')) -- 在不安全的网络中传输敏感数据
SELECT md5('abcdef'); -- 非对称加密：返回长度位32位的16进制值
SELECT md5(USER_PASSWORD) FROM TABLE_NAME; 对敏感数据(例如用户密码)进行非对称加密
SELECT sha('abc'); -- 非对称加密 <=> sha1
SELECT sha2('abc',224); -- 非对称加密：224，256，384，512
SHA系列函数是一种加密哈希函数，用于生成固定位数的哈希值。
安全性：非对称>对称
性能：对称>非对称
SELECT reflect("class","method",arg1[,arg2,...,argn]) 使用反射函数在Hive中调用Java类方法
如何实现对网页加密部分进行解码？
可以使用`reflect`函数调用Java的`URLDecoder`类来解决这个问题
SELECT reflect("java.net.URLDecoder", "decode", parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','QUERY','keyword'), 'UTF-8') AS decoded_keyword;
SELECT mask('x2你z'); -- 字母显示为x，数字显示为n，汉字保留。
SELECT mask_hash('123abc我'); -- 返回长度为64位的16进制数
SELECT mask_first_n('123abc我',3); -- mask前n个字符
SELECT mask_last_n('123abc我',3); -- mask后n个字符
SELECT mask_show_first_n('123abcdef',3); -- 保留原始字符串的前n个字符，其余字符被掩码替换。
=> 123xxxxxx
SELECT mask_show_last_n('123abcdef',3); -- 保留原始字符串的后n个字符，其余字符被掩码替换。
=> nnnxxxdef
mask 通常用于数据脱敏，如果需要自定义脱敏字符：replace(mask(...),'n','x');
如果要对汉字进行脱敏：
-- 张**
select substr('张三丰',0,1)；--str,fromIndex,Length
select concat(substr('张三丰'，0,1)，repeat("*",Length('张三丰')-1))；
SELECT repeat('abc',2); -- 将字符串重复指定的次数
SELECT replace('abc123abc','abc','XYZ') -- 替换全部相应的字符
SELECT regexp_replace('abc123def456xyz','\\d+','***') -- 替换全部符合正则的字符
SELECT translate('abc123ae12f','a1','xx'); -- 按照字符进行替换
SELECT translate('hello','el','12') => h122o
SELECT translate('hello','e','12') => h1llo
SELECT translate('hello','elo','12') => h122 -- 在原字符串中删除多出来的字符
SELECT reverse('abc'); -- 反转字符串
SELECT initcap('henry'); -- 首字母大写
SELECT lcase('HenRy'); -- 全部小写 <=> lower(str)
SELECT ucase('henRy'); -- 全部大写 <=> upper(str)
SELECT lpad('aa',5,0); => 000aa -- 左填充
SELECT rpad('aa',5,0); => aa000 -- 右填充
SELECT space(5); -- 生成N个空格
SELECT trim(' abc '); -- 两边裁剪
SELECT ltrim(' abc '); -- 左边裁剪
SELECT rtrim(' abc '); -- 右边裁剪
SELECT length('abc我'); => 4 -- 返回字符串长度
SELECT octet_length('abc我'); => 6 -- 返回字符串字节长度(汉字3字节)
-- 了解字节长度有利于评估存储需求和网络带宽使用
SELECT index(`array`(22,11,33),2); -- 返回数组中指定下标位置的元素值 <=> array(22,11,33)[2];
SELECT elt(2,"aa","bb","cc"); -- 提取字符串列表中的第N个值
SELECT field('aa','bb','cc','aa'); -- 返回参数一在后面字符串列表中的位置，从1开始
=> 3
SELECT find_in_set('aa','bb,cc,aa'); -- 返回参数一在后面字符串中的位置，找不到返回负数。
SELECT locate('aa','bbaacc',1); -- 获取参数一在参数二中的从参数三(从1开始)开始向后第一次出现的位置，找不到返回负数。
SELECT instr('bbccaa','aa'); -- 获取参数二在参数一中第一次出现的位置，从1开始
SELECT printf('%s,%d,%b,%.2f','aa',12,false,12.345); -- 格式化输出(占位符同Java)
SELECT concat('abc','-','def'); -- 拼接字符串
SELECT concat_ws('-','1','2','3'); => 1-2-3 -- 指定分隔符拼接字符串，支持【字符串数组拼接】
SELECT concat_ws('-',`array`('apple','banana','city'));
SELECT uuid(); -- 随机36位的16进制字符串
SELECT split('1,2,3',','); =>["1","2","3"] -- 按指定分隔符将字符串分割为字符串数组
SELECT sentences('hello how are you? I am fine. Thank you!'); -- 英文句子按标点拆分成二维数组，处理大型文本
=>[["hello","how","are","you"],["I","am","fine"],["Thank","you"]]
SELECT substring('henry@qq.com',2); -- 截取字符串：提取由指定位置开始的指定长度的字符串
SELECT substr('henry@qq.com',2,3);
SELECT substring_index('henry@qq.com',',',-2); -- 提取参数二指定分隔符分隔的前N个元素，若为负值则代表后N个元素。
SELECT substring_index('apple,can,you',',',2); => apple can
SELECT substring_index('apple,can,you',',',-2); => can you
SELECT substring_index(substring_index('henry,qq,com',',',2),',',-1); => qq <=> 提取第2个
SELECT 'abc' LIKE 'ab_'; -- 模糊匹配(可以作为匹配模式的字符有：%<任意个字符>,_(单个字符))
SELECT '123' rlike '\\d{3}'; -- 正则匹配
SELECT levenshtein('xyz','abcd'); -- 相似性，0为相同，值越大相似性越差
SELECT soundex('Abcef'); => A120 -- 旨在识别拼写不同但发音相似的单次
人名搜索，例如在搜索形式"Smith"，可能希望同时找到"Smyth"或"Smithe",，避免因为拼写不当造成检索遗漏
SELECT * FROM people WHERE soundex(name) = soundex('Smith');
-- 【词频统计】ngrams()和context_ngrams()都要与sentences()函数一起使用
SELECT ngrams(sentences('hello how are you? fine , thank you and you?'),2,3);
-- 第一个参数：单词二维数组
-- 第二个参数：连续N个单词
-- 第三个参数：top-k
SELECT context_ngrams(sentences('hello how are you? fine , thank you and you?'),array('how',null),3);
-- 第一个参数：单词二维数组
-- 第二个参数：和how右搭配的单词词频统计
-- 第三个参数：top-k
统计分词结果中与数组指定单词一起出现的频率最高的TOP-K结果。
** 其他搭配方式：
左侧搭配：array(null,'how');
特定位置的搭配：array('how',null,null); 寻找和'how'隔了一个单词搭配的单词
精确序列：array('how', 'are', 'you')
-- 【json】解析：解析后的内容都是字符串({"province":"江苏","city":"南京"})
SELECT get_json_object(json_string,json_path) 解析json的字符串json_string，返回path指定的内容;如果输入的json字符串无效，那么返回NULL。
假设json_string为：
{ "store": {
"book": [
{ "category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{ "category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
}
]
}
}
提取第一本书的作者：
SELECT get_json_object(json_string,'$.store.book[0].author')
FROM TABLE_NAME;
SELECT json_tuple(json_string,'FIELD1','FIELD2') AS (col1,col2) 提取json字符串中的特定字段并作为独立的列返回
with tmp as (
SELECT json_tuple('{"name":"张三","hobbies":["beauty","money","power"],"address":{"province":"江苏","city":"南京"}}',
'name', 'hobbies', 'address') as (name, hobbies, address)
如果提取的字段不可以直接作为独立的列，则可先作为临时表。
SELECT name,
get_json_object(address,'`$`.province') as province,
get_json_object(address,'`$`.city') as city,
hobby
from tmp
lateral view explode(split(regexp_replace(hobbies,'\\[|]|"',''),','))V as hobby;
使用 lateral view explode(split(...)) 处理 hobbies 字段：
hobbies字段是一个JSON数组，首先通过regexp_replace函数去除方括号和引号，将其转换为普通的以逗号分隔的字符串。
再通过split按逗号分割这个字符串，得到一个包含各个爱好的数组。
lateral view + explode : 将数组的每个元素转成一个独立的行之后，与其他查询结果合并在最后的表中。
name province city hobbies
张三 江苏 南京 beauty
张三 江苏 南京 money
张三 江苏 南京 power
with tmp as (
SELECT json_tuple('{"name":"张三","hobbies":["beauty","money","power"],"address":{"province":"江苏","city":"南京"}}',
'name', 'hobbies', 'address') as (name, hobbies, address)
), tmp2 as (
SELECT name,
-- get_json_object(address,'`$`.province') as province,
-- get_json_object(address,'`$`.city') as city,
regexp_extract(address,'\\{"province":"(.*?)","city":"(.*?)"}',1) as province,
regexp_extract(address,'\\{"province":"(.*?)","city":"(.*?)"}',2) as city
regexp_replace(hobbies,'\\[|]|"','') as hobbies
FROM tmp
)
name province city hobbies
张三 江苏 南京 beauty,money,power
如果没有将hobbies由一行转多列，如何实现类似"查询所有爱好为beauty的用户"的查询？
SELECT * FROM tmp2
-- WHERE find_in_set('beauty',hobbies)>0;
-- WHERE locate('beauty',hobbies)>0;
-- WHERE hobbies RLIKE '.*beauty.*';
-- 【url】解析：PROTOCOL 协议 HOST 域名 PATH 检索 QUERY 查询
SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'PROTOCOL');
协议
-- https
SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'HOST');
域名
-- search.jd.com
SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'PATH');
路径
-- /Search
SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'QUERY', 'KEY_NAME');
检索(?后的所有内容)，检索时可以加上KEY_NAME便于查看键对应的值
-- %E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60
动态解析网页加密信息：
WITH tmp AS (
SELECT parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','QUERY','keyword') AS keyword,
parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','QUERY','keyword') AS enc
)
SELECT reflect('java.net.URLDecoder','decode',keyword, if(enc is null,'UTF-8',enc)) AS keyword FROM tmp;
SELECT parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','PROTOCOL','QUERY')
从URL中提取多个值
注意：parse_url_tuple()如果需要同时进行多种解析，此时若有QUERY解析，该解析后不可以加键名称。
parse_url的具体应用场景：
获取引流的来源或兴趣点，便于构建用户画像。(例如通过解析HOST获取引流来源，解析KEYWORD获取热点关键词。)
不同平台的URL的格式不同，首先要对不同平台的URL格式作一定的了解和分析。
** URL的基础规则
/ 分隔域名与路径
? 表示查询字符串的开始
?query=keyword 表示查询参数(Google使用q,百度使用wd...)，后面是搜索关键词
& 分隔多个参数
?query=keyword&page=2
= 分隔键和值
-- 【正则】分组提取，0表示整个字符串，1~N表示分组编号
SELECT regexp_extract('https://www.baidu.com/s?wd=hive%20noop&rsv_spt=1&rsv_iqid=0xb9477d43000000e563&issp=1&f=8&rsv_bp=1',
'(.?)://(.?)/(.?)?(\w+)=(.?)&(\w+)=(.?)&.',7)
-- 【xml】路径提取
-- xpath 字符串返回数组
text() 用于选取XML元素的文本内容
@PROPERTY_NAME 用于选取XML元素的属性值
/ 从根节点开始查找
// 查找任意位置的元素
SELECT xpath('<student stuId="1"><name>henry</name><age>22</age><gender>male</gender></student>','student/*/text()'); 返回所有二级节点的值
SELECT xpath('<student stuId="1"><name>henry</name><age>22</age><gender>male</gender></student>','student/@stuId'); 返回字符串中所有名为stuId的属性值
-- 返回第一个匹配结点的指定类型内容 xpath_boolean|double|int|float|long|number|short|string
SELECT xpath_string('<a><b id="foo">b1</b><b id="bar">b2</b></a>','//@id') // 返回xml字符中第一个名为id的属性值
=> "foo"
4. 集合函数
SELECT collect_list(),collect_set(),array(),split('','') -- 一维数组的构建
SELECT array(array('henry','jack'),array('pola','rose')) -- 二维数组的创建
SELECT size(array(1,2,3)); -- array或map的大小
SELECT sort_array(array(22,11,33,3)); -- 数组排序(升序)
-- 如何实现降序？
SELECT reverse(sort_array(array(22,11,33,3)));
SELECT struct("henry",22,true);
{"col1":"henry","col2":22,"col3":true} -- 匿名结构体
SELECT named_struct('name','henry','age',22,'is_Member',true);
{"name":"henry","age":22,"is_member":true} -- 命名结构体
注意：is_Member => is_member 的原因是在结构化数据的过程中遵循JSON`首字母小写`的规约。
-- sort_array_by(array(structObj1,...),[f1,[f2...]],'ASC'|'DESC')
SELECT sort_array_by(array( -- 对结构体数组按照一定排序列进行排序
named_struct('name','henry','age',22,'is_member',true),
named_struct('name','pola','age',20,'is_member',true),
named_struct('name','ariel','age',16,'is_member',true)
),'name','ASC');
SELECT array_contains(array('henry','pola','ariel'),'pola') -- 判定数组中是否包含指定元素
SELECT split('henry','pola','ariel',',')[0]; -- 获取数组元素
SELECT str_to_map('java:88,hadoop:96,hive:56',',',':'); --
-- str_to_map()的输入字符串必须符合映射的格式。
SELECT `map`("java",88,"hadoop",96,"hive",56);
SELECT explode(`map`("java",88,"hadoop",96,"hive",56)) AS (subject,score); -- 列转行
SELECT map_keys(`map`("java",88,"hadoop",96,"hive",56)) AS key_set; -- 键set
SELECT map_values(`map`("java",88,"hadoop",96,"hive",56)) AS value_set; -- 值set
SELECT stack(N,f1,...,fn); -- 将n个数据分为N等份，每份占一行。若n%N!=0，报错。
5. 条件函数
SELECT if(true,1,0);
SELECT in_file('ariel','/root/hive/data/course/hive_func_in_file.data'); -- 判断 某个表中某个字段的值|指定内容 是否出现在指定文件中
SELECT isfalse();
SELECT isfalse(0); => true
SELECT istrue();
SELECT isnull();
SELECT not(); -- 取相反的情况
SELECT nullif(2,3); -- 如果两个参数相等，返回NULL;如果不相等，返回第一个参数。
避免除零错误：
SELECT col1, col2, col1/NULLIF(col2, 0) AS result(任何涉及NULL的结果都是NULL)
FROM TABLE_NAME;
SELECT nvl(null,3); -- 返回第一个非NULL参数的值(只能有两个参数)
替换NULL值为默认值：
SELECT NVL(col,'默认值') AS new_col
FROM TABLE_NAME;
聚合函数中处理NULL值：
SELECT SUM(NVL(col,0)) AS total
FROM TABLE_NAME;
SELECT coalesce(null,null,...,5); -- 返回第一个非NULL参数的值(可以有多个参数)
SELECT case f1 when V1 then ... when v2 then ... else vn end;
SELECT case when f1>=v1 then ... when f1>=v2 then ... else ... end;
WITH tmp AS(
SELECT named_struct('name','me','age',18,'is_member',true) as self,
array(
named_struct('name','henry','age',22,'is_member',true),
named_struct('name','pola','age',20,'is_member',true),
named_struct('name','ariel','age',16,'is_member',false)
) AS array_struct
)
SELECT self.name,self.age,self.is_member,name,age,is_member
FROM tmp
LATERAL VIEW inline(array_struct)V AS name,age,is_member;
self.name self.age self.is_member name age is_member
me 18 true henry 22 true
me 18 true pola 20 true
me 18 true ariel 16 false
LATERAL VIEW + inline() 用于将结构体数组的每个数组元素转化为一行，并且合并到最终结果中。
** explode和inline的区别:
explode适用于单个字段的简单数组
inline适用于复杂的结构体数组
6. 其他函数
SELECT version(); -- 检查Hive的版本
