Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。
本质是将SQL转换为MapReduce程序。
主要用途：用来做离线数据分析，比直接用MapReduce开发效率更高。
好处-----------使用Hive ：
操作接口采用类SQL语法，提供快速开发的能力
避免了去写MapReduce，减少开发人员的学习成本
功能扩展很方便。
hive组件
用户接口：包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。
元数据存储：通常是存储在关系数据库如 mysql/derby中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
解释器、编译器、优化器、执行器:完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。
Hive利用HDFS存储数据，利用MapReduce查询分析数据。
Hive 数据模型
Hive中所有的数据都存储在HDFS中，没有专门的数据存储格式
在创建表时指定数据中的分隔符，Hive 就可以映射成功，解析数据。
Hive中包含以下数据模型：
db：在hdfs中表现为hive.metastore.warehouse.dir目录下一个文件夹
table：在hdfs中表现所属db目录下一个文件夹
external table：数据存放位置可以在HDFS任意指定路径
partition：在hdfs中表现为table目录下的子目录
bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件
5．Hive 安装部署
Hive安装前需要安装好JDK和Hadoop。配置好环境变量。
根据元数据存储的介质不同，分为下面两个版本，其中derby属于内嵌模式。实际生产环境中则使用mysql来进行元数据的存储。
- 内置derby版：
解压hive安装包
bin/hive 启动即可使用
缺点：不同路径启动hive，每一个hive拥有一套自己的元数据，无法共享 - mysql版：
解压、修改配置文件
vi conf/hive-site.xml
配置Mysql元数据库信息
数据库相关操作
Hive配置单元包含一个名为 default 默认的数据库.
create database [if not exists] <database name>；---创建数据库
show databases; --显示所有数据库
drop database if exists <database name> [restrict|cascade]; --删除数据库，默认情况下，hive不允许删除含有表的数据库，要先将数据库中的表清空才能drop，否则会报错
--加入cascade关键字，可以强制删除一个数据库,默认是restrict，表示有限制的
eg. hive> drop database if exists users cascade;
use <database name>; --切换数据库
分区表（PARTITIONED BY）
分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。
单分区建表语句：create table day_table (id int, content string) partitioned by (dt string);单分区表，按天分区，在表结构中存在id，content，dt三列。
双分区建表语句：create table day_hour_table (id int, content string) partitioned by (dt string, hour string);双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。
导入数据
LOAD DATA local INPATH '/root/hivedata/dat_table.txt' INTO TABLE day_table partition(dt='2017-07-07');
LOAD DATA local INPATH '/root/hivedata/dat_table.txt' INTO TABLE day_hour_table PARTITION(dt='2017-07-07', hour='08');
基于分区的查询：
SELECT day_table.* FROM day_table WHERE day_table.dt = '2017-07-07';
查看分区：
show partitions day_hour_table;
总的说来partition就是辅助查询，缩小查询范围，加快数据的检索速度和对数据按照一定的规格和条件进行管理。
ROW FORMAT DELIMITED（指定分隔符）
create table day_table (id int, content string) partitioned by (dt string) row format delimited fields terminated by ','; ---指定分隔符创建分区表
复杂类型的数据表指定分隔符
create table complex_array(name string,work_locations array<string>) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',';
数据如下：
zhangsan beijing,shanghai,tianjin,hangzhou
wangwu shanghai,chengdu,wuhan,haerbin
create table t_map(id int,name string,hobby map<string,string>)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':' ;
数据：
1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
2,lisi,打游戏:非常喜欢-篮球:不喜欢
内部表、外部表
建内部表
create table student(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';
建外部表
create external table student_ext(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',' location '/stu';
内、外部表加载数据：
load data local inpath '/root/hivedata/students.txt' overwrite into table student;
load data inpath '/stu' into table student_ext;
本地模式
set hive.exec.mode.local.auto=true;
分桶表 cluster by 、sort by、distribute by
指定开启分桶
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
TRUNCATE TABLE stu_buck;
drop table stu_buck;
create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno)
sorted by(Sno DESC)
into 4 buckets
row format delimited
fields terminated by ',';
分桶表导入数据
insert overwrite table stu_buck
select * from student cluster by(Sno) sort by(Sage); 报错,cluster 和 sort 不能共存
insert overwrite table stu_buck
select * from student cluster by(Sno);
对某列进行分桶的同时，根据另一列进行排序
insert overwrite table stu_buck
select * from student distribute by(Sno) sort by(Sage asc);
总结：
cluster（分且排序，必须一样）==distribute（分） + sort（排序）（可以不一样）
增加/删除分区
drop table t_partition;
create table t_partition(id int,name string)
partitioned by (dt string)
row format delimited
fields terminated by ',';
增加分区
alter table t_partition add partition (dt='2008-08-08') location 'hdfs://node-21:9000/t_parti/';
执行添加分区时 /t_parti文件夹下的数据不会被移动。并且没有分区目录dt=2008-08-08
删除分区
alter table t_partition drop partition (dt='2008-08-08');
执行删除分区时/t_parti下的数据会被删除并且连同/t_parti文件夹也会被删除
注意区别于load data时候添加分区:会移动数据 会创建分区目录
Insert查询语句
多重插入：
create table source_table (id int, name string) row format delimited fields terminated by ',';
create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';
from source_table
insert overwrite table test_insert1
select id
insert overwrite table test_insert2
select name;
动态分区插入
set hive.exec.dynamic.partition=true; #是否开启动态分区功能，默认false关闭。
set hive.exec.dynamic.partition.mode=nonstrict; #动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。
需求：
将dynamic_partition_table中的数据按照时间(day)，插入到目标表d_p_t的相应分区中。
原始表：
create table dynamic_partition_table(day string,ip string)row format delimited fields terminated by ",";
load data local inpath '/root/hivedata/dynamic_partition_table.txt' into table dynamic_partition_table;
2015-05-10,ip1
2015-05-10,ip2
2015-06-14,ip3
2015-06-14,ip4
2015-06-15,ip1
2015-06-15,ip2
目标表：
create table d_p_t(ip string) partitioned by (month string,day string);
动态插入：
insert overwrite table d_p_t partition (month,day)
select ip,substr(day,1,7) as month,day
from dynamic_partition_table;
查询结果导出到文件系统
3、将查询结果保存到指定的文件目录（可以是本地，也可以是hdfs）
insert overwrite local directory '/home/hadoop/test'
select * from t_p;
insert overwrite directory '/aaa/test'
select * from t_p;
关于hive中的各种join
准备数据
1,a
2,b
3,c
4,d
7,y
8,u
2,bb
3,cc
7,yy
9,pp
建表：
create table a(id int,name string)
row format delimited fields terminated by ',';
create table b(id int,name string)
row format delimited fields terminated by ',';
导入数据：
load data local inpath '/root/hivedata/a.txt' into table a;
load data local inpath '/root/hivedata/b.txt' into table b;
实验：
** inner join
select * from a inner join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
+-------+---------+-------+---------+--+
**left join
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
+-------+---------+-------+---------+--+
**right join
select * from a right join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 7 | y | 7 | yy |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
**
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id | a.name | b.id | b.name |
+-------+---------+-------+---------+--+
| 1 | a | NULL | NULL |
| 2 | b | 2 | bb |
| 3 | c | 3 | cc |
| 4 | d | NULL | NULL |
| 7 | y | 7 | yy |
| 8 | u | NULL | NULL |
| NULL | NULL | 9 | pp |
+-------+---------+-------+---------+--+
**hive中的特别join
select * from a left semi join b on a.id = b.id;
+-------+---------+--+
| a.id | a.name |
+-------+---------+--+
| 2 | b |
| 3 | c |
| 7 | y |
+-------+---------+--+
相当于
select a.id,a.name from a where a.id in (select b.id from b); 在hive中效率极低
select a.id,a.name from a join b on (a.id = b.id);
cross join（##慎用）
返回两个表的笛卡尔积结果，不需要指定关联键。
select a.,b. from a cross join b;
内置jason函数
select get_json_object(line,'.rate') as rate from rat_json limit 10;
transform案例:
1、先加载rating.json文件到hive的一个原始表 rat_json
create table rat_json(line string) row format delimited;
load data local inpath '/root/hivedata/rating.json' into table rat_json;
2、需要解析json数据成四个字段，插入一张新的表 t_rating
drop table if exists t_rating;
create table t_rating(movieid string,rate int,timestring string,uid string)
row format delimited fields terminated by '\t';
insert overwrite table t_rating
select get_json_object(line,'.rate') as rate,get_json_object(line,'
.uid') as uid from rat_json limit 10;
3、使用transform+python的方式去转换unixtime为weekday
先编辑一个python脚本文件
########python######代码
vi weekday_mapper.py
!/bin/python
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movieid, rating, unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movieid, rating, str(weekday),userid])
保存文件
然后，将文件加入hive的classpath：
hive>add FILE /root/hivedata/weekday_mapper.py;
create table u_data_new as select
transform (movieid, rate, timestring,uid)
using 'python weekday_mapper.py'
as (movieid, rate, weekday,uid)
from t_rating;
select distinct(weekday) from u_data_new limit 10;
hive表关联查询，如何解决数据倾斜的问题?
倾斜原因：
map输出数据按key Hash的分配到reduce中，由于key分布不均匀、业务数据本身的特点、建表时考虑不周、等原因造成的reduce 上的数据量差异过大。
当数据量比较大，并且key分布不均匀，大量的key都shuffle到一个reduce上了，就出现了数据的倾斜。
1)、key分布不均匀;
2)、业务数据本身的特性;
3)、建表时考虑不周;
4)、某些SQL语句本身就有数据倾斜;
如何避免：对于key为空产生的数据倾斜，可以对其赋予一个随机值。
解决方案
1>.参数调节：
hive.map.aggr = true
hive.groupby.skewindata=true
有数据倾斜的时候进行负载均衡，当选项设定位true,生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个Reduce中），最后完成最终的聚合操作。
2>.SQL 语句调节：
常见的数据倾斜出现在group by和join..on..语句中。
join（数据倾斜）
在进行两个表join的过程中，由于hive都是从左向右执行，要注意讲小表在前，大表在后（小表会先进行缓存）。
map/reduce程序执行时，reduce节点大部分执行完毕，但是有一个或者几个reduce节点运行很慢，导致整个程序的处理时间很长，这是因为某一个key的条数比其他key多很多（有时是百倍或者千倍之多），这条key所在的reduce节点所处理的数据量比其他节点就大很多，从而导致某几个节点迟迟运行不完，此称之为数据倾斜。hive在跑数据时经常会出现数据倾斜的情况，使的作业经常reduce完成在99%后一直卡住，最后的１%花了几个小时都没跑完，这种情况就很可能是数据倾斜的原因.
hive.groupby.skewindata=true;
如果是group by过程出现倾斜应将此项设置true。
<property>
<name>hive.groupby.skewindata</name>
<value>false</value>
<description>Whether there is skew in data to optimize group by queries</description>
</property>
hive.optimize.skewjoin.compiletime=true;
如果是join 过程中出现倾斜应将此项设置为true
不影响结果可以考虑过滤空值
<property>
<name>hive.optimize.skewjoin.compiletime</name>
<value>false</value>
</property>
hive.optimize.skewjoin.compiletime=true; 如果是join过程出现倾斜应该设置为true
此时会将join语句转化为两个mapreduce任务，第一个会给jion字段加随机散列
set hive.skewjoin.key=100000; 这个是join的键对应的记录条数超过这个值则会进行优化。
可以在空值前面加随机散列
1)、选用join key分布最均匀的表作为驱动表。做好列裁剪和filter操作，以达到两表做join 的时候，数据量相对变小的效果。
2)、大小表Join：
使用map join让小的维度表（1000 条以下的记录条数）先进内存。在map端完成reduce.
4)、大表Join大表：
把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null 值关联不上，处理后并不影响最终结果。
5)、count distinct大量相同特殊值:
count distinct 时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。
. 请谈一下hive的特点是什么？hive和RDBMS有什么异同？
hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。
请说明hive中 Sort By，Order By，Cluster By，Distrbute By各代表什么意思。
order by：会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）。只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
sort by：不是全局排序，其在数据进入reducer前完成排序。
distribute by：按照指定的字段对数据进行划分输出到不同的reduce中。
cluster by：除了具有 distribute by 的功能外还兼具 sort by 的功能。
简要描述数据库中的 null，说出null在hive底层如何存储，并解释select a.* from t1 a left outer join t2 b on a.id=b.id where b.id is null; 语句的含义
null与任何值运算的结果都是null, 可以使用is null、is not null函数指定在其值为null情况下的取值。
null在hive底层默认是用'\N'来存储的，可以通过alter table test SET SERDEPROPERTIES('serialization.null.format' = 'a');来修改。
查询出t1表中与t2表中id相等的所有信息。
写出hive中split、coalesce及collect_list函数的用法（可举例）。
Split将字符串转化为数组。
split('a,b,c,d' , ',') ==> ["a","b","c","d"]
COALESCE(T v1, T v2, …) 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。
collect_list列出该字段所有的值，不去重 select collect_list(id) from table;
写出将 text.txt 文件放入 hive 中 test 表‘2016-10-10’ 分区的语句，test 的分区字段是 l_date。
LOAD DATA LOCAL INPATH '/your/path/test.txt' OVERWRITE INTO TABLE test PARTITION (l_date='2016-10-10')
Hive的两个重要参数是什么？
1.hive -e ‘’ 从命令行执行指定的HQL
2.hive -f *.hql 执行hive脚本命令
Q15：Hive中如何复制一张表的表结构（不带有被复制表数据）
create table a like b;
Q16：Hive中追加导入数据的4种方式是什么？请写出简要语法。
1.从本地导入： load data local inpath '/home/1.txt' (overwrite)into table student;
2.从Hdfs导入： load data inpath '/user/hive/warehouse/1.txt' (overwrite)into table student;
3.查询导入： create table student1 as select * from student;(也可以具体查询某项数据)
4.查询结果导入：insert （overwrite）into table staff select * from track_log;
Hive导出数据有几种方式？如何导出数据
1、用insert overwrite导出方式
导出到本地：
insert overwrite local directory ‘/home/robot/1/2’ rom format delimited fields terminated by ‘\t’ select * from staff;(递归创建目录)
2、导出到HDFS
insert overwrite directory ‘/user/hive/1/2’ rom format delimited fields terminated by ‘\t’ select * from staff;
3、Bash shell覆盖追加导出
例如：$ bin/hive -e “select * from staff;” > /home/z/backup.log
4、Sqoop把hive数据导出到外部
hive 内部表和外部表区别
创建表时：创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径， 不对数据的位置做任何改变。
删除表时：在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。
分区和分桶的区别
分区
是指按照数据表的某列或某些列分为多个区，区从形式上可以理解为文件夹，比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。
那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找
分桶
分桶是相对分区进行更细粒度的划分。
分桶将整个数据内容按照某列属性值得hash值进行区分，如要按照name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。
如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件
hive 优化
1.fetch task任务不走MapReduce，可以在hive配置文件中设置最大化和最小化fetch task任务；通常在使用hiveserver2时调整为more；
2.strict mode：严格模式设置，严格模式下将会限制一些查询操作
文件格式，ORC PARQUET 等
分区表
select 查询不加where过滤条件，不会执行
开启严格模式
hive提供的严格模式，禁止3种情况下的查询模式。
a：当表为分区表时，where字句后没有分区字段和限制时，不允许执行。
b：当使用order by语句时，必须使用limit字段，因为order by 只会产生一个reduce任务。
c：限制笛卡尔积的查询。sql语句不加where不会执行
- 优化sql语句，如先过滤再join，先分组再做distinct;
4.MapReduce过程的map、shuffle、reduce端的snappy压缩
需要先替换hadoop的native本地包开启压缩
在mapred-site.xml文件设置启用压缩及压缩编码
在执行SQL执行时设置启用压缩和指定压缩编码
set mapreduce.output.fileoutputformat.compress.codec=org apache.hadoop.io.compress.SnappyCodec;
5.大表拆分成子表，提取中间结果集，减少每次加载数据
多维度分析，多个分析模块
每个分析模块涉及字段不一样，而且并不是表的全部字段
6.分区表及外部表
设计二级分区表（一级字段为天，二级字段设置小时）
创建的的是外部表，创建表时直接指定数据所在目录即可，不用再用load加载数据
7.设置map和reduce个数：默认情况下一个块对应一个map任务，map数据我们一般不去调整，reduce个数根据reduce处理的数据量大小进行适当调整体现“分而治之”的思想
set mapred.reduce.tasks=3;
8.JVM重用：一个job可能有多个map reduce任务，每个任务会开启一个JVM虚拟机，默认情况下一个任务对应一个JVM，任务运行完JVM即销毁，我们可以设置JVM重用参数，一般不超过5个，这样一个JVM内可以连续运行多个任务
JVM重用是Hadoop调优参数的内容，对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。hadoop默认配置是使用派生JVM来执行map和reduce任务的，这是jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。
JVM重用可以使得JVM实例在同一个JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中进行设置（建议参考5~10）
mapred.job.reuse.jvm.num.tasks(旧版)
mapreduce.job.jvm.numtasks(新版)
hadoop.apache.org/docs/r2.5.2/hadoop-mapreduce-client/hadoop-mapreduce-client-core/mapred-default.xml
http://hadoop.apache.org/docs/r2.5.2/hadoop-mapreduce-client/hadoop-mapreduce-client-core/mapred-default.xml
也可在hive的执行设置：
***set mapred.job.reuse.jvm.num.tasks=10;
hive (default)> set mapred.job.reuse.jvm.num.tasks;
mapred.job.reuse.jvm.num.tasks=1
- 推测执行：例如一个Job应用有10个MapReduce任务（map 及reduce），其中9个任务已经完成，那么application Master会在另外启动一个相同的任务来运行未完成的那个，最后哪个先运行完成就把另一个kill掉
启用speculative最大的好处是，一个map执行的时候，系统会在其他空闲的服务器上启动相同的map来同时运行，哪个运行的快就使用哪个的结果，另一个运行慢的在有了结果之后就会被kill。
hive-site.xml
hive.mapred.reduce.tasks.speculative.execution=true;
<property>
<name>hive.mapred.reduce.tasks.speculative.execution</name>
<value>true</value>
<description>Whether speculative execution for reducers should be turned on. </description>
</property>
网友评论