HIVE开发常用语法介绍

1、str_to_map-字符串转为map格式：https://blog.csdn.net/yuanyangsdo/article/details/64441165
nvl(str_to_map(nvl(qua,‘LCID=0000’),’&’,’=’)[‘LCID’],nvl(split(qua,’&’)[6],‘0000’)) as lcid
case nvl(str_to_map(nvl(qua,‘TM=01’),’&’,’=’)[‘TM’],‘01’) when ‘04’ then ‘special’ else ‘normal’ end as qbtype
2、collect_set/collect_list：将分组中的某列转为一个数组（使用数字下标，直接访问数组中的元素）返回，前者去重后者不去重；
3、获取字符串中特定字符后的字符串：select reverse(split(reverse(‘wo/mi/k’),’/’)[0])
4、cube：数据魔方，可以实现hive多个任意维度的查询，cube(a,b,c)先会对(a,b,c)进行group by，然后依次是(a,b),(a,c),(a),(b,c),(b),©,最后在对全表进行group by，他会统计所选列中值的所有组合的聚合；
5、grouping sets：作为GROUP BY的子句，允许开发人员在GROUP BY语句后面指定多个统计选项，可以简单理解为多条group by语句通过union all把查询结果聚合起来结合起来；
6、Rollup：可以实现从右到左递减多级的统计，显示统计某一层次结构的聚合。
7、多行合并：concat_ws(’’,collect_set(name)) + group by ;-- 对某列进行去重 concat_ws(’’,collect_list(name)) + group by; – 对某列不去重（等同group_concat）
一行拆多行：lateral view explode(split(name,’,’))
split()函数是用于切分数据，也就是将一串字符串切割成了一个数组，标准格式为split(str,regex),支持正则切分不过有点奇怪的是，对于 “.”,"|“这样的特殊字符，不加”\“的时候是特殊字符，加了以后才是普通字符，而对于”\d"的字符，需要加"\“后才是特殊字符，就是是说”\d"才是匹配数字。返回值是一个array数组。
explode()函数用于打散行的函数（将一行的数据拆分成多行，它的参数必须为map或array）。这个函数常和split()并用。一个select后面只能获得一个explode产生的视图，如果要显示多个列，则需要将多个视图合并。
lateral view可以实现：select rename,age from demo lateral view explode(split(name,’,’)) demo as rename;
如果要实现两个字段的行拆分，需要各自处理，如下，相当于做了一次全连接，要一一匹配的话，需要再写脚本加工。
select name,age from demo
lateral view explode(split(name,’,’)) demo as name
lateral view explode(split(age,’,’)) demo as age;
报错：FAILED: ParseException line 4:0 missing EOF at ‘lateral’ near ‘20210317’，hive需要在每个子查询后面加别名。
8、hive不支持join的on后面跟非相等条件，需要放到where后面；
9、hive删除历史分区数据：alter table 表名 drop partition (分区字段=20161118);
10、取某个字符之前的所有字符：substr(last_full_version,1,instr(last_full_version,’.’,1,3)-1)
instr()函数的格式 （俗称：字符查找函数）
格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。
11、hive里没有replace这个函数，替代的是translate和regexp_replace；
12、coalesce和cube一起用的时候，如果有left join，注意由于left join产生的null，如果存在的话，也被转换为total，会产生主键重复的数据；
13、NVL函数的格式如下：NVL(expr1,expr2)，含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
NVL2函数的格式如下：NVL2(expr1,expr2, expr3)，含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。
NULLIF(exp1,expr2)，函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。
Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。格式如下：Coalesce(expr1, expr2, expr3…… exprn)，表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
14、如何判断一个字符串中全部为中文？
中文的正则表达式： [\u4e00-\u9fa5] ¹$ ^代表开头，$$\begin{gathered} 这种代表结尾，也就是开头跟结尾都是中文；如selectcasewhe{n}^{\prime }我爱你中{国}^{\prime }rlik{e}^{\prime [} \\ u4e00- \\ u9fa5] \end{gathered}$$ ’ then 1 else 0 end; 执行后会返回1，代表字符里全是中文；
如果只想判断里面含有中文，则不用开头和结尾的符号：select case when ‘sdasd我爱你中国asdsad’ rlike ‘[\u4e00-\u9fa5]’ then 1 else 0 end; 执行后会返回1，代表字符里含一个中文，含多个中文的写法是’[\\u4e00-\u9fa5]+’
[\u4E00-\u9FA5\s]+ 多个汉字，包括空格
[\u4E00-\u9FA5]+ 多个汉字，不包括空格
[\u4E00-\u9FA5] 一个汉字
正则表达式：https://blog.csdn.net/dazhi_100/article/details/11746621?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-6&spm=1001.2101.3001.4242
15、url编码解析函数是：url_decode(event_value, “GBK”)
16、字符串截取操作：https://blog.csdn.net/weixin_42412645/article/details/103709580?ops_request_misc=&request_id=&biz_id=102&utm_term=HIVE%2520%2520%25E5%258F%2596%25E5%25AD%2597%25E7%25AC%25A6%25E4%25B8%25B2%25E4%25B9%258B%25E5%2590%258E%25E7%259A%2584%25E5%25AD%2597%25E7%25AC%25A6&utm_medium=distribute.pc_search_result.none-task-blog-2_allsobaiduweb~default-2-103709580.pc_search_result_no_baidu_js
17、字符串匹配：locate(string substr, string str, int pos)，返回substr在str中位置pos后出现的位置，比如select locate(‘good’, ‘hellogoder’, 1);-- 返回0，select locate(‘good’, ‘hellogooder’, 1);-- 返回6
18、str_to_map(字符串参数,分隔符1,分隔符2)：使用两个分隔符将文本拆分为键值对；
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是“，”，分隔符2默认是“=”。
拆分为键对值后，获取对应key的方法是，直接在后面用[‘key值’]，即取到了对应的value值，但前提是知道要取的key值是什么，
举例：string_to_map(startup_cate_cnt,’#’,’:’)[‘主动打开’]，startup_cate_cnt like ‘主动打开:11#other:2’，结果是11。
19、distribute by、sort by
hive中（distribute by + “表中字段”）关键字控制map输出结果的分发,相同字段的map输出会发到一个reduce节点去处理。sort by为每一个reducer产生一个排序文件，他俩一般情况下会结合使用。
20、样本随机抽样
1）用rand()随机排序，后根据排序值筛选；
2）用limit随机抽取；
3）数据块抽样tablesample；
4）系统抽样mod,rand()，依照userid取模分组，每组随机抽样；

---

1. \u4e00-\u9fa5 ↩︎