网站日志分析离线管道：从56GB日志到Hive分区与KPI统计

简介：这份文档面向大数据初学者与离线分析实践者，以某技术学习论坛的 Apache Common 日志为样本，完整演示从数据接入到指标落地的分析链路，适合作为课程设计或面试项目复盘参考。压缩包内为 1 个 docx 文件，约 1013KB，按项目介绍、数据清洗、统计分析等章节组织，便于顺序阅读与对照实践。内容围绕 PV、注册用户数、独立 IP 数、跳出率与板块热度排行等 KPI 展开，给出计算公式与业务含义，并梳理 Shell 上传、MapReduce 清洗、Hive 统计、Sqoop 导出 MySQL、HBase 存明细的开发步骤，附 MySQL 与 HBase 表结构设计思路。已有 1405 人学习，可帮助读者理解离线日志分析的整体流程与关键环节，积累可迁移的指标体系与架构设计经验。

1. 网站日志分析：为什么 56GB 日志不能直接丢给 Hive 查

很多人第一次做网站日志分析，直觉是把原始 access log 直接灌进 Hive 建张表，然后写 SQL 查 PV、UV。真跑起来就翻车：56GB 历史日志追加写在一个文件里，Hive 按行切分时单个 mapper 要吞下整块数据，倾斜到一台机器上跑几个小时；更麻烦的是日志里混着静态资源请求、带引号的 URL、英文月份时间戳，直接 COUNT(1) 出来的 PV 里有一半是图片和 CSS。网站日志分析这件事，表面是写几条 HQL，底层其实是一套「采集 → 清洗 → 分区 → 聚合 → 导出」的离线管道设计，每一环都在解决上一环留下的数据形态问题。搞懂这条管道为什么这么切分、每个阶段的数据结构长什么样，比背几条 SQL 有用得多——因为换一个日志源、换一个 KPI，你照样能自己搭出来。

2. 日志清洗：为什么原始行不能直接进 Hive

2.1 一行日志的五个字段与解析的脆弱性

Apache Common 日志的一行大概长这样：访问者 IP、方括号包住的访问时间、双引号包住的请求行、HTTP 状态码、本次访问流量。看起来规整，但解析时全是坑。时间字段是 d/MMM/yyyy:HH:mm:ss 这种英文月份格式， SimpleDateFormat 必须显式指定 Locale.ENGLISH ，否则在中文 locale 的 JVM 上解析 May 会直接抛异常——这是血泪经验，本地跑通、集群上挂掉，八成是 locale 不一致。

项目里的 LogParser 用字符串下标定位来切字段，而不是正则。原因很实际：正则在大数据量下慢，而下标定位对固定格式的日志更快。但代价是脆弱——一旦日志格式微调， indexOf("[") 和 indexOf("+0800]") 之间取不到东西就崩。常见的实现思路是解析失败时返回 null 并在 Mapper 里计数跳过，而不是让整个 job 挂掉。

static class LogParser {
    // 英文月份必须指定 Locale，否则集群 locale 不同会解析失败
    public static final SimpleDateFormat FORMAT =
        new SimpleDateFormat("d/MMM/yyyy:HH:mm:ss", Locale.ENGLISH);
    public static final SimpleDateFormat dateformat1 =
        new SimpleDateFormat("yyyyMMddHHmmss");

    public String[] parse(String line) {
        String ip = parseIP(line);
        String time = parseTime(line);   // 转成 yyyyMMddHHmmss 便于后续按日期分区
        String url = parseURL(line);
        String status = parseStatus(line);
        String traffic = parseTraffic(line);
        return new String[] { ip, time, url, status, traffic };
    }

    private String parseTime(String line) {
        final int first = line.indexOf("[");
        final int last = line.indexOf("+0800]");
        String time = line.substring(first + 1, last).trim();
        Date date = parseDateFormat(time);
        return dateformat1.format(date);  // 统一成无分隔符格式，避免 Hive 再解析
    }
}

这里有个设计取舍值得说：为什么把时间转成 yyyyMMddHHmmss 而不是保留原格式？因为下游 Hive 分区、按天聚合都依赖一个稳定、可比较、无空格的字符串。原始格式带空格和英文月份，既不能直接做分区键，排序也不对。清洗阶段顺手把「展示格式」转成「存储格式」，是离线管道的通用做法。

2.2 Mapper 里做过滤，而不是在 Hive 里 WHERE

清洗的第二个动作是过滤静态资源。项目里判断 GET /static/ 和 GET /uc_server 开头的请求直接 return ，不写出。为什么不在 Hive 里用 WHERE url NOT LIKE ？因为静态资源请求往往占日志总量的大头，先过滤能把进入 shuffle 和后续存储的数据量砍掉一大截。MapReduce 的 Mapper 输出直接决定 shuffle 数据量，早过滤等于省 IO、省网络、省磁盘。

同时 Mapper 还做了两件「瘦身」：去掉 GET / 、 POST / 前缀和结尾的 HTTP/1.1 ，只保留 ip \t time \t url 三列。状态码和流量在 KPI 计算里用不上，留着只是占空间。这体现了一个原则：清洗阶段的输出 schema 应该贴着下游统计需求设计，而不是保留原始全字段。

protected void map(LongWritable key, Text value, Context context) {
    final String[] parsed = logParser.parse(value.toString());
    // 静态资源对 KPI 无意义，提前丢弃，减少 shuffle 量
    if (parsed[2].startsWith("GET /static/") || parsed[2].startsWith("GET /uc_server")) {
        return;
    }
    if (parsed[2].startsWith("GET /")) {
        parsed[2] = parsed[2].substring("GET /".length());
    } else if (parsed[2].startsWith("POST /")) {
        parsed[2] = parsed[2].substring("POST /".length());
    }
    if (parsed[2].endsWith(" HTTP/1.1")) {
        parsed[2] = parsed[2].substring(0, parsed[2].length() - " HTTP/1.1".length());
    }
    // 只输出 ip、time、url 三列
    outputValue.set(parsed[0] + "\t" + parsed[1] + "\t" + parsed[2]);
    context.write(key, outputValue);
}

Reducer 在这里几乎什么都不做，只是把 value 原样写出。这看起来浪费——既然不需要聚合，为什么还要 Reduce 阶段？因为清洗的目标是「一行一条干净记录」，用 Reducer 输出能保证最终文件是规整的文本，且可以通过设置 reduce 数量控制输出文件个数。如果设 job.setNumReduceTasks(0) ，Mapper 直接输出也能跑，但输出文件数等于分片数，小文件会很多，对后续 Hive 查询不友好。这是一个容易被忽略的细节：清洗 job 的 reduce 数不是为聚合服务，而是为文件布局服务。

2.3 定时脚本：把「每天一次」变成幂等动作

日志是每天产生的，所以上传和清洗必须自动化。项目用 shell 脚本配合 crontab，每天凌晨 1 点把前一天的日志 hadoop fs -put 到 HDFS，再 hadoop jar 跑清洗。脚本里 yesterday=$(date --date='1 days ago' +%Y_%m_%d) 生成日期串，作为 HDFS 目录名和后续分区名。

这里的关键设计是「日期驱动」：所有路径、分区、表名都带日期，使得每天的任务互相隔离，重跑某一天不会污染其他天。但要注意 hadoop fs -put 不是幂等的——如果目标文件已存在会报错。生产上常见的做法是先 -test -e 判断再决定是否上传，或者用 -put -f 覆盖。项目里没写这层判断，实际跑定时任务时如果某天手动重跑过，第二天就会因为文件已存在而失败，这是排查时第一个要看的地方。

3. Hive 分区表：为什么统计前要先建分区

3.1 外部表 + 分区：让 Hive 只扫当天数据

清洗后的数据按天放在 /project/techbbs/cleaned/2015_04_25 这样的目录里。Hive 建表时用 EXTERNAL TABLE 加 PARTITIONED BY (logdate string) ， LOCATION 指向 cleaned 根目录。这样每个子目录就是一个分区，查询时 WHERE logdate='2015_04_25' 只会扫描对应目录，不会全表扫。

为什么用外部表？因为数据是 MapReduce 产出的，Hive 只负责「映射」而不是「拥有」。如果误删表，外部表不会删数据，重跑清洗还能恢复。这是离线数仓里非常常见的约定：原始层和清洗层用外部表，结果层才用管理表。

CREATE EXTERNAL TABLE techbbs(ip string, atime string, url string)
PARTITIONED BY (logdate string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/project/techbbs/cleaned';

ALTER TABLE techbbs ADD PARTITION(logdate='2015_04_25')
LOCATION '/project/techbbs/cleaned/2015_04_25';

注意 FIELDS TERMINATED BY '\t' 必须和清洗输出一致。清洗时用 \t 拼接三列，这里就必须按 \t 切分，否则整行会被当成一个字段， COUNT(DISTINCT ip) 直接算错。这种「写入格式和读取格式必须对齐」的约束，是离线管道里最容易踩的坑之一。

3.2 四个 KPI 的 HQL 与它们的计算代价

PV 最简单， COUNT(1) 数行数。注册用户数用 INSTR(url,'member.php?mod=register')>0 匹配 URL 子串，而不是 = ，因为真实 URL 后面可能带其他参数。独立 IP 用 COUNT(DISTINCT ip) ，这一步会触发 shuffle 去重，数据量大时是性能瓶颈。跳出用户数最绕：先按 ip 分组数次数，再在外层数「次数等于 1」的组数。

-- PV：行数即浏览量
CREATE TABLE techbbs_pv_2015_04_25 AS
SELECT COUNT(1) AS PV FROM techbbs WHERE logdate='2015_04_25';

-- 注册用户数：URL 子串匹配，避免参数顺序变化导致漏算
CREATE TABLE techbbs_reguser_2015_04_25 AS
SELECT COUNT(1) AS REGUSER FROM techbbs
WHERE logdate='2015_04_25' AND INSTR(url,'member.php?mod=register')>0;

-- 独立 IP：DISTINCT 触发全局去重
CREATE TABLE techbbs_ip_2015_04_25 AS
SELECT COUNT(DISTINCT ip) AS IP FROM techbbs WHERE logdate='2015_04_25';

-- 跳出用户：先按 ip 聚合，再筛次数为 1 的组
CREATE TABLE techbbs_jumper_2015_04_25 AS
SELECT COUNT(1) AS jumper FROM (
  SELECT COUNT(ip) AS times FROM techbbs
  WHERE logdate='2015_04_25' GROUP BY ip HAVING times=1
) e;

跳出率的定义是「跳出数 / PV」，所以跳出用户数算出来后还要和 PV 做除法。这里有个语义细节：跳出数统计的是「只出现一条记录的 IP 个数」，严格说这是「单页访问的独立用户数」，和「单页访问次数」在多数场景下等价，但如果同一 IP 一天内多次单页访问，按 IP 分组会把它们合并成一条，导致跳出数偏低。项目采用的就是这个口径，理解它的近似性比争论定义更重要。

3.3 汇总表：为什么用 JOIN ON 1=1 拼指标

四个 KPI 分别落在四张临时表里，导出前要合成一行。项目用 JOIN ... ON 1=1 做笛卡尔积，每张表只有一行，拼出来就是一行五列。这种写法在 Hive 里能跑，但依赖「每张表恰好一行」这个前提。如果某天某个 KPI 查询返回零行（比如当天没有注册）， JOIN 结果就是空，导出到 MySQL 会缺这一天。更稳的写法是用 UNION ALL 转成行再透视，或者用 COALESCE 兜底。项目选择 JOIN ON 1=1 是因为简单直观，但在生产里要意识到它的脆弱性。

4. 避坑与排查：离线管道里最容易翻车的几处

4.1 时间解析在集群上抛异常

现象：本地 IDE 跑清洗 job 正常，提交到集群后 mapper 大量报 ParseException 。原因： SimpleDateFormat 用了默认 locale，集群节点 JVM 的 locale 和本地不一致，英文月份解析失败。解决：显式 new SimpleDateFormat(pattern, Locale.ENGLISH) ，并且把 SimpleDateFormat 定义成 Mapper 的成员变量而非静态共享（它不是线程安全的，多线程复用会串数据）。

4.2 分区目录名和分区值不一致

现象： ALTER TABLE ADD PARTITION 执行成功，但查询该分区返回 0 行。原因： LOCATION 指向的目录名和 logdate 值不匹配，比如分区值是 2015_04_25 但目录是 2015-04-25 。Hive 不会校验目录是否存在，只按路径映射。解决：统一日期格式，脚本里生成目录名和分区值时用同一个变量，不要一处用下划线一处用横杠。

4.3 Sqoop 导出字段分隔符对不上

现象：Sqoop export 成功但 MySQL 里字段错位或全是 NULL。原因：Hive 表默认输出分隔符是 \001 ，而 Sqoop 命令里 --fields-terminated-by 写成了 \t 或其他。解决：导出前确认 Hive 结果表的实际分隔符，Sqoop 参数与之严格一致。项目里用 --fields-terminated-by '\001' ，就是因为 Hive 默认用 \001 作为字段分隔。

4.4 定时任务重跑导致文件已存在

现象：某天手动补跑了一次清洗，第二天定时任务失败，日志显示 put: File exists 。原因： hadoop fs -put 不覆盖已存在文件。解决：脚本里加存在性判断，或改用 -put -f ；更规范的做法是每天的输出目录带日期且先清理再写入，保证幂等。

4.5 小文件过多拖慢 Hive 查询

现象：清洗后 cleaned 目录下每天产生几十个小文件，Hive 查询启动大量 mapper，调度开销比计算还大。原因：清洗 job 的 reduce 数设置过大，或输入分片多而输出未合并。解决：控制 setNumReduceTasks 为较小值（如 1～3），或在清洗后加一步合并小文件的操作。文件布局是离线管道里最容易被忽视、又最影响查询体验的一环。

5. 设计取舍：为什么明细进 HBase、指标进 MySQL

这套管道最后一步是「指标查询走 MySQL，明细查询走 HBase」。这个分工不是随意定的，而是由访问模式决定的。指标是每天一行、按日期主键查询、量极小，MySQL 的关系表加主键索引完全够用，运营者打开报表页就是一次主键点查。明细日志是海量行、需要按 IP 或时间范围检索，MySQL 存不下也查不快，HBase 的 rowkey 设计正好能把 IP 和时间拼进去做前缀扫描。

我一般会这么理解这个取舍：MySQL 承担「已经聚合好的、读多写少、强 schema」的结果层，HBase 承担「原始粒度、按维度随机查、弱 schema」的明细层。两者之间用 Sqoop 做一次批量导出，把 Hive 算好的指标灌进 MySQL。这条链路里，Hive 是计算引擎，MySQL 和 HBase 是两种不同访问模式的服务层，各司其职。

验证这套设计是否合理，有个简单方法：看每个 KPI 的查询频率和延迟要求。PV、注册数、跳出率是每天看几次的报表指标，延迟几百毫秒可接受，MySQL 足够；而「某个 IP 昨天访问了哪些页面」是排障时的临时查询，频率低但要求能按前缀快速定位，HBase 的 rowkey 扫描比全表扫 MySQL 快几个数量级。如果反过来把明细塞进 MySQL，单表上亿行后连分页都卡；把指标塞进 HBase，又失去了 SQL 聚合的便利。

这套管道真正值得带走的东西，不是某条 HQL，而是「按访问模式分层」的思路：采集层保留原始、清洗层贴着统计需求瘦身、计算层用分区隔离每天的数据、服务层按查询模式选存储。从那以后我每次搭离线日志管道，都会先把每个 KPI 的查询方式和数据量写在一张纸上，再决定它落在哪一层，而不是先建表再想怎么查。希望帮到你。

本文还有配套的精品资源，点击获取