Hive Table is MANAGED or EXTERNAL - issue post table type conversion

0
votes

I have a hive table in XYZ db named ABC.

When I run describe formatted XYZ.ABC; from hue, I get the following..

MANAGED TABLE SHOWN AS EXTERNAL

that is 

Table Type: MANAGED_TABLE
Table Parameters: EXTERNAL True

So is this actually an external or a managed/internal hive table?

2
hiveexternal-tableshive-table
It looks like some bug in displaying table information. Only drop table behavior will show. external=true says it is external. But table type says it is managed. There is inconsistency. Drop table should leave location with data as is if it is EXTERNAL. – leftjoin

2 Answers

0
votes

This is treated as an EXTERNAL table. Dropping table will keep the underlying HDFS data. The table type is being shown as MANAGED_TABLE since the parameter EXTERNAL is set to True, instead of TRUE.

To fix this metadata, you can run this query:

hive> ALTER TABLE XYZ.ABC SET TBLPROPERTIES('EXTERNAL'='TRUE');

Some details:

The table XYZ.ABC must have been created via this kind of query:

hive> CREATE TABLE XYZ.ABC
<additional table definition details>
TBLPROPERTIES (
  'EXTERNAL'='True');

Describing this table will give:

hive> desc formatted XYZ.ABC;
:
Location:               hdfs://<location_of_data>
Table Type:             MANAGED_TABLE
:
Table Parameters:
  EXTERNAL              True

Dropping this table will keep the data referenced in Location in describe output.

 hive> drop table XYZ.ABC;
 # does not drop table data in HDFS

The Table Type still shows as MANAGED_TABLE which is confusing.

Making the value for EXTERNAL as TRUE will fix this.

hive> ALTER TABLE XYZ.ABC SET TBLPROPERTIES('EXTERNAL'='TRUE');

Now, doing a describe will show it as expected:

hive> desc formatted XYZ.ABC;
:
Location:               hdfs://<location_of_data>
Table Type:             EXTERNAL_TABLE
:
Table Parameters:
    EXTERNAL                TRUE
0
votes

Example -

Lets create a sample MANAGED table,

CREATE TABLE TEST_TBL(abc int, xyz string);
INSERT INTO TABLE test_tbl values(1, 'abc'),(2, 'xyz');
DESCRIBE FORMATTED test_tbl;

MANAGED_TABLE describe table image

Changing type to EXTERNAL (in the wrong way using True, instead of TRUE):

ALTER TABLE test_tbl SET TBLPROPERTIES('EXTERNAL'='True');

This gives, External Table Wrongly shown image

Now lets DROP the table, DROP TABLE test_tbl;

The result:

Table is dropped but data on HDFS isn't. Showing correct external table behavior!

If we re-create the table we can see data exists:

CREATE TABLE test_tbl(abc int, xyz string);
SELECT * FROM test_tbl;

Result: Output of select *

The describe shows it wrongly as MANAGED TABLE along with EXTERNAL True because of:

.equals check in the meta

Hive Issue JIRA: HIVE-20057

Proposed fix: Use case insensitive equals