Hive函数(基本使用)

本文深入探讨Hive SQL的高级特性,包括数据插入、导出、查询优化、视图创建、分桶与分区策略、数据倾斜解决方案及窗口函数应用。通过实例解析,帮助读者掌握Hive在大数据处理中的高效使用技巧。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Hive

  • 插入(装载)数据

    • 数据在本地或者hdfs中:load data [local] inpath ‘pathname’ [overwrite] into table tablename [partition(column=xxx)]

    • insert方法:insert into tablename select xx,xx,xxx,xxx,[union all] select xxx,xxx,xx,xxx;

      from table_name insert overwrite table table_name [partition(xx,xx)] select xx,xx,xx; 典型的ETL模式

      insert into tablename values(coulmmn1 xxx,coulmn2 xx); 指定列插入 ////

      insert into tablename(name) value(‘Judy’) 指定值插入

    • 发现插入:with t as(

    select 5,‘mm’,‘1994-1-5’,array(‘study’)[union all] select xxx,xxx,xxx,

    )

    insert into emps select * from t

  • 导出数据

    export table table_name [partition(xx=xx,xx=xx)] to ‘/root/output’;

  • 创建表

    create table tablename as select * from xxx; CTS

    create table tablename as with 【结果集】 CTE

    create table tablename like tablename; 复制表结构不复制内容

  • row format serde ‘org.apache.hadoop.hive.serde2.OpenCSVSerde’ 把csv导入
    location ‘/shopping/’ 导入的是文件夹,只能放一个文件
    tblproperties (“skip.header.line.count”=“1”) 跳过首行

  • 查询数据

    单查 select xx from tablename where xxx;

    子查询:with a as(select * from tablename where xxx) select * from a;

    ​ : select * from (select * from xxx)a;

  • 删除表

    drop table if exists employee[with perge]------------------>with perge 直接删除【可选】否则会放在.Trash目录

  • alter table tabe_name rename to new_table_name; 更改表名

  • alter table table_name change old_name new_name string; 更改列名

  • alter table table_name add columns(work string); 添加列名

  • alter table table_name replace columns(name string); 替换列名

  • 静态分区(分区:提高查询性能)理解为文件夹

    创建表时添加分区字段 patitioned by(column,xxx) 此coulmn 在表中不存在

    alter table students add partition(sex=‘m’) partition(sex=‘f’);

    show partitions table_name;

    insert into students partition(sex=‘m’) select 1,‘zs’;

  • 动态分区 (分区:提高查询性能)

    set hive.exec.dynamic.partition=true;
    set hive.exec.dynamic.partition.mode=nonstrict;

    insert into students partition(sex) select 1,‘zs’,‘m’ as sex;

  • 分桶 (按照分桶字段的哈希值取模除以分桶的个数) 理解为文件

    好处:1 方便取样 2 提高join查询效率

    创建表 clustered by (dept) into 3 buckets 按字段分桶 此字段在表中存在

    set hive.enforce.bucketing=true; //让hive强制分桶,自动按照分桶表的bucket 进行分桶。

    添加数据自动分桶

    基于桶的抽样:select * from table_name tablesmple(bucket 1 out of 3 on xx);

    -------------------取样并不是一个精确的操作,因此这个 比例不一定要是桶数的整数倍---------------------

  • 视图 相当于索引)创建视图create view view_name as select xxx ; 删除 drop view xxx;

    create view vw_customer_details as
    base64(binary(last_name)) as last_name ,
    //select unbase64(base64(binary(store_name))) from ext_store_details base64解码
    MD5(credit_no) as credit_no
    from ext_customer_details

  • 侧视图–lateral view 意义:配合explode ,一个语句生成把单行数据拆解成多行后的数据结果集 。

  • explode 作用是处理map结构的字段

    行转列

    select name,myscore from xx lateral view explode(split(score,’,’)) x as myscore;

    select split(score,’,’) from xx;字符串分割为数组

    列转行

    select id,
    max(if(course='a ',1,0))a,
    max(if(course='b ',1,0))b,
    max(if(course='c ',1,0))c,
    max(if(course='d ',1,0))d,
    max(if(course='e ',1,0))e,
    max(if(course='f ',1,0))f
    from t_course group by id;

  • coalesce 相当于mysql中 ifnull

    ==select nvl(null,1) 如果null是具体的数就返回具体的数。

    insert into xx select name,coalesce(chinese,0),coalesce(math,0),coalesce(english,0) from sss

    --------------------------------先更改配置文件---------------------创建内部事务分桶表----------------------------

    插入操作------>更新操作
    
  • 列转行 主要collect_list

  • INPUT__FILE__NAME:Mapper Task的输入文件名称

  • BLOCK__OFFSET__INSIDE__FILE:当前全局文件位置

  • select b.userid,if(trantype=1,tranmoney,-tranmoney) from bankcards b;

  • select * from userinfos u where not exists(select b.userid from bankcards b )where u.userid=b.userid group by b.userid); 不存在也要内外关联 在hive中没有not in

  • insert overwrite table table_name select xxx; 加载数据

  • 数据倾斜情况以及解决

    数据倾斜:数据的分散度不够,数据集中在一台或者几台计算机上计算,计算时间远超过平均计算速度,导致整个计算过程过慢。

    hive 中数据倾斜,一般都发生在sql中group和on上,而且数据逻辑绑定比较深。

    解决思路:调节参数和调节sql语句

    1.当遇到大小表关联时:
    在hadoop中可以通过将小表加载到缓存 这种方式就是mapjoin方式
    在hive中可以设置 mapjoin set hive.auto.convert.join=true 阈值25M


    2.部分数据相对特别少
    在mapreduce中 partition 部分自定义哈希
    在hive中partition 自定义分区

    3.当遇到很多小数据表时:
    hive中带来很多的参数和机制来调节数据倾斜
    set mapred.max.split.size=100000000;
    set mapred.min.split.size=100000000;
    set mapred.min.split.size.per.node=100000000;
    set mapred.min.split.size.per.rack=100000000;
    set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

  • order by排序 对全局数据进行排序,只有一个reduce工作,速度慢

    select * from offers order by case when offerid = 1 then 1 else 0 end;

  • sort by/distribute by

    sort by 只能保证对每个reduce里的字段有序。 可以指定reduce个数 set mapred.reduce.tasks=n 来指定,对输出的数据再执行归并排序。

    distribute by 类似于sql中的group by 确保具有匹配列值的行被分区到相同的reducer,不会对每个reducer的输出进行排序。是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列,对应reduce的个数进行分发,默认是采用hash算法。

    SELECT department_id , name, employee_id, evaluation_scoreFROM employee_hr DISTRIBUTE BY department_id SORT BY evaluation_score DESC;

  • hive的优化

    博客园 知名hive 博主 :扎心了老铁 https://www.jianshu.com/p/daa4e7c86925

  • Hive 聚合

    • group by
    • Having :对GROUP BY聚合结果的条件过滤
  • 基础聚合 :

    • 使用内置函数进行数据聚合 比如:max(distinct col)、avg(distinct col)等

      select count(null) = 0

      count(*)    所有值不全为NULL时,加1操作
      count(1)    不管有没有值,只要有这条记录,值就加1
      count(col)  col列里面的值为null,值不会加1,这个列里面的值不为NULL,才加1
      
  • 高级聚合

    • grouping sets 实现对同一数据集进行多重GROUP BY操作

      SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b GROUPING SETS ( (a, b), a, b, ( ) )

    • GROUP BY WITH CUBE|ROLLUP

      cube:对分组列进行所有可能组合的聚合

  • 窗口函数

    row_number 会生成一列连续的序号,rank()函数出现1 1 3 dense_rank()则出现1 1 2这样的序号

    lag是迟滞的意思,也就是对某一列进行往后几行,lead是对某一列提前几行

    select username,dept,score, row_number() over() from userinfos order by dept,score

  • select userid,username,dept,score,row_number() over(partition by dept order by score) from userinfos 不同分区中的升序

    在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by  order by 的执行。 如果没有分组hive随机分组而不是默认为1

  • 行窗口和范围窗口

  • UDF函数

    maven导入jar包-------------hive-common和 hive-exec

继承UDF类 创建evaluate方法
public class SayHello extends UDF {
    public String evaluate(String msg){
        return "Hello,"+msg;
    }
}
打成jar包 上传到hdfs
创建UDF函数 create [temporary] function [functino_name] as 'class_path' using jar 'hdfs:///xxx/xx.jar'
  • GenericUDF
public class SearchArray extends GenericUDF {
    //准备三个工具
    private ListObjectInspector array;
    private ObjectInspector singleEle;
    private ObjectInspector second;
  private IntWritable result=new IntWritable(-1);

    /**
     * 设置本函数的返回值类型
     * @param
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] args) throws UDFArgumentException {
       //对第一个参数强转成集合
        array =(ListObjectInspector) args[0];
        //获得集合中单个元素
        singleEle = array.getListElementObjectInspector();
        //获得第二个参数
        second = args[1];
        return PrimitiveObjectInspectorFactory.writableIntObjectInspector;
    }

    @Override
    public Object evaluate(DeferredObject[] args) throws HiveException {
    this.result.set(-1);
    Object arr = args[0].get();
    Object val = args[1].get();
    int len = array.getListLength(arr);
        for (int i = 0; i < len; i++) {
           Object ele= array.getListElement(arr,i);
           if (ele!=null){
               if (ObjectInspectorUtils.compare(val,second,ele,singleEle)==0){
                       this.result.set(1);
                   break;
               }
           }
        }
        return result;
    }
    //说明文件
    @Override
    public String getDisplayString(String[] strings) {
        return null;
    }
}
一、关系运算: 4 1. 等值比较: = 4 2. 不等值比较: 4 3. 小于比较: < 4 4. 小于等于比较: 5 6. 大于等于比较: >= 5 7. 空值判断: IS NULL 5 8. 非空判断: IS NOT NULL 6 9. LIKE比较: LIKE 6 10. JAVA的LIKE操作: RLIKE 6 11. REGEXP操作: REGEXP 7 二、数学运算: 7 1. 加法操作: + 7 2. 减法操作: - 7 3. 乘法操作: * 8 4. 除法操作: / 8 5. 取余操作: % 8 6. 位与操作: & 9 7. 位或操作: | 9 8. 位异或操作: ^ 9 9.位取反操作: ~ 10 三、逻辑运算: 10 1. 逻辑与操作: AND 10 2. 逻辑或操作: OR 10 3. 逻辑非操作: NOT 10 四、数值计算 11 1. 取整函数: round 11 2. 指定精度取整函数: round 11 3. 向下取整函数: floor 11 4. 向上取整函数: ceil 12 5. 向上取整函数: ceiling 12 6. 取随机数函数: rand 12 7. 自然指数函数: exp 13 8. 以10为底对数函数: log10 13 9. 以2为底对数函数: log2 13 10. 对数函数: log 13 11. 幂运算函数: pow 14 12. 幂运算函数: power 14 13. 开平方函数: sqrt 14 14. 二进制函数: bin 14 15. 十六进制函数: hex 15 16. 反转十六进制函数: unhex 15 17. 进制转换函数: conv 15 18. 绝对值函数: abs 16 19. 正取余函数: pmod 16 20. 正弦函数: sin 16 21. 反正弦函数: asin 16 22. 余弦函数: cos 17 23. 反余弦函数: acos 17 24. positive函数: positive 17 25. negative函数: negative 17 五、日期函数 18 1. UNIX时间戳转日期函数: from_unixtime 18 2. 获取当前UNIX时间戳函数: unix_timestamp 18 3. 日期转UNIX时间戳函数: unix_timestamp 18 4. 指定格式日期转UNIX时间戳函数: unix_timestamp 18 5. 日期时间转日期函数: to_date 19 6. 日期转年函数: year 19 7. 日期转月函数: month 19 8. 日期转天函数: day 19 9. 日期转小时函数: hour 20 10. 日期转分钟函数: minute 20 11. 日期转秒函数: second 20 12. 日期转周函数: weekofyear 20 13. 日期比较函数: datediff 21 14. 日期增加函数: date_add 21 15. 日期减少函数: date_sub 21 六、条件函数 21 1. If函数: if 21 2. 非空查找函数: COALESCE 22 3. 条件判断函数:CASE 22 4. 条件判断函数:CASE 22 七、字符串函数 23 1. 字符串长度函数:length 23 2. 字符串反转函数:reverse 23 3. 字符串连接函数:concat 23 4. 带分隔符字符串连接函数:concat_ws 23 5. 字符串截取函数:substr,substring 24 6. 字符串截取函数:substr,substring 24 7. 字符串转大写函数:upper,ucase 24 8. 字符串转小写函数:lower,lcase 25 9. 去空格函数:trim 25 10. 左边去空格函数:ltrim 25 11. 右边去空格函数:rtrim 25 12. 正则表达式替换函数:regexp_replace 26 13. 正则表达式解析函数:regexp_extract 26 14. URL解析函数:parse_url 26 15. json解析函数:get_json_object 27 16. 空格字符串函数:space 27 17. 重复字符串函数:repeat 27 18. 首字符ascii函数:ascii 28 19. 左补足函数:lpad 28 20. 右补足函数:rpad 28 21. 分割字符串函数: split 28 22. 集合查找函数: find_in_set 29 八、集合统计函数 29 1. 个数统计函数: count 29 2. 总和统计函数: sum 29 3. 平均值统计函数: avg 30 4. 最小值统计函数: min 30 5. 最大值统计函数: max 30 6. 非空集合总体变量函数: var_pop 30 7. 非空集合样本变量函数: var_samp 31 8. 总体标准偏离函数: stddev_pop 31 9. 样本标准偏离函数: stddev_samp 31 10.中位数函数: percentile 31 11. 中位数函数: percentile 31 12. 近似中位数函数: percentile_approx 32 13. 近似中位数函数: percentile_approx 32 14. 直方图: histogram_numeric 32 九、复合类型构建操作 32 1. Map类型构建: map 32 2. Struct类型构建: struct 33 3. array类型构建: array 33 十、复杂类型访问操作 33 1. array类型访问: A[n] 33 2. map类型访问: M[key] 34 3. struct类型访问: S.x 34 十一、复杂类型长度统计函数 34 1. Map类型长度函数: size(Map) 34 2. array类型长度函数: size(Array) 34 3. 类型转换函数 35
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值