HiveDML表数据操作

DML ( Database Manipulation Lanuage ) 数据库操作语言，如 load、select 、update 、insert。主要用来对数据库的数据进行一些操作。

目录

• 1. load 数据加载
• 1. 导入HDFS数据 ( 移动 )
• 2. 导入服务器本地数据 ( 复制 )
• 2. insert 数据插入
• 1. 单条数据插入
• 2. 多条数据插入
• 3. 多重插入
• 4. 分区表数据插入
• 1. 静态分区方式
• 2. 动态分区
• 5. 分桶表数据插入
• 3. insert 数据导出
• 单表导出
• 多重导出
• 4. join 连接
• 1. 内连接
• 1. 只支持等值连接
• 2. 只支持and连接，不支持or
• 3. 支持多表连接
• 2. 外连接
• 1. 左外连接
• 2. 右外连接
• 3. 全外连接
• 3. 半连接
• 5. 查询
• 查询语法
• 1. CLUSTER BY​​
• 2. DISTRIBUTE BY​
• 3. SORT BY​
• 4. ORDER BY
• 另：分桶的作用

---

1. load 数据加载

load data

-- 如建表
create external table if not exists '表名'(id int, name string) 
row format delimited fields terminated by ',' 
location '/user/external';

1. 导入HDFS数据 ( 移动 )

公共表。建外部表，会加载到hive的指定路径下，不能有两个完全同名文件，第二次传入的数据会重命名_copy_01。

load data inpath '/app.log' into table '表名';

2. 导入服务器本地数据 ( 复制 )

load data local inpath '/data/app.log' into table '表名';

-- 添加分区并数据加载不用load,load不会进行数据监测
alter table '表名' add partition (`date`='xxx');

-- 指定加载到哪个分区
load data local inpath '/home/app.log' into table '表名' partition (`date`='xxx');

 

2. insert 数据插入

1. 单条数据插入

先建立临时表，再将临时表中数据拷贝到指定表中，效率低。

insert into table '表名' values(1,'xxx');

2. 多条数据插入

从一个表中查询，将结果全部插入到指定表中，一次只能一个表。

-- 建表
create external table if not exists '表名' (id int,name string) 
row format delimited fields terminated by ',' ;

-- 例如
insert into table '表名' select * from stu where id > 20;

3. 多重插入

一次插入多个表，但对基表值扫描一次。

from 表名 (基表)
insert into table 表1 select ...
insert into table 表2 select ...

-- 例如
from 表名
insert into table 表1 select * where id >=24
insert into table 表2 select * where id < 24;

4. 分区表数据插入

分区表只是建表时不同，使用时跟普通字段没区别。

create external table if not exists '表名' (id int, name string) 
partitioned by (`date` string) 
row format delimited fields terminated by ',';

1. 静态分区方式

添加前手动指定分区，不建议。

insert into table '表名' partition(`date`='xxx') values(..)

多条数据插入

insert into table '表名' partition(`date`='xxx') 
select id, name from '表名2' where`date`='xxx';


from '表名2'
insert into table '表名' partition(`date`='xxx') select id,name where `date`='xxx'
insert into table '表名' partition(`date`='xxx') select id,name where `date`='xxx';

2. 动态分区

分区个数不确定，根据数据自动分区。设置动态分区模式为非严格模式。

set hive.exec.dynamic.partition.mode = nonstrict;

默认严格模式,必须手动添加一个静态分区
set hive.exec.dynamic.partition.mode=strict;

hive-1.2.2需设置开启动态分区。动态分区字段必须放在最后，最后一个字段默认为分区字段。

set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict; ​

insert into table '表名' partition(`date`) select id, name from '原表'; 

多字段分区，目录结构按分区字段顺序划分。hive1.2中进行动态分区如果是多字段分区，必须手动指定第一级分区。

create external table if not exists '表名'(id int, name string)
partitioned by (`date` string, age int)
row format delimited fields terminated by ',';

insert into table '表名' partition(`date`, age) 
select id, name, `date` from '原表';

 

5. 分桶表数据插入

创建分桶表

create table if not exists '表名'(id int,name string,age int,sex string) 
clustered by(age) sorted by(sex) into 3 buckets 
row format delimited fields terminated by ',';

加载数据

不能使用 load 方式，只能使用 insert。

insert into table '分桶表' select * from '原表';

-- 每个reducer的吞吐量,默认256M​
set hive.exec.reducers.bytes.per.reducer=<number> 
-- reducetask的最多的执行个数
set hive.exec.reducers.max=<number> 
-- 设置reducetask实际运行个数 -1代表没有设置​
set mapreduce.job.reduces=<number>

 

3. insert 数据导出

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement

local 导出到本地 ,导出到hdfs不需要local

单表导出

insert overwrite local directory '/home/app.log' 
select * from '表名' where `date`='xxx';

多重导出

from stu
insert overwrite local directory '/home/app.log.1' 
select * from '表名' where `date`='xxx'
insert overwrite local directory '/home/app.log.2' 
select * from '表名' where `date`='xxx';

 

4. join 连接

create external table '表名1'(id int, name string) 
row format delimited fields terminated by '\t' 
location '/user/hive/warehouse/xx.db/xxx';​

create external table '表名2'b(id int, name string)
row format delimited fields terminated by '\t' 
location '/user/hive/warehouse/xx.db/xxx';​

select a.*,b.* from 表名1 a inner join 表名2 b on a.id=b.id;​​ 
-- 默认是内连接
​select a.*,b.* from 表名1 a join 表名2 b on a.id=b.id; 

1. 内连接

两表中都有的才会连接。

 

1. 只支持等值连接

-- 转换为mr能找到map端的key,不能使用 >
select a.*,b.* from a join b on a.id = b.id; 

2. 只支持and连接，不支持or

where a.id = b.id and a.name = b.name;

3. 支持多表连接

 

-- 多表关联是同一字段，只需要一个MR
select a.*,b.*,c.* from a join b on a.id = b.idjoin c on a.id=c.id;
-- ​不是同一个字段会转换为多MR
select a.*,b.*,c.* from a join b on a.id = b.idjoin c on b.age=c.age;​

 

2. 外连接

1. 左外连接

左表为基表，右表有的字段会关联,没有的null显示，右表中有左表中没有的数据不会关联。

select a.*,b.* from 表名1 a left outer ​join 表名2 b on a.id=b.id;​​

2. 右外连接

select a.*,b.* from 表名1 a right outer ​join 表名2 b on a.id=b.id;​​

3. 全外连接

左右表取并集，两个表关联字段合并。

select a.*,b.* from 表名1 a full outer ​join 表名2 b on a.id=b.id;

3. 半连接

为了解决 select id from a where id in (1,2,4,5) 的情况

一般用左半连接，查找左半边表中id在右半边表中存在的数据，只显示左表存在的数据。

select a.* from 表名1 a left semi ​join 表名2 b on a.id=b.id;

5. 查询

注意: hive中一定要尽量避免select * from 全表扫描。

查询语法

只能查询使用

SELECT [ALL | DISTINCT] select_ condition, select_ condition, ...
FROM table_name a
[JOIN table_other b ON a.id = b.id]
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list | ORDER BY col_list] ]
[LIMIT number]

1. CLUSTER BY​​

既分组又排序。

select * from 表名 cluster by 字段;

注意：

1. cluster by 与 sort by 不能同时使用

2. 当分组字段和排序字段是同一个字段时 cluster by id = distribute by id + sort by id

 

2. DISTRIBUTE BY​

只分不排。类似于分桶，按照指定 distribute by 字段和设置的 reducetask个数进行取余分组，不排序。

select * from 表名 distribute by 字段;

3. SORT BY​

局部排序 ( 只排不分 )。每个reducetask中排序，一个reducetask时与order by效果相同。

指定的字段只是用于排序的字段，不用于分reducetask输出结果,最终的输出文件是随机生成的。

select * from 表名 sort by 字段 desc;​

4. ORDER BY

全局排序，默认升序。

select * from 表名 order by 字段 desc;

 

建表语句            只能建表使用
clustered by       指定分桶
sorted by            指定分组
partitioned by    指定分区

分桶的作用

hive中的表都是大表，会出现大表1关联大表2，大表1的每一个数据都要去全表扫描大表2中的每个数据，效率低。
将两个表都进行分桶，分桶规则一致，桶数成倍数。
1. 提高join的效率
2. 提高抽样的性能