大数据开发-学习记录与要点思考-第六章 Hive

最新推荐文章于 2024-09-21 17:57:56 发布

thisisBenjamin

最新推荐文章于 2024-09-21 17:57:56 发布

阅读量186

点赞数

分类专栏：大数据文章标签：大数据 hive mysql

本文链接：https://blog.csdn.net/badassname11/article/details/114965879

版权

大数据专栏收录该内容

9 篇文章 1 订阅

订阅专栏

系列文章目录

第零章 Mac上虚拟机安装与配置
 第一章 Hadoop集群搭建
 第二章 HDFS
第三章 MapReduce
第四章 YARN
第五章 Flume - 尚未更新
第六章 Hive
第七章 Scala - 尚未更新
 第八章 Spark - 尚未更新
 第九章数据仓库 - 尚未更新
 第十章 Kafka - 尚未更新
 第十一章 Redis - 尚未更新
 第十二章 Flink - 尚未更新

一、Hive 介绍

1. 什么是Hive

建立在hadoop上的数据仓库基础构架。提供了一系列工具，用于ETL

有类SQL语言，HiveSQL，将SQL语句转译成MR任务；也支持自定义MR任务

2. Hive的数据存储

数据存储基于Hadoop的hdfs，没有专门的数据存储格式

可以直接加载文本文件，也支持SequenceFile等

3. Hive的系统架构

4. 元数据存储 Metastore

这里的metastore是指一个存储系统

是hive元数据的集中存放地

元数据包含表名、字段名、分区及其属性，表的属性，表的数据的目录路径等

二、数据库与数据仓库

1. Hive 和 Mysql

	HIVE	MySQL
数据存储位置	HDFS	本地磁盘
数据格式	用户定义	系统决定
数据更新	不支持(不支持修改和删除)	支持(支持增删改查)
索引	有，但较弱，一般很少用	有，经常使用的
执行	MapReduce	Executor
执行延迟	高	低
可扩展性	高	低
数据规模	大	小

2. OLTP 和 OLAP

OLTP(on-line transaction processing)

联机事务处理，一般针对具体业务，对用户的操作进行响应

OLAP(on-line analytical processing)

联机分析处理，一般针对历史数据进行分析，支持管理决策

3. Hive 安装部署

官方地址：https://archive.apache.org/dist/hive/

下载后，上传到集群的客户端节点，并解压

进入conf目录，修改配置文件

[root@bigdata04 soft]# cd apache-hive-3.1.2-bin/conf/
[root@bigdata04 conf]# mv hive-env.sh.template  hive-env.sh
[root@bigdata04 conf]# mv hive-default.xml.template  hive-site.xml

在hive-env.sh文件末尾，加入java、hive和hadoop的环境变量

export JAVA_HOME=/data/soft/jdk1.8
export HIVE_HOME=/data/soft/apache-hive-3.1.2-bin
export HADOOP_HOME=/data/soft/hadoop-3.2.0

修改hive-site.xml文件中如下各个参数的内容：

注意根据机器情况修改

[root@bigdata04 conf]# vi hive-site.xml
<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://{网关地址}:3306/hive?serverTimezone=Asia/Shanghai</value>
</property>
<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.cj.jdbc.Driver</value>
</property>
<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>{mysql_user_name}</value>
</property>
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>(mysql_password)</value>
</property>
<property>
    <name>hive.querylog.location</name>
    <value>/data/hive_repo/querylog</value>
</property>
<property>
    <name>hive.exec.local.scratchdir</name>
    <value>/data/hive_repo/scratchdir</value>
</property>
<property>
    <name>hive.downloaded.resources.dir</name>
    <value>/data/hive_repo/resources</value>
</property>

将mysql的java驱动包上传到hive的lib目录：

注意mysql版本和本地安装的版本一致

[root@bigdata04 lib]# ll 
........ 
-rw-r--r--. 1 root root 2293144 Mar 20  2019 mysql-connector-java-8.0.16.jar
.........

修改bigdata01中的core-site.xml文件，同步到集群中其他从节点上

[root@bigdata01 hadoop]# vi core-site.xml
    <property>
        <name>hadoop.proxyuser.root.hosts</name>
        <value>*</value>
    </property>
    <property>
        <name>hadoop.proxyuser.root.groups</name>
        <value>*</value>
</property>


[root@bigdata01 hadoop]# scp -rq etc/hadoop/core-site.xml  bigdata02:/data/soft/hadoop-3.2.0/etc/hadoop/
[root@bigdata01 hadoop]# scp -rq etc/hadoop/core-site.xml  bigdata03:/data/soft/hadoop-3.2.0/etc/hadoop/

重启hadoop集群

初始化hive的metastore

[root@bigdata04 apache-hive-3.1.2-bin]# bin/schematool -dbType mysql -initSchema
Exception in thread "main" java.lang.RuntimeException: com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8
 at [row,col,system-id]: [3215,96,"file:/data/soft/apache-hive-3.1.2-bin/conf/hive-site.xml"]
        at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:2981)
        at org.apache.hadoop.conf.Configuration.loadResources(Configuration.java:2930)
        at org.apache.hadoop.conf.Configuration.getProps(Configuration.java:2805)
        at org.apache.hadoop.conf.Configuration.get(Configuration.java:1459)
        at org.apache.hadoop.hive.conf.HiveConf.getVar(HiveConf.java:4996)
        at org.apache.hadoop.hive.conf.HiveConf.getVar(HiveConf.java:5069)
        at org.apache.hadoop.hive.conf.HiveConf.initialize(HiveConf.java:5156)
        at org.apache.hadoop.hive.conf.HiveConf.<init>(HiveConf.java:5104)
        at org.apache.hive.beeline.HiveSchemaTool.<init>(HiveSchemaTool.java:96)
        at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1473)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:323)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:236)
Caused by: com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8
 at [row,col,system-id]: [3215,96,"file:/data/soft/apache-hive-3.1.2-bin/conf/hive-site.xml"]
        at com.ctc.wstx.sr.StreamScanner.constructWfcException(StreamScanner.java:621)
        at com.ctc.wstx.sr.StreamScanner.throwParseError(StreamScanner.java:491)
        at com.ctc.wstx.sr.StreamScanner.reportIllegalChar(StreamScanner.java:2456)
        at com.ctc.wstx.sr.StreamScanner.validateChar(StreamScanner.java:2403)
        at com.ctc.wstx.sr.StreamScanner.resolveCharEnt(StreamScanner.java:2369)
        at com.ctc.wstx.sr.StreamScanner.fullyResolveEntity(StreamScanner.java:1515)
        at com.ctc.wstx.sr.BasicStreamReader.nextFromTree(BasicStreamReader.java:2828)
        at com.ctc.wstx.sr.BasicStreamReader.next(BasicStreamReader.java:1123)
        at org.apache.hadoop.conf.Configuration$Parser.parseNext(Configuration.java:3277)
        at org.apache.hadoop.conf.Configuration$Parser.parse(Configuration.java:3071)
        at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:2964)
        ... 15 more

报错，提示hive-stie.xml文件中第3215行内容有问题

需要删掉如下内容

  <property>
    <name>hive.txn.xlock.iow</name>
    <value>true</value>
    <description>
    </description>
  </property>

重新初始化：

bin/schematool -dbType mysql -initSchema

三、Hive 基础使用

使用前，先将hive的bin目录配置到path环境变量中，简化后续操作的命令

在/etc/profile文件中添加如下环境变量：

export HIVE_HOME=/data/soft/apache-hive-3.1.2-bin
export PATH=.:$HIVE_HOME/bin:$PATH

1. 使用方式

使用hive可以在shell命令行环境内，也可以用JDBC代码的方式

1.1 命令行方式

hive 命令：

在shell命令行模式下，执行hive脚本，打开hive命令行

[root@bigdata04 soft]# hive
which: no hbase in (.:/data/soft/jdk1.8/bin:/data/soft/hadoop-3.2.0/bin:/data/soft/hadoop-3.2.0/sbin:/data/soft/apache-hive-3.1.2-bin/bin:/data/soft/spark-3.0.0-bin-hadoop3.2/bin:.:/data/soft/scala-2.12.11/bin:.:/data/soft/jdk1.8/bin:/data/soft/hadoop-3.2.0/bin:/data/soft/hadoop-3.2.0/sbin:/data/soft/apache-hive-3.1.2-bin/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
Hive Session ID = b0c4c4c9-6e9d-4fa1-b389-4b05ba1be786

Logging initialized using configuration in file:/data/soft/apache-hive-3.1.2-bin/conf/hive-log4j2.properties Async: true
Hive Session ID = df61c22c-cdb0-4cbf-acd5-76faa0cfc04f
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.

hive (default)>

如果没有安装hbase会提示没有相关环境

以及提示Hive-on-MR的MR引擎在Hive 2和之后的版本中可能不再维护，建议使用其他计算引擎(spar，tez)

beeline

先启动hiveserver2服务，再用beeline客户端通过这个服务连接hive

启动hiveserver2服务之后，需要等其输出4行Hive Session ID信息后，再启动beeline，否则会提示拒绝连接

[root@bigdata04 soft]# hiveserver2
which: no hbase in (.:/data/soft/jdk1.8/bin:/data/soft/hadoop-3.2.0/bin:/data/soft/hadoop-3.2.0/sbin:/data/soft/apache-hive-3.1.2-bin/bin:/data/soft/spark-3.0.0-bin-hadoop3.2/bin:.:/data/soft/scala-2.12.11/bin:.:/data/soft/jdk1.8/bin:/data/soft/hadoop-3.2.0/bin:/data/soft/hadoop-3.2.0/sbin:/data/soft/apache-hive-3.1.2-bin/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
2021-05-02 12:19:14: Starting HiveServer2
Hive Session ID = 5c493a0f-35b0-4831-ae12-d53e66f6157f
Hive Session ID = 02d67cbb-5ee4-4a51-81c4-49738c44918f
Hive Session ID = 5b118a7d-f54e-41ca-8c0a-00e99951a039
Hive Session ID = f5af44cb-6c2d-4a09-a88b-0f656cfb372f

启动beeline连接hive: beeline -u jdbc:hive2://localhost:10000

[root@bigdata04 hivedata]# beeline -u jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost: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://localhost:10000>

其中localhost表示本地，也可以指定该机器的内网ip：
beeline -u jdbc:hive2://192.168.116.103:10000

这样，能和此机器通信的其他集群机器，也可以操作hive

如果在往表中插入数据时，遇到用户权限问题，可以退出beeline，再指定有操作权限的用户启动

``bin/beeline -u jdbc:hive2://localhost:10000 -u root`

1. 2 JDBC方式

创建maven项目，在pom文件中添加hive-jdbc依赖：

<!-- hive-jdbc驱动 -->
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>3.1.2</version>
</dependency>

编写代码、执行

2. Set命令的使用

set命令用于临时设置hive-site.xml文件中的参数的值，只在当前会话有效

配置到~/.hiverc文件中，则是当前用户有效

修改hive-site.xml文件则是永久有效

例如，可以设置如下两个参数：

set hive.cli.print.current.db = true;
set hive.cli.print.header = true;

分别是显示当前数据库名称 和 显示字段名称

3. Hive的日志配置

修改日志级别以及日志存储路径, 让hive命令行界面保持整洁

cd 到 conf目录，将hive-log4j.properties.template文件重命名为hive-log4j.properties，修改里面的property.hive.log.level和property.hive.dir参数

[root@bigdata04 conf]# mv hive-log4j2.properties.template  hive-log4j2.properties
[root@bigdata04 conf]# vi hive-log4j2.properties
property.hive.log.level = WARN
property.hive.root.logger = DRFA
property.hive.log.dir = /data/hive_repo/log
property.hive.log.file = hive.log
property.hive.perflogger.log.level = INFO

修改hive的日志执行文件

hive-exec-log4j2.properties.template文件重命名为hive-exec-log4j2.properties

[root@bigdata04 conf]# mv hive-exec-log4j2.properties.template hive-exec-log4j2.properties
[root@bigdata04 conf]# vi hive-exec-log4j2.properties
property.hive.log.level = WARN
property.hive.root.logger = FA
property.hive.query.id = hadoop
property.hive.log.dir = /data/hive_repo/log
property.hive.log.file = ${sys:hive.query.id}.log

重启hive，日志存储在/data/hive_repo/log目录内

四、Hive 核心要点

1. 数据库的操作

查看数据库

SHOW DATABASES;

选择数据库

USE database_name;

USE default, default数据库存储在HDFS上，具体目录路径，由conf/hive-site.html文件中的hive.metastore.warehouse.dir参数指定

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
  </property>

其默认值是：/user/hive/warehouse

也可以在9870端口界面查看

在这里插入图片描述

创建数据库

CREATE DATABASE database_name;

也可以指定数据库在HDFS目录的位置

CREATE DATABASE database_name LOCATION 'user/hive/database_name';

删除数据库

DROP DATABASE database_name;

2. 表的操作

创建表

CREATE TABLE t2(
	id INT
);

t2是表名称

查看表结构信息

DESC t2;

查看表的创建信息

SHOW CREATE TABLE t2;

修改表名

ALTER TABLE t2 RENAME TO t2_bak;

t2_bak是重命名后的表名

加载数据

可以用INSERT INTO命令：

INSERT INTO table_name
(column_name1, column_name2, ...)
VALUES
(data, data, ...)
(data, data, ...)

但是从数据文件中批量加载数据到数据库，需要LOAD命令：

LOAD DATA LOCAL INPATH 'hdfs-data-file_dir/name' INTO TABLE table_name;

也可以直接上传数据文件到HDFS中hive数据库的目录：

hdfs dfs -put 'hdfs-data-file_dir/name' /user/hive/warehouse/t2——bak/data-file_name

查看表中的内容

SELECE * FROM table_name;

修改表字段

添加name字段

ALTER TABLE t2_bak ADD COLUMNS (name STRING);

删除表

DROP TABLE t2_bak;

指定列、行分割符

hive中默认的行分隔符是换行符\n，列分隔符是\001

如果数据文件中的分隔符与默认的分隔符不同，需要在创建表时指定分隔符：

例如，数据文件的行分割符是\n，列分割符是\t，

CREATE TABLE t3(
id INT comment 'ID',
stu_name STRING comment 'name',
stu_birthday DATE comment 'birthday',
online BOOLEAN comment 'is online'
)ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n';

3. 数据类型

3.1 基本数据类型

数据类型	开始支持版本	数据类型	开始支持版本
TINYINT	~	TIMESTAMP	0.8.0
SMALLINT	~	DATE	0.12.0
INT/INTEGER	~	STRING	~
BIGINT	~	VARCHAR	0.12.0
FLOAT	~	CHAR	0.13.0
DOUBLE	~	BOOLEAN	~
DECIMAL	0.11.0

3.2 复合数据类型

arrays: ARRAY<data_type>

数组结构，存储一组相同类型的数据

例如，学生名单表，包含id学生编号, 'name’姓名, 'favors’爱好字段;

数据文件的行分割符是\n，列分割符是\t，数组数据的分隔符是,

数据：

1	zhangsan	swing,sing,coding
2	lisi	music,football

需要COLLECTION ITEMS TERMINATED BY ','指定数组元素的分隔符

CREATE TABLE stu(
	id INT,
	name STRING,
	favors ARRAY<STRING>
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
COLLECTION ITEMS TERMINATED BY ',';

查询数组元素时，需要指定数组字段名和索引号：

SELECT
	id, name, favors[1]
FROM
	stu
;

map: MAP<primitive_type, data_type>

键值对，每一个键值对是MAP集合的一个item

例如，学生名单表，包含id学生编号, 'name’姓名, 'scores’成绩字段;

其中成绩字段包含各科目名称和成绩

数据文件的行分割符是\n，列分割符是\t，MAP元素的分隔符是,, 键值对的分隔符是:

数据：

1	zhangsan	chinese:80,math:90,english:100
2	lisi	chinese:89,english:70,math:88

需要COLLECTION ITEMS TERMINATED BY ','指定MAP元素的分隔符

MAP KEYS TERMINATED BY ':'指定键值对key和value之间的分隔符

CREATE TABLE STU2(
id INT,
name STRING,
scores  MAP<STRING,INT>
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED by ','
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n';

查询键值对数据，需要指定key名：

SELECT
	id, name, scores['math']
FROM
	stu2
;

structs: STRUCT<col_name : data_type [COMMENT col_comment], …>

复合类型, 为数据指定一个隐形的col_name，在数据文件内不需要像map那样写明key

例如，学生名单表，包含id学生编号, 'name’姓名, 'address’地点字段;

其中地点字段包含籍贯地和居住地

数据文件的行分割符是\n，列分割符是\t，structs数据的分隔符是,

数据：

1	zhangsan	bj,sh
2	lisi	gz,sz

需要COLLECTION ITEMS TERMINATED BY ','指定structs数据的分隔符

CREATE TABLE stu3 (
	id INT,
	name STRING,
	address STRUCT<native_place_addr: STRING, living_place_addr: STRING>
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
COLLECTION ITEMS TERMINATED BY ',';

查询数据，需要指定通过.指定col_name

SELECT
	id, name, address.native_place_addr
FROM
	stu3
;

> Struct 和 Map 的区别：
map 可以随意增加键值对个数，struct中col_name个数固定  
map通过`[]`取值，struct通过`.`取值  
map的源数据需要key-value，struct的源数据只需要有value

4. 表的类型

MySQL中只有一种表，Hive中有多种表类型，内部表、外部表、分区表、桶表

4.1 内部表

hive中的默认表类型，其默认存储在warehouse目录中，在加载数据时，数据会移动到warehouse目录中

当删除表时，表中的数据和元数据会被同时删除

4.2 外部表

数据不会移动到warehouse目录中，只与外部数据建立一个映射关系，表的定义和数据的生命周期互不约束

当删除表时，只是删除这种映射关系，不会删除hdfs上的元数据

创建外部表：

官网文档给的格式，

CREATE EXTERNAL TABLE page_view(
	viewTime INT, 
	userid BIGINT,
    page_url STRING, 
    referrer_url STRING,
    ip STRING COMMENT 'IP Address of the User',
    country STRING COMMENT 'country of origination'
) COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>';

根据格式，创建外部表

需要在建表语句上添加EXTERNAL, 以及指定LOCATION表数据在hdfs上的存储路径

CREATE EXTERNAL TABLE external_table (
	key STRING
) LOCATION '/data/external';

加载数据也会存储在hdfs的/data/external目录下

4.3 分区表

例如，web服务器每天都产生一个日志数据文件， flume把数据采集到HDFS中，每天的数据都存储到其日期目录中。

如果已经采集一年的数据，要查询其中某一天的数据，需要遍历一整年的数据，再过滤出某一天的数据，会导致计算资源浪费

可以让hive在查询时，指定日期，直接定位到对应的日期目录，提升效率

要实现该功能，需要使用分区表。

分区字段就是每一个日期

使用PARTITIONED BY指定分区字段

案例如下：

创建分区表，分区字段名称是dt, 类型是STRING

CREATE TABLE partition_1 (
	id INT,
	name, STRING
) PARTITIONED BY (dt STRING)
ROW FROMAT DELIMITED
FIELDS TERMINATED BY '\t';

通过DESC table_name可以查看分区信息

hive (default)> DESC partition_1;
OK
col_name        data_type       comment
id                      int                                         
name                    string                                      
dt                      string                                      
                 
# Partition Information          
# col_name              data_type               comment             
dt                      string                                      
Time taken: 0.745 seconds, Fetched: 7 row(s)

数据：

1	zhangsan
2	lisi

加载数据时，需要指定分区信息

LOAD DATA LOCAL INPATH '/data/soft/hivedata/partition_1.data' INTO TABLE partition_1 PARTITION (dt='2021-05-04');

也可以先创建分区，再加载数据：

ALTER TABLE partition_1 ADD PARTITION (dt='2021-05-03');

加载数据到hdfs：

LOAD DATA LOCAL INPATH '/data/soft/hivedata/partition_1.data' INTO TABLE partition_1 PARTITION (dt='2021-05-03');

或者

hdfs dfs -put /data/soft/hivedata/partition_1.data /user/hive/warehouse/partition_1/dt=2021-05-03

可以通过SHOW PARTITIONS table_name; 查看表中的分区

可以通过ALTER TABLE partition_1 DROP PARTITION (dt='2021-05-03'); 删除分区

也可以指定多个分区条件：

CREATE TABLE partition_2 (
	id int,
	name string
) PARTITIONED BY (year INT, school STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';

数据：

1	zhangsan
2	lisi
3	wangwu

加载数据时，需要指定两个分区的信息：

LOAD DATA LOCAL INPATH '/data/soft/hivedata/partition_2.data' INTO TABLE partition_2 PARTITION (year=2020,school='chinese');

LOAD DATA LOCAL INPATH '/data/soft/hivedata/partition_2.data' INTO TABLE partition_2 PARTITION (year=2020,school='eng');

LOAD DATA LOCAL INPATH '/data/soft/hivedata/partition_2.data' INTO TABLE partition_2 PARTITION (year=2021,school='math');

LOAD DATA LOCAL INPATH '/data/soft/hivedata/partition_2.data' INTO TABLE partition_2 PARTITION (year=2020,school='eng');

查询分区中的数据：

全表

SELECT
	*
FROM 
	partition_2
;

单个分区字段筛选

SELECT
	*
FROM
	partition_2
WHERE
	year = 2020

多分区字段筛选

SELECT
	*
FROM
	partition_2
WHERE
	year = 2020 AND school = 'eng'

4.4 外部分区表

在外部表的基础上，增加分区

创建外部分区表：

CREATE EXTERNAL TABLE ex_par(
id INT,
name STRING
)PARTITIONED BY(dt string) 
 ROW FORMAT DELIMITED  
 FIELDS TERMINATED BY '\t'
 LOCATION '/data/ex_par';

加载数据，删除分区等操作都和内部分区表相同

删除外部表分区后，数据还在hdfs上，但是分区已经被删除了

要恢复分区的话，需要重新绑定数据到分区上

ALTER TABLE ex_par ADD PARTITION (dt='2021-05-04') LOCATION 'data/ex_par/dt=2021-05-04';

即，上传数据

hdfs dfs -mkdir /data/ex_par/dt=2021-05-04
hdfs dfs -put /data/soft/hivedata/ex_par.data /data/ex_par/dt=2021-05-04

添加分区

ALTER TABLE ex_par ADD PARTITION (dt='2021-05-04') LOCATION '/data/ex_par/dt=2021-05-04';

4.5 桶表

桶表对数据进行哈希取值，然后放到不同文件中存储

桶表能用哈希值作为索引，均匀存放数据，解决数据倾斜的问题

创建桶表：

按照id进行分桶，分成4个桶

CREATE TABLE bucket_tb (
	id INT
) CLUSTERED BY (id) INTO 4 BUCKETS;

开启hive的同操作：

set hive.ecforce.bucketing = true;

加载数据，不再是从文件加载，而是从其他表中的数据加载

INSERT INTO TABLE bucket_table_name SELECT col_names FROM table_name;

桶表的主要作用：

数据抽样

从大规模的数据集中抽取部分数据进行查看，可以使用桶表，简化抽取流程

SELECT * FROM bucket_table_name TABLESAMPLE(BUCKET x OUT OF y ON col_name);

y 表示将桶表中的数据随机分为多少桶

x 表示取出第几桶的数据

y 尽可能是buckets数量的倍数或者因数

y 大于 x

提高某些查询语句的查询效率

例如，join查询中，如果两个表是桶表，连接的字段是分桶的字段，则可以避免查询过程中产生笛卡尔积的情况

4.6 视图

视图是一张虚拟的表，是查询语句的结果，但是并不会产生新的表

创建视图：

CREATE VIEW view_name AS SELECT table_name.col_names FROM table_name;

视图创建后，可以通过SHOW tables;查看到其视图名称

可以通过DESC view_name;查看其结构

可以通过查询语句，查看数据, SELECT * FROM view_name;

5. 表的使用-综合案例

综合案例：

flume按照日期把日志数据采集到hdfs中对应的目录

由于这份数据会被多种计算引擎使用，所以使用外部表；加上离线计算的大部分需求是按日期计算，所以在表中增加日期的分区字段，使用外部分区表

数据文件的目录：

[root@bigdata04 hivedata]# hdfs dfs -ls /moreType/20200504
Found 3 items
drwxr-xr-x   - root supergroup          0 2021-05-04 21:23 /moreType/20210504/giftRecord
drwxr-xr-x   - root supergroup          0 2021-05-04 21:23 /moreType/20210504/userInfo
drwxr-xr-x   - root supergroup          0 2021-05-04 21:23 /moreType/20210504/videoInfo

数据的格式是json：

[root@bigdata04 hivedata]# hdfs dfs -cat /moreType/20210504/giftRecord/* 
{"send_id":"834688818270961664","good_id":"223","video_id":"14943443045138661356","gold":"10","timestamp":1494344574,"type":"giftRecord"}

[root@bigdata04 hivedata]# hdfs dfs -cat /moreType/20210504/userInfo/* 
{"uid":"861848974414839801","nickname":"mick","usign":"","sex":1,"birthday":"","face":"","big_face":"","email":"abc@qq.com","mobile":"","reg_type":"102","last_login_time":"1494344580","reg_time":"1494344580","last_update_time":"1494344580","status":"5","is_verified":"0","verified_info":"","is_seller":"0","level":1,"exp":0,"anchor_level":0,"anchor_exp":0,"os":"android","timestamp":1494344580,"type":"userInfo"}

[root@bigdata04 hivedata]# hdfs dfs -cat /moreType/20210504/videoInfo/* 
{"id":"14943445328940974601","uid":"840717325115457536","lat":"53.530598","lnt":"-2.5620373","hots":0,"title":"0","status":"1","topicId":"0","end_time":"1494344570","watch_num":0,"share_num":"1","replay_url":null,"replay_num":0,"start_time":"1494344544","timestamp":1494344571,"type":"videoInfo"}

面对json格式，需要对数据进行解析，以便提取其中的每个字段

hive中提供了一个get_json_object内置函数实现该功能

思路如下：

创建一个外部分区表，表中只有一个字段log用于保存json字符串，其分区是日期和数据类型
创建一个视图，查询外部分区表中的数据，并解析json数据
提取具体的数据时，直接查询视图中的字段信息

创建外部分区表

CREATE EXTERNAL TABLE ex_par_more_tyep (
	log STRING
) PARTITIONED BY ( dt STRING, d_type STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/moreType';

加载数据

因为数据已经采集到hdfs上，所以只需要在表中绑定分区和数据路径就行

ALTER TABLE ex_par_more_type ADD PARTITION (dt='20210504', d_type='giftRecord') LOCATION '/moreType/20210504/giftRecord';

ALTER TABLE ex_par_more_type ADD PARTITION (dt='20210504', d_type='userInfo') LOCATION '/moreType/20210504/userInfo';

ALTER TABLE ex_par_more_type ADD PARTITION (dt='20210504', d_type='videoInfo') LOCATION '/moreType/20210504/videoInfo';

创建视图

giftRecord数据的视图

CREATE VIEW gift_record_view AS 
	SELECT 
		get_json_object(log, '$.sent_id') AS send_id,
		get_json_object(log, '$.good_id') AS good_id,
		get_json_object(log, '$.video_id') AS video_id,
		get_json_object(log, '$.gold') AS gold,
		dt
	FROM ex_par_more_type
WHERE d_type = 'giftRecord';

userInfo数据的视图

CREATE VIEW user_info_view AS 
	SELECT
		get_json_object(log, '$.uid') AS uid,
		get_json_object(log, '$.nickname') AS nickname,
		get_json_object(log, '$.usign') AS usign, 
		get_json_object(log, '$.sex') AS sesx,
		dt
	FROM ex_par_more_type
WHERE d_type = 'userInfo';

videoInfo数据的视图

CREATE VIEW video_info_view AS 
	SELECT
		get_json_object(log, '$.id') AS id,
		get_json_object(log, '$.uid') AS uid,
		get_json_object(log, '$.lat') AS lat,
		get_json_object(log, '$.lnt') AS lnt,
		dt
	FROM ex_par_more_type
WHERE d_type = 'videoInfo';

查询解析后的数据, 可以指定dt字段的日期

SELECT * FROM gift_record_view WHERE dt = '20210504'
...

定时任务为新添加的数据绑定分区

脚本如下：

[root@bigdata04 hivedata]# vi addPartition.sh
#!/bin/bash
# 每天凌晨1点定时添加当天日期的分区
# 可以手动执行，命令后跟着日期参数
if [ "a$1" = "a" ]
then
	dt=`date +%Y%m%d`
else
	dt=$1
fi
# 指定添加分区操作
# IF NOT EXISTS 判断分区是否存在，避免报错
hive -e "
ALTER TABLE ex_par_more_type ADD IF NOT EXISTS PARTITION(dt='${dt}',d_type='giftRecord') LOCATION '/moreType/${dt}/giftRecord';
ALTER TABLE ex_par_more_type ADD IF NOT EXISTS PARTITION(dt='${dt}',d_type='userInfo') LOCATION '/moreType/${dt}/userInfo';
ALTER TABLE ex_par_more_type ADD IF NOT EXISTS PARTITION(dt='${dt}',d_type='videoInfo') LOCATION '/moreType/${dt}/videoInfo';
"

添加到定时任务到crontab：

00 01 * * * root /bin/bash /data/soft/hivedata/addPartition.sh >> /data/soft/hivedata/addPartition.log

如果数据量大，计算效率要求高

这种情况，需要对原始json数据进行解析，再存储到hdfs中，再建表

这样对原属数据的解析只需要一次，在数据量大的情况下，能提高计算效率

五、Hive 函数

1. 函数的基本操作

查看所有内置函数

SHOW functions;

查看某函数的描述信息

DESC FUNCTION function_name;

查看函数的详细信息

DESC FUNCTION EXTENDED function_name;

2. Hive窗口函数、排序函数

2.1 窗口函数

几个主要的窗口函数

rank() 排序相同时，重复，总数不变

dense_rank() 排序相同时，重复，总数减少

row_number() 根据顺序计算

2.2 窗口函数使用

分组排序取TopN

案例：有一份学生的考试成绩数据，科目包含语文数学英语三门，要求计算班级中单科排名前三名学生的姓名

数据：

1	zs1	chinese	80
2	zs1	math	90
3	zs1	english	89
4	zs2	chinese	60
5	zs2	math	75
6	zs2	english	80
7	zs3	chinese	79
8	zs3	math	83
9	zs3	english	72
10	zs4	chinese	90
11	zs4	math	76
12	zs4	english	80
13	zs5	chinese	98
14	zs5	math	80
15	zs5	english	70

创建表：

CREATE EXTERNAL TABLE student_score (
	id INT,
	name STRING,
	sub STRING,
	score INT
) ROW FROMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/data/student_score';

加载数据：

hdfs dfs -put /data/soft/hivedata/student_score.data /data/student_score

按照科目分组，按照成绩降序，编号

SELECT 
	*, 
	ROW_NUMBER() 
	OVER (
		PARTITION BY sub 
		ORDER BY score DESC
	) AS num 
FROM 
	student_score
;

取前三名

SELECT
	*
FROM (
	SELECT 
		*, 
		ROW_NUMBER() 
		OVER (
			PARTITION BY sub 
			ORDER BY score DESC
		) AS num 
	FROM 
		student_score
) AS s
WHERE s.num <= 3
;

其中ROW_NUMBER() 可以替换成RANK() 或者 DENSE_RANK()

RANK()结果如下：

同一科目分数相同的，排名编号相同，但是下一个编号不连续

13      zs5     chinese 98      1
10      zs4     chinese 90      2
1       zs1     chinese 80      3
7       zs3     chinese 79      4
4       zs2     chinese 60      5
3       zs1     english 89      1
6       zs2     english 80      2
12      zs4     english 80      2
9       zs3     english 72      4
15      zs5     english 70      5
2       zs1     math    90      1
8       zs3     math    83      2
14      zs5     math    80      3
11      zs4     math    76      4
5       zs2     math    75      5

DENSE_RANK()结果如下：

同一科目分数相同的，排名编号相同，下一个编号连续，编号总数减少

13      zs5     chinese 98      1
10      zs4     chinese 90      2
1       zs1     chinese 80      3
7       zs3     chinese 79      4
4       zs2     chinese 60      5
3       zs1     english 89      1
6       zs2     english 80      2
12      zs4     english 80      2
9       zs3     english 72      3
15      zs5     english 70      4
2       zs1     math    90      1
8       zs3     math    83      2
14      zs5     math    80      3
11      zs4     math    76      4
5       zs2     math    75      5

2.3 Hive排序相关函数

ORDER BY

对查询的结果做一次全局排序，只生成一个reduce任务

SORT BY

支持多个reduce任务，如果有多个reduce，则其查询结果在每个reducer端都会做一次排序，即保证局部排序，但是在多个reduce任务的情况下不能保证所有的数据是全局有序

可以设置hive的reduce个数
set mapreduce.job.reduces = 2;

DISTRIBUTE BY

控制map输出到reduce是如何划分的，可以结合SORT BY 实现分区内排序

如：

SELECT id FROM table_name DISTRIBUTE BY id SORT BY id;

CLUSTER BY

上一个语句，可以简写为：

SELECT id FROM table_name CLUSTER BY id;

2.4 Hive 的分组和去重函数

GROUP BY 对数据按照指定字段分组
DISTINCT 对数据中指定字段的值去重

如：

使用DISTINCT会将name都shuffle到一个reducer

SELECT 
	COUNT(DISTINCT name)
FROM
	table_name
;

将name分组并去重，然后再计算COUNT

SELECT
	COUNT(tmp.name)
FROM (
	SELECT
		name
	FROM
		table_name
	GROUP BY
		name
) AS tmp
;

2.5 其他函数应用

行转列

把多行数据转为一列数据

如下三个函数能实现这种需求：

CONCAT_WS()

COLLECT_LIST()

COLLECT_SET()

CONCAT_WS() :

CONCAT_WS(separator, [string | array(string)]+) - returns the concatenation of the strings separated by the separator.

此函数可指定分隔符，用于拼接多个字段的值

接收多个参数，第一个参数是分隔符，之后的参数可以是字符串或者字符串数组

COLLECT_LIST() :

COLLECT_LIST(x) - Returns a list of objects with duplicates

返回一个list集合，集合中的元素会重复

COLLECT_SET() :

COLLECT_SET(x) - Returns a set of objects with duplicate elements eliminated

返回一个set集合，集合中的元素不重复

案例：

有一份数据，包含学生名字和其爱好

数据：

[root@bigdata04 hivedata]# cat student_favors.data
zs	swing
zs	footbal
zs	sing
zs	codeing
zs	swing

需求：将这位学生的爱好从数行转换成一列

先创建表：

CREATE EXTERNAL TABLE student_favors (
	name STRING,
	favor STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/data/student_favors';

上传数据：

hdfs dfs -put /data/soft/hivedata/student_favors.data /data/student_favors

将数据由行转换成列

SELECT
	name,
	CONCAT_WS(',', COLLECT_LIST(favor)) AS favor_list
FROM 
	student_favors
GROUP BY 
	name
;

结果, 包含重复值：

name	favor_list
zs	swing,footbal,sing,codeing,swing

或者

SELECT
	name,
	COLLECT_LIST(favor) AS favor_list
FROM
	student_favors
GROUP BY
	name
;

结果，是一个list集合, 包含重复值：

name	favor_list
zs	["swing","footbal","sing","codeing","swing"]

或者

SELECT
	name,
	CONCAT_WS(',', COLLECT_SET(favor)) AS favor_list
FROM
	student_favors
GROUP BY
	name
;

结果，一个set集合，不包含重复值：

name	favor_list
zs	swing,footbal,sing,codeing

列转行

把一列数据转换成多行

可以使用如下三个函数

SPLIT()

EXPLODE()

LATERAL VIEW

SPLIT()

SPLIT(str, regex) - Splits str around occurances that match regex

两个参数，字符串和正则表达式(切割规则)，返回一个array数组

EXPLODE()

EXPLODE(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns

一个参数，可以说array或者map

EXPLODE(array) 把数组中每个元素转成一行

EXPLODE(map) 把map中每个键值对转成一行，key和value各自一列

LATERAL VIEW

和上述两个函数一起使用，用于生成一个支持别名的虚拟表

案例数据：

[root@bigdata04 hivedata]# cat student_favors_2.data
zs	swing,footbal,sing
ls	codeing,swing

创建表：

CREATE EXTERNAL TABLE student_favors_2 (
	name STRING,
	favorlist STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/data/student_favors_2';

上传数据：

hdfs dfs -put /data/soft/hivedata/student_favors_2.data /data/student_favors_2

将数据从字符串转换成list：

SELECT
	SPLIT(favorlist, ',')
FROM
	student_favors_2
;

结果：

_c0
["swing","footbal","sing"]
["codeing","swing"]

将list转换成多行

SELECT
	EXPLODE(SPLIT(favorlist, ','))
FROM
	student_favors_2
;

结果：

swing
footbal
sing
codeing
swing

由于转换成多行后，直接查询name字段会报错，这时需要LATERAL VIEW创建一个虚拟表，以完成查询操作

SELECT
	name, favor_new
FROM
	student_favors_2
LATERAL VIEW
	EXPLODE(SPLIT(favorlist, ',')) table1
AS favor_new
;

结果：

name	favor_new
zs	swing
zs	footbal
zs	sing
ls	codeing
ls	swing

thisisBenjamin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录