Apache hive
数据仓库:英文名称为Data Warehouse,可简写为DW或DWH。数据仓库,是为企业所有级别的决策制定过程,提供所有类型数据支持的战略集合。它是单个数据存储,出于分析性报告和决策支持目的而创建。 为需要业务智能的企业,提供指导业务流程改进监视时间、成本、质量以及控制。
Apache Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的类sql查询功能,可以将sql语句转换为MapReduce任务进行运行。其优点是学习成本低,可以通过类SQL语句快速实现简单的MapReduce统计,不必开发专门的MapReduce应用,十分适合数据仓库的统计分析。
Hive是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具,可以用来进行数据提取转化加载(ETL),这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。Hive定义了简单的类 SQL查询语言,称为 HQL,它允许熟悉 SQL 的用户查询数据。同时,这个语言也允许熟悉MapReduce 开发者的开发自定义的 mapper 和 reducer 来处理内建的 mapper 和 reducer 无法完成的复杂的分析工作。Hive 没有专门的数据格式。 Hive 可以很好的工作在 Thrift 之上,控制分隔符,也允许用户指定数据格式。
Note
ETL:大数据中的一个专业术语
- E:Extract(抽取)
- T:Transfer (转换)
- L:Load(加载)
ETL指的是从数据源到数据仓库的处理过程,E:将数据源中的数据按照一些规则提取出来关键的某些数据,T:将数据做一些简单的格式转换,存放在数据仓库的临时表中,L:将临时表中的数据按照业务需求装在到数据仓库的业务表中;
使用场景
Hive构建基于静态批处理的hadoop之上,而Hadoop通常有较高的延迟并且在作业提交和调度的时候需要大量的开销。因此,Hive并不能够在大规模数据集上实现低延迟快速查询,例如,Hive在几百MB的数据集上执行查询一般有分钟级的时间延迟。Hive不适合那些需要低延迟的应用,联机事务处理 OLTP。Hive在查询操作过程严格遵守Hadoop MapReduce的作业执行模型,Hive将用户的HiveQL 语句通过解释器转换为MapReduce 作业提交到Hadoop集群上,Hadoop监控作业执行过程,然后返回作业执行结果给用户。
Hive并非为联机事务处理而设计
Hive并不提供实时的查询和基于行级的数据更新操作。
Hive的最佳使用场合是大数据集的批处理作业,例如,网络日志分析。
特点
Hive 是一种底层封装了Hadoop 的数据仓库处理工具,使用类SQL 的HiveQL 语言实现数据查询,所有Hive 的数据都存储在Hadoop 兼容的文件系统例如(HDFS)
Hive 在加载数据过程中不会对数据进行任何的修改,只是将数据移动到HDFS 中Hive 设定的目录下,因此,Hive 不支持对数据的改写和添加,所有的数据都是在加载的时候确定的。
- 支持索引,加快数据查询
- 不同的存储类型,例如,纯文本文件、HBase 中的文件。
- 将元数据保存在关系数据库中,大大减少了在查询过程中执行语义检查的时间。
- 可以直接使用存储在Hadoop 文件系统中的数据。
- 内置大量用户函数UDF 来操作时间、字符串和其他的数据挖掘工具,支持用户扩展UDF 函数来完成内置函数无法实现的操作。
- 类SQL 的查询方式,将SQL 查询转换为MapReduce 的job 在Hadoop集群上执行。
数据类型
首先Hive没有专门的数据存储格式,也没有为数据建立索引,用户可以非常自由的组织 Hive 中的表,只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符,Hive 就可以解析数据。其次Hive 中所有的数据都存储在 HDFS 中,Hive 中包含以下数据模型:表(Table,也称为内部表),外部表(External Table),分区(Partition),分桶表(Bucket)
环境搭建
环境准备
- 使用Mysql DB(Hive 使用关系型数据库存放元数据,减少语义检查语句,需要开启Mysql的远程访问支持)
这一步其实不是必须的,因为Hive默认的metadata(元数据)是存储在Derby里面的,但是有一个弊端就是同一时间只能有一个Hive实例访问,这适合做开发程序时做本地测试。
- Hadoop 服务健康(HDFS & Yarn)
Hive HQL 语句经过解释器解释后最终执行的还是MapReduce任务
# 启动HDFS集群
start-dfs.sh || start-all.sh
# 启动start-yarn.sh
start-yarn.sh
- 使用JDK1.8以上
安装
- 上传安装包
- 解压安装
tar -zxf filename
- 配置 hive-site.xml
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<!-- 数据库连接地址 -->
<value>jdbc:mysql://192.168.197.1:3306/hive</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>1234</value>
</property>
</configuration>
Mysql中的Hive数据库需要使用拉丁字符
在Linux中ping数据库看是否ping通
长时间连接不上修改数据库的字符编码
- 替换Hadoop jline的低版本jar包
cp /usr/apache-hive-1.2.1-bin/lib/jline-2.12.jar /home/hadoop/hadoop-2.6.0/share/hadoop/yarn/lib/
# 删除旧版本
rm -rf /home/hadoop/hadoop-2.6.0/share/hadoop/yarn/lib/jline-0.9.94.jar
启动Hive服务
- 单用户访问
在一个服务窗口,同时启动Hive Server 和 Hive Client;只能允许当前的Hive Client操作Hive Server
cd /usr/apache-hive-1.2.1-bin/
# 启动服务
bin/hive
- 多用访问
首先启动HiveServer,可以在另外窗口启动多个Hive Client操作
# 启动客户端
bin/hiveserver2
# 远程访问客户端
bin/beeline -u jdbc:hive2://localhost:10000
注意:
- 启动Hive Server后会在Mysql中创建29张和元数据相关的表
- Hive会在HDFS中创建数据仓库目录,用以存放数据
数据库和表相关操作
Hive HQL的语法和SQL及其类似
创建数据库
完整语法
CREATE (DATABASE|SCHEMA `二选一` ) [IF NOT EXISTS `可省略`] database_name
[COMMENT database_comment]
[LOCATION hdfs_path] `创建的目录`
[WITH DBPROPERTIES (property_name=property_value, ...)];
写法:
# 第一种写法
create database if not exists netzhuo;
自动在hdfs 创建数据库的数据存放目录:/user/hive/warehouse/netzhuo.db
# 第二种写法
create database test3;
# 第三种完整写法
create database if not exists test2 comment 'test2 database' location '/user/test2' with dbproperties('author'='xiaguo','company'='netzhuo');
删除数据库
完整语法
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
默认是:RESTRICT 不允许删除数据库有表的库
Cascade 删除数据库时级联删除表
写法:
# 删除test3 数据库中有表不允许删除 默认
drop schema if exists test3 restrict;
# 删除数据库 同时删除数据库中的表
drop database test2 cascade;
查看数据库
完整语法
(DESC|DESCRIBE) (DATABASE|SCHEMA) database_name ;
写法:
desc database netzhuo;
修改数据库
完整语法
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
写法:
# 修改数据库的所有者为root用户
alter database netzhuo set owner user root;
切换数据库
完整语法
# 查看当前使用的数据库
select current_database();
# 切换使用的数据库 同mysql 中的use database;
use netzhuo;
展示数据库列表
完整语法
show databases;
展示数据库中的表
show tables from databasename;
Hive 表中的数据类型
数据类型(primitive,array,map,struct)
- Primitive(原始类型)
- 整数:TINYINT、SMALLINT、INT、BIGINT(等价于Byte、short、Int、Long存值范围)
- 布尔:BOOLEAN
- 小数:FLOAT、DOUBLE
- 字符:STRING、CHAR、VARCHAR
- 二进制:BINARY
- 时间类型:TIMESTAMP、DATE
- Array(数据类型):Array<data_type>
- Map(Key-value):MAP<primitive_type,data_type>
- struct(结构体类型):STRUCT<col_name:dataz_type,…>
Hive默认使用的分隔符
分隔符 | 描述 |
---|---|
\n | 对于文本来说,每一行都是一条记录。因此\n可以分割记录。 |
^A(Ctrl+a) | 用于分割字段(列),在create table中可以使用\001表示。 |
^B(Ctrl+b) | 用于分割array或者是struct中 的元素或者用于map结构中的k-v对的分隔符,在create table中可以使用\002表示。 |
^C(Ctrl+c) | 用于Map中k-v的分隔符,在create table中可以使用\003表示。 |
分隔符在vi模式下,使用Ctrl +v
+ Ctrl + A|B|C
Hive表的使用
创建表
完整语法
类似于DB的创建表的语法
create table t_user(id int, name varchar(50),sex boolean,birthday date);
装载数据
#1.准备数据文件,按照hive表的格式要求 准备数据 (^A 是使用vi编辑器 ctrl + v ctrl + A )
1^Azs^Atrue^A2018-01-01
2^Als^Afalse^A1998-07-07
#2. hive指令将数据文件的内容装在到Hive table中[本地文件系统]
load data local inpth 'path/filename' into table t_user;
# 查看表中的数据
select * from t_user;
#3.hive指令将数据文件的内容追加加载到Hive Table中[HDFS 文件系统]
# 执行完成后t_user.txt 文件会被copy到数据库目录下并将原目录下文件删除
load data inpth 'hdfs://HadoopNode00:9000/t_user.txt' into table t_user;
#4. hive指令将数据文件的内容覆盖装载到Hive Table中 [HDFS文件系统] overwrite
load data inpath 'hdfs://HadoopNode00:9000/t_user.txt' overwrite into table t_user;
总结:
- hive默认创建的表示一个内部表,数据文件在装载时会移动拷贝到数据仓库表的存储目录
- hive表装载数据时,可以是本地文件系统(local)中数据或者HDFS
- hive表装载数据时,默认采用的是追加(append);如果需要覆盖表的原始内容,在需要在装载表的时候知道
overwrite
;
数组类型的使用
#1. 创建表
create table t_person(id int,name string,hobbies array<String>);
# 2. 准备数据文件
1^Azs^ATV^BLOL^BMUSIC
2^Als^ASPORT^BDrink
# 3.装载数据
load data local inpath '/usr/apache-hive-1.2.1-bin/data/t_person.txt' into table t_person;
select * from t_person;
结构化类型的使用
# 1. 创建表
create table t_location(id tinyint,name string,address struct<country:String,city:String>);
# 2. 准备数据文件
1^A三里屯^A中国^B北京朝阳
2^A五道口^A中国^B北京海淀
# 3. 装载数据
load data local inpath '/usr/apache-hive-1.2.1-bin/data/t_location.log' into table t_location;
select * from t_location;
注意:
struct type数据本质上由Json格式组织和管理;
Map类型的使用
# 1. 创建表
hive> create table t_product(id int,name varchar(50),tag map<String,String>);
# 2. 准备数据文件
1^Aiphone11^Amemory^C256GB^Bsize^C5.8
2^Ahuawei mate30^Asize^C6.1
# 3. 加载数据
hive> load data local inpath '/usr/apache-hive-1.2.1-bin/data/t_product.txt' into table t_product;
自定义分隔符
字段分隔符
# 1. 自定义字段的分隔符 列之间使用空格分割 行用换行符
hive> create table tt_user(id int,name varchar(32),sex boolean,birth date) row format delimited fields terminated by ' ' lines terminated by '\n';
# 2. 准备数据文件
1 zs true 2018-01-01
2 ls false 2020-01-02
3 ww false 2020-01-01
# 3. 装载数据时
hive> load data local inpath '/usr/apache-hive-1.2.1-bin/data/tt_user.txt' into table tt_user;
select * from tt_user;
数组分隔符
# 1. 自定义字段和集合元素的分隔符 列分隔符 空格 数组之间分隔符 >
hive> create table t_order(id int,name varchar(32),num int,price double,tags array<string>,user_id int)row format delimited fields terminated by ' ' collection items terminated by '>' lines terminated by '\n';
# 2. 准备数据文件
[root@HadoopNode00 data]# vi t_order.txt
1 iphone11 2 4999.0 贵>好用>香 101
2 huaweimate30 1 3999.0 国产>麒麟 102
# 3. 装载数据时
hive> load data local inpath '/usr/apache-hive-1.2.1-bin/data/t_order.txt' into table t_order;
Loading data to table netzhuo.t_order
Table netzhuo.t_order stats: [numFiles=1, totalSize=81]
OK
Time taken: 0.223 seconds
hive> select * from t_order;
OK
1 iphone11 2 4999.0 ["贵","好用","香"] 101
2 huaweimate30 1 3999.0 ["国产","麒麟"] 102
Time taken: 0.04 seconds, Fetched: 2 row(s)
map key-value 分隔符
map keys terminated by '分隔符'
基于正则表达式数据装载
# 1. 样例数据
192.168.197.1 - - [20/Dec/2019:22:12:42 +0800] "GET / HTTP/1.1" 200 612 "-" "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36"
192.168.197.1 - - [20/Dec/2019:22:12:42 +0800] "GET /favicon.ico HTTP/1.1" 404 571 "http://hadoopnode00/" "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36"
# 2. 正则表达式
^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}).*\[(.*)\]\s"(\w+)\s(.*)\sHTTP\/1.1"\s(\d{3})\s.*$
# 3. 实践 创建表并指定使用正则装载数据
hive> create table t_log(ip string,access_time string,method string,uri string,code smallint) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES("input.regex"="^(\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}).*\\[(.*)\\]\\s\"(\\w+)\\s(.*)\\sHTTP\\/1.1\"\\s(\\d{3})\\s.*$")
> ;
OK
Time taken: 0.085 seconds
#4. 装载数据
hive> load data local inpath '/usr/apache-hive-1.2.1-bin/data/nginx.log' into table t_log;
Loading data to table netzhuo.t_log
Table netzhuo.t_log stats: [numFiles=1, totalSize=416]
OK
Time taken: 0.195 seconds
#5. 查询数据
hive> select * from t_log;
OK
192.168.197.1 20/Dec/2019:22:12:42 +0800 GET / 200
192.168.197.1 20/Dec/2019:22:12:42 +0800 GET /favicon.ico 404
Time taken: 0.035 seconds, Fetched: 2 row(s)
基于Json文件数据装载
# 1. 源数据
[root@HadoopNode00 json]# vi user1.json
{"id":1,"name":"zs","sex":true,"birthday":"1998-12-12"}
{"id":2,"name":"ls","sex":true,"birthday":"1990-12-12"}
[root@HadoopNode00 json]# vi user2.json
{"id":3,"name":"ww","sex":false,"birthday":"1995-07-08"}
{"id":4,"name":"zl","sex":false}
# 2. 创建hive表 并指定使用json转换
hive> create table t_user_json(id int,name varchar(32),sex boolean,birthday date)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
# 2.1 抛异常 缺少jar
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot validate serde: org.apache.hive.hcatalog.data.JsonSerDe
# 2.2 add 对应的jar包
hive> ADD JAR /usr/apache-hive-1.2.1-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar ;
Added [/usr/apache-hive-1.2.1-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar] to class path
Added resources: [/usr/apache-hive-1.2.1-bin/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar]
# 再次创建表
hive> create table t_user_json(id int,name varchar(32),sex boolean,birthday date)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
OK
Time taken: 0.138 seconds
# 3. 数据装载
hive> load data local inpath '/usr/apache-hive-1.2.1-bin/data/json' overwrite into table t_user_json;
Loading data to table netzhuo.t_user_json
Moved: 'hdfs://HadoopNode00:9000/user/hive/warehouse/netzhuo.db/t_user_json/user1.json' to trash at: hdfs://HadoopNode00:9000/user/root/.Trash/Current
Moved: 'hdfs://HadoopNode00:9000/user/hive/warehouse/netzhuo.db/t_user_json/user2.json' to trash at: hdfs://HadoopNode00:9000/user/root/.Trash/Current
Table netzhuo.t_user_json stats: [numFiles=2, numRows=0, totalSize=202, rawDataSize=0]
OK
Time taken: 0.239 seconds
hive> select * from t_user_json;
OK
1 zs true 1998-12-12
2 ls true 1990-12-12
3 ww false 1995-07-08
4 zl false NULL
Hive表分类
在Hive表分为了管理表(内部表)、外部表、分区表 、分捅表、临时表(依赖于会话),在Hive客户端穿件一个临时表,在会话结束时候自动删除。
删除表
DROP TABLE [IF EXISTS] table_name [PURGE];
可选关键字
purge
,
- 添加则删除表的元数据+表中内容
- 不添加只删除表的元数据,而表中的内容会移动到HDFS的
.trash/current
垃圾数据存放目录;
管理(内部)表
管理表会控制数据的生命周期(表删除的时候会同时将元数据删除),不能进行多团队数据共享分析处理;
0: jdbc:hive2://localhost:10000> drop table t_location;
No rows affected (0.885 seconds)
0: jdbc:hive2://localhost:10000> drop table t_user_json;
No rows affected (0.15 seconds)
外部表
# 1. 创建外部表的语法
0: jdbc:hive2://localhost:10000> create external table t_user_json(id int,name varchar(32),sex boolean,birthday date)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';
No rows affected (0.294 seconds)
# 2. 装载数据
0: jdbc:hive2://localhost:10000> load data local inpath '/usr/apache-hive-1.2.1-bin/data/json' into table t_user_json;
INFO : Loading data to table netzhuo.t_user_json from file:/usr/apache-hive-1.2.1-bin/data/json
INFO : Table netzhuo.t_user_json stats: [numFiles=2, totalSize=202]
No rows affected (0.543 seconds)
0: jdbc:hive2://localhost:10000> drop table t_user_json purge;
No rows affected (0.139 seconds)
注意:
在删除外部表时,仅仅删除的是表的元数据(metadata),而不会删除外部表控制的数据;
临时表
临时表关键字:
temporary
生命周期依赖于会话
在当前会话中创建临时表 会话结束时候会将这个临时表进行删除
0: jdbc:hive2://localhost:10000> create temporary table ttt_user(id int,name string);
No rows affected (0.132 seconds)
0: jdbc:hive2://localhost:10000> show tables;
+------------+--+
| tab_name |
+------------+--+
| t_log |
| t_order |
| t_person |
| t_product |
| t_user |
| tt_user |
| ttt_user |
+------------+--+
7 rows selected (0.492 seconds)
分区表
外部表或者内部表都可以在创建时指定分区,这样就构成了分区表;分区就是数据分片思想,将一个大数据集按照规则划分为若干个小数据集,这样在进行数据加载或者处理时会有一个比较好的处理性能;优化策略
# 1. 创建分区表
0: jdbc:hive2://localhost:10000> create table ttt_user(id int,name varchar(32),sex boolean,birth date) partitioned by(country String,state String) row format delimited fields terminated by ' ' lines terminated by '\n';
No rows affected (0.087 seconds)
# 2. 准备数据
1 zs true 2020-01-01
2 ls false 1990-01-01
3 ww false 2001-01-01
# 3. 装载数据
0: jdbc:hive2://localhost:10000> load data local inpath '/usr/apache-hive-1.2.1-bin/data/ttt_user.txt' into table ttt_user partition(country='china',state='sh');
0: jdbc:hive2://localhost:10000> load data local inpath '/usr/apache-hive-1.2.1-bin/data/ttt_user.txt' into table ttt_user partition(country='china',state='bj');
# 4. 如何使用分区表
0: jdbc:hive2://localhost:10000> select * from ttt_user where country='china' and state='bj';
+--------------+----------------+---------------+-----------------+-------------------+-----------------+--+
| ttt_user.id | ttt_user.name | ttt_user.sex | ttt_user.birth | ttt_user.country | ttt_user.state |
+--------------+----------------+---------------+-----------------+-------------------+-----------------+--+
| 1 | zs | true | 2020-01-01 | china | bj |
| 2 | ls | false | 1990-01-01 | china | bj |
| 3 | ww | false | 2001-01-01 | china | bj |
+--------------+----------------+---------------+-----------------+-------------------+-----------------+--+
分区表:
- hive优化方案,按照分区查询时只需要加载分区内的数据,而不需要加载整个表的内容;
- 使用分区伪列+分区内容 进行数据加载
分桶表
分桶表指将数据集分解成容易组织管理若干个部分的技术;解决数据倾斜问题,已经大表和大表的JOIN,高效数据取样;
参考:关于Hive 桶表
分桶规则:对分桶字段值进行哈希,哈希值除以桶的个数求余,余数决定了该条记录在哪个桶中,也就是余数相同的在一个桶中。
优点:
1. 提高join查询效率
2. 提高抽样效率
# 1. 创建分桶表
0: jdbc:hive2://localhost:10000> create table t_bucket(id int,name string) clustered by (id) into 3 buckets;
No rows affected (0.141 seconds)
# 2. 注意 分桶表在装载数据时不能使用load
# 3. 特殊设置
# 强制使用分桶表
set hive.enforce.bucketing = true;
# 设置reducer 任务数量 = 桶的数量
set mapred.reduce.tasks = 3;
# 4. 临时表 首先将数据加载临时表中
create temporary table t_bucket_tmp(id int,name string);
load data local inpath '/usr/apache-hive-1.2.1-bin/data/bucketTmp.txt' into table t_bucket_tmp;
# 5. 将临时表中的数据转换到分桶表中
insert into t_bucket select * from t_bucket_tmp cluster by id;
分区表的其它操作
# 删除某个分区
0: jdbc:hive2://localhost:10000> alter table ttt_user drop partition(country='china',state='sh');
INFO : Dropped the partition country=china/state=sh
No rows affected (0.224 seconds)
# 添加分区
0: jdbc:hive2://localhost:10000> alter table ttt_user add partition(country='china',state='sh');
No rows affected (0.167 seconds)
# 查看表中的分区
0: jdbc:hive2://localhost:10000> show partitions ttt_user;
+-------------------------+--+
| partition |
+-------------------------+--+
| country=china/state=bj |
| country=china/state=sh |
+-------------------------+--+
2 rows selected (0.113 seconds)
截断表
# 查看表数据
0: jdbc:hive2://localhost:10000> select * from t_user;
+------------+--------------+-------------+------------------+--+
| t_user.id | t_user.name | t_user.sex | t_user.birthday |
+------------+--------------+-------------+------------------+--+
| 3 | zs | true | 2018-01-01 |
| 4 | ls | false | 1998-07-07 |
+------------+--------------+-------------+------------------+--+
2 rows selected (0.134 seconds)
# 表截断 清空表中的数据
0: jdbc:hive2://localhost:10000> truncate table t_user;
No rows affected (0.107 seconds)
0: jdbc:hive2://localhost:10000> select * from t_user;
+------------+--------------+-------------+------------------+--+
| t_user.id | t_user.name | t_user.sex | t_user.birthday |
+------------+--------------+-------------+------------------+--+
+------------+--------------+-------------+------------------+--+
HiveOnJDBC
导入Hive JDBC驱动
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.1.0</version>
</dependency>
Hive驱动类
org.apache.hive.jdbc.HiveDriver
应用程序
import java.sql.*;
/**
* Created with IntelliJ IDEA.
*
* @author XiaGuo
* @Description java API 连接hive
* 操作同 JDBC
* @create 2020-01-03 20:33
*/
public class HiveTest {
public static void main(String[] args) throws Exception {
Class.forName("org.apache.hive.jdbc.HiveDriver");
Connection connection = DriverManager.getConnection("jdbc:hive2://HadoopNode00:10000/t_test");
PreparedStatement statement = connection.prepareStatement("select * from ttt_user where country = ? and state = ? ");
statement.setString(1,"china");
statement.setString(2,"bj");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString("name");
boolean sex = resultSet.getBoolean("sex");
Date date = resultSet.getDate(4);
System.out.println(id + "\t" + name + "\t" + sex + "\t" + date);
}
}
}
Hive SQL 操作
HQL 操作类似SQL
# DB SQL查询语法
select 字段列表 from 表名 where 过滤条件 group by 分组字段 having 分组后过滤 order by 排序字段 asc | desc limit 限制结果的返回条数;
# 执行顺序
1. FROM : 对FROM左边的表和右边的表计算笛卡尔积,产生虚表VT1;
2. ON : 对虚拟表VT1进行ON筛选,只有那些符合条件的行才会被记录在虚拟表VT2中;
3. JOIN :如果是OUT JOIN,那么将保留表中(如左表或者右表)未匹配的行作为外部行添加到虚拟表VT2中,从而产生虚拟表VT3;
4. WHERE :对虚拟表VT3进行WHERE条件过滤,只有符合的记录才会被放入到虚拟表VT4;
5. GROUP BY:根据GROUP BY子句中的列,对虚拟表VT4进行分组操作,产生虚拟表VT5;
6. CUBE|ROLLUP:对虚拟表VT5进行CUBE或者ROLLUP操作,产生虚拟表VT6;
7. HAVING :对虚拟表VT6进行 HAVING 条件过滤,只有符合的记录才会被插入到虚拟表VT7中;
8. SELECT :执行SELECT操作,选择指定的列,插入到虚拟表VT8中;
9. DISTINCT :对虚拟表VT8中的记录进行去重,产生虚拟表VT9;
10. ORDER BY :将虚拟表VT9中的记录按照进行排序操作,产生虚拟表VT10;
11. LIMIT :取出指定行的记录,产生虚拟表VT11,并将结果返回。
HQL 完整语法
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list] # 计算结果全局有序(全局只有一个Reducer)
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list asc|desc]] # 分区键 id.hashCode% numReduceTask
[LIMIT number]
注意:
order by col_list asc|desc
: 全局排序,只有一个Reducer任务;DISTRIBUTE BY col_list
: shuffle进行分区时,分区键; 根据指定的字段值进行分区shuffleSORT BY col_list
: 对分区进行局部排序字段CLUSTER BY col_list
: 如果DISTRIBUTE BY col_list
+SORT BY col_list
, 简写写法;
# 1. 分组 + 分区后过滤
0: jdbc:hive2://localhost:10000> select sex,count(sex) from ttt_user where country='china' and state='bj' group by sex having sex= false;
# 2. 分组 + 结果集全局排序
0: jdbc:hive2://localhost:10000> select sex,count(sex) as num from ttt_user where country='china' and state='bj' group by sex order by num desc;
# 3. 分组 + cluster by使用
0: jdbc:hive2://localhost:10000> select sex,count(sex) as num from ttt_user where country='china' and state='bj' group by sex cluster by sex;
# 4. 分组 + distribute by + sort by
0: jdbc:hive2://localhost:10000> select sex,count(sex) as num from ttt_user where country='china' and state='bj' group by sex distribute by sex sort by sex desc;
# 5. limit使用
0: jdbc:hive2://localhost:10000> select sex,count(sex) as num from ttt_user where country='china' and state='bj' group by sex distribute by sex sort by sex desc limit 1;
表连接查询
内连接([inner] join)
左表和右表符合条件的数据进行连接操作,合为一张大表;
# 员工数据
1,zs,true,18,A
2,ls,false,20,B
3,ww,false,25,A
4,zl,false,30,B
5,tq,true,21,C
# 部门数据
A,研发部
B,市场部
C,销售部
D,后勤部
# 创建 t_employee
0: jdbc:hive2://localhost:10000> create table t_employee(id int,name varchar(32),sex boolean,age tinyint,dept string) row format delimited fields terminated by ',' lines terminated by '\n';
No rows affected (0.11 seconds)
# 装载 t_employee
0: jdbc:hive2://localhost:10000> load data local inpath '/usr/apache-hive-1.2.1-bin/data/employee.txt' into table t_employee;
0: jdbc:hive2://localhost:10000> select * from t_employee;
+----------------+------------------+-----------------+-----------------+------------------+--+
| t_employee.id | t_employee.name | t_employee.sex | t_employee.age | t_employee.dept |
+----------------+------------------+-----------------+-----------------+------------------+--+
| 1 | zs | true | 18 | A |
| 2 | ls | false | 20 | B |
| 3 | ww | false | 25 | A |
| 4 | zl | false | 30 | B |
| 5 | tq | true | 21 | C |
+----------------+------------------+-----------------+-----------------+------------------+--+
# 创建 t_dept
0: jdbc:hive2://localhost:10000> create table t_dept(deptId string,name string) row format delimited fields terminated by ',' lines terminated by '\n';
No rows affected (0.094 seconds)
# 装载 t_dept
0: jdbc:hive2://localhost:10000> load data local inpath '/usr/apache-hive-1.2.1-bin/data/dept.txt' into table t_dept;
0: jdbc:hive2://localhost:10000> select * from t_dept;
+----------------+--------------+--+
| t_dept.deptid | t_dept.name |
+----------------+--------------+--+
| A | 研发部 |
| B | 市场部 |
| C | 销售部 |
| D | 后勤部 |
+----------------+--------------+--+
# 内连接查询 展示on 连接两表都有的数据
0: jdbc:hive2://localhost:10000> select * from t_employee t1 inner join t_dept t2 on t1.dept = t2.deptId;
+--------+----------+---------+---------+----------+------------+----------+--+
| t1.id | t1.name | t1.sex | t1.age | t1.dept | t2.deptid | t2.name |
+--------+----------+---------+---------+----------+------------+----------+--+
| 1 | zs | true | 18 | A | A | 研发部 |
| 2 | ls | false | 20 | B | B | 市场部 |
| 3 | ww | false | 25 | A | A | 研发部 |
| 4 | zl | false | 30 | B | B | 市场部 |
| 5 | tq | true | 21 | C | C | 销售部 |
+--------+----------+---------+---------+----------+------------+----------+--+
外连接(left | right outer join)
# 左外连接 展示左表的全部数据 右表无数据为null
0: jdbc:hive2://localhost:10000> select * from t_employee t1 left outer join t_dept t2 on t1.dept = t2.deptId;
+--------+----------+---------+---------+----------+------------+----------+--+
| t1.id | t1.name | t1.sex | t1.age | t1.dept | t2.deptid | t2.name |
+--------+----------+---------+---------+----------+------------+----------+--+
| 1 | zs | true | 18 | A | A | 研发部 |
| 2 | ls | false | 20 | B | B | 市场部 |
| 3 | ww | false | 25 | A | A | 研发部 |
| 4 | zl | false | 30 | B | B | 市场部 |
| 5 | tq | true | 21 | C | C | 销售部 |
+--------+----------+---------+---------+----------+------------+----------+--+
# 右外连接 展示右表的全部数据 左表无数据为null
0: jdbc:hive2://localhost:10000> select * from t_employee t1 right outer join t_dept t2 on t1.dept = t2.deptId;
+--------+----------+---------+---------+----------+------------+----------+--+
| t1.id | t1.name | t1.sex | t1.age | t1.dept | t2.deptid | t2.name |
+--------+----------+---------+---------+----------+------------+----------+--+
| 1 | zs | true | 18 | A | A | 研发部 |
| 3 | ww | false | 25 | A | A | 研发部 |
| 2 | ls | false | 20 | B | B | 市场部 |
| 4 | zl | false | 30 | B | B | 市场部 |
| 5 | tq | true | 21 | C | C | 销售部 |
| NULL | NULL | NULL | NULL | NULL | D | 后勤部 |
+--------+----------+---------+---------+----------+------------+----------+--+
左半开连接(left semi join)
左半开连接会返回左表的数据,前提是记录需要满足右表on的判定条件;
# 子查询 类似 select * from user where id in (1,2,3);
0: jdbc:hive2://localhost:10000> select * from t_employee t1 left semi join t_dept t2 on t1.dept = t2.deptId;
+--------+----------+---------+---------+----------+--+
| t1.id | t1.name | t1.sex | t1.age | t1.dept |
+--------+----------+---------+---------+----------+--+
| 1 | zs | true | 18 | A |
| 2 | ls | false | 20 | B |
| 3 | ww | false | 25 | A |
| 4 | zl | false | 30 | B |
| 5 | tq | true | 21 | C |
+--------+----------+---------+---------+----------+--+
map-side join
map端连接,hive优化表连接查询方法(小表和大表Join);
将小表中的数据全部加载到大表的mapTask 内存中
注意:
- map端连接只适用于内连接和左外连接;
- hive 0.70版本之前,
select /*+mapjoin(小表别名)*/ .....
- hive 0.70版本之后,要求
set hive.auto.convert.join=true;
, 自动join优化,要求小表需要写在join关键字之前,因为Hive Join从左向右连接操作;
0: jdbc:hive2://localhost:10000> select /*+mapjoin(t2)*/ * from t_employee t1 left outer join t_dept t2 on t1.dept = t2.deptId;
0: jdbc:hive2://localhost:10000> set hive.auto.convert.join=true;
0: jdbc:hive2://localhost:10000> select * from t_dept t2 left outer join t_employee t1 on t1.dept = t2.deptId;
Full Outer Join
全外连接 左边右表符合条件结果进行连接,保留左表和右表不符合条件的结果 查询为null
笛卡尔乘积连接
左表和右表交叉连接 左表5条数据 右表6条数据,连接后会产生30条记录
七、Hive 和HBase整合
要求
- HDFS
- ZooKeeper
- HBase集群运行正常
准备HBase BigTable
hbase(main):002:0> create 'netzhuo2:t_user','cf1'
0 row(s) in 2.4760 seconds
hbase(main):001:0> put 'netzhuo2:t_user','user101','cf1:name','zs'
0 row(s) in 0.3800 seconds
hbase(main):002:0> put 'netzhuo2:t_user','user101','cf1:age',18
0 row(s) in 0.0180 seconds
hbase(main):003:0> put 'netzhuo2:t_user','user102','cf1:name','ls'
0 row(s) in 0.0060 seconds
hbase(main):004:0> put 'netzhuo2:t_user','user102','cf1:age',20
0 row(s) in 0.0180 seconds
hbase(main):005:0> scan 'netzhuo2:t_user'
ROW COLUMN+CELL
user101 column=cf1:age, timestamp=1578068239429, value=18
user101 column=cf1:name, timestamp=1578068227481, value=zs
user102 column=cf1:age, timestamp=1578068289077, value=20
user102 column=cf1:name, timestamp=1578068278698, value=ls
2 row(s) in 0.0420 seconds
创建Hive Table并关联HBase
# 创建出一个新的表同时绑定HBase中的另一个表进行相关联
create external table t_hbase_user(id string,name string,age int) stored by 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' with serdeproperties('hbase.columns.mapping'=':key,cf1:name,cf1:age') tblproperties('hbase.table.name'='netzhuo2:t_user');
0: jdbc:hive2://localhost:10000> select * from t_hbase_user;
+------------------+--------------------+-------------------+--+
| t_hbase_user.id | t_hbase_user.name | t_hbase_user.age |
+------------------+--------------------+-------------------+--+
| user101 | zs | 18 |
| user102 | ls | 20 |
+------------------+--------------------+-------------------+--+
2 rows selected (1.142 seconds)