Hive 快速入门

最新推荐文章于 2022-08-20 14:03:39 发布

夏夏夏果

最新推荐文章于 2022-08-20 14:03:39 发布

阅读量247

点赞数

分类专栏： Hive 文章标签： hive

本文链接：https://blog.csdn.net/qq_41797886/article/details/103827568

版权

Hive 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

logo

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进行分区时，分区键；根据指定的字段值进行分区shuffle
SORT 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 内存中
mad-side

注意：

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)