hive快速入门

合集 - 离线数仓(14)
1.Linux系统快速入门(完整版)
2.hive快速入门
3.Spark Core快速入门4.Spark SQL快速入门5.Spark Streaming快速入门6.Zookeeper快速入门7.Hadoop快速入门8.hiveSQL常见专题9.Prometheus 快速入门10.数据仓库理论11.DataX快速入门12.Flume快速入门13.HBase快速入门14.数仓项目总结
收起

hive快速入门

一、简介

hive是facebook，用于解决海量结构化数据的统计分析，现在属于apache软件基金会。hive是一个构建在hadoop之上的数据分析工具，hive没有存储数据的能力只有使用数据的能力，底层是由HDFS来提供数据存储，可以将结构化的数据映射为一张数据表，并且提供类似sql的查询功能，本质就是将HQL转化为mapreduce程序。
  • hive与关系型数据库的区别

image-20231104083812970

查询语言：类似于sql的查询语言HQL。熟悉sql开发者可以很方便的使用hive进行开发。

数据存储：hive是建立在hadoop上，所有的hive的数据都是存储在HDFS上。

数据更新：由于hive是针对数据仓库而应用设计的，而数据仓库是读多写少。因此，hive不支持对数据的修改和添加。

执行方式：hive中大多数据查询的执行是通过hadoop提供mapreduce来实现的。而数据库通常有自己的执行引擎。

执行延迟：hive在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟高。

可扩展性：hive建立在集群上并可以利用mapreduce进行并行计算，因此可以支持很大规模的数据。

数据规模：由于hive建立在集群上并可以利用mapreduce计算，因此可以支持很大规模的数据。

事务：hive在0.14版本后开始支持事务，前提是文件格式必须为ORC格式，同时必须分桶，还必须显式声明。

  • 为什么使用hive？
要问为什么使用 Hive 不妨直接问为什么不使用 Hadoop？使用 MapReduce 面临的问题是：
学习成本较高，因为要学习 Java 或者 Python（用来编写 MapReduce 程序）
MapReduce 实现复杂的查询逻辑的时候，开发难度相对较大（约束 Key 和 Value 的类型，自定义排序规则、自定义分
区器等等).
为什么 Hive 要比直接使用 MapReduce 开发效率更高？因为：操作接口采用类似 SQL 的语法，提供快速开发的能力
免去了写 MapReduce 的过程，减少开发人员的学习成本功能扩展方便。
  • 应用场景
日志分析：大部分互联网公司使用hive进行日志分析，包括百度、淘宝等。
统计网站一个时间段内的pv、uv
多维度数据分析
海量结构化数据的离线分析

二、Hive架构

image-20231104085044755

hive允许client连接的方式有三个：hive shell、Jdbc/odbc、webui。jdbc/odbc访问中间件Thrift软件框架，跨语言服务开发。DDL DQL DML，整体仿写一套SQL语句。
  • metastore
元数据、数据的数据。元数据包括表名、表结构信息、表所属的数据库（default库）、表的拥有者（权限信息）、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。

元数据服务的作用：客户端连接metastore服务、metastore服务再去连接mysql数据库来存取元数据。有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需知道Mysql数据库的用户名和密码，只需要连接metastore服务即可。

hive默认将元数据存储在Derby数据中，但其仅支持单线程操作，若有一个用户在操作，其他用户则无法使用，造成效率不高；而且当在切换目录后，重新进入hive会找不到原来已经创建的数据库和表，因此一般使用mysql数据库存储元数据。
  • Driver

image-20231104091653846

解析器：将sql字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成。

编译器：将AST编译生成逻辑执行计划。

优化器：对逻辑执行计划进行优化。

执行器：将逻辑执行计划转换成可以运行的物理计划。对于hive来说，就是MR/Spark

  • 大致流程：
将HQL语句解析成抽象语法树，将抽象语法树编译成查询块，将查询块转化成逻辑查询计划，重写逻辑查询计划，优化逻辑执行计划(RBO)，将逻辑查询计划转化为物理计划，选择最佳的join策略，优化物理执行计划（CBO），最后执行。

三、hive工作原理

当创建表时候，需要指定Hdfs文件路径，表和其文件会保存到metastore，从而建立表和数据的映射关系。当数据载入表时，根据映射获取相应的HDFS路径，将数据导入。

用户输入HQL后，hive会将其转换为Mapreduce或者spark任务，提交到YARN上执行，执行成功将返回结果。

在搭建数据仓库时，就会将SQL语句常用指令写成模板，封装在hive中。我们需要根据业务编写SQL语句，hive会自动映射封装好的Mapreduce模板去进行匹配。匹配完后将运行MapReduce程序，生成相应的分析结果，反馈给我们。

image-20231104093224239

image-20231104093301272

四、hive安装

  • hive搭建分为三种：内嵌模式、本地模式、远程模式。这里使用远程模式。

内嵌模式：使用Derby数据库来存储元数据，不需要额外起metastore服务。数据库和metastore服务都嵌套入在主hive server进程中。内嵌模式是属于默认方式，配置简单，但是只能单用户连接，适用于实验，不适用于生产环境，解压hive安装包，启动bin/hive即可使用。

image-20231104093726010

本地模式采用外部数据库来存储元数据，目前支持的数据库有：Oracle、MySQL、PostgreSQL、SQL Server。在这里我 们使用 MySQL。本地模式不需要单独起 MetaStore 服务，用的是跟 Hive 在同一个进程里的 MetaStore 服务。也就是说当你 启动一个 Hive 服务，里面默认会帮我们启动一个 MetaStore 服务。Hive 会根据 hive.metastore.uris 参数值来判断， 如果为空，则为本地模式。

image-20231104093828985

远程模式：需要启动metastore服务，然后每个客户端都在配置文件里配置到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。在生产环境中，建议使用远程模式来配置hive metastore，这样其他依赖hive的软件都可以通过metastore访问hive。

远程模式需要配置hive.metastore.uris参数来指定metastore服务运行机器Ip和端口，并且需要单独手动启动metastore服务。hiveserver2是hive启动的一个服务，客户端可以使用jdbc/odbc协议，通过Ip+port的方式对其进行访问，达到并发访问的目的。

image-20231104094419053

远程环境搭建

hive3.1.2安装包下载：https://dlcdn.apache.org/hive/hive-3.1.2/

image-20231104094446039

注意：安装前先确定已经安装hadoop+yarn+Mysql8.0

  • 先解压包，将压缩包上传到node1，然后进行解压
tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /opt/yjx/
rm apache-hive-3.1.2-bin -rf
  • 修改配置文件hive-env.sh
HADOOP_HOME=/usr/local/hadoop/hadoop-3.3.4/
export HIVE_CONF_DIR=/usr/local/hive/apache-hive-3.1.2-bin/conf
export HIVE_AUX_JARS_PATH=/usr/local/hive/apache-hive-3.1.2-bin/lib
  • 修改配置hive-site.xml
cp hive-default.xml.template hive-site.xml  #复制模板
vim hive-site.xml  #编辑配置文件
  • 首先删除configuration节点中的所有内容，然后在configuration节点中添加以下内容：
:set nu  #显示行号
:dd 10,20  #删除第10行到20行

<configuration>
 <!-- 数据库相关配置 -->
 <property>
 <name>javax.jdo.option.ConnectionURL</name>
 <value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true</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>root</value>
  </property>
 <property>
 <name>javax.jdo.option.ConnectionPassword</name>
 <value>Root@123456.</value>
 </property>
 <!-- 自动创建表 -->
 <property>
  <name>datanucleus.schema.autoCreateAll</name>
 <value>true</value>
 </property>
 <!-- 强制 MetaStore 的 schema 一致性，开启的话会校验在 MetaStore 中存储的信>    息的版本和 Hive 的 jar 包中的版
      本一致性，并且关闭自动 schema 迁移，用户必须手动的升级 Hive 并且迁移 sc    hema。关闭的话只会在版本不一致时给出警
 告，默认是 false 不开启 -->
 <!-- 元数据校验 -->
 <property>
 <name>hive.metastore.schema.verification</name>
<!-- MySQL8 这里一定要设置为 true，不然后面 DROP TABLE 可能会出现卡住的情况     -->
 <value>true</value>
 </property>
 <!-- 美化打印数据 -->
 <!-- 是否显示表名与列名，默认值为 false -->
 <property>
 <name>hive.cli.print.header</name>
 <value>true</value>
</property>
  <!-- 是否显示数据库名，默认值为 false -->
 <property>
 <name>hive.cli.print.current.db</name>
 <value>true</value>
 </property>
 <!-- Hive 数据仓库的位置（HDFS 中的位置） -->
 <property>
 <name>hive.metastore.warehouse.dir</name>
 <value>/hive/warehouse</value>
 </property>
 <!-- HiveServer2 的 WEBUI -->
  <property>
  <name>hive.server2.webui.host</name>
 <value>node2</value>
 </property>
 <property>
 <name>hive.server2.webui.port</name>
 <value>10002</value>
  </property>
 <!-- 指定 hive.metastore.uris 的 port，为了启动 MetaStore 服务的时候不用指定
    端口 -->
 <!-- hive ==service metastore -p 9083 & | hive ==service metastore -->
  <property>
 <name>hive.metastore.uris</name>
 <value>thrift://node2:9083</value>
 </property>
 </configuration>
  • 由于hive实际上还是底层跑仍然是MapReduce程序，那么我们需要让它拥有hadoop上运行的权限，修改hadoop的配置文件core-site.xml:
vim /usr/local/hadoop/hadoop-3.3.14/etc/hadoop/core-site.xml

在configuration节点中末尾处添加以下内容：

<!-- 该参数表示可以通过 httpfs 接口访问 HDFS 的 IP 地址限制 -->
<!-- 配置 root(超级用户) 允许通过 httpfs 方式访问 HDFS 的主机名、域名 -->
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<!-- 通过 httpfs 接口访问的用户获得的群组身份 -->
<!-- 配置允许通过 httpfs 方式访问的客户端的用户组 -->
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>

这里的 root 表示安装 Hive 时的用户，实际上是为 Hive 在 Hadoop 上创建了一个代理用户。

  • 配置日志组件

首先创建目录：

mkdir /usr/local/hive/apache-hive-3.1.2-bin/logs
cp hive-log4j2.properties.template hive-log4j2.properties
vim hive-log4j2.properties

将property.hive.log.dir = ${sys:java.io.tmpdir}/${sys:user.name}替换为：

property.hive.log.dir = /usr/local/hive/apache-hive-3.1.2-bin/logs
  • 添加驱动包

将mysql驱动包（注意mysql版本）添加到hive的lib包下：

mv mysql-connector-java-8.0.16.jar /opt/yjx/apache-hive-3.1.2-bin/lib/
  • 拷贝其他节点(node1)
rsync -av /usr/local/hive/apache-hive-3.1.2-bin/etc/core-site.xml  master:/usr/local/hive/apache-hive-3.1.2-bin/etc/

rsync -av /usr/local/hive/apache-hive-3.1.2-bin/etc/core-site.xml  node2:/usr/local/hive/apache-hive-3.1.2-bin/etc/

或者直接
rsync -av /usr/local/hive/apache-hive-3.1.2-bin/  master:/usr/local/hive/
rsync -av /usr/local/hive/apache-hive-3.1.2-bin/  node2:/usr/local/hive/
  • 配置环境变量（/etc/profile）
export HIVE_HOME=/usr/local/hive/apache-hive-3.1.2-bin/
export PATH=${HIVE_HOME}/bin:${PATH}

修改完成后执行 resource /etc/profile

  • 启动

查看mysql是否启动

netstat -ltnp  #查看是否有mysql服务及其监听端口
systemctl status mysqld #查看进程
systemctl start mysqld #启动mysql服务
  • 启动三台zookeeper服务
zkServer.sh start
  • 启动hdfs+yarn
start-dfs.sh 
start-yarn.sh
或者
start-all.sh
  • 启动日志服务器（可选）
mapred --daemon start historyserver
  • 第一次启动需要建库建表（只需在第一次启动时执行命令）
schematool -dbType mysql -initSchema

初始化数据库结果如下：

image-20231104102733659

  • 启动metastore服务
hive --service metastore #前台启动
nohup hive --service metastore >> /dev/null 2>&1 & #后台启动
  • 启动hiveserver2服务
hiveserver2 #前台启动
nohup hiveserver2 >> /dev/null 2>&1 &  #后台启动
  • 客户端连接一（不推荐）
hive #登录
exit #退出
  • 客户端连接二（推荐）
beeline -u jdbc:hive2://node2:10000 -n root  #连接客户端
!exit/!quit  #退出
  • 客户端连接三
beeline 
!connect jdbc:hive2://node2:10000
root  
(没密码直接回车)

关闭服务

  • 先关闭metastore和hiveserver2服务（前台启动使用ctrl+c关闭，后台启动先用jps查看PID，kill -9 PID）

  • 再关闭jobserver和hadoop

mapred --daemon stop jobserver #关闭jobserver服务
stop-all #关闭hadoop集群
zkServer.sh stop #关闭zookeeper集群
  • hive其他交互方式
beeline -u "jdbc:hive2://node01:10000/default" hive -e "SHOW DATABASES;" #登录并执行HQL
beeline -u "jdbc:hive2://node01:10000/default" hive -f "default.sql"  #登录并执行SQL文件

如果在执行HQL语句时出现了FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask错误,说明hadoop虚拟内存不足。tiyarn-site.xml

  • 添加yarn-site.xml配置文件内容如下（不配置默认是1024MB）：
<property>
   <name>yarn.scheduler.minimum-allocation-mb</name>
   <value>3072</value>
   <description>default value is 1024</description>
</property>

总之使用beeline客户端连接更安全！

五、hive元数据/数据类型

  • 版本表（version）

image-20231104104746231

  • 数据库相关表（DBS、database_params）

image-20231104104919135

image-20231104104931883

  • 视图相关表（tbls、table_params、tbl_privs）

image-20231104105017913

image-20231104105053142

image-20231104105136750

  • 文件存储相关表（sds、sd_params、serdes、serde_params）

image-20231104105305285

image-20231104105316804

image-20231104105332881

image-20231104105344027

  • 表字段相关表（columns_V2）

image-20231104105446650

  • 分区相关表（partitions、partition_keys、partition_key_vals、partition_params）

image-20231104105627066

image-20231104105644064

image-20231104105655737

image-20231104105705216

image-20231104105718253

六、数据类型

image-20231104105802170

image-20231104105815346

Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而 STRUCT 与 C 语 言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

七、hive基础

  • 创建数据库
create database if not exists [name];  #不指定数据库，会默认生成在hdfs中/hive/warehouse/目录下
create database if not exists [name] location '/test/name';  #指定位置创建数据库
  • 修改数据库
alter database [dbname] set dbproperties('createtime'='20220803')   #修改数据库创建时间
  • 数据库详情
show databases;
  • 可以通过like进行模糊查询
show database like 's*';
  • 查看数据库详情信息
desc database [dbname];
  • 切换数据库
use [dbname]
  • 删除数据库
drop database [dbname]
DROP DATABASE IF EXISTS [dbname];
drop database if exists [dbname] cascade; #强制删除
  • 创建数据表
create table if not exists [tblname]
(
    [filed] [dataType]，
    .....,
    address struct<street:string,city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
  • 加载TextFile数据格式数据
load data inpath '/test/' into table [dbname].[tblname]

默认只能切割单字符分隔符，如果是多字符切割符，需要配置序列化（推荐）

image-20231104112529551

create table if not exists [tblname]
(
    [filed] [dataType]，
    .....,
    address struct<street:string,city:string>
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties("input.regex"='[正则表达式]')  #正则表达式匹配每行数据格式

image-20231104112836875

其他办法，比如通过maprduce程序进行maptask时，切割字符串，再上传！

image-20231104113039036

  • 显示所有的数据表
SHOW TABLES;
  • 可以通过like进行过滤
SHOW TABLES LIKE 't*';
  • 查看表的详情
desc [tblName]  #查看数据表字段信息
desc formatted [tblname]  #查看数据表更详细的信息
  • 修改表结构
alter table [old_table_name] rename to [new_table_name];
alter table [table_name] add columns ([new_col [dataType]],....[comment '注释']);
alter table [table_name] change [old_col] [new_col] string;
  • 清空表数据
truncate table [table_name] ;

清空表数据只能清空内部表数据，外部表数据不能清空，并且数据清空后不能进行回滚。

  • 删除表
drop table [table_name]

删除表能删除内部表元数据和数据，不能删除外部表数据，只能删除外部表元数据。

  • 创建内外部表
-- 创建内部表  默认创建内部表
create table if not exists [tbl_name]
(
  [field1] [dataType],
    .....,
    address struct<street:string,city:string>
)
row format delimited fields terminated by ','
-- 可以指定位置 可以指定到某个目录（该目录下的文件都会被扫描到），也可以指定到某个具体的文件
location '/test/hhj'

-- 创建外部表 
create  external table  if not exists [tbl_name]
(
  [field1] [dataType],
    .....,
    address struct<street:string,city:string>
)
row format delimited fields terminated by ','
-- 可以指定位置 可以指定到某个目录（该目录下的文件都会被扫描到），也可以指定到某个具体的文件 不写默认创建在/hive/warehouse/目录下
location '/test/hhj'

如果目录不存在，hdfs会自定帮我们创建目录，我们只需要将表的源数据文件上传到目录下即可。

image-20231104114823991

image-20231104114841371

  • 导入数据
load data inpath '/test/data.txt' into table [tbl_name]  #追加数据
load data inpath '/test/data.txt’ overwrite into table [tbl_name] #覆盖数据
insert overwrite table [tbl_name] select [field1],... from [tbl_name]; #导入数据并覆盖，一般用于ORC、parquet数据文件格式导入数据。

内外表导入数据都是一样的，底层还是把数据文本文件，移动到数据表创建时指定的目录下，唯一区别是删除外部表不可以删除源数据（目录下的文本文件），内部表可以删除源文件。

  • 导出数据

导出到本地

mkdir -p /home/file/
insert overwrite local directory '/home/file' select * from [tbl_name];
-- 按指定格式导出文件
-- 按指定的格式将数据导出到本地
INSERT OVERWRITE LOCAL DIRECTORY '/root/person'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
SELECT * FROM t_person;

导出到HDFS文件系统上(少了local关键字)

insert overwrite  directory '/home/file' select * from [tbl_name];
-- 按指定格式导出文件
-- 按指定的格式将数据导出到本地
INSERT OVERWRITE  directory '/root/person'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
SELECT * FROM t_person;

将元数据和数据同时导出

export table [tbl_name] to '[路径]'

恢复表结构和数据

import from '[路径]'
  • 内置运算符和函数
show functions -- 查看系统自带的所有函数
desc function [function_name] -- 查看函数用法
desc function extended [function_name] -- 详细查看函数

常用字符串函数

-- 字符转 ASCII 码
SELECT ASCII('A');
-- ASCII 码转字符
SELECT CHR(97);
-- 将二进制转换为 base64 编码
SELECT BASE64(ENCODE('abcde', 'UTF8'));
-- 将 base64 编码转换为二进制
SELECT UNBASE64('YWJjZGU=');
-- 字符串长度
SELECT length('123456'), char_length('123456'), character_length('123456');
-- 去除指定位置的空格
SELECT LTRIM(' ABCDE         '), RTRIM('         ABCDE '), TRIM('     ABCDE     ');
-- 产生 n 个空格的字符
SELECT SPACE(5);
-- 从哪开始截取几个
SELECT SUBSTR('ABCDEFG', 2, 3);
-- 替换字符串
SELECT REPLACE('ABCDE', 'AB', '12');
-- 大小写转换
SELECT UPPER('abcde'), LOWER('ABCDE');
-- 将所有员工的姓名转化成小写
SELECT ename, LOWER(ename) FROM emp WHERE 1=1;
-- 先转成字符串然后拼接字符串
-- 重要
SELECT CONCAT('A', ',', 'B', ',', 'C');
SELECT CONCAT_WS(',', 'A', 'B', 'C');
-- 区别
-- CONCAT 可以直接将 INT 类型拼接成 STRING
-- CONCAT_WS 不可以直接拼接 INT 类型，CONCAT_WS 只支持 STRING 或者 ARRAY
-- 1,2（STRING类型）
SELECT CONCAT(1, ',', 2);
-- 报错
SELECT CONCAT_WS(',', 1, 2);
-- CONCAT 拼接时，只要参数中有 NULL，不管有多少不为空的参数，结果都为 NULL
-- CONCAT_WS 拼接时，如果参数中有 NULL 会忽略 NULL 值
-- null
SELECT CONCAT(1, null, 2);
-- 1,2
SELECT CONCAT_WS(',', '1', null, '2');
-- 使用指定字符拆分字符串
-- 重要
SELECT SPLIT('这个杀手不太冷,剧情-动作-犯罪', '-');

常用日期时间函数

-- 获取当前日期
SELECT CURRENT_DATE();
-- 获取当前日期时间
SELECT CURRENT_TIMESTAMP();
-- 格式化日期时间，  日期字符串必须满足 yyyy-MM-dd HH:mm:ss 格式
SELECT DATE_FORMAT('2020-06-01 14:52:21.123', 'yyyy-MM-dd HH:mm:ss.SSS');
SELECT DATE_FORMAT('2020-06-01 14:52:21', 'yyyy-MM-dd');
-- 获取当前时间的 UNIX 时间戳（UNIX_TIMESTAMP(空参方法) 4.0.0 已被废弃）
SELECT UNIX_TIMESTAMP();
-- 获取指定时间的 UNIX 时间戳
SELECT UNIX_TIMESTAMP('1/6/2020 14:52:21', 'd/M/yyyy HH:mm:ss');
SELECT UNIX_TIMESTAMP('01/06/2020 14:52:21', 'dd/MM/yyyy HH:mm:ss');
-- 转化 UNIX 时间戳到当前时区的时间格式
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss.SSS');
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('1/6/2020 14:52:21', 'd/M/yyyy HH:mm:ss'), 'yyyyMMdd');
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('01/06/2020 14:52:21', 'dd/MM/yyyy HH:mm:ss'), 'yyyyMMdd');
-- 获取特定的值 YEAR()/MONTH()/DAY()/HOUR()/MINUTE()/SECOND()
SELECT YEAR(CURRENT_TIMESTAMP()),
MONTH(CURRENT_TIMESTAMP()),
DAY(CURRENT_TIMESTAMP()),
HOUR(CURRENT_TIMESTAMP()),
MINUTE(CURRENT_TIMESTAMP()),
SECOND(CURRENT_TIMESTAMP());
-- 日期字符串必须满足 yyyy-MM-dd HH:mm:ss 格式
SELECT YEAR('2022-08-05');
SELECT YEAR('2022-08-05 12:12:12.0');
-- 返回日期前 n 天的日期
SELECT DATE_SUB('2022-08-05 12:12:12.0', 1);
-- 返回日期后 n 天的日期
SELECT DATE_ADD('2022-08-05 12:12:12.0', 1);
-- 返回开始日期减去结束日期的天数
SELECT DATEDIFF('2022-08-05 12:12:12.0', '2022-07-05 12:12:12.0');
-- 得到一个字符串日期的下周几的具体日期
SELECT NEXT_DAY('2022-08-05 12:12:12.0', 'SUNDAY');
-- 当月的最后一天
SELECT LAST_DAY('2022-08-05');

常用函数-URL解析

-- 解析 URL，返回 PROTOCOL 请求协议  (第二个参数必须大写)
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'PROTOCOL');

-- 解析 URL，返回 HOST 主机
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'HOST');

-- 解析 URL，返回 PATH 请求地址
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'PATH');

-- 解析 URL，返回 QUERY 请求参数
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'QUERY');

-- 解析 URL，返回 QUERY 指定参数的值
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'QUERY', 'wq');

-- 解析 URL，返回多条数据
SELECT PARSE_URL_TUPLE('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为',
'HOST', 'PROTOCOL', 'PATH', 'QUERY', 'QUERY:wq');

常用函数-正则

regexp_extract(str,'[regex]',num)

举例：

-- 将字符串按照正则表达式的规则拆分，返回 index 指定的字符，1 表示返回正则表达式中第一个()对应的结果，以此类推
SELECT REGEXP_EXTRACT('2022-08-11 14:15:25.326 [gateway-server,,] [reactor-http-nio-2] DEBUG',
'([\\d\\x20-:\\.]*) (.*) (.*) ([a-zA-Z]*)', 1);
  • 正则替换函数
regexp_replace(str,'[regex]',rep) -- 将字符串 str 中的符合 regexp 正则表达式的部分替换为 rep。
  • HQL语句执行顺序
from---->on--->join--->where--->group by--->having--->select--->distinct
--->order by--->limit
  • 查询虚表
SELECT RAND();
  • 流程控制
SELECT ename, sal,
CASE
WHEN sal < 1000 THEN '等级1'
WHEN sal > 1000 AND sal <= 3000 THEN '等级2'
WHEN sal > 3000 AND sal <= 5000 THEN '等级3'
WHEN sal > 5000 THEN '等级4'
ELSE '等级5'
END sal
FROM emp;
  • 排序

order by 全局排序 在一个reduce中进行排序，用法与sql一样，asc 升序 desc 降序。

sort by 在每一个reducer产生一个排序文件，只在每个reducer内部进行排序。(局部排序)

-- 查看 Reduce 的个数
SET mapred.reduce.tasks;
-- 设置 Reduce 的个数
SET mapred.reduce.tasks=3;
-- 局部排序
SELECT * FROM emp SORT BY sal DESC;

image-20231104122233707

distribute by （分区排序）会根据指定的字段将数据分到不同的 Reducer，且分发算法是 Hash 散列。DISTRIBUTE BY 类似 MR 中的 PARTITION（自定义分区），进行分区，一般结合 SORT BY 使用（注意：DISTRIBUTE BY 要在 SORT BY 之前），一般配合sort by使用。

SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal DESC;

image-20231104122530533

cluster by：组合排序 CLUSTER BY 除了具有 DISTRIBUTE BY 的功能外还兼具 SORTS BY 的功能，所以当 DISTRIBUTE BY 和 SORTS BY 字段相同 时，可以使用 CLUSTER BY 方式。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC，

SELECT * FROM emp CLUSTER BY deptno;

image-20231104122704763

八、hive高级

1、分区/分桶

image-20231104140110280

单独的一个表，对表进行分区、对表进行分区再分桶,对表直接分桶。实际上分桶、分区是一种分治思想，分而治之。

  • 使用分区技术，可以避免hive全表扫描，提升查询效率；同时能够减少数据冗余进而提高特定查询分析效率，在逻辑上分区与没分区没有区别，只是分区后，物理表会建立多个分区文件夹，每个过滤后的数据文本文件分在相应不同文件夹中。

使用分区表时，尽量利用分区字段进行查询，如果不指定分区字段查询，就会全部扫描，失去了分区意义。

  • 分区表类型分为动态分区、静态分区。区别在于前者是我们手动指定的，后者是通过数据来判断分区的。根据分区的深度又分为单分区和多分区。

2、静态分区

单分区演示 （创建单分区表）

create table if not exists [tblname](
 [fields] [dataType],
 .....
)partitioned by (parColumn dataType)    -- 分区字段不在括号中定义
row format delimited fields terminated by '';

partition by 与( )中字段不能一样，也就是分区字段不能出现在括号中！

  • 载入数据
-- 查询过滤条件 分区中应该是查询过滤的条件  查询时使用该条件 每个数据分好文件 放入不同条件的分区文件夹
load data inpath '/test/data.txt' into table  [tblName]   partition  (grade=1); 

select * from  t_student where grade=1;  -- 大大提高了查询的效率

缺陷：由于是人工分好数据到每个文本文件，倘若有一个文本文件中数据有误，被分入分区中，就会出现查询出来的数据不准确。

  • 表结构操作
-- 添加分区
alter table t_student add if not exists partition (grader=1)
-- 删除分区
alter table t_student drop partition (grader=1)
-- 查看分区
show partitions t_student

多分区演示

create table  if not exists t_student(
 sno int,
 sname string,
 gender string.
) partitioned by (age int,address string)
row format  delimited fields terminated by ',' 
lines terminated by '\n';

载入数据

load data inpath '/test/data.txt' into table t_student partition (age=18,address='beijing');

插入数据

insert into table t_student partition (grade=2,clazz=3) values (10,'jueyuan10');
-- 带上分区字段条件过滤
SELECT * FROM t_teacher WHERE grade=1 AND clazz=1;

3、动态分区

动态分区是hive自动进行分区，避免数据的不准确性，是通过数据来判断分区。

  • 开启动态分区，首先要在hive会话中设置以下参数：
-- 开启动态分区 （默认是 true）
set hive.exec.dynamic.partition=true;
-- 是否允许所有分区都是动态的，strict 要求至少包含一个静态分区列，nonstrict则无此要求（默认是strict）
set hive.exec.dynamic.partition.mode=nonstrict;
  • 其余参数详细配置如下：
-- 每个 Mapper 或 Reducer 可以创建的最大动态分区个数（默认为 100）
-- 比如：源数据中包含了一年的数据，如果按天分区，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果
使用默认值 100，则会报错
hive.exec.max.dynamic.partition.pernode=100;
-- 一个动态分区创建可以创建的最大动态分区个数（默认为 1000）
hive.exec.max.dynamic.partitions=1000;
-- 全局可以创建的最大文件个数（默认为 100000）
hive.exec.max.created.files=100000;
-- 当有空分区产生时，是否抛出异常（默认为 false）
hive.error.on.empty.partition=false;
-- 是否开启严格模式 strict（严格模式）和 nostrict（非严格模式，默认）
hive.mapred.mode=nostrict;

严格检查模式

  • hive通过设置hive.mapred.mode来设置是否开启严格模式。目前参数值有两个：strict（严格模式）和nostrict（非严格模式）。
  • 开启严格模式主要禁止一些查询，目前禁止以下三类查询：
分区表查询时，必须在where语句后指定分区字段，否则不允许执行。因为在查询分区时，如果不指定where分区查询，会进行全盘扫描，而全盘扫描，如果数据量特别大时，全表扫描会非常消耗资源。

order by查询时必须带有limit语句，否则不允许执行，因为order by会进行全局排序，这个过程会将处理结果分配到一个reduce中进行处理，处理时间长且影响性能。

迪卡尔积查询。数据量特别大时，笛卡尔积查询会出现不可控的情况，因此严格模式下不允许执行，要消除笛卡尔积，多使用join on 关键词。

一般开启严格模式，在查询时会报 FAILED: Error in semantic analysis:In strict mode, XXX is not allowed. If you really want to perform the operation,+set hive.mapred.mode=nonstrict+错误提示。
  • 动态分区建表
create table if not exists t_user(
 uid int,
 uname string,
 age int
)partition by (sex string)
row format delimited fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n';
  • 导入数据
load data inpath '/test/data.txt' into table t_user; #从hdfs上载入数据
-- 通过查询载入数据
insert into overwrite table t_user partition (sex string) select * from t_teacher;

4、分桶

分桶是在分区基础上，文件分的还不够细粒度，文件大小还不均匀，也就是将文件按照字段数值划分到多个文件中去。分区针对存储路径，分桶针对的是数据文件。

  • 分桶原理：

hive采用队列值哈希，除以桶的个数求模的方式决定该记录要存放在哪个桶中。

计算公式：bucket num=hash_function(bucketing_column) mod num_buckets.

分桶优势：方便取样，提高join多表连接的查询效率。

  • 分桶实现
-- 开启分桶功能 模式是false
set hive.enforce.bucketing=true;
-- 设置reduce的个数，默认是-1，-1时会通过计算得到reduce个数，一般reduce数量与表中buckets数量一直，有时候环境无法满足时，通常可以设置为接近可用主机的数量即可
set mapred.reduce.tasks=-1;
  • 创建分桶表
create table [tbl_name](field1,field2,...) clustered by (表内字段) sorted by (表内字段) into 12 buckets;
  • 创建分桶表
CREATE TABLE IF NOT EXISTS t_citizen_bucket (
idcard int,
username string,
province int
) CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
  • 导入数据
load data inpath '/test/t_citizen_bucket' into table  t_citizen_bucket;
或者
insert overwrite table t_citizen_e select * from t_citizen_e;

5、数据抽样

在大规模数据量的数据分析及建模任务中，往往对于全量数据挖掘分析时十分耗时和消耗集群资源，因此一般采用抽取一小部分数据进行分析和建模操作，数据抽象分为数据块抽象、分桶抽象和随机抽样。

  • 数据块抽样

函数根据hive表数据的大小按照一定比例抽取数据，并保存到新的hive表中。如：抽取原hive表中10%的数据。优点：速度快！缺点：不随机！

  • 注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或者使用随机抽样解决。
select * from [table_name] tablesample(N percent|ByteLengthLiteral|N ROWS)
  • 数据抽样实践
-- 数字与rows之间要有空格
create table if not exists t_citizen_sample as select * from t_citizen_e tablesample(1000 rows);
-- 数字与percent之间要有空格
create table if not exists t_citizen_sample as select * from t_citizen_e tablesample(10 percent);
-- 数字与M之间不要有空格
create table if not exists t_citizen_sample as select * from t_citizen_e tablesample(1M);
  • 分桶抽样

hive中分桶其实就是一个字段Hash取模，放入指定数据的桶中。优点:随机且速度快，不走MR程序

  • 分桶抽样实践：
-- 在分桶表中抽样 假设表中分了64桶
-- 取一桶
select * from t_citizen_bucket tablesample(bucket 1 out of 64 on idcard);
-- 取两桶
select * from t_citizen_bucket tablesample(bucket 1 out of 128 on idcard);
-- 取16桶 分别取 2 6 10 14 18 22 26 30 ... 步长为4 从第二桶开始2抽取 out of后的数比桶数少。
select * from t_citizen_bucket tablesample(bucket 2 out of 4 on idcard);
  • 随机抽样

使用rand()函数进行随机抽样，limit关键字限制抽样返回的数据，其中rand()函数前的distribute和sort关键字可以保证数据再Mapper和reducer阶段是随机分布的。优点：提供真正的随机抽样。缺点：速度慢

  • 实践：(分区排序随机 局部排序随机 取10个数据)
select * from t_citizen_bucket distribute by rand() sort by rand() limit 10;

6、事务

hive早期是不支持事务的，因此hive数据分析工具是没有事务的！

从hive0.14版本开始，引入了事务特性，能够在hive表上实现了ACID原则，包括insert、update、delete、merge语句，解决了缓慢变化维表或部分数据不正确需要更正的情况的，但是hive事务做不到传统关系型数据库那样的事务级别，仍然有很多局限性：

1、不支持commit、begin、rollback，所有的事务自动提交。

2、事务表仅支持ORC文件格式。

3、表参数transactional必须为true。

4、外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表（会话开启事务才能查询事务表）

5、默认是关闭事务支持，需要额外配置，具体如下。

  • 实践
-- 开启hive并发
set hive.support.concurrency=true;
-- 配置事务管理器
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

-- 如果事务表配置分区分桶一起使用建议开启以下参数
-- 开启分桶功能
set hive.enforce.bucketing=true;
-- 启动自动压缩
set hive.compactor.initiator.on=true;
-- 必须大于零  理想数和分桶数一样
set hive.compactor.worker.threads=2;
-- 是否允许所有分区都是动态的
set hive.exec.dynamic.partition.mode=nonstrict;
  • 创建事务表
create table if not exists tbl_transaction(
   id int,
   uname string,
   age int,
   address string
)
partitioned by (clazzid int)  -- 分区
clustered by (id int) into 2 buckets -- 分桶 分2桶
row format delimited fields terminated by ','
lines terminated by '\n'
stored as ORC
tblproperties('transactional'='true');

由于是事务表，所以对事务表进行插入、删除、更新操作会生成新的deltta目录存放事务的信息和表的结构。

目录名称的格式为delta_minwid_maxwid_stmtId即delta前缀、写事务的ID范围、以及语句ID

  • 插入数据
INSERT INTO test.t_user VALUES (1, "张三", 18), (2, "李四", 19), (3, "王五", 20);
  • 更新数据
UPDATE test.t_user SET name = "老张", age = 58 WHERE id = 1;
  • 删除数据
DELETE FROM test.t_user WHERE id = 1

执行插入、更新、删除数据操作时，会先生产一个删除数据事务目录存放删除事务，先删除表中的老旧数据。再进行插入、更新、删除操作。

7、压缩

随着读写的积累，表中的deltal和delete文件会越来越多。事务表的读取过程中需要合并所有的文件，数量一多势必会影响效率。此外，小文件对HDFS这样的文件系统也是不够友好的。因此，hive引入了压实的概念，分为Minor和Major两类。

alter table t_user compact 'minor';
alter table t_user compact 'major';

需要注意的是，在 Minor 或 Major Compaction 执行之后，原来的文件不会被立刻删除。这是因为删除的动作是在另一 个名为 Cleaner 的线程中执行的。因此，表中可能同时存在不同事务 ID 的文件组合，这在读取过程中需要做特殊处理。

8、物化视图

视图实际上是对sql语句的封装，视图是一个虚拟的表，只保存定义。不实际存储数据，实际查询的时候改写SQL去访问实际的数据表。不同于直接操作数据表，视图是依据select语句来创建的，所以操作视图时会根据创建视图的select语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

  • 实践
-- 创建视图
CREATE VIEW IF NOT EXISTS vw_emp_mgr (mgr) AS
SELECT mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;
-- 使用视图查询部门经理人中薪水最低的部门名称
SELECT e.empno, e.minsal, e.deptno, d.dname
FROM vw_emp_mgr_minsal e
INNER JOIN dept d ON e.deptno = d.deptno;
  • 物化视图

物化视图，是一个包括查询结果的数据库对象，可以预先计算并保存表连接或聚集等耗时较多的操作。在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。

物化视图实际上是把查询到的数据存放到一张物理表中，会占用一定的资源。hive还提供了物化视图的重写机制和物化视图存储选择机制，可以本地存储在hive中，也可以通过用户自定义storage handles存储在其他系统。目的是加快查询效率，从数据预处理的角度优化数据访问。

物化视图只可以在事务表上创建。

  • 物化视图实践
-- 创建事务表
CREATE TABLE IF NOT EXISTS test.emp (
EMPNO int,
ENAME varchar(255),
JOB varchar(255),
MGR int,
HIREDATE date,
SAL decimal(10,0),
COMM decimal(10,0),
DEPTNO int
)
STORED AS ORC
TBLPROPERTIES("transactional"='true');

-- 载入数据
INSERT INTO test.emp SELECT * FROM scott.emp;
-- 创建物化视图  前提要有事务表
create metarialized view  [db].[view_name]
as
select
deptno
count(*) cnt,
avg(sal) avg_sal
from test.emp
group by deptno;
  • 刷新物化视图

增量刷新:当物化视图满足一定条件时，默认会执行增加刷新，即只刷新原始源表中的变动会影响到的数据，增量刷新会减少重 建步骤的执行时间。要执行增量刷新，物化视图的创建语句和更新源表的方式都须满足一定条件：

物化视图只使用了事务表； 如果物化视图中包含 GROUP BY，则该物化视图必须存储在 ACID 表中，因为它需要支持 MERGE 操作。对于由 ScanProject-Filter-Join 组成的物化视图，不存在该限制

  • 定时刷新视图

可以通过 SET hive.materializedview.rewriting.time.window=10min; 设置定期刷新，默认为 0min。该参数 也可以作为建表语句的一个属性，在建表时设置。

  • 全量刷新

若只用insert更新源表数据，可以对物化视图进行增量刷新。若使用update、insert更新了源表数据，那么只能进行重建，即全量刷新（rebuild）。当数据源变更（新数据插入 Inserted、数据被修改 Modified），物化视图也需要更新以保持数据一致性，需要用户主 动触发 Rebuild，命令如下：

alter metarialized view [dbName].metarialized rebuild;
  • 查询重新

物化视图创建过后即可用于相关查询的加速，并且优化器能够利用其定义语义来使用物化视图自动重写传入的查询，从而加快查询的执行速度。通俗点讲就是查询的字段在物化视图中出现相应条件，会自动从物化视图中查询语句。

-- 全局开启物化视图重写机制 默认是true
set hive.metarializedview.rewriting=true;
-- 指定物化视图开启重写
alter metarialized view [db].[view_name] enable|disale rewrite;

9、高级查询

  • 行转列

所谓的行转列就是把每行数据进行处理存进集合中，然后通过爆破生成一列。

EXPLODE() 可以将 Hive 一行中复杂的 Array 或者 Map 结构拆分成多行，那如何将某个列的数据转为数组呢？可以配 置 SPLIT 函数一起使用。

实践：

SELECT EXPLODE(SPLIT(types, "-")) FROM t_movie1;
+------+
| col  |
+------+
| 剧情   |
| 动作   |
| 犯罪   |
| 动作   |
| 冒险   |
| 剧情   |
| ...   |
+------+

如果我们还想看看爆破字段关联的其他字段，可以使用侧视图lateral view一起使用！

-- movie_type 是侧视图别名
SELECT id, name, type
FROM t_movie1,
-- 生成侧视图（表）AS 后面是侧视图的字段
LATERAL VIEW EXPLODE(SPLIT(types, "-")) movie_type AS type;
+-----+---------------+--------+
| id  |   name        | type   |
+-----+---------------+--------+
| 1   | 这个杀手不太冷  | 剧情    |
| 1   | 这个杀手不太冷  | 动作    |
| 1   | 这个杀手不太冷  | 犯罪    |
| 2   | 七武士         | 动作    |
| 2   | 七武士         | 冒险    |
| 2   | 七武士         | 剧情    |
| ... | ...           | ...    |
+-----+---------------+--------+
  • 列转行

所谓的列转行，就是把分组字段列数据通过字符拼成一行。

COLLECT_SET() 和 COLLECT_LIST() 可以将多行数据转成一行数据，区别就是 LIST 的元素可重复而 SET 的元素是去重的。

SELECT id, name,
CONCAT_WS(':', COLLECT_SET(type)) AS type_set,
CONCAT_WS(':', COLLECT_LIST(type)) AS type_list
FROM t_movie2
GROUP BY id, name;
+-----+----------+-----------------+-----------------+
| id  |   name   |   type_set     |   type_list    |
+-----+----------+-----------------+-----------------+
| 1   | 这个杀手不太冷  | 剧情:动作:犯罪        | 剧情:动作:犯罪        |
| 2   | 七武士      | 动作:冒险:剧情        | 动作:冒险:剧情        |
| 3   | 勇敢的心     | 动作:传记:剧情:历史:战争  | 动作:传记:剧情:历史:战争  |
| 4   | 东邪西毒     | 剧情:武侠:古装        | 剧情:剧情:剧情:武侠:古装  |
| 5   | 霍比特人     | 动作:奇幻:冒险        | 动作:奇幻:冒险        |
+-----+----------+-----------------+-----------------+
  • URL解析

侧视图lateral view配合PARSE_URL_TUPLE函数可以实现URL字段的一列变多列。

SELECT PARSE_URL_TUPLE(url, 'PROTOCOL', 'HOST', 'PATH', 'QUERY') FROM t_mall;
+--------+----------------+----------+-----------------------------+
|   c0   |       c1       |   c2    |             c3              |
+--------+----------------+----------+-----------------------------+
| https  | search.jd.com  | /Search  | keyword=华为&enc=utf-8&wq=华为  |
| https  | s.taobao.com   | /search  | q=苹果                        |
+--------+----------------+----------+-----------------------------+

SELECT a.id, a.name, b.protocol, b.host, b.path, b.query
FROM t_mall a,
LATERAL VIEW
PARSE_URL_TUPLE(url, 'PROTOCOL', 'HOST', 'PATH', 'QUERY') b AS protocol, host, path, query;
+-------+-----------+-------------+----------------+----------+-----------------------------+
| a.id  | a.name   | b.protocol  |     b.host     | b.path  |           b.query           |
+-------+-----------+-------------+----------------+----------+-----------------------------+
| 1     | jingdong  | https       | search.jd.com  | /Search  | keyword=华为&enc=utf-8&wq=华为  |
| 2     | taobao    | https       | s.taobao.com   | /search  | q=苹果                        |
+-------+-----------+-------------+----------------+----------+-----------------------------+
  • JSON解析

json_tuple(jsonstr,p1,p2,....）

get_object_json(jsonstr,'$.paramter1')

建表时使用序列化解析器

  • json_tuple使用
SELECT
JSON_TUPLE(user_json, 'id', 'username', 'gender', 'age') AS (id, username, gender, age)
FROM t_user_json;
+-----+-----------+---------+------+
| id  | username  | gender  | age  |
+-----+-----------+---------+------+
| 1   | admin     | 男       | 18   |
| 2   | zhangsan  | 男       | 23   |
| 3   | lisi      | 女       | 16   |
+-----+-----------+---------+------+
  • get_json_object使用
SELECT
GET_JSON_OBJECT(user_json, '$.id') AS id,
GET_JSON_OBJECT(user_json, '$.username') AS username,
GET_JSON_OBJECT(user_json, '$.gender') AS gender,
GET_JSON_OBJECT(user_json, '$.age') AS age
FROM t_user_json;
+-----+-----------+---------+------+
| id  | username  | gender  | age  |
+-----+-----------+---------+------+
| 1   | admin     | 男       | 18   |
| 2   | zhangsan  | 男       | 23   |
| 3   | lisi      | 女       | 16   |
+-----+-----------+---------+------+
  • jsonSerde

Hive 内置了很多的 SerDe 类，可以使用 JsonSerDe 序列化器来处理。建表时指定 JSON 序列化器，加载 JSON 文件到 表中时会自动解析为对应的表格式。

  • 　创建 t_user_json2 表并使用 JsonSerDe 序列化器。
create table if not exists t_user_json(
id int,
username string,
gender string,
age int
)
row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
stored as textfile;
  • 加载json数据文本
LOAD DATA INPATH '/yjx/user.json' INTO TABLE t_user_json2;
  • 窗口函数定义

image-20231104174033884

而窗口函数则可为窗口中的每行都返回一个值。简单的理解，就是在查询的结果上再多出一列，这一列可以是聚合 值，也可以是排序值.

SELECT ename, COUNT(*) OVER() FROM emp;
+---------+-----------------+
| ename  | count_window_0  |
+---------+-----------------+
| MILLER  | 14              |
| FORD    | 14              |
| JAMES   | 14              |
| ADAMS   | 14              |
| TURNER  | 14              |
| KING    | 14              |
| SCOTT   | 14              |
| CLARK   | 14              |
| BLAKE   | 14              |
| MARTIN  | 14              |
| JONES   | 14              |
| WARD    | 14              |
| ALLEN   | 14              |
| SMITH   | 14              |
+---------+-----------------+

窗口函数一般分为三类：聚合型窗口函数和分析型窗口函数以及取值窗口函数。

SELECT XX函数() OVER (PARTITION BY 用于分组的列 ORDER BY 用于排序的列 ROWS/RANGE BETWEEN 开始位置 AND 结束位置);

OVER() ：窗口函数
参数：
  PARTITION BY ：后跟分组的字段，划分的范围被称为窗口
  ORDER BY ：决定窗口范围内数据的排序方式
  CURRENT ROW ：当前行
  PRECENDING ：向当前行之前移动
  FOLLOWING ：向当前行之后移动
  UNBOUNDED ：起点或终点（一般结合 PRECEDING，FOLLOWING 使用）
  UNBOUNDED PRECEDING ：表示该窗口第一行（起点）
  UNBOUNDED FOLLOWING ：表示该窗口最后一行（终点）
移动范围： ROWS 和 RANGE
rows：物理行范围
range：数值范围
  • 如果 OVER 不提供分组方法，则将所有数据分为一组，如下：
SELECT ename, deptno,
AVG(sal) OVER()
FROM emp;
+---------+---------+---------------+
| ename  | deptno  | avg_window_0  |
+---------+---------+---------------+
| MILLER  | 10      | 2073.2143     |
| FORD    | 20      | 2073.2143     |
| JAMES   | 30      | 2073.2143     |
| ADAMS   | 20      | 2073.2143     |
| TURNER  | 30      | 2073.2143     |
| KING    | 10      | 2073.2143     |
| SCOTT   | 20      | 2073.2143     |
| CLARK   | 10      | 2073.2143     |
| BLAKE   | 30      | 2073.2143     |
| MARTIN  | 30      | 2073.2143     |
| JONES   | 20      | 2073.2143     |
| WARD    | 30      | 2073.2143     |
| ALLEN   | 30      | 2073.2143     |
| SMITH   | 20      | 2073.2143     |
+---------+---------+---------------+
  • partition by

PARTITION BY 的作用和 GROUP BY 是类似，用于分组.

SELECT ename, deptno,
AVG(sal) OVER(PARTITION BY deptno) AS avgsal
FROM emp;
+---------+------------+
| ename  |   avgsal   |
+---------+------------+
| MILLER  | 2916.6667  |
| KING    | 2916.6667  |
| CLARK   | 2916.6667  |
| ADAMS   | 2175.0000  |
| SCOTT   | 2175.0000  |
| SMITH   | 2175.0000  |
| JONES   | 2175.0000  |
| FORD    | 2175.0000  |
| TURNER  | 1566.6667  |
| ALLEN   | 1566.6667  |
  • 在每个窗口（分组）内，如果我们想按每个人的薪水进行排序，可以使用 ORDER BY 子句，这里我们用 RANK() 指定序 号，相同的薪水序号是一样的：
SELECT ename, deptno, sal,
RANK() OVER(PARTITION BY deptno ORDER BY sal) AS salorder
FROM emp;
+---------+---------+-------+-----------+
| ename  | deptno  | sal  | salorder  |
+---------+---------+-------+-----------+
| MILLER  | 10      | 1300  | 1         |
| CLARK   | 10      | 2450  | 2         |
| KING    | 10      | 5000  | 3         |
| SMITH   | 20      | 800   | 1         |
| ADAMS   | 20      | 1100  | 2         |
| JONES   | 20      | 2975  | 3         |
| SCOTT   | 20      | 3000  | 4         |
| FORD    | 20      | 3000  | 4         |
| JAMES   | 30      | 950   | 1         |
| MARTIN  | 30      | 1250  | 2         |
| WARD    | 30      | 1250  | 2         |
| TURNER  | 30      | 1500  | 4         |
| ALLEN   | 30      | 1600  | 5         |
| BLAKE   | 30      | 2850  | 6         |
+---------+---------+-------+-----------+
  • 当 ORDER BY 与聚合函数一起使用时，会形成顺序聚合，如 SUM 聚合与 ORDER BY 结合使用时，就实现类似于累计和的效果：
SELECT ename, deptno, sal,
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) AS sumsal
FROM emp;
+---------+---------+-------+---------+
| ename  | deptno  | sal  | sumsal  |
+---------+---------+-------+---------+
| MILLER  | 10      | 1300  | 1300    |
| CLARK   | 10      | 2450  | 3750    |
| KING    | 10      | 5000  | 8750    |
| SMITH   | 20      | 800   | 800     |
| ADAMS   | 20      | 1100  | 1900    |
| JONES   | 20      | 2975  | 4875    |
| SCOTT   | 20      | 3000  | 10875   |
| FORD    | 20      | 3000  | 10875   |
| JAMES   | 30      | 950   | 950     |
| MARTIN  | 30      | 1250  | 3450    |
| WARD    | 30      | 1250  | 3450    |
| TURNER  | 30      | 1500  | 4950    |
| ALLEN   | 30      | 1600  | 6550    |
| BLAKE   | 30      | 2850  | 9400    |
+---------+---------+-------+---------+
  • 总结：
与GROUP BY 的区别：
结果数据形式
窗口函数可以在保留原表中的全部数据
GROUP BY 只能保留与分组字段聚合的结果
排序范围不同
窗口函数中的ORDER BY只是决定着窗口里的数据的排序方式
普通的ORDER BY决定查询出的数据以什么样的方式整体排序
SQL顺序
GROUP BY先进行计算
窗口函数在GROUP BY后进行计算
  • 移动窗口（滑动窗口）

移动方向(窗口范围子句)

current row ：当前行
preceding：向当前行之前移动
following：向当前行之后移动。
unbounded preceding：表示该窗口第一行（起点）
unbounded following：表示该窗口最后一行（终点）

当窗口函数使用order by没有使用窗口范围子句，等价于range between unbounded preceding and

current row 。

当窗口函数order by和窗口范围子句都没使用，等价于rows between unbounded preceding and unbounded following。

rows 2 preceding等价于 rows between 2 preceding and current row。

rows unbounded preceding 等价于 rows between unbounded preceding and current row

SELECT ename, deptno, sal,
-- 统计所有人薪资
SUM(sal) OVER() AS sumsal1,
-- 按部门统计所有人薪资（范围字句默认为：ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING）
SUM(sal) OVER(PARTITION BY deptno) AS sumsal2,
-- 起点到终点的窗口聚合，和 sumsal2 结果一样
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING) AS sumsal3,
-- 按部门统计所有人薪资，实现累计和的效果（范围字句默认为：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT
ROW）
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) AS sumsal4,
-- 起点到当前行的窗口聚合，和 sumsal4 结果一样
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
sumsal5,
-- 起点到当前行的窗口聚合，为了让大家区别 ROWS 和 RANGE（观察 sumsal5 和 sumsal6 的结果）
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
sumsal6,
-- 前面一行和当前行的窗口聚合
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sumsal7,
-- 前面一行和当前行和后面一行的窗口聚合
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sumsal8,
-- 当前行到终点的窗口聚合
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS
sumsal9
FROM emp
  • 分析型窗口函数
rank(): 间断，相同值同序号。例如：1 2 2 2 5 6 7 8
row_number():不间断，相同值不同序号。如：1 2 3 4 5 6 (5 6可能相同)
dense_rank():不间断，相同值同序号。如：1 2 3 4 4 4 5
percent_rank() 这行占整列的占比
cume_dist()： 计算小于等于当前行的值在所有行中的占比
ntile(N):如果把数据按行数分为 N 份，那么该行所属的份数是第几份。注意：N 必须为 INT 类型。

SELECT ename, deptno, sal,
PERCENT_RANK() OVER(PARTITION BY deptno ORDER BY sal) AS percent_rank_sal,
CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal) AS cume_dist_sal,
NTILE(3) OVER(PARTITION BY deptno ORDER BY sal) AS ntile_sal
FROM emp;
+---------+---------+-------+-------------------+----------------------+------------+
| ename  | deptno  | sal  | percent_rank_sal  |   cume_dist_sal     | ntile_sal  |
+---------+---------+-------+-------------------+----------------------+------------+
| MILLER  | 10      | 1300  | 0.0               | 0.3333333333333333   | 1          |
| CLARK   | 10      | 2450  | 0.5               | 0.6666666666666666   | 2          |
| KING    | 10      | 5000  | 1.0               | 1.0                  | 3          |
| SMITH   | 20      | 800   | 0.0               | 0.2                  | 1          |
| ADAMS   | 20      | 1100  | 0.25              | 0.4                  | 1          |
| JONES   | 20      | 2975  | 0.5               | 0.6                  | 2          |
| SCOTT   | 20      | 3000  | 0.75              | 1.0                  | 2          |
| FORD    | 20      | 3000  | 0.75              | 1.0                  | 3          |
| JAMES   | 30      | 950   | 0.0               | 0.16666666666666666  | 1          |
| MARTIN  | 30      | 1250  | 0.2               | 0.5                  | 1          |
| WARD    | 30      | 1250  | 0.2               | 0.5                  | 2          |
| TURNER  | 30      | 1500  | 0.6               | 0.6666666666666666   | 2          |
| ALLEN   | 30      | 1600  | 0.8               | 0.8333333333333334   | 3          |
| BLAKE   | 30      | 2850  | 1.0               | 1.0                  | 3          |
+---------+---------+-------+-------------------+----------------------+------------+
  • 取值函数
lag(col,N,default_Val):往前第n行，没有数据的话用default_val代替。
lead(col,N,default_val):往后第N行数据，没有数据的话用default_val代替。
first_value(expr):分组内第一个值，但是不是真正意义的第一行，而截取当前行的第一个。
last_value(expr):分组内最后一个值，但是不是真正意义上的最后一个，而是截止到当前行的最后一个。
  • 实践
SELECT ename, deptno, sal,
FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) AS firstsal,
LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) AS lastsal,
LAG(sal, 2, 1) OVER(PARTITION BY deptno ORDER BY sal) AS lagsal,
LEAD(sal, 2, -1) OVER(PARTITION BY deptno ORDER BY sal) AS leadsal
FROM emp;
  • 自定义函数

java代码要继承UDF、UDAF、UDAF父类，重写相关方式。

UDF:普通函数，一进一出，比如UPPER，LOWER

UDAF:聚合函数，多进一出 比如count

UDTF:表生成函数，一进多出，比如：lateral view explode() 侧视图

  • 主要是流程：（重点）

java打包成jar包，上传到HDFS文件系统中。

  • 创建自定义函数
create function hello_udf  as  'com.xxx.xxx.xxx.helloUDF'
using jar 'hdfs:///test/****.jar'
  • 重新加载函数
reload functions; -- 重新加载函数
desc function extended hello_UDF； -- 查看函数详细信息
drop function hello_UDF；  -- 删除自定义函数

九、案例练习

  • WordCount案例
select w.s,count(1) cnt  from (select explode(split(trim(regexp_replace(line,"[^\\w-']+"," ")),'\\s+')) s from t_wordcount) w group by w.s;
  • 天气信息

每个地区，每个月温度最高的三天和温度最低的三天是哪几天？

WITH temp AS (
SELECT area_code, FROM_UNIXTIME(UNIX_TIMESTAMP(report_time, 'd/M/yyyy HH:mm:ss'), 'yyyyMMdd') ymd, MAX(temperature) maxt,min(temperature) minx
FROM t_weather
GROUP BY area_code, FROM_UNIXTIME(UNIX_TIMESTAMP(report_time, 'd/M/yyyy HH:mm:ss'), 'yyyyMMdd')
ORDER BY area_code
), temp2 AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY area_code, FROM_UNIXTIME(UNIX_TIMESTAMP(ymd, 'yyyyMMdd'), 'yyyyMM') ORDER BY maxt DESC,minx) rk
FROM temp
ORDER BY area_code
)
SELECT * FROM temp2 WHERE rk <= 3;
  • 好友推荐

查询直接好友和间接好友的个数？

-- 处理后的临时表
with temp as
(
    select login_user,friend
    from t_friend
    lateral view explode(friends) fds as friend
-- 直接好友
),directFriend as (

    select login_user,friend,0 favtory
    from temp
    where login_user>friend
-- 间接好友
),nodirectFriend as(
  select t1.friend as login_user,t2.friend,1 favtory
  from temp t1
  join temp t2
  on t1.login_user=t2.login_user
  where t1.friend>t2.friend
),recomand as (
-- 推荐表
    select login_user,friend,favtory from directFriend
    union all
    select login_user,friend,favtory from nodirectFriend

)
-- 查看推荐好友的列表 having过滤掉相同的好友
select login_user,friend,count(favtory) si from recomand
group by login_user,friend
having count(favtory)=sum(favtory);

十、Hive压缩/存储

计算密集型少用压缩。

特点：要进行大量的计算，消耗 CPU 资源。比如计算圆周率、对视频进行高清解码等等，全靠 CPU 的运算能力。
计算密集型任务虽然也可以用多任务完成，但是任务越多，花在任务切换的时间就越多，CPU 执行任务的效率就越
低，所以，要最高效地利用 CPU，计算密集型任务同时进行的数量应当等于 CPU 的核心数。

IO密集型作业，多用压缩

特点：CPU 消耗很少，任务的大部分时间都在等待 IO 操作完成（因为 IO 的速度远远低于 CPU 和内存的速度）。
涉及到网络、磁盘 IO 的任务都是 IO 密集型任务。对于 IO 密集型任务，任务越多，CPU 效率越高，但也有一个限度。
常见的大部分任务都是 IO 密集型任务，比如 Web 应用。

image-20231104214207978

  • 检查hadoop支持哪些压缩方式？
hadoop checknative

存放数据到 HDFS 时，可以通过配置指定数据的压缩方式。当 MapReduce 程序读取数据时，会根据扩展名自动解 压。例如：如果文件扩展名为 .snappy ，Hadoop 框架会自动使用 SnappyCodec 解压缩文件。

  • Hive压缩实践
Hive属于hadoop客户端，所以Hive的压缩其实还是hadoop压缩，只是通过Hive来完成，不需要编写MR程序而已。所以第一个使用hadoop checknative命令，查看是否具有相应的压缩算法的库，如果显示false，则需要额外安装。
  • 在beeline会话中开启压缩支持
# 开启 Hive 中间传输数据压缩功能
SET hive.exec.compress.intermediate=true;
# 开启 Mapper 输出压缩
SET mapreduce.map.output.compress=true;
# 设置 Mapper 输出压缩的压缩方式
SET mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
  • 执行HQL的查询，通过日志查看是否启用压缩。
SELECT COUNT(*) FROM test.t_wordcount;

image-20231104215056491

  • reducer压缩

在 Hive 客户端通过命令的方式启用压缩。

# 开启 Hive 最终结果数据压缩功能
SET hive.exec.compress.output=true;
# 开启 Reducer 输出压缩
SET mapreduce.output.fileoutputformat.compress=true;
# 设置 Reducer 输出压缩的压缩方式
SET mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.BZip2Codec;
# SequenceFiles 输出可以使用的压缩类型：NONE、RECORD 或者 BLOCK
# 如果作业输出被压缩为 SequenceFiles，该属性用来控制使用的压缩格式。默认为 RECORD，即针对每条记录进行压缩，如果将其改为 BLOCK，将针对一组记录进行压缩，这是推荐的压缩策略，因为它的压缩效率更高。
SET mapreduce.output.fileoutputformat.compress.type=BLOCK;

导出执行hive文件，需要触发reducer阶段，通过最终文件查看是否启用压缩。

INSERT OVERWRITE DIRECTORY '/yjx/export/emp'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM scott.emp
DISTRIBUTE BY deptno SORT BY sal DESC;

image-20231104215548159

  • 存储方式

当今的数据处理大致分为两大类：OLTP和OLAP

image-20231104215727488

OLTP联机事务处理：OLTP是传统关系型数据库的主要应用，来执行一些基本的日常事务处理，比如数据库记录的增删改查等。

数据计算和数据存储分开，所有用户发过来的请求都是一个事件 Event，事件处理从关系型数据库中查询并进行返 回。特点实时性很好，来一个事件处理一个事件，额外数据存储在关系型数据库中。最大问题是能够同时处理的数据有 限，数据库做连表查询的代价很高。

OLAP联机分析处理：OLAP 则是分布式数据库的主要应用，它对实时性要求不高， 但处理的数据量大通常应用于复杂的动态报表系统上。

  • OLTP和PLAP区别：
一般 OLTP 都是使用行式存储，因为实时性要求高，而且有大量的更新操作；OLAP 都是使用列式存储，因为实时性要
求不高，主要是要求性能好。不过近些年基于实时查询的 OLAP 数据库和计算引擎也如雨后春笋般迅速崛起，例如
ClickHouse。
  • 行式存储
查询满足条件的一整行数据的时，只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
传统的关系型数据库，如 Oracle、DB2、MySQL、SQL SERVER 等都是采用行式存储，在基于行式存储的数据库中，数
据是按照行数据为基础逻辑存储单元进行存储的，一行中的数据在存储介质中以连续存储形式存在。Text File 和Sequence File 的存储格式都是基于行存储的。
  • 列式存储
与行存（将每一行的数据连续存储）不同，列存将每一列的数据连续存储。相比于行式存储，列式存储在分析场景下
有着许多优良的特性：
压缩效果显著、压缩后文件数据更小、插入和更新很麻烦，不适合少量数据，列式存储只需要读取参与计算的列，极大减少IO消耗，加速查询效率。
  • 存储格式

我们在选择合适的存储格式时要考虑一下好处：

保证读取/写入的速度 对压缩支持友好 文件可被切分 支持 Schema 的更改（Schema 指的是一组相关联的数据库对象，包含：表、字段、字段类型、索引、外键、等等）

  • hive常见的存储格式：
Text File：缺点：默认格式，存储方式为行存储，数据不做压缩，磁盘开销大，数据解析开销大，优点：简单易读、轻量级。

sequence file：Hadoop API 提供的一种支持二进制格式存储，存储方式为 行存储，其具有使用方便、可分割、可压缩的特点。Sequence File 支持三种压缩选择：NONE，RECORD，BLOCK。Record 压缩率低，一般建议使用 BLOCK 压缩。

Map file:Map File 是排序后的 Sequence File，Map File 由两部分组成，分别是 data 和 index。相对于Sequence File 而言，Map File 的检索效率是高效的，缺点是会消耗一部分内存来存储 index 数据。

Avro File:Apache Avro 是与语言无关的序列化系统，由 Hadoop 创始人 Doug Cutting 开发。Avro 是基于行的存储格式，它会在每个文件中包含 JSON 格式的 Schema 定义，从而提高了互操作性并允许 Schema 的变化（删除列、添加列）。 除了支持可切分以外，还支持块压缩。

RC File:RC File 是由二进制键/值对组成的 flat 文件，它与 Sequence File 有许多相似之处。在数仓中执行分析时，这种面向列的存储非常有用。无法直接将数据加载到 RC File 中。需要先将数据加载到另一个表中，然后通过查询另一张表的方式写入到新创建的 RC File 中。

ORC File：　Apache ORC（Optimized Row Columnar，优化行列）是 Apache Hadoop 生态系统面向列的开源数据存储格式，它与Hadoop 环境中的大多数计算框架兼容。ORC 代表“优化行列”，它以比 RC 更为优化的方式存储数据，提供了一种非常有效的方式来存储关系数据，然后存储 RC 文件。ORC 将原始数据的大小最多减少 75％，数据处理的速度大大提高。无法直接将数据加载到 ORC File 中。需要先将数据加载到另一个表中，然后通过查询另一张表的方式写入到新创建的 ORC File 中。

parquet file:与ORC File 一样，Parquet 也是基于列的二进制存储格式，可以存储嵌套的数据结构。当指定
要使用列进行操作时，磁盘输入/输出操效率很高。Parquet 还支持块压缩。与 RC 和 ORC 文件不同的是，Parquet Serdes 支持有限的 Schema 扩展。在 Parquet 中，可以在结构的末尾添加新列。关于Hive对parquet file文件格式的支持注意事项：parquet列名必须小写，这一点非常重要。如果parquet文件包含大小写混合的列名，则hive无法读取。
  • 总结
常用数据格式：ORC file、parquet file它非常适合进行压缩，具有出色的查询性能，尤其是从特定列查询数据时，效率很高，与非列文件格式相比，写入速度通常较慢，ORC file支持hive事务操作，事务表必须要使用ORC file数据文件格式。

image-20231104222326215

ORC 文件默认采用 ZLIB 压缩。ZLIB 压缩率比 Snappy 高，但是 ZLIB 解压缩速率很低。 载入数据（只有 TEXTFILE 才可以使用 LOAD DATA 的方式）,其他数据格式，全部要通过表数据写入。

十一、Hive优化

explain 查看执行计划，使用 EXPLAIN 可以将 SQL 语句的执行 过程打印出来，帮助我们选择更好的索引和写出更优化的查询语句。hive底层会自动优化join语句查询效率，这种优化叫谓词下推。

  • 语法
explain [SQL语句]
  • 实践
JOIN 语句会自动过滤null值。
GROUP BY 分组语句会进行自动进行升序排序

RBO(基于规则优化)

RBO：完全靠写sql经验，哪种写法性能高，就使用哪种写法！
  • 谓词下推
如 SQL 中的谓词主要有 LKIE 、 BETWEEN 、 IS NULL 、 IS NOT NULL 、 IN 、 EXISTS 等。　谓词下推（Predicate Pushdown）基本思想：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。在文件格式使用 Parquet 或 ORC 时，甚至可能整块跳过不相关的文件。

而 Hive 中的谓词下推主要思想是把过滤条件下推到 Map 端，提前执行过滤，以减少 Map 到 Reduce 的传输数据，提
升整体性能。简而言之，就是在不影响结果的情况下，尽量将过滤条件提前执行。


对于 JOIN(INNER JOIN)、FULL OUTER JOIN，条件写在 ON 后面，还是 WHERE 后面，性能上面没有区别；
对于 LEFT OUTER JOIN，右侧的表写在 ON 后面、左侧的表写在 WHERE 后面，性能上有提高；
对于 RIGHT OUTER JOIN，左侧的表写在 ON 后面、右侧的表写在 WHERE 后面，性能上有提高；
  • 列裁剪&常量替换
　列裁剪（Column Pruning）表示扫描数据源的时候，只读取那些与查询相关的字段。
　
　常量替换（Constant Folding）表示将表达式提前计算出结果，然后使用结果对表达式进行替换。假设我们在部门编号
上加的过滤条件是 deptno > 5 + 5 ，Catalyst 会使用 ConstantFolding 规则，自动帮我们把条件变成 deptno > 10 。再比如，我们在 SELECT 语句中，掺杂了一些常量表达式，Catalyst 也会自动地用表达式的结果进行替换。

CBO （基于代价优化）

RBO 就像一个老司机，基本优化套路全都知道。然而世界上有一种东西叫做——不按套路出牌。与其说它不按套路
来，倒不如说它本身并没有什么套路。最典型的莫过于复杂 JOIN 算子优化，对于这些 JOIN 来说，通常有两个选择题要
做.

它需要计算所有可能执行计划的代价，并挑选出代价最小的执行计划。
  • 要基于成本的优化，需要开启以下参数设置：
# 开启 CBO 优化，默认为 true
SET hive.cbo.enable=true;
# 统计 SQL 的查询结果是否从统计信息中获取，默认为 true
SET hive.compute.query.using.stats=true;
# 是否统计列信息，默认为 false
SET hive.stats.fetch.column.stats=true;
# 是否统计分区信息，默认为 true。3.1.1 版本被废弃，不允许用户修改该属性，因为禁用分区状态的获取可能会导致分区表出现问题
SET hive.stats.fetch.partition.stats=true;
  • join优化
小表join大表的Map join让表连接在Maptask阶段过滤后再进入reducer task中。
大表join大表分两种情况：
bucket Map join(中型表和大表join)
sort meger bucket join（大表和大表join）
  • Map join
通过 MapReduce Local Task 将小表读入内存，生成 HashTableFiles 上传至 Distributed Cache 中，这里会对HashTableFiles 进行压缩。

MapReduce Job 在 Map 阶段，每个 Mapper 从 Distributed Cache 读取 HashTableFiles 到内存中，顺序扫描大表，在Map 阶段直接进行 Join，将数据传递给下一个 MapReduce 任务。

应用场景：小表 JOIN 大表或者小表 JOIN 小表。

　  实现原理：Map Join 会把小表全部读入内存中，在 Map 阶段直接拿另外一个表的数据和内存中的表数据做匹配，由
于在 Map 阶段进行了 JOIN 操作，底层不需要经过 Shuffle，这样就不会由于数据倾斜导致某个 Reduce 上落数据太多而失败，但是需要占用内存空间存放小表数据。
  • reducer join
两张表 JOIN 的时候，小表不足以放到内存中，但是又想用 Map Join，这个时候就要用到 Bucket Map Join。其方法是两个 JOIN 表都在 Join Key上都做 Hash Bucket，并且把你打算复制的那个（相对）小表的 Bucket 数设置为大表的倍数。这样数据就会按照 Key Join 做 Hash Bucket。小表依然复制到所有节点，Map Join 的时候，小表的每一组 Bucket 加载成HashTable，与对应的一个大表 Bucket 做局部 JOIN，这样每次只需要加载部分 HashTable 就可以了。


大表的分桶数与小表的分桶相等或者是倍数关系。
SET hive.optimize.bucketmapjoin=true; ，默认为 false
所有要 JOIN 的表必须分桶，如果表不是 Bucket 的，则只是做普通 JOIN
大表的 Bucket 数是小表的 Bucket 数的整数倍（或相等）
Bucket 列 == JOIN 列
必须是应用在 Map Join 的场景中
  • SMB join
SMB Join 是基于 Bucket Map Join 的有序 Bucket，可实现在 Map 端完成 JOIN 操作，只要桶内的下一条不是，就不用再比较了，有效地减少或避免 Shuffle 的数据量。

所有要 JOIN 的表必须分桶，如果表不是 Bucket 的，则只是做普通JOIN大表的 Bucket 数 = 大表的 Bucket 数Bucket 列 == JOIN 列 == SORT 列必须是应用在 Bucket Map Join 的场景中

  • 分桶分区（SQL优化方案）

  • 数据倾斜

数据倾斜，即单个节点任务所处理的数据量远大于同类型任务所处理的数据量，导致该节点成为整个作业的瓶颈，这是分布式系统不可能避免的问题。

原因：
任务读取大文件，最常见的就是读取压缩的不可分割的大文件。

单表聚合操作，部分 Key 数据量较大，且大 Key 分布在很多不同的切片。

两表进行 JOIN，都含有大量相同的倾斜数据键。

数据含有大量无意义的数据，例如空值(NULL)、空字符串等。

含有倾斜数据在进行聚合计算时无法聚合中间结果，大量数据都需要经过 Shuffle 阶段的处理，引起数据倾斜。

数据在计算时做多维数据集合，导致维度膨胀引起的数据倾斜。

解决：压缩引发的数据倾斜，更换可切割的压缩格式，比如bzip2、lzo
单表数据倾斜优化:两阶段聚合（加盐局部聚合 + 去盐全局聚合）+ Map-Side 聚合（开启 Map 端聚合或自定义Combiner）。
把数据key拼接随机值打散，然后他们就会在不同task上进行出来，然后去除随机值，再进行规并排序。

Map join：小表加载到内存中，顺序扫描大表完成join。小表join大表

采样倾斜 Key 并分拆 JOIN：将存在倾斜的表，根据抽样结果，拆分为倾斜 Key（Skew 表） 和没有倾斜 Key（Normal 表）的两个数据集。

将 Skew 表的 Key 全部加上随机前缀，然后对另外一个不存在严重数据倾斜的数据集整体与随机前缀集做笛卡尔乘积
（即将数据量扩大 N 倍，得到 New 表）。

打散的 Skew 表 JOIN 扩容的 New 表，普通表 JOIN 未扩容的 Old 表，最后将这两部分结果 UNION ALL。
  • 向量化查询
总结下来就是：让计算更多的停留在函数内，而不是频繁的交互切换，提高了 CPU 的流水线并行度。数据不仅按列式
存储，而且按列（Batch Data）计算。

开启向量化查询后，Hive 会将一个普通的查询转化为向量化查询执行。它大大减少了扫描、过滤器、聚合和连接等典
型查询操作的 CPU 使用。标准查询执行系统一次处理一行。向量化查询执行可以一次性处理 1024 行的数据块，以减少底
层操作系统处理数据时的指令和上下文切换.

-- 开启向量化查询，默认为 true
SET hive.vectorized.execution.enabled=true;
-- 开启 Reduce 任务的向量化执行模式，默认为 true（MR 计算引擎不支持，需要配置 Tez/Spark 计算引擎使用）
SET hive.vectorized.execution.reduce.enabled=true;
  • 存储优化
Hive 的存储本质还是 HDFS，而 HDFS 是不利于小文件存储的，因为每个小文件都会产生一条元数据信息，并且不利
于 MapReduce 的处理，MapReduce 中每个小文件都会启动一个 MapTask，导致资源浪费，所以在使用 Hive 进行处理时，要尽量避免小文件的生成。

-- 在 Map-Only 任务结束时合并小文件，默认为 true
SET hive.merge.mapfiles=true;
-- 在 Map-Reduce 任务结束时合并小文件，默认为 false
SET hive.merge.mapredfiles=true;
-- 合并文件的大小，默认约 244M
SET hive.merge.size.per.task=256000000;
-- 平均每个文件的大小，如果小于该值则进行合并，默认约 15M
SET hive.merge.smallfiles.avgsize=16000000;
  • 　对于小文件合并 Hive 底层是如何处理的呢？Hive 中提供了一个 CombineHiveInputFormat 类专门用于小文件合并 （默认启用）。相关配置命令如下：
SET hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
  • yarn优化 (调整NodeManager中container大小,以及小文件大小是否需要合并)
-- 配置每个容器请求被分配的最小内存。如果容器请求的资源小于该值，会以 1024MB 进行分配；如果 NodeManager 可被分配的内存小于该值，则该 NodeManager 将会被 ResouceManager 给关闭。默认值 1024MB。
SET yarn.scheduler.minimum-allocation-mb=1024;
-- 配置每个容器请求被分配的最大内存。如果容器请求的资源超过该值，程序会抛InvalidResourceRequestException异常。默认值 8192MB。 
SET yarn.scheduler.maximum-allocation-mb=8192;
-- 配置每个容器请求被分配的最小虚拟 CPU 个数，低于此值的请求将被设置为此属性的值。此外，配置为虚拟内核少于此值的 NodeManager 将被 ResouceManager 关闭。默认值 1。
SET yarn.scheduler.minimum-allocation-vcores=1;
-- 配置每个容器请求被分配的最大虚拟 CPU 个数，高于此值的请求将抛出 InvalidResourceRequestException 的异常。如果开发者所提交的作业需要处理的数据量较大，需要关注上面配置项的配置。 
SET yarn.scheduler.maximum-allocation-vcores=4;
-- 配置一个节点内所有容器所能使用的物理 CPU 的占比，默认为 100%。即如果一台机器有 16 核，CPU 的使用率最大为 16 核，该比值为 100%，如果该比值为 50%，则所有容器能使用的 CPU 资源为 8 核。 
SET yarn.nodemanager.resource.percentage-physical-cpu-limit=100;
-- 配置是否开启 CPU 的共享模式。共享模式告诉系统容器除了能够使用被分配的 CPU 资源外，还能使用空闲的 CPU 资源。默认值 false。
SET yarn.nodemanager.linux-container-executor.cgroups.strict-resource-usage=false;
  • 并行执行
-- Hive 在实现 HQL 计算运行时，会解析为多个 Stage，有时候 Stage 彼此之间有依赖关系，只能挨个执行。但有些时候，很多 Stage 之间是没有依赖关系的，例如 UNION 语句，JOIN 语句等等，这些 Stage 没有依赖关系，但是 Hive 依旧会挨个执行每个 Stage，这样会导致性能非常的差。我们可以通过修改参数，开启并行执行，当多个 Stage 之间没有依赖关系时，允许多个 Stage 并行执行，提高性能。

-- 开启任务并行执行，默认为 false
SET hive.exec.parallel=true;
-- 同一条 SQL 的并行化线程数，默认为 8
SET hive.exec.parallel.thread.number=8;

聚合优化

  • group by优化
默认情况下，Map 阶段同一Key 数据会分发给一个 Reduce，当一个 Key 数据过大时就会产生数据倾斜。但并不是所
有的聚合操作都需要在 Reduce 端完成，很多聚合操作也可以先在 Map 端进行部分聚合，最后再在 Reduce 端得出最终结果。
  • 开启Map端聚合配置
-- 是否开启 Map 端聚合，默认为 true
SET hive.map.aggr=true;
-- Map 端进行聚合操作的条目数上限，默认为 100000
SET hive.groupby.mapaggr.checkinterval=100000;

-- 开启该参数后，当前程序会自动通过两个作业来运行任务，实现数据倾斜的负载均衡处理，默认为 false
SET hive.groupby.skewindata=true;
  • order by 优化
必须先进行局部排序后，再进行全局优化。

如下：

SELECT * FROM (
SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal
) t
ORDER BY t.sal DESC;
  • count(distinct)优化
-- 改写前
SELECT COUNT(DISTINCT job) FROM emp;
-- 改写后
SELECT COUNT(*) FROM (SELECT job FROM emp GROUP BY job) e;
  • job优化
Map优化、reducer优化：
控制 Map 数量需要遵循两个原则：使大数据量利用合适的 Map 数；使单个 Map 任务处理合适的数据量。
控制 Reduce 数量需要遵循两个原则：使大数据量利用合适的 Reduce 数；使单个 Reduce 任务处理合适的数据量。
  • shuffle优化
目的：压缩中间数据，从而减少磁盘操作以及减少网络传输数据量.
  • Fetch 模式
当我们执行一个简单的 HQL 语句时，例如 SELECT * FROM emp; ，发现数据可以很快的返回，这其实涉及到 Fetch
抓取的概念。简单的SQL不执行MR程序。
该配置默认值为 more ，如果设置为 none ，那么每次执行 HQL 都会执行 MapReduce 程序。
  • 多重优化
单个表数据插入不同表中，可以使用如下的SQL语句：一次读取，多次插入，对于需要从一张表读取数据后并多次使用的场景非常管用。

FROM t_user
INSERT INTO t_user1 SELECT id, username
INSERT INTO t_user2 SELECT id, password
INSERT OVERWRITE TABLE t_user3 SELECT id, username
INSERT OVERWRITE TABLE t_user4 SELECT id, password;
  • 关联优化
-- 开启关联关系优化，默认为 false
SET hive.optimize.correlation=true;
  • 本地模式
使用 Hive 的过程中，有一些数据量不大的表也会转换为 MapReduce 执行。而只要提交到集群，就需要申请资源、等
待资源分配、启动 JVM 进程，再运行 Task，一系列的过程比较繁琐。Hive 为了解决这个问题，延用了 MapReduce 中的设计，提供了本地计算模式，允许程序不提交给 YARN，直接在本地运行，以便于提高小数据量程序的性能。对数据量比较
小的操作，就可以在本地执行，这样要比提交任务到集群执行快很多。

-- 开启本地模式  配置好相关参数  数据量的小的可以直接实在本地进行运算，增加查询效率！
SET hive.exec.mode.local.auto=true;
  • 严格模式
开启严格模式，必须带有条件过滤查询，全部排序时必须限制查询行数，不能使用笛卡尔积查询，必须使用join、on关键字。
  • 推测模式
推测执行(Speculative Execution)是通过利用更多的资源来换取时间的一种优化策略，但是在资源很紧张的情况下，推测执行也不一定能带来时间上的优化，假设在测试环境中，DataNode 总的内存空间是 40G，每个 Task 可申请的内存设置为 1G，现在有一个任务的输入数据为 5G，HDFS 分片为 128M，这样 MapTask 的个数就 40 个，基本占满了所有的
DataNode 节点，如果还因为某些 MapTask 运行过慢，启动了 Speculative Task，这样就可能会影响到 ReduceTask 的执行，自然而然就使整个 Job 的执行时间延长。所以是否启用推测执行，需要根据资源情况来决定，如果在资源本身就不够的情况下，还要跑推测执行的任务，这样会导致后续启动的任务无法获取到资源，以导致无法执行。
posted @   戴莫先生Study平台  阅读(204)  评论(0)  编辑  收藏  举报
相关博文：
· Spark Core快速入门
· Hadoop快速入门
· Hive的简单介绍与使用
· Hive-day03_hive介绍和三种交互方式
· Hive-day04 Hive基本概念
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示