创建hive 内部表(managed table)
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';
load data local inpath '/home/hadoop/data/emp.txt' overwrite into table emp_managed;
hive (ruozeg6)> desc formatted emp;
OK
# Detailed Table Information
Database: default
Owner: hadoop
CreateTime: Tue Oct 29 16:47:05 CST 2019
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
# 存储位置
Location: hdfs://hadoop001:9000/hive/warehouse/emp_managed
Table Type: MANAGED_TABLE
创建外部表(emp_external)
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 '/hive/external' //指定hdfs目录
# 上传数据到该目录
hadoop fs -put /home/hadoop/data/emp.txt /hive/external
hive> desc formatted emp_external;
OK
# Detailed Table Information
Database: default
Owner: hadoop
CreateTime: Tue Oct 29 16:37:28 CST 2019
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
# 存储位置
Location: hdfs://hadoop001:9000/hive/external
Table Type: EXTERNAL_TABLE
mysql中查看 hive表 hivedb 创建的内部表 外部表
mysql> mysql> select * from TBLS \G;
*************************** 7. row ***************************
TBL_ID: 46
CREATE_TIME: 1572338248
DB_ID: 1
LAST_ACCESS_TIME: 0
OWNER: hadoop
RETENTION: 0
SD_ID: 46
TBL_NAME: emp_external
TBL_TYPE: EXTERNAL_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL
*************************** 8. row ***************************
TBL_ID: 47
CREATE_TIME: 1572338825
DB_ID: 1
LAST_ACCESS_TIME: 0
OWNER: hadoop
RETENTION: 0
SD_ID: 47
TBL_NAME: emp_managed
TBL_TYPE: MANAGED_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL
8 rows in set (0.00 sec)
删除 外部表 内部表
hive> drop table emp_external;
OK
Time taken: 0.241 seconds
hive> drop table emp_managed;
OK
Time taken: 0.077 seconds
hive> show tables;
OK
access_log
access_log2
emp
employee
Time taken: 0.009 seconds, Fetched: 4 row(s)
hive>
查看hdfs文件数据
# 外部表存储位置 依旧存在数据
[hadoop@hadoop001 data]$ hadoop fs -ls hdfs://hadoop001:9000/hive/external/
Found 1 items
-rw-r--r-- 1 hadoop supergroup 700 2019-10-29 16:45 hdfs://hadoop001:9000/hive/external/emp.txt
# 内部表存储位置 数据被删除
[hadoop@hadoop001 data]$ hadoop fs -ls hdfs://hadoop001:9000/hive/warehouse/emp_managed/
ls: `hdfs://hadoop001:9000/hive/warehouse/emp_managed/': No such file or directory
总结
未被external修饰的是内部表(managed table);
被external修饰的为外部表(external table);
# 区别:
1.内部表数据由Hive自身管理,外部表数据由HDFS管理;
2.内部表数据存储的位置是hive.metastore.warehouse.dir(默认:/user/hive/warehouse),外部表数据的存储位置由自己制定(如不指定 则与内部表默认位置一致);
3.删除内部表会直接删除元数据(metadata)及存储数据;删除外部表仅仅会删除元数据,HDFS上的文件并不会被删除;
4.对内部表的修改会将修改直接同步给元数据,而对外部表的表结构和分区进行修改,则需要修复(MSCK REPAIR TABLE table_name;)
# 使用场景:
需要共享的数据 使用外部表;