Hive外部表和内部表区别以及相互转换_hive内外部表转换

• Hive建表语句

  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  (col_name data_type [COMMENT 'col_comment'], ...)
  [PARTITIONED BY (col_name data_type [COMMENT 'col_comment'], ...)]
  [COMMENT 'table_comment']
  [WITH SERDEPROPERTIES ('key1'='value1', 'key2'='value2', ...)]
  [
   [ROW FORMAT row_format] [STORED AS file_format]
  ]
  [LOCATION 'hdfs_path']
  [TBLPROPERTIES ('key1'='value1', 'key2'='value2', ...)]
  [CACHED IN 'pool_name' [WITH REPLICATION = integer] | UNCACHED]
1
2
3
4
5
6
7
8
9
10
11

其中external关键字用来区分内部表和外部表，location指定也可以不指定默认为hive仓库路径

• 内部表

create table tableA(id int,name string);
1

内部表默认的路径实在hive默认指定的路径，一般是在hdfs/user/hive/warehouse/database.db/tableA/下，如果删除表会连路径和数据一起删掉

• 外部表

create external table tableA(id int,name string) location '/user/appl/tmp/tableA';
或
create external table tableA(id int,name string)；
1
2
3

相比于内部表外部表建表关键字多了一个external，且后面一般会带上路径location，不过如果后面不带location的表的存储路径就是默认在hive仓库路径下即：/user/hive/warehouse/database.db/tableA/

• 外部和内部的区别
  a、最大的区别：外部表删除表仅仅删除hive中元数据不删除数据和指定的路径，内部表如果drop掉表，数据和默认路径都删除了，
  b、一般建表没有特别说一定要建内部还是外部表，看个人喜好或者公司规定，若怕数据误删可以建外部表为主

• 外部表和内部表的转换

  1、外部表和内部表其实是可以通过更改属性来转换的。而且不会因为数据量大就转换性能慢,，这个时候就是路径是在默认hive仓库路径下也不会影响外部表的性质，即删除表不会删除路径和数据，修改表名也不会将文件夹名称修改，转换示例如下

  内部转外部
  alter table tableA set TBLPROPERTIES('EXTERNAL'='true')
  外部转内部
  alter table tableA set TBLPROPERTIES('EXTERNAL'='false')
  1
  2
  3
  4

  2、问题来了：：**如果最开始创建了外部表在默认路径下，这个时候如果对表重名的话，相应的路径名是不会变，这个时候如果在重建一个表名和原表一样名称的表就会造成两个表的数据路径是一样的，显然这是不想要的结果，**如下图示例

  • 首先创建了一个外部表table_1，但是路径不指定即为默认，这时候是否修改table_1为table_111,同时查询表table_111的数据的确是刚刚插入的数据，这个时候因为table_111为外表,那他的数据路径仍然是/user/hive/warehouse/test.db/table_1
  • 这个时候在创建一个表table_1,因为没有指定路径，table_1数据路径也是/user/hive/warehouse/test.db/table_1，而因为先后创建的两个表结构是一样，这个时候查询table_1,其实也能查出1,2这个数据来(第二图所示)，这相当于table_1和table_111共用数据源，如果我建立第二个table_1表结构不一样第一次建的话，那查询table_1就会查询报查询结构的错
    
    
    3、这种情况一般用在：要备份历史表如fact_a为fact_a_bak,然后新建一个一样活着新加了字段的fact_a表，那要试的fact_are 重命名为fact_a_bak路径也能跟着边为fact_a_bak的话就不户影响新结构的fact_a了，这个时候解决方案为:需要在rename fact_a前把fact_a先改为内部表然后在重命名即可，再新建一个fact_a，就不会冲突了,如下图：
    
    4、查看表内外表属性可以通过执行 describe extended tablename;来查看，其中impala和hive中都适用，如下图，分别是impala和hive下查看表的属性；
    
    

说明：考虑到hive和impala操作基本类似，没有专门对impal和hive进行说明，以上示例在impala和hive都可以通用，我在实际工作中也用到了更改属性那个示例来进行表备份；