大数据Hive的案例、参数、动态分区、分桶、视图、索引、运行方式、权限管理、Hive的优化_03_03

一、案例：统计出掉线率最高的前10基站

需求：统计出掉线率最高的前10基站
数据：
record_time：通话时间
imei：基站编号
cell：手机编号
drop_num：掉话的秒数

duration：通话持续总秒数

1.建表
create table cell_monitor(
record_time string,
imei string,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate DOUBLE,
net_type string,
erl string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

结果表

create table cell_drop_monitor(
imei string,
total_call_num int,
total_drop_num int,
d_rate DOUBLE
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

加载数据到cell_monitor表

load data local inpath '/usr/local/cdr_summ_imei_cell_info.csv' OVERWRITE INTO TABLE cell_monitor;

找出掉线率最高的基站
from cell_monitor cm 
insert overwrite table cell_drop_monitor  
select cm.imei ,sum(cm.drop_num),sum(cm.duration),sum(cm.drop_num)/sum(cm.duration) d_rate 
group by cm.imei 
sort by d_rate desc;

二、Hive案例Wordcount

1、建表
create table docs(line string);
create table wc(word string, totalword int);

2、加载数据
load data local inpath '/usr/local/words' into table docs;
3、统计
from (select explode(split(line, ' ')) as word from docs) w 
insert into table wc 
  select word, count(1) as totalword 
  group by word 
  order by word;
4、查询结果

select * from wc;

三、hive参数

hive 参数、变量
hive当中的参数、变量，都是以命名空间开头

命名空间	读写权限	含义
hiveconf	可读写	hive-site.xml当中的各配置变量 例：hive --hiveconf hive.cli.print.header=true
system	可读写	系统变量，包含JVM运行参数等 例：system:user.name=root
env	只读	环境变量 例：env:JAVA_HOME
hivevar	可读写	例：hive -d val=key

当使用hive启动客户端时 带上参数 hive --hiveconf hive.cli.print.header=true 那么在使用参数查询数据的时候就会出现表头信息

hive 参数设置方式

1、修改配置文件 ${HIVE_HOME}/conf/hive-site.xml
2、启动hive cli时，通过--hiveconf key=value的方式进行设置
例：hive --hiveconf hive.cli.print.header=true
3、进入cli之后，通过使用set命令设置

hive set命令
在hive CLI控制台可以通过set对hive中的参数进行查询、设置
set设置：
set hive.cli.print.header=true;
set查看
set hive.cli.print.header
hive参数初始化配置
当前用户家目录下的.hiverc文件
如:   ~/.hiverc
如果没有，可直接创建该文件，将需要设置的参数写到该文件中，hive启动运行时，会加载改文件中的配置。
hive历史操作命令集
~/.hivehistory

四、Hive动态分区

分区的作用：就是把文件目录划分为一个个的子目录方便对文件的管理。

开启支持动态分区
set hive.exec.dynamic.partition=true;
默认：false
set hive.exec.dynamic.partition.mode=nostrict;
默认：strict（至少有一个分区列是静态分区）
相关参数
set hive.exec.max.dynamic.partitions.pernode;
每一个执行mr节点上，允许创建的动态分区的最大数量(100)
set hive.exec.max.dynamic.partitions;
所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)
set hive.exec.max.created.files;
所有的mr job允许创建的文件的最大数量(100000)

示例：

创建表

create table psn10(
id int,
name string,

sex string,

age int,
likes ARRAY<string>,
address MAP<string, string>
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
COLLECTION ITEMS TERMINATED BY '-' 
MAP KEYS TERMINATED BY ':';

基础数据 data3

1,xiaoming1,man,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
2,xiaoming2,man,32,book-sleep-mv,beijing:xisanqi-shanghai:pudong
3,xiaoming3,girl,20,book-sleep-mv,beijing:xisanqi-shanghai:pudong
4,xiaoming4,girl,22,book-sleep-mv,beijing:xisanqi-shanghai:pudong
5,xiaoming5,man,42,book-sleep-mv,beijing:xisanqi-shanghai:pudong

加载数据到创建的表中

load data local inpath '/usr/local/data3' into table psn10;

接着创建一张分区表(分区的字段是sex、age)

create  table psn11 (
id int,
name string,
likes ARRAY<string>,
address MAP<string, string>
)

partitioned by (sex string,age int)

ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
COLLECTION ITEMS TERMINATED BY '-' 
MAP KEYS TERMINATED BY ':';

开启支持动态分区
set hive.exec.dynamic.partition=true;
默认：false
set hive.exec.dynamic.partition.mode=nostrict;
默认：strict（至少有一个分区列是静态分区）

加载数据

from psn10
insert overwrite table psn11 partition(sex, age)  
select id, name, likes, address,sex,age distribute by age, sex;

注意加载时数据字段的顺序 ，如果不知道具体的字段顺序，可以使用命令desc psn11，查看表中字段的顺序。

查看MapReduce的执行情况

查看动态分区的目录结构

注意：动态分区和静态分区的区别

           静态的分区在使用load data...命令加载数据的时候要指定分区，同时给分区设置值。

           动态分区加载数据是从已经创建的某个表中的数据信息中获取的，其加载的方式使用from insert...（分区的字段是源数据表中的某个字段）

           同时注意加载字段的顺序，与动态分区表中的字段顺序一致。

           在动态分区加载数据前必须通过set命令开启支持动态分区、以及设置分区的模式为非严格模式。

动态分区使用的情形

比如说有一大批数据，我们需要这些数据按照年份、月份、甚至某一天进行分区，这样方便数据的管理。

这个时候我们就会使用动态分区的方式来管理这些数据。

五、分桶

hive 分桶
分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储。
对于hive中每一个表、分区都可以进一步进行分桶。
由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
适用场景：
数据抽样（ sampling ）、map-join

往分桶表中加载数据
insert into table bucket_table select columns from tbl;
insert overwrite table bucket_table select columns from tbl;

桶表 抽样查询
select * from bucket_table tablesample(bucket 1 out of 4 on columns);

TABLESAMPLE语法：
TABLESAMPLE(BUCKET x OUT OF y)
x：表示从哪个bucket开始抽取数据
y：必须为该表总bucket数的倍数或因子

例：
当表总bucket数为32时
TABLESAMPLE(BUCKET 2 OUT OF 16)，抽取哪些数据？
共抽取2（32/16）个bucket的数据，抽取第2、第18（16+2）个bucket的数据
TABLESAMPLE(BUCKET 3 OUT OF 256)，抽取哪些数据？ 抽取第三个桶的1/8数据

示例：

开启支持分桶

set hive.enforce.bucketing=true;
默认：false；设置为true之后，mr运行时会根据bucket的个数自动分配reduce task个数。（用户也可以通过mapred.reduce.tasks自己设置reduce任务个数，但分桶时不推荐使用）
注意：一次作业产生的桶（文件数量）和reduce task个数一致。

CREATE TABLE psn13( id INT, name STRING, age INT)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
测试数据：data4
1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88

加载测试数据
load data local inpath '/usr/local/data4' into table psn13;
创建分桶表
CREATE TABLE psnbucket( id INT, name STRING, age INT)
CLUSTERED BY (age) INTO 4 BUCKETS 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

加载源数据信息到分桶表中

insert into table psnbucket select id, name, age from psn13;

查看目录

抽样

select id, name, age from psnbucket tablesample(bucket 2 out of 4 on age);      ####抽取一个桶的数据（4/4）这里的2是抽取第二个桶的数据。

select id, name, age from psnbucket tablesample(bucket 2 out of 8 on age);     ####抽取1/2个桶的数据（4/8）这里的2是抽取第二个桶的1/2数据。

select id, name, age from psnbucket tablesample(bucket 2 out of 2 on age);     ####抽取2个桶的数据（4/2）这里的2是指从第2、4（2+2）个桶取数据。

总结：

当表总bucket数为32时
TABLESAMPLE(BUCKET 3 OUT OF 16)，抽取哪些数据？
1、取多少个桶的数据
桶的个数/Y = 32/16 = 2 个桶
2、X=3  从3个桶开始取数据
  第一个  3个桶  
  第二个  每隔Y=16再取一个桶  16+3=19
当表总bucket数为32时
TABLESAMPLE(BUCKET 3 OUT OF 96)，抽取哪些数据？
1、取多少个桶的数据
桶的个数/Y = 32/96 = 1/3 个桶
2、X=3 第3个桶  1/3

六、分区的基础基础上分桶

为了对数据的更加细化，我们可以将数据在分区的基础上进行分桶。

开启支持动态分区
set hive.exec.dynamic.partition=true;
默认：false
set hive.exec.dynamic.partition.mode=nostrict;
默认：strict（至少有一个分区列是静态分区）

开启支持分桶

set hive.enforce.bucketing=true;
默认：false；设置为true之后，mr运行时会根据bucket的个数自动分配reduce task个数。（用户也可以通过mapred.reduce.tasks自己设置reduce任务个数，但分桶时不推荐使用）

基础测试数据

11,xiaoming11,man,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
12,xiaoming12,gril,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
13,xiaoming13,gril,22,book-sleep-mv,beijing:xisanqi-shanghai:pudong
14,xiaoming14,man,22,book-sleep-mv,beijing:xisanqi-shanghai:pudong
15,xiaoming15,man,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
16,xiaoming16,man,32,book-sleep-mv,beijing:xisanqi-shanghai:pudong
17,xiaoming17,man,32,book-sleep-mv,beijing:xisanqi-shanghai:pudong
18,xiaoming18,gril,32,book-sleep-mv,beijing:xisanqi-shanghai:pudong
19,xiaoming19,gril,32,book-sleep-mv,beijing:xisanqi-shanghai:pudong
20,xiaoming20,gril,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
21,xiaoming21,gril,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
22,xiaoming22,gril,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
23,xiaoming23,gril,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
24,xiaoming24,gril,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
25,xiaoming25,man,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
26,xiaoming26,man,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
27,xiaoming27,man,31,book-sleep-mv,beijing:xisanqi-shanghai:pudong
28,xiaoming28,man,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
29,xiaoming29,man,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong
30,xiaoming30,man,33,book-sleep-mv,beijing:xisanqi-shanghai:pudong

创建源数据表

create table psn14 (
id int,
name string,
sex string,
age int,
likes ARRAY<string>,
address MAP<string, string>
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
COLLECTION ITEMS TERMINATED BY '-' 
MAP KEYS TERMINATED BY ':';

加载数据

load data local inpath '/usr/local/data5' into table psn14;

创建分区分桶表

create table psnbucket2 (
id int,
name string,
age int,
likes ARRAY<string>,
address MAP<string, string>
)
PARTITIONED BY (sex string) 
CLUSTERED BY (age) INTO 4 BUCKETS 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
COLLECTION ITEMS TERMINATED BY '-' 
MAP KEYS TERMINATED BY ':';

加载源数据到分区分桶表

from psn14
insert overwrite table psnbucket2 partition(sex)  
select id, name, age, likes, address, sex distribute by sex;

七、Hive Lateral View 

ive Lateral View
Lateral View用于和UDTF函数（explode、split）结合来使用。
首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
主要解决在select使用UTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题

语法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

八、hive View视图

和关系型数据库中的普通视图一样，hive也支持视图
特点：
不支持物化视图
只能查询，不能做加载数据操作
视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级更高
view支持迭代视图

View语法

创建视图：
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name 
  [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ... ;
查询视图：
select colums from view;
删除视图：
DROP VIEW [IF EXISTS] [db_name.]view_name;

九、Hive 索引

Hive 索引
目的：优化查询以及检索性能
创建索引：
create index t1_index on table psn14(name) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild 
in table t1_index_table;
as：指定索引器；
in table：指定索引表，若不指定默认生成在default__psn14_t1_index__表中

create index t1_index on table psn14(name) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;
查询索引
show index on psn2;
重建索引（建立索引之后必须重建索引才能生效）
ALTER INDEX t1_index ON psn14 REBUILD;
删除索引
DROP INDEX IF EXISTS t1_index ON psn2;

十、Hive的运行方式

Hive运行方式：
命令行方式cli：控制台模式
脚本运行方式（实际生产环境中用最多）
JDBC方式：hiveserver2
web GUI接口 （hwi、hue等）

Hive在CLI模式中
与hdfs交互
执行执行dfs命令
例：dfs –ls /
与Linux交互
！开头
例： !pwd

Hive脚本运行方式：
hive -e ""
hive -e "">aaa    
hive -S -e "">aaa
hive -f file
hive -i /home/my/hive-init.sql    
hive> source file (在hive cli中运行)

在/usr/local目录下创建一个sql文件

vi sql

select * from psn14 limit 8;
select * from wc ;

然后保存。

执行脚本：hive -f /usr/local/sql

Hive GUI接口

Hive Web GUI接口
web界面安装：
下载源码包apache-hive-*-src.tar.gz

在部署hive服务器的地方设置（也就是weekend11节点上配置）
将hwi war包放在$HIVE_HOME/lib/
制作方法：将hwi/web/*里面所有的文件打成war包
cd apache-hive-1.2.1-src/hwi/web
jar -cvf hive-hwi.war *
复制tools.jar(在jdk的lib目录下)到$HIVE_HOME/lib下
修改hive-site.xml
启动hwi服务(端口号9999)启动hive服务器（weekend11节点上执行）
hive --service hwi
浏览器通过以下链接来访问
http://weekend11:9999/hwi/

修改hive配置文件hive-site.xml添加以下配置内容：

<property>
    <name>hive.hwi.listen.host</name>
    <value>0.0.0.0</value>
  </property>
  <property>
    <name>hive.hwi.listen.port</name>
    <value>9999</value>
  </property>
  <property>
    <name>hive.hwi.war.file</name>
    <value>lib/hive-hwi.war</value>
 </property>

此方式体验度不是很好，不过适合查看元数据信息。

十一、Hive权限管理

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
完全兼容SQL的授权模型
除支持对于用户的授权认证，还支持角色role的授权认证
role可理解为是一组权限的集合，通过role为用户授权
一个用户可以具有一个或多个角色
默认包含另种角色：public、admin

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功能被禁用。
Hive - SQL Standards Based Authorization in HiveServer2
在hive服务端修改配置文件hive-site.xml添加以下配置内容：

<property>
  <name>hive.security.authorization.enabled</name>
  <value>true</value>
</property>
<property>
  <name>hive.server2.enable.doAs</name>
  <value>false</value>
</property>
<property>
  <name>hive.users.in.admin.role</name>
  <value>root</value>
</property>
<property>
  <name>hive.security.authorization.manager</name>
  <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value>
</property>
<property>
  <name>hive.security.authenticator.manager</name>
  <value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value>
</property>
服务端启动hiveserver2；客户端通过beeline进行连接

Hive权限管理
角色的添加、删除、查看、设置：
CREATE ROLE role_name;  -- 创建角色
DROP ROLE role_name;  -- 删除角色
SET ROLE (role_name|ALL|NONE);  -- 设置角色
SHOW CURRENT ROLES;  -- 查看当前具有的角色
SHOW ROLES;  -- 查看所有存在的角色

十二、Hive的优化

Hive 优化
核心思想：把Hive SQL 当做Mapreduce程序去优化
以下SQL不会转为Mapreduce来执行
select仅查询本表字段
where仅对本表字段做条件过滤
Explain 显示执行计划
EXPLAIN [EXTENDED] query;    #######作用：具体的查看sql的执行流程。

Hive运行方式：

本地模式

集群模式

本地模式
开启本地模式：
set hive.exec.mode.local.auto=true;
注意：
hive.exec.mode.local.auto.inputbytes.max默认值为128M
表示加载文件的最大值，若大于该配置仍会以集群方式来运行！

并行计算
通过设置以下参数开启并行模式：
set hive.exec.parallel=true;
注意：hive.exec.parallel.thread.number
（一次SQL计算中允许并行执行的job个数的最大值）

严格模式
通过设置以下参数开启严格模式：
set hive.mapred.mode=strict;
（默认为：nonstrict非严格模式）
查询限制：
1、对于分区表，必须添加where对于分区字段的条件过滤；
2、order by语句必须包含limit输出限制；
3、限制执行笛卡尔积的查询。

Hive排序
Order By - 对于查询结果做全排序，只允许有一个reduce处理
（当数据量较大时，应慎用。严格模式下，必须结合limit来使用）
Sort By - 对于单个reduce的数据进行排序
Distribute By - 分区排序，经常和Sort By结合使用
Cluster By - 相当于 Sort By + Distribute By
（Cluster By不能通过asc、desc的方式指定排序规则；
可通过 distribute by column sort by column asc|desc 的方式）
Hive Join
Join计算时，将小表（驱动表）放在join的左边
Map Join：在Map端完成Join
两种实现方式：
1、SQL方式，在SQL语句中添加MapJoin标记（mapjoin hint）
语法：
SELECT  /*+ MAPJOIN(smallTable) */  smallTable.key,  bigTable.value 
FROM  smallTable  JOIN  bigTable  ON  smallTable.key  =  bigTable.key;
2、开启自动的MapJoin
Hive Join
自动的mapjoin
通过修改以下配置启用自动的mapjoin：
set hive.auto.convert.join = true;
（该参数为true时，Hive自动对左边的表统计量，如果是小表就加入内存，即对小表使用Map join）
相关配置参数：
hive.mapjoin.smalltable.filesize;  
（大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行）
hive.ignore.mapjoin.hint；
（默认值：true；是否忽略mapjoin hint 即mapjoin标记）
hive.auto.convert.join.noconditionaltask;
（默认值：true；将普通的join转化为普通的mapjoin时，是否将多个mapjoin转化为一个mapjoin）
hive.auto.convert.join.noconditionaltask.size;
（将多个mapjoin转化为一个mapjoin时，其表的最大值）

Map-Side聚合
通过设置以下参数开启在Map端的聚合：
set hive.map.aggr=true;
相关配置参数：
hive.groupby.mapaggr.checkinterval： 
map端group by执行聚合时处理的多少行数据（默认：100000）
hive.map.aggr.hash.min.reduction： 
进行聚合的最小比例（预先对100000条数据做聚合，若聚合之后的数据量/100000的值大于该配置0.5，则不会聚合）
hive.map.aggr.hash.percentmemory： 
map端聚合使用的内存的最大值
hive.map.aggr.hash.force.flush.memory.threshold： 
map端做聚合操作是hash表的最大可用内容，大于该值则会触发flush
hive.groupby.skewindata
是否对GroupBy产生的数据倾斜做优化，默认为false
控制Hive中Map以及Reduce的数量
Map数量相关的参数
mapred.max.split.size
一个split的最大值，即每个map处理文件的最大值
mapred.min.split.size.per.node
一个节点上split的最小值
mapred.min.split.size.per.rack
一个机架上split的最小值

Reduce数量相关的参数
mapred.reduce.tasks
强制指定reduce任务的数量
hive.exec.reducers.bytes.per.reducer
每个reduce任务处理的数据量
hive.exec.reducers.max
每个任务最大的reduce数

Hive - JVM重用
适用场景：
1、小文件个数过多
2、task个数过多
通过 set mapred.job.reuse.jvm.num.tasks=n; 来设置
（n为task插槽个数）
缺点：设置开启之后，task插槽会一直占用资源，不论是否有task运行，直到所有的task即整个job全部执行完成时，才会释放所有的task插槽资源！

总结：