hive
一、Hive入门
1.hive基础讲解
什么是hive
Hive是建立在hadoop之上的数据仓库的基础架构。它提供了一些工具，可以用来进行数据提取、转化、加载（ETL）,这是一种储存、查询和分析储存在hadoop中的大规模数据的机制。Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。
支持创建索引，优化数据查询。
不同的储存类型，例如：纯文本文件、Hbase中的文件。
将原数据保存在关系型数据库中。
可以将数据储存在HDFS分布式文件系统中
内置大量的用户函数UDF来操作时间、字符串、和其他的数据挖掘工具、支持用户扩展UDF函数来完成内置函数无法完成的复杂操作。
类查询方式，将sql查询查询转为mapReduce的job，在hadoop集群上执行。
2.主要分为以下几部分：
-
用户接口：
用户接口主要分为：CLI、Clinet、WUI，其中最常用的是Cli命令行的模式，Cli启动的时候会启动一个Hive的副本，Client是hive的客户端，用户连接至Hive Server.在启动Client的时候，需要指出Hive server所在的节点。WUI是通过浏览器访问的方式访问Hive。 -
元数据储存：
Hive将元数据储存在数据库中，如MySql、derby。hive的元数据包括表的名字、表的列以及分区及其属性，表的属性包括是否是否外部表。表的数据所在的目录等。
解释器、编译器、优化器、执行器
解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划储存在HDFS中并随后由MapReduce执行。 -
Hadoop:
hive的数据储存在HDFS中，大部分的查询由MR完成，（不包含*的查询，比如select * from talename） -
hive数据仓库和数据库的区别：
数据库中储存的数据可以对随意其进行增删改查操作
数据库中储存的数据是随用户的操作而动态变化的
数据库中的数据储存容量是有限的，当数据库中的数据过于庞大时，查询效率会下降。数据仓库中的数据存储的是静态的历史数据，我能只能对数据进行添加和查询操作。
数据仓库的容量是很大的，数据仓库中的数据是基于HDFS的，而将数据的元数据储存在metastore中的，基于内存查询的，当client客户端进行查询数据时会很快将数据返回
hive数据仓库会和hbase进行整合，hive将成为client的角色用于储存数据的元信息，hbase进行储存数据，hive基于内存的数据处理方式将有很快的效率。
二、hive执行的流程
- 1）.首先客户端利用Cli命令行的方式提交一个查询任务。
- 2）.Thrift server与Driver进行通信，并且将查询任务发送给Driver。
- 3）.Driver将查询的数据首先从MetaStore元数据仓库中查询，是否有包含用户查询的表名4等信息。如果没有，则Driver直接通过Thrift server向客户端回馈消息。
- 4）.如果查询到了对应的元数据，则Driver会将客户端提交的结构化查询语句进行编译、解释、优化，将HQL首先生成计划树。
- 5）.将对应的查询计划树再发送给Hadoop 转成相对应的MapReduce任务去执行。当然在这里不是对所有的HQL都会转成MR任务，比如select * from table 这种语句不会 转成MR任务，hive的Driver对其做了查询优化。
- 6）.最后hadoop将MR跑出来的结果发送给Driver，Driver通过Thrift server通信，发送给客户端。
1.MetaStore元数据持久化工具–Mysql Driver
一般MetaStore一般是由MYSQL或者Derby来承担，一般储存的是表名，列名，和表的一些属性，是否是外部表等。Derby数据库是Hive自带的Apache的一个数据库，一般用于测试。
- 表是怎么产生的呢？？？
在hive进行启动时，Driver会将HDFS分布式文件系统的数据映射成一张表，MetaStore将将储存的是表的元信息了。
或者是用户在创建了一张表时，通过load方式将数据加载到对应的表中时，这时可以知道，表中是有数据了，而表中的数据会被持久化到HDFS中，在执行MR任务的时候，可以去对应的HIVE存储的文件汇总去看，会在对应的目录下有对应的数据文件信息。
注：使用derby存储方式时，运行hive会在当前目录生成一个derby文件和一个metastore_db目录。这种存储方式的弊端是在同一个目录下同时只能有一个hive客户端能使用数据库
2.时间拉链表
在大量的数据面前，我们无法对特定时间的数据进行查询，或者查看某一个时间点的数据库中数据的变化，这些我们都是无法操作的，而引入时间拉链表，可以解决这些无法执行的操作，这里的时间拉链表相当于对数据进行操作时一段时间的“快照”，也可以理解为历史记录。
3.Hive beeline
首先启动hiveserver2
要在hadoop的core-site.xml添加属性：
hadoop.proxyuser.root.groups
hadoop.proxyuser.root.hosts
执行命令或者重启hadoop集群
bin/hdfs dfsadmin -fs hdfs://node01:8020 -refreshSuperUserGroupsConfiguration
bin/hdfs dfsadmin -fs hdfs://node02:8020 -refreshSuperUserGroupsConfiguration
Beeline要与hiveserver2配合使用
客户通过beeline两种方式连接到hive:
beeline -u jdbc:hive2://node:10000/default -n root
-默认用户名、密码不验证
beeline
!connect jdbc:hive2//<host>:<port>/<db> root 123
4、Hive数据库设计中的拉链表、增量表、全量表
1.存量、流量、增量
1）存量:系统在某一点时所保存的所有数量
2）流量：某一个时间点上流入、流出系统的数量
3）增量：某一个时间段内，系统中数据量的变化
增量=输入量-输出量
4）本期期末储存量=上期期末存量+本期增量
2.拉链表
1）记录一个实物从开始当当前所有的状态变化
2）拉链表每次上报的是历史记录的最终状态，是记录在当前时刻的历史总量。
3）存量是某一时刻的总量，存量一般设计成拉链表。（工作中常用月报、日报）
4）流量和存量的区别:流量是增量，存量是总量。
5）拉链表和增量表的表结构一般是一样的。
时间拉链表既能够反应数据的历史状态，有可以最大程度地节省储存空间。
三、HIve的分区、分桶
1.分区
hive中对数据的分区是对数据在HDFS上储存是分别创建不同的目录，即在不同的目录中储存不同数据，比如将不同日期内的数据进行储存的时候，在HDFS上创建不同日期的目录，用于储存对应不同日期内的数据。
对不同的数据进行分区后如果在对不同的日期中查询时，可以根据需求再不同的日期目录下进行查询，这样既节省了了查询时间又减少了磁盘读写的IO操作
如果对数据不进行分区，那么当查询数据时，都会对数据进行全量查询，每次全量查询不光浪费时间还增加了IO量
分区优点：
对不同的数据进行分区后如果在对不同的日期中查询时，可以根据需求再不同的日期目录下进行查询，这样既节省了了查询时间又减少了磁盘读写的IO操作
如果对数据不进行分区，那么当查询数据时，都会对数据进行全量查询，每次全量查询不光浪费时间还增加了IO量
- 分区分为动态分区和静态分区：
分区时，分区的属性不能出现了数据表列中。分区的字段必须在创建表的时候就给定。
静态分区：在创建表时对分区的字段必须给定，而且还要给定值
动态分区：在创建表的时候给定分区字段就可以了，在插入数据的时候，分区自动的值可以自行设置。
分区表：
- 创建静态分区表：
create table psn3(
id int,
name string,
address array<string>,
hobby map<string,string>
)
partitioned by (age=20,age=10)
row format delimited
fields terminated by ','
collection items
map keys terminated by ':'
- 动态分区
create table psn4(
id int,
name string,
address array<string>,
hobby map<string,string>
)
partitioned by (age,int,date Date)
row format delimited
fields terminated by ','
collection items
map keys terminated by ':'
Hive删除分区语法：
alter table table_name drop partition_spec,partition_spec...
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
用户可以用 alter table drop partition 来删除分区。
内部表中、对应分区的元数据和数据将被一并删除。
例：
alter table day_hour_table drop partition (dt='2008-08-08', hour='09')
2.分桶
对于每一个表或者分区，hive可以进一步组织成桶，也就是说更为细粒度的数据划分，可以在分区的基础上在进行分桶，分桶的规则是，将一组数据，例如Day01里有北京、上海、广州的数据。Day02中同样有北京上海广州的数据，现在按照日期进行分区，分区后：day01->{北京，上海，广州}，day02->{北京，上海，广州}，如果在分区内进行查询北京的数据那么还是要进行全量的遍历，那么现在进行对数据对每个分区内的数据进行分桶，对分区中的数据分别对北京上海广州的hashCode哈希值进行对桶数（这里默认是3），则将会得到北京、上海、广州的数据分别对应一个不同的值，则将对应的数据放入对应的同种，这样在查询不同城市的数据时，只需找对应的桶就行了，无序进行全量遍历，当然这里的分区分桶都是根据实际需求的。即工作中根据经常查询的字段我们可以进行分区或者分桶，进行数据的查询。
分桶的实质就是对分桶表的字段做哈希计算，然后存放到对应文件中，所以如果原有数据没有按key哈希，在存放数据插入分桶的时候进行hash,也就是说在向分桶表中插入数据的时候必然要指定一次MapReduce。这也决定了分桶表中的数据只能从所查询的结果集中查询插入的方式进行插入数据。
分桶的特点；
- 1)分桶表是对列值取哈希值的方式，将不同的数据放到不同文件中储存。
- 2)对于hive中的每一张表，分区都可以进一步进行分桶
- 3)由列的哈希值来除以桶的个数来决定每条数据是放到哪个桶中。
*)开启支持分桶：
set hive.enforce.bucketing=true;
默认：false
开启自动分桶后mr会根据bucket的个数自动创建reducer task的个数。(用户也可以自动设置桶的个数：mapered.reduce.task但分桶时不推荐使用)
注意：一次作业产生的桶的数量(文件的数量)和task的数量是一致的。
- 创建普通表
create table psn1(
id int,
name string,
age int
)
row format delimited
fields terminated by ','
//加载数据
load data local inpath '/root/data3' into table psn1;
- 创建分桶表：
create table psn2(
id int,
name string,
age int
)
clustered by (age) into 4 buckets
row format delimited
fields terminated by ',';
加载分桶数据：
from psn1
insert into psn2
select id,name,age
-----------分桶的使用场景和优点
- 1）.方便抽样
- 2）.提高join的查询效率
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
—分桶的一个主要优势就是数据抽样，
主要有两种方式
1）基于桶抽样
tablesample是抽样语句，语法：tablesample(backet x outof y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。
x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据
2、基于百分比抽样
hive另外一种按照抽样百分比进行抽样的方式，该种方式基于行数，按照输入路径下的数据块的百分比进行抽样。
这种抽样的最小单元是一个hdfs数据块，如果表的数据大小小于普通块大小128M,将返回所有行。
数据分桶存在的一些缺陷：
如果通过数据文件LOAD 到分桶表中，会存在额外的MR负担。
实际生产中分桶策略使用频率较低，更常见的还是使用数据分区。
3.Hive SerDe
Hive SerDe -Serializer and Deserializer
-SerDe 用于做序列化和反序列化
-构建在数据储存和执行引擎之间，对两者实现解耦。
-Hive通过row format delimited 以及SerDe进行读写。
Hive正则匹配
CREATE TABLE logtbl (
host STRING,
identity STRING,
t_user STRING,
time STRING,
request STRING,
referer STRING,
agent STRING)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) \\[(.*)\\] \"(.*)\" (-|[0-9]*) (-|[0-9]*)"
)
stored as textfile;
序列化和反序列化的作用：
序列化是对象转换为字节序列的过程。 反序列化是字节序列恢复为对象的过程。 对象的序列化主要有两种用途：对象的持久化，即把对象转换成字节序列后保存到文件中；对象数据的网络传送。 除了上面两点， hive的序列化的作用还包括：Hive的反序列化是对key/value反序列化成hive table的每个列的值。Hive可以方便的将数据加载到表中而不需要对数据进行转换，这样在处理海量数据时可以节省大量的时间。
SerDe说明hive如何去处理一条记录，包括Serialize/Deserilize两个功能， Serialize把hive使用的java object转换成能写入hdfs的字节序列，或者其他系统能识别的流文件。Deserilize把字符串或者二进制流转换成hive能识别的java object对象。比如：select语句会用到Serialize对象， 把hdfs数据解析出来；insert语句会使用Deserilize，数据写入hdfs系统，需要把数据序列化。
4.hive创建表
—内部表和外部表的区别：
1.创建： 外部表需要指定一个储存路径
内部表不需要，储存在默认路径中
2.删除 内部表会把元数据和数据文件全部删除
外部表只删除元数据，不删除数据
使用场景：
如果hdfs中不存在任何已有的数据，表和数据都是新插入的，则是由内部表
如果hdfs中已经在某一个目录汇中有储存的数据，则使用外部表使用
- (1)普通建表-内部表
reate table psn
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
- (2)创建外部表
create external table psn2
(
id int,
name string,
likes array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/usr/';
- (3)创建分区表-静态分区
create table tbl1
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
//向静态分区中导入数据：
load data local inpath '/root/data' into table tbl1 partition (age=10)
- (4)创建动态分区
create table tb2
(
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by (age int,gender string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
添加分区的值（不是添加分区）
alter table psn6 add partition(int name)
只能添加已经有的分区的值
//删除分区
alter table tablename drop partition(age=12)
//删除age=12的分区
向表中插入数据
- （1）load
load data local inpath '/data' insert into psn2
local:从本地将数据复制一份上传到表中
load data inpath '/data' insert into psn2
将HDFS中的数据移动过来，而不是复制了。
- (2)查询插入
from tablename
insert into newtablename
select *
- (3)like，只复制表结构
create table psn8 like psn7;
- (4)as，表结构和表数据都复制
create table psn9 as select * from psn7;
四、hive的授权管理
三种授权模型：
- 1.Storage Based Authorization in the Metastore Server
基于存储的授权-可以对MetaStore中的元数据进行保护，但是没有提供更加细粒度的访问控制(例如：列级别、行级别) - 2.Sql Standards Based Authorization in HiveServer2
基于SQL标准的hive授权-完全兼容sql的授权模型，推荐使用该模式。 - 3.Default Hive Authorization(Legacy Mode)
hive默认授权,设计的目的仅仅只是为了防止用户产生误操作，而不是防止恶意用户未授权的数据。
Hive - SQL Standards Based Authorization in HiveServer2
限制：–阻止好人做错事 - 1）、启用当前认证方式之后，dfs, add, delete, compile, and reset等命令被禁用。
- 2）、通过set命令设置hive configuration的方式被限制某些用户使用。
（可通过修改配置文件hive-site.xml中hive.security.authorization.sqlstd.confwhitelist进行配置） - 3）、添加、删除函数以及宏的操作，仅为具有admin的用户开放。
- 4）、用户自定义函数（开放支持永久的自定义函数），可通过具有admin角色的用户创建，其他用户都可以使用。
- 5）、Transform功能被禁用。
权限操作
将角色授予某个用户、角色：
GRANT role_name [, role_name] ...
TO principal_specification [, principal_specification] ...
[ WITH ADMIN OPTION ];
principal_specification
: USER user
| ROLE role
移除某个用户、角色的角色：
REVOKE [ADMIN OPTION FOR] role_name [, role_name] …
FROM principal_specification [, principal_specification] … ;
-
principal_specification
-
USER user
| ROLE role
revoke [admin option for] role_name [,role_name] … from principal_specification [,principal_specification…]
查看授予某个用户、角色的角色列表
show role grant (user|role) principal_name;
查看属于某种角色的用户、角色列表
show principals role_name;
不进行登录hive进行查询数据
hive -e "select * from psn1"
还可以执行多条sql
hive -e "select * from psn1;select * from psn2"
执行查询并进行重定向
hive -e "select * from psn1" >/root/aaa.txt
//语法
hive -e 'sql语句'
--执行文件中的sql
vim sql
select * from psn2;
//执行sql文件
hive -f sql
创建表
创建表
create table psn3(
id int,
name string,
hobby array<string>,
address map<string,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';
//加载数据
load data local inpath '/root/data1' into table psn3;
//lateral view语法
select count(distinct(myCol1)), count(distinct(myCol2)) from psn3
LATERAL VIEW explode(hobby) myTable1 AS myCol1
LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;
lateral view的作用：
1.结束了explode的局限性，explode(column)只能处理单一的列
2.和explode结合实用扩展了实用性
select id,myCol1,myCol2 from psn3
lateral view explode(hobby) myTable1 as myCol1
lateral view explode(address) myTable2 as myCol2,myCol3;
六、View视图
- hive视图：
和关系型数据库中的普通视图一样，hive也支持视图 - 视图特点：
1.不支持物化视图
2.只能做查询，不能做加载数据操作
3.视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
4.view定义中包含了order by/limit 语句，当查询视图时也进行order by/limit 语句的操作，view当中定义的优先级更高
5.view支持迭代视图
-### ---------索引------------
目的：优化查询以及检索性能
创建索引：
create index t1_indx on table psn2(name)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
in table t1_index_table;
as 指定索引器：
in table：指定索引表，若不指定，默认生成在default_psn2_index表中
create index t2_index on table psn2(name)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;
//查看索引：
show index on tablename;
//重建索引(必须建立索引后才能使用，向索引表中插入数据)：
alter index t1_indx on psn2 rebuild;
//删除索引表：
drop index if exists t1_index on psn2;
drop index t1_index on psn2;
七、Hive优化
思想：把hive sql转换成mapreduce去优化。
理论上hive中的sql语句都可以转换位MapReudce去执行。
1.hive的抓取策略
设置参数
Set hive.fetch.task.conversion=none/more
hive中做了sql的优化，hive对一下hql语句不会执行mapreduce语句
select仅查询本表字段
select * from tablename;
where仅对本表字段做条件过滤
select * form tablename where …;
2.explain显示执行计划
explain [extended] query
例子：explain extended select count(*) from psn2;
3.并行计算
通过设置参数开启并行模式
set hive.exec.parallel=true;
注意：
hive.exec.parallel.thread.number
一次sql计算中允许并行执行的MR job的最大值，默认是8
4.严格模式
设置一下参数开启严格模式
(默认为：nonstrict非严格模式)
-查询限制
1）对于分区表，必须添加where对于分区字段的条件过滤
2）order by 语句必须包含limited输出闲置
3)限制笛卡尔积的查询
5.hive的排序
order by:对于查询结果做全排序，只允许一个reduce进行处理。将数据量较大时谨慎使用，最后不用，严格模式下结合limit来使用
Sort by : Hive中指定了sort by，那么在每个reducer端都会做排序，也就是说保证了局部有序（每个reducer出来的数据是有序的，但是不能保证所有的数据是有序的，除非只有一个reducer），好处是：执行了局部排序之后可以为接下去的全局排序提高不少的效率（其实就是做一次归并排序就可以做到全局排序了）。
Distribute by分区排序，经常和sort by 结合使用，控制map输出在reducer上是如何划分的。
Cluster by：相当于sort by +distribute by 几乎不用
(cluster by 不能通过asc,desc的方式制定排序规则)
可通过distribute by column sort by column asc|desc的方式
6.hive join
join计算时，将小表（驱动表）放在join的左边
map join:在map端完成join.
两种实现方式：
-
1)sql方式 ，在sql语句中添加map join 标记（mapjoin hint）
语法：
select /+MAPJOIN(smalltable)/ smallTable.key ,bigTable.value
from smallTable
join bigTable on smallTable.key=bigTable.key; -
2.开启自动的mapjoin
通过修改以下配置启动自动的mapjoin
通过修改以下配置启用自动的mapjoin:
set hive.auto.convert.join=true;
该参数为true时，两份数据时，如果有一份数据比较小，会将小数据全部加载到内存，按照关键字进行索引。大的数据文件 作为map的输入文件，对于map的每一对输入，都能够方便地和已经加载到内存中的小文件进行连接，将连接好的的文件按key输出。
这种方法要使用hadoop中的DistributedCache把小数据文件分散到不同的节点上。
8934
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
