大数据Hive数据操纵语言DML
目录
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;