Hive学习笔记-HiveSQL(2)-数据操控语言DML与数据查询语言DQL

Hive SQL DML

Load 加载数据

在前面的笔记中我们看到当我们在Hive中建表成功之后，在HDFS上就会创建一个对应的文件夹，我们可以利用hadoop fs -put直接将数据上传到HDFS上，Hive会自动完成表和底层数据的映射。

这种方式能够达到目标，但是Hive推荐我们使用Load命令来将数据加载到表中。Load命令能够将数据文件移动到Hive表中对应的位置，在过程中不会对数据内容进行任何转换的操作，保持数据原原本本的状态。

语法规则如下：

1
2
3
4
load data [local] inpath 'filepath' [overwrite] 
into table table_name
[partition(part_col1=val1, part_col2=val2,...)]
[inputformat 'inputformat' serde 'serde']
  • filepath：表示待移动的数据的位置，可以是相对路径、绝对路径和具有schema的完整URI。并且需要结合local关键字来确定数据文件的位置，可能是本地数据文件，HDFS数据文件或者其他文件系统下的文件
    • 如果没有指定schema，则schema由Hadoop配置文件中的参数fs.default.name指定
    • local的本地指的是HiveServer2服务所在的本地Linux文件系统
  • overwrite：覆盖目标表或者分区中已经存在的数据
  • partition：用于手动指定分区（静态分区操作）
  • inputformat和serde：用于指定输入格式，例如Text、Orc等

实际常见的数据加载情况有如下的可能：

  1. 从本地文件系统中加载数据：使用local关键字，本质是hadoop fs -put上传操作
  2. 从HDFS上加载数据：不使用local关键字，本质是hadoop fs -mv移动操作

在Hive3.0之后，Load提供了新特性，在某些场合下会将Load重写为insert as select。

具体来说，我们可以利用load来插入分区表。如果在load的时候，表具有分区，但是我们没有在命令中指定分区，则会将load转化为insert as select，并会假定最后一列作为分区列。这样的话就会要求我们文件的实际列数多于表中的列数，因为最后的列会作为分区列。这也是一种动态分区加载的形式

Insert 插入数据

在RDBMS中，我们通常使用insert+values的方式来向表中插入数据，并且执行速度都非常快。在Hive中，我们也可以使用insert+values的方式来插入数据，但是指定的速度非常慢，原因在于Hive的底层会使用MapReduce任务来将数据写入Hive表中，因此我们常用的数据加载方式是使用Load命令而不是insert。

我们更多的使用方法是insert+select，这表示我们将后面查询返回的结果作为内容插入到指定表中，语法如下：

1
2
3
4
5
6
7
8
9
10
insert into table table_name
[partition(part_col1=val1,...)]
select 语句

-- 或者

insert overwrite table table_name 
[partition(part_col1=val1,...)]
[if not exists]
select 语句

同时Hive中还提供多重插入的功能，Multiple inserts。多重插入指的是一次扫描，多次插入。对应的场景为，我们可能要将一张表中的不同内容插入到不同的表中，按照已有的知识，我们可以使用多个insert+select语句，这样会多次扫描同一张表。多重插入的思想就是减少扫描的次数，在一次扫描中完成多次的insert操作，应用举例如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
--多个insert语句
insert into table1
select col1 from test_table;

insert into table2
select col2 from test_table;

--多重插入
from test_table
insert into table table1
select col1
insert into table table2
select col2

前面在分区表中，我们也提到过动态分区加载的手段，也是通过insert+select来完成的，详细的细节可以参考前面的的笔记。

另外，我们可以利用insert来进行数据的导出，将select查询的结果导出成文件存放在文件系统中，语法如下：

1
2
3
4
5
6
7
insert overwrite [local] directory directory1
    [row format row_format] [stored as file_format]
select ... from ...

--其中的row format如下:
row format delimited [fields terminated by char [escaped by char]] [collection items terminated by char]
[map keys terminated by char] [lines terminated by char]
  • 需要注意这里的overwrite会覆盖我们指定的目录
  • local表示指定本地文件系统下的目录
  • 目录可以是完整的指定了scheme的URI，如果没有指定，则同上，使用Hadoop配置变量fs.default.name来决定
  • 写入文件系统的数据会被序列化为文本，默认列之间使用\001分隔，行之间使用换行符分隔。列也可能是复杂数据类型，则默认会序列化成json的格式。我们也可以在导出的时候自定义指定不同的换行符

事务表相关操作

除了Insert之外，常见的DML还有update和delete，但是在前面的笔记中我们也提到，Hive早期是不支持这些操作的，而是在后续的版本迭代中慢慢引入了事务表的支持。在事务表上，我们才可以使用update和delete操作。事务表的背景、创建和局限这里也不再赘述，我们主要关注这些操作的具体原理。

Hive的文件存储在HDFS上，但是HDFS并不支持对文件的任意修改，则必须采用另外的手段来达成对应的效果。

事务的操作，会以HDFS文件作为原始的基础数据，用delta文件夹来保存事务操作，记录增量数据。正在执行的事务，会用一个staging开头的文件夹来维护，执行结束之后，得到delta文件，对应事务执行的结果。每执行一次事务操作，都会得到这样一个delta增量文件夹，当访问Hive数据的时候，会根据HDFS原始数据和delta增量文件进行合并，查询最新的数据。

delta文件夹的具体命名格式为delta前缀_minID_maxID_stmID

  • delta前缀：如果是insert和update，前缀为delta；如果操作是delete，前缀为delete_delta（update语句采用split-update特性，相当于先删除，后插入）
  • minID和maxID：写事务的ID范围
  • stmID：语句ID

在Hive中，会为写事务（insert、update等）创建一个写事务ID，该ID在表范围内唯一；同时有一个语句ID，是在当一个事务中有多条写入语句时使用的，用作唯一标识。

随着表修改操作的增多，会产生越来越多的delta增量文件，这个时候应该需要进行文件的合并来保证性能。合并器Compactor就是一套在Hive Metastore内部运行，用来支持ACID系统的后台进程。所有合并在后台完成，并不会阻止数据的并发读写。合并后，系统将等待所有旧文件读操作完成之后，删除旧文件。

合并操作分为两种，Minor Compaction和Major Compaction

  • Minor Compaction：小合并，将一组delta增量文件重写为单个增量文件，默认触发条件为10个delta文件
  • Major Compaction：大合并，将一个或多个增量文件以及基础文件重写为新的基础文件，默认触发条件为delta文件相应于基础文件超过10%

Hive SQL DQL

select 语法树

Hive中的DQL数据查询语言主要指的就是select查询操作，select的语法树如下所示（表名和列名不区分大小写）

select 基础查询语法

select中的基础查询语法包括如下关键字：

1
2
3
4
5
6
select all distinct ...
from ...
where ...
group by ...
having ...
limit ...

这些基础语法的用法与MySQL中的语法类似，这里只简单介绍一下

  • 默认使用all表示不去重，distinct表示去重

  • 分区裁剪：在查询的时候可以指定分区进行查询，减少全表扫描（可以将分区的虚字段当成普通字段使用）

  • 出现在group by中select的字段，要么是group by分组的字段，要么是被聚合函数应用的字段

  • where和having的含义与用法与SQL相同

  • limit用于限制select语句返回的行数

  • 执行顺序：from > where > group > having > order > select

  • 在select语句中可以使用正则表达式来匹配字段

    1
    2
    3
    4
    5
    --查询匹配正则表达式的所有字段
    set hive.support.quoted.identifiers=none;
    --表示反引号不再解释为其他含义，被解释为正则表达式
    select `^c.*` from table_name
    --查询所有以c开头的列字段

select 排序相关语法

Hive SQL中的排序相关语法在语法树中主要体现为以下关键字：

1
2
3
order by
cluster by
distribute by sort by

order by

首先是order by语法。Hive SQL中的order by语法也类似于标准SQL中的order by语法，它会对输出的结果进行全局排序。

这也就意味着当底层是使用MapReduce引擎执行的时候，只有一个Reduce Task执行。如果输出的行数太大，会导致需要很长时间才能完成全局排序

默认的排序顺序为升序asc，也可以指定为降序desc

在Hive2.1.0之后，也支持在order by子句中为每个列指定null类型结果排序顺序，关键字为nulls first和nulls last。asc顺序的默认空排序顺序为nulls first、desc顺序的默认空排序顺序为nulls last。

强烈建议将limit于order by配合使用，以避免数据集行数过大

当hive.mapred.mode设置为strict严格模式的时候，使用不带limit的order by会引发异常

cluster by

前面的order by可以对数据进行全局排序，cluster by达到的效果就是根据字段将数据进行分组，然后每组内再根据该字段进行正序排序（只能正序）。总结来说就是根据同一个字段划分，之后再分区内部正序排序

这里就会涉及到两个重要的问题，如何划分以及划分成多少个区域

划分的规则与分桶表相同，利用hash散列函数。而划分成多少个区域则取决于启动的Reduce Task任务的个数。如果我们不指定Reduce Task任务的话，默认情况只启动一个Reduce Task，这种情况就和正序的order by类似；当然我们也可以手动指定Reduce Task的个数

1
2
3
4
5
6
7
8
9
--不指定Reduce Task个数，默认为1
select * from table_name 
cluster by col_name

--手动指定Reduce Task个数
set mapreduce.job.reduces=2;

select * from table_name 
cluster by col_name

distribute by sort by

从前面cluster by的介绍可以看出，虽然达到了分区的效果，但是还是有一定的局限性，即分区和排序只能按照同一个字段进行。而distribute by + sort by的效果就是分区和排序按照不同的字段，distribute by指定分区字段，sort by指定排序字段，同时可以指定正序或者倒序字段。

1
2
3
select * from table_name
distribute by col1
sort by col2 desc

如果distribute by和sort by指定同一个字段并且使用正序排序，就和cluster by功能相同。

排序相关语法的对比：

  1. order by是全局排序，无论配置如何，最终启动的还是只有一个Reduce Task任务
  2. cluster by完成分组和排序，但是只能按照同一个字段进行，且是正序排序。分组算法为hash散列，分组个数与Reduce Task个数有关，可以手动设置，也可以是使用默认的1
  3. distribute by + sort by可以看作是升级版的cluster by，分组字段和排序字段可以不同

union 联合查询

union用于将来自多个select语句的结果合并成为一个结果集，后面可以使用all或者distinct关键字，表示是否删除重复的行。默认行为为distinct

SQL中的union默认行为也是distinct

union必须保证每个select语句返回的列数量和名称必须相同

1
2
3
4
5
select_statement
	union [all | distinct]
select_statement 
	union [all | distinct] 
select_statement ...;

这里需要注意的是，使用union的必须是单个select语句，如果要应用order by，sort by，cluster by，distribute by或limit的话，应该要使用子查询的语法

1
2
3
select col1,col2 from (select col1,col2 from table_1 limit 2)  temp1
union
select col1,col2 from (select col1,col2 from table_2 limit 3) temp2;

CTE使用

CTE，全称为Common Table Expressions，意为公共表表达式。这是一个临时结果集，从with子句中的指定查询派生而来。CTE定义临时结果集之后，可以当作一个普通的表来使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--select语句中的CTE
with q1 as (select col1,col2 from table_name where col3 = xxx)
select *
from q1;

-- from风格
with q1 as (select col1,col2 from table_name where col3 = xxx)
from q1
select *;

-- chaining CTEs 链式风格
with q1 as (select col1,col2 from table_name where col3 = xxx),
     q2 as (select col1,col2 from table_name where col3 = yyy)
select * from (select col1 from q2) a;
  • CTE仅在单个语句的执行范围内定义

Hive SQL join 连接操作

在Hive中目前支持6中join语法，分别是inner join（内连接）、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（左半开连接）、cross join（交叉连接，也叫做笛卡尔乘积）。join的条件仍然是使用on来指定

从Hive 0.13.0开始，支持隐式联接表示法。允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字。

从Hive 2.2.0开始，支持ON子句中的复杂表达式，支持不相等连接。在此之前，Hive不支持不是相等条件的联接条件。

其中内连接、左连接、右连接、全外连接这些操作和SQL中的定义都相同，我们已经很熟悉了，这里就介绍剩下的左半开连接和交叉连接。

左半开连接（left semi join）类似于内连接的逻辑，但是只返回其中在左表的字段。即最终返回的结果行是内连接得到的行，但是字段只有在左表中出现的字段。

交叉连接（cross join）则会返回两个表的笛卡尔积。

  • 在SQL标准中，笛卡尔积是没有条件的inner join
  • 在Hive SQL语法中，cross join后面可以使用where子句或on条件过滤，但是还是先笛卡尔积，然后根据条件过滤

join的使用注意事项：

  1. 允许使用复杂的连接表达式，支持非等值连接

  2. 同一个查询中可以连接两个以上的表

  3. 如果每个表在连接子句中使用相同的列，则Hive会将多个表上的连接转换成单个MapReduce作业

    1
    2
    3
    4
    5
    SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
    --由于联接中仅涉及b的key1列，因此被转换为1个MR作业来执行
    SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
    --会转换为两个MR作业，因为在第一个连接条件中使用了b中的key1列，而在第二个连接条件中使用了b中的key2列
    --第一个map / reduce作业将a与b联接在一起，然后将结果与c联接到第二个map / reduce作业中

  4. join时最后一个表会通过Reducer进行流式传输，并在其中缓存之前的其他表（相当于是将最后一个表流式传输，进来一个join一个）。因此将大表放置在最后有助于减少Reducer阶段缓存数据所需要的内存（也是我们通常习惯的小表接大表）

  5. 在join的时候，也可以通过关键字streamtable显式指定需要流式传输的表。如果省略，则Hive将流式传输最右边的表

    1
    2
    3
    select streamtable(a) a.val, b.val c.val
    from a join b on (a.key = b.key1)
    join c on (c.key = b.key1)

  6. 如果除了一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行（mapjoin，没有Reduce阶段）

    1
    2
    3
    4
    5
    select mapjoin(b) a.key, a.value 
    from a join b on a.key=b.key
    --这样就不需要启动reducer，对于a的每个Mapper，b表数据都会被完全读取。
    --限制是不能执行full/right outer join b
    --因为full join b或者right outer join b，b表中的一行都要去找所有的a表数据
大数据 > Hive
#笔记 #Hive
Hive学习笔记-HiveSQL(2)-数据操控语言DML与数据查询语言DQL
http://example.com/2022/07/04/Hive学习笔记-HiveSQL-2-数据操控语言DML与数据查询语言DQL/
作者
EverNorif
发布于
2022年7月4日
许可协议