Use case: There is lot of data in the locally managed table and we want to convert those table into external table because we are working on a use case where our spark and home grown application has trouble reading locally managed tables.
Solution: alter table table_name SET TBLPROPERTIES('EXTERNAL'='TRUE');
Test:
Locally managed table dropped
hive> drop table sample_07;
OK
Time taken: 0.602 seconds
Data deleted as it's locally managed table.
hive> dfs -ls /apps/hive/warehouse/sample_07;
ls: `/apps/hive/warehouse/sample_07': No such file or directory
Command failed with exit code = 1
Query returned non-zero code: 1, cause: null
Converting sample_07_test from locally managed to external to keep the data there after drop.
hive> alter table sample_07_test SET TBLPROPERTIES('EXTERNAL'='TRUE');
OK
Time taken: 0.463 seconds
Table dropped after converting into external table.
hive> drop table sample_07_test;
OK
Time taken: 0.552 seconds
Data is still on HDFS
hive> dfs -ls /apps/hive/warehouse/sample_07_test;
Found 1 items
-rwxrwxrwx 3 hive hdfs 46059 2016-02-10 03:24 /apps/hive/warehouse/sample_07_test/000000_0
hive>
Table removed from HCatalog
hive> select * from sample_07_test;
FAILED: SemanticException [Error 10001]: Line 1:14 Table not found 'sample_07_test'
hive>
