Hive SQL学习整理_3


=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
`

 

参考链接:

=END=

, ,

《“Hive SQL学习整理_3”》 有 21 条评论

  1. 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
    ;
    `

  2. 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子句用来从分组的结果中筛选行。
    `

  3. 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

  4. 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)

  5. 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

  6. 数据倾斜问题:数据处理与分析过程中的杀手
    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.具体问题具体分析
    `

  7. 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

  8. 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

  9. 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

  10. 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的默认行为是从结果中删除重复的行。
    `

  11. 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

  12. 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/

  13. 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

  14. 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,而不是空串,上面我的那个正则之所以返回空字符串,是因为前面的 .+? 可以将内容全匹配上,不存在匹配失败的情况。
    `

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注