=Start=
缘由：
继续整理最近学到或是用到的Hive SQL知识，方便以后参考。
正文：
参考解答：
1、Hive SQL中如何判断某一列中是否包含某个关键字？
思路大概有2种（字符串查找、模糊匹配/正则匹配），具体的实现方法就多一些，以下是我整理的几种：
-- 使用like
where col_name like '%key_word%'
-- 使用instr
where instr(col_name,'key_word') is not null and instr(col_name,'key_word') > 0
-- 使用locate
where locate('key_word',col_name) > 0
/*
instr(string str, string substr) -- 返回substr在str中第一次出现的位置。若任何参数为null返回null，若substr不在str中返回0。str中第一个字符的位置为1
locate(string substr, string str[, int pos]) -- 返回 substr 在 str 的位置pos后第一次出现的位置
find_in_set(string str, string strList) -- 返回str在strList中第一次出现的位置，strList为用逗号分隔的字符串，如果str包含逗号则返回0，若任何参数为null，返回null。如： find_in_set('ab', 'abc,b,ab,c,def') 返回3
*/
&
select locate('a','abcd'), locate('b', 'abcd'), locate('f', 'abcd')
-- 1 2 0
select instr('abcd','a'), instr('abcd',null), instr('abcd','f')
-- 1 NULL 0
select find_in_set('ab','ef,ab,de'), find_in_set('at','ef,ab,de')
-- 2 0
select find_in_set('ab','ef,ab,de'), find_in_set('at','ef,ab,de'), find_in_set('at','not_comma-delimited_string')
-- 2 0 0
select find_in_set('com','not_comma-delimited_string')
-- 0
/*
instr(string str, string substr) -- 返回substr在str中第一次出现的位置。若任何参数为null返回null，若substr不在str中返回0。str中第一个字符的位置为1
locate(string substr, string str[, int pos]) -- 返回 substr 在 str 的位置pos后第一次出现的位置
find_in_set(string str, string strList) -- 返回str在strList中第一次出现的位置，strList为用逗号分隔的字符串，如果str包含逗号则返回0，若任何参数为null，返回null。如： find_in_set('ab', 'abc,b,ab,c,def') 返回3
*/
2、条件判断Case When Else End的用法
-- update操作： update table_name set 字段1=case when 条件1 then 值1 when 条件2 then 值2 else 值3 end where ... -- select操作： select 字段1, 字段2, case 字段3 when 值1 then 新值 when 值2 then 新值 end as 重新命名字段3的名字 from table_name where ... order by ...
3、用正则进行字符串提取、替换
/*
regexp_extract(string subject, string pattern, int index)
使用pattern从给定字符串中提取字符串。如： regexp_extract('foothebar', 'foo(.*?)(bar)', 2) 返回'bar'。有时需要使用预定义的字符类：使用'\s' 做为第二个参数将匹配s，'\\s'匹配空格等。参数index是Java正则匹配器方法group()方法中的索引。
在有些情况下要使用转义字符，需要符合java正则表达式的规则。
*/
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1);
-- the
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2);
-- bar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 0);
-- foothebar
/*
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)
使用 REPLACEMENT 替换字符串 INITIAL_STRING 中匹配 PATTERN 的子串。注意，在有些情况下要使用转义字符。
*/
select regexp_replace("foobar", "oo|ar", "");
-- 'fb'
4、Hive SQL中的子查询
/* FROM子句中的子查询(Subqueries in the FROM Clause) SELECT ... FROM (subquery) name ... SELECT ... FROM (subquery) AS name ... (Note: Only valid starting with Hive 0.13.0) */ SELECT col FROM ( SELECT a+b AS col FROM t1 ) t2 /* WHERE子句中的子查询(Subqueries in the WHERE Clause) */ SELECT * FROM A WHERE A.a IN (SELECT foo FROM B); SELECT A FROM T1 WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)
5、杂项
/*
replace(string A, string OLD, string NEW)
常规的字符串替换，使用 NEW 替换字符串 A 中的 OLD 。
*/
select replace("ababab", "abab", "Z");
-- 'Zab'
/*
levenshtein(string A, string B)
计算2个字符之间的 levenshtein 距离
*/
select levenshtein('kitten', 'sitting');
-- 3
/*
语法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])
返回值: string
说明：返回URL中指定的部分。partToExtract的有效值为: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, 和 USERINFO 。
*/
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')
-- 'facebook.com'
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
-- 'v1'
官方 LanguageManual UDF
`
内置的操作符
内置的函数
内置的聚合函数(UDAF)
内置的Table-Generating函数(UDTF)
针对f(column)的分组和排序
UDF内部
创建自定义的UDF
`
参考链接：
- HiveQL – String contains equivalent in hiveql UDF?
- Find a keyword in a column in Hive table
- 【Hive】join中的模糊匹配（locate）
- SQL中的Case When Else End的用法
- 官方 LanguageManual SubQueries
- Subqueries in SELECT
=END=
《 “Hive SQL学习整理_3” 》 有 22 条评论
Hive SQL优化之 Count Distinct
http://bigdata-blog.net/2013/11/08/hive-sql%E4%BC%98%E5%8C%96%E4%B9%8B-count-distinct/
Hive之COUNT DISTINCT优化
http://datavalley.github.io/2016/02/15/Hive%E4%B9%8BCOUNT-DISTINCT%E4%BC%98%E5%8C%96
`
COUNT(DISTINCT xxx)在hive中很容易造成数据倾斜。
使用GROUP BY 操作代替 COUNT(DISTINCT) 操作。
`
hive 多字段同时count(distinct)优化
https://superlxw1234.iteye.com/blog/1534779
SQL优化（二） 快速计算Distinct Count
http://www.jasongj.com/2015/03/15/count_distinct/
Hive优化-让数据不再倾斜
https://zhuanlan.zhihu.com/p/29276028
Counting DISTINCT over multiple columns
https://stackoverflow.com/questions/1471250/counting-distinct-over-multiple-columns
How to order by count desc in each group in a hive?
https://stackoverflow.com/questions/20391008/how-to-order-by-count-desc-in-each-group-in-a-hive
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
Hive Order By操作
https://blog.csdn.net/lzm1340458776/article/details/43230517
Hive中order by，sort by，distribute by，cluster by的区别
https://blog.csdn.net/lzm1340458776/article/details/43306115
浅析Hive的group by和count（distinct）
https://blog.csdn.net/DM_Source/article/details/80246586
Hive collect_set函数
https://my.oschina.net/jackieyeah/blog/679476
Hive 的collect_set使用详解
https://blog.csdn.net/liyantianmin/article/details/48262109
`
Hive不允许直接访问非group by字段；
对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
使用数字下标，可以直接访问数组中的元素；
`
COLLECT_SET() in Hive, keep duplicates?
https://stackoverflow.com/questions/6445339/collect-set-in-hive-keep-duplicates
`
SELECT
hash_id, COLLECT_LIST(num_of_cats) AS aggr_set
FROM
tablename
WHERE
blablabla
GROUP BY
hash_id
;
`
SQL中WHERE与HAVING用法
http://whlminds.com/2015/04/05/where-and-having-in-sql/
`
在查询数据库表时，需要对表中的记录进行筛选，SQL提供了两个约束子句，即WHERE与HAVING，二者效果有雷同，但用法有区别。
#当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
ORDER BY column_name
# 使用区别
WHERE作用对象为数据库表、视图，HAVING作用对象为于组（Group）;
WHERE在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算），而HAVING在分组和聚集之后选取分组的行。因此，WHERE子句不能包含聚集函数；因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。相反，HAVING子句总是包含聚集函数。（严格说来，你可以写不使用聚集的HAVING子句， 但这样做只是白费劲。同样的条件可以更有效地用于WHERE阶段。）
# 总结
1. WHERE子句用来筛选FROM子句中指定的操作所产生的行。
2. GROUP BY子句用来分组WHERE子句的输出。
3. HAVING子句用来从分组的结果中筛选行。
`
hive union all 使用
https://blog.csdn.net/buster2014/article/details/50143247
http://www.bkjia.com/yjs/892967.html
`
SQL中 union all 有什用法？
就是把2个具有相同列及数据类型的结果放到一起显示，并且不去重。
select a,b,c from table1
union all
select ca,cb,cc from table2
什情况下用union？什情况下用union all？
union与union all的区别是：
前者会把两个记录集中相同的记录合并，而后者不会，性能上前者优。
如此一说，你知道什么时候用Union什么时候用Union All了吧。
当确认多个记录集不会存在相同记录，或者有可能有相同记录但明确要合并的，用Union；即使有相同记录也不合并的，用Union All。
`
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union
Hive 的collect_set使用详解
https://blog.csdn.net/liyantianmin/article/details/48262109
`
Hive不允许直接访问非group by字段；
对于非group by字段，可以用Hive的 collect_set 函数收集这些字段，返回一个数组；
然后结合 concat_ws 对集合中元素使用指定分隔符连接成字符串返回。
`
hive中的concat，concat_ws，collect_set用法
https://blog.csdn.net/waiwai3/article/details/79071544
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inAggregateFunctions(UDAF)
Hadoop Hive UDF 教程
https://blog.csdn.net/u014571011/article/details/52171530
hive 自定义UDF UDAF UDTF
https://blog.csdn.net/cheersu/article/details/8333045
Writing Custom Hive UDF and UDAF
https://dzone.com/articles/writing-custom-hive-udf-andudaf
HIVE UDF函数编写
https://github.com/delongwu/techdoc/wiki/HIVE-UDF%E5%87%BD%E6%95%B0%E7%BC%96%E5%86%99
Hadoop Hive UDF Tutorial – Extending Hive with Custom Functions
https://blog.matthewrathbone.com/2013/08/10/guide-to-writing-hive-udfs.html
https://github.com/rathboma/hive-extension-examples
HiveSQL的CASE-WHEN的使用
https://blog.csdn.net/kwu_ganymede/article/details/52106392
Hive中case when的两种语法
https://blog.csdn.net/ygdlx521/article/details/71156354
Hive分组取TOPN数据
https://www.jianshu.com/p/9802f3a035e1
HIVE中 ROW_NUMBER() OVER() 函数
https://blog.csdn.net/qq_31573519/article/details/78586205
Hive的分组排序方法-row_number
https://blog.csdn.net/u014571011/article/details/51907822
Row number functionality in Hive
https://stackoverflow.com/questions/37476952/row-number-functionality-in-hive
Hive / SQL query for top n values per key
https://stackoverflow.com/questions/50974809/hive-sql-query-for-top-n-values-per-key
row_number() over(partition by 列名1 order by 列名2 desc)的使用
https://blog.csdn.net/mzy755423868/article/details/80573654
数据倾斜问题：数据处理与分析过程中的杀手
https://toutiao.io/posts/l0n4mg/preview
https://mp.weixin.qq.com/s/xLlKORTD-jgQN7W5CV_WFw
`
本文面向的读者是从事数据分析、数据处理（ETL）等相关工作的朋友们，相信大家在工作中一定遇到过数据倾斜的问题，读完本文，你会了解到数据倾斜的定义及其危害、产生的原因及应对措施、常见倾斜场景及解决办法等知识，相信对你今后处理数据倾斜问题会有一定的帮助。
什么是数据倾斜？
数据倾斜，指的是并行处理的过程中，某些分区或节点处理的数据，显著高于其他分区或节点，导致这部分的数据处理任务比其他任务要大很多，从而成为这个阶段执行最慢的部分，进而成为整个作业执行的瓶颈，甚至直接导致作业失败。
数据倾斜的危害？
1.任务长时间挂起，资源利用率下降
2.引发内存溢出，导致任务失败
3.作业执行时间超出预期，导致后续依赖数据结果的作业出错
为什么会产生数据倾斜？
1.读入数据源时就是倾斜的
2.shuffle产生倾斜
3.过滤导致倾斜
怎么预防或解决数据倾斜？
1.尽量保证数据源是均衡的
2.对大数据集做过滤，结束后做repartition
3.对小表进行广播
4.编码时要注意，不要人为造成倾斜
5.join前优化
6.具体问题具体分析
`
HiveQL中case when……….then…….else的用法总结
https://blog.csdn.net/a2011480169/article/details/70137969
`
select ip,
sum(case when wifi_name is not NULL then 1 else 0 end) as wifi_cnt, –这里借助case-when和sum一起做统计
count(distinct wifi_name) as wifi_name_cnt,
concat_ws(‘#’, collect_set(wifi_name)) as wifi_name_s,
count(*) as cnt
from
log.access_log
group by ip
;
`
case when then else end
https://blog.csdn.net/xuxurui007/article/details/8479953
https://stackoverflow.com/questions/41023835/case-statements-in-hive
Presto equivalent of MySQL group_concat
https://stackoverflow.com/questions/44142356/presto-equivalent-of-mysql-group-concat
`
#presto
array_join(array_distinct(array_agg(col_name)), ‘#’)
#hive
concat_ws(‘#’, collect_set(col_name)) as col_name_s,
`
https://prestodb.io/docs/current/functions/aggregate.html#array_agg
Aggregating a column based on column values in Presto/Hive
https://stackoverflow.com/questions/50574650/aggregating-a-column-based-on-column-values-in-presto-hive
hive常用函数之条件判断函数IF,COALESCE,CASE
https://blog.csdn.net/qq_26442553/article/details/79465417
`
1、If函数:if和case差不多，都是处理单个列的查询结果
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
2、非空查找函数: COALESCE
语法: COALESCE(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
3、条件件判断函数：这种CASE A和if差不多，条件查询（常用）
语法: CASE a WHEN b THEN c [WHENd THEN e]* [ELSE f] END
返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
`
hive 中的条件判断
https://stackoverflow.com/questions/51076998/using-select-statement-with-if-condition-in-hive-to-populate-another-column
https://www.cnblogs.com/kxdblog/p/4034243.html
Union和Union All的区别
https://blog.csdn.net/wanghai__/article/details/4712555/
`
Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All，对两个结果集进行并集操作，包括重复行，不进行排序；
Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
`
[Hive]Union使用指南
https://blog.csdn.net/SunnyYoona/article/details/60779047
`
使用 DISTINCT 关键字与使用 UNION 默认值效果一样，都会删除重复行
使用 ALL 关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行）
Hive 1.2.0 之前的版本仅支持UNION ALL，其中重复的行不会被删除。
Hive 1.2.0 和更高版本中，UNION的默认行为是从结果中删除重复的行。
`
UNION vs UNION ALL in HIVE
https://selfstudynote.blogspot.com/2018/06/union-vs-union-all-in-hive.html
`
UNION 会删除合并后的重复行，而 UNION ALL 不会；
UNION ALL 比 UNION 会更快一些，因为有额外的操作，比如删除重复行(为达到这个目标，需要先对结果集进行排序，这就有性能损耗)；
UNION 的结果集是升序排列的，而 UNION ALL 的结果集并没有排列。
==
UNION removes duplicates, whereas UNION ALL does not. UNION operation eliminates the duplicated rows from the result set but UNION ALL returns all rows after joining.
UNION ALL is faster than UNION. Additional work is needed to remove duplicates. In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION, depending on the volume of data being sorted.
UNION result set is sorted in ascending order whereas UNION ALL Result set is not sorted
`
HiveQL UNION ALL
https://stackoverflow.com/questions/14096968/hiveql-union-all
Combine many tables in Hive using UNION ALL?
https://stackoverflow.com/questions/16181684/combine-many-tables-in-hive-using-union-all
Hive: how to show all partitions of a table?
https://stackoverflow.com/questions/15616290/hive-how-to-show-all-partitions-of-a-table
`
— 在不读取表内容进行统计的情况下，查看表的分区情况
hive> show partitions table_name;
`
Hive Show命令
https://blog.csdn.net/Post_Yuan/article/details/64437869
Hive学习之函数DDL和Show、Describe语句
https://blog.csdn.net/skywalker_only/article/details/32709777
HIVE SQL使用字符函数 instr 的问题（参数只有两个）
https://blog.csdn.net/vatermutter/article/details/97243492
SQL 函数 instr的用法
https://blog.csdn.net/qq_36414165/article/details/69374947
instr()函数的用法
https://blog.csdn.net/weixin_42772554/article/details/82744215
HIVE SQL中常用的字符串处理函数
https://blog.csdn.net/li_canhui/article/details/85258626
`
函数名: instr
参数: (string str, string substr)
返回值类型: int
描述:
instr()函数返回字符串str中子字符串substr第一次出现的位置，在sql中第一字符的位置是1，如果 str不含substr返回0。
样例:
select instr(“abcde”,”b”); — 2
`
http://hadooptutorial.info/string-functions-in-hive/
ARRAY_CONTAINS muliple values in hive
https://stackoverflow.com/questions/25645558/array-contains-muliple-values-in-hive
https://github.com/klout/brickhouse/blob/master/src/main/java/brickhouse/udf/collect/ArrayIntersectUDF.java
`
select array_contains(array(1,2,3,4,5),3) — true
select array_contains(split(‘ABCD:C:D:E’,’:’),’A’) — false
select array_contains(split(‘ABCD:C:D:E’,’:’),’ABCD’) — true
`
UDF
https://github.com/klout/brickhouse/wiki
字符串函数split、array_contains
https://blog.csdn.net/isourire/java/article/details/43986013
hive array_contains
https://blog.csdn.net/bbbeoy/article/details/83184800
hive笔记：复杂数据类型-array结构
https://www.cnblogs.com/sonia0087/p/9895349.html
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
https://docs.oracle.com/javase/8/docs/api/java/util/regex/Matcher.html
`
open-platform-log-20220422
log-collection-2022.03.15
shop-order-merge-info0
shop-order-merge-info3
ad_web_record_search2020.10
ad_web_record_search2020.11
infra_traffic_storage_es_1119
infra_traffic_storage_es_1123
index_pm_podcast_v1
identity_search_3
# 在Hive SQL中使用下面的语句进行正则提取
regexp_extract(index_name,'(.+?)[\\d\\.\\-]{1,}$’, 1) as index_name_real,
regexp_extract 函数在做字符串提取的时候，如果无法匹配上，则提取出的内容是个长度为0的空字符串，不是NULL，因此需要用 length>1 来判断，而不能使用 isnotnull/nvl/coalesce 等函数进行操作，否则返回结果可能不符合预期。
public String group(int group)
Parameters:
group – The index of a capturing group in this matcher’s pattern
Returns:
The (possibly empty) subsequence captured by the group during the previous match, or null if the group failed to match part of the input
当完全匹配失败的时候会返回null，而不是空串，上面我的那个正则之所以返回空字符串，是因为前面的 .+? 可以将内容全匹配上，不存在匹配失败的情况。
`
regexp_extract 函数在做字符串提取的时候，如果无法匹配上，则提取出的内容是个长度为0的空字符串，不是NULL，因此需要用 length>1 来判断，而不能使用 isnotnull/nvl/coalesce 等函数进行操作，否则返回结果可能不符合预期。