Hive 3.x 配置&详解
Hive
1. 数据仓库概述
1.1 基本概念
-
数据仓库（英语：Data Warehouse，简称数仓、DW），是一个用于存储、分析、报告的数据系统。
-
数据仓库的目的是构建面向分析的集成化数据环境，分析结果为企业提供决策支持（Decision Support）
数仓专注分析
- 数据仓库本身并不“生产”任何数据，其数据来源于不同外部系统
- 同时数据仓库自身也不需要“消费”任何的数据，其结果开放给各个外部应用使用
- 这也是为什么叫“仓库”，而不叫“工厂”的原因。
1.2 数仓为何而来？
为了分析数据而来，分析结果给企业决策提供支撑
案例：中国人手保险公司
1. 业务数据的存储问题
- 中国人寿保险（集团）公司下辖多条业务线，包括：人寿险、财险、车险，养老险等。各业务线的业务正常运营需 要记录维护包括客户、保单、收付费、核保、理赔等信息。这么多业务数据存储在哪里呢？
- 联机事务处理系统（OLTP）正好可以满足上述业务需求开展, 其主要任务是执行联机事务处理。其基本特征是前台接收的用户数据可以立即传送到后台进行处理，并在很短的时间内给出处理结果。
- 关系型数据库（RDBMS）是OLTP典型应用，比如：Oracle、MySQL、SQL Server等
2. 分析型决策的制定
- 随着集团业务的持续运营，业务数据将会越来越多。由此也产生出许多运营相关的困惑：
- 能够确定哪些险种正在恶化或已成为不良险种？
- 能够用有效的方式制定新增和续保的政策吗？
- 理赔过程有欺诈的可能吗？
- 现在得到的报表是否只是某条业务线的？集团整体层面数据如何？
- 为了能够正确认识这些问题，制定相关的解决措施，瞎拍桌子是肯定不行的。
- 最稳妥办法就是：基于业务数据开展数据分析，基于分析的结果给决策提供支撑。也就是所谓的数据驱动决策的制定。
OLTP环境开展分析可行吗？
OLTP（On-line Transaction Processing）：操作型处理、称为联机事务处理，也可以称为面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性等问题。
可以，但是没必要
- OLTP系统的核心是面向业务，支持业务，支持事务。所有的业务操作可以分为读、写两种操作，一般来说读的压力明显大于写的压力。如果在OLTP环境直接开展各种分析，有以下问题需要考虑：
- 数据分析也是对数据进行读取操作，会让读取压力倍增
- OLTP仅存储数周或数月的数据
- 数据分散在不同系统不同表中，字段类型属性不统一
数据仓库面世
OLAP（On-line Analytical Processing）：分析型处理，称为联机分析处理，一般针对某些主题历史数据进行分析，支持管理决策。
- 当分析所涉及数据规模较小的时候，在业务低峰期时可以在OLTP系统上开展直接分析。
- 但为了更好的进行各种规模的数据分析，同时也不影响OLTP系统运行，此时需要构建一个集成统一的数据分析平台。该平台的目的很简单：面向分析，支持分析，并且和OLTP系统解耦合。
- 基于这种需求，数据仓库的雏形开始在企业中出现了
数据仓库的构建
- 如数仓定义所说，数仓是一个用于存储、分析、报告的数据系统，目的是构建面向分析的集成化数据环境。我们把 这种面向分析、支持分析的系统称之为OLAP（联机分析处理）系统。当然，数据仓库是OLAP系统的一种实现。
- 中国人寿保险公司就可以基于分析决策需求，构建数仓平台。
1.3 数仓的主要特征
- 面向主题
- 集成性
- 非易失性
- 时变性
1. 面向主题性（Subject-Oriented）
主题是一个抽象的概念，是较高层次上数据综合、归类并进行分析利用的抽象
- 主题是一个抽象的概念，是较高层次上企业信息系统中的数据综合、归类并进行分析利用的抽象。在逻辑意义上， 它是对应企业中某一宏观分析领域所涉及的分析对象。
- 传统OLTP系统对数据的划分并不适用于决策分析。而基于主题组织的数据则不同，它们被划分为各自独立的领域 ，每个领域有各自的逻辑内涵但互不交叉，在抽象层次上对数据进行完整、一致和准确的描述。
2. 集成性（Integrated）
主题相关的数据通常会分布在多个操作型系统中，彼此分散、独立、异构。需要集成到数仓主题下。
-
主题相关的数据通常会分布在多个操作型系统中，彼此分散、独立、异构。
-
因此在数据进入数据仓库之前，必然要经过统一与综合，对数据进行抽取、清理、转换和汇总，这一步是数据仓库 建设中最关键、最复杂的一步，所要完成的工作有：
- 要统一源数据中所有矛盾之处
- 如字段的同名异义、异名同义、单位不统一、字长不一致等等。
- 进行数据综合和计算
- 数据仓库中的数据综合工作可以在从原有数据库抽取数据时生成，但许多是在数据仓库内部生成的，即进入数据仓库以后进行综合生成的
- 要统一源数据中所有矛盾之处
-
下图说明了保险公司综合数据的简单处理过程，其中数据仓库中与“承保”主题有关的数据来自于多个不同的操作型系统。
-
这些系统内部数据的命名可能不同，数据格式也可能不同。把不同来源的数据存储到数据仓库之前，需要去除这些 不一致
3. 非易失性、非异变性（Non-Volatile）
也叫非易变性。数据仓库是分析数据的平台，而不是创造数据的平台。
- 数据仓库是分析数据的平台，而不是创造数据的平台。我们是通过数仓去分析数据中的规律，而不是去创造修改其中的规律。因此数据进入数据仓库后，它便稳定且不会改变。
- 数据仓库的数据反映的是一段相当长的时间内历史数据的内容，数据仓库的用户对数据的操作大多是数据查询或比 较复杂的挖掘，一旦数据进入数据仓库以后，一般情况下被较长时间保留。
- 数据仓库中一般有大量的查询操作，但修改和删除操作很少。
4. 时变性
数据仓库的数据需要随着时间更新，以适应决策的需要。
- 数据仓库包含各种粒度的历史数据，数据可能与某个特定日期、星期、月份、季度或者年份有关。
- 当业务变化后会失去时效性。因此数据仓库的数据需要随着时间更新，以适应决策的需要。
- 从这个角度讲，数据仓库建设是一个项目，更是一个过程 。
1.4 SQL-数仓主流开发语言
数仓开发语言概述
- 数仓作为面向分析的数据平台，其主职工作就是对存储在其中的数据开展分析，那么如何读取数据分析呢？
- 理论上来说，任何一款编程语言只要具备读写数据、处理数据的能力，都可以用于数仓的开发。比如大家耳熟能详 的C、java、Python等
- 关键在于编程语言是否易学、好用、功能是否强大。遗憾的是上面所列出的C、Python等编程语言都需要一定的时间进行语法的学习，并且学习语法之后还需要结合分析的业务场景进行编码，跑通业务逻辑。
- 不管从学习成本还是开发效率来说，上述所说的编程语言都不是十分友好的。
- 在数据分析领域，不得不提的就是SQL编程语言，应该称之为分析领域主流开发语言。
SQL语言介绍
- 结构化查询语言（Structured Query Language）简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理数据。
- SQL语言使我们有能力访问数据库，并且SQL是一种ANSI（美国国家标准化组织）的标准计算机语言，各大数据库 厂商在生产数据库软件的时候，几乎都会去支持SQL的语法，以使得用户在使用软件时更加容易上手，以及在不同 厂商软件之间进行切换时更加适应，因为大家的SQL语法都差不多。
- SQL语言功能很强，十分简洁，核心功能只用了9个动词。语法接近英语口语，所以，用户很容易学习和使用。
数仓与SQL
- 虽然SQL语言本身是针对数据库软件设计的，但是在数据仓库领域，尤其是大数据数仓领域，很多数仓软件都会去支持SQL语法
- 原因在于一是用户学习SQL成本低，二是SQL语言对于数据分析真的十分友好，爱不释手
SQL全称叫做结构化查询语言，结构化是什么意思？
结构化数据
- 结构化数据也称作行数据，是由二维表结构来逻辑表达和实现的数据，严格地遵循数据格式与长度规范，主要通过关系型数据库进行存储和管理。
- 与结构化数据相对的是不适于由数据库二维表来表现的非结构化数据，包括所有格式的办公文档、XML、HTML、 各类报表、图片和音频、视频信息等。
- 通俗来说，结构化数据会有严格的行列对齐，便于解读与理解。
SQL语法分类
SQL主要语法分为两个部分：数据定义语言 (DDL)和数据操纵语言 (DML) 。
-
DDL语法使我们有能力创建或删除表，以及数据库、索引等各种对象，但是不涉及表中具体数据操作：
- CREATE DATABASE - 创建新数据库
- CREATE TABLE - 创建新表
-
DML语法是我们有能力针对表中的数据进行插入、更新、删除、查询操作：
- SELECT - 从数据库表中获取数据
- UPDATE - 更新数据库表中的数据
- DELETE - 从数据库表中删除数据
- INSERT - 向数据库表中插入数据
2. Apache Hive
2.1 概述
什么是Hive？
- Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言（HQL），用于访问和 分析存储在Hadoop文件中的大型数据集。
- Hive核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行。
- Hive由Facebook实现并开源。
为什么使用Hive？
- 使用Hadoop MapReduce直接处理数据所面临的问题
- 人员学习成本太高，需要掌握java语言
- MapReduce实现复杂查询逻辑开发难度太大
- 使用Hive处理数据的好处
- 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）
- 避免直接写MapReduce，减少开发人员的学习成本
- 支持自定义函数，功能扩展很方便
- 背靠Hadoop，擅长存储分析海量数据集
Hive和Hadoop关系
- 从功能来说，数据仓库软件，至少需要具备下述两种能力：
- 存储数据的能力
- 分析数据的能力
- Apache Hive作为一款大数据时代的数据仓库软件，当然也具备上述两种能力。只不过Hive并不是自己实现了上述 两种能力，而是借助Hadoop
- Hive利用HDFS存储数据
- Hive利用MapReduce查询分析数据
- 这样突然发现Hive没啥用，不过是套壳Hadoop罢了。其实不然，Hive的最大的魅力在于用户专注于编写HQL， Hive帮您转换成为MapReduce程序完成对数据的分析
2.2 如何模拟实现Hive功能？
如果让您设计Hive这款软件，要求能够实现用户只编写sql语句，Hive自动将sql转换MapReduce程序，处理位于HDFS上的结构化数据。如何实现？
案例
在HDFS文件系统上有一个文件，路径为/data/china_user.txt
需求：统计来自于上海年龄大于25岁的用户有多少个？
场景目的
重点理解下面两点：
- Hive能将数据文件映射成为一张表，这个映射是指什么？
- Hive软件本身到底承担了什么功能职责？
映射信息记录
- 映射在数学上称之为一种对应关系，比如y=x+1，对于每一个x的值都有与之对应的y的值
- 在hive中能够写sql处理的前提是针对表，而不是针对文件，因此需要将文件和表之间的对应关系描述记录清楚。映射信息专业的叫法称之为元数据信息（元数据是指用来描述数据的数据 metadata）
- 具体来看，要记录的元数据信息包括：
- 表对应着哪个文件（位置信息）
- 表的列对应着文件哪一个字段（顺序信息）
- 文件字段之间的分隔符是什么
SQL语法解析、编译
- 用户写完sql之后，hive需要针对sql进行语法校验，并且根据记录的元数据信息解读sql背后的含义，制定执行计划 。
- 并且把执行计划转换成MapReduce程序来具体执行，把执行的结果封装返回给用户。
对Hive的理解
- Hive能将数据文件映射成为一张表，这个映射是指什么？
- 文件和表之间的对应关系
- Hive软件本身到底承担了什么功能职责？
- SQL语法解析编译称为MapReduce
最终效果
- 基于上述分析，最终要想模拟实现的Hive的功能，大致需要下图所示组件参与其中。
- 从中可以感受一下Hive承担了什么职责，当然，也可以把这个理解为Hive的架构图
2.3 Apache Hive架构
Hive架构图
用户接口
- 包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为shell命令行；Hive中的Thrift服务器允许外部客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议。WebGUI是通过浏览器访问Hive。
元数据存储
- 通常是存储在关系数据库如 mysql/derby中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
Driver驱动程序
- 包括语法解析器、计划编译器、优化器、执行器
- 完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有执行引擎调用执行
执行引擎
- Hive本身并不直接处理数据文件。而是通过执行引擎处理。当下Hive支持MapReduce、Tez、Spark执行引擎。
3. Apache Hive安装部署
3.1 元数据
什么是元数据
- 元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（data about data），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。
Hive Metadata
- Hive Metadata即Hive的元数据
- 包含用Hive创建的database、table、表的位置、类型、属性，字段顺序类型等元信息
- 元数据存储在关系型数据库中。如hive内置的Derby、或者第三方如MySQL等
Hive Metatore
- Metastore即元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据。
- 有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。某种程度上也保证了hive元数据的安全
metastore配置方式
- metastore服务配置有3种模式：内嵌模式、本地模式、远程模式。
- 区分3种配置方式的关键是弄清楚两个问题：
- Metastore服务是否需要单独配置、单独启动？
- Metadata是存储在内置的derby中，还是第三方RDBMS,比如MySQL
- 本文使用企业推荐模式--远程模式部署。
metastore远程模式
- 在生产环境中，建议用远程模式来配置Hive Metastore。在这种情况下，其他依赖hive的软件都可以通过Metastore访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性。
3.2 部署实战
安装前准备
- 由于Apache Hive是一款基于Hadoop的数据仓库软件，通常部署运行在Linux系统之上。因此不管使用何种方式配置Hive Metastore，必须要先保证服务器的基础环境正常，Hadoop集群健康可用。
- 服务器基础环境
- 集群时间同步、防火墙关闭、主机Host映射、免密登录、JDK安装
- Hadoop集群健康可用
- 启动Hive之前必须先启动Hadoop集群。特别要注意，需等待HDFS安全模式关闭之后再启动运行Hive。
- Hive不是分布式安装运行的软件，其分布式的特性主要借由Hadoop完成。包括分布式存储、分布式计算。
Hadoop与Hive整合
- 因为Hive需要把数据存储在HDFS上，并且通过MapReduce作为执行引擎处理数据
- 因此需要在Hadoop中添加相关配置属性，以满足Hive在Hadoop上运行。
- 修改Hadoop中core-site.xml，并且Hadoop集群同步配置文件，重启生效。
<!-- 整合hive -->
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
Step1 MySQL安装
Linux
-
命令行安装
sudo apt-get install mysql-server -
查看运行状态
systemctl status mysql # 显示running说明已经在运行 -
查看默认用户密码
sudo cat /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = XXXX password = XXXXX socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = XXXX password = XXXX socket = /var/run/mysqld/mysqld.sock -
使用默认用户登陆
mysql -uXXX -p -
查看用户权限
use mysql; select host, user, plugin from user; # 可以看到root用户只能localhost登陆 -
设置root运行远程登陆
# 更改localhost为 % update user set host='%' where user='root'; # 刷新权限 flush privileges; -
修改Mysql加密规则和root密码
MySQL8.0之前的版本密码加密规则：mysql_native_password
MySQL8.0密码加密规则：caching_sha2_password# 修改加密方式 ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; # 重新设置root的密码 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码'; FLUSH PRIVILEGES; select host, user, plugin from user; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | root | mysql_native_password | | localhost | debian-sys-maint | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | +-----------+------------------+-----------------------+ -
修改bind-address开放远程登陆
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf # 修改 bind-address = 0.0.0.0 -
重启MySql服务
systemctl restart mysql -
查看防火墙是否放行3306端口
namp -p3306 localhost
如果不是open则需要，关闭防火墙
MySQL安装后的目录结构分析
(此结构只针对于使用apt-get install 在线安装情况)
- 数据库存放目录：
/var/lib/mysql/ - 相关配置文件存放目录：
/usr/share/mysql - 相关命令存放目录：
/usr/bin(mysqladmin mysqldump等命令) - 启动脚步存放目录：
/etc/rc.d/init.d/
MySQL的卸载
# 停止进程
sudo kill $(pgrep mysql)
sudo apt-get remove --purge mysql-\*
Step2 Hive安装
（node1安装即可）
tar zxvf apache-hive-3.1.2-bin.tar.gz
mv apache-hive-3.1.2-bin/ hive
# 解决Hive与Hadoop之间guava版本差异
cd /export/server/apache-hive-3.1.2-bin/
rm -rf lib/guava-19.0.jar
cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar \
./lib/
Step3 修改hive-env.sh
cd /export/server/apache-hive-3.1.2-bin/conf
mv hive-env.sh.template hive-env.sh
vim hive-env.sh
export HADOOP_HOME=/opt/hadoop-3.3.0
export HIVE_CONF_DIR=/opt/hive-3.1.3/conf
export HIVE_AUX_JARS_PATH=/opt/hive-3.1.3/lib
Step4 新增hive-site.xml
<configuration>
<!-- 存储元数据mysql相关配置 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://XXX(hive节点名):3306/hive3?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>sql数据库的用户名</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>sql密码需要自己填写</value>
</property>
<!-- H2S运行绑定host -->
<property>
<name>hive.server2.thrift.bind.host</name>
<value>XXX(hive节点名)</value>
</property>
<!-- 远程模式部署metastore metastore地址 -->
<property>
<name>hive.metastore.uris</name>
<value>thrift://XXX(hive节点名):9083</value>
</property>
<!-- 关闭元数据存储授权 -->
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
</configuration>
Step5 添加驱动、初始化
-
上传MySQL JDBC驱动到Hive安装包lib路径下 下载链接
-
初始化Hive的元数据
cd /export/server/apache-hive-3.1.2-bin/ bin/schematool -initSchema -dbType mysql -verbos #初始化成功会在mysql中创建74张表
3.3 客户端使用
1. 自带客户端
bin/hive,bin/beeline- Hive发展至今，总共历经了两代客户端工具。
- 第一代客户端（deprecated不推荐使用）：
$HIVE_HOME/bin/hive, 是一个shellUtil。主要功能：一是可用于以交互或批处理模式运行Hive查询；二是用于Hive相关服务的启动，比如metastore服务。 - 第二代客户端（recommended 推荐使用）：
$HIVE_HOME/bin/beeline，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具，和第一代客户端相比，性能加强安全性提高。
HiveServer2服务介绍
- 远程模式下beeline通过 Thrift 连接到单独的HiveServer2服务上，这也是官方推荐在生产环境中使用的模式
- HiveServer2支持多客户端的并发和身份认证，旨在为开放API客户端如JDBC、ODBC提供更好的支持
关系梳理
- HiveServer2通过Metastore服务读写元数据。所以在远程模式下，启动HiveServer2之前必须先首先启动metastore服务
- 特别注意：远程模式下，Beeline客户端只能通过HiveServer2服务访问Hive。而bin/hive是通过Metastore服务访问的。具体关系如下：
bin/beeline客户端使用
-
在hive安装的服务器上，首先启动metastore服务，然后启动hiveserver2服务
#先启动metastore服务 然后启动hiveserver2服务 nohup /opt/hive-3.1.3/bin/hive --service metastore & nohup /opt/hive-3.1.3/bin/hive --service hiveserver2 & -
在node3上使用beeline客户端进行连接访问。需要注意hiveserver2服务启动之后需要稍等一会才可以对外提供服务。
-
Beeline是JDBC的客户端，通过JDBC协议和Hiveserver2服务进行通信，协议的地址是： jdbc:hive2://node1:10000
[root@node3 ~]# /opt/hive-3.1.3/bin/beeline Beeline version 3.1.2 by Apache Hive beeline> ! connect jdbc:hive2://node1:10000 Connecting to jdbc:hive2://node1:10000 Enter username for jdbc:hive2://node1:10000: root Enter password for jdbc:hive2://node1:10000: Connected to: Apache Hive (version 3.1.2) Driver: Hive JDBC (version 3.1.2) Transaction isolation: TRANSACTION_REPEATABLE_READ 0: jdbc:hive2://node1:10000>
2. Hive 可视化客户端
- DataGrip、Dbeaver、SQuirrel SQL Client等
- 可以在Windows、MAC平台中通过JDBC连接HiveServer2的图形界面工具
- 这类工具往往专门针对SQL类软件进行开发优化、页面美观大方，操作简洁，更重要的是SQL编辑环境优
- SQL语法智能提示补全、关键字高亮、查询结果智能显示、按钮操作大于命令操作
Datagrip
- DataGrip是由JetBrains公司推出的数据库管理软件，DataGrip支持几乎所有主流的关系数据库产品，如DB2、Derby、MySQL、Oracle、SQL Server等，也支持几乎所有主流的大数据生态圈SQL软件，并且提供了简单易用的界面，开发者上手几乎不会遇到任何困难
3.4 Set命令的使用
-
Hive命令行下执行set命令【仅当前会话有效】
hive> set hive.cli.print.current.db=true; # 显示当前数据库名称 -
Hive脚本
~/.hiverc中配置set命令【当前用户有效】
3.5 日志配置
-
Hive运行时日志
cd /opt/hive-3.1.3/conf mv hive-log4j.properties.template hive-log4j.properties # 更改以下选项，日志级别可以根据需要调整# list of properties property.hive.log.level = WARN property.hive.root.logger = DRFA # 更改路径 property.hive.log.dir = /opt/hive-3.1.3/logs property.hive.log.file = hive.log property.hive.perflogger.log.level = INFO -
Hive任务执行日志
cd /opt/hive-3.1.3/conf mv hive-exec-log4j.properties.template hive-exec-log4j.properties # 更改以下选项，日志级别可以根据需要调整# list of properties property.hive.log.level = WARN property.hive.root.logger = FA property.hive.query.id = hadoop # 更改路径 property.hive.log.dir = /opt/hive-3.1.3/logs property.hive.log.file = ${sys:hive.query.id}.log
4. Hive SQL语言
Hive数据模型总览
4.1 数据库&建库
SQL中DDL语法的作用
- 数据定义语言 (Data Definition Language, DDL)，是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database、table等
- DDL核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作
- Hive SQL（HQL）与标准SQL的语法大同小异，基本相通
- 基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握Hive DDL语法的重中之重
- 建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。通俗点说，没有表，表没有数据，你用Hive分析什么呢？
数据库database
- 在Hive中，默认的数据库叫做default，存储数据位置位于HDFS的
/user/hive/warehouse下 - 用户自己创建的数据库存储位置是
/user/hive/warehouse/database_name.db下
create database
-
create database用于创建新的数据库
- COMMENT：数据库的注释说明语句
- LOCATION：指定数据库在HDFS存储位置，默认
/user/hive/warehouse/dbname.db - WITH DBPROPERTIES：用于指定一些数据库的属性配置
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; -
例子：创建数据库itcast
注意：如果需要使用location指定路径的时候，最好指向的是一个新创建的空文件夹
create database if not exists itcast comment "this is my first db" with dbproperties ('createdBy'='Allen');
use database
- 选择特定的数据库：切换当前会话使用哪一个数据库进行操作
drop database
-
删除数据库
- 默认行为是RESTRICT，这意味着仅在数据库为空时才删除它
- 要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
4.2 表&建表
表Table
- 一个数据库通常包含一个或多个表。每个表由一个名字标识（例如“客户”或者“订单”）。
- 表包含带有数据的记录（行）
建表语法树（基础）
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT col_comment], ... )
[COMMENT table_comment]
[ROW FORMAT DELIMITED …];
-- 简写
CREATE TABLE table_name (col_name data_type);
数据类型
- Hive数据类型指的是表中列的字段类型
- 整体分为两类：原生数据类型（primitive data type）和复杂数据类型（complex data type）
- 最常用的数据类型是字符串String和数字类型Int。
分隔符指定语法
-
LazySimpleSerDe是Hive默认的，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、 换行的分隔符号
-
在建表的时候可以根据数据的特点灵活搭配使用
Hive默认分隔符
- Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符
- 默认的分割符是'\001'，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的
- 在vim编辑器中，连续按下Ctrl+v/Ctrl+a即可输入'\001' ，显示^A
- 在一些文本编辑器中将以SOH的形式显示
实例练习
- 数据类型
- 分隔符指定
- 默认分隔符
实例1
- 文件archer.txt中记录了手游《王者荣耀》射手的相关信息，包括生命、物防、物攻等属性信息，其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件。
- 字段含义：id、name（英雄名称）、hp_max（最大生命）、mp_max（最大法力）、attack_max（最高物攻） 、defense_max（最大物防）、attack_range（攻击范围）、role_main（主要定位）、role_assist（次要定位）
- 字段都是基本类型，字段的顺序需要注意一下
- 字段之间的分隔符是制表符，需要使用row format语法进行指定
建表
create database if not exists learnsql;
use learnsql;
create table t_archer (
id int comment "ID",
name string comment "英雄名称",
hp_max int comment "最大生命",
mp_max int comment "最大法力",
attack_max int comment "最高物理攻击",
defense_max int comment "最高物理防御",
attack_range string comment "攻击范围",
role_main string comment "主要定位",
role_assist string comment "次要定位"
) comment "英雄信息"
ROW FORMAT delimited
fields terminated by '\t';
上传文件
#把文件从课程资料中首先上传到node1 linux系统上
#执行命令把文件上传到HDFS表所对应的目录下
hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer
查看表
在bin/beeline客户端查看 or 可视化工具
select * from t_archer;
---
1,后羿,5986,1784,396,336,remotely,archer,
2,马可波罗,5584,200,362,344,remotely,archer,
3,鲁班七号,5989,1756,400,323,remotely,archer,
4,李元芳,5725,1770,396,340,remotely,archer,
5,孙尚香,6014,1756,411,346,remotely,archer,
6,黄忠,5898,1784,403,319,remotely,archer,
7,狄仁杰,5710,1770,376,338,remotely,archer,
8,虞姬,5669,1770,407,329,remotely,archer,
9,成吉思汗,5799,1742,394,329,remotely,archer,
10,百里守约,5611,1784,410,329,remotely,archer,assassin
表类型
-
内部表
- Hive中的默认表类型，表数据默认存储在warehouse目录中
- 在加载数据的过程中，实际数据会被移动到warehouse目录中
- 删除表时，表中的数据和元数据将会被同时删除
-
外部表
-
检表语句中包含External的表叫做外部表
-
外部表在加载数据的时候，实际数据并不会移动到warehouse目录中，只是与外部数据建立一个链接（映射关系）
-
当删除一个外部表是，只删除元数据，不删除表中的数据，仅删除表和数据之间的链接
create external table external_table( key string )location '/data/external'; -- 目录是hdfs中的位置，若不存在，会自动创建 -- 添加数据会进入到此目录下，不会进入到warehouse相互转换
-- 内部表转外部表 alter table tblName set tblproperties('external'='true'); -- 外部表转内部表 alter table tblName set tblproperties('external'='false');
-
-
分区表
-
分区可以理解为分类，通过分区把不同类型数据放到不同目录
-
分区的标准就是指定分区字段，分区字段可以有一个或多个
-
分区表的意义在于优化查询，查询时，尽量利用分区字段，如果不使用分区字段，就会全表扫描，最典型的一个场景就是把天作为分区字段，查询的时候指定天
create table partition_1( id int, name string )partitioned by (dt string) row format delimited fields terminated by '\t'; -- 需要指定分区 load data local inpath '/root/Documents/partition_1.data' into table partition_1 partition(dt='20200101'); -- 展示分区情况 show partitions partition_1; -
外部分区表
工作中最常见的表：外部表+分区表
删除分区时，分区数据不会被删除
-
-
桶表
-
桶表是对数据进行哈希取值，然后放到不同文件中存储
-
物理上，每个通就是表（或分区）里的一个文件
-
桶表的作用
- 数据抽样
- 提高某些查询效率。例如join
create table bucket_tb( id int )clustered by (id) into 4 buckets; -- 根据id值，分成4个桶 -- 直接使用load命令是没有办法分桶的 -- 设置有几个桶就有几个reduce任务 set hive.enforce.bucketing=true; -- 只能使用insert ... select进行插入数据 insert into table bucket_tb select id from b_source where id is not null; -- 抽样数据 -- tablesample(bucket x out of y on id); -- 注意：y>=x -- y：表示把桶表中的数据随机分为多少桶 -- x: 表示取出第几桶的数据 select * from bucket_tb tablesample(bucket 1 out of 4 on id); -- 如果a、b表都是id的桶表，就不会产生全表的笛卡尔积，只会在同id内做笛卡尔积 select a.id,a.name,b.addr from a join b on a.id = b.id;
-
4.3 show
- Show相关的语句可以帮助用户查询相关信息。
- 比如我们最常使用的查询当前数据库下有哪些表 show tables.
--1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
show databases;
show schemas;
--2、显示当前数据库所有表
show tables;
SHOW TABLES [IN database_name]; --指定某个数据库
--3、查询显示一张表的元数据信息
desc formatted t_team_ace_player;
4.4 DML—Load
Load加载数据
- 在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名
- 文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse
-
不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功
-
最原始暴力的方式就是使用
hadoop fs –put|-mv等方式直接将数据移动到表文件夹下 -
但是，Hive官方推荐使用Load命令将数据加载到表中
因为采用hadoop fs上传文件就跨过了hive去操作底层，这不符合操作逻辑也不安全
Load语法
- Load英文单词的含义为：加载、装载
- 所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作
- local：纯复制
- 非local：移动
- 纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
语法规则之LOCAL
-
指定LOCAL， 将在本地文件系统中查找文件路径。
- 若指定相对路径，将相对于用户的当前工作目录进行解释
- 用户也可以为本地文件指定完整的URI-例如：
file:///user/hive/project/data1
-
没有指定LOCAL关键字
- 如果
filepath指向的是一个完整的URI，会直接使用这个URI - 如果没有指定schema，Hive会使用在hadoop配置文件中参数
fs.default.name指定的（不出意外，都是HDFS）
- 如果
-
LOCAL本地是哪里？
-
如果对HiveServer2服务运行此命令
本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统
-
语法规则之filepath
filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在 这种情况下，Hive将把该目录中的所有文件移动到表中）。filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：- 相对路径，例如：
project/data1 - 绝对路径，例如：
/user/hive/project/data1 - 具有
schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1
- 相对路径，例如：
实例1
--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
desc student_local;
--建表student_HDFS 用于演示从HDFS加载数据
create table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
desc student_HDFS;
--建议使用beeline客户端 可以显示出加载过程日志信息
--step2:加载数据
-- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/home/node1/Document/students.txt' INTO TABLE student_local;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上 hadoop fs -put /home/node1/Document/students.txt
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
4.5 DML—Insert
慢
语法功能
- Hive官方推荐加载数据的方式
- 清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。
- 也可以使用insert语法把数据插入到指定的表中，最常用的配合是把查询返回的结果插入到另一张表中
insert+select
- insert+select表示：将后面查询返回的结果作为内容插入到指定表中
- 需要保证查询结果列的数目和需要插入数据表格的列数目一致
- 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL
INSERT INTO TABLE tablename select_statement1 FROM from_statement;
4.6 DML—查询数据
Select语法树
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];
具体语法与sql类似，可参考此文章。
5. Hive函数
5.1 基本函数
查看所有内置函数
show functions;
查看指定函数信息
desc function FUNC;
查看指定函数扩展信息
desc function extended FUNC;
字符串函数
- 字符串长度函数：length
- 字符串反转函数：reverse
- 字符串连接函数：concat
- 带分隔符字符串连接函数：concat_ws
- 字符串截取函数：substr,substring
------------String Functions 字符串函数------------
select length("itcast");
select reverse("itcast");
select concat("angela","baby");
--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));
--字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);
--分割字符串函数: split(str, regex)
select split('apache hive', ' ');
日期函数
----------- Date Functions 日期函数 -----------------
--获取当前日期: current_date
select current_date();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);
数学函数
----Mathematical Functions 数学函数-------------
--取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);
条件函数
主要用于条件判断、逻辑判断转换这样的场合
-----Conditional Functions 条件函数------------------
select * from student limit 3;
--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空值转换函数: nvl(T value, T default_value)
select nvl("allen","cool");
select nvl(null,"cool");
--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;
5.2 分组排序TopN
ROW_NUMBER()：对每一行赋其行值
OVER()：分组
取出每个学科中的前三名
-- student_score table
-- id,name,sub,score
-- 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
select *
from (
select *, row_number() over(partition by sub order by score desc) as num
from student_score
) s
where s.num<=3;
RANK()：排名，存在相等的排名，比如 1、2、2、4这样的排名
DENSE_RANK()：密集排名，与rank不同的是1、2、2、3
5.3 行转列&列转行
行转列
CONCAT_WS()：连接元素
COLLECT_LIST()：聚合元素成list
COLLECT_SET()：聚合元素成set
-- student_favor table
-- name favor
-- zs swing
-- zs footbal
-- zs sing
-- zs codeing
-- zs swing
-- 将上面的多行，转换成单行
select name, collect_list(favor) as favor_list from student_favors group by name;
select name, collect_set(favor) as favor_list from student_favors group by name;
select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name;
列转行
SPLIT()：分割字符串，返回数组
EXPLODE()：将数组元素，转换成多行，或者将map元素转换为多行和多列
LATERAL VIEW：
1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
3..主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
4.语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
-- name favorlist
-- zs swing,footbal,sing
-- ls codeing,swing
-- 转换成下面的形式
-- zs swing
-- zs footbal
-- zs sing
-- ls codeing
-- ls swing
select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new;
-- 这样写会直接报错，因为name的个数与explode后的行数不等了
select name,explode(split(favorlist,',')) from student_favors_2 ;
5.4 排序函数
ORDER BY：全局有序
SORT BY：局部有序（单个reduce内有序）
DISTRIBUTE BY：对数据进行分区，一般和sort by结合使用
CLUSTER BY：cluster by id = distribute by id sort by id 等同于，但不支持desc
5.5 分组和去重
-- 统计order 表中name 去重之后的数据量
select count(distinct name) from order
-- 性能很低，要在一个reduce任务内完成
-- 分为两步，可以使用多个reduce任务完成，效率高
select count(*) from (select name from order group by name) tmp
6. 扩展
数据倾斜小实例
之前在Hadoop三大组件中最后一个单元提到过
select a.Key, SUM(a.Cnt) as Cnt
from (
select Key, COUNT(*) as Cnt
From TableName
Group By Key,
-- 将key001占比大的数据打散，可以根据其实际比例来进行，这样可以分配到多个reduce任务中
CASE
When key = 'Key001' THEN Hash(Random()) % 50
ELSE 0
END
) a
GROUP by a.Key;
