Hive——Data Managed Language(DML)（一）

Hive中表的概述

Hive中的表可分为内部表和外部表，在此基础上对表数据进行细粒度的分割，又有分区表和分桶表，分桶表是比分区表更细粒度的对数据进行分割。

内部表： 在创建表时，未被external修饰的是内部表（managed table）。

hive的表与数据库中的表的概念是相似的，每一个表在HIVE中都有一个相对应的目录，该目录是存储数据的路径，所有表数据（不包括外部表）都保存在这个目录中，数据以文件的形式保存在hdfs文件系统上，表的元数据保存在元数据库中，通常为mysql，删除内部表时，元数据和数据都会被删除。

外部表： 在创建表时，被external修饰的为外部表（external table）。

在删除外部表时，只删除mysql中的元数据，而不会删除存储在hdfs中的原始数据。

创建内部表和外部表

创建内部表

create table managed_table(
id int,
name string,
hobby array<string>,
extra_info map<string, string>
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":";

由下图可以看到Table Type 为MANAGED_TABLE

创建外部表

create external table external_table(
id int,
name string,
hobby array<string>,
extra_info map<string, string>
)
row format delimited
fields terminated by ","
collection items terminated by "-"
map keys terminated by ":";

由下图可以看到Table Type为EXTERNAL_TABLE

向表中加载数据

实验数据格式

1,xiaoming,book-TV-code,beijing:chaoyang-shagnhai:pudong
2,lilei,book-code,nanjing:jiangning-taiwan:taibei
3,lihua,music-book,heilongjiang:haerbin

加载数据到表中

# 使用overwrite，如果表中已经有数据，会被先删除，在加载新的数据
load data local inpath '/home/xh/Desktop/test' overwrite into table managed_table;
# 不使用overwrite，新的数据会追加到表中，已经存在的数据还会存在
load data local inpath '/home/xh/Desktop/test' into table external_table;

查询表数据

如下图所示，当再次加载数据，不使用overwrite时，数据会追加到表数据中

hdfs的表目录下，会生成两个文件，分别保存两次加载的数据

可以看到，load…into table及load… overwrite into table和 insert into table… select 及insert overwrite table使用方法及目的是一样的

PS：注意load… overwrite into table必须有into，而insert overwrite table不能使用into。

同时，无论是insert还是load，每使用一次都会在hdfs中重新创建一个新的文件，如果每次新建的文件的数据量都很小，会极度消耗namenode的元数据内存的，所以应该避免多次加载小批量的数据，否则会降低性能。

总结：内部表和外部表主要是用于对原始数据及元数据的管理，根据不同场景来选用不同类型的表，有助于整个集群数据的管理。

分区表

以表pt01为例，如下图可以知道该表为分区表

查看其分区信息

由下图可以看到该表已经有两个分区

再添加一个分区

添加分区

alter table pt01 add partition(dept="MA");

查看分区信息

静态插入数据

insert overwrite table pt01 partition (dept='MA')
select id, name, gender, age from student
where dept="MA";

由上面可以知道，这种静态插入数据，需要表中已经有相应的分区，但是还有一种方法，在一个SQL语句中建立分区后插入数据

下面先删除一个分区

alter table pt01 drop if exists partition (dept='MA');

如下图所示，建立分区和插入数据同时进行

insert into table pt01 partition (dept="MA")
select id, name, gender, age from student
where dept="MA";

动态加载数据

首先将表pt01中的分区全部删除

alter table pt01 drop if exists partition (dept="MA");
alter table pt01 drop if exists partition (dept="IS");
alter table pt01 drop if exists partition (dept="CS");

在没有添加分区的情况下，向表中添加数据

insert into table pt01 partition(dept)
select id, name, gender, age, dept from student;

如下图所示，默认情况下，是不允许动态添加分区的

当按照报错的信息，更改为nonstrict模式后，便成功建立分区和加载了数据

静态同时删除多个分区

静态同时添加多个分区

注意：删除多个分区时，要用“，”隔开各个分区，而删除多个分区时，不需要使用“，”隔开。

如下图建立另一个分区表pt02

向分区表中动态添加数据

按照上面的语句是错误的，插入数据的最后一个字段必须是分区字段，如下图所示：

重新将分区字段放在最后面

insert overwrite table pt02 partition(age)
select id, name, gender, dept, age from student;

此时，并没有将表中第一次错误数据删除，而是还保留在表中。

删除表中所有数据

只用通过先删除表中所有数据了

但是分区信息却没有删除

分区只能使用删除分区的语句删除了，结果发现由于字段的类型已经混乱了，只能删除表重新建表了。

修改分区

修改分区，一般来说，都是指修改分区的数据存储目录

1. 设定分区的存储目录

在添加分区的时候，直接指定当前分区的数据存放的目录，如下图，表pt01现在已经有三个分区了

对表再添加一个分区，并且指定该分区在hdfs的存储位置

通过下面命令

hive (test01)> desc formatted pt01 partition(dept="AA");

获得如下图所示的结果，该分区存储位置在指定的地址

2. 修改分区的储存位置

输入如下命令，修改刚才添加分区的存储位置：

hive (test01)> alter table pt01 partition(dept="AA") set location '/bb';

如下图所示，分区的存储位置变为修改的地址

注意：

1. 由于添加分区时就是在hdfs中建立一个分区目录，所以分区已经存在时，就说明该分区数据所在的hdfs的目录是已经确定好的了；
2. 所以如果想指定分区数据存储位置，那在添加分区的时候就应该指定想要存储的位置。