深入了解Hiv

一、了解Hive基础

（一）认识Hive

1、背景

数据存储在HDFS上没有Schema（模式）的概念，所以Facebook创造Hive将数据从一个现有的数据架构转移到Hadoop

Schema：模式，相当于表里的列、字段、字段名称、字段与字段的分隔符

2、数据仓库与数据库的区别

	数据仓库	数据库
处理方式	联机分析处理（OLAP，On-Line Analytical Processing）	联机事务处理（OLTP，On-Line Transaction Processing）
数据	历史数据	业务数据
面向	面向主题	面向事务
目的	分析数据	捕获数据
是否冗余	有意冗余，依照分析需求设计	避免冗余，针对某一业务

3、Hive在Hadoop生态中的位置

Hive将存储在HDFS中的结构化数据映射成类关系型数据库表，接受类SQL语句，转化为MapReduce程序去执行。

Hive执行本质仍是MapReduce，但多了SQL到MapReduce的转化，所以同等条件下，Hive在运行时并没有直接编写MapReduce效率高。

Hive的必要环境：HDFS、MapReduce和YARN。

4、Hive与传统关系型数据库

传统关系型数据库：Relational Database Management System，简称RDBMS

	Hive	RDBMS
查询语言	HQL	SQL
数据存储	HDFS	块设备，本地文件系统
执行	MapReduce（硬盘执行）	Executor（）
执行延迟	高	低
处理数据规模	大	小
事务	0.14版本后加入	支持
索引	0.8版本后加入	索引复杂
应用场景	离线大数据分析（日志分析）	在线应用

5、Hive优势

• 语言和SQL类似，提高了开发效率

• 支持在不同的计算机框架运行，YARN、Tez、Spark、Flink等。

• 支持用户自定义的函数、脚本

• 避免编写MapReduce，减少开发人员的学习成本

• 支持Java数据库与开放数据库的连接

• 支持HDFS与HBase上的ad-hoc

ad-hoc：拉丁语，for this 意为 "为了此事 ",大数据领域称之为 “即席查询”，用户根据自己的需求，灵活的选择查询条件，系统能够根据条件快速的进行查询分析返回结果

生产环境中的优势

• 可扩展性：自由扩展规模，无需重启

• 可延展性：用户可自定义函数

• 可容错性：节点出错SQL仍可完成任务

（二）Hive架构设计

1、Hive客户端

针对应用程序提供不同驱动，对Java应用程序提供JDBC；其它的提供ODBC。

2、Hive服务端

客户端通过服务端进行交互，服务端主要包括CLI、Driver等。

3、Hive存储与计算

通过元数据存储数据库和Hadoop集群进行数据的存储和计算。

主要组件

（三）Hive工作流程：

（四）Hive适用的场景

• 非结构化数据的离线分析统计场合

• 由于高延迟所以只适用实时性不高的场景

• 大数据处理

二、掌握Hive数据存储模型

（一）Hive存储格式

1、真实数据（HDFS）

存储格式	存储
TEXTFILE	行	Hive默认存储格式，常见有txt、csv、tsv
SEQUENCEFILE	行	二进制序列化过得Key/Value字节流，压缩选项有：None不压缩、RECORD（默认）记录级压缩&压缩率低、BLOCK块级压缩&压缩率高
RCFILE	行列	先水平再垂直
ORCFILE	行列	RCFILE得优化，支持压缩比高的压缩算法，支持复杂的数据结构

2、元数据

Hive元数据包括hive库信息、表信息（表的属性、表的名称、表的列、分区及其属性）以及表数据所在的目录等。

Hive的元数据，默认是存储在derby中的，但是我们一般会修改其存储在关系型数据库比如MYSQL中（其可以在hive配置中修改），在关系型数据库中会有一个hive库，存放相应的表。

（二）Hive数据单元

数据单元
数据库	类似RDBMS的数据库
表	由列构成，可进行过滤、映射、连接和联合操作
分区	指定键分为多个区，提高查询效率
分桶	指定某列哈希值分为多个桶

❗注意

• Hive表没有主键

• 0.14版本前不支持行级操作

• 不支持批量update，只能先删除再添加

• 分区和分桶极大地提高了查询效率

（三）Hive存储模型

Hive数据库及表操作

一、基本数据类型

数据类型
整数	tinyint、smallint、int、bigint
小数	float、double、decimal
文本	string、char、varchar
布尔	boolean
二进制	binary
时间	date、timestamp、interval

💡上述都是对Java接口地实现，具体行为与Java对应的数据类型完全一致。

（一）整数类型

类型	字节范围	对应Java	后缀	例子
tinyint	(-2^7,2^7-1)	byte	Y	100Y
smallint	(-2^15,2^15-1)	short	S	100S
int	(-2^31,2^31-1)	int	\	\
bigint	(-2^63,2^63-1)	long	L	100L

（二）小数类型

小数也称浮点类型，包括 float（32位）、double（64位），还可使用decimal来表示任意精度的小数。

（三）文本类型

字符串类型	长度
string	2GB
varchar	（1~65355）
char	最大固定为255

💡注意：

VARCHAR（20）与CHAR（20）的区别，如果输入字符长度为10，那么VARCHAR（20）实际长度为10，而CHAR（20）仍为20，未用的空间用空格填充。

（四）时间类型

unix_timestamp()

#得到当前时间戳
unix_timestamp()
#将yyyy-mm-dd hh:mm:ss格式的字符串转为时间戳
unix_timestamp(string_date)
#将指定字符串转换为指定格式的时间戳
unix_timestamp(string_date，string format)

from_unixtime()：将指定时间戳以指定格式显示

cast():在timestamp、date和string类型之间做转换

二、复杂数据类型

数据类型
map	(key-value)键值对类型。	map.get（）#根据k找v k for k,v in d.items() if v == value#根据v找k
array	数组类型，array 中存放相同类型的数据
struct	集合类型
uniontype	在同一时间点，保持恰好有一个指定的数据类型。类Java泛型

Java数据类型

基本数据类型：整型、浮点型、字符型、布尔型

引用数据类型：接口（interface）、数组（[ ]）、类（class）

三、Hive DDL操作

（一）数据库操作

创建数据库

 create database name;

切换库

 use name;

查看库列表

 show databases;

查看正在使用的库

 select current_database();

删除库

#只能删除空的
drop database name;  
#严格模式下的删除库 会进行库的检查 如果库不是空的不允许删除
drop database name restrict; 
#删除非空数据库 级联删除
drop database name cascade;  

（二）表的操作

#创建表
CREATE [external] TABLE table_name(  
  [col_name] data_type,   
  [col_name] data_type,   
  [col_name] data_type)
#分区
partitioned by(date string ) 
#以逗号作分隔符
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ; 

（三）内部表&外部表

对比项	内部表	外部表
Hive 管理范围建表语句	元数据、实际的表数据	元数据
建表语句	不需要 external 关键字修饰	需要 external 关键字修饰
drop 表后果	删除元数据和实际的表数据	只删除元数据，实际的表数据保留
存储路径	一般在默认位置存储数据	一般搭配 location 指定数据存放位置
表结构和分区更改的区别	自动同步到元数据	需要修复表

四、Hive DML操作

（一）数据装载与插入

1、load

-- 加载本地文件
load data local inpath '/home/hadoop/load1.txt' into table tb_load1;
​
-- 加载HDFS文件
load data inpath '/hive/test/load2.txt' into table tb_load1;
​
-- 加载分区数据
load data inpath '/hive/test/load_part_male.txt' into table tb_load2 
partition (sex='male');
​
--使用overwrite：会覆盖之前的数据
load data local inpath '/home/hadoop/load3.txt' overwrite into table tb_load1;
​

（2）insert

--将cite表中的数据复制一份，然后插入到原表中
insert into table cite select * from cite;
--用tt表查出来的数据overwrite(覆盖)掉cite表格中已经存在的数据
insert overwrite table cite select * from tt;

2、数据导入/导出

（1）export

export table users to '/user/hive/warehouse/export';
#export导出的数据，里面包含了具体数据和元数据

（2）import

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
FROM 'source_path' [LOCATION 'import_target_path']
​
source_path：表示导入的文件所在路径。

export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出的本地。

3、数据执行计划查询

Hive高级操作

查询操作

---

1.select语句

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
FROM table_reference 
[WHERE where_condition] 
[GROUP BY col_list] 
[HAVING having_condition] 
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]] 
[LIMIT number];

1.1where子句

必须是布尔表达式，过滤结果集

select * from customers where customer_state = 'NY'
and customer_city='New York';

hive常用表达式

A [not] LIKE B
'abc' like 'a*'
'abc' like 'a%'
'abc' like 'a__'

运算符	操作	描述
A = B	所有基本类型	如果表达A等于表达B，结果TRUE ，否则FALSE。
A != B	所有基本类型	如果A不等于表达式B表达返回TRUE ，否则FALSE。
A < B	所有基本类型	TRUE，如果表达式A小于表达式B，否则FALSE。
A <= B	所有基本类型	TRUE，如果表达式A小于或等于表达式B，否则FALSE。
A > B	所有基本类型	TRUE，如果表达式A大于表达式B，否则FALSE。
A >= B	所有基本类型	TRUE，如果表达式A大于或等于表达式B，否则FALSE。
A IS NULL	所有类型	TRUE，如果表达式的计算结果为NULL，否则FALSE。
A IS NOT NULL	所有类型	FALSE，如果表达式A的计算结果为NULL，否则TRUE。
A LIKE B	字符串	TRUE，如果字符串模式A匹配到B，否则FALSE。
A RLIKE B	字符串	NULL，如果A或B为NULL；TRUE，如果A任何子字符串匹配Java正则表达式B；否则FALSE。
A REGEXP B	字符串	等同于RLIKE。

1.2 ALL、DISTINCT子句

两个子句都表示是否返回重复行，默认是all。

all	默认项，返回所有匹配行
DISTINCT	查询会返回所有不重复的行

1.3. limit子句

limit子句用于限制select语句返回的行数。

select p_id,p_name from products limit 5;

1.4 公共表达式（CTE）

表示一个临时的结果集，只在CTE语句范围内才可使用该临时表(即一句语句结束，;之前)，用关键字with 开头

with t1 as (
select concat(customer_fname,'-',customer_lname)as fullname from customers
)select * from t1;

1.5 嵌套查询

又称子查询，用于FROM 子句后。

• 子查询必须给定名称，因为FROM子句中每个表必须有表明

• 列有唯一名称且可在外部查询引用

• 可使用union和join操作

SELECT *FROM (
select concat(name,age)as a from customers)t1
limit 5;

其中t1是表名，必须要给嵌套的子查询给定名称，原本是表则不需要。

1.6 列匹配正则表达式

HQL支持使用正则表达式指定列名称，符合的列名将被作为结果集的一列。需要提前设置set hive.support.quoted.identufiers=None;,使hive将反引号解释为正则表达式。

select `regex_expr` from table;

1.7 虚拟列

并非真正存在的列，用于相关验证。常用如下：

input__file__name：该行数据包含于哪个文件中

block__offset__inside__file:包含文件内的块内偏移量

2.关联查询（join）

指多表进行联合查询，主要通过join语句实现

2.1 内连接

返回两张表的交集

select * from a inner join b on a.id=b.id;
# 逗号的连表方式就是内连接
select * from A , B where A.id = B.id 

2.2 外连接

2.2.1 左外连接

是以左表为基础，根据ON后给出的两表的条件将两表连接起来。

结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

# left join
select * from A left join B on  A.id = B.id 
# left outer join
select * from A left outer join B on  A.id = B.id 

2.2.2 右外连接

是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

# right join
select * from A right join B on  A.id = B.id 
# right outer join
select * from A right outer join B on  A.id = B.id 

2.3 交叉连接

笛卡尔乘积，两表相乘。关键字是cross join

3.联合查询

union语句用于合并两个或多个select语句的结果集

• union all用的比较多，是直接连接，取到得是所有值

• union 是取唯一值，记录没有重复

4.技能实训

根据零售商店查询所有的订单明细，并保存为新的表orders_details。

create table order_details as
select order_items.order_item_order_id,
orders.order_date,
customers.customer_lname,
customers.customer_city,
products.product_name,
categories.category_name,
order_items.order_item_quantity,
order_items.order_item_product_price 
from 
orders join customers on orders.order_customer_id=customers.customer_id
join order_items on orders.order_id=order_items.order_item_order_id 
join products on order_items.order_item_product_id=products.product_id
join categories on products.product_category_id=categories.category_id
​
limit 10;

排序操作

---

1. 排序

1.1 order by（全局排序）

作用：全局排序，只有一个reducer（多个reducer无法保证全局有序）。

升序ASC（ascend），降序DESC（descend）

因为全局排序比较耗时，防止单个reducer处理时间过长，当数据规模大时，需设置hive.mapred.mode=strict ，后面必须跟limit子句。

查询员工信息按工资降序排序
select * from employInfo order by employSalary desc;

1.2 sort by（每个Reducer内部排序）

sort by 的数据只能保证在同一reduce中的数据可以按指定字段排序。 使用sort by 你可以指定执行的reduce 个数 （set mapred.reduce.tasks=）,对输出的数据再执行归并排序，即可以得到全部结果。

1.3 distribute by（分区排序）

distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。distribute by要写在sort by前面。

#设置reducer的个数为4
set mapreduce.job.reduces=4;
#将查询结果导入到文件中（按照部门编号分区且按照薪资降序排序）
select * from employInfo distribute by deptID sort by employSalary desc;

1.4 cluster by

作用：当distribute by和sorts by字段相同时，可以使用cluster by方式代替。 但是排序只能是升序排序，不能像distribute by一样去指定排序的规则为ASC或者DESC，否则报错。

select * from employInfo cluster by deptID;
select * from employInfo distribute by deptID sort by deptID;

2. 分组聚合

聚合函数是指对一组值进行计算并返回单个值的函数，通常与select语句的group by子句一起使用。

2.1 分组 Group by

🆘 使用Group by时，在select后面出现的非聚合函数字段也要出现在Group by后面。

#deptno是非聚合字段，需要出现在Group by后面
select deptno from emp group by deptno;
#avg_sal是聚合字段，不需要出现在Group by后面
select deptno,avg(salary) avg_sal from emp group by deptno;

2.2 基础聚合

sum(col) : 计算指定列的总和。

count(*) : 计算行数，可以计算所有行或指定列的非空值数。

max(col) : 返回指定列中的最大值。

min(col) : 返回指定列中的最小值。

avg(col) : 计算指定列的平均值。

2.3 高级聚合

2.3.1 GROUPING SETS

GROUPING SETS 可以用于对多个列组合进行分组聚合。

2.3.2 CUBE和ROLLUP

ROLLUP 是一种特殊的 GROUP BY，用于创建层次化的聚合数据。

CUBE 类似于 ROLLUP，但会生成所有可能的组合汇总。

2.4 技能实训

查询销售金额前10名的顾客

select a.id ,sum(a.subtotal) total
from 
(select orders.order_customer_id id,order_items.order_item_subtotal subtotal
from 
orders join customers on orders.order_customer_id=customers.customer_id
join order_items on orders.order_id=order_items.order_item_order_id) a
group by a.id order by total desc
limit 10;

窗口函数

扫描多个输入行以计算各输出值，可为每行数据分别生成一行结果记录，几乎所有聚合计算都可以通过它来完成。

over（）

#两种固定搭配，不能变更
function(arg1,...)OVER(partition by…order by…)
function(arg1,...)OVER(distribute by…sort by…)

1.排序类

1.1 ROW_NUMBER( )

基于over对象分组、排序地结果，为每行分组记录返回一个序号。该序号从1开始递增，遇到新组则重新从1开始编号，且编号总是连续的。

1.2 RANK（）

类似ROW_NUMBER( )，但规则不同，RANK() 排序相同时会重复，总数不会变 1、1、 3、4

1.3 DENSE_RANK()

排序相同时会重复，总数会减少 1、1、 2、3

ROW_NUMBER() 会根据顺序计算1、2、3、4

函数名	解释
ROW_NUMBER( )	会根据顺序计算1、2、3、4
ROW_NUMBER( )	排序相同时会重复，总数不会变 1、1、 3、4
DENSE_RANK()	排序相同时会重复，总数会减少 1、1、 2、3

1.4 NTILE(n)

NTILE(n)将OVER对象的分组结果数据集按照顺序平均切分成n片，并为每一行返回一个切片号。

1.5 PERCENT_RANK（）

PERCENT_RANK()函数用于将每行按照(rank - 1) / (rows - 1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数。

PERCENT_RANK()函数返回介于 0 和 1 之间的小数值

2.聚合类

count()：计数，可以和distinct一起用，使用distinct时会很耗资源。 sum()：求和； AVG(): 求平均数； Max()/Min() : 求最大值/最小值；

内置函数

函数概述

函数分类	输入输出
标准函数	以一行数据的一列或多列数据作为输入且返回结果是一个值。
聚合函数	以多行的零列或多列的数据作为输入且返回单一值。
表生成函数	接受零个多多个输入且产生多列或多行输出。

1.查看函数

​

视图和索引

1.视图

视图是隐藏了子查询、连接查询等操作的逻辑结构，由数据库的真实数据组成，是一个与真实表不同的虚拟表。

• 只保存定义而不存储数据

• 行列数据均来自视图所引用的表，将在使用视图时动态生成

• 被引用的列被删除视图会报错，删除基表并不会删除视图，需要手动删除视图

• Hive视图是只读的，不能插入或装载数据，不能用作 LOAD / INSERT / ALTER 的目标

1.1视图使用场景

1.一些真实表对未授权的用户进行查看限制，可以选取能提供用户的列，并授权用户查看（如用户密码等）。

2.查询语句非常复杂时，如多表关联及多重子查询，可以用视图来代替复杂查询语句，简化查询语句。

1.2视图优势

简化用户对数据的理解，只关注相关的数据而不是全部数据。

降低查询复杂度。

1.3 视图基本操作

1.3.1 创建视图

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name   -- 视图名称
  [(column_name [COMMENT column_comment], ...) ]    --列名
  [COMMENT view_comment]  --视图注释
  [TBLPROPERTIES (property_name = property_value, ...)]  --额外信息
  AS SELECT ...;

1.3.2 查看视图

-- 查看所有视图： 没有单独查看视图列表的语句，只能使用 show tables
show tables;
-- 查看某个视图
desc view_name;
-- 查看某个视图详细信息
desc formatted view_name;

1.3.3 修改视图

ALTER VIEW [db_name.]view_name AS select_statement;

1.3.4 删除视图

DROP VIEW [IF EXISTS] [db_name.]view_name;

1.4 物化视图和侧视图

1.4.1 物化视图（ Materialized View）

对数据做的一个快照，在执行复杂的表连接或聚集等耗时较多的查询操作时，可以使用物化视图预先计算并保存计算结果。

与普通视图的区别：

物化视图会存储数据，与表一样的特征。

普通视图不会存储数据，是只有表结构的虚拟表。

1.4.2 侧视图（Lateral View）