Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 36 ยท Tables, Partitions and Buckets

Managed and External Tables

Understand Hive managed and external tables: who owns the data, what DROP TABLE does, LOCATION, converting between types, ACID managed tables, and best practices for raw data.

Every Hive table is one of two kinds, and the difference decides what happens to your files when you drop the table. A managed table owns its data; an external table only describes data that someone else owns. Picking the wrong kind is one of the most expensive beginner mistakes in Hive, because it can delete production data or leave junk behind forever.

Table types are a Hive concept, so the statements are read-only. The runnable editors are SQLite stand-ins that illustrate the two ideas with a view (describes data it does not own) and a table (owns its data).

Managed tables (internal)

SQL
CREATE TABLE sales (
  id BIGINT,
  total DECIMAL(10,2)
)
STORED AS ORC;

With no keyword and no LOCATION, Hive creates a managed table and puts its folder in the warehouse directory (/user/hive/warehouse/<db>.db/sales). Hive is the owner:

  • DROP TABLE sales deletes the metadata and the data files.
  • TRUNCATE TABLE works.
  • LOAD DATA INPATH moves your files in; they now belong to Hive.
  • In Hive 3 and later, managed tables are normally ACID (transactional) tables stored as ORC, with support for UPDATE, DELETE and MERGE, and direct access from other tools is restricted. Non-transactional "legacy" managed tables are still possible through table properties.

External tables

SQL
CREATE EXTERNAL TABLE raw_logs (
  line STRING
)
ROW FORMAT DELIMITED
STORED AS TEXTFILE
LOCATION '/landing/web_logs/';

An external table points to data somewhere else (LOCATION). Hive is a reader, not the owner:

  • DROP TABLE raw_logs removes only the metadata. The files stay.
  • You can have several external tables over the same folder (for example one parsing JSON, one parsing text).
  • Other tools (Spark, Flink, an ingestion job) keep writing to the folder, and Hive sees new files at the next query.
  • TRUNCATE is refused; Hive does not manage the files.
  • With cloud storage (s3a://bucket/path, abfs://...) external tables are the standard.
flowchart LR
    subgraph Managed
        MT["Table: sales"] --- MF[("warehouse/sales/<br/>owned by Hive")]
    end
    subgraph External
        ET["Table: raw_logs"] -.reads.-> EF[("/landing/web_logs/<br/>owned by the pipeline")]
    end
    MT -->|DROP| X1["metadata AND files deleted"]
    ET -->|DROP| X2["metadata deleted, files kept"]

DROP is the moment the difference matters.

A stand-in for the difference

In SQLite a view is like an external table (it describes data owned elsewhere); a table is like a managed one. Dropping the view leaves the underlying data intact:

SQL Stand-in: view versus table when dropped (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

Side-by-side

Managed External
Keyword none EXTERNAL
Data owner Hive Someone else
Default location Warehouse directory Any path you choose (LOCATION)
DROP TABLE Deletes data Keeps data
TRUNCATE Allowed Not allowed
ACID UPDATE/DELETE Yes (Hive 3 default) No
Typical use Curated tables Hive produces Raw landing zones, shared data

Which one to use

Think about who is responsible for the lifetime of the data:

  1. Data arrives from outside (logs, files from partners, an ingest tool) and must survive if you recreate the table: external.
  2. Data is produced and consumed only inside Hive pipelines, and you want it cleaned up with the table: managed.
  3. Many teams or engines read the same files: external, and let one process own writes.
  4. You need row-level UPDATE, DELETE or MERGE: managed ACID.
  5. A good layered design: raw layer as external tables over landing files, curated layer as managed ORC tables built with INSERT OVERWRITE or CTAS.

Converting between types

SQL
ALTER TABLE sales SET TBLPROPERTIES ('EXTERNAL' = 'TRUE');   -- managed -> external
ALTER TABLE sales SET TBLPROPERTIES ('EXTERNAL' = 'FALSE');  -- external -> managed

Setting EXTERNAL to TRUE is a safe way to protect a table you realize is too valuable to drop by accident. The property name is case-sensitive ('EXTERNAL' in capital letters, and the value 'TRUE'). In Hive 3, you can also protect data with 'external.table.purge'='true', which tells an external table to delete its data on DROP (useful for scratch tables in cloud storage).

Inspecting the type and location

SQL
DESCRIBE FORMATTED sales;
-- Table Type:   MANAGED_TABLE   or   EXTERNAL_TABLE
-- Location:     hdfs://.../warehouse/sales

Always check Table Type before running DROP TABLE on something you did not create.

Changing the location

SQL
ALTER TABLE raw_logs SET LOCATION '/landing/web_logs_v2/';

This changes only the metastore pointer; it does not move any files. For partitioned tables you must also update each partition location (ALTER TABLE t PARTITION (...) SET LOCATION ...).

Creating an external table over existing data

A classic workflow: a pipeline writes Parquet files into object storage, and you expose them to SQL without copying anything:

SQL
CREATE EXTERNAL TABLE clicks (
  user_id BIGINT,
  url STRING,
  ts TIMESTAMP
)
STORED AS PARQUET
LOCATION 's3a://company-datalake/clicks/';

The table is instantly queryable. If the folder is partitioned (.../dt=2025-03-01/), add PARTITIONED BY (dt STRING) and run MSCK REPAIR TABLE clicks;, as shown in Partitioned Tables.

Common mistakes

  • Creating a managed table over a location that holds important data, and then dropping it.
  • Expecting DROP TABLE on an external table to free storage space.
  • Assuming TRUNCATE or UPDATE works on an external table.
  • Forgetting LOCATION and wondering why a new external table is empty (it created an empty folder in the warehouse).
  • Mixing writers: an ingestion job and INSERT OVERWRITE both writing the same external folder.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to the files when you DROP an external table?

    An external table does not own its data, so dropping it removes only the definition.
  2. 2. Which table type supports UPDATE and DELETE in Hive 3 (by default)?

    Hive 3 managed tables are transactional ORC tables.
  3. 3. How can you protect a managed table from losing its data on DROP?

    Turning it into an external table means DROP removes only metadata.
  4. 4. What does ALTER TABLE ... SET LOCATION do?

    Hive does not move any data when you change the location.

Summary

  • Managed tables own their data (DROP deletes it); external tables point at data owned elsewhere (DROP keeps it).
  • Use external for raw, shared or cloud data and managed ACID tables for curated data needing updates.
  • Convert with the EXTERNAL table property; verify with DESCRIBE FORMATTED.
  • SET LOCATION changes only metadata.

Next: Partitioned Tables