35

I created a table in hive as a managed table, but it was supposed to be external, is it possible to change the table type of the table without losing the data?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
George TeVelde
  • 1,561
  • 2
  • 12
  • 13

3 Answers3

82
ALTER TABLE <table> SET TBLPROPERTIES('EXTERNAL'='TRUE')

Note: EXTERNAL and TRUE need to caps or it will not work

proutray
  • 1,943
  • 3
  • 30
  • 48
George TeVelde
  • 1,561
  • 2
  • 12
  • 13
  • 1
    This doesn't move you data to external locations actually, your data are still on HDFS. @Olaf 's solution is right – soulmachine Mar 18 '15 at 21:46
  • As [@Shan Hadoop Learner](https://stackoverflow.com/users/4304732/shan-hadoop-learner) mentions, this only works if the table is non-transactional, which is NOT the default behavior of managed tables. In all likelihood, one will need to recreate the table schema as an EXTERNAL table, specify the location of the data, and then INSERT OVERWRITE with the data. – DataSci-IOPsy Oct 21 '22 at 15:36
3
alter table <table_name> SET TBLPROPERTIES('EXTERNAL'='TRUE')

Note: It work only if it is non transactional internal/managed table.

Shan Hadoop Learner
  • 97
  • 5
1

You can copy your data files from the Hive data location to the location you planned to store your external table, drop the table and re-create it as external.

Olaf
  • 6,249
  • 1
  • 19
  • 37