大数据学习之Hive

什么是Hive

Hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。

Hive的优点

1.操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
2.避免了去写MapReduce，减少开发人员的学习成本
3.Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合
4.Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高
5.Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数

Hive缺点

1.Hive的HQL表达能力有限：
迭代式算法无法表达
数据挖掘方面不擅长

2.Hive的效率比较低：
Hive自动生成的MapReduce作业，通常情况下不够智能化
Hive调优比较困难，粒度较粗

Hive组成架构

Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。

下面对其中的架构元件做简单的梳理：
1.用户接口：Client
CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）。

2.元数据：Metastore
元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。

默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore。

3.Hadoop
使用HDFS进行存储，使用MapReduce进行计算。

4.驱动器：Driver
解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误

编译器（Physical Plan）：将AST编译生成逻辑执行计划

优化器（Query Optimizer）：对逻辑执行计划进行优化

执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark

Hive和数据库比较

由于 Hive 采用了类似SQL的查询语言HQL，因此很容易将Hive理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。

1.索引
Hive没有对数据中的某些Key建立索引。Hive要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。

2.执行引擎
Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而数据库通常有自己的执行引擎。

3.执行延迟
Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce框架。由于MapReduce 本身具有较高的延迟，因此在利用MapReduce 执行Hive查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。

4.可扩展性
由于Hive是建立在Hadoop之上的，因此Hive的可扩展性是和Hadoop的可扩展性是一致的。而数据库由于ACID语义的严格限制，扩展性非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。

5.数据更新
Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的。

6.数据规模
由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

7.数据存储位置
Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。

Hive搭建

1.安装mysql

yum install -y mysql-server

启动服务

service mysqld start
chkconfig mysqld on

进入mysql

mysql

修改mysql登录权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;

修改为登录权限是“root”，用户名是“%”，密码是“123”；
删除多余的权限;

use mysql;
select user,host,password from user;
//此时可以查看所有的登录权限，删除不需要的

刷新：

flush privileges;

再次登录mysql：

mysql -uroot -p

会提示输入密码，输入密码即可登录。

2.上传Hive和Mysql安装包到服务器并解压到指定路径

tar -zxvf hive-1.2.1 -C /usr/local/apps/

Hive安装包下载地址：Hive官网下载
Mysql安装包下载：下载地址

3.修改配置文件
进入到hive安装目录下的conf文件夹；
修改hive-default.xml.template文件名为 hive-site.xml；

mv hive-default.xml.template hive-site.xml

进入hive-site.xml：

vi hive-site.xml

将光标移动到如下位置：

然后输入：

.,$-1d

得到如下结果：

添加以下内容：

<property>  
  <name>hive.metastore.warehouse.dir</name>  
  <value>/user/hive_remote/warehouse</value>  
</property>  
   
<property>  
  <name>hive.metastore.local</name>  
  <value>false</value>  
</property>  
   
<property>  
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:mysql://hadoop-server/hive_remote?createDatabaseIfNotExist=true</value>  
</property>  
   
<property>  
  <name>javax.jdo.option.ConnectionDriverName</name>  
  <value>com.mysql.jdbc.Driver</value>  
</property>  
   
<property>  
  <name>javax.jdo.option.ConnectionUserName</name>  
  <value>root</value>  
</property>  
   
<property>  
  <name>javax.jdo.option.ConnectionPassword</name>  
  <value>123</value>  
</property>  

4.把mysql安装包拷贝到hive目录下的lib文件夹中：

cp /root/mysql-connector-java-5.1.32-bin.jar /usr/local/apps/hive-1.2.1/lib/

到此Hive就搭建完成。需要先启动hadoop集群，然后直接输入hive启动。

Hive的常用HiveQL操作

1.Hive数据类型
Hive支持基本数据类型和复杂类型, 基本数据类型主要有数值类型(INT、FLOAT、DOUBLE ) 、布尔型和字符串, 复杂类型有三种:ARRAY、MAP 和 STRUCT。

1）基本数据类型：
TINYINT: 1个字节
SMALLINT: 2个字节
INT: 4个字节
BIGINT: 8个字节
BOOLEAN: TRUE/FALSE
FLOAT: 4个字节，单精度浮点型
DOUBLE: 8个字节，双精度浮点型STRING 字符串

2）复杂数据类型：
ARRAY: 有序字段

    create table arr_tb(name string,score Array<double>)
        row format delimited fields terminated by '\t'  # 设置每行中的每一个属性间的分隔符
        collection items terminated by '_'              # 设置一个数组类型的数据项间的分隔符     
        lines terminated by '\n';                       # 设置多行间的分隔符

MAP: 无序字段

    create table hash_tb(name string ,score map<string,double>)
        row format delimited fields terminated by '\t' 
        collection items terminated by '_'
        map keys terminated by ':'          # map中一个数据项的kv的分隔符
        lines terminated by '\n';   

STRUCT: 一组命名的字段

    create table struct_tb(name string ,score struct<java:double,bigdata:double,mysql:double>)
        row format delimited fields terminated by '\t' 
        collection items terminated by '_'
        lines terminated by '\n';

2.常用的HiveQL操作命令
分类：
数据定义语言 ：Data definition language DDL ，create /drop /alter
数据操作语言 ：Data manipulation language DML ，update /delete /insert
数据查询语言 ：Data Query language DQL ， select

创建、修改和删除数据库

create database if not exists hive;       #创建数据库
show databases;                           #查看Hive中包含数据库
show databases like 'h.*';                #查看Hive中以h开头数据库
describe databases;                       #查看hive数据库位置等信息
alter database hive set dbproperties;     #为hive设置键值对属性
use hive;                                 #切换到hive数据库下
drop database if exists hive;             #删除不含表的数据库
drop database if exists hive cascade;     #删除数据库和它中的表

注意，除 dbproperties属性外，数据库的元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置，没有办法删除或重置数据库属性。

创建、修改和删除表

#创建内部表（管理表）
create table if not exists hive.usr(
      name string comment 'username',
      pwd string comment 'password',
      address struct<street:string,city:string,state:string,zip:int>,
      comment  'home address',
      identify map<int,tinyint> comment 'number,sex') 
      comment 'description of the table'  
     tblproperties('creator'='me','time'='2016.1.1'); 
#创建外部表
create external table if not exists usr2(
      name string,
      pwd string,
  address struct<street:string,city:string,state:string,zip:int>,
      identify map<int,tinyint>) 
      row format delimited fields terminated by ','
     location '/usr/local/hive/warehouse/hive.db/usr'; 
#创建分区表
create table if not exists usr3(
      name string,
      pwd string,
      address struct<street:string,city:string,state:string,zip:int>,
      identify map<int,tinyint>) 
      partitioned by(city string,state string);    
#复制usr表的表模式  
create table if not exists hive.usr1 like hive.usr;
 
show tables in hive;  
show tables 'u.*';        #查看hive中以u开头的表
describe hive.usr;        #查看usr表相关信息
alter table usr rename to custom;      #重命名表
 
#为表增加一个分区
alter table usr2 add if not exists 
     partition(city=”beijing”,state=”China”) 
     location '/usr/local/hive/warehouse/usr2/China/beijing'; 
#修改分区路径
alter table usr2 partition(city=”beijing”,state=”China”)
     set location '/usr/local/hive/warehouse/usr2/CH/beijing';
#删除分区
alter table usr2 drop if exists  partition(city=”beijing”,state=”China”)
#修改列信息
alter table usr change column pwd password string after address;
 
alter table usr add columns(hobby string);                  #增加列
alter table usr replace columns(uname string);              #删除替换列
alter table usr set tblproperties('creator'='liming');      #修改表属性
alter table usr2 partition(city=”beijing”,state=”China”)    #修改存储属性
set fileformat sequencefile;             
use hive;                                                   #切换到hive数据库下
drop table if exists usr1;                                  #删除表
drop database if exists hive cascade;                       #删除数据库和它中的表

查看表
show tables;  查看当前库中的所有的表
show create table tbName;  查看某一个表的具体的建表语句（获取当前表设置的分隔符信息）

show tables in dbName;  指定查看某一个数据库中的所有的表
show tables like 'stu*' ;  模糊查询多个表

desc tbName; 查看表中的具体的字段信息
desc extended tbName ; 查看表的详情 （查看外部表）
desc formatted tbName ; 查看表的详情 （查看内部表和外部表）

修改表
1. 修改表的名称
    alter table tbName  rename to newTbName ;

2. 修改表字段的定义
    添加新的列：
        alter table tbName add columns(colName type... ....)

    修改列定义：change可以修改  列名称 / 列类型 /  列的位置
        alert table tbName change  oldColName  newColName newType  [first|after colName]

    表结构替换： 替换调原来的表结构
        alert table tbName replace  columns(colName type... ....)


注意：hive2.0+版本中，对类型转换进行限制
        小类型 》大类型 ---> 运行
        大类型 》小大类型 ---> 运行

删除表
1. 删除内部表
    drop table tbName;
    truncate table tbName;


    注意：更换mysql驱动包的版本

2. 删除外部表
    方式一：
        alter table  tbName set tblproperties('external'='false');  
        drop table tbName;

    方式二：
        hdfs dfs -rm -r -f /user/hive/warehouse/tbName

视图和索引的创建、修改和删除

主要语法如下，用户可自行实现：

create view view_name as....;                #创建视图
alter view view_name set tblproperties(…);   #修改视图

因为视图是只读的，所以 对于视图只允许改变元数据中的 tblproperties属性。

#删除视图
drop view if exists view_name;
#创建索引
create index index_name on table table_name(partition_name/column_name)  
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild....; 

这里’org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’是一个索引处理器，即一个实现了索引接口的Java类，另外Hive还有其他的索引实现。

alter index index_name on table table_name partition(...)  rebulid;   #重建索引

如果使用 deferred rebuild，那么新索引成空白状态，任何时候可以进行第一次索引创建或重建。

show formatted index on table_name;                       #显示索引
drop index if exists index_name on table table_name;      #删除索引

向表中装载数据
这里我们以只有两个属性的简单表为例来介绍。首先创建表stu和course，stu有两个属性id与name，course有两个属性cid与sid。

create table if not exists hive.stu(id int,name string) 
row format delimited fields terminated by '\t';
create table if not exists hive.course(cid int,sid int) 
row format delimited fields terminated by '\t';

向表中装载数据有两种方法：从文件中导入和通过查询语句插入。

a.从文件中导入
假如这个表中的记录存储于文件stu.txt中，该文件的存储路径为/usr/local/hadoop/examples/stu.txt，内容如下。
stu.txt：

1 xiapi 
2 xiaoxue 
3 qingqing

下面我们把这个文件中的数据装载到表stu中，操作如下：

load data local inpath '/usr/local/hadoop/examples/stu.txt' overwrite into table stu;

如果stu.txt文件存储在HDFS 上，则不需要 local 关键字。

b.通过查询语句插入：
使用如下命令，创建stu1表，它和stu表属性相同，我们要把从stu表中查询得到的数据插入到stu1中：

create table stu1 as select id,name from stu;

上面是创建表，并直接向新表插入数据；若表已经存在，向表中插入数据需执行以下命令：

insert overwrite table stu1 select id,name from stu where（条件）;

这里关键字overwrite的作用是替换掉表（或分区）中原有数据，换成into关键字，直接追加到原有内容后。

从表中导出数据
a.可以简单拷贝文件或文件夹。

hadoop  fs -cp source_path target_path;

b.写入临时文件

insert overwrite local directory '/usr/local/hadoop/tmp/stu'  select id,name from stu;

查询操作

和SQL的查询完全一样，这里不再赘述。主要使用select…from…where…等语句，再结合关键字group by、having、like、rlike等操作。这里我们简单介绍一下SQL中没有的case…when…then…句式、join操作和子查询操作。

case…when…then…句式和if条件语句类似，用于处理单个列的查询结果，语句如下：

select id,name,
  case 
  when id=1 then 'first' 
  when id=2 then 'second'
  else 'third'

连接
连接（join）是将两个表中在共同数据项上相互匹配的那些行合并起来, HiveQL 的连接分为内连接、左向外连接、右向外连接、全外连接和半连接 5 种。

a. 内连接(等值连接)：
内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行。
查询stu和course表中学号相同的所有行，命令如下：

select stu.*, course.* from stu join course on(stu .id=course .sid);

b. 左连接：
左连接的结果集包括“LEFT OUTER”子句中指定的左表的所有行, 而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行, 则在相关联的结果集中右表的所有选择列均为空值，命令如下：

select stu.*, course.* from stu left outer join course on(stu .id=course .sid); 

c. 右连接：
右连接是左向外连接的反向连接,将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。命令如下：

select stu.*, course.* from stu right outer join course on(stu .id=course .sid); 

d. 全连接：
全连接返回左表和右表中的所有行。当某行在另一表中没有匹配行时,则另一个表的选择列表包含空值。如果表之间有匹配行,则整个结果集包含基表的数据值。命令如下：

select stu.*, course.* from stu full outer join course on(stu .id=course .sid); 

e. 半连接：
半连接是 Hive 所特有的, Hive 不支持 in 操作,但是拥有替代的方案; left semi join, 称为半连接, 需要注意的是连接的表不能在查询的列中,只能出现在 on 子句中。命令如下：

select stu.* from stu left semi join course on(stu .id=course .sid); 

表的分类

1.内部表（管理表）
概述：
默认创建的所有表
当前用户对其拥有所有的操作权限（删除，修改… …）
删除内部表，数据仓库存储的数据被删除，元数据中的内容也被删除

查看表的类型：desc formatted tbName ;
Table Type: MANAGED_TABLE

删除表：
drop table tbName;

注意：删除管理表，将所有数据全部删除

2.外部表：
概述：
在创建表时使用external关键字创建的表时外部表
当前用户其拥有部分的操作权限（删除，修改… …）
删除外部表，数据仓库存储的数据不会被删除，元数据中的内容会被删除
适合存储被共享的数据

创建表：
使用关键字 : external

查看表的类型：desc formatted tbName ;
desc extended tbName;
desc tbName;

删除表： 删除的是当前表的元数据
drop table tbName;

注意：删除外部表，不会将所有数据全部删除
创建新的同名称表，数据会进行自动的管理操作

3.分区表
概述：
分区表对当前数据进行划分并存储不同的分区中（ 文件夹 ）
分区表对当前的数据所在的分区进行单独的管理
提高查询效率

创建表：partitioned by

create table tbName(col type... ...)
    partitioned by (pCol type)  # 按照什么字段进行分区
    row format delimited fields terminated by ',';

导入数据：将一个文件中的所有数据全部加载到一个分区中

    load data local inpath ' asdsad' into table partition_tb  
            partition(k=v);

查询分区数据：

select * from tbName where k=v ; 查询 k所在分区   

select * from tbName where k=v 
    union
    select * from tbName  where k =v2 ; 查询 多个分区 

查看表的分区信息：

show partitions tbName;

删除分区：

alter table tbName drop  parititon(k='v') [,parititon(k='v'),parititon(k='v')... ...]           

添加分区：一次添加多个分区时，分区间不需要添加逗号

alter table tbName add  parititon(k='v') [parititon(k='v') parititon(k='v')... ...]     

多级分区表：（本质上就是多层级目录）

partitioned by(year string ,month string)  //按照多个字段分区

多级分区表查询指定分区的数据：

select * from tbName where year=2019 and  month=6