HiveDML表数据操作

你的名字 2021-09-23 06:56 299阅读 0赞

> 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输出结果,最终的输出文件是随机生成的。