跟着黑马学Hive - 第三周

Anthony_4926
278 阅读17分钟

🐎本文来源：整理自黑马程序员B站课程

🔗课程链接：www.bilibili.com/video/BV1L5…

✒️本文作者：Anthony_4926

1. DML - Load 加载数据

不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hivve 才能映射解析成功；

最原始暴力的方式就是使用 hadoop fs-put|-mv 等方式直接将数据移动到表文件夹下

但是，Hive 官方推荐使用 Load 命令将数据加载到表中。

1.1. 功能

Load 英文单词的含义为：加载、装载；

所谓加载是指：将数据文件移动到与 Hive 表对应的位置，移动时是纯复制、移动操作。

纯复制、移动指在数据 1oad 加载到表中时，Hive 不会对表中的的数据内容进行任何转换，任何操作。

1.2. 语法规则

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

-- 3.o 后还支持指定serde
[INPUTFORMAT 'inputformat' SERDE 'serde']
  • Filepath
    • filepath 表示待移动数据的路径。可以指向文件（在这种情况下，Hlive 将文件移动到表中），也可以指向目录（在这种情况下，Hive 将把该目录中的所有文件移动到表中）。
    • filepath 文件路径支持下面三种形式，要结合 LOCAL 关键字一起考虑：
      • 相对路径，例如：project/datal
      • 绝对路径，例如：/user/hive/project/datal
      • 具有 schema 的完整 URI，例如：hdfs://namenode:9000/user/hive/project/datal
  • Local
    • 指定 LOCAL，将在本地文件系统中查找文件路径。
      • 若指定相对路径，将相对于用户的当前工作目录进行解释；
      • 用户也可以为本地文件指定完整的 URI-例如：file:///user/hiive/project/datal
      • 从本地加载就是复制操作
    • 没有指定 LOCAL 关键字。
      • 如果 filepath 指向的是一个完整的 URI，会直接使用这个 URI；
      • 如果没有指定 schema，Hive 会使用在 hadoop 配置文件中参数 fs.default.name 指定的（不出意外，都是 HDFS）。
      • 不是本地加载就是移动操作
  • Overwrite

如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的已经存在的数据会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

Local 本地是哪里？

如果对 HiveServer2 服务运行此命令

本地文件系统指的是 Hiveserver2 服务所在机器的本地 Linux 文件系统，不是 Hive 客户端所在的本地文件系统。

image.png

1.3. Hive 3.0 新特性

Hive3.0+，load 加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为 INSERT AS SELECT。

Hive3.0+，还支持使用 inputformat、SerDe 指定输入格式，例如如 Text，ORC 等。

比如，如果表具有分区，则 load 命令没有指定分区，则将 1oad 车传换为 INSERT AS SELECT，并假定最后一组列为分区列，如果文件不符合预期，则报错。

例子如下

本来加载的时候没有指定分区，语句是报错的，但是文件的格式符合表的结构，前两个是 col1，col2，最后一个是分区字

段 col3，则此时会将 load 语句转换成为 insert as select 语狗。

--hive 3.0load命令新特性
CREATE TABLE if not exists tab1 (col1 int, col2 int)
PARTITIONED BY (col3 int)
row format delimited fields terminated by ',';

LOAD DATA LOCAL INPATH '/root/hivedata/tab1.txt' IINTO TABLE tab1;

--tab1.txt内容如下
11,22,1
33,44,2

2. Hive insert 使用方式

2.1. 背景：RDBMS 中如何使用 insert

在 MySQL 这样的 RDBMS 中，通常使用 insert+values 的方式来向表表插入数据，并且执行速度很快。

这也是 RDBMS 中表插入数据的核心方式。

INSERT INTO table_name (field1, field2,...fieldN)
VALUES
(value1, value2,...valueN);

假如把 Hive 当成 RDBMS，用 insert+values 的方式插入数据，会如何？

执行过程非常非常慢，原因在于底层是使用 MapReduce 把数据写入 Hive 表中

试想一下，如果在 Hive 中使用 insert+values，对于大数据环境一条条插入数据，用时难以想象。

Hive 官方推荐加载数据的方式：

清洗数据成为结构化文件，再使用 Load 语法加载数据到表中。这样的效率更高。

但是并不意味 insert 语法在 Hive 中没有用武之地。

2.2. insert+select

insert+select 表示：将后面查询返回的结果作为内容插入到指定表中，注意 OVERWRITE 将覆盖已有数据。

需要保证查询结果列的数目和需要插入数据表格的列数目一致。

如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换

失败的数据将会为 NULL。

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;

2.3. Multiple inserts 多重插入

翻译为多次插入，多重插入，其核心功能是：一次扫描，多次插入。

语法目的就是减少扫描的次数，在一次扫描中。完成多次 insert 操作。

假设我们现在有一张 student 表，现在新建了两张表，student_insert1、student_insert2，想从 student 查询数据插入到这两张表中。

--当前库下已有一张表student
select * from student;

--创建两张新表
create table student_insert1(sno int);
create table student_insert2(sname string);

-- 正常思路来讲
insert into student_insert1
select num from student;
insert into student_insert2
select name from student;

--多重插入
from student
insert overwrite table student_insert1
select num
insert overwrite table student_insert2
select name;

2.4. Insert Directory 导出数据

Hive 支持将 select 查询的结果导出成文件存放在文件系统中。语法格式如下；

注意：导出操作是一个 OVERWRITE 覆盖操作，慎重。

--标准语法:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format]
SELECT ... FROM ...

--Hive extension (multiple inserts):
FROM from statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_sstatement
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select[statement2] ...

--row format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BYY char]][COLLECTION ITEMS
TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BYchar
  • 目录可以是完整的 URI。如果未指定 scheme，则 Hive 将使用 hadoop 配置变量 fs.default.name 来决定导出位置；
  • 如果使用 LOCAL 关键字，则 Hive 会将数据写入本地文件系统上的目录；
  • 写入文件系统的数据被序列化为文本，列之间用\001 隔开，行之间用换行符隔开。如果列都不是原始数据类型，那么这些列将序列化为 JSON 格式。也可以在导出的时候指定分隔符符换行符和文件格式。

3. 再说 Hive 事务表

3.1. 前置回顾

前置内容

3.2. Hive 事务表实现原理

Hive 的文件是存储在 HDFS 上的，而 HDFS 上又不支持对文件的任意修改，只能是采取另外的手段来完成。

  • 用 HDFS 文件作为原始数据（基础数据），用 delta 保存事务操作的记录增量数据；
  • 正在执行中的事务，是以一个 staging 开头的文件夹维护的，执行结束就是 delta 文件夹。每次执行一次事务操作都会有这样的一个 delta 增量文件夹；
  • 当访问 Hive 数据时，根据 HDFS 原始文件和 delta 增量文件做合并，查询最新的数据。

image.png

  • INSERT 语句会直接创建 delta 目录；
  • DELETE 目录的前缀是 delete_delta；
  • UPDATE 语句采用了 split-update 特性，即先删除、后插入；

image.png

3.3. 实现原理之 delta 文件夹命名格式

  • delta_minWID_maxWID_stmtID，即 delta 前缀、写事务的 ID 范围、以及语句 ID；删除时前缀是 delete_delta，里面包含了要删除的文件；
  • Hive 会为写事务（INSERT、DELETE 等）创建一个写事务 ID（WriteID），该 ID 在表范围内唯一；
  • 语句 ID（StatementID）则是当一个事务中有多条写入语句日时使用的，用作唯一标识。

image.png

每个事务的 delta 文件夹下，都有两个文件：

  • _orc_acid_version 的内容是 2，即当前 ACID 版本号是 2。和版本 1 的主要区别是 UPDATE 语句采用了 split-update 特性，即先删除、后插入。这个文件不是 ORC 文件，可以下载下来直接接查看

image.png

bucket_0000 文件则是写入的数据内容。如果事务表没有分区和分桶，就只有一个这样的文件。文件都以 ORC 格式存储，底层二级制，需要使用 ORC TOOLS 查看，详见附件资料。

image.png

  • operation:0 表示插入，1 表示更新，2 表示删除。由于使用了 split-update，UPDATE 是不会出现的，所以
  • delta 文件中的 operation 是 0，delete_delta 文件中的 operation 是 2。
  • originalTransaction、currentTransaction:该条记录的原始写写事务 ID，当前的写事务 ID。
  • rowId:一个自增的唯一 ID，在写事务和分桶的组合中唯一。
  • row:具体数据。对于 DELETE 语句，则为 null，对于 INSERT 就是插入的数据，对于 UPDATE 就是更新后的数据。

3.4. 合并器

  • 随着表的修改操作，创建了越来越多的 delta 增量文件，就需要合并以保持足够的性能。
  • 合并器 Compactor 是一套在 Hive Metastore 内运行，支持 ACID 系统的后台进程。所有合并都是在后台完成的，不会阻止数据的并发读、写。合并后，系统将等待所有旧文件的读操作非完成后，删除旧文件。
  • 合并操作分为两种，minor compaction（小合并）、major compaaction（大合并）:
    • 小合并会将一组 delta 增量文件重写为单个增量文件，默认触发条件为 10 个 delta 文件；
    • 大合并将一个或多个增量文件和基础文件重写为新的基础文件，默认触发条件为 delta 文件相应于基础文件占比，10%。

image.png

3.5. 局限性

虽然 Hive 支持了具有 ACID 语义的事务，但是在使用起来，并没有像像在 MySQL 中使用那样方便，有很多限制；

  • 尚不支持 BEGIN，COMMIT 和 ROLLBACK，所有语言操作都是自动提交的；
  • 表文件存储格式仅支持 ORC（STOREDAS ORC）；
  • 需要配置参数开启事务使用；
  • 外部表无法创建为事务表，因为 Hive 只能控制元数据，无法管理数据
  • 表属性参数 transactional 必须设置为 true；
  • 必须将 Hive 事务管理器设置为 org.apache.hadoop.hive.ql.loockmgr.DbTxnManager 才能使用 ACID 表；
  • 事务表不支持 LOAD DATA...语句。

3.6. 设置参数

  • 客户端参数
-- 可以使用set设置当前session生效也可以配置在hive-site.xml中)
set hive.support.concurrency=true;---Hive是否支持并发
set hive.enforce.bucketing=true;--从Hive2.0开始不再需要是否开启分桶功能
set hive.exec.dynamic.partition.mode=nonstrict;--动态分区模式 非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmg.DbTxnManager;
  • 服务端参数
set hive.compactor.initiator.on=true;--是否在Metastore实例上运行启动压缩合并
set hive.compactor.worker.threads=1;--在此metastore实例上运行多少个合并程序工作线程。

4. Select 查询数据

从哪里查询取决于 FROM 关键字后面的 table_reference。可以是普通物理表、视图、join 结果或子查询结果。

表名和列名不区分大小写。

[WITH CommonTableExpression (, CommonTableExpressionh)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
[LIMIT [offset,] rows];

4.1. 基础语法

image.png

  • where

在 WHERE 表达式中，可以使用 Hive 支持的任何函数和运算符，但聚合函数除外。那么为什么不能在 where 子句中使用聚合函数呢？ 因为聚合函数要使用它的前提是结果集已经确定。而 where 子句还处于"确定"结果集的过程中，因而不能使用聚合函数。

从 Hive0.13 开始，WHERE 子句支持某些类型的子查询。

  • Group by

GROUPBY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

注意：出现在 GROUPBY 中 select_expr 的字段：要么是 GROUPBY 分组的字段；要么是被聚合函数应用的字段。 原因：避免出现一个字段多个值的歧义。

分组字段出现 select_expr 中，一定没有歧义，因为就是基于详该字段分组的，同一组中必相同；

被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果。

image.png

  • Having

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据，并且可以在 Having 中使用聚合函数，因为此时 where，group by 已经执行结束，结果集已经确定。

4.2. 执行顺序

在查询过程中执行顺序：from > where > group (含聚合) > having > order > select

  • 聚合语句（sum，min，max，avg，count）要比 having 子句优先执行
  • where 子句在查询过程中执行优先级别优先于聚合语句（sum，miln， max， avg， count）

结合下面 SQL 感受：

select state, sum(deaths) as cnts
from t_usa_covid19_p
where count_date = '2021-01-28'
group by state
having cnts > 10000;

4.3. 高阶语法

image.png

  • Order by

Hive SQL 中的 ORDER BY 语法类似于标准 SQL 语言中的 ORDERBY 语法，会对输出的结果进行全局排序。

因此当底层使用 MapReduce 引擎执行的时候，只会有一个 reducetask 执行。如果输出的行数太大，会导致需要很长的时间才能能完成全局排序。

默认排序为升序（ASC），也可以指定为 DESC 降序。

在 Hive2.1.0 和更高版本中，支持在 ORDERBY 子句中为每个列宿定 null 类型结果排序顺序。

ASC 顺序的默认空排序顺序为 NULLS FIRST，而 DESC 顺序的默认空排序顺序为 NULLS LAST。

  • Cluster by

根据指定字段将数据分组，每组内再根据该字段正序排序（只能正序）。

概况起来就是：根据同一个字段，分且排序。

分组规则 hash 散列（分桶表规则一样）:Hash_Func(col_name) % reducetask个数

分为几组取决于 reducetask 的个数，默认是 1，即不排序

set mapreduce.job.reduces = 2;
select * from student cluster by num;

执行结果：分为两个部分，部分内部正序排序

image.png

  • Distribute by + Sort by

需求：根据 sex 性别分为两个部分，每个分组内再根据 age 年冷的倒序排序。

CLUSTER BY 无法单独完成，因为分和排序的字段只能是同一个；

ORDER BY 更不能在这里使用，因为是全局排序，只有一个输出，无法满足分的需求。

select * from student cluster by sex order byage desc -- 不可行
select * from student cluster by sex sort by age desc -- 不可行

DISTRIBUTE BY + SORT BY 就相当于把 CLUSTER BY 的功能一分为二：

DISTRIBUTE BY 负责根据指定字段分组；

SORT BY 负责分组内排序规则。

分组和排序的字段可以不同。

select * from student distribute by sex sort by age desc;

如果 DISTRIBUTEBY+SORTBY 的字段一样，则：CLUSTER BY=DISTRIBUTE BY +SORT BY

4.4. 排序对比

  • order by 全局排序，因此只有一个 reducer，结果输出在一个个文件中，当输入规模大时，需要较长的计算时间。
  • distribute by 根据指定字段将数据分组，算法是 hash 散列列。sort by 是在分组之后，每个组内局部排序。
  • cluster by 既有分组，又有排序，但是两个字段只能是同一个字段。

4.5. Union 联合查询

UNION 用于将来自于多个 SELECT 语句的结果合并为一个结果集。

  • 使用 DISTINCT 关键字与只使用 UNION 默认值效果一样，都会删除重复行。1.2.0 之前的 Hive 版本仅支持 UNION ALL，在这种情况下不会消除重复的行。
  • 使用 ALL 关键字，不会删除重复行，结果集包括所有 SELECT 语句的四匹配行（包括重复行）。
  • 每个 select_statement 返回的列的数量和名称必须相同。
select_statement
union [ALL|DISTINCT]
select_statement
union [ALL|DISTINCT]
select_statement...;

如果要将 ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY 或 LIMIT 应用于单个 SELECT，请将子句放在括住 SELECT 的括号内，否则会报错

SELECT num,name FROM (select num, hame from student_local LIMIT 2) subq1
UNION
SELECT num,name FROM (select num,name from studdent_hdfs LIMIT 3) subq2

如果要将 ORDER BY，SORT BY，CLUSTER BY，DISTRRIBUTE BY 或 LIMIT 子句应用于整个 UNION 结果，请将 ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTEBY 或 LIMIT 放在最后一个之后。

select num,name from student_local
UNION
select num,name from student_hdfs
order by num desc;

4.6. from 子句中子查询

在 Hive0.12 版本，仅在 FROM 子句中支持子查询。

必须要给子查询一个名称，因为 FROM 子句中的每个表都必须有一个名称。子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列在外部查询中可用，就像真实表的列一样。子查询也可以是带有 UNION 的查询表达式。

Hive 支持任意级别的子查询，也就是所谓的嵌套子查询。

Hive0.13.0 和更高版本中的子查询名称之前可以包含可选关键字 AS。

--from子句中子查询(Subqueries)
--子查询
SELECT num
FROM (
    select num,name from student_local
) tmp;
--包含UNION ALL的子查询的示例
SELECT t3.name
FROM (
    select num,name from student_local
    UNION distinct
    select num,name from student_hdfs
) t3;

4.7. where 子句中子查询

从 Hivel:0.13 开始，WHERE 子句支持下述类型的子查询

  • 不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为 IN 和 NOTIN 语句的常量；
  • 相关子查询：子查询引用父查询中的列；
--where子句中子查询(Subqueries)
--不相关子查询,相当于IN、NOTIN,子查询只能选择一个列。
--(1)执行子查询,其结果不被显示,而是传递给外部查询,作为外部查询
的条件使用。
(2)执行外部查询,并显示整个结果。
SELECT
FROM student_hdfs
WHERE student_hdfs.num IN (select num from student_local limit 2);
--相关子查询,指EXISTS和NOT EXISTS子查询
--子查询的WHERE子句中支持对父查询的引用
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2Y);

4.8. Common Table Expressions(CTE)

公用表表达式（CTE）是一个临时结果集：该结果集是从 WITH 子句中指定的简单查询派生而来的，紧接在 SELECT 或

INSERT 关键字之前。

CTE 仅在单个语句的执行范围内定义。

CTE 可以在 SELECT， INSERT，CREATE TABLE AS SELECT 或 CREATE VIEW AS SELECT 语句中使用。

image.png

就拿这个东西当一个表用就可以。

with q1 as ( select * from student where num= 95002),
q2 as ( select num,name,age from q1)
select * from (select num from g2) a;

with q1 as (select * from student where num = 95002),
     q2 as (select * from student where num = 95004)
select * from q1 union all select * from q2;

5. join 链接操作

根据数据库的三范式设计要求和日常工作习惯来说，我们通常不会设计一张大表把所有类型的数据都放在一起，而

是不同类型的数据设计不同的表存储。

比如在设计一个订单数据表的时候，可以将客户编号作为一个个外键和订单表建立相应的关系。而不可以在订单表中 添加关于客户其它信息（比如姓名、所属公司等）的字段。

image.png

在这种情况下，有时需要基于多张表查询才能得到最终完整的结果；

join 语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据。

5.1. Join 语法规则

在 Hive 中，当下版本 3.1.2 总共支持 6 种 join 语法。分别是：Inner join 内连接、Left join、Right join、full outer join、Left semi join、Cross join。

SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.departmerht = b.department)

Join 语法丰富，从 Hive0.13.0 开始，支持隐式联接表示法（请参阅 HIVE-5558）。允许 FROM 子句连接以逗号分隔的表列表，而省略 JOIN 关键字。

SELECT
FROM table1 t1, table2 t2, table3 t3
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '025335';

从 Hive2.2.0 开始，支持 ON 子句中的复杂表达式，支持不相等连接（请参阅 HIVE-15211 和 HIVE-15251）。在此之前，Hive 不支持不是相等条件的联接条件。

SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

5.2. Full outer join 全外连接

image.png

5.3. Left semi join 左半开连接

左半开连接（LEFT SEMI JOIN）会返回左边表的记录，前提是是其记录对于右边的表满足 0N 语句中的判定条件。

从效果上来看有点像 inner join 之后只返回左表的结果。

avatar
文章
阅读
粉丝