Can We Create An External Table Using CTAS?

Can we create an external table using CTAS?

The answer is NO.  (Read till the end)

As per the Hive’s documentation, CTAS has these restrictions:

• The target table cannot be an external table.
• The target table cannot be a partitioned table (until the version 3.2.0)
• The target table cannot be a list bucketing table.

Let’s see what Hive returns if we try to create it.

CREATE EXTERNAL TABLE empinfo3
LOCATION ‘/user/cloudera/empinfo3’
AS SELECT * FROM empinfo;



It is throwing a semantic exception saying CTAS cannot create external table.

However, there is an alternative to do it.
1) Create the internal table
2) Convert it into external

CREATE TABLE empinfo3
LOCATION ‘/user/cloudera/empinfo3’
AS
SELECT * FROM empinfo;

ALTER TABLE empinfo3 SET TBLPROPERTIES(‘EXTERNAL’=’TRUE’);



Stay in touch for more interesting updates.