Hive函数(基本使用)

最新推荐文章于 2024-06-24 22:35:35 发布

大帅帅帅呆呆

最新推荐文章于 2024-06-24 22:35:35 发布

阅读量740

点赞数

CC 4.0 BY-SA版权

分类专栏： hadoop 文章标签： Hive函数

本文链接：https://blog.youkuaiyun.com/weixin_44685655/article/details/97482874

hadoop 专栏收录该内容

7 篇文章

订阅专栏

本文深入探讨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;
    }
}