【Hive】4-HiveSQL 数据操控语言（DML）

HiveSQL 数据操控语言（DML）

load加载数据

语法

-- 语法规则
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2...)]
-- 语法规则（Hive 3.0及以后）
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2...)][INPUTFORMAT 'inputformat'SERDE 'serde']

语法规则之filepath

filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。
filepath文件路径支持下面三种形式，要结合LOCAL关键字—起考虑︰
1. 相对路径，例如: project/datal
2. 绝对路径，例如︰/user/hive/project/data1
3. 具有schema的完整URI，例如: hdfs://namenode:9000/user/hive/project/data1

语法规则之LOCAL

指定LOCAL，将在本地文件系统中查找文件路径。
1. 若指定相对路径，将相对于用户的当前工作目录进行解释；
2. 用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1。
没有指定LOCAL关键字。
1. 如果filepath指向的是一个完整的URI，会直接使用这个URI ;
2. 如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的(不出意外，都是HDFS)。

LOCAL本地是哪里？

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

在这里插入图片描述

语法规则之OVERWRITE

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

Hive3.0新特性

Hive3.0+，load加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为INSERT AS SELECT。
Hive3.0+，还支持使用inputformat、SerDe指定输入格式，例如Text，ORC等。
比如，如果表具有分区，而load命令没有指定分区，则将load转换为INSERT AS SELECT，并假定最后一组列为分区列，如果文件不符合预期，则报错。

示例

-- 1、创建分区表
create table if not exists tab1(col1 int,col2 int)
partitioned by (col3 int)
row format delimited fields terminated by ',';-- 2、使用load命令加载数据，不指定分区
load data local inpath '/root/hivedata/tab1.txt' into table tab1;-- tab1.txt内容如下：
11,22,1
55,66,2-- 3、查询表
select * from tab1;

表查询结果：

在这里插入图片描述

HDFS查看（自动创建分区）：

在这里插入图片描述

insert+select使用方式

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

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

清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。
但是并不意味insert语法在Hive中没有用武之地。

Insert使用方式

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

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

-- 方式1（此方式不支持指定插入列，会覆盖已有数据）
INSERT OVERWRITE TABLE tablename1[PARTITION (partcol1=val1, partcol2=val2...)[IF NOT EXISTS]] 
select_statement1 FROM from_statement;
-- 方式2（此方式支持指定插入列）
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2...)][(col1,col2,...)]
select_statement1 FROM from_statement;

INSERT INTO TABLE语法可以不带TABLE关键字

Multiple Inserts 多重插入

翻译为多次插入，多重插入，其核心功能是∶一次扫描，多次插入。
语法目的就是减少扫描的次数，在一次扫描中。完成多次insert操作。

-- 当前库已有一张表student
select * from student;
-- 创建两张表
create table student_insert1(sno int);
create table student_insert2(sname string);
-- 正常插入
insert into table student_insert1 select num from student;
insert into table student_insert2 select name from student;
-- 多重插入
from student
insert overwrite table student_insert1 select num
insert overwrite table student_insert2 select name;

Dynamic Partition Inserts 动态分区插入

动态分区插入指的是︰分区的值是由后续的select查询语句的结果来动态确定的。

根据查询结果自动分区。

参考前面笔记：[分区表数据加载–动态分区]

Insert Directory 导出数据

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

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

-- 标准语法:
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_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
-- row_format
:DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]][COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char][LINES TERMINATED BY char]

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

示例：

-- 当前厍下已有一张表student
select * from student;
-- 1、导出查询结果到HDFS指定目录下
insert overwrite directory '/tmp/hive_export/e1' select * from student;
-- 2、导出时指定分隔符和文件存储格式
insert overwrite directory '/tmp/hive_export/e2' row format delimited fields terminated by ','stored as orc
select * from student;
-- 3、导出数据到本地文件系统指定目录下
insert overwrite local directory '/root/hive_export/e1' select * from student;