2

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 ?

m hanif f
  • 378
  • 1
  • 4
  • 17

1 Answers1

0

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/';
Soumendra Mishra
  • 2,754
  • 1
  • 3
  • 29
  • what if the managed table is created by sqoop import hive-table? I am sorry to mention it before and I've update it in question – m hanif f Nov 12 '20 at 01:30
  • That is not an issue. Only thing you need to looked into is - the delimiter used for the managed table must match with delimiter used in file stored in HDFS location. – Soumendra Mishra Nov 12 '20 at 05:02