目录
为什么要用Hive
什么是Hive
-
Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具,可以用来进行数据提取转化加载(ETL ),这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言,称为 HQL ,它允许熟悉 SQL 的用户查询数据。同时,这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
-
数据仓库,英文名称为Data Warehouse,可简写为DW或DWH。数据仓库,是为企业所有级别的决策制定过程,提供所有类型数据支持的战略集合。它出于分析性报告和决策支持目的而创建。为需要业务智能的企业,提供指导业务流程改进、监视时间、成本、质量以及控制。
-
Hive是SQL解析引擎,它将SQL语句转译成M/R Job然后在Hadoop执行。
-
Hive的表其实就是HDFS的目录,按表名把文件夹分开。如果是分区表,则分区值是子文件夹,可以直接在M/R Job里使用这些数据。
-
Hive相当于hadoop的客户端工具,部署时不一定放在集群管理节点中,可以放在某个节点上
Hive的概念架构
Hive的搭建(首先要提前安装完MySQL并且开启了Hadoop)
1、解压hive的安装包:
tar -zxvf apache-hive-1.2.1-bin.tar.gz
修改目录名称:
mv apache-hive-1.2.1-bin hive-1.2.1
2、进入hive-1.2.1/conf目录,复制备份文件并重命名
cp hive-env.sh.template hive-env.sh
cp hive-default.xml.template hive-site.xml
3、配置hive的配置文件(hive的配置文件比较大,在linux中查找某项配置比较难,可以先将hive-site.xml文件复制到windows用文本编辑打开,然后ctrl+f查关键字修改,修改之后再放回到hive 的conf目录)
3.1修改hive-env.sh
加入三行内容(大家根据自己的目录和实际情况来添加)
HADOOP_HOME=/opt/modules/hadoop-2.7.6
JAVA_HOME=/opt/modules/jdk1.8.0_171
HIVE_HOME=/opt/modules/hive-1.2.1
3.2修改hive-site.xml
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master:3306/hive?useSSL=false</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<property>
<name>hive.querylog.location</name>
<value>/usr/local/soft/hive-1.2.1/tmp</value>
</property>
<property>
<name>hive.exec.local.scratchdir</name>
<value>/usr/local/soft/hive-1.2.1/tmp</value>
</property>
<property>
<name>hive.downloaded.resources.dir</name>
<value>/usr/local/soft/hive-1.2.1/tmp</value>
</property>
3.4拷贝mysql驱动到$HIVE_HOME/lib目录下
cp /usr/local/soft/mysql-connector-java-5.1.49.jar …/lib/
3.5将hive的jline-2.12.jar拷贝到hadoop对应目录下,hive的 jline-2.12.jar 位置在 :
/usr/local/soft/hive-1.2.1/lib/jline-2.12.jar
将hive的jar拷过去hadoop下:
cp /usr/local/soft/hive-1.2.1/lib/jline-2.12.jar /usr/local/soft/hadoop-2.7.6/share/hadoop/yarn/lib/
3.6配置环境变量,加上HIVE_HOME
修改/etc/profile
vim /etc/profile
重新加载环境变量
source /etc/profile
3.7启动hive:
hive(任意目录下执行都可,因为已经配置完环境变量了)
(如果安装时候报了没有元数据库的错,就自己手动在MySQL中创建一个hive数据库即可)
Hive与传统数据库的比较
先有格式再有数据,还是现有数据再有格式
- MySQL:
先有格式,再有数据
数据在本地
数据是一条一条insert into(直接就进去表了,所以之前表应该是存在的) - Hive:
先有数据,再有格式
数据在HDFS
数据是一个一个文件put进去的,之后再一个个解析成Hive中的数据格式(数据可以是csv、可以是txt,但都是现有数据再根据数据的格式解析进表)
数据更新(insert into)
- Hive:
Hive的数据来自于HDFS,HDFS中的数据想要做更新就必须要下载下来再进行修改,再上传(所以hive不管是delete、insert都不好使)
1.x版本之后虽然支持了,但是也没有什么必要,因为hive中存储的数据量本身就很大,对它做一些单独的修改没有必要
如图所示可以进行插入,但是每次插入数据都要运行mr任务,比较缓慢,结束之后,数据并不会显示在表中
而是在HDFS的存储路径重新出现了一个文件。(而HQL又会转化为mr任务,每一个小文件又对应一个map task,小文件越多,处理的越慢)所以hive可以进行数据更新,但没必要
索引
用来精准的查询数据
- hive:
也不用索引这个功能,不用,所以就弱了
map进任务的时候,是一行一行进的,给索引没有必要;有没有索引数据都是一行一行进的
执行
- Hive:
底层是MapReduce,分布式计算框架 - MySQL:
有着自己的执行器
执行延迟(执行)
mr:分布式的,执行的延迟比较高
mysql:延迟低,效率高
可扩展性
- Hive:
存储数据没有极限(数据来源于HDFS,HDFS又来源于机器,机器多少台就能存多少数据) - MySQL:
存储数据有极限
Hive元数据
- Hive将元数据存储在数据库中(metastore),目前只支持mysql(保证了多用户,意思就是可以同时凯多个窗口)、derby(hive自带的,只支持单独用户)
- Hive 中的元数据包括表的名字,表的列和分区及其属性,表的属性(是否为外部表等),表的数据所在目录等;由解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划(plan)的生成。生成的查询计划存储在 HDFS 中,并在随后由 MapReduce 调用执行(总的理解为,Hive中有一个解析器,可以解析、编译、优化,最后转换为mr任务)
- Hive 的数据存储在 HDFS 中,大部分的查询由 MapReduce 完成(包含 * 的查询,比如 select * from table 不会生成 MapRedcue 任务(剩下一部分MySQL完成、或者Hadoop完成,因为小部分不会产生mapreduce任务)
Hive库表的位置
DBS中:数据库的元数据位置
SDS中:表的元数据位置
Hive的存储格式
Hive没有专门的数据文件格式(Hive本身不存数据,接纳各方的数据,比如MySQL的数据想要传输到Hive,都要先经过HDFS,而MySQL的数据到HDFS,可以是txt,可以是csv),常见的有这六种格式
TextFile
-
TEXTFILE 即正常的文本格式,是Hive默认文件存储格式,因为大多数情况下源数据文件都是以text文件格式保存(便于查看验数和防止乱码)。此种格式的表文件在HDFS上是明文(可以直接cat),可用hadoop fs -cat命令查看,从HDFS上get下来后也可以直接读取。
-
TEXTFILE 存储文件默认每一行就是一条记录,可以指定任意的分隔符进行字段间的分割。但这个格式无压缩(原本数据多大,存储的就多大,原本100MB,存储的还是100MB),需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用,使用这种方式,Hive不会对数据进行切分,从而无法对数据进行并行操作。
-
一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式,其他事实表和维度表都不建议使用。
RCFile
Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能。通常写操作比较慢,比非列形式的文件格式需要更多的内存空间和计算量。 (每一列一列的存储,行与行之间需要连起来,中间加了许多的计算量和内存空间)**RCFile是一种行列存储相结合的存储方式。**首先,其将数据按行分块,保证同一个record在一个块上,避免读一个记录需要读取多个block(避免小文件,多个map task)。其次,块数据列式存储,有利于数据压缩和快速的列存取。
ORCFile
Hive从0.11版本开始提供了ORC的文件格式,ORC文件不仅仅是一种列式文件存储格式,最重要的是有着很高的压缩比,并且对于MapReduce来说是可切分(Split)的。因此,在Hive中使用ORC作为表的文件存储格式,不仅可以很大程度的节省HDFS存储资源,而且对数据的查询和处理性能有着非常大的提升,因为ORC较其他文件格式压缩比高,查询任务的输入数据量减少,使用的Task也就减少了。ORC能很大程度的节省存储和计算资源,但它在读写时候需要消耗额外的CPU资源来压缩和解压缩,当然这部分的CPU消耗是非常少的。
Parquet
-
通常我们使用关系数据库存储结构化数据,而关系数据库中使用数据模型都是扁平式的(类似于这种可以直接读取的数据就是扁平化的)
-
遇到诸如List、Map和自定义Struct的时候就需要用户在应用层解析。(比如k-v格式中,v中的数据依然是k-v格式)但是在大数据环境下,通常数据的来源是服务端的埋点数据,很可能需要把程序中的某些对象内容作为输出的一部分,而每一个对象都可能是嵌套的,所以如果能够原生的支持这种数据,这样在查询的时候就不需要额外的解析便能获得想要的结果。
-
Parquet的灵感来自于2010年Google发表的Dremel论文,文中介绍了一种支持嵌套结构的存储格式,并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式,它是语言、平台无关的,并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势:支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.
SEQUENCEFILE
- SequenceFile是Hadoop API 提供的一种二进制文件,它将数据以<key,value>的形式序列化到文件中。**这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。**它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile,不过它的key为空,使用value 存放实际的值, 这样是为了避免MR 在运行map 阶段的排序过程。
- SequenceFile支持三种压缩选择:NONE, RECORD, BLOCK。 Record压缩率低,一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好,有API,但除此之外平平无奇,实际生产中不会使用。
AVRO
Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑,若要读取大量数据时,Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的,所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式,如Pig 、Hive、Flume、Sqoop和Hcatalog。
Hive存储格式总结
TextFile:明文(计算之后需要经常的查看数据)
ORC:列,很高的压缩比(追求极致的效率)
Parquet:集合
二进制存储格式不怎么使用
Hive创建表
create [EXTERNAL] table students
(
id bigint,
name string,
age int comment,
gender string,
clazz string
)
PARTITIONED BY ( 非必选;创建分区表
dt string)
clustered by (userid) into 3000 buckets // 非必选;分桶子
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ // 必选;指定列之间的分隔符
STORED AS rcfile // 非必选;指定文件的读取格式,默认textfile格式
location ‘/testdata/’; //非必选;指定文件在hdfs上的存储路径,如果已经有文件,会自动加载 ,默认在hive的warehouse下
数据类型与创建类型不一致
数据类型与创建的数据类型不一致(比如一个是int,一个是string)
分割类型与实际类型不一致
比如数据文件是“*”分割,创建时以“,”分割
会解析在第一列
所以,创建表必须指定数据分割的格式,而且要一致
Hive外部表与内部表
外部表
create EXTERNAL table students
(
id bigint,
name string,
age int comment,
gender string,
clazz string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
删除表,发现,表不见了,但是数据还在
内部表
create table students
(
id bigint,
name string,
age int comment,
gender string,
clazz string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
删除表,发现表不见了
内部表与外部表的区别
区别:内部表删除数据跟着删除
外部表只会删除表结构,数据依然存在
注意:公司中实际应用场景为外部表,为了避免表意外删除数据也丢失
不能通过路径来判断是目录还是hive表(是内部表还是外部表)
Hive加载数据
使用load data 命令
-
从hdfs导入数据,路径可以是目录,会将目录下所有文件导入,但是文件格式必须一致
load data inpath ‘/opt/datas/students.txt’ into table students; -
从本地文件系统导入
load data local inpath ‘/opt/datas/students.txt’ into table students;
-
表对表加载:
create table IF NOT EXISTS students_test1 as select * from students
insert [overwrite] into table students_test2 select * from students;
Hive分区
- 分区的概念和分区表:
分区表指的是在创建表时指定分区空间,实际上就是在hdfs上表的目录下再创建子目录。 - 在使用数据时如果指定了需要访问的分区名称,则只会读取相应的分区,避免全表扫描,提高查询效率。
- 建表语句:
CREATE TABLE students_pt(id bigint,name string,age int comment,gender string,clazz string
) PARTITIONED BY (year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’; - 作用:进行分区裁剪,避免全表扫描,减少MapReduce处理的数据量,提高效率,通常按日期分区、地域分区
建立分区表
切分的越多,操作起来越方便,但是切分的越多,产生的map task也越多,mr任务走的时间也越长(通常两三层就可以)
create external table students_pt1
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(pt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
增加一个分区
alter table students_pt1 add partition(pt='20210904');
删除一个分区
alter table students_pt drop partition(pt='20210904');
查看某个表的所有分区
show partitions students_pt; // 推荐这种方式(直接从元数据中获取分区信息)
select distinct pt from students_pt; // 不推荐
往分区中插入数据
(分过区之后,插入数据要指定分区)
insert into table students_pt partition(pt='20210902') select * from students;
load data local inpath '/usr/local/soft/data/students.txt' into table students_pt partition(pt='20210902');
查询某个分区的数据
(将分区条件当作一个字段进行操作就可)
// 全表扫描,不推荐,效率低
select count(*) from students_pt;
// 使用where条件进行分区裁剪,避免了全表扫描,效率高
select count(*) from students_pt where pt='20210101';
// 也可以在where条件中使用非等值判断
select count(*) from students_pt where pt<='20210112' and pt>='20210110';
Hive动态分区
如果需要创建非常多的分区,就需要做许多执行导入不同分区的操作,这时候,就可以开启Hive的动态分区来实现数据的自己分区;
Hive会根据数据是怎么给的,来自己做一个分区,比如这种本身就按照时间格式分好了,开启动态分区之后,就会自行进行分区
Hive开启动态分区(根据现有数据,自动创建子集目录)
表示开启动态分区
hive> set hive.exec.dynamic.partition=true;
表示动态分区模式:strict(需要配合静态分区一起使用(先有目录,然后再有数据,很麻烦,不用))、nostrict
strict: insert into table students_pt partition(dt=‘anhui’,pt) select …,pt from students;
hive> set hive.exec.dynamic.partition.mode=nostrict;
表示支持的最大的分区数量为1000,可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;
使用Hive动态分区(只根据一个字段分区)
创建表
一张普通表,表中存所有数据
将数据导入普通表中
load data local inpath ‘/opt/datas/students.txt’ into table students;
数据格式
create table students
(
id bigint,
name string,
age int,
gender string,
clazz string,
time string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
一张动态分区表
create table students_dt_p
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
这时候还没有数据
向动态分区表中导入数据
// 分区字段需要放在 select 的最后,如果有多个分区字段 同理,它是按位置匹配,不是按名字匹配
insert into table students_dt_p partition(dt) select id,name,age,gender,clazz,time from students;
(比如这地方就是根据partition和select 之后的最后一个字段匹配,假如最后一个字段给的是age,那就自动按照age分区了)
// 比如下面这条语句会使用age作为分区字段,而不会使用student_dt中的dt作为分区字段
insert into table students_dt_p partition(dt) select id,name,age,gender,dt,age from students_dt;
执行的时候可以明显看出分了很多区
使用Hive动态分区(根据两个字段分区)
创建表
一张普通表,表中存所有数据
将数据导入普通表中
load data local inpath ‘/opt/datas/students_year_month.txt’ into table students_year_month;
数据格式
create table students_year_month
(
id bigint,
name string,
age int,
gender string,
clazz string,
year string,
month string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
一张动态分区表
create table students_year_month_pt
(
id bigint,
name string,
age int,
gender string,
clazz string
)
PARTITIONED BY(year string,month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
这时候还没有数据
向动态分区表中导入数据
这里面的最后这两位也是要对应的
insert into table students_year_month_pt partition(year,month) select id,name,age,gender,clazz,year,month from students_year_month;
可以看到根据年份先分了一个区
在年份的基础上根据月份又进行分区
Hive分桶
对数据文件的进一步切分,如果有分区,就在分区的基础上进一步切分,没有的话,就直接分桶
Hive默认关闭分桶
作用:在往分桶表中插入数据的时候,会根据 clustered by 指定的字段 进行hash分区 对指定的buckets个数 进行取余,进而可以将数据分割成buckets个数个文件,以达到数据均匀分布,可以解决Map端的“数据倾斜”问题,方便我们取抽样数据,提高Map join效率
分桶字段 需要根据业务进行设定
Hive如何分桶
开启分桶开关
hive> set hive.enforce.bucketing=true;
创建分桶表
按照班级进行分桶
create table students_buks
(
id bigint,
name string,
age int,
gender string,
clazz string
)
CLUSTERED BY (clazz) into 12 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
向分桶表中加载数据
load data local inpath ‘/opt/datas/students.txt’ into table students_buks;
直接这样加载数据,发现并不能进行分桶
要通过insert into来进行打散(也就是打散成不同的桶)
insert into students_buks select * from students;
数据如何分别进入不同的桶中
可以看到,这里就被安插进了许多不同的桶中,但我们又可以看到,这里虽然分了12个桶,但并不是每一个桶中都有数据。(它不是一个桶中存一个班级的数据(12个班级,创建12个桶)),这个分桶的操作类似于shuffle
可能有不同的数据进入同一个桶中
数据还没分之前就有12个桶了,之后每个班级再根据hashCode()得到int类型的数据,int%12=0,1,2,3,4,5,6,7,8,9,10,11
Java客户端操作Hive
打开Hive的连接权限
hive --service hiveserver2 &
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class HiveTest {
public static void main(String[] args) throws Exception{
Class.forName("org.apache.hive.jdbc.HiveDriver");
Connection connection = DriverManager.getConnection(
"jdbc:hive2//master:10000/shujia"
);
String sql = "select * from students";
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
int age = resultSet.getInt(3);
String sex = resultSet.getString(4);
String clazz = resultSet.getString(5);
System.out.println(
id+"---"+
name+"---"+
age+"---"+
sex+"---"+
clazz+"---"
);
}
resultSet.close();
statement.close();
connection.close();
}
}
Hive的数据类型
基本数据类型
基本数据类型
- 数值型
- TINYINT — 微整型,只占用1个字节,只能存储0-255的整数。
- SMALLINT– 小整型,占用2个字节,存储范围–32768 到 32767。
- INT– 整型,占用4个字节,存储范围-2147483648到2147483647。
BIGINT– 长整型,占用8个字节,存储范围-263到263-1。
- 布尔型BOOLEAN — TRUE/FALSE
- 浮点型FLOAT– 单精度浮点数。
DOUBLE– 双精度浮点数。
字符串型STRING– 不设定长度。 - 日期类型:
1,Timestamp 格式“YYYY-MM-DD HH:MM:SS.fffffffff”(9位小数位精度)
2,Date DATE值描述特定的年/月/日,格式为YYYY-MM-DD。
复杂数据类型
Structs,Maps,Arrays
Array
数据格式:
创建表 :
查询到的数据格式:
获取表中的数据:
Map
数据格式:
创建表:
查询到的数据格式:
获取表中的数据:
Structs
数据格式:
创建表:
查询到的数据格式:
获取表中的数据:
HQL语法-DML
-
where 用于过滤、分区裁剪、指定条件
-
join 用于两表关联,left join,join,mapjoin(1.2版本后默认开启)
在map端进行一个join
-
group by 用于分组聚合
-
order by 用于全局排序(reduce端只有一个,只有一个reduce的时候,才能做全局排序)、要尽量避免排序,是针对全局排序的,即对所有的reduce输出都是有序的
-
sort by 当有多个reduce的时候,只能保证单个reduce有序输出,不能保证全局有序输出
cluster by = distribute by(分区) + sort by(排序)
-
distinct 去重
Hive函数
普通常用函数
-
nvl(,)
nvl()函数,一个参数跟着列名,一个参数跟着要替换的值,直接加null,结果是null,转换成0再相加
-
if函数 if(,,)
-
case when 函数:case when …end
-
日期函数:to_date…
-
字符串函数:concat,concat_ws, split
-
聚合函数:sum,count,avg,min,max
-
null值判断:is null ,is not null
-
其他:round,floor,……
-
explode(数据扁平化):解析数据
其他函数
-
LAG(col,n):往前第n行数据
-
LEAD(col,n):往后第n行数据
-
FIRST_VALUE:取分组内排序后,截止到当前行,第一个值
-
LAST_VALUE:取分组内排序后,截止到当前行,最后一个值,对于并列的排名,取最后一个
举例:
select id
,score
,clazz
,department
,lag(id,2) over (partition by clazz order by score desc) as lag_num
,LEAD(id,2) over (partition by clazz order by score desc) as lead_num
,FIRST_VALUE(id) over (partition by clazz order by score desc) as first_v_num
,LAST_VALUE(id) over (partition by clazz order by score desc) as last_v_num
,NTILE(3) over (partition by clazz order by score desc) as ntile_num
from new_score;
结合使用explode实现hive中的wordcount
准备数据
实现word count
解析json数据
Hive高级函数
开窗函数(多加一列)
好像给每一份数据打开一扇窗户,所以叫做开窗函数
在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等,这类函数可以将多行数据按照规则聚集为一行,一般来说,聚集后的行数要少于聚集前的行数,但有时候既想要显示聚集前的数据,又想要显示聚集后的数据,这时候引入了开窗函数
测试数据
111,69,class1,department1
112,80,class1,department1
113,74,class1,department1
114,94,class1,department1
115,93,class1,department1
121,74,class2,department1
122,86,class2,department1
123,78,class2,department1
124,70,class2,department1
211,93,class1,department2
212,83,class1,department2
213,94,class1,department2
214,94,class1,department2
215,82,class1,department2
216,74,class1,department2
221,99,class2,department2
222,78,class2,department2
223,74,class2,department2
224,80,class2,department2
225,85,class2,department2
over
原本的count(*)
select count(*) from new_score
开窗的count(*)
select count(*) over(partition by clazz) from new_score
开窗的max(*)
select *,max(score) over (partition by clazz) from new_score
开窗的求自己和最高分相差的分数
select s1.id,s2.*,s1.score1-s2.score,clazz,department from
(select id,max(score) over(partition by clazz) as score1 from new_score) as s1
left join
(select id,score,clazz,department,max(score) over (partition by clazz) from new_score) as s2
on s1.id=s2.id
row_number:无并列排名
按照班级分组,就是给每个班级中的人打了一个标注
用法: select xxxx, row_number() over(partition by 分组字段 order by 排序字段 desc) as rn from tb group by xxxx
只是打上排名
select *,row_number() over(partition by clazz) from new_score;
按照成绩排序,并且无并列排名
select *,row_number() over(partition by clazz order by score desc) from new_score;
分组求topN
select t.id,t.score,t.clazz,t.department,t.num from
(select
id,score,clazz,department,
row_number() over(partition by clazz order by score desc) as num
from new_score) as t
where t.num<=3
或
select * from
(select
*,
row_number() over(partition by clazz order by score desc) as num
from new_score) as t
where t.num<=3
dense_rank:有并列排名,并且依次递增
select *,dense_rank() over (partition by clazz order by score desc) as score from new_sco
re;
rank:有并列排名,不依次递增
select *,rank() over (partition by clazz order by score desc) as score from new_score;
percent_rank:(rank的结果-1)/(分区内数据的个数-1)
select *,percent_rank() over (partition by clazz order by score desc) as score from new_s
core;
cume_dist:计算某个窗口或分区中某个值的累积分布。
假定升序排序,则使用以下公式确定累积分布: 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值。
select *,cume_dist() over (partition by clazz order by score desc) as score from new_scor
e;
NTILE(n):对分区内数据再分成n组,然后打上组号
平均去分
select *,ntile(3) over (partition by clazz order by score desc) as ntile from new_score;
窗口帧
可以理解为就是一个滑动窗口
(可以设定一个范围,比如找一个中间值,上2个,下2个,这就是5个一个区间,可以做找最大值之类的操作,不满5个,就有多少个算多少个)
Hive 提供了两种定义窗口帧的形式:
ROWS
(按行)和RANGE
(按区域)。两种类型都需要配置上界和下界。例如,ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
表示选择分区起始记录到当前记录的所有行;SUM(close) RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING
则通过 字段差值 来进行选择。如当前行的close
字段值是200
,那么这个窗口帧的定义就会选择分区中close
字段值落在100
至400
区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧,则默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
。
只能运用在max、min、avg、count、sum、FIRST_VALUE、LAST_VALUE这几个窗口函数上
窗口帧格式
格式1:按照行的记录取值
ROWS BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
格式2:当前所指定值的范围取值
RANGE BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
注意:
UNBOUNDED:无界限
CURRENT ROW:当前行
举例窗口帧使用
rows格式1:前2行+当前行+后两行
sum(score) over (partition by clazz order by score desc rows between 2 PRECEDING and 2 FOLLOWING)
rows格式2:前记录到最末尾的总和
sum(score) over (partition by clazz order by score desc rows between CURRENT ROW and UNBOUNDED FOLLOWING)
range格式1: 如果当前值在80,取值就会落在范围在80-2=78和80+2=82组件之内的行
max(score) over (partition by clazz order by score desc range between 2 PRECEDING and 2 FOLLOWING)
窗口帧使用举例
lateral view—行转列
也会多加一个列
lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF,UTDF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
explode函数 参数仅接受array和map类型,不支持两个一起用。所以lateral view可以解决
现有的数据与数据格式:
想要转换成:
思路:
1.可以先形成一个笛卡尔积,将数据变成如下这样
2.再取需要的就可
实现array的行转列
使用行转列函数的时候,要给字段和表都加一个别名
举例:
select * from arrtest lateral view explode(score) t as c;
实现:
select name,c from arrtest lateral view explode(score) t as c
实现map的行转列
数据格式:
实现:
举例一个列转行
数据格式:
列转行:
select collect_list(tt.c) from (select name,c from arrtest lateral view explode(score) t
as c) tt;
列转行:
select name,collect_list(tt.c) from (select name,c from arrtest lateral view explode(scor
e) t as c) tt group by tt.name;
Hive自定义函数
Hive 提供的内置函数无法满足你的业务处理需要时,此时就可以考虑使用用户自定义函数
比如这里每一天都来了很多的数据,但是数据前面没有给上来时候的时间,我们就可以使用自定义函数来给它加上
UDF一进一出
一个参数
1.首先创建maven项目并添加依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
2.编写代码,继承org.apache.hadoop.hive.ql.exec.UDF 实现evaluate方法,在evaluate方法中实现自己的逻辑
import org.apache.hadoop.hive.ql.exec.UDF;
public class HiveUDF extends UDF {
public String evaluate(String str){
String s = "---"+str+"---";
return s;
}
}
3.打成jar包上传至虚拟机
上传至存放包的路径下
4.进入hive客户端添加jar包
5.创建临时函数:hive>CREATE TEMPORARY FUNCTION f_up as ‘name’;
6.使用HiveUDF函数
多个参数
import org.apache.hadoop.hive.ql.exec.UDF;
public class HiveUDF01 extends UDF {
public String evaluate(String str1,String str2){
String s = str1+"---"+str2;
return s;
}
}
发现有多个参数的时候,也是可以只输出一个结果,所以UDAF(多进一出就不常用)
UDTF一进多出
大致的步骤差不多,这里只给出关键代码
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.lang.reflect.Array;
import java.util.ArrayList;
public class HiveUDTF extends GenericUDTF {
//1.3.1之后不需要写
//初始化:指定输出的数量和输出的格式
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//指定输出多少列(操作列的数量和名称)
ArrayList<String> fieldName = new ArrayList<String>();
//操作列的类型
ArrayList<ObjectInspector> fieldObj = new ArrayList<ObjectInspector>();//检测当前给的是什么类型
//输出两列
//第一列
fieldName.add("x");
fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);//检查给的是不是String类型
//第二列
fieldName.add("y");
fieldObj.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldName,fieldObj);
}
//假设进来的格式为:("k1:v1,k2:v2,k3:v3")
@Override
public void process(Object[] objects) throws HiveException {
String s = objects[0].toString();//这里的参数0,指的就是上面进来的格式
//切分出来每行数据
String[] rows = s.split(",");
for (String row : rows) {
String[] split = row.split(":");
//通过forward()输出
forward(split);
}
}
@Override
public void close() {
}
}
打包上传
注册函数
使用自定义UDTF函数
使用UDTF实现行转列
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
public class HiveUDTF01 extends GenericUDTF {
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
ArrayList<String> fieldName = new ArrayList<String>();
ArrayList<ObjectInspector> fieldObject = new ArrayList<ObjectInspector>();
fieldName.add("math");
fieldObject.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
return ObjectInspectorFactory.getStandardStructObjectInspector(fieldName,fieldObject);
}
@Override
public void process(Object[] objects) throws HiveException {
String s = objects[0].toString();
String[] split = s.split(",");
for (String s1 : split) {
String[] split1 = s1.split(",");
forward(split1);
}
}
@Override
public void close() {
}
}
打包上传
创建并使用临时函数
UDAF多进一出(不经常用)
感谢阅读,我是啊帅和和,一位大数据专业大四学生,祝你快乐。