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

Question

I have a hive table in XYZ db named ABC.

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

that is

Table Type: MANAGED_TABLE
Table Parameters: EXTERNAL True

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

Answer 1

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