hive知识简单全面详解

hive知识简单全面详解

hive学习整理

• 1、认识Hive
• • 1.1hive认识
  • • 1.1.1 优点
    • 1.1.1 缺点
  • 1.2hive架构
  • 1.3 为什么用Hive而不用关系型数据库？
• 2、hive安装
• • 2.1安装及配置环境
  • 2.2hive启动并使用
  • • 2.2.1使用mysql作为hive的元数据
    • 2.2.2使用JDBC连接远程Hive服务
• 3、hive 的基础使用
• • 3.1数据结构
  • 3.2 STRUCT、MAP、ARRAY
• 4、DDL数据定义
• • 4.2查询数据库
  • • 4.2.2查看数据库详情
    • 4.2.3 切换当前数据库
  • 4.3修改数据库
  • 4.4删除数据库
  • 4.5管理表
  • 4.6外部表
  • 4.7分区表
  • 4.8 分桶表
  • • 4.8.1创建分桶表
  • 4.8.2插入数据：
  • • 4.8.3随机抽样
• 5、数据管理
• • 5.1向表中装载数据(Load)
  • 5.2数据导出
• 6、hive 查询
• • 6.1基本查询
  • • 6.1.1全表和特定列查询
    • 6.1.2 列别名
    • 6.1.3 算术运算符
    • 6.1.4 常用函数
    • 6.1.5 Limit 语句
    • 6.1.6 Where 语句
    • 6.1.7 比较运算符（Between/In/ Is Null）
    • 6.1.8 Like 和 RLike
    • 6.1.9 逻辑运算符（And/Or/Not）
  • 6.2 分组
  • 6.3 Join 语句
  • 6.4 排序
• 7、hive函数

1、认识Hive 1.1hive认识

Hive是建立在Hadoop上的数据仓库工具，可以借助提取、转化、加载技术存储、查询和分析查询在Hadoop中的大规模数据。使得开发人员使用相对简单的SQL语句，就可以 *** 作Hadoop 处理海量数据。
  对外提供HQL，在查询时HQL语句转换为MapReduce任务，在Hadoop层执行。

1.1.1 优点

• 提供了易于 *** 作的类SQL查询语言HQL，不用再去写复杂的MapReduce程序，减少开发成本，上手快。
• 利用MapRecuce计算引擎，HDFS作为存储系统，可以 *** 作大数据集。
• 支持用户的自定义函数，具有良好的容错性。

1.1.1 缺点

• 延迟性比较高，因为启动MapReduce本身需要消耗很多资源
• HQL语言表达能力有限，基于模板实现的，不够智能，处理复杂的业务能力有限，如迭代计算无法是实现
• 不提供实时查询，处理大数据量擅长，处理小数据量没有优势

1.2hive架构

1.3 为什么用Hive而不用关系型数据库？ 对比项HiveRDBMS查询语言HQLSQL数据存储HDFS块设备或本地文件系统执行MapReduceExecutor执行延迟较高较低处理数据规模较高较低可扩展性较高较低数据更新读多写少，不建议改写经常修改 2、hive安装

实验环境:
三台机器，/etc/hosts文件中的机器名分别配置为
hadoop102,hadoop103,hadoop104(集群都是通过机器名来配置的， 只要网络没问题， IP不重要)。上面已经安装了hadoop集群和spark集群。另外，在hadoop102上安装了mysql数据库。

2.1安装及配置环境

1） 从官网下载hive安装包，上传到linux目录下

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

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

$ sudo vim /etc/profile.d/my_env.sh

3） 添加内容

#HIVE_HOME
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin

 使环境变量生效

$ source /etc/profile

4）解决日志 Jar 包冲突，有多个log4j的日志jar包冲突，可以选择删除(可选 *** 作)

$ mv $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak

5）初始化元数据库

$ bin/schematool -dbType derby -initSchema

2.2hive启动并使用

启动Hive之前一定要注意Hadoop的hdfs和yarn，必须是启动的
1） 启动

[xuyuan@hadoop102 hive]$ bin/hive

2）使用，对数据库的基本 *** 作

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

hive使用的derby作为元数据库，开启hive之后就会占用元数据库，并且不能与其他客户端共享

2.2.1使用mysql作为hive的元数据

实验环境已经在hadoop102机器上安装好了mysql

首先需要获取mysql的jdbc驱动包mysql-connector-java-8.0.23.jar(与mysql版
本对应)，放到hive的lib目录下。 hive默认是不带jdbc驱动包的。

1 ）进入$HIVE_HOME/conf目录，新建一个配置文件hive-site.xml。添加以下内容：

 
 
     javax.jdo.option.ConnectionURL
     jdbc:mysql://hadoop102:3306/metastoreuseSSL=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
 

2） 接下来登入mysql，创建一个metastore数据库。

[xuyuan@hadoop102 software]$ mysql -uroot -p密码

[xuyuan@hadoop102 software]$ schematool -initSchema -dbType mysql -verbose

3）最后就可以启动hive，此时hive可打开多个窗口

2.2.2使用JDBC连接远程Hive服务

到目前为止，Hive还只是一个单机的服务，只能在服务器上进行本地连接，其他服务器无法访问。这时可以通过hive提供的元数据服务，将Hive提升为一个对外服务。

 1） 启动服务: 在Hive的conf/hive-site.xml中添加配置信息：

  
  
     hive.metastore.uris 
     thrift://hadoop102:9083 
  
 
  
  
     hive.server2.thrift.bind.host                 
     hadoop102 
  
 
  
  
     hive.server2.thrift.port 
     10000 
 

 2）启动元数据服务

hive --service metastore

 3）启动一个hiveserver2服务。

hive --service hiveserver2

这两个服务都是前台服务，会占据当前命令行窗口。当然也可以通过
nohup hive --service metastore & 将他转到后台。

  然后就可以使用Hive的Beanline客户端以JDBC的方式来接Hive了。但是这时要注意，如果配置了元数据服务的端口，那在hive指令运行时，就会去连接元数据服务。

4）连接Hive服务
使用Hive的beeline连接Hive服务：

[xuyuan@hadoop102 hive]$ bin/beeline -u jdbc:hive2://hadoop102:10000 -n xuyuan

显示如下界面，则表示连接成功

Connecting to jdbc:hive2://hadoop102:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive
0: jdbc:hive2://hadoop102:10000>

一旦配置了Hive的服务，那在使用hive之前就必须要启
动服务才行。即使是使用本地的hive指令连接，也需要服务支持。

3、hive 的基础使用 3.1数据结构

工作机制：在元数据中保存数据结构，而数据全部保存在hdfs上
beeline工具来一个简单的建表：

create table userinfo( 
   name string, 
   age int, 
   salary bigint 
 );
insert into userinfo(name,age,salary)values('roy',18,100); 
select * from userinfo;

3.2 STRUCT、MAP、ARRAY

创建测试文件

     roy,bob_john,yula:6_sophia:17,lugu_changsha 
     mike,lea_jacky,rita:7_king:20,chao yang_beijing

在hive中建表:

create table test3(
   name string, 
   friends array, 
   children map, 
   address struct 
)
row format delimited fields terminated by ','# 表示列分隔符
collection items terminated by '_' #表示 Map、Struct和Array的分隔符
map keys terminated by ':'#Map中key和value的分隔符
lines terminated by 'n'; #一行数据的分隔符

上传test.txt文件到hdfs目录

hadoop fs -put test.txt /xxx/xxx/test

最后，可直接在hive中执行select * from test3，查看所有的数据。
可以尝试下访问这些复杂结构中的各个数据

select name,friends[1],children['yula'],address.city from test3;

hive最常用的工作方式，即数据已经采集到了hdfs上，然后再用hive去针对文本结构进行表映射。
hive的元数据中维护表结构，而表结构本质上就是对文件的一种映射关系。具体的数据以文件的形式放在hdfs对应的目录里。hive中的一个表实际上就对hdfs中的一个目录，hive中的hql语句 *** 作的就是这个目录中的文本文件。

4、DDL数据定义

4.1创建数据库

CREATE DATAbase [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

1）创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。

hive (default)> create database db_hive;

2）避免要创建的数据库已经存在错误，增加 if not exists 判断。（标准写法）

hive (default)> create database db_hive;
FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists
hive (default)> create database if not exists db_hive;

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

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

4.2查询数据库

1）显示数据库

hive> show databases;

2）过滤显示查询的数据库

hive> show databases like 'db_hive*';
OK
db_hive
db_hive_1

4.2.2查看数据库详情

1）显示数据库信息

hive> desc database db_hive;
OK
db_hive  hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db  xuyuanUSER

2）显示数据库详细信息，extended

hive> desc database extended db_hive;
OK
db_hive  hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db  xuyuanUSER

4.2.3 切换当前数据库

hive (default)> use db_hive;

4.3修改数据库

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

ALTER TABLE table_name RENAME TO new_table_name; #重命名表

ALTER TABLE table_name CHANGE  [COLUMN] col_old_name col_new_name column_type
 [COMMENT col_comment] [FIRST|AFTER column_name] #更新列

ALTER TABLE table_name ADD|REPLACe COLUMNS (col_name data_type
 [COMMENT col_comment], ...)#增加或替换列

4.4删除数据库

1）删除空数据库

hive>drop database db_hive2;

2）如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在

hive> drop database db_hive;
FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
hive> drop database if exists db_hive2;

3）如果数据库不为空，可以采用 cascade 命令，强制删除

hive> drop database db_hive;
FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask.
InvalidOperationException(message:Database db_hive is not empty. One or 
more tables exist.)
hive> drop database db_hive cascade;

4.5管理表

  默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会（或多或少地）控制着数据的生命周期。
   当我们删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据。管理表不适合和其他工具共享数据

查询表的类型：

hive (default)> desc formatted student2;
Table Type:      MANAGED_TABLE

4.6外部表

删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉

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

修改内部表 student2 为外部表：

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

修改外部表 student2 为内部表

alter table student2 settblproperties('EXTERNAL'='FALSE');

4.7分区表

Hive中的一个表实际对应HDFS文件系统上的一个独立的文
件夹，文件夹下的所有文本就是Hive中的表数据。而分区表同样也是对应HDFS上的一个文件夹，不过，在分区表中增加了分区的概念，每个分区对应文件夹中的一个子目录。
1）建立分区表

create table deptinfo( 
   deptno int, dname string, officeroom string 
)
partitioned by (year string) 
row format delimited fields terminated by ',';

注意，分区字段不能是表中已经存在的列。

2）然后我们在服务器本地准备几个数据文件dept_1.txt、dept_2.txt、dept_3.txt。内容如下

10,ACCOUNTING,1700 
15,RESEARCH,1800 20,
SALES,1900 25,MANAGER,1700 
30,DEV,1600 35,TEST,1600

3）然后将文件导入分区表：

load data local inpath '/user/dept_1.txt' into table deptinfo partition (year='2021');
load data local inpath '/user/dept_2.txt' into table deptinfo partition (year='2020');
load data local inpath '/user/dept_3.txt' into table deptinfo partition (year='2019');

注意：分区表加载数据时，必须要指定分区

查看表的数据：

select * from deptinfo; 
select * from deptinfo where year='2021'; 
select * from deptinfo where year='2021' or year='2020';

管理分区：

 #创建分区 
 hive>alter table deptinfo add partition(year='2022'); 
 hive>alter table deptinfo add partition(year='2017') partition(year='2018'); 
 #删除分区 
 hive>alter table deptinfo drop partition(year='2017'),partition(year='2018');
 #查看分区表的分区情况 
 hive>show partitions deptinfo;
 #查看表结构 
 hive>desc formatted deptinfo; 
 # Partition Information # col_name data_type comment year string

动态分区
之前的部门信息表，就希望按照officeroom字段来分区。这种就称为动态分区。
1)创建另外一张分区表，对部门信息按照deptno分区

create table deptinfo_dep( 
   dname string, officeroom string
)
partitioned by (deptno int) 
row format delimited fields terminated by ',';

2)指定分区键deptno=‘70’

insert into table deptinfo_dep partition(deptno='70')
select dname,officeroom from deptinfo; --执行mapreduce转移数据。

不指定分区键的值，然后在后面的select语句中，会默认以最后一个字段作为分区键去创建对应的分区。但是这个语句执行后，会报错。
Hive的动态分区默认是strict严格模式，这种模式下必须指定分区键的值。
在hive中执行这个语句 set hive.exec.dynamic.partition.mode=nonstrict 后，就可以执行不指定分区的动态分区语句了

总结与动态分区有关的参数：

#是否开启动态分区功能，默认true
hive> set hive.exec.dynamic.partition;
hive.exec.dynamic.partition=true
#在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000 
hive> set hive.exec.max.dynamic.partitions; hive.exec.max.dynamic.partitions=1000 
#在每个执行MR的节点上，最大可以创建多少个动态分区。 
hive> set hive.exec.max.dynamic.partitions.pernode;
hive.exec.max.dynamic.partitions.pernode=100 
#整个MR Job中，最大可以创建多少个HDFS文件。 
hive> set hive.exec.max.created.files;
hive.exec.max.created.files=100000
#当有空分区生成时，是否抛出异常
hive> set hive.error.on.empty.partition; hive.error.on.empty.partition=false

采用静态分区时，大部分情况下都不会产生MR的计算过程，也就是说通过hive自己的元数据就能够处理简单的分区表。而采用动态分区后，大部分情况下都会产生MR的计算。这对资源和性能都是有损耗的，所以在使用过程中应该要酌情考虑

4.8 分桶表

在分区的基础上，Hive可以进一步组织成桶，对数据文件
进行更细粒度的数据范围划分。分区针对的是数据的存储路径，而分桶针对的是数据文件。

4.8.1创建分桶表

按照ID分桶，分成4个桶。

create table stu_buck(id int, name string) 
clustered by(id)
into 4 buckets
row format delimited fields terminated by ',';

4.8.2插入数据：

这次我们直接从hdfs把数据导入到hive。

 hive> insert into stu_buck values (1,'s1'); 
 hive> insert into stu_buck values (2,'s2');

在Hdfs上可以看到，分桶表对应了四个分桶文件，然后每次计算时，都会重新产生新的分桶文件。
Hive在分桶时， 会对分桶字段的值进行hash计算。然后对桶的个数取余，以此来决定记录存放在哪个桶里。在计算过程中，建议将reduce的个数设置为-1，这样可以让job自行决定需要用多少个reduce。

4.8.3随机抽样

对于非常大的数据集，Hive还支持按通进行随意抽样，以满足某些只需要少量有代表性数据的场景。

hive>select * from stu_buck tablesample(bucket 1 out of 4 on id);

这个tablesample表示在对id的四个分桶中抽取第一个分桶里的随机数据。

查看hive中建表情况：

show create table stu_buck;

5、数据管理

对于Hive来说，管理数据的方式重要的就是导入导出 和 查询两个方面

5.1向表中装载数据(Load)

 hive> load data [local] inpath 'path' [overwrite] into table tablename 
 [partition (partcol1=val1,…)];

load data表示加载数据。
local表示从本地加载数据，默认是从Hdfs加载数据
inpath表示加载数据的路径
override表示覆盖表中已有的数据，否则就表示追加数据。
partition表示上传到指定的分区。

在指定位置建表： 大部分情况下都是建为外部表，hive只做数据索引，不做数据管理。

 create external table if not exists student5( 
 id int ,name string
  )
 row format delimited fields terminated by ',' 、
 location '/student';

5.2数据导出

使用insert直接将查询结果导出到本地

 #导出到服务器本地
 insert overrite local directory '/app/hive/data/export/student'
select * from student;
 #格式化后导出到本地 
 insert overrite local directory '/app/hive/data/export/student1'  
 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
 select * from student; #导出到HDFS
 insert overrite directory '/hive/data/export/student' 
 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' 
 select * from student; 
 #在Hive中直接 *** 作
 hdfs dfs -get /usr/hive/warehouse/student/student.txt /app/hive/data/export/student.txt

导入导出的指令 export import ，多用于不同平台集群之间迁移
Hive表。

 export table db_hive.student to '/user/hive/warehouse/export/student';
 import table student2 from '/user/hive/warehouse/export/student';

对于Hive的数据导入导出，更多的场景不是在Hive之间转移数据，而是在不同数据平台之间转移数据，例如hive与mysql数据、与redis数据之间进行互相导入导出。这时候一般会用其他一些专门的数据转移工具，比如Sqoop。

6、hive 查询

完整的查询语法：

[WITH CommonTableexpression (, CommonTableexpression)*]    (Note: only available starting with Hive 0.13.0)
SELECt [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERe where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

6.1基本查询 6.1.1全表和特定列查询

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;

1）全表查询

hive (default)> select * from emp;
hive (default)> select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp ;

2)选择特定列查询

hive (default)> select empno, ename from emp;

6.1.2 列别名

1）重命名一个列
2）便于计算
3）紧跟列名，也可以在列名和别名之间加入关键字‘AS’

   hive (default)> select ename AS name, deptno dn from emp;

6.1.3 算术运算符 运算符描述A+BA 和 B 相加A-BA 减去 BA*BA 和 B 相乘A/BA 除以 BA%BA 对 B 取余A&BA 和 B 按位取与ABA^BA 和 B 按位取异或~AA 按位取反

案例实 *** ：查询出所有员工的薪水后加 1 显示。

hive (default)> select sal +1 from em

6.1.4 常用函数

1）求总行数（count）

hive (default)> select count(*) cnt from emp;

2）求工资的最大值（max）

hive (default)> select max(sal) max_sal from emp;

3）求工资的最小值（min）

hive (default)> select min(sal) min_sal from emp;

4）求工资的总和（sum）

hive (default)> select sum(sal) sum_sal from emp; 

5）求工资的平均值（avg）

hive (default)> select avg(sal) avg_sal from emp;

6.1.5 Limit 语句

LIMIT 子句用于限制返回的行数。

hive (default)> select * from emp limit 5;

6.1.6 Where 语句

1）使用 WHERe 子句，将不满足条件的行过滤掉
2）WHERe 子句紧随 FROM 子句

查询出薪水大于 1000 的所有员工
hive (default)> select * from emp where sal >1000;

6.1.7 比较运算符（Between/In/ Is Null） 6.1.8 Like 和 RLike 6.1.9 逻辑运算符（And/Or/Not） 6.2 分组 6.3 Join 语句 6.4 排序 7、hive函数