mysql 外部表_内部表和外部表的区别

Managed and External Tables(内部表和外部表)

Hive上有两种类型的表，一种是Managed Table(默认的)，另一种是External Table(加上EXTERNAL关键字)。它俩的主要区别在于：当我们drop表时，Managed Table会同时删去

data(存储在HDFS上)和meta data(存储在MySQL)，而External Table只会删meta data。

#创建内部表和外部表

hive (d6_hive)> create EXTERNAL table emp_external(

> empno int,

> ename string,

> job string,

> mgr int,

> hiredate string,

> sal double,

> comm double,

> deptno int

> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

> location '/d6_hive/external/emp';

OK

Time taken: 0.04 seconds

#HDFS中查看

hive (d6_hive)> dfs -ls /d6_hive/external/emp;

Found 1 items

-rwxr-xr-x 3 hadoop supergroup 700 2019-03-21 11:08 /d6_hive/external/emp/emp.txt

#MySQL中查看

mysql>select * from ruoze_d6.tbls\G;

*************************** 1. row ***************************

TBL_ID: 45

CREATE_TIME: 1553138028

DB_ID: 11

LAST_ACCESS_TIME: 0

OWNER: hadoop

RETENTION: 0

SD_ID: 45

TBL_NAME: emp_external

TBL_TYPE: EXTERNAL_TABLE

VIEW_EXPANDED_TEXT: NULL

VIEW_ORIGINAL_TEXT: NULL

#删除外部表

hive>drop table emp_external;

OK

Time taken: 1.143 seconds

#再次查看

hive (d6_hive)> dfs -ls /d6_hive/external/emp;

Found 1 items

-rwxr-xr-x 3 hadoop supergroup 700 2019-03-21 11:08 /d6_hive/external/emp/emp.txt

#虽然外部表被删除了，但是HDFS上的数据还是存在的。

mysql>select * from ruoze_d6.tbls\G;

#但是元数据信息就没有了

创建内部表

hive (d6_hive)> create table emp_managed(

> empno int,

> ename string,

> job string,

> mgr int,

> hiredate string,

> sal double,

> comm double,

> deptno int

> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

OK

Time taken: 0.075 seconds

#我们可以发现内部表和外部表的建表区别：内部表没有EXTERNAL，也就是说建表默认就是内部表，再就是，见内部表没有指定目录，使用的是默认目录，默认目录是Hive的${HIVE_HOME}/conf/hive-site.xml配置文件的hive.metastore.warehouse.dir属性指向的就是Hive表数据存放的路径

我的默认路径是这样的

hive (d6_hive)> set hive.metastore.warehouse.dir;

hive.metastore.warehouse.dir=/user/hive/warehouse

如果我们想一下子知道，load入hive内部表的数据的最终目录可以查看一下这个内部表的详细信息。如下所示

根据这个路径我们去HDFS里查看一下。

正如我们所预想的那样，数据就是在那里边。

接着我们在mysql里查看一下元数据信息，数据是存在的。

mysql> select * from ruoze_d6.tbls\G;

*************************** 15. row ***************************

TBL_ID: 46

CREATE_TIME: 1553138641

DB_ID: 11

LAST_ACCESS_TIME: 0

OWNER: hadoop

RETENTION: 0

SD_ID: 46

TBL_NAME: emp_managed

TBL_TYPE: MANAGED_TABLE

VIEW_EXPANDED_TEXT: NULL

VIEW_ORIGINAL_TEXT: NULL

#删除内部表

hive (d6_hive)> drop table emp_managed;

OK

Time taken: 0.082 seconds

hive (d6_hive)>

此时我们查看 /user/hive/warehouse/d6_hive.db/emp_managed路径，发现emp.txt文件没有

也就是说HDFS上的数据被删掉了。

hive (d6_hive)> dfs -ls /user/hive/warehouse/d6_hive.db/emp_managed;

ls: `/user/hive/warehouse/d6_hive.db/emp_managed': No such file or directory

Command failed with exit code = 1

Query returned non-zero code: 1, cause: null

再次去查看MySQL元数据信息，元数据信息也没有了。也就是说，内部表一单被删除，Hdfs上的data和mysql里的metedata都会被删除。

由以上数据可以看出，内部表被drop以后，mysql和HDFS上的数据都会被清空，外部表被drop以后，mysql上的数据没了，但是HDFS上的数据还存在。

如何查看一个表是内部表还是外部表，进入那个表所在的hive库，执行desc formatted tablename(表名)；

hive (d6_hive)> desc formatted emp;

得到到的信息中有一个Table Type，后边会标明他是MANAGED_TABLE 还是External Table

也可以在mysql中查看

mysql> select * from tbls where tbl_name='emp'\G;

*************************** 1. row ***************************

TBL_ID: 36

CREATE_TIME: 1553070314

DB_ID: 11

LAST_ACCESS_TIME: 0

OWNER: hadoop

RETENTION: 0

SD_ID: 36

TBL_NAME: emp

TBL_TYPE: MANAGED_TABLE

VIEW_EXPANDED_TEXT: NULL

VIEW_ORIGINAL_TEXT: NULL

1 row in set (0.00 sec)

使用场景：

1.外部表在删除时不会清理HDFS上的数据，所以相对较安全，同时由于其指定目录的特性，在跨部门合作中经常用到。

所以不小心删除外部表后，可以创建一个新表指定到(location ‘/hive/external_table/’)这个位置，那么数据就会恢复。

2.对于一些原始日志文件，同时被多个部门同时操作的时候就需要使用外部表，如果不小心将meta data删除了，

HDFS上 的data还在可以恢复，增加了数据的安全性。