Hive（一）
一.hive基础
1.hive产生背景
（1）直接使用mapreduce所面临的问题
1）、人员学习成本太高
2）、项目周期要求太短
3）、MapReduce实现复杂查询逻辑开发难度太大
（2）为什么要使用 Hive：
1）、更友好的接口：操作接口采用类 SQL 的语法，提供快速开发的能力
2）、更低的学习成本：避免了写 MapReduce，减少开发人员的学习成本
3）、更好的扩展性：可自由扩展集群规模而无需重启服务，还支持用户自定义函数
2.Hive含义
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，可以将SQL语句转换成MapReduce任务运行。使不熟悉MapReduce的用户很方便地利用HQL处理和计算HDFS上的结构化的数据，适用于离线的批量数据计算。
Hive依赖于HDFS存储数据，Hive 将HQL转换成mapreduce执行，所以说hive是基于hadoop的一个数据仓库工具，是指就是一款基于HDFS的MapReduce计算框架，对存储在HDFS中的数据进行分析和管理。
3.Hive特点
（1）.可扩展：Hive可以自由扩展集群的规模，一般情况下不需要重启服务
（2）.延展性：Hive支持用户自定义函数，用户可以根据自己的需求来实现自定义的函数
（3）.容错性：良好的容错性，即使节点出现问题SQL仍可完成执行。
优势：
- 提供了一个简单的优化模型
- HQL类SQL语法，简化MR开发
- 支持在不同的计算框架上运行
- 支持在HDFS和HBase上临时查询数据
- 支持用户自定义函数、格式
- 成熟的JDBC和ODBC驱动程序，用于ETL和BI
- 稳定可靠（真实生产环境）的批处理
- 有庞大活跃的社区
二.Hive架构
hive的体系结构可以分成以下几个部分
1.用户接口
CLI：即Shell命令行（Command Line Interface）,采用交互形式使用Hive命令行与Hive进行交互
JDBC/ODBC:是Hive的基于JDBC操作提供的客户端，用户通过这连接至Hive sever服务
Web UI：通过浏览器访问Hive
2.跨语言服务
Thrift是Facebook开发的一个软件框架，可以用来进行可扩展且跨语言的服务的开发，Hive集成了该服务，
能让不同的编程语言调用Hive的接口。
3.底层的Driver
驱动器Driver，编译器Compiler，优化器Optimizer，执行器Executor，Driver组件完成HQL查询语句从语法分析，语法分析，编译，优化，以及生产逻辑执行计划的生成。生成的逻辑之和星计划存储在HDFS中，并随后又MapReduce调用执行。
Hive的核心是驱动引擎，驱动引擎又四个部分组成：
（1）解释器：将HiveSQL语句转换为抽象语法树（AST）
（2）编译器：是将语法树编译成逻辑执行计划
（3）优化器：优化器是对逻辑执行计划进行优化
（4）执行器：执行器是调用底层的运行框架执行逻辑执行计划
4.元数据存储系统
元数据：存储在Hive中的数据的描述信息，HCatalog：将Hive元数据共享给其他应用程序
Hive 中的元数据通常包括：表的名字，表的列和分区及其属性，表的属性（内部表和 外部表），表的数据所在目录
Metastore 默认存在自带的 Derby 数据库中。缺点就是不适合多用户操作，并且数据存 储目录不固定。数据库跟着 Hive 走，极度不方便管理。
实际生产一般存储在MySQL中，修改配置文件hive-site.xml
5.执行流程
HiveQL 通过命令行或者客户端提交，经过 Compiler 编译器，运用 MetaStore 中的元数 据进行类型检测和语法分析，生成一个逻辑方案(Logical Plan)，然后通过的优化处理，产生 一个 MapReduce 任务。
三.Hive数据存储模型
Hive中所有的数据都在存储HDFS中，没有专门的数据存储格式；只需要在创建表的适合告诉hive数据中的列分隔符和行分隔符，hive就可以解析数据
hive中的数据模型：
（1）db：在HDFS中表现为：${hive.metastore.warehouse.dir}目录下的一个文件夹。
（2）table：在HDFS表现为所属db目录下的一个文件夹
（3）external table：与table类似，但其数据存放位置可以在任意位置指定路径
（4）partition：在HDFS中表现为table目录下的子目录
（5）bucket：在HDDFS中表现为同一个表目录下根据hash散列之后的多个文件。
四.数据类型
1.基本数据类型
| 类型 |
描述 |
示例 |
| boolean |
true/false |
TRUE |
| tinyint |
1字节的有符号整数 |
-128~127 1Y |
| smallint |
2个字节的有符号整数，-32768~32767 |
1S |
| int |
4个字节的带符号整数 |
1 |
| bigint |
8字节带符号整数 |
1L |
| float |
4字节单精度浮点数 |
1.0 |
| double |
8字节双精度浮点数 |
1.0 |
| deicimal |
任意精度的带符号小数 |
1.0 |
| String |
字符串，变长 |
“a”,’b’ |
| varchar |
变长字符串 |
“a”,’b’ |
| char |
固定长度字符串 |
“a”,’b’ |
| binary |
字节数组 |
无法表示 |
| timestamp |
时间戳，纳秒精度 |
122327493795 |
| date |
日期 |
‘2018-04-07’ |
2.复杂数据类型
| 类型 |
描述 |
示例 |
| array |
有序的的同类型的集合 |
array(1,2) |
| map |
key-value,key必须为原始类型，value可以任意类型 |
map(‘a’,1,’b’,2) |
| struct |
字段集合,类型可以不同 |
struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) |
示例建表
（1）有表数据文件employee.txt如下
Michael|Montreal,Toronto|Male,30|DB:80|Product:DeveloperLead
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female,57|Sales:89|Sales:Lead
（2）建表employee
create table employee(
name string,
address array<string>,
personaInfo array<string>,
technol map<string,int>,
jobs map<string,string>)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';
（3）上传表
hdfs dfs -put /root/employee.txt /opt/hive/warehouse/employee/
（4）访问数据
hive> select * from employee;
OK
Michael ["Montreal","Toronto"] ["Male","30"] {"DB":80} {"Product":"Developer\u0004Lead"}
Will ["Montreal"] ["Male","35"] {"Perl":85} {"Product":"Lead","Test":"Lead"}
Shelley ["New York"] ["Female","27"] {"Python":80} {"Test":"Lead","COE":"Architect"}
Lucy ["Vancouver"] ["Female","57"] {"Sales":89} {"Sales":"Lead"}
Time taken: 0.037 seconds, Fetched: 4 row(s)
访问列数据
hive> select name,address[0],personaInfo[0],technol["Sales"] from employee;
OK
Michael Montreal Male NULL
Will Montreal Male NULL
Shelley New York Female NULL
Lucy Vancouver Female 89
Michael 100 Montreal NULL
Will 101 Montreal NULL
Steven 102 New York NULL
Lucy 103 Vancouver NULL
Mike 104 Montreal NULL
Shelley 105 New York NULL
Luly 106 Vancouver NULL
Lily 107 Montreal NULL
Shell 108 New York NULL
Mich 109 Vancouver NULL
Dayong 110 Montreal NULL
Sara 111 New York NULL
Roman 112 Vancouver NULL
Christine 113 Montreal NULL
Eman 114 New York NULL
Alex 115 Vancouver NULL
Alan 116 Montreal NULL
Andy 117 New York NULL
Ryan 118 Vancouver NULL
Rome 119 Montreal NULL
Lym 120 New York NULL
Linm 121 Vancouver NULL
Dach 122 Montreal NULL
Ilon 123 New York NULL
Elaine 124 Vancouver NULL
Time taken: 0.292 seconds, Fetched: 29 row(s)
修改表结构
将personinfo改成info struct<gender:string,age:int>
hive> alter table employee change personainfo info struct<gender:string,age:int>
> ;
OK
Time taken: 0.171 seconds
hive> select * from employee;
OK
Michael ["Montreal","Toronto"] {"gender":"Male","age":30} {"DB":80} {"Product":"Developer\u0004Lead"}
Will ["Montreal"] {"gender":"Male","age":35} {"Perl":85} {"Product":"Lead","Test":"Lead"}
Shelley ["New York"] {"gender":"Female","age":27} {"Python":80} {"Test":"Lead","COE":"Architect"}
Lucy ["Vancouver"] {"gender":"Female","age":57} {"Sales":89} {"Sales":"Lead"}
Time taken: 0.06 seconds, Fetched: 4 row(s)
insert into employee_partition
values('qingniao'|'nanjing'|'gender':'male'|'age':21|'hadoop':15|'hive':'important')
partition(country='china',add='nanjing')
五.Hive操作
1.Hive DDL
（1）创建库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
注：默认创建地址是你在/opt/hive/conf/hive-site.xml文件中进行配置的
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/opt/hive/warehouse</value>
</property>
1）创建普通的数据库
hive> create database t1;
OK
Time taken: 0.037 seconds
hive> show databases;
OK
default
t1
test
Time taken: 0.018 seconds, Fetched: 3 row(s)
2）创建库的时候检查存与否
hive> create database if not exists t1;
OK
Time taken: 0.007 seconds
hive> show databases;
OK
default
t1
test
Time taken: 0.018 seconds, Fetched: 3 row(s)
3）创建库的时候带注释
hive> create database if not exists t2 comment 'learning hive';
OK
Time taken: 0.028 seconds
hive> desc database t2;
OK
t2 learning hive hdfs:
Time taken: 0.021 seconds, Fetched: 1 row(s)
4）创建带属性的库
create database if not exists t3 with dbproperties('creator'='hadoop','date'='2018-04-05');
（2）查看库
1）查看数据库
hive> show databases;
OK
default
t1
t2
test
Time taken: 0.015 seconds, Fetched: 4 row(s)
2）显示数据库的详细属性信息
hive> desc database extended t2;
OK
t2 learning hive hdfs:
Time taken: 0.023 seconds, Fetched: 1 row(s)
3）查看正在使用的哪一个库
hive> select current_database();
OK
default
Time taken: 0.016 seconds, Fetched: 1 row(s)
（3）删除库
删除库操作drop database [if exists] dbname
默认情况下，hive不允许删除包含表的数据库，一般手动删除库下所有表再删除库；或者使用cascade关键字**drop database if exists dbname cascade;**
1)删除不含表的数据库
hive> show databases;
OK
default
t1
t2
test
Time taken: 0.013 seconds, Fetched: 4 row(s)
hive> show tables in t1;
OK
Time taken: 0.02 seconds
hive> drop database t1;
OK
Time taken: 0.053 seconds
hive> show databases;
OK
default
t2
test
Time taken: 0.009 seconds, Fetched: 3 row(s)
2）删除含有表的数据库
hive> show tables in t2;
OK
pp
Time taken: 0.014 seconds, Fetched: 1 row(s)
hive> drop database t2 cascade;
OK
Time taken: 0.078 seconds
hive> show tables in t2;
FAILED: SemanticException [Error 10072]: Database does not exist: t2
（4）切换库use database_name
hive> use t2;
OK
Time taken: 0.009 seconds
(5)修改库
用户可以使用alter database 命令为某个数据库的dbproperties设置键=值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息是不可更改的，包括数据库名和数据库所在的目录位置。
hive> alter database t1 set dbproperties('createtime'='20200917');
OK
Time taken: 0.026 seconds
hive> desc database extended t1;
OK
t1 hdfs:
（6）创建表
create [tempporary][external] table [if not exists][db_name.]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]
[skewed by(col_name,col_name,...)] on((col_value,col_value,...),(col_value,col_value,...),...)
[stored as directories][
[row format row_format]
[stored as file_format]
[stored by 'storage.handler.class.name'][with serdeproperties(...)]]
[location hdfs_path]
[telproperties(property_name=property_value,...)]
[as select_statement];
创建表语句说明：
1)create table创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常；
2）external可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）；
注：Hive在创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3）like允许用户复制现有的表结构，但是不复制数据。
4）comment注释
5）partitioned by指定分区
6）row format：
row format delimited
[fields terminated by '']
[collection items terminated by '']
[map keys terminated by '']
[lines terminated by '']
|serde serde_name
[with serdeproperties(property_name=property_value,property_name=property_value,...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
7）stored as：
sequencefile --序列化文件
textfile–普通的文本文件格式
rcfile–行列存储相结合的文件
inputformat input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
8）**location：**指定外部表在HDFS的存储路径
示例
1）创建默认内部表
hive> create table student(id int,name string,sex string,age int,department string)
> row format delimited
> fields terminated by ',';
OK
Time taken: 0.062 seconds
hive> desc student;
OK
id int
name string
sex string
age int
department string
Time taken: 0.073 seconds, Fetched: 5 row(s)
2）外部表
hive> create external table ext_student(id int,name string,sex string,age int,department string)
> row format delimited
> fields terminated by ','
> location '/hive/student';
OK
Time taken: 0.041 seconds
3）分区表
hive> create external table par_student(id int,name string,sex string,age int,department string)
> partitioned by (city string)
> row format delimited
> fields terminated by ','
> location '/hive/student_ptn';
OK
Time taken: 0.05 seconds
hive> alter table par_student add partition(city="beijing");
OK
Time taken: 0.095 seconds
hive> alter table par_student add partition(city="nanjing");
OK
Time taken: 0.084 seconds
4）分桶表
hive> create external table bck_student(id int,name string,sex string,age int,department string)
> clustered by (id) sorted by (id asc,name desc) into 4 buckets
> row format delimited fields terminated by ","
> location "/hive/student_bck";
OK
Time taken: 0.034 seconds
5）使用CATS创建表
hive> load data local inpath "/home/hadoop/student.txt" into table student;
hive>create table student_ctas as select * from student where id < 95012;
6)复制表结构
create table student_copy like student;
注意：
如果在table的前面没有加external关键字，那么复制出来的新表。无论如何都是内部表
如果在table的前面有加external关键字，那么复制出来的新表。无论如何都是外部表
(7)查看表
1）查看表列表
show tables;
show tables in mylive;
show tables like 'student_c*';
2)查看表的详细信息
desc student;
desc extended student;
desc formatted student;
show partitions student_ptn;
3)查看表的详细建表语句
show create table student_ptn;
（8）修改表
（1）修改表名
alter table student rename to new_student;
(2)修改字段定义
alter table new_student add columns(score int);
alter table new_student change name new_name string;
alter table new_student replace columns(in int,name string, address string);
(3)修改分区信息
alter table student_ptn add partition(city="chongqing") partition(city="chongqing2") partition(city="chongqing3");
load data local inpath "/home/hadoop/student.txt" into table student_ptn partition(city="beijing");
create table student_ptn_age(id int,name string,sex string,department string) partitioned by (age int);
insert overwrite table student_ptn_age partition(age)select id,name,sex,department，age from student_ptn;
alter table student_ptn add if not exists partition(city='beijing') location '/student_ptn_beijing' partition(city='cc') location '/student_cc';
alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';
alter table student_ptn drop partition (city='beijing');
（9）删除表
drop table new_student;
(10)清空表
truncate table student_ptn;
2.Hive DML
(1)load
语法结构
load data [local] inpath 'filepath' [overwrite] into
table tablename [partition(partcol1=val1,partcol2=val2...)]
语法说明：
1）filepath
相对路径，project/data1
绝对路径 /user/hive/project/data1
2)local关键字
指定local关键字，load命令会1去查找本地文件系统中的filepath。
没有指定local，则会根据inpath中的uri查找文件
3）overwriter关键字
表示覆盖表中已有数据，否则表示追加
4）partition
表示上传到指定分区
示例
load data local inpath '/home/hadoop/data/emp.txt' into table emp;
hdfs dfs -put emp.txt /data/hive
load data inpath '/data/hive/emp.txt' into table emp;
load data inpath 'data/hive/emp.txt' overwrite into table emp;
load data local inpath '/home/hadoop/data/order.txt'overwrite into table order_partition partition(event_month='2014-05');
(2)insert
语法结构
insert overwrite table tablename1 [partition(partcl1=val1,partctl2=val2...)]select_statement from from_statement
from from_statement
insert overwrite tablename1[partition(partcol1=val1,partcol2=val2...)]select_statement1
[insert overwrite tablename2[partition...]select_statement2 ]...
insert overwrite table tablename partition(partcol1[=val1],partcol2[=val2]...)select_statement from from_statement
示例
insert into table student partition(month='201709') values('1004','wangwu');
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';
create table emp2 as select empno,empname,job,deptno from emp;
insert into table order_artition partition(event_month='2014-07')select * from order_4_partition;
(3)数据导出
将hive的表中的数据导出到文件系统
语法结构；
insert overwrite [local] directory directory1 select ... from ...
示例
insert overwrite local directory '/opt/data/student'
row format delimited
fields terminated by '\t'
collection items terminated by '\n'
select * from student;
insert overwrite directory '/hive/' select * from emp;
(4)select
基本查询语法
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 co_list]]
[limit number]
说明：
1)order by 会对输入做全局排序，因此只有一个reducer，会导致输入规模较大时，需要较长的计算时间。
2）sort by 不是全局排序，其在数据进入reducer前完成排序，所以sort by 只保证每个reduce的输出有序，不保证全局有序。
3）distribute by 根据指定内容将数据分到同一个reduce
4）cluster by 除了具有对distribute by的功能外，还会对该字段进行排序。
select * from emp;
select empno, ename,from emp;
select * from emp where empno=100;
select * from emp where empno>=100;
select * from emp where empno between 90 and 100;
select * from emp limit 4;
select * from emp where empno in(100,200,300);
select ename from emp where empno is null;
select count(*) from emp where deptno=10;
select max(sal),min(sal),sum(sal),avg(sal) from emp;
select deptno,avg(sal) from emp group by deptno;
select deptno,job,max(sal)from emp group by deptno,job;
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
select ename,sal case
when sal>1 and sal<=1000 then'lower'
when sal>1000 and sal<=2000 then'middle'
when sal>2000 and sal<=4000 then'high'
else 'highest'end
from emp;
(5)join
多表关联查询
语法
join table:
table_reference join table_factor[join_condition]
|table_reference {left|right|full}[outer]join table_reference join_condition
|table_reference left semi join table_reference join_condition
注：hive不支持非等值连接
1）只支持等值join
select a.* from a join b on(a.id=b.id);
select a.* from a join b on(a.id>b,id);
2）可以join多于两个的表
select a.val,b.val,c.val from a join b on(a.key=b.key1)join c on(c.key=b.key2)
