Why hive doesn't allow create external table w

问题:

In hive, create external table by CTAS is a semantic error, why? The table created by CTAS is atomic, while external table means data will not be deleted when dropping table, they do not seem to conflict.

回答1:

Am I missing something here?

Try this...You should be able to create an external table with CTAS.

CREATE TABLE ext_table LOCATION '/user/XXXXX/XXXXXX' 
AS SELECT * from managed_table;

I was able to create one. I am using 0.12.

回答2:

In Hive when we create a table(NOT external) the data will be stored in /user/hive/warehouse. But during External hive table creation the file will be anywhere else, we are just pointing to that hdfs directory and exposing the data as hive table to run hive queries etc. This SO answer more precisely Create hive table using "as select" or "like" and also specify delimiter

回答3:

i think its a semantic error because it misses the most imp parameter of external table definition viz. the External Location of the data file! by definition, 1. External means the data is outside hive control residing outside the hive data warehouse dir. 2. if table is dropped data remains intact only table definition is removed from hive metastore. so, i. if CTAS is with managed table, the new ext table will have file in warehouse which will be removed with drop table making #2 wrong ii. if CTAS is with other external table, the 2 tables will point to same file location.

回答4:

Sometimes it happens to me, its because i am not logged in as a proper user with proper rights.