1 背景：RDBMS中insert使用（insert+values）

在MySQL这样的RDBMS中，通常是insert+values的方式来向表插入数据，并且速度很快。这也是RDBMS中插入数据的核心方式。

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
假如说对Hive的定位不清，把Hive当成RDBMS来使用，也使用insert+values的方式插入数据，会如何呢？
--hive中insert+values
create table t_test_insert(id int,name string,age int);
insert into table t_test_insert values(1,"allen",18);

你会发现执行过程非常非常慢，底层是使用MapReduce把数据写入HDFS的。

试想一下，如何在Hive中这样玩，对于大数据分析，海量数据一条条插入是不是非常刺激。因此在Hive中我们通过将数据清洗成为结构化文件，再Load加载到表中。

但是并不意味着insert语法在Hive中没有使用地位了，通常在Hive中我们使用insert+select语句。即插入表的数据来自于后续select查询语句返回的结果。

2 insert + select

Hive中insert主要是结合select查询语句使用，将查询结果插入到表中，例如：

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将覆盖表或分区中的任何现有数据。

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

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

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;
--step2：创建一张目标表  只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;
select *
from student_insert1;

3 multiple inserts多重插入

multiple inserts可以翻译成为多次插入，多重插入，核心是：一次扫描，多次插入。其功能也体现出来了就是减少扫描的次数。

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

4 dynamic partition insert动态分区插入

4.1 功能

对于分区表的数据导入加载，最常见最基础的是通过load命令加载数据。如下：

create table student_HDFS_p(Sno int,Sname string,Sex string,Sage int,Sdept string) partitioned by(country string) row format delimited fields terminated by ',';
--注意 分区字段country的值是在导入数据的时候手动指定的 China
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS_p partition(country ="China");

接下来我们考虑一下性能问题：

假如说现在有全球224个国家的人员名单（每个国家名单单独一个文件），让你导入数据到分区表中，不同国家不同分区，如何高效实现？使用load语法导入224次？

再假如，现在有一份名单students.txt，内容如下：

95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA

让你创建一张分区表，根据最后一个字段（选修专业）进行分区，同一个专业的同学分到同一个分区中，如何实现？如果还是load加载手动指定，即使最终可以成功，效率也是极慢的。

为此，Hive提供了动态分区插入的语法。

所谓动态分区插入指的是：分区的值是由后续的select查询语句的结果来动态确定的。根据查询结果自动分区。

4.2 配置参数

关于严格模式、非严格模式，演示如下：

FROM page_view_stg pvs
INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt
--在这里，country分区将由SELECT子句（即pvs.cnt）的最后一列动态创建。
--而dt分区是手动指定写死的。
--如果是nonstrict模式下，dt分区也可以动态创建。

4.3 案例：动态分区插入

--动态分区插入
--1、首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
--2、当前库下已有一张表student
select * from student;
--3、创建分区表 以sdept作为分区字段
--注意：分区字段名不能和表中的字段名重复。
create table student_partition(Sno int,Sname string,Sex string,Sage int) partitioned by(Sdept string);
--4、执行动态分区插入操作
insert into table student_partition partition(Sdept)
select Sno,Sname,Sex,Sage,Sdept from student;
--其中，Sno,Sname,Sex,Sage作为表的字段内容插入表中
--Sdept作为分区字段值

最终执行结果如下，可以发现实现了自动分区：

5 insert + directory导出数据

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

--标准语法:
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
    [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
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]

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

目录可以是完整的URI。如果未指定scheme或Authority，则Hive将使用hadoop配置变量fs.default.name中的方案和Authority，该变量指定Namenode URI。

如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录。

写入文件系统的数据被序列化为文本，列之间用^ A隔开，行之间用换行符隔开。如果任何列都不是原始类型，那么这些列将序列化为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;

6 Hive Transaction事务

6.1 Hive事务背景知识

Hive本身从设计之初时，就是不支持事务的，因为Hive的核心目标是将已经存在的结构化数据文件映射成为表，然后提供基于表的SQL分析处理，是一款面向分析的工具。

并且Hive映射的数据通常存储于HDFS上，而HDFS是不支持随机修改文件数据的。

这个定位就意味着在早期的Hive的SQL语法中是没有update，delete操作的，也就没有所谓的事务支持了，因为都是select查询分析操作。

从Hive0.14版本开始，具有ACID语义的事务（支持INSERT，UPDATE和 DELETE这些用例）已添加到Hive中，以解决以下场景下遇到的问题：

➢ 流式传输数据。使用如Apache Flume或Apache Kafka之类的工具将数据流式传输到现有分区中，但是这会使读者感到脏读（也就是说，开始查询后能看到写入的数据）。

➢ 变化缓慢的维度数据。在典型的星型模式数据仓库中，维度表随时间缓慢变化。例如，零售商将开设新商店，需要将其添加到商店表中，或者现有商店可能会更改其平方英尺或某些其他跟踪的特征。这些更改导致插入单个记录或更新记录（取决于所选策略）。

➢ 数据更新。有时发现收集的数据不正确，需要更正。

6.2 Hive事务表局限性

虽然Hive支持了具有ACID语义的事务，但是在使用起来，并没有像在MySQL中使用那样方便，有很多局限性。原因很简单，毕竟Hive的设计目标不是为了支持事务操作，而是支持分析操作，且最终基于HDFS的底层存储机制使得文件的增加删除修改操作需要动一些小心思。具体限制如下：

➢ 尚不支持BEGIN，COMMIT和ROLLBACK。所有语言操作都是自动提交的。

➢ 仅支持ORC文件格式（STORED AS ORC）。

➢ 默认情况下事务配置为关闭。需要配置参数开启使用。

➢ 表必须是分桶表（Bucketed）才可以使用事务功能。外部表无法创建事务表。

➢ 表参数transactional必须为true；

➢ 外部表不能成为ACID表，不允许从非ACID会话读取/写入ACID表。

6.3 案例：创建使用Hive事务表

--Hive中事务表的创建使用
--1、开启事务配置（可以使用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.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动压缩合并
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。
--2、创建Hive事务表
create table trans_student(
    id int,
    name String,
    age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--3、针对事务表进行insert update delete操作
insert into trans_student (id, name, age)
values (1,"allen",18);
update trans_student
set age = 20
where id = 1;
delete from trans_student where id =1;
select *
from trans_student;

7. DML-Update、Delete更新、删除数据

首先，必须明确，你理解的Hive这款软件，定位是什么？是面向事务支持事务的RDBMS?还是面向分析，支持分析的数据仓库。这很重要。

Hive是基于Hadoop的数据仓库，面向分析支持分析工具。因此在Hive中常见的操作的就是分析查询select操作。将已有的结构化数据文件映射成为表，然后提供SQL分析数据的能力。

因此Hive刚出现的时候是不支持update和delete语法支持的，因为Hive所处理的数据都是已经存在的结构化文件，加载到hive表中即可。

后续Hive支持了相关的update和delete操作，不过有很多约束。详见Hive事务的支持。

7.1 update操作

–1、开启事务配置（可以使用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.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动压缩合并
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

--2、创建Hive事务表
create table trans_student(
                              id int,
                              name String,
                              age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--3、针对事务表进行insert update delete操作
insert into trans_student (id, name, age)
values (1,"allen",18);
select *
from trans_student;
update trans_student
set age = 20
where id = 1;

7.2 delete操作

--1、开启事务配置（可以使用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.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动压缩合并
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。
--2、创建Hive事务表
create table trans_student(
                              id int,
                              name String,
                              age int
)clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');
--3、针对事务表进行insert update delete操作
insert into trans_student (id, name, age)
values (1,"allen",18);
select *
from trans_student;
delete from trans_student where id =1;

大数据Hive数据操纵语言DML

1 背景：RDBMS中insert使用（insert+values）

2 insert + select

3 multiple inserts多重插入

4 dynamic partition insert动态分区插入

4.1 功能

4.2 配置参数

4.3 案例：动态分区插入

5 insert + directory导出数据

6 Hive Transaction事务

6.1 Hive事务背景知识

6.2 Hive事务表局限性

6.3 案例：创建使用Hive事务表

7. DML-Update、Delete更新、删除数据

7.1 update操作

7.2 delete操作

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

大数据Hive数据操纵语言DML

1 背景：RDBMS中insert使用（insert+values）

2 insert + select

3 multiple inserts多重插入

4 dynamic partition insert动态分区插入

4.1 功能

4.2 配置参数

4.3 案例：动态分区插入

5 insert + directory导出数据

6 Hive Transaction事务

6.1 Hive事务背景知识

6.2 Hive事务表局限性

6.3 案例：创建使用Hive事务表

7. DML-Update、Delete更新、删除数据

7.1 update操作

7.2 delete操作

热门文章

最新文章

相关课程

相关电子书

相关实验场景