Hive SQL学习整理_3

本文最后更新于2019年1月13日,已超过 1 年没有更新,如果文章内容失效,还请反馈给我,谢谢!

=Start=

缘由:

继续整理最近学到或是用到的Hive SQL知识,方便以后参考。

正文:

参考解答:
1、Hive SQL中如何判断某一列中是否包含某个关键字?

思路大概有2种(字符串查找、模糊匹配/正则匹配),具体的实现方法就多一些,以下是我整理的几种:

&

2、条件判断Case When Else End的用法

3、用正则进行字符串提取、替换

4、Hive SQL中的子查询

5、杂项

 

官方 LanguageManual UDF


内置的操作符
内置的函数
内置的聚合函数(UDAF)
内置的Table-Generating函数(UDTF)
针对f(column)的分组和排序
UDF内部
创建自定义的UDF

 

参考链接:

=END=

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/4248.html

《Hive SQL学习整理_3》上有18条评论

  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/

发表评论

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