7

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.

blackSmith
  • 3,054
  • 1
  • 20
  • 37
Liu Cheng
  • 71
  • 1
  • 1
  • 2

4 Answers4

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

Community
  • 1
  • 1
scalauser
  • 1,327
  • 1
  • 12
  • 34
  • plz add more explanation. Don't miss the line `The table created by CTAS is atomic, meaning that the table is not seen by other users until all the query results are populated. So other users will either see the table with the complete results of the query or will not see the table at all` in hive manual. – blackSmith Nov 03 '14 at 10:52
  • I guess the question is creating external hive table by ctas is semantic error. Obviously it will be a semantic error. because Create Table as select (CTAS) is possible in Hive with the target table should not being an external table. – scalauser Nov 03 '14 at 11:38
2

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.

blackSmith
  • 3,054
  • 1
  • 20
  • 37
Venkat Ankam
  • 916
  • 11
  • 17
  • Probably `SELECT col1,col2 .. FROM table` will be more helpful. When `SELECT *` is helpful in creating the table only, with data it creates unformatted files at the external location. Anyway OP should accept it as an answer. – blackSmith Nov 07 '14 at 12:48
  • I tried and failed. Hive doc states `CTAS has these restrictions: The target table cannot be a partitioned table. The target table cannot be an external table. The target table cannot be a list bucketing table.` See https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/TruncateTable under CTAS – Tom Harrison Jun 10 '16 at 20:10
  • 5
    This is creating managed table and not external table! Refer to https://issues.apache.org/jira/browse/HIVE-891 – Kumar Vaibhav May 08 '17 at 18:39
0

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.

Kunal
  • 126
  • 1
  • 8
0

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');
Ani Menon
  • 27,209
  • 16
  • 105
  • 126