1. Hive简介
1.1 介绍
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质是：将HQL转化成MapReduce程序。
Hive在Hadoop之上，所以使用hive的前提是先要安装Hadoop
- Hive处理的数据存储在HDFS
- Hive分析数据底层的实现是MapReduce
- 执行程序运行在Yarn上
1.2 优缺点
1.2.1 优点
- 操作接口采用类SQL语法，避免了去写MapReduce，减少开发人员的学习成本，提供快速开发的能力（简单、容易上手）
- Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数
1.2.2 缺点
- Hive的HQL表达能力有限
- Hive的效率比较低，调优比较困难，粒度较粗
- Hive延迟较高，无法做到实时查询，适合数据分析
- 由于基于HDFS，HDFS不支持更新及删除，所以不支持行级别更新(update,delete)
1.2 Hive架构
说明：
1. 元数据：Metastore，包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
2. 驱动器Driver
- 解析器：对SQL进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
- 编译器：编译生成逻辑执行计划。
- 优化器：对逻辑执行计划进行优化。
- 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MapReduce/Spark。
Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。
1.3 Hive与数据库的对比
- 查询语言：hive采用了类sql的HQL语句，所以两者语法类似，少数不同
-
数据存储位置：所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。
-
数据更新：Hive数据是在HDFS中，所以不支持数据的修改和删除，只可插入，即追加内容，且所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的
-
执行：Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。
-
执行延迟：hive由于没有索引，需要扫描整个表，且MapReduce框架本身延迟就很高，这两点决定了hive的高延迟，而数据库一般数据量小的时候延迟很低。
-
数据规模：hive本身就是大数据框架，可以支持的数据规模很大，而数据库规模较小
2. Hive安装
2.1 hadoop和hive版本对应关系
要想知道这两者的对应关系，我们可以在hive的下载页面中随便下载一个src.tar.gz文件：https://mirrors.bfsu.edu.cn/apache/hive/
比如我这里想知道hive-3.1.2版本和哪个hadoop版本比较契合，那么我只要下载
hive-3.1.2/下的apache-hive-3.1.2-src.tar.gz（源代码）压缩包，解压并查看其pom文件
可以看到：hive-3.1.2 最合适的hadoop版本为3.1.0。不过一般只需要注意大版本之间能对应就可以了。
2.2 下载hive
https://ftp.tsukuba.wide.ad.jp/software/apache/hive/hive-3.1.2/网址下下载hive ：apache-hive-3.1.2-bin.tar.gz
2.3 安装
任选hadoop集群中的一台机器上传压缩包并解压，并做以下准备
- 保证配置了Hadoop的环境变量
- 保证配置了JAVA的环境变量
- 由于hive命令使用频繁，所以我们也需要创建HIVE_HOME，并将bin配置进环境变量中
- 启动hadoop集群
当准备工作做好后，执行hive命令即可，
不过会报错
2.3.1 错误1
Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V
at org.apache.hadoop.conf.Configuration.set(Configuration.java:1357)
at org.apache.hadoop.conf.Configuration.set(Configuration.java:1338)
at org.apache.hadoop.mapred.JobConf.setJar(JobConf.java:518)
at org.apache.hadoop.mapred.JobConf.setJarByClass(JobConf.java:536)
at org.apache.hadoop.mapred.JobConf.<init>(JobConf.java:430)
at org.apache.hadoop.hive.conf.HiveConf.initialize(HiveConf.java:5141)
at org.apache.hadoop.hive.conf.HiveConf.<init>(HiveConf.java:5099)
at org.apache.hadoop.hive.common.LogUtils.initHiveLog4jCommon(LogUtils.java:97)
at org.apache.hadoop.hive.common.LogUtils.initHiveLog4j(LogUtils.java:81)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:699)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:683)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:318)
at org.apache.hadoop.util.RunJar.main(RunJar.java:232)该错是因为
com.google.common.base.Preconditions.checkArgument类位于我们的guava.jar包中
- 该包在hadoop中存储的位置是hadoop\share\hadoop\common\lib
- 该包在hive中存储的位置是hive/lib
因为hive依赖了hadoop的jar包，所以自然也依赖了hadoop jar包中的guava.jar包，导致出现了两个版本不一致的guava.jar,所以我们只需要将hive下的guava.jar包换成hadoop jar包下的guava.jar就可以了
rm -rf /usr/local/workspace/hive/lib/guava-19.0.jar
cp /usr/local/workspace/hadoop/share/hadoop/common/lib/guava-27.0-jre.jar /usr/local/workspace/hive/lib/2.3.2 错误2
该错是没有配置hive的元数据存储的数据库导致的
1. docker安装mysql数据库，并创建数据库：metastore，编码格式为utf8mb4
#下载mysql镜像
docker pull mysql:5.6
#创建mysql数据外部存储目录
mkdir /usr/local/docker-mysql-data
#启动mysql容器
docker run -p 3306:3306 --name master -v /usr/local/docker-mysql-data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6
2.maven上去下载mysql:mysql-connector-java:5.1.46的jar包，并放在hive->lib目录下
3. 进入hive->conf目录，创建文件hive-site.xml，内容如下：
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop101:3306/metastore?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
<description>password to use against metastore database</description>
</property>
<-- 设置连接jdbc的账号和密码tanfp和123456 !-->
<property>
<name>hive.jdbc_passwd.auth.root</name>
<value>root</value>
<description/>
</property>
</configuration>
4. 初始化数据库，执行名称 初始化数据库即可，我这里用的mysql：
schematool -dbType mysql -initSchema2.3.3 错误3
当出现com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Communications link failure报错时，检查一下mysql是否正常启动，或者hive中设置的mysql驱动是否符合版本
2.4 相关配置
2.4.1 数据仓库位置
Default数据仓库的最原始位置是在hdfs上的：/user/hive/warehouse路径下。在仓库目录下，没有对默认的数据库default创建文件夹。如果某张表属于default数据库，直接在数据仓库目录下创建一个文件夹。修改默认位置需要在hive-site.xml配置文件中进行新增配置：
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/hive</value>
<description>location of default database for the warehouse</description>
</property>修改后，数据就会被放在HDFS的/hive目录下
2.4.2 运行日志信息配置
将conf目录下的hive-log4j2.properties.template文件拷贝一份，该文件命名为hive-log4j2.properties，并修改其中的配置：
property.hive.log.dir = /usr/local/workspace/hive/logs（自定义）
2.4.3 查询显示配置
在hive-site.xml中配置
<-- select结果显示字段名 -->
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<-- cli客户端显示数据库名称 -->
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>2.5 使用jdbc的方式连接hive
1. 当你使用的shell用户是root时，需要对hadoop进行配置，否则启动报错
解决方式：
在hadoop的配置文件core-site.xml增加如下配置，其中“xxx”是连接beeline的用户，此处我就是root，将“xxx”替换成自己的用户名即可。最关键的是一定要重启hadoop，先stop-all.sh，再start-all.sh，否则不会生效的
<property>
<name>hadoop.proxyuser.xxx.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.xxx.groups</name>
<value>*</value>
</property>
2. 启动bin目录下的hiveserver2
注意：启动过程会比较慢，需要等一伙，当出现4个session id时表示启动完成。
3. 编写java代码
public class HiveJdbc {
public static void main(String[] args) throws Exception {
//①加载驱动
//Class.forName("org.apache.hive.jdbc.HiveDriver");
//②创建连接
Connection connection = DriverManager.getConnection("jdbc:hive2://hadoop101:10000", "root", "root");
// ③准备SQL
String sql="select * from student";
// ④预编译sql
PreparedStatement ps = connection.prepareStatement(sql);
// ⑤执行sql
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()) {
System.out.println("id:"+resultSet.getInt("id")+"---->name:"+
resultSet.getString("name"));
}
}
}3. 数据类型
3.1 基本数据类型
3.2 集合数据类型
- STRUCT: 理解为java中的对象，只是struct只有属性，没有方法。可通过"点"符合获取属性值
- MAP: 理解为java中的map， 通过["key"]来获取value
-
ARRAY： 理解为java中的数组array，通过[下表]获取值
举例：
假设某表有如下一行，我们用JSON格式来表示其数据结构。在Hive下访问的格式为：
{
"name": "songsong",
"friends": ["bingbing" , "lili"] , //列表Array,
"children": { //键值Map,
"xiao song": 18 ,
"xiaoxiao song": 19
}
"address": { //结构Struct,
"street": "hui long guan" ,
"city": "beijing"
}
}1. 在根目录下创建一个test.txt文件，内容如下：
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing2. 在Hive中创建测试表test
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' -- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP中的key与value的分隔符
lines terminated by '\n'; -- 行分隔符3. 使用hive命令导入数据到测试表中
load data local inpath '/test.txt' into table test;4. 查询
select friends[1],children['xiao song'],address.city from test where name="songsong";4. 表
4.1 创建表、内部表、外部表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)] -- 表中的字段信息
[COMMENT table_comment] -- 表的注释
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format] -- 表中数据每行的格式，定义数据字段的分隔符，集合元素的分隔符等
[STORED AS file_format] -- 表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）可以设置
为SequnceFile或 Paquret,ORC等
[LOCATION hdfs_path] -- 表在hdfs上的位置EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
-
管理表： 默认创建的表都是所谓的管理表，有时也被称为内部表。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。即既会删除HDFS上真实的数据，也会删除mysql中的元数据
-
外部表：Hive不完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。即只删除mysql中的元数据，不删除HDFS上真实数据，一般企业建表都是用外部表
内部表与外部表的转化：
-- 修改EXTERNAL为TRUE或FALSE即可，TRUE表示是外部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
4.2 分区表
建表语句：[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
在建表时，指定了PARTITIONED BY ，作用是将表中的数据，分散到表目录下的多个子目录(分区目录)中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率！只有分区表才有子目录(分区目录)。
分区目录的名称由两部分确定： 分区列列名=分区列列值
分区表操作
1. 创建分区表
create external table if not exists default.deptpart1(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t';PARTITIONED BY中的分区字段可以自定义名称，将它当成表的一个特殊的列即可。
2. 分区的查询
show partitions 表名;3. 创建分区
创建分区有两种方式：第一种显式创建
alter table 表名 add partition(分区字段名=分区字段值);
-- 示例
alter table deptpart1 add partition(area='huazhong');第二种：隐式创建，即当使用load命令加载数据时，也会自动创建一个分区
给指定的表创建分区后，会有两个明显的变化：
a)在hdfs上生成分区路径
b)在mysql中metastore.partitions表中生成分区的元数据
4. 加载数据到area=huazhong的分区中
-- 如果还没有huazhong分区，那么执行命令后，hive会自动帮你创建分区
load data local inpath '/dept.txt' into table default.deptpart1 partition(area='huazhong');注意： 如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据
dept.txt的内容如下：
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700导入数据后HDFS目录结构：
5. 查询数据
select * from deptpart1;
-- 将分区字段用在where中进行过滤
select * from deptpart1 where area = 'huazhong';查询结果：
可以看到分区字段其实就是一个特殊的列，可以对分区列进行where过滤。
4.3 多级分区表
多级分区表，即一个表有多级分区字段
4.3.1 多级分区表的使用
建表语句如下：表示有两级分区
create external table if not exists default.deptpart2(
deptno int,
dname string,
loc int
)
PARTITIONED BY(area string,province string)
row format delimited fields terminated by '\t';加载数据
注意：如果表是多级分区表，在导入数据时，数据必须位于最后一级分区的目录
-- 加载数据时指定多级分区
load data local inpath '/dept.txt' into table default.deptpart2 partition(area='huazhong',province='sichuan');上传后的HDFS目录结构如下：
查询结果
可以看出二级目录也被当做一个字段，那么我们where过滤时可如下 写法：
select * from deptpart2 where area ='huazhong' and province='sichuan';4.3.2 修复分区
当HDFS中存在数据，而mysql的元数据被我们删除了（删除了外部表），此时当我们再次创建完此表进行查询时，会查不到数据，原因就是元数据中没有恢复HDFS上的多级分区，此时我们执行需要执行命令即可修复：
msck repair table 表名;4.4 分桶表
建表语句：
[CLUSTERED BY (col_name, col_name, ...) -- 分桶的字段，是从表的普通字段中来取
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] -- 指定列排序，并设置桶的数量建表时指定了CLUSTERED BY，这个表称为分桶表！和MR中分区是一个概念！ 通过规则把数据分散到多个文件中。（默认通过hash计算分区号）。
作用：分桶本质上也是为了分散数据！在分桶后，可以结合hive提供的抽样查询，只查询指定桶的数据，和分区表不同的是，分区表是额外指定一个字段作为区分，而分桶表则是指定一个已定义的普通字段作为区分。
在分桶时，也可以指定将每个桶的数据根据一定的规则来排序，如果需要排序，那么可以在CLUSTERED BY后根SORTED BY
4.4.1 分桶表操作
创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';导入数据到表中
向分桶表导入数据时，由于需要拆分文件，和MR的分区概念相同，所以必须运行MR程序，才能实现分桶操作！
load命令加载数据的方式，只是执行put操作，即仅仅将文件上传到HDFS，无法满足分桶表导入数据！所以必须执行insert into 语句，为了方便插入数据，我们可以使用以下命令：
insert into 表名 select 语句由语句可看出我们需要从其他的表获取到数据再导入到分桶表，所以我们可以创建一个临时表：
create table stu_buck_tmp(id int, name string)
row format delimited fields terminated by '\t';加载数据到临时表：
load data local inpath '/student.txt' into table stu_buck_tmp;将临时表数据导入到分桶表stu_buck前还需要对hive进行一些设置：
// 强制可分桶
hive (default)> set hive.enforce.bucketing=true;
// -1表示reduceTask数量由hive决定
hive (default)> set mapreduce.job.reduces=-1;
// 强制可排序
hive (default)> set hive.enforce.sorting=true;将临时表数据导入到分桶表stu_buck中
insert into table stu_buck select * from stu_buck_tmp;导入后，查看HDFS可知数据已经被分成4个文件，即MR分区成功，默认分区规则即是对分桶字段进行了hash运算（MR默认的分区规则）
4.4.2 抽样查询
格式
-- x,y均是变量数值
select * from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段);
-- 举例
select * from stu_buck tablesample(bucket 1 out of 2 on id)bucket x out of y on 分桶表分桶字段 语句说明：
假设当前表一共分了z个桶，
- x表示从当前表的第几桶开始抽样（0<x<=y）
- y表示每隔多少次抽一次
- 通过z/y计算出一共抽多少桶，z/y必须能除尽，小数也可以（y必须是z的因子或倍数！）
即从第x桶开始抽样，每间隔y桶抽一桶，直到抽满 z/y桶，以下举例说明：
- bucket 1 out of 2 on id： 从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽2桶 ： 0号桶,2号桶
- bucket 1 out of 1 on id： 从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽4桶 ： 0号桶,2号桶,1号桶,3号桶
- bucket 2 out of 4 on id： 从第2桶(1号桶)开始抽，一共抽1桶 ： 1号桶
- bucket 2 out of 8 on id： 从第2桶(1号桶)开始抽，一共抽0.5桶 ： 1号桶的一半（查询出来就是1号桶的一半数据）
5. 数据导入
5.1 load
load data [local] inpath '/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
- load data:表示加载数据
- local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
- inpath:表示加载数据的路径
- overwrite:表示覆盖表中已有数据，否则表示追加
- partition:表示上传到指定分区
5.2 通过查询语句向表中插入数据（Insert）
-- 基本插入数据
insert into table student partition(month='201709') values(1,'wangwu');
-- 根据单张表查询结果插入
insert overwrite table student partition(month='201708') select id, name from student where month='201709';
-- 根据多张表查询结果插入数据
from student
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';5.3 Import数据到指定Hive表中
-- 先用export导出后，再将数据导入
import table student2 partition(month='201709') from '/export/student';6. 数据导出
6.1 Insert导出
-- 将查询的结果导出到本地
insert overwrite local directory '/export/student' select * from student;
-- 将查询的结果格式化导出到本地
insert overwrite local directory '/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
-- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/user/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;6.2 Export导出到HDFS上
export table default.student to '/export/student';7. 排序
Hive的本质是MR，MR中排序：
全排序： 结果只有一个(只有一个分区)，所有的数据整体有序！
部分排序： 结果有多个(有多个分区)，每个分区内部有序！
- ORDER BY col_list ： 全排序！
- SORT BY col_list ： 部分排序，设置reduceTaskNum>1。 只写sort by是随机字段分区！
- DISTRIBUTE BY：部分排序指定按照哪个字段分区！结合sort by 使用
- CLUSTER BY col_list ： 如果部分排序的分区的字段和排序的字段一致，可以简写为CLUSTER BY ，即DISTRIBUTE BY sal sort by sal asc 等价于 CLUSTER BY sal，注意：CLUSTER BY 后不能写排序方式，只能按照asc排序！
insert overwrite local directory '/home/sortby'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from emp where mgr is not null CLUSTER BY mgr;通过以上sql语句导出的文件就是按照mgr字段分区出来的n个文件（n取决于设置的reduceTask的数量），而每个文件中的数据都是按照mgr字段排序好的
8. 函数
8.1 常用函数
- NVL( string1, replace_with)： 判断string1是否为null，如果为null，使用replace_with替换null，否则不做操作！
- .concat: 字符串拼接
- concat_ws(分隔符,[string | array<string>]+): 使用指定的分隔符完成字符串拼接！
- collect_set(列名) ： 将此列的多行记录合并为一个set集合，去重
- collect_list(列名) ： 将此列的多行记录合并为一个set集合，不去重
- explode(列名) : 参数只能是array或map, 将array类型参数转为1列N行
将map类型参数转为2列N行
8.1.1 行转列
1列N行 转为 1列1行，即将多行某字段相同的值进行聚合，使用group by即可，对于聚合后的其他字段可以使用collect_list()函数进行值的合并
8.1.2 列转行
列转行需要以下两个关键词：
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
源数据：
names tags hobbys
jack|tom|jerry 阳光男孩|肌肉男孩|直男 晒太阳|健身|说多喝热水
marry|nancy 阳光女孩|肌肉女孩|腐女 晒太阳|健身|看有内涵的段子期望结果：
jack 阳光男孩 晒太阳
jack 阳光男孩 健身
jack 阳光男孩 说多喝热水
jack 肌肉男孩 晒太阳
jack 肌肉男孩 健身
jack 肌肉男孩 说多喝热水
......创建表
create table person_info2(names array<string>,tags array<string>,hobbys array<string>)
row format delimited fields terminated by '\t'
collection items terminated by '|'查询语句
select name,tag,hobby
from person_info2
lateral view explode(names) tmp1 as name
lateral view explode(tags) tmp1 as tag
lateral view explode(hobbys) tmp1 as hobby9. 窗口函数
9.1 窗口函数定义
场景：我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.
注意：在SQL处理中，窗口函数都是最后一步执行，而且仅位于Order by字句之前。
官网地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics
窗口函数简言之就是窗口+函数
格式：函数 over( partition by 字段 ,order by 字段 window_clause )
窗口就是函数运行时计算的数据集的范围,ove()代表的就是窗口
函数支持以下几类函数：
- Windowing functions
① LEAD (scalar_expression [,offset] [,default]) ：返回当前行以下第N行的指定列的列值！ 如果找不到，就采用默认值
② LAG (scalar_expression [,offset] [,default])： 返回当前行以上第N行的指定列的列值！
③ FIRST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的第一个值，第二个参数如果为true,代表如果第一个值为null，则跳过空值，继续寻找！
④ LAST_VALUE(列名,[false(默认)])： 返回当前窗口指定列的最后一个值，第二个参数如果为true,代表如果第一个值为null，则跳过空值，继续寻找！ - 统计类的函数
如min,max,avg,sum,count - 排名函数：
RANK, ROW_NUMBER, DENSE_RANK, CUME_DIST, PERCENT_RANK, NTILE
9.2 窗口的大小
窗口的大小可以通过windows_clause来指定：
OVER()：指定分析函数工作的数据窗口大小
CURRENT ROW：当前行
num PRECEDING：往前 n 行数据
num FOLLOWING：往后 n 行数据
UNBOUNDED：表示边界
UNBOUNDED PRECEDING 表示上无边界，即从第一行开始取
UNBOUNDED FOLLOWING 表示下午无边界 ，即取到最后一行截至
默认情况：
- 在over()中既没有出现windows_clause，也没有出现order by，窗口默认为rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING，即上无边界到下无边界，取全部数据
- 在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW，即上无边界到当前行。
注意：
1. 不是所有的函数在运行都是可以通过改变窗口的大小，来控制计算的数据集的范围！
所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义 window_clause
2. 窗口函数会对结果集的每一行都运行一次，上面说的上无边界到当前行就是说从第一条记录到窗口函数当前执行的那一行的数据范围
为了更好的理解窗口函数，窗口函数和分组有什么区别？
①如果是分组操作，select后只能写分组后的字段, 窗口函数则没有限制，更灵活
②如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数，而分组操作则是只会对数据集执行一次
③如果是分组操作，会对结果有去重效果，而窗口函数中的partition不会对结果集去重，相当于重新开了个窗口执行函数，并不会影响原来的数据集
9.3 排名函数
排名函数可以跟Over()，但是不能定义window_clause.在计算名次前，需要先排序！
RANK: 允许并列，一旦有并列跳号！
ROW_NUMBER: 行号！ 连续的，不允许并列，每个号之间差1！
DENSE_RANK： 允许并列，一旦有并列不跳号！
CUME_DIST： 从排序后的第一行到当前值之间数据条数 占整个总数据条数的百分比！
PERCENT_RANK： rank-1/ 总数据量-1
NTILE(x): 将数据集均分到X个组中，返回每条记录所在的组号
CUME_DIST可能稍微难理解一点，举例说明：
9.3 窗口函数实例说明
实战例子可以查看博客：https://blog.csdn.net/weixin_38750084/article/details/82779910
-- 表结构：business.name | business.orderdate | business.cost
-- 1. 查询在2017年4月份购买过的顾客及总人数(默认窗口范围上下均无界)
select name,count(*) over() from business where substring(orderdate,1,7)='2017-04' group by name;
-- 2. 查询顾客的购买明细及月购买总额(没有order by，默认窗口范围上下均无界)
select name,orderdate,cost,sum(cost) over(partition by name,substring(orderdate,1,7)) from business;
-- 3. 查询顾客的购买明细要将cost按照日期进行累加(有order by，默认窗口范围上无边界到当前行)
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate) from business;
-- 4. 查询顾客的购买明细及顾客上次的购买时间
select name,orderdate,cost,lag(orderdate,1,'无数据') over(partition by name order by orderdate ) from business;
-- 5. 查询顾客的购买明细及顾客下次的购买时间
select name,orderdate,cost,lead(orderdate,1,'无数据') over(partition by name order by orderdate ) from business;
-- 6. 查询顾客的购买明细及顾客本月第一次购买的时间
select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate ) from business;
-- 7. 查询顾客的购买明细及顾客本月最后一次购买的时间
select name,orderdate,cost,LAST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate rows between CURRENT row and UNBOUNDED FOLLOWING) from business;
-- 8. 查询顾客的购买明细及顾客最近三次cost花费
-- 当前和之前两次:
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 2 PRECEDING and CURRENT row) from business;
-- 当前+前一次+后一次
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) from business;
-- 9. 查询前20%时间的订单信息
select * from
(select name,orderdate,cost,cume_dist() over(order by orderdate ) cdnum from business) tmp where cdnum<=0.2;排名函数实例：
-- 表结构： score.name | score.subject | score.score
-- 1. 按照科目进行排名
select *,rank() over(partition by subject order by score desc) from score;
-- 2. 给每个学生的总分进行排名
select name,sumscore,rank() over( order by sumscore desc) from
(select name,sum(score) sumscore from score group by name) tmp;
-- 3. 求每个学生的成绩明细及给每个学生的总分和总分排名
select *,DENSE_RANK() over(order by tmp.sumscore desc) from
(select *,sum(score) over(partition by name) sumscore from score) tmp;
-- 4. 只查询每个科目的成绩的前2名
select * from
(select *,rank() over(partition by subject order by score desc) rn from score) tmp
where rn<=2;
-- 5. 查询学生成绩明细，并显示当前科目最高分
select *,max(score) over(partition by subject) from score;
或
select *,FIRST_VALUE(score) over(partition by subject order by score desc) from score;
-- 6. 查询学生成绩，并显示当前科目最低分
select *,min(score) over(partition by subject) from score;10 自定义函数
参考官方文档：https://cwiki.apache.org/confluence/display/Hive/HivePlugins
1. 编写类，该类继承UDF并在该类中创建evaluate方法，并打成jar包
2. 将jar上传到服务器
3.开启hive命令行，执行命令将jar包添加到hive的classpath中
-- xx 表示你上传的jar包的路径
add jar /xx/myjar.jar;
4. 在hive命令行中输入命令，创建函数
-- temporary表示临时函数，关闭命令行就不能调用了
create [temporary] function my_db.my_lower as 'com.example.hive.udf.Lower';11. 文件存储格式
Hive 支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。
存储格式分为两种：行式存储和列式存储。TEXTFILE 和 SEQUENCEFILE 的存储格式都是基于行存储的；而ORC、PARQUET是基于列存储的。
11.1 行存储
优点：数据被保存在一起了，insert和update更加容易
缺点：查询时即使只涉及某几列，所有数据也都会被读取
11.2 列存储的特点
优点：查询时只有涉及到的列会被读取；投影（projection）很高效；任何列都能作为索引
在大数据领域一般都采用列式存储。压缩比高，速度快，节省空间
ORC、PARQUET对比：
- ORC： hive独有，只有在hive中可以使用，ORC比PARQUET更优一点！ ORC的压缩比高！
- PARQUET： clodera公司提供的一个旨在整个hadoop生态系统中设计一个通用的高效的数据格式！PARQUET格式的文件，不仅hive支持，hbase，impala，spark都支持！
11.3 说明
①如果hive表以TEXTFILE为格式存储数据，可以使用load的方式，否则都必须使用insert into
②压缩比： ORC>PARQUET>TEXTFILE
③在查询速度上无明显差别
④一般使用ORC存储格式(内部使用SNAPPY压缩），压缩，查询，压缩速度等各方面都很优秀
⑤如果使用Parquet格式，一般使用LZO压缩方式
471
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
