Hi, I've been wondering if anyone can tell me what the differences and limitations of managed vs. external tables are these days.
I've seen the docs from Hortonworks[1] that list these features as not supported by External tables: * Query cache * Materialized views, except in a limited way * Default statistics gathering * Compute queries using statistics * Automatic runtime filtering * File merging after insert But there are no details. For some of them I'm not even sure what they are supposed to mean because I couldn't find any documentation in our Wiki :( So I'd love to create a Wiki page that describes the differences between managed & external tables and list relevant JIRAs but I need your help for that. I scanned the code for all references to TableType and this is the list I found but I'm almost certainly missing something: * ARCHIVE/UNARCHIVE - DDLTask - Only works for managed tables * TRUNCATE - DDLSemanticAnalyzer - Only works for managed tables * MERGE/CONCATENATE - HiveRelOpMaterializationValidator - Only works for managed tables * Constraints - DDLSemanticAnalyzer - (NOT NULL, DEFAULT, CHECK, only RELY ist allowed) * IMPORT - ImportSemanticAnalyzer - This has some wild restrictions I didn't follow for external tables * Query Results Caching - https://issues.apache.org/jira/browse/HIVE-18513 SemanticAnalyzer - Documentation missing for Results Cache So there's a bunch of those from the Hortonworks list missing. If anyone could point me to documentation or code for those that'd be great. I also didn't find the most obvious/oldest one: Data is not deleted for EXTERNAL tables on DROP, I'm sure I just missed it. And last but not least: I have not looked at the grammar to see what's already forbidden at that level. As I said: Any hints would be greatly appreciated. Thank you! Lars A side note: There are so many fantastic features in Hive but because lots of them are not or under-documented most people I know just stick to the basics. That's a pity :( [1] < https://docs.hortonworks.com/HDPDocuments/HDP3/HDP-3.0.0/using-hiveql/content/hive_hive_3_tables.html >
