Apache Hive基础知识

终回首

于 2021-05-05 12:27:12 发布

阅读量1.5k

点赞数 4

分类专栏：笔记 # Apache Hive 文章标签：大数据 hive hadoop

本文链接：https://blog.csdn.net/qq_39945938/article/details/115643823

版权

笔记同时被 2 个专栏收录

13 篇文章 1 订阅

订阅专栏

Apache Hive

12 篇文章 1 订阅

订阅专栏

你想要什么？你在做什么？它们一样吗？你今天比昨天更好吗？

文章目录

一、简介
二、架构
- 1 Hive语句的执行过程
- 2 元数据的三种模式
三、客户端命令行工具
- Hive Cli
- Beeline Cli
四、数据类型
- 基本数据类型
- 复合数据类型
五、语法
六、元数据
七、Hive优化
参考资料

我的hive学习笔记

一、简介

Apache Hive是一个提供访问HDFS上存储数据的SQL接口的数据仓库。适合存储数据不会频繁变化，且不需要快速响应给出结果的场景。Hive2.1.0暂不支持记录级别的更新、删除。

二、架构

在这里插入图片描述

JobTracker负责分配任务和资源管理
TaskTracker负责具体执行
Driver负责SQL编译、优化、执行
MetaStore存储元数据

1 Hive语句的执行过程

在这里插入图片描述
Hive 这一侧，总共有五个组件：

UI：用户界面。可看作我们提交SQL语句的命令行界面。
DRIVER：驱动程序。接收查询的组件。该组件实现了会话句柄的概念。
COMPILER：编译器。负责将 SQL 转化为平台可执行的执行计划。对不同的查询块和查询表达式进行语义分析，并最终借助表和从 metastore 查找的分区元数据来生成执行计划。
METASTORE：元数据库。存储 Hive 中各种表和分区的所有结构信息。
EXECUTION ENGINE：执行引擎。负责提交 COMPILER 阶段编译好的执行计划到不同的平台上。

图里流程详细情况

步骤1：UI 调用 DRIVER 的接口；
步骤2：DRIVER 为查询创建会话句柄，并将查询发送到 COMPILER(编译器)生成执行计划；
步骤3和4：编译器从元数据存储中获取本次查询所需要的元数据，该元数据用于对查询树中的表达式进行类型检查，以及基于查询谓词修建分区；
步骤5：编译器生成的计划是分阶段的DAG，每个阶段要么是 map/reduce 作业，要么是一个元数据或者HDFS上的操作。将生成的计划发给 DRIVER。
如果是 map/reduce 作业，该计划包括 map operator trees 和一个 reduce operator tree，执行引擎将会把这些作业发送给 MapReduce ：
步骤6、6.1、6.2和6.3：执行引擎将这些阶段提交给适当的组件。在每个 task(mapper/reducer) 中，从HDFS文件中读取与表或中间输出相关联的数据，并通过相关算子树传递这些数据。最终这些数据通过序列化器写入到一个临时HDFS文件中（如果不需要 reduce 阶段，则在 map 中操作）。临时文件用于向计划中后面的 map/reduce 阶段提供数据。
步骤7、8和9：最终的临时文件将移动到表的位置，确保不读取脏数据(文件重命名在HDFS中是原子操作)。对于用户的查询，临时文件的内容由执行引擎直接从HDFS读取，然后通过Driver发送到UI。

2 元数据的三种模式

	1 单用户模式（不建议使用此模式）
		此模式连接到一个In-memory的数据库Derby，同一时间只允许一个用户连接
	2 多用户模式（推荐使用此模式）
		通过网络连接远程数据库，允许多个用户连接
	3 远程服务模式
		多了一层封装，可以提供用户通过Thrift协议调用服务来访问元数据

三、客户端命令行工具

Hive Cli

Hive Cli是一个客户端命令行界面，是和Hive交互的最常用的方式，使用Cli，用户可以建表、导入数据、查询等等。

注意：Hive1.0.0 后 Hive Cli不推荐使用，推荐的是Beeline Cli

案例：·

# 切换到hdfs用户
su hdfs
# 启动Hive Cli
hive
# 查看所有数据库
show Databases;
# 使用default数据库
use default;
# 查看所有的表
show tables;

Hive Cli 官方参考文档：
https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-RunningHiveCLI

Beeline Cli

Hive 1.0.0后推荐使用Beeline Cli

案例：

# 切换到hdfs用户
su hdfs
# 启动beeline
beeline 
# 连接指定ip,port和用户名密码的HiveServer2
!connect jdbc:hive2://localhost:10000 username passward
# 查看所有数据库
show databases;
# 使用default;
use default;
# 查看所有表
show tables;

Beeline 官方参考文档：
https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-Beeline%E2%80%93NewCommandLineShell

四、数据类型

基本数据类型

类型	描述	示例
tinyint	1个字节有符号整数	1
smallint	2个字节有符号整数	2
int	4个字节有符号整数	3
bigint	8个字节有符号整数	4
flout	4个字节单精度浮点数	1.0
double	8个字节双精度浮点数	1.0
boolean	布尔值	true
string	字符串，无长度限制	“hello”

复合数据类型

类型	描述	示例
ARRAY	有序列表，字段类型必须相同	array(1,2,3)
MAP	无序key-value映射，key的类型必须为基本类型，value可以是任意类型。同一个MAP中key的类型必须相同，value的类型也必须相同	map(“a”:1,“b”:2,“c”:3)
struct	可以包含不同数据类型的元素,多个字段为一组	struct(“zhangsan”,23,3000)

数据类型官方参考文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Overview

五、语法

这里只详细记录常用的，冷门操作可以看官方文档

1 DDL

数据库定义语言

DDL 官方文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Overview

CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
TRUNCATE TABLE
ALTER DATABASE/SCHEMA, TABLE, VIEW
MSCK REPAIR TABLE (or ALTER TABLE RECOVER PARTITIONS)
SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, VIEWS, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS, CREATE TABLE
DESCRIBE DATABASE/SCHEMA, table_name, view_name, materialized_view_name

1.1 Create/Drop/Alter/Use Database

数据库的创建、删除、修改、使用操作

1.1.1 Create Database

创建数据库

语法：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

DATABASE和SCHEMA是一个意思，类似Mysql里的Database
COMMENT 备注，添加备注后，查看库的时信息可以看到
LOCATION 默认库的路径，创建内部表后，内部表的默认父路径
MANAGEDLOCATION 默认的管理库的路径，创建外部表后，外部表的默认父路径
DBPROPERTIES 自定义设置一些属性，比如可以指定username=zhangsan,create_time=2021-12-12 12:12:12

示例：

create database if not exists test_db_1 
comment "test database"  
with dbproperties (create_user=zhangsan);

1.1.2 Use Database

使用数据库

语法：

USE database_name;

示例：

use test_db_1 ;

1.1.3 Drop Database

删除数据库

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

CASCADE 假如数据库里有表，默认删除时会提示无法删除，此时加上CASCADE 即可强制删除（慎用）

案例：

# 强制删除数据库，即使有表存在也会一起删除
drop database if exists test_db_1 cascade;

1.2 Create/Drop/Truncate Table

表的创建、删除、截断操作

1.2.1 Create Table

语法：

-- 语法1-直接建表
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

-- 语法2-使用现有表建表
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

关键词解释：

TEMPORARY 建表时添加这个关键字建好的表是临时表，当前会话退出后表就会被删除，用的较少
EXTERNAL 建表时指定external的表是外部表，什么是外部表下面有介绍
IF NOT EXISTS 如果表不存在则创建，存在则正常退出
db_name. 指定要创建的表位于哪个数据库
table_name 指定表名
col_name data_type COMMENT col_comment 字段名和字段对应的类型，COMMENT的意思是申明字段的备注，备注内容为col_comment；可以有多个
COMMENT table_comment 申明表的备注，备注内容为table_comment
PARTITIONED BY (col_name data_type [COMMENT col_comment], …) 指定根据哪些字段分区，col_name data_type COMMENT col_comment 指定分区字段名和字段类型、备注和备注内容；可以有多个
CLUSTERED BY (col_name, col_name, …) [SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS
CLUSTERED BY申明分桶，根据类型为字段名为col_name等一个或多个字段分桶，SORTED BY根据列名为col_name的字段进行排序，asc是顺序，desc是倒序，INTO num_buckets BUCKETS指定分几个桶，桶的数量是num_buckets
SKEWED BY (col_name, col_name, …) 指定数据倾斜列，可以提高有数据倾斜列时的查询性能
ON ((col_value, col_value, …) 指定具体的倾斜列的倾斜的值
STORED AS DIRECTORIES 指定使用列表桶，为倾斜的字段的值创建子目录，查询时提高性能
ROW FORMAT row_format 指定字段、array、map、行按照什么规则切分，切分规则如下
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] – (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
STORED AS file_format 指定文件格式，支持TEXTFILE（默认）、SEQUENCEFILE、RCFILE、ORC、PARQUET、AVRO、JSONFILE（Hive4.0.0后支持的类型，其他类型都是1.0.0版本之前就支持的类型）
LOCATION hdfs_path 指定表在HDFS上的存储路径
TBLPROPERTIES (property_name=property_value, …) 指定表的自定义属性，既可以修改预先定义好的属性也可以自定义添加新属性，这里类似数据库的自定义属性
AS select_statement 以查询结果作为表结构

Hive表有四种，内部表，外部表，分区表，分桶表

内部表是不指定external和temporary时默认的建表方式，这种方式创建的表被删除时，数据也会被一起删除，表定义和数据强关联
外部表是指定external的表，这种方式创建的表被删除时，数据不会被一起删除，表定义和数据弱关联
分区表就是有分区的表，分区就是把数据安装某个或多个字段分别存在不同的目录下。在查询时可以根据分区筛选数据，减少读取的数据量
分桶表

案例：

-- 内部表
create table if not exists default.test_tb_1(
	user_id int "user's id",
	user_name string "user's name"
);
-- 外部表
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>';
-- 分区表
create external table if not exists default.test_tb_2(
	user_id int "user's id",
	user_name string "user's name"
)partitioned by (create_date string,org_num int);
-- 分桶表
CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;

1.2.2 Drop Table

删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];

关键字解释：

PURGE 跳过回收站，在不指定purge的默认情况下，数据会进入HDFS的.Trash/Current路径下同时表定义会被完全删除，误删后可以通过此项找回。但指定purge后，数据将被跳过回收站直接删除（慎用）

案例：

drop table if exists default.test_tb_1;

1.2.3 Truncate Table

清空表，只能清空内部表或内部表分区的数据
语法：

TRUNCATE TABLE table_name [PARTITION partition_spec];

关键字解释：

table_name 要清空的表名
PARTITION partition_spec 要清空具体分区

案例：

truncate table default.test_tb_1;

1.2.3 Alter Table/Partition/Column

修改表、分区、列
内容较多，常用的不多，建议直接查看官方文档

此部分内容的官方文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable

2 DML

数据处理语言

2.1 将数据导入表

注意：

多个分区字段是有先后顺序的；
动态插入分区表时需要开启动态分区（set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict;）;
导入分区表或动态插入分区表时，分区字段必须按顺序排到所有非分区字段后

2.1.1 Loading files into tables

将文件导入到表

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

关键词意思：

LOCAL 指定文件存放在本地文件系统，不指定LOCAL默认为HDFS文件系统；从本地导入时是复制，导入表后原有数据仍旧存在；从HDFS导入时，是移动，导入后原路径数据会消失；
filepath 文件的路径
OVERWRITE 指定清空表再导入新数据，默认为append（追加）
tablename 要导入的表
PARTITION (partcol1=val1, partcol2=val2 …) 如果上面的表是分区表，那么需要指定要导入的分区；有多个分区需要指定多个分区

案例：

# 导入本地的数据到非分区表,追加导入
LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' INTO TABLE test_1;
# 导入本地的数据到分区表的指定分区，追加导入
LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' INTO TABLE test_1 PARTITIO(pt='20200101');

# 导入HDFS的数据到非分区表，覆盖原有数据
LOAD DATA INPATH '/home/admin/test/test.txt' OVERWRITE  INTO TABLE test_2;
# 导入HDFS的数据到分区表的指定分区，覆盖原有数据
LOAD DATA INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1 PARTITIO(pt='20200101');

2.1.2 Inserting data into Hive Tables from queries

从查询插入数据到hive表

语法：

# 标准语法
	#覆盖原有数据
	INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
	# 追加插入
	INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

# 动态分区插入
INSERT OVERWRITE|INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

案例：

# 插入非分区表，覆盖原有数据
insert overwrite table tablename1 select a, b, c from test_1;
# 插入非分区表，覆盖原有数据
insert overwrite table tablename1 select a, b, c from test_1;
# 插入分区表，以追加的方式
insert into table tablename1 partition (create_date='20210101',org='1') select a, b, c from test_1;

# 一个静态分区，一个动态分区，插入分区表
insert overwrite table page_view partition (dt='2008-06-08', country) select pvs.user_id, pvs.user_name from page_view_stg pvs;
# 动态插入分区表,2个动态分区，不包括静态分区
insert overwrite table test_1(create_date, org) select user_id, user_name,create_date,org from tablename1;

动态分区需要注意调整的参数

参数名	默认值	意思
hive.exec.dynamic.partition	true	设置为true，表示开启动态分区
hive.exec.dynamic.partition.mode	strict	当处于strict模式时，用户至少需要指定一个静态分区；在nonstrict 模式时，允许分区都是动态分区
hive.exec.max.dynamic.partitions.pernode	100	每个mapper/reducer被允许创建的动态分区数的最大值
hive.exec.max.dynamic.partitions	1000	允许创建的动态分区数的最大值
hive.exec.max.created.files	100000	hive任务的mapper/reducer创建的文件数的个数
hive.error.on.empty.partition	false	动态分区插入产生空结果时是否抛出异常

2.1.3 Writing data into the filesystem from queries

将查询出的数据导出到文件系统

语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)

案例：

# 将表数据导出到本地文件系统，分割符为逗号
insert OVERWRITE LOCAL DIRECTORY '/home/hadoop/local_test_output' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from test_1;
# 将表数据导出到HDFS文件系统，分隔符为逗号
insert OVERWRITE DIRECTORY '/home/hadoop/hdfs_test_output' ROW FORMAT DELIMITED FIELDS TERMINATED by ',' select * from test_1;

注意导出到文件系统时，启动用户要有输出目录的写权限。

DML 官方文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

3 DQL

数据查询语言

语法：

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [HAVING having_condition]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]

关键字解释：

WITH CommonTableExpression (, CommonTableExpression)*
定义CTE（公共表表达式），定义好后，一个查询里可以多次引用定义好的公共表
SELECT [ALL | DISTINCT] select_expr, select_expr, …
DISTINCT指定时，查询返回去重后的结果，不指定默认为ALL，返回所有数据
select_expr 查询的列或者表达式，例如user_id，max(user_id)
FROM table_reference
table_reference 表名
WHERE where_condition
where_condition 筛选条件，对select到的列的值进行筛选，保留满足条件的
GROUP BY col_list
col_list 分组依据的列
HAVING having_condition
having_condition 分组后，组内数据的筛选条件，保留满足条件的
ORDER BY col_list
col_list 根据指定的字段排序，全局排序；order by 是在一个reduce里执行，所以数据量大时，速度很慢，可以通过嵌套排序加快速度
CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list
CLUSTER BY col_list 根据指定的列排序，全局排序，只能降序
DISTRIBUTE BY col_list 根据指定的字段分区，同一个字段的同一个值落在一个分区里
SORT BY col_list 每个reducer里排序，非全局排序
LIMIT [offset,] rows
限制返回的条数，offset表示返回的数据从第几行开始，rows表示返回几行

案例：

# 定义公共表，查询公共表
with 
t1 as select * from test_1
select * from t1;

# 查询
select org,sum(age)
from t_user
where user_id > 1
group by org
having avg(age) > 35
order by org;

3.1 语句执行顺序

Hive SQL执行顺序

-- 正常SQL顺序
select … from … where … group by … having … order by … limit …
-- 真正执行顺序
from … join … where … select … group by … having … order by … limit …

我的理解：

from 找到从哪个或哪几个表找数据
where 根据where条件留下符合条件的数据，这个是过滤不需要的行
select 留下要查询的字段，这个是过滤不需要的列
group by 根据哪些字段分组
having 组内过滤
order by 排序
限制返回行数

3.2 操作符和用户自定义函数

此部分内容较多，且官方文档就很容易理解，直接看官方文档即可

官方文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

4 开窗函数、分析函数

4.1 WINDOW子句

用于指定窗口范围

PRECEDING # 往前
FOLLOWING # 往后
CURRENT ROW # 当前行
UNBOUNDED # 起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING # 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING # 表示该窗口最后面的行（终点）

示例：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW #（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING #（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW #（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING #（表示当前行到终点）

不支持window子句的函数

Lead、Lag、Rank、NTile、DenseRank、CumeDist、PercentRank

4.2 窗口函数

建表语句

create table test_user_login(
  user_name string, 
  login_date string, 
  pv long
) row format delimited fields terminated by ',';

测试数据

user_name	login_date	pv
zhangsan	2021-01-01	3
zhangsan	2021-01-02	4
lisi	2021-01-01	5
lisi	2021-01-02	6
wangwu	2021-01-01	2

4.2.1 LEAD

LEAD(col, n, DEFAULT) 用于查询窗口内往下第n行值

第一个参数是列名
第二个参数为往下第n行（可选，默认为1），
第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

案例

# 查询用户当前行的下一次登录的pv
select 
  user_name, 
  login_date, 
  pv, 
  LEAD(pv, 1, 0) over (
    partition by user_name 
    order by 
      login_date
  ) as lead_1_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	pv	lead_1_pv
zhangsan	2021-01-01	3	4
zhangsan	2021-01-02	4	0
lisi	2021-01-01	5	6
lisi	2021-01-02	6	0
wangwu	2021-01-01	2	0

4.2.2 LAG

LAG(col, n, DEFAULT) 用于查询窗口内往上第n行值

第一个参数col是列名
第二个参数为往上第n行（可选，默认为1）
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

案例

# 查询用户当前行的上一次登录的pv
select 
  user_name, 
  login_date, 
  pv, 
  LAG(pv, 1, 0) over (
    partition by user_name 
    order by 
      login_date
  ) as lead_1_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	pv	lead_1_pv
zhangsan	2021-01-01	3	0
zhangsan	2021-01-02	4	3
lisi	2021-01-01	5	0
lisi	2021-01-02	6	5
wangwu	2021-01-01	2	0

4.2.3 FIRST_VALUE

FIRST_VALUE(col, flag)取分组内排序后，截止到当前行，第一个值

第一个参数是您想要第一个值的列
第二个（可选）参数默认情况是false，不跳过空值。如果设置为 true 则跳过空值

案例

# 查询用户第一次登录日期和pv
select 
  user_name, 
  login_date, 
  FIRST_VALUE(login_date) over (
    partition by user_name 
    order by 
      login_date
  ) as first_login_date, 
  ROW_NUMBER() over (
    partition by user_name 
    order by 
      login_date
  ) AS rn, 
  FIRST_VALUE(pv) over (
    partition by user_name 
    order by 
      login_date
  ) as first_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	first_login_date	pv	rn	first_pv
zhangsan	2021-01-01	2021-01-01	3	1	3
zhangsan	2021-01-02	2021-01-01	4	2	3
lisi	2021-01-01	2021-01-01	5	1	5
lisi	2021-01-02	2021-01-01	6	2	5
wangwu	2021-01-01	2021-01-01	2	1	2

加window子句案例

# 查询用户第一次登录日期和pv
select 
  user_name, 
  login_date, 
  FIRST_VALUE(login_date) over (
    partition by user_name 
    order by 
      login_date
  ) as first_login_date, 
  ROW_NUMBER() over (
    partition by user_name 
    order by 
      login_date
  ) AS rn, 
  FIRST_VALUE(pv) over (
    partition by user_name 
    order by 
      login_date
  ) as first_pv, 
  FIRST_VALUE(pv) over (
    partition by user_name 
    order by 
      login_date rows between unbounded preceding 
      and current row
  ) as window_first_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	first_login_date	pv	rn	first_pv	window_first_pv
zhangsan	2021-01-01	2021-01-01	3	1	3	3
zhangsan	2021-01-02	2021-01-01	4	2	3	3
lisi	2021-01-01	2021-01-01	5	1	5	5
lisi	2021-01-02	2021-01-01	6	2	5	5
wangwu	2021-01-01	2021-01-01	2	1	2	2

4.2.3 LAST_VALUE

FIRST_VALUE(col, flag)取分组内排序后，截止到当前行，取第一个值

第一个参数是您想要第一个值的列
第二个（可选）参数默认情况是false，不跳过空值。如果设置为 true 则跳过空值

案例

# 查询用户最后一次登录日期和pv
select 
  user_name, 
  login_date, 
  LAST_VALUE(login_date) over (
    partition by user_name 
    order by 
      login_date
  ) as last_login_date, 
  ROW_NUMBER() over (
    partition by user_name 
    order by 
      login_date
  ) AS rn, 
  LAST_VALUE(pv) over (
    partition by user_name 
    order by 
      login_date
  ) as last_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	last_login_date	pv	rn	last_pv
zhangsan	2021-01-01	2021-01-02	3	1	4
zhangsan	2021-01-02	2021-01-02	4	2	4
lisi	2021-01-01	2021-01-02	5	1	6
lisi	2021-01-02	2021-01-02	6	2	6
wangwu	2021-01-01	2021-01-01	2	1	2

加window子句案例

# 查询用户最后一次登录日期和pv
select 
  user_name, 
  login_date, 
  LAST_VALUE(login_date) over (
    partition by user_name 
    order by 
      login_date
  ) as last_login_date, 
  ROW_NUMBER() over (
    partition by user_name 
    order by 
      login_date
  ) AS rn, 
  LAST_VALUE(pv) over (
    partition by user_name 
    order by 
      login_date
  ) as last_pv, 
  LAST_VALUE(pv) over (
    partition by user_name 
    order by 
      login_date rows between unbounded preceding 
      and current row
  ) as window_last_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	last_login_date	pv	rn	last_pv	window_last_pv
zhangsan	2021-01-01	2021-01-02	3	1	4	3
zhangsan	2021-01-02	2021-01-02	4	2	4	4
lisi	2021-01-01	2021-01-02	5	1	6	5
lisi	2021-01-02	2021-01-02	6	2	6	6
wangwu	2021-01-01	2021-01-01	2	1	2	2

4.3 聚合函数

目前支持5个带有聚合意义的窗口函数

COUNT
SUM
MIN
MAX
AVG

以SUM为例

select 
  user_name, 
  login_date, 
  pv, 
  ROW_NUMBER() over (
    partition by user_name 
    order by 
      login_date
  ) AS rn, 
  sum(pv) over (
    partition by user_name 
    order by 
      login_date
  ) as sum_pv_1, --默认情况，包括从开始到结束的所有行
  sum(pv) over (
    partition by user_name 
    order by 
      login_date ROWS BETWEEN UNBOUNDED PRECEDING 
      AND CURRENT ROW
  ) as sum_pv_2, --表示从起点到当前行
  sum(pv) over (partition by user_name) as sum_pv_3, --表示窗口内所有行
  sum(pv) over (
    partition by user_name 
    order by 
      login_date ROWS BETWEEN UNBOUNDED PRECEDING 
      AND UNBOUNDED sum
  ) as sum_pv_4, --表示起点到终点
  SUM(pv) over (
    partition by user_name 
    order by 
      login_date ROWS BETWEEN 2 PRECEDING 
      AND 1 FOLLOWING
  ) as sum_pv_5 --表示前2行到后面1行
from 
  default.test_user_login;

查询结果

user_name	login_date	pv	rn	sum_pv_1	sum_pv_2	sum_pv_3	sum_pv_4	sum_pv_5
zhangsan	2021-01-01	3	1	7	3	7	7	7
zhangsan	2021-01-02	4	2	7	7	7	7	7
lisi	2021-01-01	5	1	11	5	11	11	11
lisi	2021-01-02	6	2	11	11	11	11	11
wangwu	2021-01-01	2	1	2	2	2	2	2

4.4 分析函数

为了体现函数的差异，重新造一批数据

新测试数据如下

user_name	login_date	pv
zhangsan	2021-01-01	3
zhangsan	2021-01-02	4
zhangsan	2021-01-03	3
lisi	2021-01-01	5
lisi	2021-01-02	6
lisi	2021-01-03	5
wangwu	2021-01-01	2

生成排序的3个函数

ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
RANK 生成数据项在分组中的排名，排名相等会在名次中留下空位
DENSE_RANK 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

案例

select 
  user_name, 
  login_date, 
  pv, 
  ROW_NUMBER() over (
    partition by user_name 
    order by 
      pv
  ) as rank_pv_1, 
  RANK() over (
    partition by user_name 
    order by 
      pv
  ) as rank_pv_2, 
  DENSE_RANK() over (
    partition by user_name 
    order by 
      pv
  ) as rank_pv_3 
from 
  default.test_user_login;

查询结果

user_name	login_date	pv	rank_pv_1	rank_pv_2	rank_pv_3
zhangsan	2021-01-01	3	1	1	1
zhangsan	2021-01-03	3	2	1	1
zhangsan	2021-01-02	4	3	3	2
lisi	2021-01-01	5	1	1	1
lisi	2021-01-03	5	2	1	1
lisi	2021-01-02	6	3	3	2
wangwu	2021-01-01	2	1	1	1

3个函数的使用场景：

分组取前3，比如，查询每个班级总分排名前3的学生，每个科目排名第一的学生名称

# 查询每个班级总分排名前3的学生,表结构 stu_id,class_id,subject,score
select 
  tmp1.stu_id, 
  tmp1.calss_id, 
  tmp1.sum_score 
from 
  (
    select 
      tmp.stu_id, 
      tmp.calss_id, 
      tmp.sum_score, 
      row_number() over (
        partition by class_id 
        order by 
          sum_score
      ) as rn 
    from 
      (
        select 
          stu_id, 
          calss_id, 
          sum(score) as sum_score
      ) 
    from 
      student 
    group by 
      calss_id, 
      stu_id
  ) tmp
) tmp1 
where 
  tmp1.rn >= 3;

连续登陆天数大于3天的用户

# 连续登陆天数大于3天的用户
select 
  user_name, 
  count(1) as cnt 
from 
  (
    select 
      user_name, 
      login_date, 
      row_number() over (
        partition by user_name 
        order by 
          login_date
      ) as rn
  ) 
group by 
  user_name, 
  date_sub(login_date, rn) 
having 
  count(1) > 3;

CUME_DIST 小于等于当前值的行数/分组内总行数
PERCENT_RANK (分组内当前行的RANK值-1) / (分组内总行数-1)

案例

select 
  user_name, 
  login_date, 
  pv, 
  RANK() over (partition by user_name order by pv) as rank_pv_2, 
  CUME_DIST() over (
    partition by user_name 
    order by 
      pv
  ) as cume_pv, 
  PERCENT_RANK() over (
    partition by user_name 
    order by 
      pv
  ) as percent_pv 
from 
  default.test_user_login;

查询结果

user_name	login_date	pv	rank_pv_1	cume_pv	percent_pv
zhangsan	2021-01-01	3	1	0.666666667	(1-1)/(3-1)=0
zhangsan	2021-01-03	3	1	0.666666667	(1-1)/(3-1)=0
zhangsan	2021-01-02	4	3	0.333333333	(3-1)/(3-1)=1
lisi	2021-01-01	5	1	0.666666667	(1-1)/(3-1)=0
lisi	2021-01-03	5	1	0.666666667	(1-1)/(3-1)=0
lisi	2021-01-02	6	3	0.333333333	(3-1)/(3-1)=1
wangwu	2021-01-01	2	1	1	(1-1)/(1-1)=0

NTILE(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布

案例

select 
  user_name, 
  login_date, 
  pv, 
  NTILE(2) over (
    partition by user_name 
    order by 
      pv
  ) as ntile_pv_1, 
  NTILE(3) over (
    partition by uname 
    order by 
      pv
  ) as ntile_pv_2, 
  NTILE(4) over (
    partition by uname 
    order by 
      pv
  ) as ntile_pv_3 
from 
  default.test_user_login;

查询结果

user_name	login_date	pv	ntile_pv_1	ntile_pv_2	ntile_pv_3
wangwu	2021-01-01	2	1	1	1
zhangsan	2021-01-01	3	1	1	1
zhangsan	2021-01-03	3	1	1	2
zhangsan	2021-01-02	4	1	2	2
lisi	2021-01-01	5	2	2	3
lisi	2021-01-03	5	2	3	3
lisi	2021-01-02	6	2	3	4

开窗函数官方文档

六、元数据

元数据详解：
https://www.cnblogs.com/qingyunzong/p/8710356.html

元数据统计信息收集思路：
https://blog.csdn.net/songjifei/article/details/104706737

元数据详细信息收集思路：
https://blog.csdn.net/sanbudeyu_008/article/details/102800508

七、Hive优化

1 设置合理的map/reduce 数量

1.1 调整map数量

目的就是让单个map处理合适数据量的数据。

1.1.1 数据量小，减少map数

当有几百个小文件时，可以通过调整参数合并文件，减少文件数

设置如下参数：

# 设置map任务的文件切割大小为100MB
set mapred.max.split.size=100000000;
# 设置每个节点的文件切割大小为100MB
set mapred.min.split.size.per.node=100000000;
# 设置每个机架的文件切割大小为100MB
set mapred.min.split.size.per.rack=100000000;
# 设置执行map前先合并文件
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，
小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的）进行合并。

1.1.2 数据量大，增加map数

当文件很大，只用1个map去处理很费时间，此时可以增加map数量(按自己集群情况调整)。

设置如下参数：

set mapred.map.tasks=10;

1.2 调整reduce数量

如果设置了mapred.reduce.tasks/mapreduce.job.reduces参数，那么Hive会直接使用它的值作为Reduce的个数；如果mapred.reduce.tasks/mapreduce.job.reduces的值没有设置（也就是-1），那么Hive会根据输入文件的大小估算出Reduce的个数。根据输入文件估算Reduce的个数可能未必很准确，因为Reduce的输入是Map的输出，而Map的输出可能会比输入要小，所以最准确的数根据Map的输出估算Reduce的个数。

reduce个数的设定极大影响任务执行效率，不指定reduce个数的情况下，Hive会猜测确定一个reduce个数，基于以下两个设定：

hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
hive.exec.reducers.max（每个任务最大的reduce数，默认为999）

计算reducer数的公式 N=min（参数2，总输入数据量/参数1）
如果reduce的输入（map的输出）总大小不超过1G，那么只会有一个reduce任务；

1.2.1 方法1

调整如下参数

# 设置每个reduce处理的文件大小设置为500MB
set hive.exec.reducers.bytes.per.reducer=500000000;

1.2.2 方法2

调整如下参数

# 设置reduce的数量为15
set mapred.reduce.tasks=15;

2 小文件优化

小文件产生可能的原因：

1 动态分区插入数据，产生大量的小文件，从而导致map数量剧增；
2 reduce数量越多，小文件也越多（reduce的个数和输出文件是对应的）；
3 数据源本身就包含大量的小文件

小文件的影响：

从Hive的角度看，小文件会开很多map，一个map开一个JVM去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重影响性能。
在HDFS中，每个小文件对象约占150byte，如果小文件过多会占用大量内存。这样NameNode内存容量严重制约了集群的扩展。

源头解决思路：

1 减少reduce的数量（可以使用参数进行控制）;
2 少用动态分区，用时记得按distribute by分区;

已有小文件解决思路：

1 使用hadoop archive命令把小文件进行归档;
2 重建表，建表时减少reduce数量;
3 通过参数进行调节，设置map/reduce端的相关参数

可调整的map/reduce参数：

# 每个Map最大输入大小(这个值决定了合并后文件的数量)  
set mapred.max.split.size=100000000;
# 一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000; 
# 一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)    
set mapred.min.split.size.per.rack=100000000;  
# 执行Map前进行小文件合并  
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; 

# 设置map端输出进行合并，默认为true  
set hive.merge.mapfiles = true  
# 设置reduce端输出进行合并，默认为false  
set hive.merge.mapredfiles = true  
# 设置合并文件的大小  
set hive.merge.size.per.task = 256*1000*1000  
# 当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。  
set hive.merge.smallfiles.avgsize=16000000

3 SQL优化

优化思路：

尽早尽量过滤数据，减少每个阶段的数据量
减少job数
减少扫描的文件数或分区数

案例表

# t_order 订单表
create table t_order(
	order_id int "订单编号",
	user_id int "用户编号",
	create_date string "订单创建时间"
);
# t_user 用户表
create table t_user(
	user_id int "用户编号",
	user_name string "用户名",
	create_date string "创建时间"
);

3.1 列剪裁

一个表中有多个字段，查询时只查询需要的列

# 优化前
select * from t_user;
# 优化后
select user_id from t_user;

3.2 分区剪裁

数据量大的表必须是分区表，查询时where里必须指定分区的筛选条件

# 优化前
select * from tablename;
# 优化后
select order_id,order_name from t_order where create_date>='2020-01-01';

3.3 避免笛卡尔积

关联查询时必须加关联条件

# 优化前
select order_id,user_name from t_order, t_user;

select order_id,user_name from t_order inner join t_user;
# 优化后
select order_id,user_name from t_order, t_user where t_order.user_id = t_user.user_id;

select order_id,user_name from t_order inner join t_user on t_order.user_id=t_user.user_id;

3.4 join前过滤无用数据

# 优化前
select o.order_id,u.user_name  
from t_order o 
join t_user u on (o,user_id=u.user_id)  
where o.create_date = '2020-01-01' and u.create_date='2020-01-01';
# 优化后
select o2.order_id,u2.user_name from (select o.order_id,o.user_id from t_order o where o.create_date = '2020-01-01') o2 join (select user_id,user_name from t_user u where u.create_date='2020-01-01') u2 on o2.user_id = u2.user_id;

3.5 join时小表在前大表在右

在Reduce阶段，位于join操作符左边的表会先被加载到内存，载入条目较少的表可以有效的防止内存溢出(OOM)。

# 优化前
select o.order_id,o.user_name from t_order o join t_user u on o.user_id=u.user_id;
# 优化后
select o.order_id,o.user_name from t_user u join  t_order o on u.user_id=o.user_id;

3.6 mapjoin

join有两种，一种是map join，一种是reduce join。当小表和大表进行join时，尽量采用mapjoin；如果把join的操作先在map join，到reduce后接收到的数据会更少，同时可以避免小表和大表join产生的数据倾斜；

注意：一般行数小于2000行，大小小于1M(扩容后可以适当放大)的表才能使用

有2种使用方式
1 自动mapjoin默认是已开启的，只需要小表放左边即可
2 显示指定mapjoin

# 方式1

set hive.auto.convert.join = true;# 开启自动mapjoin
set hive.mapjoin.smalltable.filesize = 6250000;# 设置小表存储的文件大小
 
select * from t_user
join t_order 
on t_user.user_id=t_order.user_id;

# 方式2

set hive.ignore.mapjoin.hint=true;
 
select /*+ mapjoin(t_user) */   * 
from t_user
join t_order 
on t_user.user_id=t_order.user_id;

如果是mapjoin会有如下日志
在这里插入图片描述

3.7 避免distinct

distinct使用一个reduce效率很慢，建议使用group by替代

# 优化前
select distinct user_id from t_order;
# 优化后
select user_id  from t_order group by user_id;

3.8 explain查看hive查询的执行计划

可以根据执行计划的情况，调整sql

语法：

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

关键字：

EXTENDED 查看详细信息
CBO 查看Calcite优化器生成的执行计划，还可以查看每个操作花费的资源（cpu,io）
AST 查看抽象语法树
DEPENDENCY 查看本次输入的额外信息，包括表或其他的一些属性
AUTHORIZATION 查看查询权限
LOCKS 查看本次查询所需要的锁
VECTORIZATION 查看本次查询不是向量化执行的原因
ANALYZE 查看计划和实际行数

案例：

EXPLAIN
FROM src INSERT OVERWRITE TABLE dest_g1 SELECT src.key, sum(substr(src.value,4)) GROUP BY src.key;

依赖图：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-2 depends on stages: Stage-1
  Stage-0 depends on stages: Stage-2

Stage-1是根步骤，Stage-2在Stage-1执行完之后执行，Stage-0在Stage-2执行完后执行。

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        src
            Reduce Output Operator
              key expressions:
                    expr: key
                    type: string
              sort order: +
              Map-reduce partition columns:
                    expr: rand()
                    type: double
              tag: -1
              value expressions:
                    expr: substr(value, 4)
                    type: string
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: sum(UDFToDouble(VALUE.0))
          keys:
                expr: KEY.0
                type: string
          mode: partial1
          File Output Operator
            compressed: false
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.mapred.SequenceFileOutputFormat
                name: binary_table
 
  Stage: Stage-2
    Map Reduce
      Alias -> Map Operator Tree:
        /tmp/hive-zshao/67494501/106593589.10001
          Reduce Output Operator
            key expressions:
                  expr: 0
                  type: string
            sort order: +
            Map-reduce partition columns:
                  expr: 0
                  type: string
            tag: -1
            value expressions:
                  expr: 1
                  type: double
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: sum(VALUE.0)
          keys:
                expr: KEY.0
                type: string
          mode: final
          Select Operator
            expressions:
                  expr: 0
                  type: string
                  expr: 1
                  type: double
            Select Operator
              expressions:
                    expr: UDFToInteger(0)
                    type: int
                    expr: 1
                    type: double
              File Output Operator
                compressed: false
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe
                    name: dest_g1
 
  Stage: Stage-0
    Move Operator
      tables:
            replace: true
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe
                name: dest_g1

这个例子里有2个map/reduce步骤和1个文件系统相关的步骤。Stage-1是读取src表的数据并做一些转换；Stage-2做分组聚合；Stage-0最后将数据移动到表相关的目录里。

explain 官方文档：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain#LanguageManualExplain-EXPLAINSyntax

参考资料

Hive官方网站

《hive编程指南》

Hive SQL 底层执行过程详细剖析

开窗和分析函数：
hive窗口函数总结
 hive窗口分析总结

Hive优化（整理版）

终回首

关注

4
点赞
踩
2

收藏

觉得还不错? 一键收藏
11
评论
Apache Hive基础知识

我的hive学习笔记一、简介Apache Hive是一个提供访问HDFS上存储数据的SQL接口的数据仓库。适合存储数据不会频繁变化，且不需要快速响应给出结果的场景。Hive暂不支持记录级别的更新、删除。
复制链接

扫一扫