HIVE介绍

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质：
将HQL转化成MapReduce程序
Hive在Hadoop生态中的位置如下图所示：

hive和hadoop的关系：
1）Hive处理的数据存储在HDFS
2）Hive分析数据底层的实现是MapReduce
3）执行程序运行在Yarn上

Hive的基本操作（在操作之前需要先启动hdfs(存储源数据)和mysql数据库(存储表结构)）

一、错误

seman 表示是一个无法解决的错误
parse 表示是一个解析异常，修改完hql语句之后就能解决错误
剩下其他错误需要通过查看日志来确定是什么错 cat /tmp/root/hive.log

二、进入hive操作界面的两种方式

2.1、直接输 hive 就可以进入
2.2、beeline连接（连接hive的三种方式：cli，webGUI，beeline（JDBC/ODBC））界面比较美观
一、启动后台
hive --service hiveserver2
netstat -ntlp | grep 10000 查看hiveserver2（端口号是10000） 是否启动
二、进入客户端
2.1、 beeline -u jdbc:hive2://localhost:10000 -n root hive2之后才有这个beeline启动
!quit 退出客户端
2.2、beeline -u jdbc:hive2://localhost:10000 -n root 启动的时候会报两个错
一、错误

二、解决方法
首先，要在hadoop的core-site.xml中添加如下配置：

<property>
  <name>hadoop.proxyuser.root.hosts</name>
  <value>*</value>
</property>
<property>
  <name>hadoop.proxyuser.root.groups</name>
  <value>*</value>
</property>

修改之后需要在重新期待hadoop集群（start-all.sh）

三、往表中添加数据的方式

3.1 hive> insert into table stu values(1,“xss”);

3.2、创建一个表然后再用load将数据加载进表对应的hdfs目录

hive> create table stu(id int,name string)row format delimited fields terminated by ","; 

1、row format：对数据行格式的声明，其声明的目的是为了让hive知道以什么方式处理行数据，以便让hive能够以正确的方式来处理文件中的行，并与表中的行对应起来。
2、delimited：指定哪些是分隔符，这些分隔符用于将文本拆分成字段。
3、fields terminated by ‘\t’：告诉hive，每个字段之间的分隔符是‘\t’，这必须与文件中字段的分隔符一致。如果不一致，虽然可以成功地将数据加载到hdfs上，但是通过select查询得到的字段值是不正确的，如为null。也就是说，hive在查询时会使用’\t’切分每一行，来获得字段的值。

加载/root/data/student.txt 文件到student数据库表中。

hive> load data local inpath ‘/root/data/student.txt‘ overwrite into table student;

1、load data：加载外部数据到HDFS上。
2、local：指加载的是本地文件系统中的数据，即所在Linux中的数据文件。如果没有这个参数，则是加载hdfs上的文件。
3、inpath：文件所在的路径。如果没有使用local，则该参数的值应为hdfs的文件路径，即hdfs://192.168.100.40:9000/…
4、overwrite：意思是加载数据并覆盖原有文件中的内容。如果不使用该关键字，则在原来的文件中追加新的数据。
5、into table：指定要关联的hive。如果表关联错了，一般情况下不会报告异常，但后期的查询等操作的结果是非预期的。

3.3、根据查询结果创建表（查询的结果会添加到新创建的表中，这种方式不能创建外部表) create as select

create table if not exists student3
as select id, name from student;

3.4、创建表时通过Location指定加载数据路径，然后把txt文档传到设定的路径下

1）创建表，并指定在hdfs上的位置
hive (default)> create table if not exists student5(
              id int, name string
              )
              row format delimited fields terminated by ‘\t’
              location ‘/user/hive/warehouse/student5’;
2）上传数据到hdfs上
hive (default)> dfs -put /root/data/student.txt  /user/hive/warehouse/student5;
3）查询数据
hive (default)> select * from student5;

3.5、根据 student 创建相同结构的表 student4

create table if not exists student4 like student;

3.6、通过查询结果添加进表 insert select

insert into table stutwo select id,name from stu;

四、在hive中操作haoop

dfs -mkdir /s; 在hadoop上创建一个目录
dfs -rm -r /s; 在hadoop上删除一个目录
dfs -put /root/data/s.txt /s; 将s.txt 上传到hadoop的s目录下
dfs -ls /; 查看haoop上的目录
dfs -chmod 777 /user; 将haoop上的权限修改为777

五、在hive中查看linux上的文件 在linux命令之前加感叹号

!cat /root/s.txt

六、在不进入hive的情况下在linux界面操作hive

(1)“-e”不进入hive的交互窗口执行sql语句
[root@master hive]$ bin/hive -e “select id from student;”
(2)“-f”执行脚本中sql语句
（1）在/root/data目录下创建hivef.sql文件
[root@master ~]$ touch hivef.sql
文件中写入正确的sql语句
select *from student;
（2）执行文件中的sql语句
[root@master hive]$ bin/hive -f /root/data/hivef.sql
（3）执行文件中的sql语句并将结果写入文件中 >是覆盖如果没有 hive_result.txt 则自己创建 >>是追加
[root@master hive]$ bin/hive -f /root/data/hivef.sql > /root/data/hive_result.txt

七、hive的数据类型

7.1、基本数据类型

7.2、集合数据类型

集合数据类型演示例子：

txt文档
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing
建表语句

create table json(
name string,
friends array<String>,
children map<string,int>,
address struct<street:string,city:string>
)row format delimited fields terminated by ","
collection items terminated by "_"
map keys terminated by ":"
lines terminated by "\n";

字段解释:
row format
delimited
fields terminated by ‘,’ — 列分隔符
collection items terminated by ‘_’ –MAP STRUCT 和 ARRAY 的元素分隔符(数据分割符号)
map keys terminated by ‘:’ — MAP中的key与value的分隔符
lines terminated by ‘\n’; — 行分隔符
取出songsong的朋友、孩子、城市

hive (default)> select friends[1],children[‘xiao song’],address.city from test where name=”songsong”;

八、数据库操作

创建一个数据库，指定数据库在HDFS上存放的位置 location

hive (default)> create database db_hive2 location ‘/db_hive2.db’;

删除数据库

drop database xss;

** 数据库不为空的时候强制删除数据庫 cascade **

drop database xss cascade    

查看數據庫信息

desc database lesson;
desc database extended db_hive;        詳細信息

九、表操作

创建表（可以吧JSON数据导进表的）

数据
{“student”:{“name”:“范阳”,“age”:17,“sex”:“M”},“sub_score”:[{“subject”:“语文”,“score”:87},{“subject”:“数学”,“score”:80},{“subject”:“英语”,“score”:80}]}
表结构

create table if not exists stu_josn_scores (
student struct<name:string, age:int, sex:string>,
sub_score array<struct<subject:string, score:int>>
)
row format serde "org.apache.hive.hcatalog.data.JsonSerDe";

hive的lib包下面可以找到那个解析json的类

显示表格信息

内部表 MANAGED_TABLE

缺点：删除hive上的表则hdfs上的源数据也会被删除掉，不能共享hdfs上的源数据
例：

create table if not exists student2(
id int, name string
)
row format delimited fields terminated by ‘\t’
stored as textfile     文件存储格式
location ‘/user/hive/warehouse/student2’;   内部表，删除表之后，数据也会随之删除

外部表 EXTERNAL_TABLE

相比内部表，它有关键词 （external），并且可以共享hdfs上的源数据，删除hive上的表则hdfs上的源数据也不会被删除掉，不影响
例：

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by ‘\t’;

管理表和外部表的使用场景：
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表

分区表 当数据量比较大的时候可以缩小查找范围，从而提高查询效率

创建一级分区表

创建分区表的语法格式 只需要在小括号后面加 partitioned by( 条件 ) 就行

hive (default)> create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)       ****额外需要加的地方
row format delimited fields terminated by ‘\t’;

往分区表内加载数据 跟平常添加数据的区别是往分区表内添加数据需要在最后指定要添加进那个分区

load data local inpath ‘/root/data/dept.txt’ into table default.dept_partition    partition(month=’201709′);

查询分区表

hive (default)> select * from dept_partition where month=’201709′;

多分区联合查询

hive (default)> select * from dept_partition where month=’201709′
union
select * from dept_partition where month=’201708′
union
 select * from dept_partition where month=’201707′;

增加分区
添加单个分区
hive (default)> alter table dept_partition add partition(month=’201706′) ;
同时添加多个分区
hive (default)> alter table dept_partition add partition(month=’201705′), partition(month=’201704′);
删除分区
删除单个分区
hive (default)> alter table dept_partition drop partition (month=’201704′);
同时删除多个分区
hive (default)> alter table dept_partition drop partition (month=’201705′), partition (month=’201706′);
查看分区表有多少分区
hive> show partitions dept_partition;
查看分区表结构
hive> desc formatted dept_partition;

创建二级分区表

正常的加载数据

语法格式

create table par(id int,name string) partitioned by(month string,day string) row format delimited fields terminated by ",";

往二级分区表内加载数据

 load data local inpath "/root/data/stu.txt" into table par partition(month="20201",day="29");

查询二级分区表

select * from par where month="20201" and day="29";

把数据直接上传到分区目录上，让分区表和数据产生关联的两种方式。（前提是表定义的时候要有两级目录，既有month又有day）

（1）方式一：上传数据后修复

上传数据
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
hive (default)> dfs -put /root/data/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
查询数据（查询不到刚上传的数据）
hive (default)> select * from dept_partition2 where month=’201709′ and day=’12’;
执行修复命令
hive> msck repair table dept_partition2;   ***执行完这条命令之后，才能查询出数据
再次查询数据
hive (default)> select * from dept_partition2 where month=’201709′ and day=’12’;

（2）方式二：上传数据后添加分区

上传数据
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11;
hive (default)> dfs -put /root/data/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;
执行添加分区
hive (default)> alter table dept_partition2 add partition(month=’201709′, day=’11’);
查询数据
hive (default)> select * from dept_partition2 where month=’201709′ and day=’11’;

（3）方式三：上传数据后load数据到分区

创建目录
hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
hive (default)> dfs -put /root/data/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=10;
hive (default)> load data local inpath ‘/root/data/dept.txt’ into table dept_partition2 partition(month=’201709′,day=’10’);
包含两个方面的内容：一是创建元数据；二是将数据文件上传到目录。如果分区表目录存在，则直接保存；如果不存在，创建分区表目录，然后上传数据。
查询数据
hive (default)> select * from dept_partition2 where month=’201709′ and day=’10’;

十、数据导入

一、向表中装载数据（Load）

语法

hive> load data [local] inpath ‘/root/data/student.txt’ [overwrite] into table table_name [partition (partcol1=val1,…)];

（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区

二、通过查询语句向表中插入数据（Insert）

1）创建一张分区表

hive (default)> create table student(id int, name string) 
partitioned by (month string) 
row format 
delimited 
fields terminated by ‘\t’;

2）基本插入数据

hive (default)> insert into table  student partition(month=’201709′) values(1,’wangwu’);

3）基本模式插入（根据单张表查询结果）

hive (default)> insert overwrite table student partition(month=’201708′)
             select id, name from student where month=’201709′;

4）多插入模式（根据多张表查询结果）

hive (default)> from student
              insert overwrite table student partition(month=’201707′)
              select id, name where month=’201709′
              insert overwrite table student partition(month=’201706′)
              select id, name where month=’201709′;

三、查询语句中创建表并加载数据（As Select）

根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student3
as select id, name from student;

四、创建表时通过Location指定加载数据路径

1）创建表，并指定在hdfs上的位置

hive (default)> create table if not exists student5(
              id int, name string
              )
              row format delimited fields terminated by ‘\t’
              location ‘/user/hive/warehouse/student5’;

2）上传数据到hdfs上

hive (default)> dfs -put /root/data/student.txt  /user/hive/warehouse/student5;

3）查询数据

hive (default)> select * from student5;

五、Import数据到指定Hive表中

注意：先用export导出后，再将数据导入。

hive (default)> import table student2 partition(month=’201709′) from ‘/user/hive/warehouse/export/student’;

十一、导出数据

一、Insert导出

1）将查询的结果导出到本地
hive (default)> insert overwrite local directory ‘/root/data/export/student’ select * from student;
如果没有指定分隔符，则使用Hive默认的分隔符：
分隔符 描述

2）将查询的结果格式化导出到本地

hive (default)> insert overwrite local directory ‘/root/data/export/student1’
             ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ select * from student;

3）将查询的结果导出到HDFS上(没有local)

hive (default)> insert overwrite directory ‘/user/root/student2’
             ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’
             select * from student;

二、hdfs命令导出到本地

hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0 /root/data/export/student3.txt;

三、Hive Shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file，即使用重定向）
[root@master hive]$ bin/hive -e ‘select * from default.student;’ > /root/data/export/student4.txt;

四、Export导出到HDFS上

hive (default)> export table default.student to ‘/user/hive/warehouse/export/student’;

十二、分桶表

测试数据

1001	ss1
1002	ss2
1003	ss3
1004	ss4
1005	ss5
1006	ss6
1007	ss7
1008	ss8
1009	ss9
1010	ss10
1011	ss11
1012	ss12
1013	ss13
1014	ss14
1015	ss15
1016	ss16

创建分桶表

创建分桶表定义格式

create table buck(id int, name string)
clustered by(id) -- 括号不能省略
into 4 buckets     --4是随意的，自己需要几个同就设置成几
row format delimited fields terminated by ‘\t’;

查看分桶表结构

hive (default)> desc formatted stu_buck;
Num Buckets:            4     

往分桶表加载数据 大致分为三步

一、先创建一个字段跟分桶表一样的表
create table stu(id int, name string)
row format delimited fields terminated by ‘\t’;
二、向普通的stu表中导入数据
load data local inpath ‘/root/data/student.txt’ into table stu;
三、导入数据到分桶表，通过子查询的方式
insert into table buck
select id, name from stu;

到hdfs上可以看到在stu_buck目录下有四个数据文件。

查询分桶表信息

id%分桶数=桶号 然后每个id根据%桶数的余数，然后放到对应的桶中（这里定义的是4个桶 into 4 buckets ，所以只有0,1,2,3 这四个桶）

select * from buck

分桶表抽样查询

hive (default)> select * from stu_buck tablesample(bucket x out of y on id);

注意事项
y必须是table总bucket数的倍数或者因子
x表示从哪个bucket开始抽取

总共抽一个桶或者小于1个桶的时候，简言之，公式就是从x号桶中取出（桶数/y）个桶中的数据
select * from buck tablesample(bucket 2 out of 4 on id);
注意：x的值必须小于等于y的值，否则

要是抽一个桶以上或者两个桶的时候那么计算公式就不是（x,桶数/y）了，而是抽取第 x 个和第 x+y 个bucket的数据。
select * from buck tablesample(bucket 2 out of 2 on id); 就意味着是抽取除 第2个桶和第 2+2 个桶

十三、hive函数

Order By 和 Sort By 的区别

ORDER BY 对reducer处理过的结果集执行一个全局排序
它和其他的SQL方言中的定义是一样的。其会对reducer处理过的结果集执行一个全局排序。这也就是说会有一个所有的数据都通过一个reducer进行处理的过程。对于大数据集，这个过程可能会消耗太过漫长的时间来执行。
1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2）ORDER BY 子句在SELECT语句的结尾。
**Sort By（局部有序，全局无序），每个MapReduce内部排序， 在每个reducer中对数据进行排序，可以提高后面进行的全局排序的效率 **
由于ORDER BY进行全局排序，性能会受到影响。因此，Hive增加了一个可供选择的方式，也就是SORT BY,它只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程。这可以保证每个reducer的输出数据都是有序的（但并非全局有序）。这样可以提高后面进行的全局排序的效率。正是因为如此，SORT BY排序应该作用于多个reduce上，所以通常会在执行前将reduce的数目设置为大于1的数。
1）设置reduce个数
hive (default)> set mapreduce.job.reduces=3; set只对当前hive环境有影响，一旦hive退出了，下次再进入的时候就需要重新set 要想永远起作用就需要在hadoop的配置文件hive-site.xml中配置reducer数目
2）查看设置reduce个数
hive (default)> set mapreduce.job.reduces;

分区排序（Distribute By） 多个reducer，分区排序要和局部排序结合使用

Distribute By：类似MR中partition，进行分区，结合sort by使用。
注意：
1、Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
2、对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

【案例实操】
先按照部门编号分区，再按照员工编号降序排序。

hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory ‘/root/data/distribute-result’ select * from emp distribute by deptno sort by empno desc;

Cluster By 当distribute by和sort by字段相同时，可以使用cluster by方式。（但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。）

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
1）以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

聚合函数

collect_set(name) array_contains(collect_set(course),“a”) if(array_contains(collect_set(course),“d”),1,0),

collect_set: 用set将分组之后的name用 , 逗号拼接在一块，返回一个数组
array_contains:判断用set组成的数组中是否包含a，包含的话就返回true否则返回false
if（条件,1,2）：如果条件满足就返回1否则返回0
例题：
需要文件

1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e

题目要求

**解题代码

select id,
if(array_contains(collect_set(course),"a"),1,0),
if(array_contains(collect_set(course),"b"),1,0),
if(array_contains(collect_set(course),"c"),1,0),
if(array_contains(collect_set(course),"d"),1,0),
if(array_contains(collect_set(course),"e"),1,0),
if(array_contains(collect_set(course),"f"),1,0) 
from xk group by id;

行转列 CONCAT(字符串A,",",字符串B) CONCAT_WS("|",collect_set( ))

例题

需要文件

孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A

创建表及加载数据

 create table xkk(name string,con string,type string)row format delimited fields terminated by " " location "/user/hive/warehouse/xkk";
 
 dfs -put /root/data/xk.txt /user/hive/warehouse/xkk；

解题SQL语句

 select a.ct,concat_ws("|",collect_set(a.name))
 from (
 select name,concat(con,",",type) as ct from xkk 
 ) as a 
 group by a.ct;

相关函数说明
CONCAT(string A/col, string B/col…)：将输入的两个字符串用逗号拼接起来，各个参数之间用逗号隔开
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数是其后面参数间的分隔符。分隔符可以是与后面参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接到字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
注意：使用CONCAT_WS后必须使用GROUP BY 子句，否则会报告如一错误：
FAILED: SemanticException [Error 10025]: Line 3:7 Expression not in GROUP BY key ‘ct’

列转行 lateral view explode(cate)

需要文件

《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

创建表及加载数据

create table movie(movie string,cate array<string>)row format delimited fields terminated by "\t" \
collection items terminated by "," \
location "/user/hive/warehouse/movie";

dfs -put /root/data/xk.txt /user/hive/warehouse/movie;

解题SQL语句

select movie,tt from movie lateral view explode(cate) t as tt;

** 相关函数说明**
LATERAL VIEW:
用法:LATERAL VIEW udtf(expression) 表别名 AS 列别名
解释：用于和split,explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
EXPLODE(col):将hive某个列中复杂的array或者map结构拆分成多行。

开窗函数 over（） 一般旁边都跟一个聚合函数，两个共同使用

写得好吗？如果觉得还不错点个赞吧^ _ ^