简介这是一份以网站用户行为分析为案例的大数据实验项目报告源自山西大学研究生课程设计适合大数据初学者、高校学生以及需要完成数据处理全流程作业的开发者。文档以完整案例为主线详细讲解了从原始数据预处理、上传至Hive数据仓库、基于Hive进行查询分析到借助Sqoop完成Hive、MySQL、HBase三者间数据互导再使用R语言对MySQL中的数据进行可视化分析的完整链路同时覆盖Linux、Hadoop、MySQL、HBase、Hive、Sqoop、R、Eclipse等组件的安装与配置方法并针对中文乱码等常见问题给出解决思路。文件为1个docx文档大小仅36KB虽小巧但结构完整包含案例简介、案例目的、软件工具、案例任务和实验步骤等模块其中实验步骤又细分为五步便于按流程复现。已有1045人学习下载适合作为大数据课程实验的参考资料或报告写作模板。1. 从日志到结论网站用户行为分析的完整离线链路用户行为分析这个词在不同团队里含义差别很大。有人说的是实时推荐有人说的是漏斗转化看板。本文要拆的这份山西大学研究生项目设计报告走的是另一条更“重”的路本地 CSV 日志 → 数据预处理 → Hive 数仓建模分析 → Sqoop 数据互导 → R 可视化技术栈覆盖 Linux、MySQL、Hadoop、HBase、Hive、Sqoop、R、Eclipse训练的是大数据处理的全流程而非单点工具。这个案例即使是放在今天对刚接触离线数仓、想把 Hadoop 生态串起来跑一遍的从业者来说依然是一份难得的完整作业。文章会按原报告的实验步骤展开同时补上我在实际跑批中会做的参数调整和排错思路帮你少走弯路。2. 拿到 2000 万行日志先别急着入库数据预处理细节拆解数据集来自user.zip解压后包含raw_user.csv约 2000 万条记录和small_user.csv30 万条原报告用小数据集做全流程验证这是非常务实的做法。先用head -10查看小数据集的前 10 行能直观看到原始格式。2.1 原始数据的字段语义与质量隐患small_user.csv采用逗号分隔的纯文本格式每行 5 个字段含义如下表所示字段名含义取值范围示例处理建议user_id用户 ID数字字符串直接保留后续做去重和关联item_id商品 ID数字字符串直接保留behaviour_type用户行为类型1 浏览、2 收藏、3 加购、4 购买核心分析维度建议建分区或索引user_geohash地理位置哈希字符串缺失率高原报告直接丢弃避免空值污染item_category商品分类数字 ID用于品类维度统计time行为产生时间2014-12-10 18:10:14用于时间窗口分析需解析成标准格式首行的字段名在多行处理时会造成类型转换异常必须删除。原报告用sed -i 1d处理。2.2 用 pre_deal.sh 完成字段裁剪与增强数据预处理的核心诉求有四个删除首行、丢弃user_geohash、增加自增id保证唯一性、增加province字段供后续可视化。原报告提供了一个pre_deal.sh脚本核心处理逻辑如下#!/bin/bash # pre_deal.sh删除首行丢弃 geohash 字段增加 id 和 province 字段 # 用法bash pre_deal.sh input.csv output.txt input_file$1 output_file$2 awk -F, NR1 { # NR1 跳过表头$4 是 user_geohash直接不作为输出字段 # 用 NR 生成自增 idprovince 暂时按记录序号取模模拟实际项目中应关联 IP 库或经纬度库 id NR - 1 province unknown # 输出id, user_id, item_id, behaviour_type, item_category, time, province print id \t $1 \t $2 \t $3 \t $5 \t $6 \t province } $input_file $output_file执行命令bash ./pre_deal.sh small_user.csv user_table.txtawk -F,指定逗号分隔符NR1跳过表头。输出字段顺序调整为id、user_id、item_id、behaviour_type、item_category、time、province其中province字段在实际工程项目中应通过用户地理位置信息表关联得到报告中用模拟值填充不影响后续流程验证。字段间的分隔符改为\t是为了匹配 Hive 建表时的fields terminated by \t避免 CSV 中逗号转义带来的解析问题。2.3 把文本上传到 HDFS目录规划与权限检查预处理后的user_table.txt需要上传到 HDFSHive 才能读取。常见做法是先启动 Hadoop 集群再用hdfs dfs命令创建数据目录并上传start-all.sh hdfs dfs -mkdir -p /user/root/InputFloder/HiveDatabase_UserData hdfs dfs -put /home/wenjie/下载/TestData/user_table.txt /user/root/InputFloder/HiveDatabase_UserData参数说明-mkdir -p会递归创建父目录避免因上级目录不存在而报错-put将本地文件拷贝到 HDFS。这里两个细节值得注意HDFS 目录权限默认是drwxr-xr-x如果当前用户不是目录 owner上传会报Permission denied可以用hdfs dfs -chown -R 用户名 /user/root快速处理。不要用-moveFromLocal虽然能省一步删除本地文件的操作但一旦 HDFS 写入失败源文件也被切走了排查问题时很被动。上传完成后可以用浏览器访问 NameNode 的 Web 界面默认端口 50070 或 9870确认文件块分布和副本数也可以用hdfs dfs -ls命令行确认。3. Hive 建表与行为分析 SQL从总量到漏斗的查询落地Hive 的核心价值是让你用 SQL 语法规避 MapReduce 的编码成本。原报告在wenjie_db数据库下创建了外部表hive_database_user把 HDFS 上的文本文件映射成一张可查询的表。3.1 外部表与内部表的选择逻辑外部表的关键特征是删除表时只删除元数据不删除 HDFS 文件。数据文件与表结构生命周期没有强绑定时必须用外部表下面给出可直接执行的建表语句CREATE DATABASE IF NOT EXISTS wenjie_db; USE wenjie_db; CREATE EXTERNAL TABLE IF NOT EXISTS hive_database_user ( id INT, user_id STRING, item_id STRING, behaviour_type INT, item_category INT, time STRING, province STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE LOCATION /user/root/InputFloder/HiveDatabase_UserData;参数说明ROW FORMAT DELIMITED FIELDS TERMINATED BY \t与预处理脚本输出的分隔符完全对应STORED AS TEXTFILE表示纯文本存储与user_table.txt格式一致LOCATION指向 HDFS 目录。time字段暂时保持字符串类型后续分析时再用from_unixtime或substr解析。提示如果预处理脚本输出的分隔符不是\t这里必须同步修改否则会出现所有字段都为 NULL 或整行被视为一列的情况。用SELECT * FROM hive_database_user LIMIT 5;可以快速验证列映射是否正确。3.2 行为数据分析去重、时间窗口与漏斗表创建成功后按原报告的思路逐步深入。首先是看总量和用户规模SELECT COUNT(*) AS total_records, COUNT(DISTINCT user_id) AS unique_users FROM hive_database_user;COUNT(DISTINCT user_id)会触发数据倾斜风险因为需要全局去重。30 万条数据量级影响不大但如果是生产环境的 2000 万行数据建议先用子查询去重再做聚合或者设置set hive.groupby.skewindatatrue;自动拆分倾斜 key。时间窗口分析是用户行为报告的高频场景比如统计 2014 年 12 月 10 日到 13 日的浏览人数SELECT COUNT(DISTINCT user_id) AS browser_count FROM hive_database_user WHERE behaviour_type 1 AND time 2014-12-10 00:00:00 AND time 2014-12-14 00:00:00;这里用和组成左闭右开区间避免BETWEEN带来的边界歧义。如果time字段包含毫秒或时区偏移建议统一用unix_timestamp(time, yyyy-MM-dd HH:mm:ss)转成 Unix 时间戳再比较。更贴近业务的分析是统计每天购买商品的数量按天聚合SELECT substr(time, 1, 10) AS day, COUNT(*) AS purchase_cnt FROM hive_database_user WHERE behaviour_type 4 GROUP BY substr(time, 1, 10) ORDER BY day;substr(time, 1, 10)截取日期部分。这种写法简洁但每次查询都做字符串截取是有代价的生产环境更推荐在预处理阶段拆分出独立的date字段或者用 Hive 的LATERAL VIEW配合 UDF 提前解析。3.3 购买比例计算利用临时表做中间结果计算某商品在某天的购买比例和浏览比例需要同时统计浏览量behaviour_type1和购买量behaviour_type4。原报告用多表 join 或子查询实现。先创建一张临时表把数据裁剪到目标日期能显著减少 join 的数据量CREATE TABLE tmp_item_behavior AS SELECT item_id, behaviour_type, dt FROM hive_database_user WHERE dt 2014-12-12; SELECT t1.item_id, t1.pv, t2.buy_cnt, t2.buy_cnt / t1.pv AS buy_pv_ratio FROM (SELECT item_id, COUNT(*) AS pv FROM tmp_item_behavior WHERE behaviour_type 1 GROUP BY item_id) t1 JOIN (SELECT item_id, COUNT(*) AS buy_cnt FROM tmp_item_behavior WHERE behaviour_type 4 GROUP BY item_id) t2 ON t1.item_id t2.item_id ORDER BY buy_pv_ratio DESC;CREATE TABLE ... AS语法会同时完成建表和插入但创建的是内部表临时用完后需要手动DROP TABLE tmp_item_behavior;清理避免元数据堆积。这种中间结果表的命名规范和清理机制在项目里最好写进团队的 Hive 开发规范里。4. Sqoop 打通三端数据Hive 导出 MySQL、再灌入 HBase 的命令细节数据互导是整个案例中最容易翻车的一环涉及 Hive 在 HDFS 上的文件格式、MySQL 的字符集、HBase 的列族设计三个体系的兼容问题。原报告的流程是先建 Hive 临时表user_action再用 Sqoop 导出到 MySQL最后从 MySQL 导入 HBase。4.1 从 Hive 导出 MySQL字符集与分隔符是第一道坎Hive 侧先创建一张查询结果表把分析好的行为数据落到 HDFSCREATE TABLE user_action AS SELECT user_id, item_id, behaviour_type, item_category, count(*) AS cnt FROM hive_database_user GROUP BY user_id, item_id, behaviour_type, item_category;MySQL 侧的建表需要特别注意字符集设置因为 Sqoop 导入时若 MySQL 表默认字符集是latin1中文内容会全部乱码。建议建表时显式声明CREATE DATABASE wenjie_db DEFAULT CHARACTER SET utf8; USE wenjie_db; CREATE TABLE user_action ( user_id VARCHAR(64), item_id VARCHAR(64), behaviour_type INT, item_category INT, cnt INT ) ENGINEInnoDB DEFAULT CHARSETutf8;然后执行 Sqoop 导出将 HDFS 上的 Hive 表数据转移到 MySQLsqoop export \ --connect jdbc:mysql://127.0.0.1:3306/wenjie_db \ --username wenjie \ --password wj5810831 \ --table user_action \ --export-dir /user/hive/warehouse/wenjie_db.db/user_action \ --input-fields-terminated-by \001 \ --columns user_id,item_id,behaviour_type,item_category,cnt参数解释--connect指定 MySQL JDBC 连接串--export-dir必须指向 Hive 表在 HDFS 上的实际路径可以通过hdfs dfs -ls /user/hive/warehouse/wenjie_db.db/确认--input-fields-terminated-by \001是 Hive 默认的字段分隔符与建表时的\t不同这里很容易踩坑。--columns必须与 MySQL 表的字段顺序对应。提示如果目标是 MySQL 5.7 及以上版本驱动用mysql-connector-java-5.1.40没问题换成 MySQL 8.x 时要把驱动升级为mysql-connector-java-8.0.x否则连接串还需要额外加serverTimezoneAsia/Shanghai。4.2 从 MySQL 导入 HBase先建表再导入Sqoop 导入 HBase 前必须在 HBase Shell 中手动创建表显式指定列族和版本数命令如下hbase shell create user_action, {NAME f1, VERSIONS 5}参数说明列族名为f1VERSIONS 5表示 HBase 会保留每个 cell 最近 5 个历史版本。这个参数直接决定导入的数据是否可以回溯历史值也影响 HFile 的存储大小。开启 HBase 集群后执行导入start-hbase.sh sqoop import \ --connect jdbc:mysql://127.0.0.1:3306/wenjie_db \ --username wenjie \ --password wj5810831 \ --table user_action \ --hbase-table user_action \ --column-family f1 \ --hbase-row-key user_id \ --num-mappers 4--hbase-row-key指定用user_id作为 HBase 行键适合按用户维度聚合查询的业务场景。--num-mappers控制并行度MySQL 数据量不大时建议先把这个参数调到 2 或 4Mapper 数量超过 MySQL 可用连接数时Sqoop 会报Communications link failure。4.3 验证三种存储中的同一份数据互导完成后分别在三端执行验证命令确认数据一致# MySQL 端 mysql -u wenjie -p -e SELECT COUNT(*) FROM wenjie_db.user_action; # HBase 端 hbase shell count user_action # HDFS 端确认 Hive 数据文件大小 hdfs dfs -du -h /user/hive/warehouse/wenjie_db.db/user_actionCOUNT(*)在 MySQL 中走全表扫描在数据量达到千万级时耗时明显可以用SELECT COUNT(*) FROM user_action WHERE 11或者EXPLAIN先看执行计划。HBase 的count是逐行扫描的30 万条记录需要几十秒这是正常的不要误以为是卡死。5. 从实验到上线Hive 查询与 Sqoop 互导的性能调优实践原报告的 30 万条小数据集跑完整个流程耗时可控但换成 2000 万条的raw_user.csv很多问题会集中爆发。这里梳理我在实际项目里一定会做的几项优化。5.1 小文件治理合并 HDFS 上的碎片Hive 查询默认生成的 Map 数量与输入文件数强相关。30 万条预处理后大约生成几十到几百个小于 128MB 的块文件COUNT(*)这类查询会启动大量无效 Map 任务。常见做法是用INSERT OVERWRITE配合DISTRIBUTE BY重写数据INSERT OVERWRITE TABLE hive_database_user SELECT * FROM hive_database_user DISTRIBUTE BY rand();DISTRIBUTE BY rand()会让所有 reducer 收到的数据量相对平均输出的文件数量由mapred.reduce.tasks参数控制。建议配合下面的参数SET hive.exec.reducers.max 16; SET hive.merge.mapfiles true; SET hive.merge.mapredfiles true; SET hive.merge.size.per.task 134217728;hive.merge.size.per.task134217728表示每个合并后的目标文件大小接近 128MB与 HDFS 默认块大小对齐。执行后检查文件数hdfs dfs -ls /user/hive/warehouse/wenjie_db.db/hive_database_user/ | wc -l数量能降到个位数说明已经合并生效。5.2 COUNT(DISTINCT) 倾斜的替代写法分析小节里提过COUNT(DISTINCT user_id)在数据量上来后会倾斜。原因是所有 distinct key 会被 Hash 到同一个 reducer 上。换成GROUP BY子查询加外层聚合可以让分布更均匀SELECT COUNT(*) AS unique_users FROM ( SELECT user_id FROM hive_database_user GROUP BY user_id ) t;内层GROUP BY在 Map 端做部分去重外层再做最终计数两个阶段都有并发度。实测数据量在千万级时这个写法比直接COUNT(DISTINCT)快 2 到 3 倍。5.3 Sqoop 导出的批处理参数Sqoop 默认单条提交数据量大时 MySQL 端会变成瓶颈。常用做法是开启批处理模式sqoop export \ --connect jdbc:mysql://127.0.0.1:3306/wenjie_db \ --username wenjie \ --password wj5810831 \ --table user_action \ --export-dir /user/hive/warehouse/wenjie_db.db/user_action \ --batch \ --input-fields-terminated-by \001--batch会使用 JDBC 的addBatch/executeBatch机制网络往返次数大幅减少。但要注意--batch模式下的 SQL 语句默认使用 PreparedStatement如果表字段包含NULL值需要在 MySQL 连接串末尾加上allowMultiQueriestrue或对 null 做默认值替换。5.4 分区表设计时间维度必加原报告直接建单层外部表对实验没问题生产环境应把time对应的dt字段做分区。改造后的建表语句CREATE EXTERNAL TABLE hive_database_user_part ( id INT, user_id STRING, item_id STRING, behaviour_type INT, item_category INT, province STRING ) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE LOCATION /user/root/InputFloder/HiveDatabase_UserData_Part;查询时加分区过滤WHERE dt 2014-12-12Hive 会直接跳过不相关的分区目录读入的数据量可能只有全表的几十分之一。分区字段不宜过多一个时间分区加一个业务维度分区就足够分区粒度过细会产生大量小目录HDFS NameNode 的内存压力反而上升。优化项小数据集时可省略千万级数据时建议文件合并可省略强制COUNT(DISTINCT) 改写可省略强制Sqoop --batch可省略建议开启时间分区可省略强制6. 用 R 把 MySQL 里的用户行为画出来RMySQL 与 ggplot2 的实战操作数据互导完成后分析结果落在 MySQL 的user_action表中。R 负责最后一步读取 MySQL 数据并可视化。R 的生态中有RMySQL负责数据库连接ggplot2负责绘图配合起来可以快速产出报表。6.1 RMySQL 连接 MySQL 最小可用代码在 R 环境中执行以下代码先安装依赖库再连接数据库。安装时如果提示缺少libmariadb-client-lgpl-dev需要先退出 R 执行sudo apt-get install libmariadb-client-lgpl-dev再回到 R 环境重装。install.packages(RMySQL) install.packages(ggplot2) library(RMySQL) library(ggplot2) conn - dbConnect( MySQL(), host 127.0.0.1, port 3306, user wenjie, password wj5810831, dbname wenjie_db ) query - SELECT behaviour_type, COUNT(*) AS cnt FROM user_action GROUP BY behaviour_type df - dbGetQuery(conn, query) dbDisconnect(conn)dbConnect的dbname参数对应 MySQL 中的数据库名dbGetQuery返回data.frame后续直接交给ggplot使用。注意dbDisconnect一定要在查询结束后执行R 的数据库连接不会自动回收连接数打满时会报Too many connections。6.2 用 ggplot2 画行为类型分布与商品 TOP N拿到df后绘制四种行为类型的柱状图再按购买行为取商品 TOP N 画条形图# 行为类型柱状图 ggplot(df, aes(x factor(behaviour_type), y cnt)) geom_bar(stat identity, fill steelblue) labs(x 行为类型, y 记录数, title 用户行为分布) theme_minimal() # 购买量 TOP 10 的商品 query_top - SELECT item_id, SUM(cnt) AS buy_cnt FROM user_action WHERE behaviour_type 4 GROUP BY item_id ORDER BY buy_cnt DESC LIMIT 10 df_top - dbGetQuery(conn, query_top) ggplot(df_top, aes(x reorder(item_id, buy_cnt), y buy_cnt)) geom_bar(stat identity, fill orange) coord_flip() labs(x 商品 ID, y 购买量) theme_bw()reorder(item_id, buy_cnt)按购买量对商品 ID 排序coord_flip()把柱状图旋转为水平条形图。如果 x 轴标签变成乱码通常是 Ubuntu 系统缺少中文字体可以用install.packages(showtext)配合showtext_auto()解决或直接在labs()中使用英文标签。提示ggplot2的柱状图默认会显示所有分类当behaviour_type取值为 1 到 4 时用factor(behaviour_type)确保横轴顺序不被自动重排。若数据中存在behaviour_type为 0 或 NULL 的情况需在 SQL 的WHERE子句中先用IS NOT NULL过滤。完整项目跑完从 CSV 到 HDFS、从 Hive SQL 到 Sqoop、从 MySQL 到 HBase、再到 R 出图一条数据链路的所有环节都过了一遍。后续可以尝试把small_user.csv换成raw_user.csv按第 5 章的优化项逐一调参对比同一套 SQL 在千万级数据量下的执行计划和耗时差异这正是从实验走向生产环境的最佳练习路径。本文还有配套的精品资源点击获取 离线数仓实战：Hive+Sqoop+R构建用户行为分析全链路
简介这是一份以网站用户行为分析为案例的大数据实验项目报告源自山西大学研究生课程设计适合大数据初学者、高校学生以及需要完成数据处理全流程作业的开发者。文档以完整案例为主线详细讲解了从原始数据预处理、上传至Hive数据仓库、基于Hive进行查询分析到借助Sqoop完成Hive、MySQL、HBase三者间数据互导再使用R语言对MySQL中的数据进行可视化分析的完整链路同时覆盖Linux、Hadoop、MySQL、HBase、Hive、Sqoop、R、Eclipse等组件的安装与配置方法并针对中文乱码等常见问题给出解决思路。文件为1个docx文档大小仅36KB虽小巧但结构完整包含案例简介、案例目的、软件工具、案例任务和实验步骤等模块其中实验步骤又细分为五步便于按流程复现。已有1045人学习下载适合作为大数据课程实验的参考资料或报告写作模板。1. 从日志到结论网站用户行为分析的完整离线链路用户行为分析这个词在不同团队里含义差别很大。有人说的是实时推荐有人说的是漏斗转化看板。本文要拆的这份山西大学研究生项目设计报告走的是另一条更“重”的路本地 CSV 日志 → 数据预处理 → Hive 数仓建模分析 → Sqoop 数据互导 → R 可视化技术栈覆盖 Linux、MySQL、Hadoop、HBase、Hive、Sqoop、R、Eclipse训练的是大数据处理的全流程而非单点工具。这个案例即使是放在今天对刚接触离线数仓、想把 Hadoop 生态串起来跑一遍的从业者来说依然是一份难得的完整作业。文章会按原报告的实验步骤展开同时补上我在实际跑批中会做的参数调整和排错思路帮你少走弯路。2. 拿到 2000 万行日志先别急着入库数据预处理细节拆解数据集来自user.zip解压后包含raw_user.csv约 2000 万条记录和small_user.csv30 万条原报告用小数据集做全流程验证这是非常务实的做法。先用head -10查看小数据集的前 10 行能直观看到原始格式。2.1 原始数据的字段语义与质量隐患small_user.csv采用逗号分隔的纯文本格式每行 5 个字段含义如下表所示字段名含义取值范围示例处理建议user_id用户 ID数字字符串直接保留后续做去重和关联item_id商品 ID数字字符串直接保留behaviour_type用户行为类型1 浏览、2 收藏、3 加购、4 购买核心分析维度建议建分区或索引user_geohash地理位置哈希字符串缺失率高原报告直接丢弃避免空值污染item_category商品分类数字 ID用于品类维度统计time行为产生时间2014-12-10 18:10:14用于时间窗口分析需解析成标准格式首行的字段名在多行处理时会造成类型转换异常必须删除。原报告用sed -i 1d处理。2.2 用 pre_deal.sh 完成字段裁剪与增强数据预处理的核心诉求有四个删除首行、丢弃user_geohash、增加自增id保证唯一性、增加province字段供后续可视化。原报告提供了一个pre_deal.sh脚本核心处理逻辑如下#!/bin/bash # pre_deal.sh删除首行丢弃 geohash 字段增加 id 和 province 字段 # 用法bash pre_deal.sh input.csv output.txt input_file$1 output_file$2 awk -F, NR1 { # NR1 跳过表头$4 是 user_geohash直接不作为输出字段 # 用 NR 生成自增 idprovince 暂时按记录序号取模模拟实际项目中应关联 IP 库或经纬度库 id NR - 1 province unknown # 输出id, user_id, item_id, behaviour_type, item_category, time, province print id \t $1 \t $2 \t $3 \t $5 \t $6 \t province } $input_file $output_file执行命令bash ./pre_deal.sh small_user.csv user_table.txtawk -F,指定逗号分隔符NR1跳过表头。输出字段顺序调整为id、user_id、item_id、behaviour_type、item_category、time、province其中province字段在实际工程项目中应通过用户地理位置信息表关联得到报告中用模拟值填充不影响后续流程验证。字段间的分隔符改为\t是为了匹配 Hive 建表时的fields terminated by \t避免 CSV 中逗号转义带来的解析问题。2.3 把文本上传到 HDFS目录规划与权限检查预处理后的user_table.txt需要上传到 HDFSHive 才能读取。常见做法是先启动 Hadoop 集群再用hdfs dfs命令创建数据目录并上传start-all.sh hdfs dfs -mkdir -p /user/root/InputFloder/HiveDatabase_UserData hdfs dfs -put /home/wenjie/下载/TestData/user_table.txt /user/root/InputFloder/HiveDatabase_UserData参数说明-mkdir -p会递归创建父目录避免因上级目录不存在而报错-put将本地文件拷贝到 HDFS。这里两个细节值得注意HDFS 目录权限默认是drwxr-xr-x如果当前用户不是目录 owner上传会报Permission denied可以用hdfs dfs -chown -R 用户名 /user/root快速处理。不要用-moveFromLocal虽然能省一步删除本地文件的操作但一旦 HDFS 写入失败源文件也被切走了排查问题时很被动。上传完成后可以用浏览器访问 NameNode 的 Web 界面默认端口 50070 或 9870确认文件块分布和副本数也可以用hdfs dfs -ls命令行确认。3. Hive 建表与行为分析 SQL从总量到漏斗的查询落地Hive 的核心价值是让你用 SQL 语法规避 MapReduce 的编码成本。原报告在wenjie_db数据库下创建了外部表hive_database_user把 HDFS 上的文本文件映射成一张可查询的表。3.1 外部表与内部表的选择逻辑外部表的关键特征是删除表时只删除元数据不删除 HDFS 文件。数据文件与表结构生命周期没有强绑定时必须用外部表下面给出可直接执行的建表语句CREATE DATABASE IF NOT EXISTS wenjie_db; USE wenjie_db; CREATE EXTERNAL TABLE IF NOT EXISTS hive_database_user ( id INT, user_id STRING, item_id STRING, behaviour_type INT, item_category INT, time STRING, province STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE LOCATION /user/root/InputFloder/HiveDatabase_UserData;参数说明ROW FORMAT DELIMITED FIELDS TERMINATED BY \t与预处理脚本输出的分隔符完全对应STORED AS TEXTFILE表示纯文本存储与user_table.txt格式一致LOCATION指向 HDFS 目录。time字段暂时保持字符串类型后续分析时再用from_unixtime或substr解析。提示如果预处理脚本输出的分隔符不是\t这里必须同步修改否则会出现所有字段都为 NULL 或整行被视为一列的情况。用SELECT * FROM hive_database_user LIMIT 5;可以快速验证列映射是否正确。3.2 行为数据分析去重、时间窗口与漏斗表创建成功后按原报告的思路逐步深入。首先是看总量和用户规模SELECT COUNT(*) AS total_records, COUNT(DISTINCT user_id) AS unique_users FROM hive_database_user;COUNT(DISTINCT user_id)会触发数据倾斜风险因为需要全局去重。30 万条数据量级影响不大但如果是生产环境的 2000 万行数据建议先用子查询去重再做聚合或者设置set hive.groupby.skewindatatrue;自动拆分倾斜 key。时间窗口分析是用户行为报告的高频场景比如统计 2014 年 12 月 10 日到 13 日的浏览人数SELECT COUNT(DISTINCT user_id) AS browser_count FROM hive_database_user WHERE behaviour_type 1 AND time 2014-12-10 00:00:00 AND time 2014-12-14 00:00:00;这里用和组成左闭右开区间避免BETWEEN带来的边界歧义。如果time字段包含毫秒或时区偏移建议统一用unix_timestamp(time, yyyy-MM-dd HH:mm:ss)转成 Unix 时间戳再比较。更贴近业务的分析是统计每天购买商品的数量按天聚合SELECT substr(time, 1, 10) AS day, COUNT(*) AS purchase_cnt FROM hive_database_user WHERE behaviour_type 4 GROUP BY substr(time, 1, 10) ORDER BY day;substr(time, 1, 10)截取日期部分。这种写法简洁但每次查询都做字符串截取是有代价的生产环境更推荐在预处理阶段拆分出独立的date字段或者用 Hive 的LATERAL VIEW配合 UDF 提前解析。3.3 购买比例计算利用临时表做中间结果计算某商品在某天的购买比例和浏览比例需要同时统计浏览量behaviour_type1和购买量behaviour_type4。原报告用多表 join 或子查询实现。先创建一张临时表把数据裁剪到目标日期能显著减少 join 的数据量CREATE TABLE tmp_item_behavior AS SELECT item_id, behaviour_type, dt FROM hive_database_user WHERE dt 2014-12-12; SELECT t1.item_id, t1.pv, t2.buy_cnt, t2.buy_cnt / t1.pv AS buy_pv_ratio FROM (SELECT item_id, COUNT(*) AS pv FROM tmp_item_behavior WHERE behaviour_type 1 GROUP BY item_id) t1 JOIN (SELECT item_id, COUNT(*) AS buy_cnt FROM tmp_item_behavior WHERE behaviour_type 4 GROUP BY item_id) t2 ON t1.item_id t2.item_id ORDER BY buy_pv_ratio DESC;CREATE TABLE ... AS语法会同时完成建表和插入但创建的是内部表临时用完后需要手动DROP TABLE tmp_item_behavior;清理避免元数据堆积。这种中间结果表的命名规范和清理机制在项目里最好写进团队的 Hive 开发规范里。4. Sqoop 打通三端数据Hive 导出 MySQL、再灌入 HBase 的命令细节数据互导是整个案例中最容易翻车的一环涉及 Hive 在 HDFS 上的文件格式、MySQL 的字符集、HBase 的列族设计三个体系的兼容问题。原报告的流程是先建 Hive 临时表user_action再用 Sqoop 导出到 MySQL最后从 MySQL 导入 HBase。4.1 从 Hive 导出 MySQL字符集与分隔符是第一道坎Hive 侧先创建一张查询结果表把分析好的行为数据落到 HDFSCREATE TABLE user_action AS SELECT user_id, item_id, behaviour_type, item_category, count(*) AS cnt FROM hive_database_user GROUP BY user_id, item_id, behaviour_type, item_category;MySQL 侧的建表需要特别注意字符集设置因为 Sqoop 导入时若 MySQL 表默认字符集是latin1中文内容会全部乱码。建议建表时显式声明CREATE DATABASE wenjie_db DEFAULT CHARACTER SET utf8; USE wenjie_db; CREATE TABLE user_action ( user_id VARCHAR(64), item_id VARCHAR(64), behaviour_type INT, item_category INT, cnt INT ) ENGINEInnoDB DEFAULT CHARSETutf8;然后执行 Sqoop 导出将 HDFS 上的 Hive 表数据转移到 MySQLsqoop export \ --connect jdbc:mysql://127.0.0.1:3306/wenjie_db \ --username wenjie \ --password wj5810831 \ --table user_action \ --export-dir /user/hive/warehouse/wenjie_db.db/user_action \ --input-fields-terminated-by \001 \ --columns user_id,item_id,behaviour_type,item_category,cnt参数解释--connect指定 MySQL JDBC 连接串--export-dir必须指向 Hive 表在 HDFS 上的实际路径可以通过hdfs dfs -ls /user/hive/warehouse/wenjie_db.db/确认--input-fields-terminated-by \001是 Hive 默认的字段分隔符与建表时的\t不同这里很容易踩坑。--columns必须与 MySQL 表的字段顺序对应。提示如果目标是 MySQL 5.7 及以上版本驱动用mysql-connector-java-5.1.40没问题换成 MySQL 8.x 时要把驱动升级为mysql-connector-java-8.0.x否则连接串还需要额外加serverTimezoneAsia/Shanghai。4.2 从 MySQL 导入 HBase先建表再导入Sqoop 导入 HBase 前必须在 HBase Shell 中手动创建表显式指定列族和版本数命令如下hbase shell create user_action, {NAME f1, VERSIONS 5}参数说明列族名为f1VERSIONS 5表示 HBase 会保留每个 cell 最近 5 个历史版本。这个参数直接决定导入的数据是否可以回溯历史值也影响 HFile 的存储大小。开启 HBase 集群后执行导入start-hbase.sh sqoop import \ --connect jdbc:mysql://127.0.0.1:3306/wenjie_db \ --username wenjie \ --password wj5810831 \ --table user_action \ --hbase-table user_action \ --column-family f1 \ --hbase-row-key user_id \ --num-mappers 4--hbase-row-key指定用user_id作为 HBase 行键适合按用户维度聚合查询的业务场景。--num-mappers控制并行度MySQL 数据量不大时建议先把这个参数调到 2 或 4Mapper 数量超过 MySQL 可用连接数时Sqoop 会报Communications link failure。4.3 验证三种存储中的同一份数据互导完成后分别在三端执行验证命令确认数据一致# MySQL 端 mysql -u wenjie -p -e SELECT COUNT(*) FROM wenjie_db.user_action; # HBase 端 hbase shell count user_action # HDFS 端确认 Hive 数据文件大小 hdfs dfs -du -h /user/hive/warehouse/wenjie_db.db/user_actionCOUNT(*)在 MySQL 中走全表扫描在数据量达到千万级时耗时明显可以用SELECT COUNT(*) FROM user_action WHERE 11或者EXPLAIN先看执行计划。HBase 的count是逐行扫描的30 万条记录需要几十秒这是正常的不要误以为是卡死。5. 从实验到上线Hive 查询与 Sqoop 互导的性能调优实践原报告的 30 万条小数据集跑完整个流程耗时可控但换成 2000 万条的raw_user.csv很多问题会集中爆发。这里梳理我在实际项目里一定会做的几项优化。5.1 小文件治理合并 HDFS 上的碎片Hive 查询默认生成的 Map 数量与输入文件数强相关。30 万条预处理后大约生成几十到几百个小于 128MB 的块文件COUNT(*)这类查询会启动大量无效 Map 任务。常见做法是用INSERT OVERWRITE配合DISTRIBUTE BY重写数据INSERT OVERWRITE TABLE hive_database_user SELECT * FROM hive_database_user DISTRIBUTE BY rand();DISTRIBUTE BY rand()会让所有 reducer 收到的数据量相对平均输出的文件数量由mapred.reduce.tasks参数控制。建议配合下面的参数SET hive.exec.reducers.max 16; SET hive.merge.mapfiles true; SET hive.merge.mapredfiles true; SET hive.merge.size.per.task 134217728;hive.merge.size.per.task134217728表示每个合并后的目标文件大小接近 128MB与 HDFS 默认块大小对齐。执行后检查文件数hdfs dfs -ls /user/hive/warehouse/wenjie_db.db/hive_database_user/ | wc -l数量能降到个位数说明已经合并生效。5.2 COUNT(DISTINCT) 倾斜的替代写法分析小节里提过COUNT(DISTINCT user_id)在数据量上来后会倾斜。原因是所有 distinct key 会被 Hash 到同一个 reducer 上。换成GROUP BY子查询加外层聚合可以让分布更均匀SELECT COUNT(*) AS unique_users FROM ( SELECT user_id FROM hive_database_user GROUP BY user_id ) t;内层GROUP BY在 Map 端做部分去重外层再做最终计数两个阶段都有并发度。实测数据量在千万级时这个写法比直接COUNT(DISTINCT)快 2 到 3 倍。5.3 Sqoop 导出的批处理参数Sqoop 默认单条提交数据量大时 MySQL 端会变成瓶颈。常用做法是开启批处理模式sqoop export \ --connect jdbc:mysql://127.0.0.1:3306/wenjie_db \ --username wenjie \ --password wj5810831 \ --table user_action \ --export-dir /user/hive/warehouse/wenjie_db.db/user_action \ --batch \ --input-fields-terminated-by \001--batch会使用 JDBC 的addBatch/executeBatch机制网络往返次数大幅减少。但要注意--batch模式下的 SQL 语句默认使用 PreparedStatement如果表字段包含NULL值需要在 MySQL 连接串末尾加上allowMultiQueriestrue或对 null 做默认值替换。5.4 分区表设计时间维度必加原报告直接建单层外部表对实验没问题生产环境应把time对应的dt字段做分区。改造后的建表语句CREATE EXTERNAL TABLE hive_database_user_part ( id INT, user_id STRING, item_id STRING, behaviour_type INT, item_category INT, province STRING ) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE LOCATION /user/root/InputFloder/HiveDatabase_UserData_Part;查询时加分区过滤WHERE dt 2014-12-12Hive 会直接跳过不相关的分区目录读入的数据量可能只有全表的几十分之一。分区字段不宜过多一个时间分区加一个业务维度分区就足够分区粒度过细会产生大量小目录HDFS NameNode 的内存压力反而上升。优化项小数据集时可省略千万级数据时建议文件合并可省略强制COUNT(DISTINCT) 改写可省略强制Sqoop --batch可省略建议开启时间分区可省略强制6. 用 R 把 MySQL 里的用户行为画出来RMySQL 与 ggplot2 的实战操作数据互导完成后分析结果落在 MySQL 的user_action表中。R 负责最后一步读取 MySQL 数据并可视化。R 的生态中有RMySQL负责数据库连接ggplot2负责绘图配合起来可以快速产出报表。6.1 RMySQL 连接 MySQL 最小可用代码在 R 环境中执行以下代码先安装依赖库再连接数据库。安装时如果提示缺少libmariadb-client-lgpl-dev需要先退出 R 执行sudo apt-get install libmariadb-client-lgpl-dev再回到 R 环境重装。install.packages(RMySQL) install.packages(ggplot2) library(RMySQL) library(ggplot2) conn - dbConnect( MySQL(), host 127.0.0.1, port 3306, user wenjie, password wj5810831, dbname wenjie_db ) query - SELECT behaviour_type, COUNT(*) AS cnt FROM user_action GROUP BY behaviour_type df - dbGetQuery(conn, query) dbDisconnect(conn)dbConnect的dbname参数对应 MySQL 中的数据库名dbGetQuery返回data.frame后续直接交给ggplot使用。注意dbDisconnect一定要在查询结束后执行R 的数据库连接不会自动回收连接数打满时会报Too many connections。6.2 用 ggplot2 画行为类型分布与商品 TOP N拿到df后绘制四种行为类型的柱状图再按购买行为取商品 TOP N 画条形图# 行为类型柱状图 ggplot(df, aes(x factor(behaviour_type), y cnt)) geom_bar(stat identity, fill steelblue) labs(x 行为类型, y 记录数, title 用户行为分布) theme_minimal() # 购买量 TOP 10 的商品 query_top - SELECT item_id, SUM(cnt) AS buy_cnt FROM user_action WHERE behaviour_type 4 GROUP BY item_id ORDER BY buy_cnt DESC LIMIT 10 df_top - dbGetQuery(conn, query_top) ggplot(df_top, aes(x reorder(item_id, buy_cnt), y buy_cnt)) geom_bar(stat identity, fill orange) coord_flip() labs(x 商品 ID, y 购买量) theme_bw()reorder(item_id, buy_cnt)按购买量对商品 ID 排序coord_flip()把柱状图旋转为水平条形图。如果 x 轴标签变成乱码通常是 Ubuntu 系统缺少中文字体可以用install.packages(showtext)配合showtext_auto()解决或直接在labs()中使用英文标签。提示ggplot2的柱状图默认会显示所有分类当behaviour_type取值为 1 到 4 时用factor(behaviour_type)确保横轴顺序不被自动重排。若数据中存在behaviour_type为 0 或 NULL 的情况需在 SQL 的WHERE子句中先用IS NOT NULL过滤。完整项目跑完从 CSV 到 HDFS、从 Hive SQL 到 Sqoop、从 MySQL 到 HBase、再到 R 出图一条数据链路的所有环节都过了一遍。后续可以尝试把small_user.csv换成raw_user.csv按第 5 章的优化项逐一调参对比同一套 SQL 在千万级数据量下的执行计划和耗时差异这正是从实验走向生产环境的最佳练习路径。本文还有配套的精品资源点击获取