Hive常用SQL之DML

load

load加载操作是将数据文件移动到与Hive表对应的位置的纯复制/移动操作。若文件从本地文件系统加载，则将文件复制一份到HDFS的数据库目录中；若文件从HDFS中加载，则将文件从原位置移动到数据库目录下。

使用语法：

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

若指定local参数，表示从本地文件系统加载数据，反之从HDFS。

建议在Hive中使用load命令加载数据到表中，这也是Hive比Mysql加载数据高效地方所在。

值得注意的是，指定local后并不是从客户端的本地文件系统加载数据，而是从Hive服务端（HS2）的文件系统中寻找。

insert

与Mysql相同，Hive中也支持insert+values的方式插入数据，但是效率极其低下。因为底层是通过MapReduce插入数据的 因此实际中推荐使用load加载数据。

create table t_insert(id int,name string);
insert into table t_insert values(1,"allen");

insert + select

在hive中，insert主要是结合select查询语句使用，将查询结果插入到表中。

• 保证后面select查询语句返回的结果字段个数、类型、顺序和待插入表一致；
• 如果不一致，Hive会尝试帮你转换，但是不保证成功；
• insert+select也是在数仓中ETL数据常见的操作。

--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;

Multi inserts 多重插入

• 功能：一次扫描，多次插入

create table source_table (id int, name string) row format delimited fields terminated by ',';

create table test_insert1 (id int) row format delimited fields terminated by ',';
create table test_insert2 (name string) row format delimited fields terminated by ',';

--普通插入：
insert into table test_insert1 select id from source_table;
insert into table test_insert2 select name from source_table;

--在上述需求实现中 从同一张表扫描了2次 分别插入不同的目标表中 性能低下。

--多重插入：
from source_table                     
insert overwrite table test_insert1 
select id
insert overwrite table test_insert2
select name;
--只需要扫描一次表  分别把不同字段插入到不同的表中即可 减少扫描次数 提高效率

Dynamic partition inserts 动态分区插入

• 何谓动态分区，静态分区

  针对的是分区表。
  --问题：分区表中分区字段值是如何确定的？
  
  1、如果是在加载数据的时候人手动写死指定的  叫做静态分区 
  load data local inpath '/root/hivedata/usa_texas.txt'
  into table t_user_double_p partition(guojia="usa",sheng="texas");
  
  2、如果是通过insert+select 动态确定分区值的，叫做动态分区
  insert table partition (分区字段) + select

• 栗子

  --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 num,name,sex,age,dept from student;
  --其中，num,name,sex,age作为表的字段内容插入表中
  --dept作为分区字段值
  
  select *
  from student_partition;
  
  show partitions student_partition;

导出数据

• 功能：把select查询的结果导出成为一个文件。

• 注意：导出操作是一个overwrite操作，将会清空整个目标文件夹，效果不亚于rm -rf /*！

• 语法

  --当前库下已有一张表student
  select * from student;
  
  --1、导出查询结果到HDFS指定目录下
  insert overwrite directory '/tmp/hive_export/e1'
  select num,name,age
  from student limit 2;   --默认导出数据字段之间的分隔符是\001
  
  --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;