目录
1. Hive简介
1.1 什么是Hive
Hive是Facebook为了解决海量日志数据的统计分析而开发的基于Hadoop的一个数据仓库工具(后来开源给了Apache软件基金会)，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能.HQL。
本质上：将HQL语句转换为MapReduce任务进行运行(转化流程如下）
主要用途：做离线数据分析，比直接用MapReduce 开发效率更高
1.2 为什么使用Hive
直接使用Hadoop MapReduce 处理数据面临的问题：
- 人员学习成本太高
- MapReduce 实现复杂查询逻辑开发难度太大
使用Hive：
- 操作接口采用类SQL语法，提供快速开发的能力
- 避免了去写MapReduce,减少开发人员的学习成本
- 功能扩展很方便
1.3 Hive的优缺点
- 优点
- 操作接口采用类SQL语法，避免了写MapReduce程序，简单易上手，减少开发人员学习成本
- 在数据处理方面，Hive语句最终会生成MapReduce任务去计算，常用于离线数据分析，对数据实时
性要求不高的场景 - 在数据存储方面，它能够存储很大的数据集，并且对数据完整性、格式要求并不严格
- 在延展性方面，Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数
- 缺点
- Hive的HQL本身表达能力有限，不能够进行迭代式计算，在数据挖掘方面也不擅长
- Hive操作默认基于MapReduce引擎，延迟高，不适用于交互式查询，因此智能化程度低，并且基于
SQL调优困难，粒度较粗
1.4 Hive架构
1．用户接口：Client CLI（hive shell）、JDBC/ODBC(java 访问 hive)、WEBUI（浏览器访问 hive）
2．元数据：Metastore 元数据包括：表名、表所属的数据库（默认是 default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
3．Hadoop 使用 HDFS 进行存储，使用 MapReduce 进行计算。
4．驱动器：Driver
（1）解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
（2）编译器（Physical Plan）：将 AST 编译生成逻辑执行计划。
（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来说，就是MR/Spark。
Hive 运行机制如下图：
Hive 通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的 Driver，结合元数据(MetaStore)，将这些指令翻译成 MapReduce，提交到 Hadoop 中执行，最后，将执行返回的结果输出到用户交互接口。
1.5 Hive与传统数据库对比
Hive 用于海量数据的离线分析。
Hive 具有sql数据库的外表，但应用场景完全不同，Hive 只适用于批量数据统计分析。
更直观的对比图如下：
1.6 Hive数据模型
Hive 的数据模型主要有以下四种，如下图所示：
1.7 Hive安装
Hive 安装前需要安装好JDK和Hadoop，配置好环境变量
根据元数据存储介质不同，分为两个版本
- 内置derby版：
- 优点：安装配置简单
- 缺点：不同路径启动Hive,每个Hive拥有一套自己的元数据，无法共享每个目录下都会出现(derby.log 及 metastore_db)
- mysql 版：(mysql 进行元数据保存)
- 缺点： 安装配置复杂
- 优点： 实现了元数据的共享
2.Hive DDL:数据定义语言
Hive 语句注意事项：
- SQL 语言大小写不敏感。
- SQL 可以写在一行或者多行
- 关键字不能被缩写也不能分行
- 各子句一般要分行写
- 使用缩进提高语句的可读性
- – 为注释符号
2.1 创建删除数据库
- 创建数据库
#CREATE DATABASE [IF NOT EXISTS] 数据库名;
create database lagou;
- 查看数据库
SHOW DATABASES;
- 删除数据库
- 删除空数据库 drop database 数据库名;
--drop database 数据库名;
drop database lagou;
--查看所有数据库
show databases;
- 如果删除的数据库不存在，最好采用 if exists判断数据库是否存在
--drop database if exists 数据库名;
drop database if exists lagou;
- 如果数据库不为空，可以采用cascade命令，强制删除
--drop database 数据库名 cascade;
drop database lagou cascade;
- 使用进入数据库
--use 数据库名
use lagou;
2.2 创建表——数据类型
2.2.1 Hive 数据类型
数字类
日期时间类
字符串类
Misc类
复合类
2.2.2 Hive建表
内部表与外部表的区别
1.直接建表法
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]
- CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；
--创建测试表
create table test_Create_Table(id int,name string);
查看表的相关信息
--查看当前库表名
show tables;
--查看建表信息
show create table test_Create_Table;
--查看表结构
desc test_Create_Table;
- IF NOT EXIST 忽略同名表的异常问题
create table if not exists test_Create_Table(id int,name string);
- EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
--不指定路径，同内表路径一致
create external table test_External_Table(id int,name string);
--指定路径创建外部表（要求为新建路径)
create external table test_External_Location(id int, name string) location
'/lagou_Exteranl';
- LIKE 允许用户复制现有的表结构，但是不复制数据
create table test_like like test_create_table;
- COMMENT可以为表与字段增加描述
create table table_Comment(id int comment '编号',name string comment '姓名') comment '员工信息表';
- PARTITIONED BY 指定分区
- ROW FORMAT
- STORED AS
| SEQUENCEFILE | 序列化文件 |
|---|---|
| TEXTFILE | 普通的文本文件格式 |
| RCFILE | 行列存储相结合的文件 |
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname | 自定义文件格式 |
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
- LOCATION指定表在HDFS的存储路径
- CLUSTERED表示的是按照某列聚类，例如在插入数据中有两项“张三，数学”和“张三，英语”
若是CLUSTERED BY name，则只会有一项，“张三，(数学，英语)”，这个机制也是为了加快查询的操作。
2.查询建表法
create [EXTERNAL] table [IF NOT EXISTS] 表名 as select 语句;
create table NewTableBySelect as select * from test_create_table;
- 使用查询创建并填充表，select 中选取的列名会作为新表的列名（所以通常是要取别名），会改变表的属性、结构，比如只能是内部表、分区分桶也没了
- 目标表不允许使用分区分桶的；对于旧表中的分区字段，如果通过 select * 的方式，新表会把它看作一个新的字段，这里要注意 ；
- 目标表不允许使用外部表，会报错
- 创建的表存储格式会变成默认的格式 TEXTFILE ,可以指定表的存储格式，行和列的分隔符等。
3.like建表法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name LIke old_talble_name
- 用来复制表的结构
- 需要外部表的话，通过 create external table like … 指定
- 不填充数据
2.3 创建表——分隔符
特殊分隔符
分隔符类型
字段分隔符
语法： fields terminated by ‘\t’ （hive 默认的字段分隔符为ascii码的控制符 \001 ctrl +V+A）
--设置字段分隔符
create table test_delimit(id int, name string) row format delimited fields
terminated by '^B';
--查看字段分隔符
desc formatted test_delimit;
array 类型成员分隔符
语法：collection items terminated by ‘,’
--指定 |为字段分隔符 ，为数组分隔符
create table sales_info(
sku_id string comment '商品id',
sku_name string comment '商品名称',
id_array array<string> comment '商品相关id列表')
row format delimited fields terminated by '|'
collection items terminated by ',' ;
--vim 创建本地文件data1.txt
123|华为Mate10|1235,345
456|华为Mate30|89,635
789|小米5|452,63
1235|小米6|785,36
4562|OPPO Findx|7875,3563
--装载本地文件到 sales_info中
load data local inpath '/home/hadoop/datas/data1.txt' overwrite into table
sales_info;
--通过查询语句查看数据
hive>select * from sales_info;
OK
123 华为Mate10 ["1235","345"]
456 华为Mate30 ["89","635"]
789 小米5 ["452","63"]
1235 小米6 ["785","36"]
4562 OPPO Findx ["7875","3563"]
map 的 Key 和 Value 之间的分隔符
语法：map keys terminated by ‘:’
create table mapkeys1(
sku_id string comment '商品id',
sku_name string comment '商品名称',
state_map map<string,string> comment '商品状态信息')
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':';
--创建还有map类型的数据表
create table mapkeys(
sku_id string comment '商品id',
sku_name string comment '商品名称',
state_map map<string,string> comment '商品状态信息')
row format delimited
fields terminated by '|'
map keys terminated by ':';
--liunx 本地创建 data2.txt 文件
123|华为Mate10|id:1111,token:2222,user_name:zhangsan1
456|华为Mate30|id:1113,token:2224,user_name:zhangsan3
789|小米5|id:1114,token:2225,user_name:zhangsan4
1235|小米6|id:1115,token:2226,user_name:zhangsan5
4562|OPPO Findx|id:1116,token:2227,user_name:zhangsan6
--将本地data2.txt数据加载到mapkeys表
load data local inpath '/home/hadoop/datas/data2.txt' overwrite into table
mapkeys;
--查看表内数据是否与文件一致
hive> select * from mapkeys;
OK
123 华为Mate10 {"id":"1111,token:2222,user_name:zhangsan1"}
456 华为Mate30 {"id":"1113,token:2224,user_name:zhangsan3"}
789 小米5 {"id":"1114,token:2225,user_name:zhangsan4"}
1235 小米6 {"id":"1115,token:2226,user_name:zhangsan5"}
4562 OPPO Findx {"id":"1116,token:2227,user_name:zhangsan6"}
行分隔符
（必须放在最后，目前不支持其他分隔符 仅支持"\n"）
语法：lines terminated by ‘\n’
使用多字符作为分隔符
- 使用MultiDelimitSerDe的方法来实现
--创建多分割符表
CREATE TABLE test_MultiDelimit(id int, name string ,tel string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="##")
STORED AS TEXTFILE;
--查看表
desc formatted test_MultiDelimit;
- 使用RegexSerDe的方法实现：
--RegexSerDe 仅支持字符串类型的，不能有其他类型
CREATE TABLE test_RegexSerDe(id ints, name string ,tel string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "^(.*)\\#\\#(.*)$")
STORED AS TEXTFILE;
错误提示
--RegexSerDe 仅支持字符串类型的，不能有其他类型
CREATE TABLE test1(id string, name string ,tel string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "^(.*)\\#\\#(.*)$")
STORED AS TEXTFILE;
#建表结果查看
desc formatted test1;
2.4 创建表——分区表创建
2.4.1 分区表技术与意义
- 避免hive全表扫描，提升查询效率。
从而引进分区技术，使用分区技术，避免hive全表扫描，提升查询效率。 - 减少数据冗余进而提高特定(指定分区)查询分析的效率。
- 在逻辑上分区表与未分区表没有区别，在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名为“分区键=键值”。
- 查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。
2.4.2 分区表类型
静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断
- 静态分区
- 动态分区
2.4.3 建立分区
静态分区和动态分区的建表语句是一样的
-- 创建分区表 PARTITIONED BY (分区字段名 分区字段类型)
create table test_partition1(
sku_id string comment '商品id',
sku_name string comment '商品名称')
PARTITIONED BY (sku_class string);
--建立分区表之后，此时没有数据，也没有分区，需要建立分区
--创建分区
alter table test_partition1 add partition(sku_class='xiaomi')
--查看表现有分区
show partitions test_partition1
--静态分区添加数据
insert into table test_partition1 partition(sku_class='xiaomi')
values('001','xiaomi1');
insert into table test_partition1 partition(sku_class='xiaomi') select
sku_id,sku_name from sales_info;
--本地文件加载
load data local inpath '本地文件路径' into table test_partition1 partition
(sku_class='xiaomi');
--查询数据
--严格模式下（对于分区表，必须添加where对于分区字段的条件过滤）
set hive.mapred.mode=strict;
select * from test_partition1 where sku_class='xiaomi'
--通过设置以下参数开启非严格模式
set hive.mapred.mode=nonstrict;
select * from test_partition1;
--动态分区添加数据
insert into table test_partition1 partition(sku_class) values
('001','xiaomi2','xiaomi');
动态分区默认不开启，执行上面的语句会报错、
--修改一下hive的默认设置以支持动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
--动态分区插入数据
insert into table test_partition1 partition(sku_class) values
('001','xiaomi2','xiaomi');
--查看插入后数据
select * from test_partition1
通过多字段分区
-- 创建分区表 PARTITIONED BY (分区字段名 分区字段类型,分区字段名2 分区字段类型2) 多字段分
区
create table test_partition_mul(
sku_id string comment '商品id',
sku_name string comment '商品名称')
PARTITIONED BY (sku_class string,sku_lable string);
--添加分区
alter table test_partition_mul add IF NOT EXISTS
partition(sku_class='xiaomi',sku_lable='dianzi')
--查看现有分区
show partitions test_partition_mul;
--静态分区添加数据
insert into table test_partition_mul
partition(sku_class='xiaomi',sku_lable='dianzi') values('001','xiaomi1');
insert into table test_partition_mul
partition(sku_class='xiaomi',sku_lable='dianzi') select sku_id,sku_name from
sales_info;
--加载本地文件到分区表中
load data local inpath '本地文件路径' into table test_partition partition
(sku_class='xiaomi',sku_lable='dianzi');
#动态分区插入数据
insert into table test_partition_mul partition(sku_class,sku_lable) values
('001','xiaomi2','xiaomi','dianzi');
#查看插入后数据
select * from test_partition_mul;
select *,renshu/sum(renshu) over() from (
select sex,count(distincct user_id)，sum(sale) from t1
group by sex
) as a
1 20 200 20/30
0 10 100 10/30
2.4.3 删除分区 drop
alter table 表名 drop partition(分区字段名=取值);
alter table test_partition1 drop partition(sku_class='xiaomi');
2.2.5 创建表——分桶表创建
create table 表名(字段1 类型1，字段2，类型2 ) clustered by(表内字段) sorted by(表内字段) into 分桶数 buckets
--创建分桶表
create table test_buckets(
sku_id string comment '商品id',
sku_name string comment '商品名称')
clustered by(sku_id) into 3 buckets;
--设置自动分桶开关
set hive.enforce.bucketing=true;
--为分桶表添加数据
insert into test_buckets select sku_id,sku_name from sales_info;
2.2.6 电商下单业务流程数据库搭建
把 shop_create.sql 上传到虚拟机 或者 在虚拟机上创建本文件
#此语句在liunx 下非 hive 环境下运行
hive -f 'SQL文件路径'
2.2.7 学员线上学习业务流程数据库搭建
把 online_edu_create.sql上传到虚拟机 或者 在虚拟机上创建本文件
#此语句在liunx 下非 hive 环境下运行
hive -f '/home/hadoop/datas/online_edu_create.sql'
2.3 Hive DML: 数据操作语言
2.3.1 装载数据
从本地装载数据
- 普通表：
load data local inpath ‘数据文件路径’ [overwrite] into table 表名 ;
overwrite 关键字表示覆盖原有数据,没有此关键字表示添加数据
load data local inpath '/home/hadoop/datas/payments.txt' into table payments;
- 分区表：
load data local inpath ‘数据文件路径’ [overwrite] into table 表名 partition
(分区字段=值);
load data local inpath '/home/hadoop/datas/product_category_level1.txt' into
table product_category partition(level=1);
- 分桶表：
load data local inpath ‘数据文件路径’ [overwrite] into table 表名;
--开启分桶功能
set hive.enforce.bucketing=true
-- 忽略掉安全检查
hive.strict.checks.bucketing=false;
load data local inpath '/home/hadoop/datas/shop.txt' into table shops;
从HDFS装载数据
- liunx 本地文件上传到HDFS文件系统
hdfs dfs -put 本地文件路径 hdfs路径
#文件上传
hdfs dfs -put '/home/hadoop/datas/product_info.txt' '/datas'
#命令查看文件目录
hdfs dfs -ls /datas
- 文件数据装载到hive表中
load data inpath ‘hdfs数据文件路径’ into table 表名;
load data inpath '/datas/product_info.txt' into table product_info;
2.3.2 插入数据(values,select)
- 普通表
insert into 表名 values(值)
insert overwrite|into 【table】 表名 values(值)
注意 insert overwrite 时 table 关键字不可省略
--插入数据
insert into sales_info(sku_id,sku_name) values(1,'sku_new');
--查看插入的数据
insert into sales_info(sku_id,sku_name) select sku_id,sku_name from sales_info;
- 分区表
insert into 表名 partition (分区字段=字段值) values()
--查看分区情况
show partitions test_partition1;
desc test_partition1;
--静态分区
insert into test_partition1 partition(sku_class="xiaomi") values(1,'sku_new');
--动态分区
insert into test_partition1 partition(sku_class) values(1,'sku_new','苹果');
- 分桶表
insert into 分桶表表名 select * from 中间表
#查看表定义语句
show create table test_buckets;
desc test_buckets;
#插入数据
insert into test_buckets values(1,'sku_new');
# 查看插入的中间数据
select * from test_buckets;
2.3.3 导出数据
导出到本地文件系统
INSERT OVERWRITE LOCAL DIRECTORY ‘文件夹路径’ ROW FORMAT DELIMITED FIELDS TERMINATED by ‘字段分隔符’ 查询语句;
注意： OVERWRITE 把指定的文件夹重写了 （一定要小心覆盖掉有用的文件）
默认分割符是用系统指定的，和本身建表语句指定的没有关系
有新建文件夹功能，
导出的文件都名都为000000_0
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/datas/test_output' ROW FORMAT
DELIMITED FIELDS TERMINATED by '\t'
select * from sales_info;
导出到HDFS
INSERT OVERWRITE DIRECTORY ‘文件夹路径’ 查询语句;
INSERT OVERWRITE DIRECTORY '/datas' select * from sales_info;
2.3.4 删除表
删除所有数据
使用truncate仅可删除内部表数据，不可删除表结构
--删除数据
truncate table byselect;
--查看表
select * from byselect;
使用shell命令删除外部表数据(hdfs dfs -rm -r 外部表路径)删除
hdfs dfs -rm -r /datas/test_Exteranl/*
删除表部分数据
- 有partition表
- 删除具体partition
alter table table_name drop partition(partiton_name=‘value’))
--查看数据
select * from test_partition1;
show partitions test_partition1;
--删除指定分区
alter table test_partition1 drop partition(sku_class = 'xiaomi');
- 删除partition内的部分信息(INSERT OVERWRITE TABLE）
INSERT OVERWRITE TABLE test_partition_mul
PARTITION(sku_class='xiaomi',sku_lable='dianzi')
SELECT sku_id,sku_name FROM test_partition_mul
WHERE sku_id='1235';
重新把对应的partition信息写一遍，通过WHERE 来限定需要留下的信息，没有留下的信息就被删除了。
- 无partiton表
INSERT OVERWRITE TABLE 表名 SELECT * FROM 表名 WHERE 条件;
--插入测试数据
insert into sales_info(sku_id,sku_name) values(1,'a');
--删除不为2的数据
Insert overwrite table sales_info(sku_id,sku_name) select sku_id,sku_name
from sales_info where sku_id='2';
- 删除整个表
使用 drop 可删除整个表 (drop table 表名)
drop table test_external;
2.3.5 模拟电商下单流程进行数据入库
hive -f '/home/hadoop/datas/loadData_shop.sql';
2.3.6 模拟学员线上学习流程进行数据入库
hive -f '/home/hadoop/datas/loadData_shop.sql';
扫一扫
2641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
