简介:
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。
其本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据的存储，hive可以理解为一个将SQL转换为MapReduce的任务的工具。
特点:
可扩展
Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。
延展性
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
容错
良好的容错性，节点出现问题SQL仍可完成执行。
Hive与hadoop的关系
hive利用HDFS存储数据,利用MapRuduce 查询分析数据
Hive 与传统数据库对比
hive用于海量数据的离线数据分析
1.数据格式。Hive中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、”\t”、”\x001″）、行分隔符（”\n”）以及读取文件数据的方法（Hive 中默认有三个文件格式 TextFile，SequenceFile 以及 RCFile）。
2.Hive在加载数据的过程中，不需要从用户数据格式到 Hive 定义的数据格式的转换。
3.Hive 在加载的过程中不会对数据本身进行任何修改，甚至不会对数据进行扫描。而只是将数据内容复制或者移动到相应的 HDFS 目录中。
4.Hive 中不支持对数据的改写和添加，所有的数据都是在加载的时候中确定好的。
5.Hive 在加载数据的过程中不会对数据中的某些 Key 建立索引。Hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于数据的访问延迟较高，决定了Hive 不适合在线数据查询。
6.Hive 是建立在 Hadoop 之上的，因此 Hive 的可扩展性是和 Hadoop 的可扩展性是一致的。
Hive的数据存储
1、Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，ORC格式RCFILE等）
2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。
3、Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。
db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
table：在hdfs中表现所属db目录下一个文件夹
external table：与table类似，不过其数据存放位置可以在任意指定路径
partition：在hdfs中表现为table目录下的子目录
bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件
Hive的使用方式
第一种交互方式：Hive交互shell
cd /export/servers/hive-1.1.0-cdh5.14.0
bin/hive
第二种交互方式:Hive JDBC 服务
第一步:后台启动 hiveserver2 服务
cd /export/servers/hive-1.1.0-cdh5.14.0
nohup bin/hive –service hiveserver2 &
第二步
beeline连接hiveserver2
bin/beeline
beeline> !connect jdbc:hive2://node03.hadoop.com:10000
注:如果使用beeline方式连接hiveserver2 一定保证hive在mysql 当中的元数据库已经创建成功,否则会拒绝连接
第三种交互方式 :hive命令
使用-e参数来直接执行hql的语句
bin/hive -e “use myhive;select * from test;”
使用 –f 参数通过指定文本文件来执行hql的语句
vim hive.sql
use myhive;select * from test;
bin/hive -f hive.sql
Hive基本操作
hive的表存放位置 是由由hive-site.xml当中的一个属性指定的
hive.metastore.warehouse.dir
/user/hive/warehouse
创建数据库并指定hdfs存储位置
create database myhive2 location ‘/myhive2’;
修改数据库
可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置
修改数据库的创建日期
alter database myhive2 set dbproperties(‘createtime’=‘20180611’);
查看数据库详细信息
查看数据库基本信息
desc database myhive2;
查看数据库更多详细信息
desc database extended myhive2;
删除数据库
删除一个空数据库，如果数据库下面有数据表，那么就会报错
drop database myhive2;
创建数据库表语法
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]
[LOCATION hdfs_path]
创建表并指定字段之间的分隔符
create table if not exists stu2(id int, name string) row format delimited fields terminated by ‘\t’ stored as textfile location ‘/user/stu2’;
插入数据
insert into stu2 values(1,“zhangsan”)
insert into stu2 values(2,‘lisi’)
根据查询结果创建表
create table stu333 as select* from stu2
根据已经存在的表结构创建表
create table stu4 like stu2
查询表的类型
desc formatted stu2
外部表
外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉
管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
创建外部表
create external table student (t_id string,t_name string) row format delimited fields terminated by’\t’
从本地文件系统向表中加载数据
load data local inpath ‘/export/servers/hivedatas/student.csv’ into table student
加载数据 并覆盖已有数据
load data local inpath’/export/servers/hivedatas/student.csv’ overwrite into table student
从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统，其实就是一个移动文件的操作）
cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath ‘/hivedatas/techer.csv’ into table techer;
如果删掉tstudent表，hdfs的数据仍然存在，并且重新创建表之后，表中就直接存在数据了,因为我们的techer表使用的是外部表，drop table之后，表当中的数据依然保留在hdfs上面了
分区表
在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天，或者每小时进行切分成一个个的小的文件，这样去操作小的文件就会容易得多了
创建分区表 语法
create table score (s_id string,c_id string,s_score int) partitioned by(month string) row format delimited fields terminated by ‘\t’
加载数据到分区中
load data local inpath’/export/servers/hivedatas/score.csv’ into table score partition(month =‘201806’
‘)
创建一个表带多个分区
create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by ‘\t’;
加载数据到一个多分区的表中去
load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score2 partition(year=‘2018’,month=‘06’,day=‘01’);
多分区联合查询使用union all 来实现
select * from score where month =‘201806’ union all select* from score where month =‘201806’
查看分区
show partitions score
添加一个分区
alter table score add partition(month=‘201805’
)
同时添加多个分区
alter table score add partition(month=‘201804’) partition(month = ‘201803’);
注：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹
分桶表
将数据按照指定的字段进行划分成多个桶中去,说白了就是将数据按照字段进行划分,可以将数据 按照字段划分到多个文件中去
开启hive的桶表功能
set hive.enforce.bucketing= true
设置reduce 的个数
set mapreduce.job.reduces=3
创建桶表
create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by ‘\t’;
注: 桶表的数据加载 只能通过 insert overwrite hdfs dfs -put文件或者通过load data无法加载
通过insert overwrite给桶表中加载数据
insert overwrite table course select * from course_common cluster by(c_id);
修改表
alter table old_table_name rename to new _table_name
增加/修改 列信息
（1）查询表结构
desc score5;
（2）添加列
alter table score5 add columns (mycol string, mysco string);
（3）查询表结构
desc score5;
（4）更新列
alter table score5 change column mysco mysconew int;
（5）查询表结构
desc score5;
删除表
drop table score5;
Hive 表中加载数据
直接向分区表中插入数据
create table score3 like score;
insert into table score3 partition(month =‘201807’) values (‘001’,‘002’,‘100’);
通过查询插入数据
通过load方式加载数据
load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=‘201806’);
通过查询方式加载数据
create table score4 like score;
insert overwrite table score4 partition(month = ‘201806’) select s_id,c_id,s_score from score;
注: 关键字overwrite 必须要有
常用于实际生产环境当中，将一张表拆开成两部分或者多部分
给score表加载数据
load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=‘201806’);
创建第一部分表：
create table score_first( s_id string,c_id string) partitioned by (month string) row format delimited fields terminated by ‘\t’ ;
创建第二部分表：
create table score_second(c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by ‘\t’;
分别给第一部分与第二部分表加载数据
from score
insert overwrite table score_first partition(month=‘201806’) select s_id,c_id
insert overwrite table score_second partition(month = ‘201806’) select c_id,s_score;
hive表中数据导出
1）将查询的结果导出到本地
insert overwrite local directory ‘/export/servers/exporthive/a’ select * from score;
2）将查询的结果格式化导出到本地
insert overwrite local directory ‘/export/servers/exporthive’ row format delimited fields terminated by ‘\t’ collection items terminated by ‘#’ select * from student;
collection items terminated by ‘#’ 对集合类型使用#来进行分割
3）将查询的结果导出到HDFS上(没有local)
insert overwrite directory ‘/export/servers/exporthive’ row format delimited fields terminated by ‘\t’ collection items terminated by ‘#’ select * from score;
注: 对于集合类型我们使用#来进行分割，因为这个表里面没有集合类型，所以加不加这个结果都一样
清空表数据
只能清空管理表，也就是内部表
truncate table score 5
Hive的查询语法
语法结构
SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
注: order by 会对输入做全局排序,因此只有一个reduce时,会导致当输入规模较大时,需要较长的计算时间
sort by 不是全局排序 是输入做全局排序,其在数据进入reduce前完成排序2、因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。
因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by
分桶表的作用：最大的作用是用来提高join操作的效率；
常用函数
1）求总行数（count）
select count(1) from score;
2）求分数的最大值（max）
select max(s_score) from score;
3）求分数的最小值（min）
select min(s_score) from score;
4）求分数的总和（sum）
select sum(s_score) from score;
5）求分数的平均值（avg）
select avg(s_score) from score;
2.2.3、LIMIT语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
select * from score limit 3;
分组
GROUP BY语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
select s_id ,avg(s_score) from score group by s_id;
注:注意group by的字段，必须是select后面的字段，select后面的字段不能比group by的字段多
内连接（INNER JOIN）
内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select * from techer t inner join course c on t.t_id = c.t_id;
左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
查询老师对应的课程
select * from techer t left join course c on t.t_id = c.t_id;
右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select * from techer t right join course c on t.t_id = c.t_id;
满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;
Hive函数
1）查看系统自带的函数
hive> show functions;
2）显示自带的函数的用法
hive> desc function upper;
3）详细显示自带的函数的用法
hive> desc function extended upper;
自定义函数
当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore()
编程步骤：
（1）继承org.apache.hadoop.hive.ql.UDF
（2）需要实现evaluate函数；evaluate函数支持重载；
hive的数据压缩
在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy。