Hive函数使用方法

小球-大数据

于 2024-08-28 09:00:00 发布

阅读量2.6k

点赞数 57

文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/weixin_54462642/article/details/141602972

版权

窗口函数

over(partition by  分组  order by 排序  rows between  起始行  and  结束行)

-- 向上无限制
unbounded preceding
-- 指定向上的行数
行数  preceding
-- 当前行
current row
-- 向下几行
1 following 
-- 向下无限制
unbounded following

配合聚合函数使用

聚合方法  over()

sum
avg
count()
max
min

配合取值函数一起使用

取值函数  over()
取值函数  over(order by 字段)

lag(字段,间隔行,默认值) 向上找数据
- 间隔行，间隔几行取值默认是1行可选
- 默认值，如果取不到值使用默认值可选
lead(字段,间隔行,默认值) 向下找数据
frist_value 取第一个值
last_value 取最后一个值

select name ,
       lag(name) over () as l1,
       lag(name,3) over () l2,
       lag(name,3,'itcast') over () l3,
       lead(name) over () lead1,
       lead(name,3) over () lead2,
       lead(name,3,'itheima') over () lead3,
       first_value(name) over() f1,
       last_value(name) over() last1
from tb_stu5;

配合生成序号函数一起使用

序号函数 over(order by 字段)

rank
- 生成不连续序号
desen_rank
- 生成连续序号
row_number
- 生成行号

select age,
       rank() over (order by age) as rk,
       dense_rank() over (order by age  ) as drk,
       row_number() over (order by age) as rn
       from tb_stu5;

CTE语法

将子查询转为一张表，用来取代子查询操作，方便分步计算

with 表名1 as(第一步计算查询语句),
     表名2 as(第二步计算查询语句)
select  * from  表名1 join 表名2


with 表名1 as(第一步计算查询语句),
     表名2 as(第二步计算查询语句)
select  * from  表名1 join 其他表


with 表名1 as(第一步计算查询语句),
     表名2 as(第二步计算查询语句  select  * from  表1)
select  * from  表名2


# CTE语法的数据写入新表
with 表名1 as(第一步计算查询语句),
     表名2 as(第二步计算查询语句  select  * from  表1)
insert into  tb_new     
select  * from  表名2

对年龄排序后取top3年龄

-- CTE语法使用
with tb1 as(
    -- 1-对年龄排序生成序号
    select age,
       dense_rank() over (order by age  ) as drk
       from tb_stu5
)
-- 2-筛序序号前三名
select * from tb1 where drk <=3;

-- 保存计算结果
create table  tb_result(
    age int,
    rn int
);

with tb1 as(
    -- 1-对年龄排序生成序号
    select age,
       dense_rank() over (order by age  ) as drk
       from tb_stu5
)
insert into tb_result
-- 2-筛序序号前三名
select * from tb1 where drk <=3;

一、爆炸函数和合并函数

explode方法
- 爆炸函数，可以将数组中的数据拆分多行

-- lateral view explode( split(hobby,'-')) t1 as data;
--  lateral view 侧视图
-- explode( split(hobby,'-'))需要爆炸处理的字段  t1 相当于表名  data 字段名
select id,name,hobby,data from tb_stu5 lateral view explode( split(hobby,'-')) t1 as data;

collect方法
- 将一列数据中的多行数据合并成一行
- collect_set 会数据去重
- collect_list 不去重

-- 数据合并
select gender,
collect_set(age),
collect_list(age) 
from tb_stu5 group by gender;

二、随机抽样

LanguageManual Sampling - Apache Hive - Apache Software Foundation

从海量数据中随机抽取部分样本数据进行计算得到的结果趋势和整体趋势一致

格式

SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))

y表示将表数据随机划分成y份（y个桶）
x表示取第几桶
colname表示随机的依据基于某个列的值
rand()表示随机的依据基于整行

随机抽样的原理，是先将数据进行分桶，在从多个分桶中抽取数据

-- 随机抽样
-- 如果表不是分桶表 需要指定on的分桶字段
-- 随机采样过程 hash(cls)%3 余数相同的数据会放在一个桶
-- 1 取第几桶
select * from tb_stu5 tablesample ( bucket 1 out of 3 on cls);
-- 如果指定了分桶字段，每次取出的值都是一样的
-- 使用rand()可以随机从桶中取值
select * from tb_stu5 tablesample ( bucket 1 out of 3 on rand());




-- 如果是分桶表  不需要指定on, 按照建表时指定的分桶字段进行分桶
-- 分桶表不需要执行hash取余的分桶计算，直接从已经分好桶的文件中取值，取值速度快
select * from tb_user_bucket tablesample ( bucket 1 out of 3);
-- 从分桶中随机取值
select * from tb_user_bucket tablesample ( bucket 1 out of 3 on rand());


-- 按照文件块取值
-- 指定行数
select * from tb_stu5 tablesample ( 10 rows );
-- 指定大小
select * from tb_stu5 tablesample ( 1b );

三、虚拟列

LanguageManual VirtualColumns - Apache Hive - Apache Software Foundation

hive表中自带字段列，在进行select查询时没有指定，不会出现在查询结果中

可以在select中指定这些字段显示内容

INPUT__FILE__NAME，显示数据行所在的具体文件
BLOCK__OFFSET__INSIDE__FILE，显示数据行所在文件的偏移量
ROW__OFFSET__INSIDE__BLOCK，显示数据所在HDFS块的偏移量 
    此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用

select *,INPUT__FILE__NAME from brand;
select * from brand where INPUT__FILE__NAME='hdfs://node1:8020/user/hive/warehouse/pydata.db/brand/000001_0';

select *,BLOCK__OFFSET__INSIDE__FILE from tb_stu;
SET hive.exec.rowoffset=true;
select *,ROW__OFFSET__INSIDE__BLOCK from tb_stu;

四、快速建表

基于已经存在的表创建新的表，对原始表复制一个新的表

LanguageManual DDL - Apache Hive - Apache Software Foundation

like语法
- 将原始表的元数据(也就是表的名字字段等信息复制一份)，不会复制行数据
- 创建之后是一个空表

create table 新的表名 like 原始表名

as语法
- 会将原始数据表的内容全部复制一份到新表中

create table 新的表名 as select * from 原始表

-- 使用like语法  相当于复制一个已经存在的表结构,不会复制表的行数据
create table tb_stu6 like tb_stu5;
select * from tb_stu6;

-- 使用as语法  将查询的结果直接创建成一张表，会自定识别表中的数据类型，在进行表创建时会将数据写入hdfs
create table tb_stu7 as select * from tb_stu5;
select * from tb_stu7;

五、视图

视图本质是将select查询语句进行保存，每次进行数据计算时，如果使用相同的sql语句，就不需要再重新写一遍

LanguageManual DDL - Apache Hive - Apache Software Foundation

create view 视图名  as 查询语句

-- 视图
-- 如果经常需要使用某个查询语句，可以将该查询语句保存到视图中
create view sum_gender as select gender,sum(age) as data from tb_stu5 group by gender;

-- 当查询视图时就是执行sql语句
select * from sum_gender;


drop view sum_gender;

创建视图时名字不能重复

六、数据压缩和存储格式

LanguageManual DDL - Apache Hive - Apache Software Foundation

6-1 数据压缩

hive的表的行数据是以文件方式存在hdfs

优点：

减少存储磁盘空间，降低单节点的磁盘IO。

由于压缩后的数据占用的带宽更少，因此可以加快数据在Hadoop集群流动的速度，减少网络传输带宽。

缺点：

需要花费额外的时间/CPU做压缩和解压缩计算。

压缩格式	压缩格式所在的类
Zlib	org.apache.hadoop.io.compress.DefaultCodec
Gzip	org.apache.hadoop.io.compress.GzipCodec
Bzip2	org.apache.hadoop.io.compress.BZip2Codec
Lzo	com.hadoop.compression.lzo.LzoCodec
Lz4	org.apache.hadoop.io.compress.Lz4Codec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

默认文件的压缩方式是Zlib，可以在建表的时候指定表数据按照那种压缩方式存储数据，zlib压缩的占用空间少，但是消耗的时间

实际开发建议使用Snappy 压缩空间和速度比较均衡

不同压缩格式使用的压缩算法不一样

区别别在压缩的大小和压缩时间

体积越小，需要的压缩时间就越长，在选择压缩方式时要两者兼顾

实际开发中会选择Snappy

压缩方式需要再建表是，在表的属性中指定

6-2 存储格式

表数据存储方式有两种

一个行存储一个列存储

行存储方式方便数据的写入

列存储方式方便数据的读取

逻辑表中的数据，最终需要落到磁盘上，以文件的形式存储，有两种常见的存储形式。行式存储和列式存储。

Hive支持的存储数的格式主要有：TEXTFILE（行式存储）、SEQUENCEFILE(行式存储)、ORC（列式存储）、PARQUET（列式存储）。

默认的存储格式是TEXTFILE（行式存储)

列存储的数据会转为二进制存储，所以文件打开后乱码

# 建表是指定
STORED AS 存储格式  tblproperties(指定压缩格式)
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

-- 创建表指定存储格式和压缩方式
create table tb_movie2(
    movie string,
    rate string,
    dt string,
    uid string
)stored as orc tblproperties("orc.compress"="SNAPPY");

-- 在写入数据时就会按照指定的存储格式和压缩方式进行存储
insert into tb_movie2 select * from tb_movie;