Hive SQL学习整理_4

=Start=

缘由:

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

正文:

参考解答:
0、Hive SQL中的 having 子句以及它与 where 子句的区别

&

having子句与where都是过滤语句。

  • where 子句的作用是在对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,条件中不能包含聚合函数,使用where条件显示特定的行。
  • having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,条件中经常包含聚合函数,使用having条件显示特定的组,也可以使用多个分组标准进行分组。和group by一样,having 子句中的每一个元素也必须出现在select列表中。
  • 总之,select 用where过滤,找到符合条件的元组。而having 用在group by后,配合使用,过滤结果。

1、Hive SQL中的条件判断函数(类似于编程语言中的if..else)

2、Hive SQL中的非空查找函数

3、Hive SQL中嵌套的if..else该如何表达?

&

4、Hive SQL中union all的使用

SQL中 union all 有什用?
简而言之:就是把2个具有相同列及数据类型的结果放到一起显示,并且不去重。

什情况下用union?什情况下用union all?
union与union all的区别是:
前者会把两个记录集中相同的记录合并,而后者不会,性能上前者优。当前Hive只支持UNION ALL(bag union)。不能消除重复行,每个select语句返回的列的数量和名字必须一样,否则会抛出语法错误。

5、Hive SQL中字符串切分函数split的使用示例

对于括号「)」这种特殊符号需要进行转义,而且需要多个转义符!

 

 

参考链接:

=END=

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

《Hive SQL学习整理_4》上有14条评论

  1. 纠正一下,对于split中手动指定的字符串参数而言,如果其中包含分号「;」需要进行转义,而非里面的括号「)」

    select split('Mozilla/5.0 (xxx) AppleWebKit/537.36 (xxx)', '\\)')
    /*
    ["Mozilla/5.0 (xxx"," AppleWebKit/537.36 (xxx",""]
    */

    select split('Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (xxx)', '\\)')
    /*
    Statement Intel Mac OS X 10_13_6) AppleWebKit/537.36 (xxx)', '\\)') is not supported
    */
    select split('Mozilla/5.0 (Macintosh\; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (xxx)', '\\)')
    /*
    ["Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6"," AppleWebKit/537.36 (xxx",""]
    */

  2. Hive 的字符串UrlDecode 中文解码
    http://blog.dezai.cn/article.asp?id=660

    # 方法一
    select reflect("java.net.URLDecoder", "decode", trim(字段名), "UTF-8") from 库名.表名 limit 10;

    select reflect("java.net.URLDecoder", "decode", trim('https%3A%2F%2Fixyzero.com%2Fblog%2Fhi+world'), "UTF-8")
    -- https://ixyzero.com/blog/hi world

    select reflect("java.net.URLDecoder", "decode", 'https%3A%2F%2Fixyzero.com%2Fblog%2Fhi+world', "UTF-8")
    -- https://ixyzero.com/blog/hi world

    # 方法二
    自构建一个UDF函数,需要继承UDF,实现其evaluate()方法。

    HOW TO DECODE URLS IN HIVE
    http://bigdatums.net/2016/11/13/how-to-decode-urls-in-hive/

    Decode Raw URL in Hadoop Hive, prefer non-Java solutions
    https://stackoverflow.com/questions/12291580/decode-raw-url-in-hadoop-hive-prefer-non-java-solutions

  3. 单步快速字符串截取
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions

    select substring_index('www.apache.org', '.', 2),
    substring_index('1.2.3.4', '.', 3),
    substring_index('1.2.3.4', 'x', 3);

    -- 'www.apache' '1.2.3' '1.2.3.4'

    -- substring_index(string A, string delim, int count)
    -- 返回字符串A中,第count个分隔符delim所在位置和字符串开头之间的子串内容;如果没有找到分隔符delim,就返回原始字符串内容。(此功能从1.3.0版本开始支持)
    -- Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Substring_index performs a case-sensitive match when searching for delim. Example: substring_index('www.apache.org', '.', 2) = 'www.apache'.

  4. 对于文章中的第5点,可以使用substring_index进行相同功能的实现,记录一下

    select split('python-requests/2.9.1', '\\)')[0],
    substring_index('python-requests/2.9.1', '(', 1),
    substring_index('Mozilla/5.0 (iPhone', '(', 1);
    -- 'python-requests/2.9.1' 'python-requests/2.9.1' 'Mozilla/5.0'

    select split('python-requests/2.9.1', '\\)')[0],
    substring_index('python-requests/2.9.1', '(', 1),
    substring_index('Mozilla/5.0 (iPhone)', '(', 1),
    substring_index('Mozilla/5.0 (iPhone)', ')', 1);
    -- 'python-requests/2.9.1' 'python-requests/2.9.1' 'Mozilla/5.0' 'Mozilla/5.0 (iPhone'

  5. Hive SQL中的正则表达式语法及其使用
    https://stackoverflow.com/questions/46060475/regular-expression-in-hive
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

    hive 正则表达式详解
    https://blog.csdn.net/bitcarmanlee/article/details/51106726

    select '密码:ABCD-1234-EFGH-98DA ' as raw,
    regexp_extract('密码:ABCD-1234-EFGH-98DA ', '密码:([a-zA-Z0-9\\-]{2,})\\s+', 0) as passwd,
    regexp_extract('密码:ABCD-1234-EFGH-98DA ', '密码:([a-zA-Z0-9\\-]{2,})\\s+', 1) as passwd2,
    regexp_extract('密码:ABCD-1234-EFGH-98DA ', '密码:([a-zA-Z0-9\\-]{2,})\\s+') as passwd3,
    regexp_extract('密码:ABCD-1234-EFGH-98DA ', '密码:(.+?)\\s+') as passwd4,
    regexp_extract('密码:ABCD-1234-EFGH-98DA ', '密码:(.*?)\\s+', 0) as passwd5
    ;
    /*
    密码:ABCD-1234-EFGH-98DA 密码:ABCD-1234-EFGH-98DA ABCD-1234-EFGH-98DA ABCD-1234-EFGH-98DA ABCD-1234-EFGH-98DA 密码:ABCD-1234-EFGH-98DA
    */
    空格 要用 \\s 来表示,而不是 \s ;
    数字 要用 \\d 来表示,而不是 \d ,用 [0-9] 也行;

    总之就是,Hive中的正则可以用,但是有所区别,区别在于原来的'\'转义,这里变成了双斜杠了'\\'

  6. Hive的正则匹配中文
    https://blog.csdn.net/changzoe/article/details/80251700

    利用Hive的正则匹配中文时需要注意:

    中文的字符集合为[\u4e00-\u9fa5]
    但是hive在hive执行中会被转义,因此需要增加一次java的转义字符才能够正确使用
    例如:
    select '密码:在入职时收到的短信中' rlike '密码:[\\u4e00-\\u9fa5]+',
    regexp_extract('密码:在入职时收到的短信中', '密码[:\\:\\=]{1}([\\u4e00-\\u9fa5]+).*?', 0);

    hive匹配全中文字段
    https://superlxw1234.iteye.com/blog/1622414

  7. hive函数 — split 字符串分割函数
    https://blog.csdn.net/lxpbs8851/article/details/18712407
    https://stackoverflow.com/questions/4065999/does-hive-have-a-string-split-function

    split(str, regex) - Splits str around occurances that match regex
    要注意HQL中的 split 函数的第二个参数是「正则」字符串,可以同时指定多个分隔符进行切分操作,但要注意进行转义,比如:
    split(input, '[\\[\\]]') as output #表示使用 [ 或者 ] 作为分隔符

  8. Hive SQL中如何截取「变长」「特定分隔符」字符串的除最后一段内容之外的内容?

    /*
    比如希望获取原始字符串:
    "a-b-c-d-e-f-g-h"
    希望提取出:
    "a-b-c-d-e-f-g"

    "a-b-c-d-e-f"
    中最后一个、两个分隔符之前的内容,但分隔符的个数不确定,不适于使用 index/locate 等方式先定位再截取,这里最好用正则的方式进行提取。
    */

    select
    raw,
    regexp_extract(raw, '(.+?)-([^\\-]{1,})$', 1) as raw1
    from
    (select
    "a-b-c-d-e-f-g-h" as raw
    )tmp
    ;
    -- a-b-c-d-e-f-g-h a-b-c-d-e-f-g

发表评论

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