Hive Data Warehouse and SQL-on-Big-Data Development
Hive architecture
HiveServer2 accepts SQL; the metastore stores table, partition, and column metadata in a relational database; execution runs on Tez, MapReduce, or Spark. Internal tables drop data with metadata; external tables drop metadata only. Partitions map to directories and enable pruning; buckets hash rows into files for sampling and joins.
Rules to remember
- Filter on partition columns directly; wrapping them in functions disables pruning.
- ORC or Parquet with predicate pushdown and vectorization cuts I/O.
- Dynamic partitioning needs nonstrict mode and can create many small files; set limits.
- Bucket map joins require both tables bucketed on the join key with compatible bucket counts.
- MSCK REPAIR TABLE adds partitions discovered in HDFS.
Worked example
A table is partitioned by dt. Query A uses WHERE dt='2024-05-01'; Query B uses WHERE substr(dt,1,7)='2024-05'. Query A prunes to one partition. Query B scans all partitions because the partition column is inside a function. Rewrite B as a range: dt BETWEEN '2024-05-01' AND '2024-05-31'.
Traps that cost marks
Dropping an external table does not delete HDFS data. COUNT(DISTINCT col) on a high-cardinality column can create one reducer bottleneck. Adding partitions manually without repair leaves the metastore stale. Small files hurt Hive more than large ORC files.