Hive 3.1 cannot create external table that copy schema from internal/managed table

Question

we try to make an external hive table which its schema is similar from existing internal/managed table and the data for ecternal will be inserted next step.

Below the code to create external table with copy schema of managed table

CREATE EXTERNAL TABLE IF NOT EXISTS sls_test_ext like sls_test
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
LOCATION "/bla/bla/";

But when its executed, it returns as below

ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:sp2020.sls_test_ext cannot be declared transactional because it's an external table)
INFO  : Completed executing command(queryId=hive_20201111233927_593d38ff-39ed-412e-b128-41d949a3a476); Time taken: 0.017 seconds
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:sp2020.sls_test_ext cannot be declared transactional because it's an external table) (state=08S01,code=1)

Then we try to modif the code above as below

CREATE EXTERNAL TABLE IF NOT EXISTS sls_test_ext like sls_test
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
LOCATION "/bla/bla/"
TBLPROPERTIES ("transactional"="false");

It will returns same error. Is there any missing code or there is some hive configuration to be changed

NB: the managed table is created automatically using sqoop import hive-table ?

Soumendra Mishra Soumendra Mishra · Accepted Answer · 2020-11-11T18:16:07

You can try the below solution:

Managed Table:

CREATE TABLE sls_test(<columns>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

External Table:

CREATE EXTERNAL TABLE IF NOT EXISTS sls_test_ext
LIKE sls_test
LOCATION '/bla/bla/';

Hive 3.1 cannot create external table that copy schema from internal/managed table

1 Answers