Hive基础（四）

Hive语法函数

Hive语法

  1. 排序

    1. cluster by 

      1
      2
      # 先分桶再排序

    2. distribute by + sorted by

      1

  2. 正则

    1
    2
    # select * from 表名 where 字段名 rlike "正则表达式"
    在线测试网站： ip138.com/zhengze

    1. 匹配单个字符

      https://www.runoob.com/regexp/regexp-metachar.html

    2. 匹配多个字符

      image-20230915110353223

    3. 匹配开头或结尾

      image-20230915110523146

  3. union、CTE

    1. UNION

      1
      2
      3
      select id,name from test1
      union [all]
      select id,name from test2

    2. CTE

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      # 临时结果集,可以再from中使用
      with 别名 as
      	(select 语句)
      	[别名 as （select 语句）]
      select 语句;

      -- 年龄>=20
      with tmp_age_stu as (select *
                           from stu
      )
      select *
      from tmp_age_stu where age>20;

      -- from 前置
      with tmp_stu as (select *
                       from stu
      )
      from tmp_stu
      select *
      where gender="女" and age>20;
      1. 别名仅在本次查询中可以引用
      2. from可以前置

  4. 抽样、虚拟列

    1. 抽样tablesample

      1
      2
      3
      4
      5
      6
      # 
      select * from 表名 tablesample (bucket xv out of y [on colname字段名|rand()]);
      -- y 表示桶的数量
      -- x 是要抽样的桶编号；colname字段名表示要抽样的列
      -- rand() 表示再整个行中抽取样本而不是整个列
      -- 按照colname字段名分成y桶，抽取其中的x桶

    2. 虚拟列（窗口函数、分区列就是虚拟列）

      1. Hive中有3个虚拟列

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        27
        28
        29
        1. INPUT__FILE__NAME:显示数据行所在的具体文件
        2. BLOCK__OFFSET__INSIDE__FILE:显示数据行所在文件偏移量
        3. ROW__OFFSET__INSIDE__BLOCK:显示数据所在HDFS块的偏移量

        # ---------------------------------------------------------------------------
        查看数据再HDFS块的偏移量设置是否开启
        set hive.exec.rowoffset;

        #  设置开启
        set hive.exec.rowoffset=true;

        # ---------------------------------------------------------------
        select
            orderid,totalmoney,input__file__name,
            BLOCK__OFFSET__INSIDE__FILE
        from tb_orders;

        # ----------------------必须先启动配置信息----------------------------------------
        set hive.exec.rowoffset=true;

        select
            orderid,
            totalmoney,
            ROW__OFFSET__INSIDE__BLOCK
        from tb_orders;

        set hive.exec.rowoffset=false;
        # 查看是否修改为默认？？
        set hive.exec.rowoffset

Hive函数

  1. HIve基础函数

    1. 查看可用函数

      1
      2
      3
      show function;
      -- 查看函数使用方式
      desc function extended 函数名;

    2. 内置函数（built-in Functions）

      1. 数学函数

        1
        2
        -- 获取0-100的随机整数
        select round(rand()*100);

      2. 日期函数

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        -- 字符串
        select concat("hello","world");
        select concat_ws("=","hello","world","python");  -- hello=world=python
        select split("1-23-3423-43","-");   --- ["1","23","3423","43"]
        select length("hello heima"); --11
        select upper("hello world");  -- HELLO WORLD
        select lower("HDSDLSD SLKDASKD"); --hdsdlsd slkdaskd

        select `current_timestamp`();
        select substr("2023-09-15 17:15:32",0,4);

      3. 条件函数

        1
        2
        3
        4
        5
        6
        select `if`(1=1,"男","女");
        select isnull("it");
        select isnotnull("it");
        select isnotnull(null);
        select  nvl(null,"深圳");
        select nvl("guangzou","深圳");

      4. 类型转换函数

        1
        2
        3
        4
        -- cast(参数 as 字段类型)
        select cast(100 as string);
        select cast("100" as int);
        select cast("123" as double);

      5. 数据脱敏函数

        1
        2
        3
        4
        5
        6
        select mask_hash("aaaAAA123");  -- 8e737df4411d9cc159c446f8699826b74f9806e8911d21b5ee911d2617957e6b
        select mask("aaaAAA123");   -- xxxXXXnnn
        select mask_first_n("aaaAAA123",4);  -- xxxXAA123
        select mask_last_n("aaaAAA123",4);  -- aaaAAXnnn
        select mask_show_first_n("aaaAAA123",4);     --aaaAXXnnn
        select mask_show_last_n("aaaAAA123",4); -- xxxXXA123

      6. 其他函数

        1
        2
        3
        4
        5
        6
        select current_database();  -- day10
        select current_user();      -- root
        select version();           -- 3.1.2 r8190d2be7b7165effa62bd21b7d60ef81fb0e4af
        select hash("lcl");         -- 106965
        select md5("charlie");      -- bf779e0933a882808585d19455cd7937
        select sha1("charlie");     -- d8cd10b920dcbdb5163ca0185e402357bc27c265

  2. Hive高阶函数

    1. 用户自定义函数（User-Defined Functions）
      1. UDF （User Defined Functions） 用户定义函数
        1. 一入一出
      2. UDAF (User Defined Table-generating Functions ) 用户定义聚合函数
        1. 多进一出
      3. UDTF (User Defined Aggregate Funtions) 用户定义表生成函数
        1. 一入多出（如：炸裂函数）
        2. 炸裂函数