Hive 外部表关联分区数据

0. 说明

已经安装好Hadoop和hive环境，hive把元数据存储在mysql数据库。这里仅讨论外部表和HDFS的关联，并且删掉外部表之后，对HDFS上的文件没有影响

1. 在HDFS创建分区，并存有文件

手工创建或者由程序在HDFS上生成了分区目录，每个分区目录下有相应的文件。本例中根据day分了两个分区，如下所示：

/test/in/day=20/20.txt
/test/in/day=21/21.txt

文件里面的内容格式如下：

2;Lily;1991;Shanghai
3;Jack;1992;Guangxi
4;Jenny;1999;Xinjiang
5;Jay;1995;Xizang
6;Tom;1990;Beijing
7;Lily;1991;Shanghai
8;Jack;1992;Guangxi
9;Jenny;1999;Xinjiang
10;Jay;1995;Xizang

2. 创建hive外部表（建表语句没有用location直接关联HDFS文件）

根据HDFS上的源文件里面的数据格式，创建hive外部表。

hive> create external table t3 (seq int,name string,year int, city string)
    > partitioned by (day int)
    > row format delimited
    > fields terminated by '\073'
    > stored as textfile;
OK
Time taken: 0.207 seconds

源文件里面列之间的分隔符是分号，hive里面分号是特殊符号（命令执行结束符），如果建表语句里面用分号则会出错，使用分号的ascii码’\073’（这里用了八进制码）则正常。

查看t3的表结构

hive> desc t3;
OK
seq                     int
name                    string
year                    int
city                    string
day                     int
 
# Partition Information
# col_name              data_type               comment
day                     int
Time taken: 0.079 seconds, Fetched: 10 row(s)

表格已经成功创建。

3.关联HDFS文件

hive并不会自动关联hdfs中指定目录的partitions目录，需要手工操作。
通过给已经创建的表增加分区数据的方式，与HDFS上的文件相互关联起来。
语法格式

alter table 表名称 add partition (分区名1=分区值1，...) location 'HDFS上的文件的路径';

关联day=20的分区

hive> alter table t3 add partition (day=20) location '/test/in/day=20/20.txt';
OK
或者
hive> alter table t3 add partition (day=20) location '/test/in/day=20';
OK

关联day=21的分区：

hive> alter table t3 add partition (day=21) location '/test/in/day=21/21.txt';
OK
或者
hive> alter table t3 add partition (day=21) location '/test/in/day=21';
OK

执行下面命令，查看表格可以发现数据已经导入。

hive> select * from t3;

4.之后HDFS分区添加新文件，hive会自动关联

如果这个时候往HDFS上面的分区增加一个文件（符合t3表格式），hive表也可以自动读取到并添加数据。

如新建一个文件b.txt，内容是：1101;JayFEF;2095;BBBB

上传到HDFS的day=21分区：

hadoop fs -put /jaydisk/b.txt /test/in/day=21

直接查看表格，可以看到新的数据也已经添加进去。

hive> select * from t3;
OK
2   Lily    1991    Shanghai    20
3   Jack    1992    Guangxi 20
4   Jenny   1999    Xinjiang    20
5   Jay 1995    Xizang  20
6   Tom 1990    Beijing 20
7   Lily    1991    Shanghai    20
8   Jack    1992    Guangxi 20
9   Jenny   1999    Xinjiang    20
10  Jay 1995    Xizang  20
1101    JayFEF  2095    BBBB    21
Time taken: 1.549 seconds, Fetched: 19 row(s)

5. 内部表和外部表相互转化

可以通过如下语句转换外部表和内部表

alter table tablePartition set TBLPROPERTIES ('EXTERNAL'='TRUE');  //内部表转外部表
 
alter table tablePartition set TBLPROPERTIES ('EXTERNAL'='FALSE');  //外部表转内部表

6. 建外部表时指定HDFS路径（建表语句没有用location直接关联HDFS文件）

6.1 建表

create external table t1 (seq int,name string,year int, city string)
partitioned by (day int)
row format delimited
fields terminated by '\073'
stored as textfile
location '/test/in';

6.2 添加分区day=20

alter table t1 add partition (day=20);

6.3 查看成功

hive> select * from t1;
OK
2   Lily    1991    Shanghai    20
3   Jack    1992    Guangxi 20
4   Jenny   1999    Xinjiang    20
5   Jay 1995    Xizang  20
6   Tom 1990    Beijing 20
7   Lily    1991    Shanghai    20
8   Jack    1992    Guangxi 20
9   Jenny   1999    Xinjiang    20
10  Jay 1995    Xizang  20
Time taken: 0.108 seconds, Fetched: 9 row(s)