Hive and Data Warehousing Basics
This domain tests Hive's warehouse model, metastore, table types, partitioning and bucketing, file formats, and basic dimensional modeling. Expect questions on where data physically lives and what a DDL statement actually deletes.
Core rules
- Hive stores metadata in a relational metastore; HQL compiles to MapReduce, Tez, or Spark. It is schema-on-read and designed for large scans, not OLTP.
- Managed (internal) tables: dropping the table removes metadata and warehouse data. External tables: dropping removes metadata only; the data remains.
- Partitions are directories; the partition column value is encoded in the path, not inside the files. Buckets are files produced by a hash of the bucket column.
- ORC and Parquet are columnar and compress well; ACID transactional tables require ORC and specific table properties.
- Warehouse modeling: fact tables hold measures and foreign keys; dimension tables hold descriptive attributes. Star schemas use denormalized dimensions; snowflake schemas normalize them further.
Worked example
An external table stores clicks partitioned by dt and bucketed by user_id into 16 buckets. A query with WHERE dt='2024-05-01' prunes to one directory. A join on user_id with a second table bucketed the same way can use a bucket map join. Dropping the external table leaves the HDFS directories intact, so the data can be re-registered.
Traps
- The partition column is not stored in the data files; loading data does not automatically create partitions. Use ALTER TABLE ADD PARTITION or MSCK REPAIR TABLE.
- Dynamic partitioning needs non-strict mode and reasonable limits, or it can create huge numbers of small files.
- Hive is not built for single-row updates; do not treat it as an OLTP database.