CODEWITHSUNDEEP
hadoophive
Liu Cheng
Liu Cheng

Reputation: 71

Why hive doesn't allow create external table with CTAS?

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.

Upvotes: 7

Views: 14861

Answers (4)

Ani Menon
Ani Menon

Reputation: 28277

CTAS creates a managed hive table with the new name using the schema and data of the said table.

You can convert it to an external table using:

ALTER TABLE <TABLE_NAME> SET TBLPROPERTIES('EXTERNAL'='TRUE');

Upvotes: 0

Venkat Ankam
Venkat Ankam

Reputation: 926

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.

Upvotes: 2

Kunal
Kunal

Reputation: 126

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.

Upvotes: 0

scalauser
scalauser

Reputation: 1327

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

Upvotes: 2

Related Questions