单元8 综合项目——搭建教育大数据分析平台
从项目背景、需求、数仓分层，到 Sqoop 采集、指标分析、DataEase 可视化全流程交付
本单元学习目标 摘自课件“学习目标/Target”页
- 了解项目建设基础，能够说明项目背景、项目需求、原始数据结构和项目架构。
- 了解部署Spark，能够基于Spark On YARN模式完成Spark的部署。
- 掌握构建数据仓库，能够熟练运用HiveQL语句构建本项目所需的数据仓库。
- 熟悉业务数据采集流程，能够通过编写和运行Spark程序将业务数据采集到数据仓库。
- 掌握业务数据分析方法，能够使用HiveQL语句完成指标统计。
- 了解数据可视化，能够将数据分析结果导出到MySQL，并利用FineBI进行可视化展示。
- 掌握业务数据预处理方法，能够使用HiveQL语句完成数据关联、字段转换、空值处理等操作。
单元概述
本单元以教育大数据分析平台的实现为案例，全面展示 Hive在实际项目中的综合应用。通过本项目，读者将进一步理解并掌握Hive在数据仓库建设中的关键作用。项目主要任务包括使用Hive搭建数据仓库，完成数据的转换与分析，同时结合Spark实现Hive数据的导入与导出。
知识地图
本单元知识地图
学完本单元，你应该能回答
- 一个离线分析项目从需求到看板要经历哪些环节？
- ODS / DWD / DWS / ADS 分层在项目中如何落地？
- 五个分析指标分别用到了哪些 SQL 能力？
- DataEase 如何对接 Hive/MySQL 结果集做可视化？
知识链位置
核心知识 点击标题右侧勾选框可标记“已掌握”，进度会保存在本机浏览器
8.1 明确项目建设基础
了解项目背景
了解项目建设基础，能够说明本项目的背景。
近年来，随着移动互联网技术的普及与发展，在线教育行业迅速崛起，成为教育领域的重要组成部分。尤其是在教育数字化转型加速的背景下，线上教学模式广泛推广，打破了传统教学的时空限制，为广大师生提供了便捷灵活的学习方式。
在特定时期，例如大规模线下教学无法正常开展的情况下，全国各地的教师通过线上平台开展远程教学，实现“停课不停学”。同时，线上教育平台快速响应用户需求，承接了大量来自线下教学场景的用户流量，行业迎来一轮快速增长。
然而，用户规模的扩大也带来了新的挑战。如何提升服务质量和教学效果，成为各大教育平台面临的重要课题。如果不能对海量的用户行为数据进行深入挖掘和科学分析，企业管理者将难以获得有效的数据支持，影响平台的运营与决策。
在此背景下，教育大数据分析平台应运而生。该平台通过大数据技术对用户行为数据进行全面采集、分析与可视化展示，为教育平台优化资源配置、提升教学质量和用户体验提供有力的数据支撑。
📄 展开课件原文（第 8–12 页，共 5 页）
分析项目需求
了解项目建设基础，能够说出本项目的需求。
为满足教育平台在用户服务优化、教学质量提升等方面的业务需求，本项目基于实际运营场景，围绕用户访问行为与咨询行为展开数据分析。在正式进行系统设计与开发之前，需明确平台所需解决的核心问题。以下是本项目的主要业务需求。
访问用户量统计
会话页面统计
总访问用户量统计
来源渠道访问用户量统计
咨询率统计
访问用户量是指访问网站的用户总数（按用户去重计算），不同于访问次数，可避免单个用户多次访问造成的数据干扰。
在本项目中，重点统计访问网站的总用户量，以全面了解平台的整体流量水平。该数据不仅可用于评估网站的访问情况，还可作为后续数据分析的基础。
来源渠道是指用户访问网站的来源，例如搜索引擎推广、广告投放链接等。
咨询率是指在所有访问用户中，发起有效咨询的用户所占的比例。
会话页面是指用户在当前页面首次发起与客服对话的起始页面。
📄 展开课件原文（第 13–15 页，共 3 页）
认识原始数据结构
了解项目建设基础，能够说出本项目的原始数据结构。
本项目使用的数据来源于某在线教育网站的咨询系统，时间范围为2025年7月份的会话记录。这些数据存储在MySQL数据库的用户会话信息表web_chat_ems_2025_07和用户会话信息附属表web_chat_text_ems_2025_07中。用户会话信息表web_chat_ems_2025_07结构如表。
| 字段 | 字段类型 | 描述 |
|---|---|---|
| id | INT | 主键ID |
| session_id | VARCHAR(48) | 会话ID |
| sid | VARCHAR(48) | 用户ID |
| create_time | DATETIME | 会话创建时间 |
| 字段 | 字段类型 | 描述 |
|---|---|---|
| seo_source | VARCHAR(255) | 搜索来源 |
| seo_keywords | VARCHAR(512) | 搜索关键字 |
| ip | VARCHAR(48) | IP地址 |
| area | VARCHAR(255) | 地区 |
| country | VARCHAR(16) | 国家 |
| province | VARCHAR(16) | 省级行政区 |
| city | VARCHAR(255) | 城市 |
| 字段 | 字段类型 | 描述 |
|---|---|---|
| origin_channel | VARCHAR(32) | 来源渠道 |
| user_match | VARCHAR(255) | 所属客服 |
| manual_time | DATETIME | 客服开始时间 |
| begin_time | DATETIME | 客服领取时间 |
| end_time | DATETIME | 客服结束时间 |
| last_customer_msg_time_stamp | DATETIME | 用户发送最后一条消息的时间 |
| 字段 | 字段类型 | 描述 |
|---|---|---|
| last_agent_msg_time_stamp | DATETIME | 客服回复最后一条消息的时间 |
| reply_msg_count | INT | 客服回复消息数 |
| msg_count | INT | 用户发送消息数 |
| browser_name | VARCHAR(255) | 用户使用的浏览器名称 |
| os_info | VARCHAR(255) | 用户使用的设备系统名称 |
用户会话信息表web_chat_text_ems_2025_07结构如表。
| 字段 | 字段类型 | 描述 |
|---|---|---|
| id | INT | 主键ID |
| referrer | TEXT | 上级来源页面，即当前页面的上一级页面 |
| from_url | TEXT | 会话来源页面 |
| landing_page_url | TEXT | 用户着陆页面，即广告点击或搜索结果页面 |
| 字段 | 字段类型 | 描述 |
|---|---|---|
| url_title | TEXT | 当前页面标题 |
| platform_description | TEXT | 用户信息 |
| other_params | TEXT | 扩展字段 |
| history | TEXT | 历史访问记录 |
📄 展开课件原文（第 16–22 页，共 7 页）
认识项目架构
了解项目建设基础，能够说出本项目的架构。
为了帮助读者更直观地理解教育大数据分析平台的开发流程与架构设计，本节将介绍该平台的基础架构，如图。
基于上述架构实现教育大数据分析平台的流程如下。
利用HiveQL语句对用户行为数据进行预处理，使其符合业务需求。
利用HiveQL语句对数据仓库进行分层建模。
通过编写Spark程序将MySQL数据库中存储的业务数据（用户行为数据）导入Hive数据仓库中。
根据项目需求，使用HiveQL语句对预处理后的数据开展分析，生成所需结果。
通过编写Spark程序将分析结果从Hive数据仓库迁移回MySQL数据库。
通过FineBI商业智能工具对部分数据分析的结果进行可视化展现，辅助企业决策。
任务：部署Spark
8.2
📄 展开课件原文（第 23–26 页，共 4 页）
部署Spark
了解部署Spark，能够基于Spark On YARN模式完成Spark的部署。
通常情况下，系统的业务数据存储在关系型数据库中。在使用Hive对这些业务数据进行处理之前，需要先将其导入Hive数据仓库。同样地，当Hive数据仓库中的分析结果需要提供给外部系统使用时，也需要将数据导出，这就涉及到数据迁移。
在大数据环境中，实现数据迁移的方式多种多样，例如使用数据迁移工具DataX、编写 Spark程序等。Spark是一个快速、通用的分布式计算引擎，广泛应用于大数据的处理与分析。它支持多种部署模式，包括Standalone、Spark on YARN和Spark on Kubernetes等。
在已有Hadoop基础环境中，通常会选择Spark on YARN模式进行部署。这是因为该模式能够有效提高集群的资源利用率，并充分发挥YARN在资源调度与管理方面的优势。Spark on YARN的工作原理是将Spark作为客户端，向YARN集群提交应用程序，由YARN统一调度和分配资源。因此，在该模式下，通常只需在一台服务器上安装Spark，用于提交应用程序即可，而无须在集群的每台节点上都安装Spark。
为了使Spark能够通过Thrift协议连接Hive 4.1.0版本的Metastore，从而正常获取Hive元数据并实现数据的读取与写入操作，需要使用与之兼容的Spark 4.1.0版本。截至本书编写完成时，Spark 4.1.0版本仅发布了预览版（Preview）。因此，本项目在部署Spark时，选用了Spark 4.1.0预览版作为运行环境。
接下来，将演示如何在虚拟机Node03中，基于Spark on YARN模式部署Spark，具体操作步骤如下。
▸ 课件步骤 01
上传Spark安装包
将Spark安装包spark-4.1.0-preview2-bin-hadoop3.tgz上传到虚拟机Node03的/export/software目录中。
▸ 课件步骤 02
安装Spark
将Spark安装包解压缩至虚拟机Node03的/export/servers目录。在虚拟机Node03的/export/software目录执行如下命令。
tar -zxvf spark-4.1.0-preview2-bin-hadoop3.tgz -C /export/servers上述命令执行完成后，会在虚拟机Node03的/export/servers目录中生成一个名为spark-4.1.0-preview2-bin-hadoop3的文件夹，这里将其重命名为spark-4.1.0。在虚拟机Node03执行如下命令。
mv /export/servers/spark-4.1.0-preview2-bin-hadoop3 \/export/servers/spark-4.1.0
▸ 课件步骤 03
创建配置文件spark-env.sh
通过复制模板文件spark-env.sh.template创建配置文件spark-env.sh。在虚拟机Node03的/export/servers/spark-4.1.0/conf目录中执行如下命令。
cp spark-env.sh.template spark-env.sh▸ 课件步骤 04
修改配置文件spark-env.sh
在虚拟机Node03的/export/servers/spark-4.1.0/conf目录中使用vi编辑器编辑配置文件spark-env.sh，在该文件的末尾添加如下内容。
export YARN_CONF_DIR=/export/servers/hadoop-3.4.1/etc/hadoop
export SPARK_HISTORY_OPTS="-Dspark.history.ui.port=18080
-Dspark.history.fs.logDirectory=hdfs://node01:8020/spark/history
-Dspark.history.retainedApplications=30"配置Spark历史服务，并指定历史服务端口号、日志存储目录和记录数
配置YARN的配置文件目录，确保Spark能够正确地与YARN集成和通信
▸ 课件步骤 05
创建配置文件spark-defaults.conf
cp spark-defaults.conf.template spark-defaults.conf▸ 课件步骤 06
修改配置文件spark-defaults.conf
在虚拟机Node03的/export/servers/spark-4.1.0/conf目录中使用vi编辑器编辑配置文件spark-defaults.conf，在该文件的末尾添加如下内容。
spark.eventLog.enabled true
spark.eventLog.dir hdfs://node01:8020/spark/history
spark.yarn.historyServer.address=node03:18080
spark.history.ui.port=18080开启日志记录功能
指定日志存储目录，该目录需要与配置文件spark-env.sh中指定的日志存储目录一致
关联Spark历史服务和YARN，便于在YARN WEB UI中查看Spark历史服务记录的日志信息
指定Spark历史服务的端口号
▸ 课件步骤 07
配置Spark系统环境变量
为了便于Spark的管理和使用，需要在虚拟机Node03中配置Spark系统环境变量。在虚拟机Node03中使用vi编辑器编辑系统环境变量文件profile，在文件的末尾添加如下内容。
export SPARK_HOME=/export/servers/spark-4.1.0
export PATH=$PATH:$SPARK_HOME/bin
export PATH=$PATH:$SPARK_HOME/sbin▸ 课件步骤 08
初始化系统环境变量
初始化虚拟机Node03的系统环境变量，使系统环境变量文件profile中添加的内容生效。在虚拟机Node03执行如下命令。
source /etc/profile▸ 课件步骤 09
创建日志存储目录
在HDFS中创建Spark历史服务存储日志的目录/spark/history，在虚拟机Node03中执行如下命令。
hdfs dfs -mkdir -p /spark/history▸ 课件步骤 10
启动Spark历史服务
在虚拟机Node03中启动Spark历史服务，具体命令如下。
start-history-server.sh上述命令执行完成后，会在虚拟机Node03中运行着一个名为HistoryServer的Java进程。读者可以通过在本地计算机的浏览器中输入http://192.168.121.134:18080访问Spark的历史服务。
▸ 课件步骤 11
验证Spark是否部署成功
为了验证基于Spark On YARN模式部署Spark是否成功，可以将Spark程序提交到YARN集群运行。这里使用了Spark官方提供的一个用于计算圆周率（）的Spark程序作为测试程序。确保Hadoop集群处于启动状态下，在虚拟机Node03执行如下命令。
spark-submit \
--master yarn \
--deploy-mode client \${SPARK_HOME}/examples/src/main/python/pi.py \
指定将Spark程序提交到YARN集群
指定Spark程序的运行模式为Client
指定Spark程序的参数，表示计算圆周率的迭代次数为10
▸ 课件步骤 11
上述命令执行完成后，可以在本地计算机的浏览器中输入http://192.168.121.132:8088/cluster访问YARN Web UI，查看Spark程序的运行状态。Spark程序运行成功的效果如图。
▸ 课件步骤 11
在远程登录虚拟机Node03的窗口中查看圆周率的计算结果，如图。
任务：构建数据仓库
8.3
📄 展开课件原文（第 27–45 页，共 19 页）
构建数据仓库
掌握构建数据仓库，能够熟练运用HiveQL语句构建本项目所需的数据仓库。
本项目构建的数据仓库仅包含ODS层和DW层，未设置DA层。其原因在于，数据应用层通常需要支持频繁查询以及实时或近实时的响应。如果直接依赖Hive实现，往往需要额外设计分区、物化视图等优化措施，复杂度较高。
在本项目中，业务需求主要集中在离线分析与可视化。因此，分析结果由Hive导出至MySQL，再由FineBI直接连接MySQL进行可视化展示。这样的设计既能充分利用MySQL在高效查询方面的优势，又能有效降低Hive的计算与存储压力。
为避免针对每个需求单独生成结果表而导致表数量增加、维护成本上升，本项目将分析需求归纳为访问用户量与咨询用户量两个核心指标。其中，访问用户量用于满足网站访问用户数（去重）的相关需求，咨询用户量用于满足发起有效咨询用户数（去重）的相关需求。
围绕这两个指标，在DW层的DWS层设计相应的汇总事实表，从而统一支撑按地区、来源渠道等多维度的统计分析。本项目中数据仓库的结构如图。
接下来，将演示如何在Hive中构建本项目所使用的数据仓库，具体内容如下。
在ODS层中存储的是从业务系统导入的原始数据。为了避免在表被误删除时导致数据丢失，通常会将ODS层的表创建为外部表，以确保底层数据文件仍然保留。
此外，ODS层的数据访问频率相对较低，为了节省存储空间，通常会采用压缩率较高的存储方式。本项目中，选择使用ORC文件格式并结合Zstandard压缩方式来创建外部表，以实现高效存储和良好的压缩效果。
1.构建ODS层
在Hive中构建ODS层的操作步骤如下。
▸ 课件步骤 01
创建数据库itcast_ods，用于存储ODS层的表，具体HiveQL语句如下。
CREATE DATABASE IF NOT EXISTS itcast_ods;▸ 课件步骤 02
在数据库itcast_ods中创建表web_chat_ems_ods，具体HiveQL语句如下。
点击查看
▸ 课件步骤 02
在数据库itcast_ods中创建表web_chat_text_ems_ods，具体HiveQL语句如下。
在DW层中存储的是明细数据和汇总数据，这些数据会根据业务需求不断调整和更新。为了在删除表时能够同时删除其存储的数据，从而简化管理操作，通常会将DW层的表创建为内部表。
2.构建DW层
此外，DW层的数据访问频率较高，需要在保证查询性能的同时兼顾存储效率。因此，常采用压缩与解压速度较快的存储方式。本项目中，选择使用ORC文件格式 并结合Snappy压缩方式来创建内部表，以在节省存储空间的同时有效提升查询效率。
在Hive中构建DW层的操作步骤如下。
▸ 课件步骤 01
创建数据库itcast_dwd，用于存储DWD层的表，具体HiveQL语句如下。
CREATE DATABASE IF NOT EXISTS itcast_dwd;▸ 课件步骤 02
在数据库itcast_dwd中创建明细事实表visit_consult_dwd，具体HiveQL语句如下。
▸ 课件步骤 03
创建数据库itcast_dws，用于存储DWS层的表，具体HiveQL语句如下。
CREATE DATABASE IF NOT EXISTS itcast_dws;▸ 课件步骤 04
在数据库itcast_dws中创建汇总事实表visit_dws，具体HiveQL语句如下。
▸ 课件步骤 05
在数据库itcast_dws中创建汇总事实表consult_dws，具体HiveQL语句如下。
CREATE TABLE IF NOT EXISTS itcast_dws.consult_dws(
sid_total INT COMMENT '用户数（去重）'
)
COMMENT '基于咨询用户量指标的汇总事实表'
PARTITIONED BY (yearinfo STRING, monthinfo STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');任务：采集业务数据
8.4
📄 展开课件原文（第 46–65 页，共 20 页）
采集业务数据
熟悉业务数据采集流程，能够通过编写和运行Spark程序将业务数据采集到数据仓库。
在本项目中，数据采集的主要过程是通过编写Spark程序，将MySQL数据库中存储的业务数据导入Hive数据仓库。本项目采用Python语言编写Spark程序来实现数据采集。
因此，在实现数据采集之前，需要在本地计算机中准备Python开发环境，包括安装 Python（版本为3.9.13）和Python集成开发工具PyCharm。
接下来，将重点介绍如何在PyCharm中编写Spark程序，以实现数据采集，具体操作步骤如下。
▸ 课件步骤 01
打开Pycharm，进入Welcome to Pycharm界面，如图。
单击“New Project”按钮
▸ 课件步骤 02
打开“New Project”对话框，在该对话框中配置项目的基本信息，具体内容如下。
在“Interpreter type”区域选择“Custom environment”选项使用自定义环境。
在“Name”输入框中指定项目名称为hive_project。
在“Location”输入框中指定项目的存储路径为D:\Project\PycharmProject。
在“Base python”下拉框中选择本地安装的Python 3.9.13。
▸ 课件步骤 02
“New Project”对话框配置完成的效果如图。
单击“Create”按钮
▸ 课件步骤 03
创建项目hive_project。项目hive_project创建完成的效果如图。
▸ 课件步骤 04
在PyCharm中，使用快捷键“Ctrl+Alt+S”打开“Settings”对话框。在该对话框中，展开“Project:hive_project”并选择“Python Interpreter”选项，如图。
单击按钮打开Available Packages对话框
▸ 课件步骤 05
首先，在输入框中输入pyspark。接着，选择名为pyspark的包。
然后，勾选“Specify version”复选框。
最后，在“Specify version”复选框后方的下拉框中选择pyspark包的版本为4.1.0 dev1。
▸ 课件步骤 05
“Available Package”对话框配置完成的效果如图。
单击“Install Package”按钮安装pyspark包
▸ 课件步骤 06
当出现“Package 'pyspark' Installed successfully”的提示信息时，说明成功安装pyspark包。
▸ 课件步骤 07
在项目hive_project中创建一个名为mysql_to_hive的Python文件，在该文件中实现Spark程序，用于将MySQL数据库中存储的业务数据导入Hive数据仓库，具体代码如文件。
点击查看
▸ 课件步骤 08
为了便于在后续步骤中，在虚拟机Node03中使用spark-submit命令将mysql_to_hive.py文件中编写的Spark程序提交到YARN集群运行，此处将该文件上传到虚拟机Node03的/export/data目录中。
▸ 课件步骤 09
SQL脚本文件nev.sql包含本项目所需的业务数据。需要在MySQL中执行该脚本文件，以创建用户会话信息表web_chat_ems_2025_07和用户会话信息附属表web_chat_text_ems_2025_07，并向表中导入相应的业务数据。首先将SQL脚本文件nev.sql上传到虚拟机Node03的/export/data目录中。然后使用root用户登录MySQL，在MySQL命令行界面执行如下命令。
source /export/data/nev.sql▸ 课件步骤 10
将MySQL数据库nev中表web_chat_ems_2025_07的数据导入到Hive数据库itcast_ods中的表web_chat_ems_ods，在虚拟机Node03执行如下命令。
▸ 课件步骤 11
将MySQL数据库nev中表web_chat_text_ems_2025_07的数据导入到Hive数据库itcast_ods中的表web_chat_text_ems_ods，在虚拟机Node03执行如下命令。
任务：预处理业务数据
8.5
📄 展开课件原文（第 66–81 页，共 16 页）
预处理业务数据
掌握业务数据预处理方法，能够使用HiveQL语句完成数据关联、字段转换、空值处理等操作。
在本项目中，业务数据预处理的主要过程是将web_chat_ems_ods和web_chat_text_ems_ods两张表中的数据进行合并，并将合并后的结果存储到明细事实表visit_consult_dwd中。
在数据合并过程中，还需根据业务需求对原始数据进行转换，具体规则如下。
将表web_chat_ems_ods中字段create_time的值转换为Unix时间戳，以便在数据计算与时间范围筛选中实现更高效的处理。
从表web_chat_ems_ods中的字段create_time提取年和月，用于作为明细事实表visit_consult_dwd的分区字段取值，以便后续按年、月进行分区裁剪与查询优化。
将表web_chat_ems_ods中字段msg_count的NULL替换为0，以便在后续分析中准确判断用户是否发起了有效咨询。
过滤表web_chat_ems_ods的字段area中值的开头不是中国的数据，仅保留中国地区的数据。
接下来，将依据上述需求在Hive中完成本项目的数据转换操作，具体HiveQL语句如下。
点击查看
任务：分析业务数据
8.6
📄 展开课件原文（第 82–86 页，共 5 页）
8.6 分析业务数据
分析业务数据（任务导学）
数据分析是一种从数据中提取有价值信息的方法，能够帮助我们深入理解数据背后的规律与趋势。在本项目中，数据分析的主要过程是根据项目需求，使用HiveQL语句对明细事实表visit_consult_dwd中的数据进行统计分析，并将分析结果存储在相应的汇总事实表中，为后续的可视化展示提供数据支撑。
📄 展开课件原文（第 87–87 页，共 1 页）
访问用户量统计
掌握业务数据分析方法，能使用HiveQL语句进行访问用户量统计的操作。
通过查询明细事实表visit_consult_dwd的数据，统计不同地区的访问用户量，并将结果插入到汇总事实表visit_dws的相应分区中，具体HiveQL语句如下。
点击查看
上述语句执行完成后，可查询汇总事实表visit_dws，获取2025年7月访问用户量排名前六的地区，具体HiveQL语句如下。
SELECT sid_total,provinceFROM itcast_dws.visit_dws
WHERE yearinfo='2025' AND monthinfo='07' AND grouptype=1
ORDER BY sid_total DESC LIMIT 6;上述语句执行完成后的效果如图。
📄 展开课件原文（第 88–91 页，共 4 页）
会话页面统计
掌握业务数据分析方法，能够使用HiveQL语句进行会话页面统计的操作。
通过查询明细事实表visit_consult_dwd的数据，统计不同用户在各页面发起会话的频次，并将结果插入到汇总事实表visit_dws的相应分区中，具体HiveQL语句如下。
点击查看
上述语句执行完成后，可查询汇总事实表visit_dws，获取2025年7月发起会话次数排名前三的页面，具体HiveQL语句如下。
SELECT sid_total,from_urlFROM itcast_dws.visit_dws
WHERE yearinfo='2025' AND monthinfo='07'
AND grouptype=3 AND from_url IS NOT NULL
ORDER BY sid_total DESC LIMIT 3;上述语句执行完成后的效果如图。
📄 展开课件原文（第 92–95 页，共 4 页）
总访问用户量统计
掌握业务数据分析方法，能够使用HiveQL语句进行总访问用户量统计的操作。
通过查询明细事实表visit_consult_dwd的数据，统计网站的整体访问用户量，并将结果插入到汇总事实表visit_dws的相应分区中，具体HiveQL语句如下。
点击查看
上述语句执行完成后，可查询汇总事实表visit_dws，获取2025年7月的总访问用户量，具体HiveQL语句如下。
SELECT sid_total
FROM itcast_dws.visit_dws
WHERE yearinfo='2025' AND monthinfo='07' AND grouptype='4';上述语句执行完成后的效果如图。
📄 展开课件原文（第 96–99 页，共 4 页）
来源渠道访问用户量统计
掌握业务数据分析方法，能够使用HiveQL语句进行来源渠道访问用户量统计的操作。
通过查询明细事实表visit_consult_dwd，统计不同来源渠道的访问用户量，并将结果插入到汇总事实表visit_dws的相应分区中，具体HiveQL语句如下。
点击查看
上述语句执行完成后，可查询汇总事实表visit_dws，获取2025年7月访问用户量排名前三的来源渠道，具体HiveQL语句如下。
SELECT sid_total,origin_channelFROM itcast_dws.visit_dws
WHERE yearinfo='2025' AND monthinfo='07'AND grouptype=2 AND origin_channel != '未知'
ORDER BY sid_total DESC LIMIT 3;上述语句执行完成后的效果如图。
📄 展开课件原文（第 100–103 页，共 4 页）
咨询率统计
掌握业务数据分析方法，能够使用HiveQL语句进行咨询率统计的操作。
实现咨询率统计的操作步骤如下。
通过查询明细事实表visit_consult_dwd，统计发起有效咨询的用户数量，并将结果插入到汇总事实表consult_dws的相应分区中，具体HiveQL语句如下。
▸ 课件步骤 01
INSERT INTO itcast_dws.consult_dws PARTITION (yearinfo,monthinfo)SELECT
count(DISTINCT sid) sid_total,yearinfo,monthinfo
FROM itcast_dwd.visit_consult_dwd
WHERE msg_count >= 1
GROUP BY yearinfo,monthinfo;根据访问用户量和咨询用户量计算网站的咨询率，具体HiveQL语句如下。
▸ 课件步骤 02
点击查看
上述语句执行完成后的效果如图。
▸ 课件步骤 02
任务：实现数据可视化
8.7
📄 展开课件原文（第 104–109 页，共 6 页）
8.7 实现数据可视化
实现数据可视化（任务导学）
在我们的日常生活中，数据无处不在。从社交媒体的互动记录，到企业的经营数据，再到政府的统计报告，数据已经成为我们生活的一部分。然而，理解和解释这些数据并不总是那么容易。因此，数据可视化变得非常重要。数据可视化是一种将复杂数据转换为直观和易于解释的视觉形式的方法，它通过利用图形、图表和其他可视化元素来呈现数据的模式、趋势和关系。本任务将详细介绍如何使用FineBI对部分数据分析的结果进行可视化展示。
📄 展开课件原文（第 110–110 页，共 1 页）
导出分析结果
了解数据可视化，能够完成将数据分析结果导出到MySQL的操作。
在本项目中，主要对访问用户量统计、会话页面统计和来源渠道访问用户量统计三个分析结果进行可视化展示。因此，为了便于FineBI连接和展示，需要通过Spark程序将Hive中汇总事实表visit_dws的分析结果导出到MySQL数据库。
在MySQL的数据库nev中创建与Hive数据库itcast_dws中汇总事实表visit_dws结构和名称相同的表。在MySQL命令行界面执行如下命令。
▸ 课件步骤 01
点击查看
在项目hive_project中创建一个名为hive_to_mysql的Python文件，在该文件中实现Spark程序，用于将Hive数据仓库中存储的分析结果导出到MySQL数据库中，具体代码如文件。
▸ 课件步骤 02
为了便于在后续步骤中，在虚拟机Node03中使用spark-submit命令将hive_to_mysql.py文件中编写的Spark程序提交到YARN集群运行，此处将该文件上传到虚拟机Node03的/export/data目录中。
▸ 课件步骤 03
将Hive数据库itcast_dws中表visit_dws的数据导出到MySQL数据库nev中的表visit_dws，在虚拟机Node03执行如下命令。
▸ 课件步骤 04
📄 展开课件原文（第 111–116 页，共 6 页）
配置数据集
了解数据可视化，能够完成配置数据集的操作。
FineBI是一款国内知名的商业智能（BI，Business Intelligence）工具，主要应用于企业级数据分析和决策支持。该工具提供丰富的图表与报表设计功能，能够满足企业不同层级、不同角色的数据可视化需求。
在使用FineBI之前，需要在本地计算机上完成软件的安装与基本配置。有关安装与配置的详细步骤，读者可参考本书配套资源中提供的补充文档。完成FineBI的安装与配置后，需要在FineBI中配置数据集，以便从指定数据源中读取用于可视化展示的数据。
在FineBI中配置数据集的操作主要包括配置数据连接和新建数据集两部分，具体介绍如下。
新建数据集
配置数据连接
在FineBI中配置数据连接的目的是指定获取所需数据的数据源。接下来，将演示如何在FineBI中配置数据连接，以连接虚拟机Node03上的MySQL数据库，具体操作步骤如下。
1.配置数据连接
▸ 课件步骤 01
在FineBI平台的主界面，依次单击“管理系统”→“数据连接”→“数据连接管理”选项配置FineBI的数据连接，如图。
▸ 课件步骤 02
在“配置FineBI的数据连接”界面，单击“新建数据连接”按钮，如图。
▸ 课件步骤 03
单击“MySQL”选项选择使用MySQL作为数据源，并且设置数据连接的相关信息，如图。
定义数据连接的名称为教育大数据分析平台
指定存储业务数据的数据库nev
指定MySQL的IP地址为192.168.121.134
指定用户itcast的密码为Itcast@2025
▸ 课件步骤 04
单击“测试连接”按钮测试名为教育大数据分析平台的数据连接是否可以连接MySQL，如图。
新建数据集的目的是在FineBI中获取要进行可视化展示的数据。在本项目中，需要从MySQL数据库nev的表visit_dws中读取数据，以获取访问用户量统计、会话页面统计和来源渠道访问用户量统计三个分析结果，具体操作步骤如下。
2.新建数据集
▸ 课件步骤 01
在FineBI平台的主界面单击“公共数据”选项进入公共数据界面，如图。
▸ 课件步骤 02
单击“新建文件夹”按钮创建用于存放本项目相关数据的文件夹，并将该文件夹重命名为教育大数据分析平台，如图。
▸ 课件步骤 03
将鼠标移动至名为教育大数据分析平台的文件夹，单击该文件夹后方显示的按钮，在弹出的菜单选择“数据库表”选项，表示通过数据库中的表获取数据，如图。
▸ 课件步骤 04
进入到数据库选表界面，在该界面中选择名为教育大数据分析平台的数据连接，并选中该数据连接中的表visit_dws，如图。
▸ 课件步骤 05
单击“确定”按钮返回公共数据界面，如图。
▸ 课件步骤 06
名为教育大数据分析平台的文件夹中添加的表并不会立即从MySQL中相应的表获取数据，用户需要进行手动的数据更新操作。在“公共数据界面”，单击“更新数据”按钮，如图 。
📷 展开本知识点其余 5 张课件截图
📄 展开课件原文（第 117–132 页，共 16 页）
实现访问用户量统计可视化
了解数据可视化，能够实现访问用户量统计可视化的操作。
利用FineBI中的区域地图对访问用户量统计的分析结果进行可视化展示，具体实现过程如下。
分析主题是FineBI中用于进行数据分析与可视化的容器，所有关于数据分析和可视化的操作均在分析主题中进行。接下来，将演示如何在FineBI中创建分析主题，用于展示访问用户量统计的分析结果，具体操作步骤如下。
1.创建分析主题
▸ 课件步骤 01
在FineBI平台的主界面单击“我的分析”选项进入我的分析界面，如图。
▸ 课件步骤 02
将鼠标移动至“全部分析”选项，单击该选项后方显示的 按钮，在弹出的菜单选择“文件夹”选项，创建用于存放本项目相关分析主题的文件夹，并重命名为教育大数据分析平台，如图。
▸ 课件步骤 03
将鼠标移动至名为教育大数据分析平台的文件夹，单击该文件夹后方显示的 按钮，在弹出的菜单选择“分析主题”选项，在名为教育大数据分析平台的文件夹中新建分析主题。此时，浏览器会跳转到“分析主题”窗口，并弹出“选择数据”对话框，如图。
▸ 课件步骤 04
单击名为教育大数据分析平台的文件夹，并选择名为教育大数据分析平台_visit_dws的表，指定当前分析主题使用的数据，如图。
▸ 课件步骤 05
单击“确定”按钮返回“分析主题”窗口，如图。
▸ 课件步骤 06
单击分析主题后方的 按钮，在弹出的菜单选择“重命名”选项，将分析主题的名称修改为访问用户量统计可视化。此时，当前窗口的名称也会修改为访问用户量统计可视化，如图。
由于表visit_dws中包含多个维度的分析结果，为了简化当前分析主题所使用的数据，便于后续进行图表设计，在开展访问用户量统计的可视化展示之前，需要对当前主题的数据进行预处理，过滤掉与地区维度分析结果无关的数据，具体操作步骤如下。
2.数据预处理
▸ 课件步骤 01
在“访问用户量统计可视化”窗口，单击“更多”按钮，在弹出的菜单中选择“过滤”选项，如图。
▸ 课件步骤 02
在“过滤数据”界面，单击“添加条件(且)”选项。然后，单击出现的“请选择字段”选项，在弹出的菜单中选择字段province，如图。
▸ 课件步骤 03
在“添加条件”界面，依次单击默认值为属于和无限制的下拉框，在弹出的菜单中，分别选择“不属于”选项和勾选“-1”复选框，表示过滤字段province中值等于-1的数据，仅保留地区维度的分析结果，如图。
▸ 课件步骤 04
在“添加条件”界面，单击“更多”按钮，在弹出的菜单中选择“字段设置”选项。然后，仅勾选字段sid_total和province的复选框，如图。
在分析主题中，通过添加不同类型的图表组件来对数据进行可视化。在实现访问用户量统计的可视化时，需要在名为访问用户量统计可视化的分析主题中添加一个类型为区域地图的图表组件，通过该组件实现访问用户量统计的可视化。
3.添加组件
在“字段设置”界面，单击“组件”选项卡，在该选项卡中选择“图表类型”部分的“区域地图”选项，如图。
在分析主题中使用区域地图的图表组件时，传递给区域地图的数据必须是地理角色的经纬度，而访问用户量统计的分析结果中，存储的是地理角色的名称。因此，需要将地理角色的名称转换为经纬度，具体操作步骤如下。
4. 将地理角色的名称转换为经纬度
在“添加类型为区域地图的图表组件”界面，选择字段province，并单击该字段后方显示的按钮，在弹出的菜单中依次选择“地理角色（无）”→“省/市/自治区”选项，如图。
▸ 课件步骤 01
在“将地理角色的名称转换为经纬度”界面的操作后，进入到“地理角色（省/市/自治区）”对话框，在该对话框中FineBI会根据字段province中地理角色的名称与所有省级行政区的名称进行匹配，并自动为匹配成功的地理角色生成经纬度，如图
▸ 课件步骤 02
在“地理角色（省/市/自治区）”对话框界面，单击“请选择匹配项”选项，在弹出的菜单中选择“北京市”选项，将名为中国的地理角色匹配为首都北京，如图。
▸ 课件步骤 03
在“地理角色（省/市/自治区）对话框”单击“确定”按钮将地理角色的名称转换为经纬度，如图。
▸ 课件步骤 04
在分析主题中，配置组件的作用是将数据填充到相应的图表组件中，并修改图表组件的样式。在名为访问用户量统计可视化的分析主题中，配置类型为区域地图的图表组件的操作步骤如下。
5. 配置组件
在“将地理角色的名称转换为经纬度”界面，首先分别将标注经度和纬度的临时字段province拖动到“横轴”和“纵轴”输入框。然后，将字段sid_total拖动到“颜色”输入框，如图。
▸ 课件步骤 01
📷 展开本知识点其余 12 张课件截图
📄 展开课件原文（第 133–155 页，共 23 页）
实现会话页面统计可视化
了解数据可视化，能够实现访问会话页面统计可视化的操作。
利用FineBI中的多系列柱状图对会话页面统计的分析结果进行可视化展示，具体实现过程如下。
为了区分不同分析结果的可视化效果，这里创建一个新的分析主题，用于展示会话页面统计的分析结果，具体操作步骤如下。
1.创建分析主题
在我的分析界面中，将鼠标移动至名为教育大数据分析平台的文件夹，单击该文件夹后方显示的 按钮，在弹出的菜单选择“分析主题”选项，在名为教育大数据分析平台的文件夹新建分析主题。此时，浏览器会跳转到“分析主题”窗口，并弹出“选择数据”对话框，在该对话框中单击名为教育大数据分析平台的文件夹，并选择名为教育大数据分析平台_visit_dws的表，指定当前分析主题使用的数据。
▸ 课件步骤 01
为了区分不同分析主题的作用，将当前分析主题重命名为会话页面统计可视化，如图。
▸ 课件步骤 02
由于表visit_dws中包含多个维度的分析结果，为了简化当前分析主题所使用的数据，便于后续进行图表设计，在开展会话页面统计的可视化展示之前，需要对当前主题的数据进行预处理，过滤掉与会话来源页面维度分析结果无关的数据，具体操作步骤如下。
2.数据预处理
在“会话页面统计可视化窗口”界面，单击“更多”按钮，在弹出的菜单中选择“过滤”选项，如图。
▸ 课件步骤 01
在“过滤数据”界面，单击“添加条件(且)”选项。然后，单击出现的“请选择字段”选项，在弹出的菜单中选择字段from_url，如图。
▸ 课件步骤 02
在“添加条件”界面，依次单击默认值为属于和无限制的下拉框，在弹出的菜单中，分别选择“不属于”选项，以及勾选“-1”和“ ”复选框，表示过滤字段from_url中值等于-1和空的数据，仅保留会话来源页面维度的分析结果，如图。
▸ 课件步骤 03
在“添加条件”界面，单击“更多”按钮，在弹出的菜单中选择“字段设置”选项。然后，仅勾选字段sid_total和from_url的复选框，如图。
▸ 课件步骤 04
在实现会话页面统计的可视化时，需要在名为会话页面统计可视化的分析主题中添加一个类型为多系列柱状图的图表组件，通过该组件实现会话页面统计的可视化。
3.添加组件
在“添加类型为多系列柱状图的图表组件”界面，单击“组件”选项卡，在该选项卡中选择“图表类型”部分的“多系列柱状图”选项，如图。
在名为会话页面统计可视化的分析主题中，配置类型为多系列柱状图的图表组件的操作步骤如下。
4.配置组件
分别将字段from_url和sid_total拖动到“横轴”和“纵轴”输入框，如图。
▸ 课件步骤 01
在“配置类型为多系列柱状图的图表组件”界面可以看出，默认情况下，所有页面名称均显示在多系列柱状图的横轴上，这种展示方式不便于对数据进行直观观察与比较。在“配置类型为多系列柱状图的图表组件”界面中，单击“组件样式”选项卡，在该选项卡中选中“自适应显示”折叠框中的“标准适应”选项，如图。
▸ 课件步骤 02
为便于按照一定规律观察各页面的会话发起次数，对多系列柱状图中横轴显示的页面名称按发起会话次数由高到低的顺序进行排列，使数据分布更加直观。在“配置类型为多系列柱状图的图表组件”界面中，将鼠标移动至“横轴”输入框中的字段from_url，单击该字段后方显示的按钮，在弹出的菜单中依次选择“降序”→“sid_total(求和)”选项，如图。
▸ 课件步骤 03
📷 展开本知识点其余 5 张课件截图
📄 展开课件原文（第 156–171 页，共 16 页）
实现来源渠道访问用户量统计可视化
了解数据可视化，能够实现来源渠道访问用户量统计可视化的操作。
利用FineBI中的饼图对来源渠道访问用户量统计的分析结果进行可视化展示，具体实现过程如下。
为了区分不同分析结果的可视化效果，这里创建一个新的分析主题，用于展示来源渠道访问用户量统计的分析结果，具体操作步骤如下。
1.创建分析主题
在我的分析界面中，将鼠标移动至名为教育大数据分析平台的文件夹，单击该文件夹后方显示的 按钮，在弹出的菜单选择“分析主题”选项，在名为教育大数据分析平台的文件夹新建分析主题。此时，浏览器会跳转到“分析主题”窗口，并弹出“选择数据”对话框，在该对话框中单击名为教育大数据分析平台的文件夹，并选择名为教育大数据分析平台_visit_dws的表，指定当前分析主题使用的数据。
▸ 课件步骤 01
为了区分不同分析主题的作用，将当前分析主题重命名为来源渠道访问用户量统计可视化，如图。
▸ 课件步骤 02
由于表visit_dws中包含多个维度的分析结果，为了简化当前分析主题所使用的数据，便于后续进行图表设计，在开展来源渠道访问用户量统计的可视化展示之前，需要对当前主题的数据进行预处理，过滤掉与来源渠道维度分析结果无关的数据，具体操作步骤如下。
2.数据预处理
在“来源渠道访问用户量统计可视化”窗口界面，单击“更多”按钮，在弹出的菜单中选择“过滤”选项，如图。
▸ 课件步骤 01
在“过滤数据”界面中，单击“添加条件(且)”选项。然后，单击出现的“请选择字段”选项，在弹出的菜单中选择字段origin_channel，如图。
▸ 课件步骤 02
在“添加条件”界面，依次单击默认值为属于和无限制的下拉框，在弹出的菜单中，分别选择“不属于”选项，以及勾选“-1”和“未知”复选框，表示过滤字段origin_channel中值等于-1和未知的数据，仅保留来源渠道维度的分析结果，如图。
▸ 课件步骤 03
在“添加条件”界面，单击“更多”按钮，在弹出的菜单中选择“字段设置”选项。然后，仅勾选字段sid_total和origin_channel的复选框，如图。
▸ 课件步骤 04
在实现来源渠道访问用户量统计的可视化时，需要在名为来源渠道访问用户量统计可视化的分析主题中添加一个类型为饼图的图表组件，通过该组件实现来源渠道访问用户量统计的可视化。
3.添加组件
在“字段设置”界面，单击“组件”选项卡，在该选项卡中选择“图表类型”部分的“饼图”选项，如图。
在名为来源渠道访问用户量统计可视化的分析主题中，配置类型为饼图的图表组件的操作步骤如下。
4.配置组件
在“添加类型为饼图的图表组件”界面，首先将字段origin_channel拖动到“颜色”输入框。然后，将字段sid_total分别拖入到“角度”和“细粒度” 输入框，如图。
▸ 课件步骤 01
在“配置类型为饼图的图表组件”界面，将鼠标移动至“角度”输入框中的字段sid_total，单击该字段后方显示的按钮，在弹出的菜单中依次选择“快速计算(无)”→“占比”选项，如图。
▸ 课件步骤 02
AI助学
学完本单元后，读者已经初步了解教育大数据分析平台的构建流程。然而，在项目实施过程中，仍可能难以判断数据仓库分层是否合理、数据采集与预处理步骤是否衔接正确、统计指标口径是否一致等。此时，可借助AI对项目实施过程进行辅助分析，帮助梳理关键环节，检查可能存在的问题，并提出改进建议。
AI
助
学
单元小结
本单元主要讲解了综合项目——搭建教育大数据分析平台的实现过程。首先，介绍了项目建设基础，包括项目背景、需求分析、原始数据结构和项目架构。接着，介绍了部署Spark。然后，介绍了构建数据仓库、采集业务数据、预处理业务数据和分析业务数据的相关操作。最后，介绍了数据可视化的实现过程，包括导出分析结果、配置数据集、实现访问用户量统计可视化等。通过本单元的学习，读者能够系统掌握基于Hive的数据仓库构建与分析流程，并能够综合运用Spark、Hive与FineBI等工具完成从数据采集到可视化展示的完整实现，具备开展实际大数据项目开发的基本能力。
单
元
总
结
📷 展开本知识点其余 4 张课件截图
📄 展开课件原文（第 172–188 页，共 17 页）
🧪 动手做一遍 项目最小闭环：从 MySQL 业务库到可视化看板
预计用时约 60 分钟 · 每一步都给出可执行语句与预期结果，建议边看边在 Beeline / DataGrip 里敲一遍
本实例步骤
- 01 ODS 层：原样接住业务数据
- 02 DWD 层：清洗 + 合并成明细事实表
- 03 DWS 层：预聚合成指标
- 04 ADS 层：五个分析指标逐个算
- 05 导出到 MySQL，给可视化工具用
- 06 DataEase 出图：把指标变成看板
01ODS 层：原样接住业务数据
贴源存放，不做任何加工，方便随时回溯。
CREATE DATABASE IF NOT EXISTS itcast_ods;
CREATE TABLE IF NOT EXISTS itcast_ods.web_chat_ems_ods(
id INT,
create_date_time STRING,
session_id STRING,
sid STRING,
create_time STRING,
seo_source STRING,
area STRING,
origin_channel STRING,
msg_count INT
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC;
-- 用 Sqoop 把 MySQL 的 web_chat_ems_2025_07 全量搬过来
sqoop import --connect jdbc:mysql://node03:3306/nev --username root --password 123456 \
--table web_chat_ems_2025_07 \
--hive-import --hive-database itcast_ods --hive-table web_chat_ems_ods \
--fields-terminated-by '\t' -m 1| 层次 | 存放内容 | 加工程度 |
|---|---|---|
| ODS | 业务库原样数据 | 不加工（可加分区与清洗标记） |
| DWD | 结构规范、质量可靠的明细 | 清洗、类型转换、字段合并 |
| DWS | 按维度汇总的指标 | 预聚合 |
| ADS/DA | 面向报表与看板的结果 | 贴合展示口径 |
💡 ODS 表建议加日期分区，方便按天重跑；字段类型先全部用 STRING 接住，到 DWD 层再转型。
02DWD 层：清洗 + 合并成明细事实表
把两张 ODS 表合并成一张宽明细表，并处理空值、时间与地区问题。
CREATE DATABASE IF NOT EXISTS itcast_dwd;
CREATE TABLE IF NOT EXISTS itcast_dwd.visit_consult_dwd(
session_id STRING,
sid STRING,
create_time BIGINT,
seo_source STRING,
area STRING,
origin_channel STRING,
msg_count INT,
consult_count INT
)
PARTITIONED BY (yearinfo STRING, monthinfo STRING)
STORED AS ORC;
INSERT OVERWRITE TABLE itcast_dwd.visit_consult_dwd PARTITION (yearinfo, monthinfo)
SELECT e.session_id,
e.sid,
unix_timestamp(e.create_time) AS create_time, -- 时间转时间戳
e.seo_source,
e.area,
e.origin_channel,
nvl(e.msg_count, 0) AS msg_count, -- NULL → 0
nvl(t.consult_count, 0) AS consult_count, -- 两表合并
year(e.create_time) AS yearinfo, -- 分区字段
month(e.create_time) AS monthinfo
FROM itcast_ods.web_chat_ems_ods e
LEFT JOIN itcast_ods.web_chat_text_ems_ods t ON e.id = t.id
WHERE e.area LIKE '中国%'; -- 过滤脏数据03DWS 层：预聚合成指标
按“地区 / 月份 / 渠道”等分析维度预先汇总，上层查询直接读结果。
CREATE DATABASE IF NOT EXISTS itcast_dws;
CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws(
sid STRING,
session_cnt INT,
visit_cnt INT,
consult_cnt INT
)
PARTITIONED BY (yearinfo STRING, monthinfo STRING, area STRING, origin_channel STRING)
STORED AS ORC;
-- 例：按“年/月/地区/渠道”汇总访问量、会话数、咨询量
INSERT INTO itcast_dws.visit_dws PARTITION (yearinfo, monthinfo, area, origin_channel)
SELECT concat_ws('-', yearinfo, monthinfo, area, origin_channel) AS sid,
count(DISTINCT session_id) AS session_cnt,
count(*) AS visit_cnt,
sum(consult_count) AS consult_cnt,
yearinfo, monthinfo, area, origin_channel
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo, monthinfo, area, origin_channel;💡 DWS 的粒度决定上层能查什么：维度越多、粒度越细，灵活性越高但数据量也越大。
04ADS 层：五个分析指标逐个算
项目要求交付的五类统计，全部用 HiveQL 完成。
-- ① 各地区访问用户量 Top6
SELECT area, count(DISTINCT sid) AS user_cnt
FROM itcast_dwd.visit_consult_dwd
WHERE yearinfo = '2025' AND monthinfo = '07'
GROUP BY area ORDER BY user_cnt DESC LIMIT 6;
-- ② 会话页面 Top3（按会话次数）
SELECT sid, count(DISTINCT session_id) AS session_cnt
FROM itcast_dwd.visit_consult_dwd
WHERE yearinfo = '2025' AND monthinfo = '07'
GROUP BY sid ORDER BY session_cnt DESC LIMIT 3;
-- ③ 总访问用户量
SELECT count(DISTINCT sid) AS total_user FROM itcast_dwd.visit_consult_dwd
WHERE yearinfo = '2025' AND monthinfo = '07';
-- ④ 来源渠道访问用户量
SELECT origin_channel, count(DISTINCT sid) AS user_cnt
FROM itcast_dwd.visit_consult_dwd
WHERE yearinfo = '2025' AND monthinfo = '07'
GROUP BY origin_channel ORDER BY user_cnt DESC;
-- ⑤ 咨询率 = 发起有效咨询的会话数 / 总会话数
SELECT round(sum(IF(consult_count > 0, 1, 0)) / count(DISTINCT session_id), 4) AS consult_rate
FROM itcast_dwd.visit_consult_dwd
WHERE yearinfo = '2025' AND monthinfo = '07';| 指标 | 用到的 SQL 能力 |
|---|---|
| 地区访问用户量 | GROUP BY + count(DISTINCT) + ORDER BY + LIMIT |
| 会话页面统计 | 同上，去重维度换成 session_id |
| 总访问用户量 | 单值聚合 |
| 来源渠道 | GROUP BY 分组维度不同 |
| 咨询率 | 条件聚合 IF + 除法 + round |
visit_dws（宽表）与 consult_dws（咨询汇总）两张表里，便于 DataEase 直接取数。05导出到 MySQL，给可视化工具用
DataEase 连 MySQL 比连 Hive 更顺畅，所以把结果表同步过去。
-- 1) MySQL 端先建同结构的接收表
CREATE TABLE nev.visit_dws(
sid VARCHAR(200), session_cnt INT, visit_cnt INT, consult_cnt INT,
yearinfo VARCHAR(10), monthinfo VARCHAR(10), area VARCHAR(50), origin_channel VARCHAR(50)
);
-- 2) Hive → MySQL（Sqoop 导出）
sqoop export --connect jdbc:mysql://node03:3306/nev --username root --password 123456 \
--table visit_dws \
--export-dir /user/hive/warehouse/itcast_dws.db/visit_dws \
--input-fields-terminated-by '\t'
-- 3) 检查
SELECT * FROM nev.visit_dws LIMIT 5;06DataEase 出图：把指标变成看板
数据集 → 视图 → 仪表板，三步做出可交付的可视化。
1) 数据源：选择 MySQL，填 node03:3306 / nev 库
2) 数据集：新建 SQL 数据集，例如
SELECT area, SUM(visit_cnt) AS visit_cnt
FROM visit_dws WHERE yearinfo='2025' AND monthinfo='07'
GROUP BY area ORDER BY visit_cnt DESC LIMIT 6;
3) 视图：把数据集拖到画布，地图/柱状图展示“地区访问用户量”
4) 仪表板：新建仪表板 → 添加多个视图 → 选主题（如“智慧数据”）→ 保存发布| 视图 | 图表类型 | 数据来源 |
|---|---|---|
| 地区访问用户量 Top6 | 地图 / 柱状图 | 数据集① |
| 会话页面 Top3 | 条形图 | 数据集② |
| 总访问用户量 | 指标卡 | 数据集③ |
| 来源渠道分布 | 饼图 / 环形图 | 数据集④ |
| 咨询率 | 指标卡 / 仪表盘 | 数据集⑤ |
💡 仪表板做好后可以设置定时刷新；教学演示时把 LIMIT 调小、只取 7 月数据，出图更快。