Hive详解（附Hive安装及使用，MySQL的安装等）

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

4）驱动器：Driver

（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。这一步做了准备工作。

（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划，也就是将SQL编译成MR程序。

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

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

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

1.4 Hive和数据库比较

        由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将 Hive 理解为数据库。其实从结构上来看，Hive 和数据库除了拥有类似的查询语言，再无类似之处。下列将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中，但是Hive 是为数据仓库而设计的，清楚这一点，有助于从应用角度理解 Hive 的特性。

1.4.1 查询语言

        由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

1.4.2 数据更新

        由于Hive是针对数据仓库应用设计的，而数据仓库的内容是读多修改的少。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。

        而数据库中的数据通常是需要经常进行修改的。

1.4.3 执行延迟

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

1.4.4 数据规模

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

2. Hive的安装

1）把apache-hive-3.1.2-bin.tar.gz上传到linux的/opt/software目录下

2）解压apache-hive-3.1.2-bin.tar.gz到/opt/module/目录下面

tar -zxvf /opt/software/apache-hive-3.1.2-bin.tar.gz -C /opt/module/

3）修改apache-hive-3.1.2-bin.tar.gz的名称为hive

mv /opt/module/apache-hive-3.1.2-bin/ /opt/module/hive

4）修改/etc/profile.d/my_env.sh，添加环境变量

sudo vim /etc/profile
输入：
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin
退出！

source /etc/profile

5）初始化数据库

bin/schematool -dbType derby -initSchema

bin/hive

7）查看hive日志信息

# 如果hive启动报错，可以去查看它的日志
cat /tmp/[用户名]/hive.log

注意：Hive 默认使用的元数据库为 derby，开启 Hive 之后就会占用元数据库，且不与

3. 安装mysql

3.1 安装包准备

1）卸载自带的Mysql-libs（如果之前安装过mysql，要全都卸载掉）

rpm -qa|grep mariadb
sudo rpm -e --nodeps mariadb-libs

2）将安装包和JDBC驱动上传到/opt/software，共计6个（下列这五个一解压mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar就能得到，可以查看一下）

mysql-community-common-5.7.29-1.el7.x86_64.rpm

mysql-community-libs-5.7.29-1.el7.x86_64.rpm

mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm

mysql-community-client-5.7.29-1.el7.x86_64.rpm

mysql-community-server-5.7.29-1.el7.x86_64.rpm

mysql-connector-java-5.1.48.jar

3.2 安装MySql

快速安装命令（进入到software目录下执行，确保无其他.rpm文件）

ls *.rpm | xargs -n1 sudo rpm -ivh

或者如下操作一个一个安装：并作出相应rpm简单解释

1）安装mysql依赖

sudo rpm -ivh 01_mysql-community-common-5.7.29-1.el7.x86_64.rpm

sudo rpm -ivh 02_mysql-community-libs-5.7.29-1.el7.x86_64.rpm

sudo rpm -ivh 03_mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm

2）安装mysql-client

sudo rpm -ivh 04_mysql-community-client-5.7.29-1.el7.x86_64.rpm

3）安装mysql-server

sudo rpm -ivh 05_mysql-community-server-5.7.29-1.el7.x86_64.rpm

如报错，使用yum安装libail

sudo yum install libaio

4）初始化并启动mysql

sudo -u mysql mysqld --initialize

sudo systemctl start mysqld

5）查看mysql密码

sudo cat /var/log/mysqld.log | grep password

3.3 配置MySql

配置只要是root用户+密码，在任何主机上都能登录MySQL数据库。

1）用刚刚查到的密码进入mysql（如果报错，给密码加单引号）

mysql -uroot -p’password’

2）设置简单好记的密码

set password=password("000000");

3）进入msyql库：use mysql

4）查询user表

select user, host from user;

5）修改user表，把Host表内容修改为%

update user set host="%" where user="root";

8）刷新：flush privileges;

9）退出：quit;

简单小总结：Hive中出错去：/tmp/[用户名]/hive.log，如果MySQL出错了去：/var/log/mysql.log

3.4 Hive元数据配置到MySql

        3.4.1 将MySQL的JDBC驱动拷贝到Hive的lib目录下

cp /opt/software/mysql-connector-java-5.1.48.jar $HIVE_HOME/lib

        3.4.2 配置Metastore到MySql

vim $HIVE_HOME/conf/hive-site.xml

        添加一下内容

    
    
        javax.jdo.option.ConnectionURL
        jdbc:mysql://hadoop100:3306/hive?useSSL=false
    
    
    
    
        javax.jdo.option.ConnectionDriverName
        com.mysql.jdbc.Driver
    

    
    
        javax.jdo.option.ConnectionUserName
        root
    

    
    
        javax.jdo.option.ConnectionPassword
        000000
    

    
    
        hive.metastore.schema.verification
        false
    

    
    
        hive.metastore.event.db.notification.api.auth
        false
    

    
    
        hive.metastore.warehouse.dir
        /user/hive/warehouse
    

mysql -uroot -p000000

create database metastore;
quit;

schematool -initSchema -dbType mysql -verbose

hive> show databases;
hive> show tables;
hive> create table test (id int);
hive> insert into test values(1);
hive> select * from test;

        4.1 Hive基本的数据类型

        4.2 集合数据类型

5. DDL数据定义 

         5.1 数据库操作

# 其中if not表示创建XXX表的时候如果已经存在就不执行该命令
# location表示将库创建到指定的目录下边
create database if not exists XXX location '/XXX';

# 查询所有数据库
show databases;

# like参数代表查找以XXX开头的
show databases like 'XXX*';

# 查询当前数据库的详细信息
desc database XXX;

# 切换数据库
use XXX;

# 修改数据库
alter database XXX set dfproperties("XXX":"XXX")

# 删除数据库,只能删除空的库
drop database XXX;

# 如果不为空但是想要删除使用
drop database XXX cascade;

        5.2 建表语法

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

        5.2.1 字段解释说明 

        （1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

        （2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

        （3）COMMENT：为表和列添加注释。

        （4）PARTITIONED BY创建分区表

        （5）CLUSTERED BY创建分桶表

        （6）SORTED BY不常用，对桶中的一个或多个列另外排序

        （7）ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

        用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。

        SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。

        （8）STORED AS指定存储文件类型

        常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

        如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

        （9）LOCATION ：指定表在HDFS上的存储位置。

        （10）AS：后跟查询语句，根据查询结果创建表。

        （11）LIKE允许用户复制现有的表结构，但是不复制数据。  

        5.2.2 管理表

        1）理论

        默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

        2）案例实操

        （1）普通创建表

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';

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

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

        （3）根据已经存在的表结构创建表

create table if not exists student4 like student;

        （4）查询表的类型

desc formatted student2;

Table Type:             MANAGED_TABLE  # 这个类型就是内部表

        5.2.3 外部表

        1）理论

        因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

        2）管理表和外部表的使用场景

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

        3）案例实操

分别创建部门和员工外部表，并向表中导入数据。

（1）上传数据到HDFS

（2）建表语句，创建外部表

hive (default)> create external table stu_external2(

id int,

name string)

row format delimited fields terminated by '\t'

location '/student';

（3）查看创建的表

hive (default)> select * from stu_external;

OK

stu_external.id stu_external.name

1001    lisi

1002    wangwu

1003    zhaoliu

（4）查看表格式化数据

hive (default)> desc formatted dept;

Table Type:             EXTERNAL_TABLE

（5）删除外部表

hive (default)> drop table stu_external;

外部表删除后，hdfs中的数据还在，但是metadata中stu_external的元数据已被删除

5.2.4 管理表与外部表的互相转换

（1）查询表的类型

hive (default)> desc formatted student2;

Table Type:             MANAGED_TABLE

（2）修改内部表student2为外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

（3）查询表的类型

hive (default)> desc formatted student2;

Table Type:             EXTERNAL_TABLE

（4）修改外部表student2为内部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

（5）查询表的类型

hive (default)> desc formatted student2;

Table Type:             MANAGED_TABLE

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

6. DML数据操作

        6.1 数据导入

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

        6.1.2 实操案例
        （0）创建一张表

create table student(id string, name string) row format 
delimited fields terminated by '\t';

        （1）加载本地文件到 hive

load data local inpath '/opt/module/hive/datas/student.txt' into table default.student;

        （2）加载 HDFS 文件到 hive 中

# 上传文件到 HDFS
hadoop fs -put /opt/module/hive/data/student.txt /user/XXX/hive;

# 加载 HDFS 上数据
load data inpath '/user/XXX/hive/student.txt' into table default.student;

        发现从HDFS上加载数据到Hive要比从本地往上传快很多。
        （3）加载数据覆盖表中已有的数据

# 上传文件到 HDFS
hadoop fs -put /opt/module/data/student.txt /user/XXX/hive;

# 加载数据覆盖表中已有的数据
load data inpath '/user/XXX/hive/student.txt' 
overwrite into table default.student;

        6.2 通过查询语句向表中查数据（Insert）

        6.2.1 从一张表的查询结果直接写到另一张表上

# 从student这张表上的查询数据再插入到student这个表上，打印出来结果是两份student的数据
insert into student select * from student;

# 从student这张表上的查询数据再插入到student这个表上，overwriter会覆盖原来表的内容
insert overwrite table student select * from student;

        6.2.2 在建表的时候，从一个表中查数据并且建到另一个新表上

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

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

# 上传数据到 hdfs 上 
hadoop fs -mkdir /student; 
hadoop fs -put /opt/module/datas/student.txt /student; 

# 创建表，并指定在 hdfs 上的位置 
create external table if not exists student5(id int, name string)  row format delimited fields terminated by '\t'  location '/student; 

# 查询数据 
select * from student5;

        6.3 数据导出

# 将hdfs上的表通过Hive导下来
# 其中添加row format是为了导出来的数据是以‘，’分割的
insert overwrite local directory '/opt/module/hive/student' row format delimited fields terminated by ',' select * from student;

# 导出到HDFS上的其他路径的话，去掉local参数就可以
insert overwrite directory '/student' row format delimited fields terminated by ',' select * from student;

        6.3.1 通过hadoop命令拉下来

hadoop fs -get /user/hive/warehouse/student/student.txt
/opt/module/data/export/student3.txt;

        6.3.2 通过Shell命令导出

 bin/hive -e 'select * from default.student;' >/opt/module/hive/data/export/student4.txt;

        6.4 清空表

# 只能清空内部表
truncate table student;

7. 查询

        7.1 基本查询

（0）原始数据
dept.txt:
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
emp.txt：
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10

（1）创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

（2）创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

（3）导入数据
load data local inpath '/opt/module/datas/dept.txt' into table dept;
load data local inpath '/opt/module/datas/emp.txt' into table emp;

        7.1.1 全表查询

select * from emp;

        7.1.2 特定列查询

select empno, ename from emp;

# 也可以为列取别名，取别名的时候，用不用AS都可以。
select ename AS name, deptno dn from emp;

        注意SQL对大小写不敏感，SQL尽量不要写Tab缩进。关键字不能分行写。

        7.1.3 算术运算符

# 实现所有源共的薪水 +1 显示
select ename sal + 1000 from emp;

         7.1.4 常用函数

# 求总行数（count）
hive (default)> select count(*) from emp;
# 求工资的最大值（max）
hive (default)> select max(sal) from emp;
# 求工资的最小值（min）
hive (default)> select min(sal) from emp;
# 求工资的总和（sum）
hive (default)> select sum(sal) from emp; 
# 求工资的平均值（avg）
hive (default)> select avg(sal) from emp;

        7.1.5 Limit语句

# 限制输出的行数
select * from emp limit 2;

        7.1.6 where语句

# 查看工资大于1000的
select * from emp where sal > 1000;

# 查询出薪水等于 5000 的所有员工
select * from emp where sal =5000;

# 查询工资在 500 到 1000 的员工信息
select * from emp where sal between 500 and 1000;

# 查询 comm 为空的所有员工信息
select * from emp where comm is null;

# 查询工资是 1500 或 5000 的员工信息
select * from emp where sal IN (1500, 5000);

# 查找名字以 A 开头的员工信息
select * from emp where ename LIKE 'A%';

# 查找名字中第二个字母为 A 的员工信息
select * from emp where ename LIKE '_A%';

# 查找名字中带有 A 的员工信息
select * from emp where ename RLIKE '[A]';

        7.1.7 逻辑运算符

# 查询薪水大于 1000，部门是 30
select * from emp where sal>1000 and deptno=30;

# 查询薪水大于 1000，或者部门是 30
select * from emp where sal>1000 or deptno=30;

# 查询除了 20 部门和 30 部门以外的员工信息
select * from emp where deptno not IN(30, 20)

         7.2 聚合函数Group By

        GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

# 计算 emp 表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

# 计算 emp 每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

        7.2.1 Having语句

        （1）where 后面不能写分组函数，而 having 后面可以使用分组函数。

        （2）having 只用于 group by 分组统计语句。

# 求每个部门的平均薪水大于 2000 的部门
# 求每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;

# 求每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

# 上述语句等同于where：
select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno)t1 where avg_sal > 2000;

# 建议还是使用having好了！

注意：where后边不能写聚合函数！！！