1. 创建数仓数据库
sql
-- 创建项目数据库 CREATE DATABASE IF NOT EXISTS ecommerce_dw; USE ecommerce_dw;2. ODS 层 外部表（用户行为日志）
sql
CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_behavior ( user_id STRING, item_id STRING, category_id STRING, behavior STRING, dt STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE LOCATION '/user/hive/warehouse/ecommerce_dw.db/ods_user_behavior';3. DWD 明细层 清洗表
sql
CREATE TABLE IF NOT EXISTS dwd_user_behavior_detail ( user_id STRING, item_id STRING, category_id STRING, behavior STRING, dt STRING ) PARTITIONED BY (day_str STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; -- 插入清洗后数据 INSERT OVERWRITE TABLE dwd_user_behavior_detail PARTITION(day_str='2025-01-01') SELECT user_id, item_id, category_id, behavior, dt FROM ods_user_behavior WHERE user_id IS NOT NULL AND item_id IS NOT NULL;4. DWS 汇总层 用户日活宽表
sql
CREATE TABLE IF NOT EXISTS dws_user_day_summary ( user_id STRING, login_cnt INT, browse_cnt INT, buy_cnt INT, day_str STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; -- 按用户聚合统计 INSERT OVERWRITE TABLE dws_user_day_summary SELECT user_id, SUM(CASE WHEN behavior='login' THEN 1 ELSE 0 END) login_cnt, SUM(CASE WHEN behavior='browse' THEN 1 ELSE 0 END) browse_cnt, SUM(CASE WHEN behavior='buy' THEN 1 ELSE 0 END) buy_cnt, '2025-01-01' day_str FROM dwd_user_behavior_detail WHERE day_str='2025-01-01' GROUP BY user_id;5. ADS 应用层 业务报表层
sql
CREATE TABLE IF NOT EXISTS ads_day_report ( day_str STRING, active_user_num INT, total_browse INT, total_buy INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'; -- 生成日报指标 INSERT OVERWRITE TABLE ads_day_report SELECT day_str, COUNT(DISTINCT user_id) active_user_num, SUM(browse_cnt) total_browse, SUM(buy_cnt) total_buy FROM dws_user_day_summary GROUP BY day_str;6. 查询最终报表（可直接看结果）
sql
SELECT * FROM ads_day_report;使用说明
- 你直接把上面 SQL 按顺序在 Hive 执行，伪分布式集群完全能跑通；
- 项目架构：ODS→DWD→DWS→ADS 四层数仓分层，标准企业级流程，直接写进简历；
- 每天一边背我给你的面试口述版，一边敲这套 Hive 代码，并行学习完全没问题。
|注释版
sql
-- 1. 创建数据库 -- 意思：建一个名叫“电商数仓”的文件夹，用来放所有表 CREATE DATABASE IF NOT EXISTS ecommerce_dw; -- 意思：接下来我要在“电商数仓”里干活 USE ecommerce_dw;sql
-- 2. ODS 原始层 -- 意思：建一张“用户行为原始表” -- ODS = 最原始的数据，不修改、不清洗，直接从系统搬过来 CREATE EXTERNAL TABLE IF NOT EXISTS ods_user_behavior ( user_id STRING, -- 用户编号（谁） item_id STRING, -- 商品编号（哪个商品） category_id STRING,-- 商品分类编号（哪一类商品） behavior STRING, -- 用户行为（浏览/点击/购买） dt STRING -- 日期（哪一天） ) ROW FORMAT DELIMITED -- 数据按行存放 FIELDS TERMINATED BY '\t' -- 字段之间用 tab 隔开 STORED AS TEXTFILE -- 数据存成文本文件 LOCATION '/user/hive/warehouse/ecommerce_dw.db/ods_user_behavior'; -- 数据在HDFS上的存放位置sql
-- 3. DWD 明细清洗层 -- DWD = 把原始数据洗干净 -- 去掉脏数据、去掉空值、格式统一 CREATE TABLE IF NOT EXISTS dwd_user_behavior_detail ( user_id STRING, -- 用户编号 item_id STRING, -- 商品编号 category_id STRING, -- 商品分类 behavior STRING, -- 行为 dt STRING -- 日期 ) PARTITIONED BY (day_str STRING) -- 按天分区（一天一个文件夹，查得更快） ROW FORMAT DELIMITED -- 按行存储 FIELDS TERMINATED BY '\t'; -- 字段用 tab 分隔sql
-- 插入清洗数据 -- 意思：把 ODS 原始数据 → 搬到 DWD 清洗层 -- 只搬“有效数据”，空用户、空商品都丢掉 INSERT OVERWRITE TABLE dwd_user_behavior_detail PARTITION(day_str='2025-01-01') SELECT user_id, item_id, category_id, behavior, dt FROM ods_user_behavior -- 来自原始表 WHERE user_id IS NOT NULL -- 用户编号不能为空 AND item_id IS NOT NULL; -- 商品编号不能为空sql
-- 4. DWS 汇总层 -- DWS = 把明细数据“算成统计结果” -- 按用户、按天汇总：登录几次、浏览几次、买了几次 CREATE TABLE IF NOT EXISTS dws_user_day_summary ( user_id STRING, -- 用户编号 login_cnt INT, -- 当天登录次数 browse_cnt INT, -- 当天浏览次数 buy_cnt INT, -- 当天购买次数 day_str STRING -- 日期 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';sql
-- 聚合插入 -- 意思：把 DWD 明细 → 算成 DWS 汇总 INSERT OVERWRITE TABLE dws_user_day_summary SELECT user_id, -- 按用户分组 SUM(CASE WHEN behavior='login' THEN 1 ELSE 0 END) login_cnt, -- 统计登录次数 SUM(CASE WHEN behavior='browse' THEN 1 ELSE 0 END) browse_cnt,-- 统计浏览次数 SUM(CASE WHEN behavior='buy' THEN 1 ELSE 0 END) buy_cnt, -- 统计购买次数 '2025-01-01' day_str -- 日期 FROM dwd_user_behavior_detail -- 从清洗后的明细取数 WHERE day_str='2025-01-01' -- 只算这一天 GROUP BY user_id; -- 按每个用户分组统计sql
-- 5. ADS 应用层（报表） -- ADS = 给老板、运营看的最终报表 CREATE TABLE IF NOT EXISTS ads_day_report ( day_str STRING, -- 日期 active_user_num INT, -- 日活用户数（当天来了多少人） total_browse INT, -- 全站总浏览次数 total_buy INT -- 全站总购买次数 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';sql
-- 生成日报 -- 意思：把 DWS 汇总 → 算出最终报表 INSERT OVERWRITE TABLE ads_day_report SELECT day_str, -- 日期 COUNT(DISTINCT user_id) active_user_num, -- 当天来了多少独立用户 SUM(browse_cnt) total_browse, -- 全站总浏览量 SUM(buy_cnt) total_buy -- 全站总购买量 FROM dws_user_day_summary -- 从汇总层取数 GROUP BY day_str; -- 按天分组sql
-- 查看结果 -- 意思：展示最终日报 SELECT * FROM ads_day_report;
