Hive Tables, in depth: managed vs external, transactional tables, CREATE TABLE clauses, partitions, schema evolution and DROP semantics

Anatomy: metadata plus files

When you run a query, HiveServer2 asks the Metastore for the table definition: column names and types, the storage descriptor (location, input and output format, SerDe), the partition columns and the table parameters. The planner uses that to decide which directories to read; the execution engine then reads files directly from storage. The Metastore never sees the data itself.

Two consequences follow. First, the metadata and the files can disagree: a job can write files into a partition directory that the Metastore has never heard of, and queries will not see them until the partition is registered. Second, anything that changes files behind Hive's back, such as an hdfs dfs -rm or an S3 lifecycle rule, changes the table without Hive knowing. The internals of the Metastore service and its database are covered in Hive Metastore architecture.

The table types

Views and materialized views are also listed as tables in the Metastore, but they have no files of their own (or, for materialized views, files Hive manages for you). DESCRIBE FORMATTED shows which one you have in its Table Type: line, for example MANAGED_TABLE or EXTERNAL_TABLE, and its table parameters show transactional=true and transactional_properties for ACID tables. Check this line before any destructive operation; it is the single most useful habit for working with Hive.

Transactional tables write new data as delta directories and read through a transaction snapshot, which is why other engines need ACID support to read them correctly. How deltas, write ids and snapshot reads work is the subject of Hive ACID architecture.

Defaults changed in Hive 3

In Hive 1 and 2, a plain CREATE TABLE produced a non-transactional managed table. Hive 3 introduced strict managed tables (the hive.strict.managed.tables setting), under which managed tables are expected to be transactional and non-transactional data is expected to be external. Distributions built on Hive 3 and later, such as Cloudera's CDP, enable this and make a plain CREATE TABLE produce a transactional table; depending on the version and configuration, a table that cannot be full ACID may instead be created as insert-only or translated by the Metastore into an external table with external.table.purge=true.

The exact behaviour depends on your version, your distribution and several settings, so do not rely on documentation for another cluster. Create a test table, run DESCRIBE FORMATTED and read what you actually got. Then write the table type explicitly in every DDL statement: CREATE EXTERNAL TABLE for data you own outside Hive, and TBLPROPERTIES ('transactional'='true') where you need ACID. Explicit DDL behaves the same after an upgrade.

CREATE TABLE clause by clause

CREATE EXTERNAL TABLE raw.orders_csv (
  order_id     BIGINT,
  customer_id  BIGINT,
  amount       DECIMAL(12,2),
  status       STRING,
  created_at   STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3a://lake-raw/orders/'
TBLPROPERTIES ('skip.header.line.count'='1');

CREATE TABLE curated.orders (
  order_id     BIGINT,
  customer_id  BIGINT,
  amount       DECIMAL(12,2),
  status       STRING,
  created_at   TIMESTAMP
)
PARTITIONED BY (dt DATE)
STORED AS ORC
TBLPROPERTIES ('transactional'='true', 'orc.compress'='ZLIB');

• Columns. Choose precise types. DECIMAL for money, TIMESTAMP or DATE for time. Raw tables often keep strings so a bad row does not break the whole read; curated tables should be strict.
• PARTITIONED BY. Partition columns are not stored in the files; they are encoded in directory names such as dt=2026-10-01. Partition by a column used in almost every filter and with modest cardinality, typically a day.
• CLUSTERED BY ... INTO n BUCKETS. Hashes rows into a fixed number of files per partition. Useful for joins and sampling, covered in Hive bucketing.
• ROW FORMAT and STORED AS. The SerDe and file format. Text needs a delimiter; ORC and Parquet carry their own schema and statistics.
• LOCATION. Where the files live. Required in practice for external tables; for managed tables Hive picks a directory under the warehouse root.
• TBLPROPERTIES. Free-form key-value settings: transactional flags, compression, header skipping, purge behaviour.

What DROP, TRUNCATE, LOAD and INSERT do to files

These are the operations where the table type decides whether data survives.

• DROP TABLE on a managed table deletes the directory, possibly into the HDFS trash if trash is enabled; on object stores there is often no trash at all. On an external table it removes only the metadata, unless external.table.purge is true. Re-running the CREATE EXTERNAL TABLE brings the table back over the same files.
• TRUNCATE TABLE deletes data files of a managed table. Hive has traditionally rejected it on external tables; to empty an external table, delete the files with storage tools or drop and recreate partitions.
• LOAD DATA INPATH moves the source files into the table directory; it does not copy them and does not convert their format. After a load the source path is empty. LOAD DATA LOCAL INPATH copies from the client machine instead.
• INSERT OVERWRITE replaces the files of the target table or of the partitions it writes. With dynamic partitioning, only the partitions present in the query output are replaced; others are left alone.
• ALTER TABLE ... DROP PARTITION follows the same ownership rule as DROP TABLE: files go away for managed tables and stay for external ones.

Converting between types is possible in limited cases. On non-transactional tables, ALTER TABLE t SET TBLPROPERTIES ('EXTERNAL'='TRUE') turns a managed table into an external one, which is a common safety step before dropping a table whose files you want to keep. Converting a transactional table back to non-transactional is not supported in place; copy the data out instead.

Partitions: registering and repairing

Because partitions are both directories and Metastore records, they can get out of step. If an upstream job writes dt=2026-10-01/ directly into storage, the Metastore does not know about it and queries return nothing for that day.

-- Register one known partition explicitly (cheap, precise)
ALTER TABLE raw.orders_csv ADD IF NOT EXISTS PARTITION (dt='2026-10-01');

-- Scan the table location and add any partition directories found
MSCK REPAIR TABLE raw.orders_csv;

-- Check what the Metastore thinks exists
SHOW PARTITIONS raw.orders_csv;

MSCK REPAIR TABLE lists the whole table location, which is slow on object stores with thousands of partitions. Prefer having the writer register the partition it just wrote with ADD PARTITION as its last step. Hive 3 and later also accept ADD, DROP and SYNC PARTITIONS options on MSCK so it can remove records for deleted directories; confirm support on your version. Too many small partitions, each holding a few small files, is the classic Hive performance problem described in Hive small files.

Schema evolution without breaking readers

Adding a column is the safe change: ALTER TABLE curated.orders ADD COLUMNS (channel STRING) appends a column at the end, and old files simply return null for it. On partitioned tables add CASCADE so existing partitions' metadata picks up the column too; without it, older partitions keep their old column list.

Everything else needs care. Whether old files are matched to columns by name or by position depends on the format and settings: text is positional, Parquet matches by name by default, and ORC behaviour has varied between Hive versions. Renaming or reordering columns can therefore silently shift data into the wrong column. REPLACE COLUMNS rewrites only metadata, so it can make every existing file unreadable or misread. Widening a type, such as INT to BIGINT, is usually allowed; narrowing is not. For anything beyond adding columns, write a new table, copy the data, and swap readers over, or use a table format with real schema evolution such as Iceberg.

Worked example: raw CSV to curated ORC

An upstream system drops one CSV file per day, about 2 GB and 20 million rows, into s3a://lake-raw/orders/dt=YYYY-MM-DD/. Analysts need a fast, typed table. The design uses both table types for what each is good at.

1. Raw layer, external text table. The files belong to the upstream system and must survive any mistake in Hive, so raw.orders_csv is external. The ingest job registers each day with ADD PARTITION as soon as the file lands.
2. Curated layer, transactional ORC table. curated.orders is owned by Hive. ORC with compression typically shrinks text several times and lets readers skip stripes using min/max statistics.
3. Daily transform. Cast and clean while copying one partition:

INSERT OVERWRITE TABLE curated.orders PARTITION (dt = DATE '2026-10-01')
SELECT order_id, customer_id, CAST(amount AS DECIMAL(12,2)), status,
       CAST(created_at AS TIMESTAMP)
FROM raw.orders_csv
WHERE dt = '2026-10-01' AND order_id IS NOT NULL;

ANALYZE TABLE curated.orders PARTITION (dt = DATE '2026-10-01') COMPUTE STATISTICS;

Because the overwrite targets one partition, re-running the job for a day is idempotent: it replaces that day only. Statistics let the cost-based optimiser plan joins well. Corrections arrive as MERGE statements on the transactional table, and the deltas they produce are folded back into base files by compaction, which you should monitor rather than assume.

Failure modes

• Dropping a managed table you thought was external. The files are gone. Always check Table Type: first and keep raw data in external tables.
• Invisible data. Files written without registering the partition. Register partitions as part of the write.
• Engines that cannot read ACID. A Spark or Impala version without Hive ACID support reads nothing, or reads wrong data, from a full ACID table. Check compatibility before choosing transactional tables for shared data.
• Two tables, one location. Two tables pointing at the same directory, one managed, means dropping one deletes the other's data.
• Metadata-only schema changes. REPLACE COLUMNS or a rename that leaves old files mapped to the wrong columns.
• Object-store surprises. No trash on S3 and storage-side lifecycle rules that delete files Hive still lists.

What to do next

1. Run DESCRIBE FORMATTED on your ten most important tables and record their table type, location and transactional properties.
2. Create a test table with plain CREATE TABLE on your cluster and confirm what type your version actually produces.
3. Make every DDL explicit: EXTERNAL for data owned outside Hive, transactional properties where you need ACID.
4. Move raw landing data to external tables and change ingest jobs to register partitions with ADD PARTITION.
5. Restrict schema changes to adding columns with CASCADE; plan anything else as a copy to a new table.
6. Check that every engine that reads your transactional tables supports Hive ACID at its current version.