重生之我要学Hive

最新推荐文章于 2025-08-09 21:35:30 发布

原创最新推荐文章于 2025-08-09 21:35:30 发布 · 1.2k 阅读

25 ·

CC 4.0 BY-SA版权

文章标签：

#hive #hadoop #数据仓库 #大数据

大数据专栏收录该内容

11 篇文章

订阅专栏

1. hive基本概念

1.1. hive简介

构建在hadoop之上的数据分析工具，没有数据存储能力，只有使用数据的能力，操作数据交互采用sql，元数据存放在Derby或者Mysql上，数据存储在HDFS中，分析数据库底层实现的是MapReduce，执行程序实在Yarn上。

1.2. hive和数据库的区别

1.3. 为什么使用hive

使用Hadoop操作HDFS或者数据库的文件学习成本过高，要学习java或者python。
MapReduce复杂逻辑开发困难。
Hive操作接口类似sql语法，开发上手快。
免去了写MapReduce的过程，开发简单
功能扩展方便

1.4. Hive的优缺点

优点：

操作数据使用类似SQL的HQL语法，简单。

免去写MapReduce的过程。

延迟比较高，适合离线开发，对实时性能不高的场合。

适合处理大数据

更具需求自定义函数

缺点：

HQL表达操作能力有限，复杂情况需要使用MapReduce

Hive运行底层基于MapReduce，默认基于此，延迟会很高。

Hive调优比较困难，不够智能化。

该图体现了数据处理的快速，不需要再MapReduce中写比较复杂的代码去重输出。

1.5. Hive应用场景：

日志分析：大部分互联网公司使用 Hive 进行日志分析，包括百度、淘宝等。
统计网站一个时间段内的 PV、UV
多维度数据分析
海量结构化数据的离线分析

2. Hive架构

1.用户交互层（Client）

用户 / 应用通过多种方式提交 Hive SQL：

CLI/Shell：命令行工具（如 hive 命令）直接写 SQL。
Beeline/Thrift：通过 HiveServer2 连接，支持 JDBC/ODBC（如 BI 工具、程序代码调用）。
Web UI：Hue 等可视化界面提交查询。

2.Hive 服务层（Hive 核心流程）

（1）SQL 解析与优化（Driver 模块）

SQL Parser：解析 SQL 语法，生成抽象语法树（AST）。
Compiler：将 AST 编译为逻辑执行计划（Logical Plan）。
Query Optimizer：优化执行计划（如分区裁剪、Join 优化），生成物理执行计划（Physical Plan）。
Execution：将物理计划转换为 MapReduce/Spark 任务，提交到 Hadoop/YARN。

3. 元数据管理（Meta Store）

存储表结构（库名、表名、字段、分区、存储格式等）、权限信息。
默认用 Derby 数据库，生产常用 MySQL 独立存储（通过 Thrift Server 交互）。

4. 计算与存储层（Hadoop）

YARN：资源调度，分配 CPU、内存给 MapReduce/Spark 任务。
MapReduce：执行 Hive 转换后的计算任务（也可对接 Spark 等引擎）。
HDFS：存储原始数据文件（表数据按分区 / 存储格式落地 HDFS）。

5.Driver

解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第三方工具库完成，比如 ANTLR；对
AST 进行语法分析，分析查询语法和查询计划，检查 SQL 语义是否有误。
编译器（Compiler）：获取元数据，检查表是否存在、字段是否存在，然后将 AST 编译生成逻辑执行计划。
优化器（Query Optimizer）：对逻辑执行计划进行优化。
执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。最后根据优化后的物理执行计划生成底层代码进行执行，对于 Hive 来说，就是 MR/Spark。

6. 流程闭环

用户提交 Hive SQL → 2. Hive 解析优化生成任务 → 3. 提交到 Hadoop 执行 → 4. 结果返回给 Client → 5. 元数据全程协同（如校验表是否存在、分区是否有效）。

核心逻辑：用类 SQL 语法（Hive SQL）屏蔽 MapReduce 复杂度，让用户通过简单查询处理海量数据，元数据保障表结构和权限管理，Hadoop 提供分布式计算存储能力。

3. Hive交互方式

这里我们使用Hive Shell（CLI，命令行界面），简单直接，无需额外配置，能快速上手进行基本操作；但缺乏图形化界面，操作不够直观；

[root@node03 ~]# hive
...
hive (default)> SHOW DATABASES;
OK
database_name
default
Time taken: 0.633 seconds, Fetched: 1 row(s)
hive (default)> exit;

default 是默认的数据库，路径为 /hive/warehouse 。

4. Hive基础

4.1. 数据库

创建数据库

HDFS 上默认的存储路径是 /hive/warehouse/*.db 。

CREATE DATABASE shop;

标准写法

CREATE DATABASE IF NOT EXISTS crm;

指定数据库创建的位置（数据库在 HDFS 上的存储路径）。

CREATE DATABASE IF NOT EXISTS school location '/hive/school.db';

修改数据库

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

ALTER DATABASE school SET DBPROPERTIES('createtime'='20220803');

数据库详情

show databases;
show databases like 's*' --以s开头的数据库名

desc databases school;
describe databases school;

删除数据库

DROP DATABASE school;
DROP DATABASE IF EXISTS school;--不存在则会保存
DROP DATABASE IF EXISTS school CASCADE;--不为空删除，否则会报错

4.2. 数据表

4.2.1. 语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  [(col_name data_type [column_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, ...)
     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 (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION
  | STRING
  | BINARY
  | TIMESTAMP
  | DECIMAL
  | DECIMAL(precision, scale)
  | DATE
  | VARCHAR
  | CHAR
array_type
  : ARRAY < data_type >
map_type
  : MAP < primitive_type, data_type >
struct_type
    : STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
   : UNIONTYPE < data_type, data_type, ... >  
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]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
property_name=property_value, ...)]
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE
NOVALIDATE RELY/NORELY ]
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ]
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...)
DISABLE NOVALIDATE
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[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]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

external：创建表为外部表

comment：表和列注释

partitioned by：创建分区表

clustered by：创建分桶表

sorted by：排序方式

row format：设置导入数据的分列格式。

案例一

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE IF NOT EXISTS test.t_user (
id int,
username string,
password string,
gender string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','--分列依据，按照‘，’号分割
LINES TERMINATED BY '\n';--每行数据以换行结束

LOAD DATA INPATH '/yjx/user.txt' INTO TABLE test.t_user;

SELECT * FROM test.t_user WHERE age <= 18;
+------------+------------------+------------------+----------------+-------------+
| t_user.id  | t_user.username  | t_user.password  | t_user.gender  | t_user.age  |
+------------+------------------+------------------+----------------+-------------+
| 1          | admin            | 123456           | 男              | 18          |
| 3          | lisi             | 654321           | 女              | 16          |
+------------+------------------+------------------+----------------+-------------+

简单的查询不会执行 MapReduce，复杂的查询会调用 MapReduce 模板生成 MapReduce 任务。

表详情

SHOW TABLES;

SHOW TABLES LIKE 't*';

DESC t_person;
DESC FORMATTED t_person;--详细显示
DESCRIBE FORMATTED t_person;

ALTER TABLE old_table_name RENAME TO new_table_name;

重命名

ALTER TABLE old_table_name RENAME TO new_table_name;

修改列

-- 添加列
ALTER TABLE table_name ADD COLUMNS (new_col INT);
-- 一次增加一个列(默认添加为最后一列)
ALTER TABLE table_name ADD COLUMNS (new_col INT);
-- 可以一次增加多个列
ALTER TABLE table_name ADD COLUMNS (c1 INT, c2 STRING);
-- 添加一列并增加列字段注释
ALTER TABLE table_name ADD COLUMNS (new_col INT COMMENT 'a comment');
-- 更新列
ALTER TABLE table_name CHANGE old_col new_col STRING;
-- 将列 a 的名称更改为 a1
ALTER TABLE table_name CHANGE a a1 INT;
-- 将列 a1 的名称更改为 a2，将其数据类型更改为字符串，并将其放在列 b 之后
ALTER TABLE table_name CHANGE a1 a2 STRING AFTER b;
-- 将 c 列的名称改为 c1，并将其作为第一列
ALTER TABLE table_name CHANGE c c1 INT FIRST;

清空表

TRUNCATE TABLE table_name;

注意：清空表只能删除内部表的数据（HDFS 文件），不能删除外部表中的数据。

在 SQL 中，TRUNCATE 属于 DDL 语句，主要功能是彻底删除数据，使其不能进行回滚。

删除表

DROP TABLE table_name;

注意：删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据

4.3. 排序

Hive 中有四种排序：

全局排序：ORDER BY

内部排序：SORT BY

分区排序：DISTRIBUTE BY

组合排序：CLUSTER BY

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

全局排序：ORDER BY

全局排序是在一个 Reduce 中进行排序的。


SELECT * FROM emp WHERE 1=1 ORDER BY sal DESC;

局部排序

只在每个 Reducer 内部进行排序。

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

分区排序

DISTRIBUTE BY 类似 MR 中的 PARTITION（自定义分区），进行分区，一般结合 SORT BY 使用（注意：DISTRIBUTE BY 要在 SORT BY 之前）。

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

组合排序

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

FAILED: ParseException line 1:35 extraneous input 'DESC' expecting EOF near '<EOF>' 。

SELECT * FROM emp CLUSTER BY deptno;

4.4. 内部表和外部表

内部表是hive管理的，数据存在HDFS中，元数据存储在Mysql中，内部表是物理实际意义上的，删除内部表也会删除HDFS中的原表，默认表。

对外部表的增、删（单条 / 部分数据）、改操作会直接影响源数据；但删除表本身（DROP TABLE）不会影响源数据。这是因为外部表仅管理元数据（表结构等），其数据存储在外部路径，操作表数据本质上是操作该路径下的源数据。

一般情况下，在企业内部都是使用外部表的。因为会有多人操作数据仓库，可能会产生数据表误删除操作，为了数据安全性，通常会使用外部表，且方便达到数据共享。

CREATE EXTERNAL TABLE IF NOT EXISTS test.t_user4 (
id int,
username string,
password string,
gender string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
-- 可以指定到某个目录（该目录下的文件都会被扫描到），也可以指定到某个具体的文件。通配符 * 无效，例如
/yjx/teacher/*/*
LOCATION '/yjx/user';

没有该表

CREATE EXTERNAL TABLE IF NOT EXISTS test.t_user2 (
id int,
username string,
password string,
gender string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
LOAD DATA INPATH '/yjx/user/user.txt' INTO TABLE test.t_user2;

有该表

总结：推荐使用方案一，数据不会被随意移动，更易于团队使用。

4.5. 载入数据

LOAD DATA [LOCAL] INPATH 'datapath' [OVERWRITE] INTO TABLE student [PARTITION (partcol1=val1,…)];

[LOCAL]：本地，不加 LOCAL 就是从 HDFS 获取

INPATH：数据的路径

'datapath'：具体的路径，要参考本地还是 HDFS，支持通配符 *，例如 /yjx/user/*/*

[OVERWRITE]：覆盖，不加 OVERWRITE 则追加数据

INTO TABLE：加入到表

student：表名

[PARTITION (partcol1=val1,…)]：分区

LOAD DATA LOCAL INPATH '/root/user.txt' INTO TABLE t_user;

4.6. 导出数据

INSERT OVERWRITE LOCAL DIRECTORY '/root/user' SELECT * FROM t_user;

[root@node01 ~]# cat /root/user/000000_0
1admin123456男18
2zhangsanabc123男23
3lisi654321女16

将查询结果输出到HDFS中

INSERT OVERWRITE DIRECTORY '/yjx/export/user'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM t_user;

使用HDFS拷贝文件到HDFS中其他目录

hdfs dfs -cp /hive/warehouse/t_person/* /yjx/export/person

将元数据和数据同时导出

EXPORT TABLE t_person TO '/yjx/export/person';

5. Hive高级

5.1. 分区、分桶

从左至右，分别是一个表，一个表中有分区，一个表中有分区有分桶，一个表中有多个分桶。

在大数据中，最常见的一种思想就是分治，我们可以把大文件切割成一个个的小文件，这样每次操作小文件时就会容易许多。同样的道理，在 Hive 中也是支持的，我们可以把大的数据，按照每天或者每小时切分成一个个的小文件，这样去操作小文件就会容易许多，这就是分区、分桶的意思。

分区分桶是提高数据查找的速度的，通过分区我们可以更快的定位到这个数据区，可以避免 Hive 全表扫描，提升查询效率；通过分桶我们可以更快的找到这类型的数据。分区时针对目录，分桶是针对目录。

分区可分为单分区和多分区。

5.1.1. 静态分区

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

-- 单分区：创建分区表 PARTITIONED BY (分区字段名 分区字段类型)
-- 多分区：创建分区表 PARTITIONED BY (分区字段名 分区字段类型, 分区字段名2 分区字段类型2)
CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
)
PARTITIONED BY (grade int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

ALTER TABLE t_student ADD IF NOT EXISTS PARTITION (grade=1)

SHOW PARTITIONS t_student;
...
+------------+
| partition  |
+------------+
| grade=1    |
+------------+

-- ALTER TABLE 表名 DROP PARTITION(分区字段名=键值)
ALTER TABLE t_student DROP PARTITION (grade=1);
-- 查看分区
SHOW PARTITIONS t_student;
...
+------------+
| partition  |
+------------+
+------------+

LOAD DATA INPATH '/yjx/s1.txt' INTO TABLE t_student PARTITION (grade=1)

多分区

CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
)
PARTITIONED BY (grade int, clazz int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

注意：前后两个分区的关系为父子关系，也就是 grade 文件夹下面有多个 clazz 子文件夹

5.1.2. 动态分区

动态分区和静态分区的创建方式是一样的，但文件不需要按照分区创建文件，并按照分区上传文件。

需要开启会话并设置以下参数

-- 开启动态分区支持（默认 true）
SET hive.exec.dynamic.partition=true;
-- 是否允许所有分区都是动态的，strict 要求至少包含一个静态分区列，nonstrict 则无此要求（默认 strict）
SET hive.exec.dynamic.partition.mode=nonstrict;

严格模式

分区表查询时，必须在 WHERE 语句后指定分区字段，否则不允许执行。因为在查询分区表时，如果不指定分区查询，会进行全表扫描。而分区表通常有非常大的数据量，全表扫描非常消耗资源。
ORDER BY 查询必须带有 LIMIT 语句，否则不允许执行。因为 ORDER BY 会进行全局排序，这个过程会将处理的结果分配到一个 Reduce 中进行处理，处理时间长且影响性能。
笛卡尔积查询（多使用 JOIN 和 ON 语句查询）。数据量非常大时，笛卡尔积查询会出现不可控的情况，因此严格模式下也不允许执行。

5.1.3. 分桶

Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条记录要存放在哪个桶中。

假如某电商平台每天产生 1 千万的订单数据，为了快速处理订单数据（比如分析每个用户的购买行为），可以根据用户 ID 进行分桶。比如将表按照 ID 分成 100 个桶，其算法是 hash(id) % 100，这样 hash(id) % 100 = 0 的数据会被放到第一个桶中，hash(id) % 100 = 1 的记录被放到第二个桶中。

优势

方便抽样，提高join查询效率。

实践

-- 开启分桶功能，默认为 false
SET hive.enforce.bucketing=true;
-- 设置 Reduce 的个数，默认是 -1，-1 时会通过计算得到 Reduce 个数，一般 Reduce 的数量与表中的 BUCKETS 数量
一致
-- 有些时候环境无法满足时，通常设置为接近可用主机的数量即可
SET mapred.reduce.tasks=-1;

CREATE TABLE IF NOT EXISTS t_citizen_bucket (
idcard int,
username string,
province int
)
CLUSTERED BY (idcard) SORTED BY (username DESC) INTO 12 BUCKETS
--分桶依据是idcard，排序依据是username降序，分成12桶。会根据idcard的哈希值%12来进行分区。
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

for (int i = 1000; i < 10000; i++) {
    System.out.println(i + "," + "admin" + (new Random().nextInt(89999) + 10000) + "," + i % 34);
}

CREATE EXTERNAL TABLE IF NOT EXISTS t_citizen_e (
idcard int,
username string,
province int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION '/yjx/citizen';

INSERT OVERWRITE TABLE t_citizen_bucket SELECT * FROM t_citizen_e;

5.2. 数据抽样

在大规模数据量的数据分析及建模任务中，往往针对全量数据进行挖掘分析时会十分耗时和占用集群资源，因此一般情况下只需要抽取一小部分数据进行分析及建模操作。Hive提供了数据取样（SAMPLING）的功能，能够根据一定的规则进行数据抽样，目前支持数据块抽样，分桶抽样和随机抽样。

5.2.1. 块抽样

按照比例抽样，比如抽hive中10%的数据。

缺点：不随机。该方法实际上是按照文件中的顺序返回数据，对分区表，从头开始抽取，可能造成只有前面几个分区的数据。

优点：速度快。

CREATE TABLE IF NOT EXISTS
t_citizen_block_sample AS SELECT * FROM t_citizen_e TABLESAMPLE(1000 ROWS);
-- 数字与 PERCENT 之间要有空格
CREATE TABLE IF NOT EXISTS
t_citizen_block_sample AS SELECT * FROM t_citizen_e TABLESAMPLE(10 PERCENT);
-- 数字与 M 之间不要有空格
CREATE TABLE IF NOT EXISTS
t_citizen_block_sample AS SELECT * FROM t_citizen_e TABLESAMPLE(1M);

TABLESAMPLE(nM) ：指定抽样数据的大小，单位为 M。

TABLESAMPLE(n ROWS) ：指定抽样数据的行数，其中 n 代表每个 Map 任务均取 n 行数据，Map 数量可通过 Hive 表的简单查询语句确认（关键词：number of mappers: x)。

TABLESAMPLE(n PERCENT) ：按百分比抽样数据，如果数据不超过 128M 还是全量数据。

5.2.2. 分桶抽样

优点：随机且不走MR


TABLESAMPLE(BUCKET x OUT OF y [ON colname])
-- 假设 Table 总共分了 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、34、38、42、46、50、54、58、62 桶
SELECT * FROM t_citizen_bucket TABLESAMPLE(BUCKET 2 OUT OF 4 ON idcard);

5.2.3. 随机抽样

优点：提供真正的随机抽样。

缺点：速度慢。

-- 使用RAND()函数和LIMIT关键字来获取样例数据，使用DISTRIBUTE和SORT关键字来保证数据随机分散到Mapper和Reducer
-- SORT BY 提供了单个 Reducer 内的排序功能，但不保证整体有序
SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT <N rows to sample>;
-- ORDER BY RAND() 语句可以获得同样的效果，但是性能会有所降低
SELECT * FROM <Table_Name> WHERE col=xxx ORDER BY RAND() LIMIT <N rows to sample>;

5.3. 事物

Hive 支持了具有 ACID 语义的事务，但做不到和传统关系型数据库那样的事务级别，仍有很多局限如：

不支持 BEGIN、COMMIT、ROLLBACK，所有操作自动提交；

事务表仅支持 ORC 文件格式；

表参数 transactional 必须为 true；

外部表不能成为 ACID 表，不允许从非 ACID 会话读取/写入 ACID 表（例如会话开启了事务，才可以查询事务表，否则

查询就会报错）；

默认事务关闭，需要额外配置

实践

# 开启 hive 并发
SET hive.support.concurrency=true;
# 配置事务管理类
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager

CREATE TABLE IF NOT EXISTS test.t_user (
id int,
name string,
age int
)
STORED AS ORC
TBLPROPERTIES('transactional'='true');

INSERT INTO test.t_user VALUES (1, "张三", 18), (2, "李四", 19), (3, "王五", 20);

合并

CREATE TABLE IF NOT EXISTS test.t_user2 (
id int,
name string,
age int
  )
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 非事务表仅支持 INSERT INTO
INSERT INTO test.t_user2 VALUES (3, 'wangwu', 30), (4, 'zhaoliu', 21);
-- 合并
MERGE INTO test.t_user AS a
USING test.t_user2 AS b ON a.id = b.id
WHEN MATCHED THEN UPDATE SET name = b.name, age = b.age
WHEN NOT MATCHED THEN INSERT VALUES (b.id, b.name, b.age);

这条语句会更新王五的姓名和年龄字段，并插入一条 zhaoliu 的新记录。多条 WHEN 子句会被视为不同的语句，有各自的语句 ID（Statement ID）。INSERT 子句会创建 delta_ 文件，内容是 zhaoliu 的数据；UPDATE 语句则会创建 delete_delta_ 和 delta_ 两个文件，删除并新增 wangwu 的数据。

压缩

Minor Compaction 会将所有的 delta 文件压实为一个文件，delete 也压实为一个。压实后的结果文件名中会包含写事务 ID 范围，同时省略掉语句 ID。压实过程是在 Hive Metastore 中运行的，会根据一定阈值自动触发。我们也可以使用如下语句人工触发：

ALTER TABLE test.t_user COMPACT 'minor';

Major Compaction 则会将所有文件合并为一个文件，以 base_N 的形式命名，其中 N 表示最新的写事务 ID。已删除的数据将在这个过程中被剔除。row__id 则按原样保留。

ALTER TABLE test.t_user COMPACT 'major';

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

读取

5.4. 视图

5.4.1. 视图

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

视图是一个虚拟表，它基于 SQL 查询定义，不存储实际数据。每次查询视图时，会动态执行底层 SQL 语句并返回结果。

-- 使用视图
-- 创建视图：查询部门经理人，处理NULL和去重
CREATE VIEW IF NOT EXISTS vw_emp_mgr (mgr) AS
SELECT mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;
-- 创建视图：查询部门经理人的薪水和部门编号
CREATE VIEW IF NOT EXISTS vw_emp_mgr_sal (empno, sal, deptno) AS
SELECT empno, sal, deptno FROM emp WHERE empno IN (
SELECT mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr
);
-- 创建视图：查询部门经理人中的最低薪水
CREATE VIEW IF NOT EXISTS vw_emp_mgr_minsal (empno, minsal, deptno) AS
SELECT empno, sal minsal, deptno FROM emp WHERE empno IN (
SELECT mgr FROM emp WHERE mgr IS NOT NULL GROUP BY mgr
) ORDER BY sal LIMIT 1;
-- 使用视图查询部门经理人中薪水最低的部门名称
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 中的视图是一种虚拟表，只保存定义，不实际存储数据；

通常从真实的物理表查询中创建生成视图，也可以从已经存在的视图上创建新视图；

创建视图时，将冻结视图的结构，如果删除或更改基础表，则视图将失败；

视图是用来简化操作的，不缓冲记录，也不会提高查询性能。

优点：

通过视图可以提高数据的安全性，将真实表中特定的列提供给用户，保护数据隐私；

上层的业务查询和底层数据表解耦，业务上可以查的一张表，但是底层可能映射的是三张或多张表的数据；

修改底层数据模型只需要重建视图即可，不需要上层业务修改业务逻辑；

降低查询复杂度，优化查询语句（注意不是提高查询效率）。

缺点：

无法再对视图进行优化，而且并没有提升查询速度，只是使上层的业务逻辑变得更清晰简洁。

5.4.2. 物化视图

使用物化视图的目的就是通过预计算，提高查询性能，所以需要占用一定的存储空间。Hive 还提供了物化视图的查询自动重写机制（基于 Apache Calcite 实现）和物化视图存储选择机制，可以本地存储在 Hive，也可以通过用户自定义 Storage Handlers 存储在其他系统（如 Apache Druid）。

物化视图是一个预计算的物理表，它存储查询结果的实际数据，并定期刷新。

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

-- 创建事务表
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 MATERIALIZED VIEW test.emp_analysis
AS
SELECT
deptno,
COUNT(*) cnt,
AVG(sal) avg_sal,
MAX(sal) max_sal,
MIN(sal) min_sal
FROM test.emp GROUP BY deptno;

通过事物查询可以非常的快速，通过 HDFS 发现物化视图是会真正创建表的。

刷新物化视图

增量刷新

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

定时刷新

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

全量刷新

若只用 INSERT 更新了源表数据，可以对物化视图进行增量刷新。若使用 UPDATE、INSERT 更新了源表数据，那么只能进行重建，即全量刷新(REBUILD)。

注意：如果一张表创建了许多物化视图，那么在数据写入这张表时，并刷新的时候，可能会消耗许多机器的资源，比如数据带宽占满、存储增加等等。

5.5. 高级查询

5.5.1. 一行变多行（行转列）

1,这个杀手不太冷,剧情-动作-犯罪
2,七武士,动作-冒险-剧情
3,勇敢的心,动作-传记-剧情-历史-战争
4,东邪西毒,剧情-动作-爱情-武侠-古装
5,霍比特人,动作-奇幻-冒险
CREATE TABLE IF NOT EXISTS t_movie1 (
id int,
name string,
types string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

LOAD DATA INPATH '/yjx/movie1.txt' INTO TABLE t_movie1;

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   | 七武士         | 剧情    |
| ... | ...           | ...    |
+-----+---------------+--------+

5.5.2. 多行变一行

1,这个杀手不太冷,剧情
1,这个杀手不太冷,动作
1,这个杀手不太冷,犯罪
2,七武士,动作
2,七武士,冒险
2,七武士,剧情
3,勇敢的心,动作
3,勇敢的心,传记
3,勇敢的心,剧情
3,勇敢的心,历史
3,勇敢的心,战争
4,东邪西毒,剧情
4,东邪西毒,剧情
4,东邪西毒,剧情
4,东邪西毒,武侠
4,东邪西毒,古装
5,霍比特人,动作
5,霍比特人,奇幻
5,霍比特人,冒险
CREATE TABLE IF NOT EXISTS t_movie2 (
id int,
name string,
type string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
LOAD DATA INPATH '/yjx/movie2.txt' INTO TABLE t_movie2;

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   | 霍比特人     | 动作:奇幻:冒险        | 动作:奇幻:冒险        |
+-----+----------+-----------------+-----------------+

5.6. 窗口函数

窗口函数是用于分析用的一类函数，要理解窗口函数要先从聚合函数说起。聚合函数是将某列中多行的值合并为一行，比如 SUM、COUNT 等。这类函数往往无法与单独列一起进行查询，比如

-- MySQL 中可以执行，但是返回结果 ename 无意义
-- Hive 中直接报错：FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key
'ename'
SELECT ename, COUNT(*) FROM emp;

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

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 结束位置);