hive学习 踏雪扬尘

目录

• • Hive 简介
  • Hive 特点
  • Hive 和 RDBMS 的对比
  • Hive 的架构
  • Hive 的数据存储
  • DDL表操作
  • DML操作
  • 数据类型
  • Hive函数
  • hive 全操作
  • UDF案例
  • 特殊分隔符的处理
• Storage Information

Hive 简介

Hive 由 Facebook 实现并开源，是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据 映射
为一张数据库表，并提供 HQL(Hive SQL)查询功能，底层数据是存储在 HDFS 上。Hive 的本质是将
SQL 语句转换为 MapReduce 任务运行，使不熟悉 MapReduce 的用户很方便地利 用 HQL 处理和计算
HDFS 上的结构化的数据，适用于离线的批量数据计算。

Hive 特点

优点：
1、可扩展性,横向扩展，Hive 可以自由的扩展集群的规模，一般情况下不需要重启服务
横向扩展
纵向扩展
2、延展性，Hive 支持自定义函数，用户可以根据自己的需求来实现自己的函数
3、良好的容错性，可以保障即使有节点出现问题，SQL 语句仍可完成执行

缺点：
1、Hive 不支持记录级别的增删改操作，但是用户可以通过查询生成新表或者将查询结果导入到文件中
2、Hive 的查询延时严重，因为 MapReduce的启动过程消耗很长时间，所以不能用在交互查询系统
中。
3、Hive 不支持事务（因为不没有增删改，所以主要用来做 OLAP（联机分析处理），而不是
OLTP（联机事务处理），这就是数据处理的两大级别）

Hive 和 RDBMS 的对比

Hive 的架构

基本组成
一、用户接口
CLI，Shell 终端命令行（Command Line Interface），采用交互形式使用 hive 命令行与 hive 进
行交互，最常用（学习，调试，生产）
JDBC/ODBC，是 Hive 的基于 JDBC 操作提供的客户端，用户（开发员，运维人员）通过 这连接至
Hive server 服务
Web UI，通过浏览器访问 Hive
二、Thrift Server
Hive 集成了该服务，能让不同的编程语言调用 hive 的接口
三、元数据存储
解决方案：通常存我们自己创建的 MySQL 库（本地 或 远程）
一般是远程的， hive 和MySQL之间通过 hive metastore 服务交互。
Hive 的核心是驱动引擎， 驱动引擎由四部分组成：
(1) 解释器：解释器的作用是将 HiveSQL 语句转换为抽象语法树（AST）
(2) 编译器：编译器是将语法树编译为逻辑执行计划
(3) 优化器：优化器是对逻辑执行计划进行优化
(4) 执行器：执行器是调用底层的运行框架执行逻辑执行计划

Hive 的数据存储

Hive 中包含以下数据模型：
database：在 HDFS 中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
table：在 HDFS 中表现所属 database 目录下一个文件夹
external table：与 table 类似，不过其数据存放位置可以指定任意 HDFS 目录路径
partition：在 HDFS 中表现为 table 目录下的子目录
bucket：在 HDFS 中表现为同一个表目录或者分区目录下根据某个字段的值进行 hash 散列之后的多个文件
view：与传统数据库类似，只读，基于基本表创建

DDL表操作

建表语句

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --
(Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [column_constraint_specification] [COMMENT col_comment],
... [constraint_specification])]
[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, ...) -- (Note: Available in
Hive 0.10.0 and later)]
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 (...)] --
(Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in
Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not
supported for external tables)

DML操作

1、装载数据
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION
(partcol1=val1, partcol2=val2 ...)]
load data local inpath "/home/data/student.txt" into table student;

2, 插入数据
插入一条数据：
INSERT INTO TABLE tablename VALUES(a,b,c)
利用查询语句将结果导入新表：
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)
[IF NOT EXISTS]] select_statement1 FROM from_statement;
多重插入
FROM from_statement
INSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2
...)] select_statement1
INSERT OVERWRITE TABLE table_name2 [PARTITION (partcol1=val1, partcol2=val2
...)] select_statement2] ...

from student
insert into table student_ptn partition (department = 'IS') select id,sex,name,age where department = 'IS'
insert into table student_ptn partition (department = 'CS') select id,sex,name,age where department = 'CS'
insert into table student_ptn partition (department = 'MA') select id,sex,name,age where department = 'MA'

3,导出数据
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement
insert overwrite local directory "/home/data/cs_student" select * from student where department = 'CS';
insert overwrite directory "/home/data/cs_student" select * from student where department = 'CS';

4,查询数据

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]

数据类型

原子数据类型

复杂数据类型
复杂数据类型包括数组（ARRAY）、映射（MAP）和结构体（STRUCT）

总结：在关系型数据库中，我们至少需要三张表来定义，包括学生基本表、爱好表、成绩表； 但在
Hive 中通过一张表就可以搞定了。也就是说，复合数据类型把多表关系通过一张表就 可以实现了。

Hive函数

一、关系运算：
1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 区间比较
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
10. LIKE比较: LIKE
11. JAVA的LIKE操作: RLIKE
12. REGEXP操作: REGEXP
二、数学运算：
1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^
9．位取反操作: ~
三、逻辑运算：
1. 逻辑与操作: AND 、&&
2. 逻辑或操作: OR 、||
3. 逻辑非操作: NOT、!
四、复合类型构造函数
1. map结构
2. struct结构
3. named_struct结构
4. array结构
5. create_union
五、复合类型操作符
1. 获取array中的元素
2. 获取map中的元素
3. 获取struct中的元素
六、数值计算函数
1. 取整函数: round
2. 指定精度取整函数: round
3. 向下取整函数: floor
4. 向上取整函数: ceil
5. 向上取整函数: ceiling
6. 取随机数函数: rand
7. 自然指数函数: exp
8. 以10为底对数函数: log10
9. 以2为底对数函数: log2
10. 对数函数: log
11. 幂运算函数: pow
12. 幂运算函数: power
13. 开平方函数: sqrt
14. 二进制函数: bin
15. 十六进制函数: hex
16. 反转十六进制函数: unhex
17. 进制转换函数: conv
18. 绝对值函数: abs
19. 正取余函数: pmod
20. 正弦函数: sin
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive函数: positive
25. negative函数: negative
七、集合操作函数
1. map类型大小：size
2. array类型大小：size
3. 判断元素数组是否包含元素：array_contains
4. 获取map中所有value集合
5. 获取map中所有key集合
6. 数组排序
八、类型转换函数
1. 二进制转换：binary
2. 基础类型之间强制转换：cast
九、日期函数
1. UNIX时间戳转日期函数: from_unixtime
2. 获取当前UNIX时间戳函数: unix_timestamp
3. 日期转UNIX时间戳函数: unix_timestamp
4. 指定格式日期转UNIX时间戳函数: unix_timestamp
5. 日期时间转日期函数: to_date
6. 日期转年函数: year
7. 日期转月函数: month
8. 日期转天函数: day
9. 日期转小时函数: hour
10. 日期转分钟函数: minute
11. 日期转秒函数: second
12. 日期转周函数: weekofyear
13. 日期比较函数: datediff
14. 日期增加函数: date_add
15. 日期减少函数: date_sub
十、条件函数
1. If函数: if
2. 非空查找函数: COALESCE
3. 条件判断函数：CASE
4. 条件判断函数：CASE
十一、字符串函数
1. 字符ascii码函数：ascii
2. base64字符串
3. 字符串连接函数：concat
4. 带分隔符字符串连接函数：concat_ws
5. 数组转换成字符串的函数：concat_ws
6. 小数位格式化成字符串函数：format_number
7. 字符串截取函数：substr,substring
8. 字符串截取函数：substr,substring
9. 字符串查找函数：instr
10. 字符串长度函数：length
11. 字符串查找函数：locate
12. 字符串格式化函数：printf
13. 字符串转换成map函数：str_to_map
14. base64解码函数：unbase64(string str)
15. 字符串转大写函数：upper,ucase
16. 字符串转小写函数：lower,lcase
17. 去空格函数：trim
18. 左边去空格函数：ltrim
19. 右边去空格函数：rtrim
20. 正则表达式替换函数：regexp_replace
21. 正则表达式解析函数：regexp_extract
22. URL解析函数：parse_url
23. json解析函数：get_json_object
24. 空格字符串函数：space
25. 重复字符串函数：repeat
26. 左补足函数：lpad
27. 右补足函数：rpad
28. 分割字符串函数: split
29. 集合查找函数: find_in_set
30. 分词函数：sentences
31. 分词后统计一起出现频次最高的TOP-K
32. 分词后统计与指定单词一起出现频次最高的TOP-K
十二、混合函数
1. 调用Java函数：java_method
2. 调用Java函数：reflect
3. 字符串的hash值：hash
十三、XPath解析XML函数
1. xpath
2. xpath_string
3. xpath_boolean
4. xpath_short, xpath_int, xpath_long
5. xpath_float, xpath_double, xpath_number
十四、汇总统计函数（UDAF）
1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 非空集合总体变量函数: var_pop
7. 非空集合样本变量函数: var_samp
8. 总体标准偏离函数: stddev_pop
9. 样本标准偏离函数: stddev_samp
10．中位数函数: percentile
11. 中位数函数: percentile
12. 近似中位数函数: percentile_approx
13. 近似中位数函数: percentile_approx
14. 直方图: histogram_numeric
15. 集合去重数：collect_set
16. 集合不去重函数：collect_list
十五、表格生成函数Table-Generating Functions (UDTF)
1. 数组拆分成多行：explode
2. Map拆分成多行：explode

hive 全操作

1、建库

create database mydb;
create database if no exists mydb;

2、查询数据库

查询库列表：show databases;
查询库详细信息：desc database [extended] mydb;
查询当前正在使用的库： select current_database();

3、删除数据库

drop database mydb;
drop database if exists mydb;
drop database if exists mydb [restrict|cascade];

4、先进入我们要操作的数据库/切换库

use mydb;

5、查看数据库里面的表

show tables;
show tables in mydb;

6、添加表

创建内部表（Managered_Table）
create table mingxing(id int, name string, sex string, age int, department string comment "ss") comment "table comment" row format delimited fields terminated by ',';

show create table mingxing;

创建外部表（External_Table）
create external table mingxing(id int, name string, sex string, age int, department string)
row format delimited fields terminated by ',' location '/root/hivedata';
注意：创建外部表的时候指定location的位置必须是目录，不能是单个文件

跟内部表对比：
1、在创建表的时候指定关键字： external
2、一般来说，创建外部表，都需要指定一个外部路径

内部表和外部表的区别
内部表和外部表的选择

不管是创建外部表还是内部表，都可以指定数据存储目录
默认的目录：
hdfs://hadoop02:9000/user/hive/warehouse/myhive.db/student/student.txt

创建分区表
create table mingxing(id int, name string, sex string, age int, department string)
partitioned by (city string)
row format delimited fields terminated by ',';
注意：partitioned里的字段不是能是表中声明的字段
分区字段是一个虚拟列。分区字段的值是不存在于数据文件中的。
分区字段的使用和普通字段没有区别

分区字段/表字段

创建分桶表
create table mingxing(id int, name string, sex string, age int, department string)
clustered by(id) sorted by(age desc) into 4 buckets
row format delimited fields terminated by ',';
注意：clustered里的字段必须要是表字段中出现的字段

分桶字段/表字段

分桶字段和排序字段可以不一样，分桶字段和排序字段都必须是表字段中的一部分

7、删除表

drop table mingxing;
drop table if exists mingxing;

8、对表进行重命名

alter table mingxing rename to student;

9、对表的字段进行操作（增加add，删除drop，修改change，替换replace）

增加字段：
alter table mingxing add columns (province string);
alter table mingxing add columns (province string, xxx bigint);

删除字段：
drop（不支持） XXXXX

修改字段：
alter table mingxing change age newage string;
alter table mingxing change age newage string after id;
alter table mingxing change age newage string first;

替换字段
alter table mingxing replace columns(id int, name string, sex string);

10、对表中的分区进行操作（添加分区，删除分区，修改分区）

增加分区：
alter table mingxing add partition(city='beijing');
alter table mingxing add partition(city='beijing') location '/city_beijing';
alter table mingxing add partition(city='beijing') partition(city='tianjin');

删除分区：
alter table mingxing drop partition(city='beijing');
alter table mingxing drop partition(city='beijing'), partition(city='tianjin');

修改分区路径：
alter table mingxing partition(city="beijing") set location "/mingxing_beijing";

11、查询显示命令

查看库：show databases;
查看表：show tables;
查看建表完整语法：show create table mingxing;
查看内置函数库：show functions;
查看函数的详细手册：desc function extended concat;
查看分区：show partitions mingxing;
查看表的字段：desc mingxing;
查看表的详细信息：desc extended mingxing;
查看表的格式化了之后的详细信息：desc formatted mingxing;

12、load方式导入数据

load data ..... [overwrite] into table .....

导入本地相对路径的数据
load data local inpath './student.txt' into table mingxing;
load data local inpath './student.txt' overwrite into table mingxing;
(覆盖导入)

导入本地绝对路径数据：
load data local inpath '/home/hadoop/student.txt' into table mingxing;

导入本地数据，相当于复制或者上传

导入HDFS上的简便路径数据：
load data inpath '/home/hadoop/student.txt' into table mingxing;

导入HDFS上的全路径模式下的数据：
load data inpath 'hdfs://hadoop01:9000/root/hivedata/student.txt' into table mingxing;

导入HDFS上的数据到hive表，表示截切，移动

13、利用insert关键字往表中插入数据

五种：
1、单条记录插入
2、单重插入
3、多重插入
4、分区插入
5、分桶插入
6、CTAS

insert ...  values ..
insert  ... select ..

最有趣最有用最常用的：  hadoop fs -put /source /dst
hive是一个读模式的数据仓库

单条数据插入：
insert into table mingxing values(001,'huangbo','male',50,'MA');

单重插入模式： insert ... select ....
insert into table student select id,name,sex,age,department from mingxing;
注意：查询出的字段必须是student表中存在的字段

多重插入模式：
from mingxing
insert into table student1 select id,name,sex,age
insert into table student2 select id,department;

from mingxing2
insert into table student1 partition(department='MA') select id,name,sex ,age where department='MA'
insert into table student1 partition(department='CS') select id,name,sex ,age where department='CS'; 

分区插入：
需要手动的创建分区
alter table student add partition (city="zhengzhou")
load data local inpath '/root/hivedata/student.txt' into table student partition(city='zhengzhou');


CTAS(create table ... as select ...)(直接把查询出来的结果存储到新建的一张表里)
内部表/内建表
create table student as select id,name,age,department from mingxing;
注意：自动新建的表中的字段和查询语句出现的字段的名称，类型，注释一模一样

限制：
1、不能创建外部表
2、不能创建分区表
3、不能创建分桶表

分桶插入：

创建分桶表：
create table mingxing(id int, name string, sex string, age int, department string)
clustered by(id) sorted by(age desc) into 4 buckets
row format delimited fields terminated by ',';

不能使用load方式直接往分桶表中导入数据
插入数据：
insert into table mingxing select id,name,sex,age,department from mingxing2
distribute by id sort by age desc;
注意：查询语句中的分桶信息必须和分桶表中的信息一致

14、like关键字使用：复制表结构

create [external] table student like mingxing;

15、利用insert导出数据到本地或者hdfs

单模式导出数据到本地：
insert overwrite local directory '/root/outputdata' select id,name,sex,age,department from mingxing;

多模式导出数据到本地：
from mingxing
insert overwrite local directory '/root/outputdata1' select id, name
insert overwrite local directory '/root/outputdata2' select id, name,age

简便路径模式导出到hdfs：
insert overwrite directory '/root/outputdata' select id,name,sex,age,department from mingxing;

全路径模式查询数据到hdfs：
insert overwrite directory 'hdfs://hadoop01:9000/root/outputdata1' select id,name,sex,age,department from mingxing;

local ：导出到本地目录
overwrite ：表示覆盖

16、清空数据库表中的数据

truncate table mingxing2;
清空表只是清空该表的所有数据文件

17、select查询

order by : 全局排序

sort by ：局部排序
一般来说，要搭配 分桶操作使用
distribute by id sort by age desc;

distribute by : 纯粹就是分桶
在使用distribute by的时候：要设置reduceTask的个数

cluster by ： 既分桶，也排序
cluster by age = distribute by age sort by age;

cluster by 和 sort by 不能同时使用

where , group by, distinct ,having ,  case...when, ....

UDF案例

依赖：

org.apache.hive
hive-exec
1.2.1

1、先开发一个简单的 java 类，继承 org.apache.hadoop.hive.ql.exec.UDF，重载 evaluate 方法
public class ToLowerCase extends UDF {
// 必须是 public，并且 evaluate 方法可以重载
public String evaluate(String field)
{
String result = field.toLowerCase();
return result;
}
}

2、打成 jar 包上传到服务器 /home/data/utf.jar

3、将 jar 包添加到 hive 的 classpath
hive>add JAR /home/data/utf.jar;

4、查看加入的 jar 的命令： hive> list jar;

5、创建临时函数与开发好的 class 关联起来
hive>create temporary function tolowercase as 'com.naixue.hive.ToLowerCase';

6、至此，便可以在 hql 在使用自定义的函数
select tolowercase(name) from student;

特殊分隔符的处理

数据：
11||zhangsan||22
22||lisi||22
33||wangwu||22
建表语句：
create table bi_test(id int,name string) row format delimited fields terminated by '||';
注意项：默认的分隔符只能是char类型。

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

Hive特殊分隔符处理

Hive 对文件中字段的分隔符默认情况下只支持单字节分隔符，如果数据文件中的分隔符是多字符的，如下所示：
01||huangbo
02||xuzheng
03||wangbaoqiang

1 使用 RegexSerDe 通过正则表达式来抽取字段

create table t_bi_reg(id string,name string) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties('input.regex'='(.)\|\|(.)','output.format.string'='%1$s %2$s') stored as textfile;

hive>load data local inpath '/home/data/bi2.txt' into table t_bi_reg;

hive>select * from t_bi_reg;

2 通过自定义 InputFormat 解决特殊分隔符问题
其原理是在 inputformat 读取行的时候将数据中的“多字节分隔符”替换为 hive 默认的分隔符（ctrl+A 亦即 \001）或用于替代的单字符分隔符，以便 hive 在 serde 操作时按照默认的单字节分隔符进行字段抽取