hive 总结二

作者: 利伊奥克儿 | 来源:发表于2019-07-15 21:48 被阅读0次

    本文参考:黑泽君相关博客
    本文是我总结日常工作中遇到的坑,结合黑泽君相关博客,选取、补充了部分内容。

    查询函数(Hive高级)

    • NVL(cloumn,replace_with)

    如果cloumn为NULL,则NVL函数返回 replace_with 的值;
    否则返回cloumn的值;
    如果两个参数都为NULL,则返回NULL。

    hive> select nvl(a,1) from (select null as a) as  aa;
    1
    Time taken: 0.147 seconds, Fetched: 1 row(s)
    
    hive> select nvl(a,1) from (select 2 as a) as  aa;
    2
    

    • case … when … then … else … end 函数

    select 与 case结合使用最大的好处有两点:
    一是在显示查询结果时可以灵活的组织格式;
    二是有效避免了多次对同一个表或几个表的访问。

    Case具有两种格式:
    简单Case函数
    Case搜索函数

    简单Case函数
    hive> CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
    
    Case搜索函数 
    hive> CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END  
    种方式,可以实现相同的功能,  
    简单Case函数的写法相对比较简洁,但是和Case搜索函数相比,功能方面会有些限制。  
    比如写判断式,或者对多个列判断简单Case函数都有点不方便。   
    在Case函数中Else部分的默认值是NULL
    

    行转列

    CONCAT(string A/col, string B/col, …)
    返回输入字符串连接后的结果,支持任意个输入字符串。

    CONCAT_WS(separator, str1, str2,…)
    它是一个特殊形式的CONCAT()。
    第一个参数是剩余参数间的分隔符;
    分隔符可以是与剩余参数一样的字符串;
    如果分隔符是 NULL,返回值也将为NULL;
    这个函数会跳过分隔符参数后的任何 NULL和空字符串; >分隔符将被加到被连接的字符串之间。

    COLLECT_SET(col)
    函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段
    注意:CONCAT()和CONCAT_WS()都是UDTF函数
    COLLECT_SET()函数类似聚合函数。


    列转行

    • EXPLODE(col)
      将hive一列中复杂的array或者map结构拆分成多行。

    • LATERAL VIEW

    用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
    解释:lateral view用于和split,explode等UDTF函数一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。

    hive> select movie explode(category) from  movie_info;
    上面是错误的。假设能执行的话,得到的是笛卡尔积。
    
    正确写法
    hive> select  movie,  category_name from 
      movie_info  lateral view explode(category) table_tmp as category_name;   
    lateral view对原表的字段进行了侧写,得到侧写表和侧写字段。
    

    像split,explode等UDTF函数,是不能跟原表的字段直接进行查询的,UDTF函数一定要和lateral view联合在一块用。


    • 窗口函数

    OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化(比如在首行 或者最后一行)。
      CURRENT ROW:当前行。
      n PRECEDING:往前n行数据。
      n FOLLOWING:往后n行数据。
      UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING 表示到后面的终点。
      LAG(col,n):往前第n行数据。
      LEAD(col,n):往后第n行数据。
      NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。

    hive> desc  business;
    OK
    name                    string
    orderdate               string
    cost                    int
    
    hive> select * from business;
    OK
    jack    2017-01-01      10
    tony    2017-01-02      15
    jack    2017-02-03      23
    tony    2017-01-04      29
    jack    2017-01-05      46
    jack    2017-04-06      42
    tony    2017-01-07      50
    jack    2017-01-08      55
    mart    2017-04-08      62
    mart    2017-04-09      68
    neil    2017-05-10      12
    mart    2017-04-11      75
    neil    2017-06-12      80
    mart    2017-04-13      94
    
    
    只有一个分区,所有行相加,得一个值
    hive> select * ,sum(cost) over() as sample1 from business ;
    OK
    mart    2017-04-13      94      661
    neil    2017-06-12      80      661
    mart    2017-04-11      75      661
    neil    2017-05-10      12      661
    mart    2017-04-09      68      661
    mart    2017-04-08      62      661
    jack    2017-01-08      55      661
    tony    2017-01-07      50      661
    jack    2017-04-06      42      661
    jack    2017-01-05      46      661
    tony    2017-01-04      29      661
    jack    2017-02-03      23      661
    tony    2017-01-02      15      661
    jack    2017-01-01      10      661
    
    按orderdate排序,只有一个分区,区内数据累加
    hive> select * ,sum(cost) over(order by orderdate)  from business ;
    OK
    jack    2017-01-01      10      10
    tony    2017-01-02      15      25
    tony    2017-01-04      29      54
    jack    2017-01-05      46      100
    tony    2017-01-07      50      150
    jack    2017-01-08      55      205
    jack    2017-02-03      23      228
    jack    2017-04-06      42      270
    mart    2017-04-08      62      332
    mart    2017-04-09      68      400
    mart    2017-04-11      75      475
    mart    2017-04-13      94      569
    neil    2017-05-10      12      581
    neil    2017-06-12      80      661
    
    等效上一个语句
    hive> select * ,sum(cost) over(order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)  from business ;
    OK
    jack    2017-01-01      10      10
    tony    2017-01-02      15      25
    tony    2017-01-04      29      54
    jack    2017-01-05      46      100
    tony    2017-01-07      50      150
    jack    2017-01-08      55      205
    jack    2017-02-03      23      228
    jack    2017-04-06      42      270
    mart    2017-04-08      62      332
    mart    2017-04-09      68      400
    mart    2017-04-11      75      475
    mart    2017-04-13      94      569
    neil    2017-05-10      12      581
    neil    2017-06-12      80      661
    
    按name分区,按orderdate排序,有多个分区,区内数据各自累加
    hive> select * ,sum(cost) over(partition by name order by orderdate)  from business ;
    OK
    jack    2017-01-01      10      10
    jack    2017-01-05      46      56
    jack    2017-01-08      55      111
    jack    2017-02-03      23      134
    jack    2017-04-06      42      176
    mart    2017-04-08      62      62
    mart    2017-04-09      68      130
    mart    2017-04-11      75      205
    mart    2017-04-13      94      299
    neil    2017-05-10      12      12
    neil    2017-06-12      80      92
    tony    2017-01-02      15      15
    tony    2017-01-04      29      44
    tony    2017-01-07      50      94
    
    同上
    hive> select * ,sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)  from business ; 
    
    
    当前行和前一行累积
    hive> select * ,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and CURRENT ROW) from business ;
    OK
    jack    2017-01-01      10      10
    jack    2017-01-05      46      56
    jack    2017-01-08      55      101
    jack    2017-02-03      23      78
    jack    2017-04-06      42      65
    mart    2017-04-08      62      62
    mart    2017-04-09      68      130
    mart    2017-04-11      75      143
    mart    2017-04-13      94      169
    neil    2017-05-10      12      12
    neil    2017-06-12      80      92
    tony    2017-01-02      15      15
    tony    2017-01-04      29      44
    tony    2017-01-07      50      79
    
    前一行、当前行、下一行做累积
    hive> select * ,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) from business ;
    OK
    jack    2017-01-01      10      56
    jack    2017-01-05      46      111
    jack    2017-01-08      55      124
    jack    2017-02-03      23      120
    jack    2017-04-06      42      65
    mart    2017-04-08      62      130
    mart    2017-04-09      68      205
    mart    2017-04-11      75      237
    mart    2017-04-13      94      169
    neil    2017-05-10      12      92
    neil    2017-06-12      80      92
    tony    2017-01-02      15      44
    tony    2017-01-04      29      94
    tony    2017-01-07      50      79
    
    当前行和后续所有行做累积
    hive> select * ,sum(cost) over(partition by name order by orderdate rows between CURRENT ROW and UNBOUNDED FOLLOWING) from business ;
    OK
    jack    2017-01-01      10      176
    jack    2017-01-05      46      166
    jack    2017-01-08      55      120
    jack    2017-02-03      23      65
    jack    2017-04-06      42      42
    mart    2017-04-08      62      299
    mart    2017-04-09      68      237
    mart    2017-04-11      75      169
    mart    2017-04-13      94      94
    neil    2017-05-10      12      92
    neil    2017-06-12      80      80
    tony    2017-01-02      15      94
    tony    2017-01-04      29      79
    tony    2017-01-07      50      50
    
    
    按月份分区
    hive> select *,
        > sum(cost) over(distribute by month(orderdate)) 
        > from business;
    OK
    jack    2017-01-01      10      205
    jack    2017-01-08      55      205
    tony    2017-01-07      50      205
    jack    2017-01-05      46      205
    tony    2017-01-04      29      205
    tony    2017-01-02      15      205
    jack    2017-02-03      23      23
    mart    2017-04-13      94      341
    jack    2017-04-06      42      341
    mart    2017-04-11      75      341
    mart    2017-04-09      68      341
    mart    2017-04-08      62      341
    neil    2017-05-10      12      12
    neil    2017-06-12      80      80
    
    按月份分区(同上)
    hive> select *,
        > sum(cost) over(partition by month(orderdate)) 
        > from business;
    OK
    jack    2017-01-01      10      205
    jack    2017-01-08      55      205
    tony    2017-01-07      50      205
    jack    2017-01-05      46      205
    tony    2017-01-04      29      205
    tony    2017-01-02      15      205
    jack    2017-02-03      23      23
    mart    2017-04-13      94      341
    jack    2017-04-06      42      341
    mart    2017-04-11      75      341
    mart    2017-04-09      68      341
    mart    2017-04-08      62      341
    neil    2017-05-10      12      12
    neil    2017-06-12      80      80
    
    按日期累加 从开始到现在
    hive> select *,
        > sum(cost) over(sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
        > from business;
    OK
    jack    2017-01-01      10      10
    tony    2017-01-02      15      25
    tony    2017-01-04      29      54
    jack    2017-01-05      46      100
    tony    2017-01-07      50      150
    jack    2017-01-08      55      205
    jack    2017-02-03      23      228
    jack    2017-04-06      42      270
    mart    2017-04-08      62      332
    mart    2017-04-09      68      400
    mart    2017-04-11      75      475
    mart    2017-04-13      94      569
    neil    2017-05-10      12      581
    neil    2017-06-12      80      661
    
    按日期累加 某一天和前后一天 统计(当前行和前边一行及后面一行)
    hive> select *,
        > sum(cost) over(sort by orderdate rows between  1 PRECEDING and 1 FOLLOWING)
        > from business;
    OK
    jack    2017-01-01      10      25
    tony    2017-01-02      15      54
    tony    2017-01-04      29      90
    jack    2017-01-05      46      125
    tony    2017-01-07      50      151
    jack    2017-01-08      55      128
    jack    2017-02-03      23      120
    jack    2017-04-06      42      127
    mart    2017-04-08      62      172
    mart    2017-04-09      68      205
    mart    2017-04-11      75      237
    mart    2017-04-13      94      181
    neil    2017-05-10      12      186
    neil    2017-06-12      80      92
    
    按用户 日累积
    hive> select *,
        > sum(cost) over(distribute by name sort by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)
        > from business;
    OK
    jack    2017-01-01      10      10
    jack    2017-01-05      46      56
    jack    2017-01-08      55      111
    jack    2017-02-03      23      134
    jack    2017-04-06      42      176
    mart    2017-04-08      62      62
    mart    2017-04-09      68      130
    mart    2017-04-11      75      205
    mart    2017-04-13      94      299
    neil    2017-05-10      12      12
    neil    2017-06-12      80      92
    tony    2017-01-02      15      15
    tony    2017-01-04      29      44
    tony    2017-01-07      50      94
    
    查询上一次,当前购买时间
    hive> select *,
        > lag(orderdate, 1) over(distribute by name sort by orderdate) ,
        > lead(orderdate, 1) over(distribute by name sort by orderdate)
        > from business;
    OK
    jack    2017-01-01      10      NULL    2017-01-05
    jack    2017-01-05      46      2017-01-01      2017-01-08
    jack    2017-01-08      55      2017-01-05      2017-02-03
    jack    2017-02-03      23      2017-01-08      2017-04-06
    jack    2017-04-06      42      2017-02-03      NULL
    mart    2017-04-08      62      NULL    2017-04-09
    mart    2017-04-09      68      2017-04-08      2017-04-11
    mart    2017-04-11      75      2017-04-09      2017-04-13
    mart    2017-04-13      94      2017-04-11      NULL
    neil    2017-05-10      12      NULL    2017-06-12
    neil    2017-06-12      80      2017-05-10      NULL
    tony    2017-01-02      15      NULL    2017-01-04
    tony    2017-01-04      29      2017-01-02      2017-01-07
    tony    2017-01-07      50      2017-01-04      NULL
    
    查询前20%时间的订单信息
    hive> select *
        > from (select *,
        >        ntile(5) over(order by orderdate) as gid
        >        from business) as t
        > where t.gid=1;
    OK
    jack    2017-01-01      10      1
    tony    2017-01-02      15      1
    tony    2017-01-04      29      1
    
    注: ntile(n) 将有序分区中的行平均分发到指定数据的组中,每个组中记录数量为total/n,  
    那么取前20%就是前五分之一,只要分为五个组,去第一个组即可
    hive> select *,
        > ntile(5) over(sort by orderdate)
        > from business;
    OK
    jack    2017-01-01      10      1
    tony    2017-01-02      15      1
    tony    2017-01-04      29      1
    jack    2017-01-05      46      2
    tony    2017-01-07      50      2
    jack    2017-01-08      55      2
    jack    2017-02-03      23      3
    jack    2017-04-06      42      3
    mart    2017-04-08      62      3
    mart    2017-04-09      68      4
    mart    2017-04-11      75      4
    mart    2017-04-13      94      4
    neil    2017-05-10      12      5
    neil    2017-06-12      80      5
    

    rank函数

    RANK():排序相同时会重复,总数不会变。(两个100分为列第一名和第二名,99分的为第三名)
    DENSE_RANK():排序相同时会重复,总数会减少。(两个100分并列第一,99分的为第二名)
    ROW_NUMBER():会根据顺序计算。
    注意:使用rank函数需要配合over函数(窗口函数)使用

    准备数据
    vim score.txt
    李白,语文,100
    李白,数学,90
    李白,天文,85
    屈原,语文,100
    屈原,数学,85
    屈原,天文,90
    诸葛亮,语文,95
    诸葛亮,数学,99
    诸葛亮,天文,110
    祖冲之,语文,90
    祖冲之,数学,100
    祖冲之,天文,90
    
    
    hive> create table score(
        > name string,
        > subject string,
        > score int)
        > row format delimited fields terminated by ",";
    OK
    hive> load data local inpath '/root/tmp_lillcol/score.txt' into table score;
    Loading data to table iptv.score
    Table iptv.score stats: [numFiles=1, totalSize=226]
    OK
    Time taken: 0.325 seconds
    hive> select * from score;
    OK
    李白    语文    100
    李白    数学    90
    李白    天文    85
    屈原    语文    100
    屈原    数学    85
    屈原    天文    90
    诸葛亮  语文    95
    诸葛亮  数学    99
    诸葛亮  天文    110
    祖冲之  语文    90
    祖冲之  数学    100
    祖冲之  天文    90
    
    根据学科排名,排序相同时会重复,总数不会变。
    hive> select *,
        > rank() over(partition by subject order by score desc)
        > from score;
    OK
    诸葛亮  天文    110     1
    祖冲之  天文    90      2
    屈原    天文    90      2
    李白    天文    85      4
    祖冲之  数学    100     1
    诸葛亮  数学    99      2
    李白    数学    90      3
    屈原    数学    85      4
    屈原    语文    100     1
    李白    语文    100     1
    诸葛亮  语文    95      3
    祖冲之  语文    90      4
    Time taken: 19.103 seconds, Fetched: 12 row(s)
    
    根据学科排名,排序相同时会重复,总数会减少。
    hive> select *,
        > dense_rank()over(partition by subject order by score desc)
        > from score;
    OK
    诸葛亮  天文    110     1
    祖冲之  天文    90      2
    屈原    天文    90      2
    李白    天文    85      3
    祖冲之  数学    100     1
    诸葛亮  数学    99      2
    李白    数学    90      3
    屈原    数学    85      4
    屈原    语文    100     1
    李白    语文    100     1
    诸葛亮  语文    95      2
    祖冲之  语文    90      3
    
    根据学科排名,返回的是行号,分数相同排序不同
    hive> select *,
        > row_number() over(partition by subject order by score desc)
        > from score;
    OK
    诸葛亮  天文    110     1
    祖冲之  天文    90      2
    屈原    天文    90      3
    李白    天文    85      4
    祖冲之  数学    100     1
    诸葛亮  数学    99      2
    李白    数学    90      3
    屈原    数学    85      4
    屈原    语文    100     1
    李白    语文    100     2
    诸葛亮  语文    95      3
    祖冲之  语文    90      4
    

    函数
    查看系统自带的函数
    hive> show functions;
    OK
    !
    !=
    %
    &
    *
    +
    -
    /
    <
    <=
    <=>
    <>
    =
    ==
    >
    >=
    ^
    abs
    acos
    add_months
    and
    array
    array_contains
    ascii
    asin
    assert_true
    atan
    avg
    base64
    between
    bin
    case
    cbrt
    ceil
    ceiling
    coalesce
    collect_list
    collect_set
    compute_stats
    concat
    concat_ws
    context_ngrams
    conv
    corr
    cos
    count
    covar_pop
    covar_samp
    crc32
    create_union
    cume_dist
    current_database
    current_date
    current_timestamp
    current_user
    date_add
    date_format
    date_sub
    datediff
    day
    dayofmonth
    dayofweek
    decode
    degrees
    dense_rank
    div
    e
    elt
    encode
    ewah_bitmap
    ewah_bitmap_and
    ewah_bitmap_empty
    ewah_bitmap_or
    exp
    explode
    field
    find_in_set
    first_value
    floor
    format_number
    from_unixtime
    from_utc_timestamp
    get_json_object
    greatest
    hash
    hex
    histogram_numeric
    hour
    if
    in
    in_file
    index
    initcap
    inline
    instr
    isnotnull
    isnull
    java_method
    json_tuple
    lag
    last_day
    last_value
    lcase
    lead
    least
    length
    levenshtein
    like
    ln
    locate
    log
    log10
    log2
    logged_in_user
    lower
    lpad
    ltrim
    map
    map_keys
    map_values
    matchpath
    max
    md5
    min
    minute
    month
    months_between
    named_struct
    negative
    next_day
    ngrams
    noop
    noopstreaming
    noopwithmap
    noopwithmapstreaming
    not
    ntile
    nvl
    or
    parse_url
    parse_url_tuple
    percent_rank
    percentile
    percentile_approx
    pi
    pmod
    posexplode
    positive
    pow
    power
    printf
    radians
    rand
    rank
    reflect
    reflect2
    regexp
    regexp_extract
    regexp_replace
    repeat
    reverse
    rlike
    round
    row_number
    rpad
    rtrim
    second
    sentences
    sha2
    sign
    sin
    size
    sort_array
    soundex
    space
    split
    sqrt
    stack
    std
    stddev
    stddev_pop
    stddev_samp
    str_to_map
    struct
    substr
    substring
    sum
    tan
    to_date
    to_unix_timestamp
    to_utc_timestamp
    translate
    trim
    trunc
    ucase
    unbase64
    unhex
    unix_timestamp
    upper
    uuid
    var_pop
    var_samp
    variance
    version
    weekofyear
    when
    windowingtablefunction
    xpath
    xpath_boolean
    xpath_double
    xpath_float
    xpath_int
    xpath_long
    xpath_number
    xpath_short
    xpath_string
    year
    |
    ~
    
    
    显示自带函数用法
    hive> desc function extended  minute;
    OK
    minute(date) - Returns the minute of date
    date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.
    Example:
       > SELECT minute('2009-07-30 12:58:59') FROM src LIMIT 1;
      58
      > SELECT minute('12:58:59') FROM src LIMIT 1;
      58
    
    简单显示
    hive> desc function minute;
    minute(date) - Returns the minute of date
    
    

    自定义函数

    Hive自定义函数包括三种UDF、UDAF、UDTF
      UDF(User-Defined-Function) 一进一出
      UDAF(User- Defined Aggregation Funcation)聚集函数,多进一出。Count/max/min
      UDTF(User-Defined Table-Generating Functions) 一进多出,如lateral view explore()
      使用方式 :在HIVE会话中add自定义函数的jar文件,然后创建function继而使用函数

    UDF(User-Defined-Function) 一进一出
    
    依赖:
    sbt:
    libraryDependencies += "org.apache.hive" % "hive-exec" % "1.2.1"  
    
    maven:  
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>1.2.1</version>
        </dependency>
    </dependencies>
    
    关键代码:  
    import org.apache.hadoop.hive.ql.exec.UDF;
    /**
     * @author lillcol
     * 2019/7/15-17:56
     */
    public class MyUDF extends UDF {
        public int evaluate(String str) {
            try{
                int n = Integer.parseInt(str);
                return n*n;
            }catch(Exception e){
                e.printStackTrace();
                return -1;
            }
        }
    }
    
    上述代码打包为udf.jar
    
    将jar包添加到hive的class path  
    hive> add jar /root/tmp_lillcol/udf.jar;
    Added [/root/tmp_lillcol/udf.jar] to class path
    Added resources: [/root/tmp_lillcol/udf.jar]
    
    创建临时函数与开发好的java class关联  
    hive> create temporary function square as "com.iptv.test.MyUDF";
    OK
    hive> select * ,square(score) from score ;
    OK
    李白    语文    100     10000
    李白    数学    90      8100
    李白    天文    85      7225
    屈原    语文    100     10000
    屈原    数学    85      7225
    屈原    天文    90      8100
    诸葛亮  语文    95      9025
    诸葛亮  数学    99      9801
    诸葛亮  天文    110     12100
    祖冲之  语文    90      8100
    祖冲之  数学    100     10000
    祖冲之  天文    90      8100
    
    

    相关文章

      网友评论

        本文标题:hive 总结二

        本文链接:https://www.haomeiwen.com/subject/janokctx.html