Hive Data Warehouse: Modeling, Partitioning, and ID‑Mapping for User Profiles

Building a user‑profile system starts with a data warehouse; Hive, built on Hadoop and HDFS, provides a SQL‑like interface for storing and querying tag data. The classic definition of a data warehouse—subject‑oriented, integrated, non‑volatile, time‑variant, and decision‑supportive—is introduced.

The article details the core modeling concepts: fact tables (transaction, periodic snapshot, cumulative snapshot) and dimension tables, emphasizing how slow‑changing dimensions are handled via techniques such as overwriting, multi‑record retention, date‑partitioned tables, and link (slowly changing) tables.

To improve ETL performance, a partitioned storage strategy is proposed, splitting user‑tag tables by tag categories (population, behavior, consumption, risk, social) and storing each as a separate Hive partitioned table, illustrated with example CREATE TABLE statements.

Label aggregation is achieved by creating a map‑type column ( userlabels ) that stores all tags for a user as a JSON string, using a custom UDF cast_to_json and an INSERT‑OVERWRITE statement to populate the aggregated table.

The concept of ID‑Mapping (linking multiple identifiers such as userid, cookieid, deviceid) is explained, with a focus on handling many‑to‑many relationships via a slowly changing dimension (link) table (zipper table). Hive DDL for the link table and ETL scripts that ingest data from event and page‑view logs into ods.cookie_user_signin , then merge into the zipper table, are provided.

Sample queries demonstrate how to retrieve a snapshot of user‑device mappings for a specific date and how to avoid data explosion by carefully limiting joins on the many‑to‑many relationship.

The article concludes with a brief recap of the ID‑Mapping solution and notes that similar techniques can be applied to bridge user behavior across web and app platforms.