基于 Hive 数据仓库的教育大数据分析平台（伪分布式）

一、Hive 部署

（一）Linux 环境搭建

一、虚拟机的安装

VMware (Virtual Machine ware ）是一个“虚拟 PC ”软件公司，提供服务器、桌

面虚拟化的解决方案。

使用的虚拟软件： VMware15

二、Linux 系统安装

1. 环境准备

VMware15.5 pro

Centos7 镜像文件 (mini)

2. 虚拟机安装

2.1 新建虚拟机，选择自定义

2.2  选择下一步安装程序光盘映像文件（ iso ）

2.3 选择稍后安装操作系统

2.4 选择Liunx（L）版本Centos7

2.5 选择虚拟机的名称和位置

2.6配置处理器的数量和每个处理器的内核数量

2.7配置虚拟机的内存

2.8配置网络类型

2.9选择 I/OK控制器类型

2.10选择 虚拟磁盘类型

2.11选择磁盘

2.12配置磁盘大小

2.13配置系统

 

2.14 完成配置开启虚拟机

三、Centos 系统安装

1. 开启虚拟机

2. 安装 CentOS7

3. 选择语言（默认为 English ）

4. 日期和时间亚洲 / 上海（这里默认为最小）

5. 安装位置

6. 系统网络

7. 开始安装

8. 设置 ROOT 密码

9. 安装完成后重启即可

10. 使用 root 权限登录

四、静态网络配置

1.查看网络是否连通

ping www.baidu.com

2. 安装 net-tools

yum upgrade
yum install net-tools

查看ip地址和网卡

ifconfip

3. 查看 Mac 地址

4. 查看 ip 地址的起始和结束地址

5. 修改网络配置文件

vi /etc/sysconfig/network-scripts/ifcfg-ens33

修改BOOTPROTO为

BOOTPROTO="static"

修改ONBOOT为

ONBOOT="yes"

mac 地址为 2 步骤的 enter 值 2

ip 地址参照 4 步骤自行选择（必须在起始和结束的范围内）

子网掩码默认设置为 255.255.255.0

网关的值为将 ip 地址中最后一段的值改为 2

DNS 使用谷歌提供的免费 dns1 ： 8.8.8.8

6. 重启网络服务，查看是否配置成功

systemctl restart network
ping www.baidu.com

7.重启虚拟机后，查看是否连通网络（ip 地址并未改变，且能连通网络）

reboot
ifconfig
ping www.baidu.com

五、虚拟配置

（一）X-Shell远程连接Linux

1.新建会话连接

2.设置名称主机

3.设置用户身份认证（Linux的账号 密码 ）

4.连接成功

（二）Hadoop 的部署

1. 在所有虚拟机根目录下新建文件夹 export ， export 文件夹中新建 data 、 servers

和 software 文件

mkdir -p /export/data
mkdir -p /export/servers
mkdir -p /export/software

2. 准备安装包

hadoop-2.7.4.tar.gz

jdk-8u161-linux-x64.tar.gz

3. 在 Xshell 先进入 software 文件内，然后下载 rz 命令，并使用 rz 命令进行文件

上传，此时会弹出上传的窗口，选择要上传的文件，点击确定即可将本地文件上

传到 Linux 上。

cd /export/software
yum -y install lrzsz
rz

4.ls查看安装包是否导入

5.安装 JDK（所有虚拟机都要操作）

5.1 解压 jdk

cd /export/software
tar -zxvf jdk-8u161-linux-x64.tar.gz -C /export/servers/

5.2 重命名 jdk 目录

cd /export/servers
mv jdk1.8.0_161 jdk

5.3 配置环境变量

vi /etc/profile
#tip:在配置文件末尾追加
export JAVA_HOME=/export/servers/jdk
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

5.4 使配置文件生效

source /etc/profile

5.5 查看是否配置成功

java -version

6.Hadoop 安装（所有虚拟机都要操作）

6.1 解压 hadoop

cd /export/software
tar -zxvf hadoop-2.7.4.tar.gz -C /export/servers/

6.2 打开配置文件

vi /etc/profile

6.3 配置 hadoop 环境变量

#tip:在文件末尾追加
export HADOOP_HOME=/export/servers/hadoop-2.7.4
export PATH=$PATH:$HADOOP_HOME/bin:$HADOOP_HOME/sbin

6.4 使配置文件生效

source /etc/profile

6.5 查看是否配置成功

hadoop version

6.6配置ssh免密登录

6.6.1生成密钥

ssh-keygen -t rsa

输入上面的代码后回车四次

6.6.2 把公钥拷贝到本台虚拟机上面去

ssh-copy-id localhost

6.6.4 验证一下是否需要密码

7.Hadoop 集群配置

7.1 进入主节点配置目录

cd /export/servers/hadoop-2.7.4/etc/hadoop/

7.2 修改 hadoop-env.sh 文件

vi hadoop-env.sh
#tip:找到相应位置，添加这段话
export JAVA_HOME=/export/servers/jdk

7.3 修改 core-site.xml 文件

vi core-site.xml

#tip: 下图中乱码部分为注释代码，可以删掉，不影响

<configuration>

<configuration>
	<property>
		<name>hadoop.tmp.dir</name>
		<value>file:/export/servers/hadoop-2.7.4/tmp</value>
		<description>Abase for other temporary directories.</description>
	</property>
	<property>
		<name>fs.defaultFS</name>
		<value>hdfs://localhost:9000</value>
	</property>
</configuration>

7.4 修改 hdfs-site.xml 文件

vi hdfs-site.xml

<configuration>
	<property>
		<name>dfs.replication</name> 
		<value>1</value>
	</property>
	<property>
		<name>dfs.namenode.name.dir</name> 
		<value>file:/export/servers/hadoop-2.7.4/tmp/dfs/name</value>
	</property>
	<property>
		<name>dfs.datanode.data.dir</name> 
		<value>file:/export/servers/hadoop-2.7.4/tmp/dfs/data</value>
	</property>
</configuration>

7.5修改mapred-site.xml

mv mapred-site.xml.template mapred-site.xml
vi mapred-site.xml

<configuration>
        <property>
                <name>mapreduce.framework.name</name>
                <value>yarn</value>
        </property>
</configuration>

7.6修改yarn-site.xml

vi yarn-site.xml

<configuration>
        <property>
                <name>yarn.nodemanager.aux-services</name>
                <value>mapreduce_shuffle</value>
        </property>
</configuration>

7.7 格式化文件系统（ successfully formatted 格式化成功）

hdfs namenode -format

三、Hadoop 启动

1. 启动集群

1.1 点启动所有 HDFS 服务进程

start-dfs.sh

1.2 启动所有 HDFS 服务进程

start-yarn.sh

1.3一键启动集群

start-all.sh

1.4 使用 jps 命令查看进程

2.关闭防火墙（所有虚拟机都要操作）

systemctl stop firewalld
#关闭防火墙

systemctl disable firewalld.service
#关闭防火墙开机启动

3.打开 window 下

firewall-cmd --state
# 查看防火墙状态

的 C:\Windows\System32\drivers\etc 打开 hosts 文件，在文件末

添加代码：

192.168.230.222 hadoop

4. 通 过 UI 界 面 查 看 Hadoop 运 行 状 态 ， 在 Windows 系 统 下 ， 访 问

http://hadoop:50070，或http://192.168.230.200:50070查看 HDFS 集群状态

5. 在 Windows 系统下，访问 http://hadoop:8088，或http://192.168.230.200:8088 查看 Yarn 集群状态

（二）Hive 的部署

Hive 安装

第一步：打开 X-Shell 软件，在 hive 上进入 /export/software/ 目录，指令如下： cd /export/software/

cd /export/software/

第二步：使用指令 rz 进行安装包上传，选择安装包 apache-hive-2.3.9-bin.tar.gz 。

ls 可查看到该目录

下存在 apache-hive-2.3.9-bin.tar.gz 文件。

第三步：上传完毕后将该安装包解压到 /export/servers/ 目录，使用如下指令：

tar -zxvf apache-hive-2.3.9-bin.tar.gz -C /export/servers/

解压完成后使用 cd /export/servers/ 进入该目录，

cd /export/servers/

修改hive名称

mv apache-hive-2.3.9-bin hive-2.3.9
ls

配置环境

vi /etc/profile
export HIVE_HOME=/export/servers/hive-2.3.9
export PATH=$PATH:$HIVE_HOME/bin

source /etc/profile 

2.安装mysql病配置sql服务器

2.1上传mysql安装包rz mysql-community-release-el7-5.noarch.rpm
2.2 安装 rpm

cd /export/sofrware
rpm -ivh mysql-community-release-el7-5.noarch.rpm

2.3 执行安装

yum install mysql-community-server

2.4 设置开机启动并启动mysql服务

systemctl enable mysqld
systemctl start mysqld

ok,因为版本的原因，安装的mysql未自动生成密码，8.x版本以上会自动生成
2.5 输入mysql ，启动mysql

mysql

在mysql命令窗口执行,为了能让root用户可以操作hive
abc123 为root密码

grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;

2.6 启动、关闭、重启服务

systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld

第四步： Hive 的配置。

首先进入 Hive 安装包下的 conf 文件夹，将 hive-env.sh.template 文件进行复制并

重命名为 hive-env.sh 。具体指令如下：

cd /export/servers/hive-2.3.9/conf

cp hive-env.sh.template hive-env.sh

然后修改 hive-env.sh 文件，添加 Hadoop 环境变量。

修改指令：

vi hive-env.sh

在文件内容中找到 #HADOOP_HOME=${bin}/../../hadoop 这句话，将这句话修改

成如下内容即可。

HADOOP_HOME=/export/servers/hadoop-2.7.4
export HIVE_CONF_DIR=/export/servers/hive-2.3.9/conf
export HIVE_AUX_JARS_PATH=/export/servers/hive-2.3.9/lib

4.2 配置hive-log4j2.properties和hive-site.xml

cd /export/servers/hive-2.3.9/conf/
cp hive-log4j2.properties.template hive-log4j2.properties

之后在 conf 目录下新建 hive-site.xml 文件，并添加配置信息。

新建文件并编辑如下：

vi hive-site.xml

编辑如下内容：
注：下述代码中，192.168.230.200为本机虚拟机ip地址，编辑时需要修改成自己的

<configuration>
   <property>
       <name>javax.jdo.option.ConnectionURL</name>
       <value>jdbc:mysql://192.168.230.200:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
    </property>
    <property>
       <name>javax.jdo.option.ConnectionDriverName</name>
       <value>com.mysql.jdbc.Driver</value>
    </property>
    <property>
           <name>javax.jdo.option.ConnectionUserName</name>
           <value>root</value>
    </property>
    <property>
           <name>javax.jdo.option.ConnectionPassword</name>
       <value>abc123</value>
    </property>
</configuration>

最后将连接驱动的 jar 包 mysql-connector-java-5.1.40.jar 上传至 Hive 安装包的 lib

文件夹下。

4.3 初始化数据库

schematool -dbType mysql -initSchema

4.4启动hive

二、数据仓库分层

（一）源数据层的实现

源数据层是数据仓库的基础层，负责存储原始的、未经加工的数据。在数据仓库

中，源数据层扮演着收集、存储和保留数据的角色。它是数据仓库中数据质量和

数据完整性的基础，同时也是其他层的数据加工和转换的起点。源数据层的作用

包括但不限于：

数据收集： 从各种数据源（如数据库、日志文件、 API 等）中收集原始数据。

数据存储： 将原始数据存储在数据仓库中，通常使用原始格式或轻微加工后的

格式存储。

数据保留： 源数据层通常保留了历史数据，以便进行后续的数据分析、报表生

成等操作。

在实现数据仓库分层之前，需要启动 Hadoop   Hive 实现数据库分层，具体操作步骤如下。

创建数据库 itcast_ods

在 Hive 中创建数据库 itcast_ods ，用于存储源数据层的源数据表，具体命令如下。

CREATE DATABASE IF NOT EXISTS itcast_ods;

设置存储格式

设置 Hive 的属性 hive.exec.orc .compression.strategy 的属性值为

COMPRESSION ，配置压缩生效，具体命令如下。

Set hive.exec.orc.compression.strategy=COMPRESSION;

创建源数据表 web_chat_ems_ods

在 Hive 的数据库 itcast_ods 中创建源数据表 web_chat_ems_ods 用于存储 MySQL

数据库中用户会话信息表 web_chat_ems_2019_07 的数据，具体命令如下。

CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_ems (
  id INT comment '主键',
  create_date_time STRING comment '数据创建时间',
  session_id STRING comment '七陌sessionId',
  sid STRING comment '访客id',
  create_time STRING comment '会话创建时间',
  seo_source STRING comment '搜索来源',
  seo_keywords STRING comment '关键字',
  ip STRING comment 'IP地址',
  area STRING comment '地域',
  country STRING comment '所在国家',
  province STRING comment '省',
  city STRING comment '城市',
  origin_channel STRING comment '投放渠道',
  user_match STRING comment '所属坐席',
  manual_time STRING comment '人工开始时间',
  begin_time STRING comment '坐席领取时间 ',
  end_time STRING comment '会话结束时间',
  last_customer_msg_time_stamp STRING comment '客户最后一条消息的时间',
  last_agent_msg_time_stamp STRING comment '坐席最后一下回复的时间',
  reply_msg_count INT comment '客服回复消息数',
  msg_count INT comment '客户发送消息数',
  browser_name STRING comment '浏览器名称',
  os_info STRING comment '系统名称')
comment '访问会话信息表'
PARTITIONED BY(starts_time STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orc
location '/user/hive/warehouse/itcast_ods.db/web_chat_ems_ods'
TBLPROPERTIES ('orc.compress'='ZLIB');

创建源数据表 web_chat_text_ems_ods 。

在 Hive 的数据库 itcast_ods 中创建源数据表 web_chat_text_ems_ods 用于存储

MySQL 数据库中用户会话信息表 web_chat_text_ems_2019_07 的数据，具体命令

如下。

CREATE EXTERNAL TABLE IF NOT EXISTS itcast_ods.web_chat_text_ems_ods
(
id INT COMMENT "主键 ID",
referrer STRING COMMENT "上级来源页面",
from_url STRING COMMENT "会话来源页面",
landing_page_url STRING COMMENT "访客浏览页面",
url_title STRING COMMENT "页面标题",
platform_description STRING COMMENT "用户信息",
other_params STRING COMMENT "扩展字段",
history STRING COMMENT "历史访问记录"
)
COMMENT "用户会话信息附属表"
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
STORED AS ORC
LOCATION "/user/hive/warehouse/itcast_ods.db/web_chat_text_ems_ods"
TBLPROPERTIES ("orc.compress"="ZLIB");

（二）数据仓库层的实现

数据仓库层通常用于存储源数据层清洗或转换后的数据，根据业务需求的不同，

清洗或转换后的数据也会有所不同。本项目的数据仓库层涉及明细层和业务层，

其中明细层用于存储源数据层清洗转换后的数据；业务层用于存储访问用户量和

咨询用户量这两个指标的数据，实现数据仓库层的步骤如下。

创建数据库 itcast_dwd

在 Hive 中创建数据库 itcast_dwd 用于存储明细层的数据，具体命令如下。

CREATE DATABASE IF NOT EXISTS itcast_dwd;

创建明细表 visit _consult_dwd

在 Hive 的数据库 itcast_dwd 中创建明细层的明细表 visit_consult_dwd 用于存储源

数据表 web_chat_ems_ods 和 web_chat_text_ems_ods 清洗转换后的数据，命令如

下。

CREATE TABLE IF NOT EXISTS itcast_dwd.visit_consult_dwd (
session_id STRING COMMENT "会话 ID",
sid STRING COMMENT "用户 ID",
create_time BIGINT COMMENT "会话创建时间",
ip STRING COMMENT "IP 地址",
area STRING COMMENT "地区",
msg_count INT COMMENT "客户发送消息数",
origin_channel STRING COMMENT "来源渠道",
from_url STRING COMMENT "会话来源页面"
)
COMMENT "访问咨询用户明细表"
PARTITIONED BY (yearinfo STRING, monthinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");

创建数据库 itcast_dws

在 Hive 中创建数据库 itcast_dws 用于存储明细层的数据，具体命令如下。

CREATE DATABASE IF NOT EXISTS itcast_dws;

创建业务层宽表 visit_dws

在 Hive 的数据库 itcast_dws 中创建业务层的宽表 visit_dws ，用于存储访问用户

量指标的数据，具体命令如下。

CREATE TABLE IF NOT EXISTS itcast_dws.visit_dws (
sid_total INT COMMENT "根据用户 ID 去重统计",
sessionid_total INT COMMENT "根据 SessionID 去重统计",
ip_total INT COMMENT "根据 IP 地址去重统计",
area STRING COMMENT "地区",
origin_channel STRING COMMENT "来源渠道",
from_url STRING COMMENT "会话来源页面",
groupType STRING COMMENT "1.地区维度 2.来源渠道维度 3.会话页面维度 4.
总访问量维度"
)
COMMENT "访问用户量宽表"
PARTITIONED BY (yearinfo STRING,monthinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
Stored as orc
TBLPROPERTIES ("orc.compress"="SNAPPY");

创建业务层的宽表 consult_dws

在 Hive 的数据库 itcast_dws 中创建业务层的宽表 consult_dws ，用于存储咨询用

户量的数据，具体命令如下。

CREATE TABLE IF NOT EXISTS itcast_dws.consult_dws
(
    sid_total INT COMMENT "根据用户 ID 去重统计",
    sessionid_total INT COMMENT "根据 SessionID 去重统计",
    ip_total INT COMMENT "根据 IP 地址去重统计"
)
COMMENT "咨询用户量宽表"
PARTITIONED BY (yearinfo STRING, monthinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY "\t"
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");

三、数据采集与转换

（一）数据迁移过程

1、Sqoop 的简介

Sqoop 是一款开源的工具，主要用于在 Hadoop(Hive) 与传统的数据库 (mysql 、

postgresql … ) 间 进 行 数 据 的 传 递 ， 可 以 将 一 个 关 系 型 数 据 库 （ 例 如 ：

MySQL ,Oracle ,Postgres 等）中的数据导进到 Hadoop 的 HDFS 中，也可以将 HDFS

的数据导进到关系型数据库中。

sqoop1 最新版本是 1.4.7 ， sqoop2 最新版本是 1.99.7

sqoop2 特征不完整，不建议用于生产部署。

1.1 、 sqoop 依赖环境

2 、 sqoop 集群安装部署。

安装 sqoop 前集群的最低要求安装 mysql ， jdk 和 hadoop 。

可以添加 hbase ， hive 和 zookeeper ，进行试验。

参考 hive,hbase,zookeeper 和 hadoop 这些进行搭建。

2.1 、 sqoop 解压

cd /export/software
tar -zxvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /export/servers/

cd /export/servers
mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7

2.2 、 sqoop 配置环境变量

vi /etc/profile

# SQOOP_HOME
export SQOOP_HOME=/export/servers/sqoop-1.4.7
export PATH=$PATH:$SQOOP_HOME/bin

source /etc/profile

2.3 、 sqoop 配置文件

进入 conf

cd /export/servers/sqoop-1.4.7/conf
cp sqoop-env-template.sh sqoop-env.sh

将 hadoop ， hive ， 的绝对路径进行填写。

在Sqoop安装目录的conf目录中修改文件sqoop-env.sh，具体命令如下。

#指定Hadoop安装目录
export HADOOP_COMMON_HOME=/export/servers/hadoop-2.7.4/
export HADOOP_MAPRED_HOME=/export/servers/hadoop-2.7.4/
#指定Hive安装目录
export HIVE_HOME=/export/servers/hive-2.3.9

将 MySQL 的驱动包放到 sqoop 的 lib 下面

cp /export/servers/hive-2.3.9/lib/mysql-connector-java-5.1.40.jar /export/servers/sqoop-1.4.7/lib/

2.4 、验证

sqoop help

执行Sqoop查看本地Mysql数据库中数据库列表的命令，验证Sqoop是否成功部署，具体命令如下。

sqoop list-databases \
  --connect jdbc:mysql://192.168.230.200:3306/ \
  --username root --password abc123

导入数据文件

以root身份登录MySQL数据库，在MySQL数据库的命令行交互界面执行“source /export/data/nev.sql”命令，将数据库文件导入到MySQL数据库。数 据库文件导入完成后执行“showdatabases;”命令查看数据库列表。

source /export/data/nev.sql
show databases;

在MySQL的命令行交互界面执行“use nev;”命令，切换到数据库nev，然后执行“show tables;”命令查看数据库nev中的数据表。

use nev;
show tables;

导入依赖包

由于Sqoop向Hive表中导入数据时依赖于Hive的相关jar包，所以需要将Hive的相关jar包导入到Sqoop的lib目录中，具体命令如下。

cp /export/servers/hive-2.3.9/lib/{antlr-runtime-3.5.2.jar,hive-hcatalog-core-2.3.9.jar,hive-exec-2.3.9.jar,datanucleus-api-jdo-4.2.4.jar,datanucleus-core-4.1.17.jar,datanucleus-rdbms-4.1.19.jar,derby-10.10.2.0.jar,javax.jdo-3.2.0-m3.jar} /export/servers/sqoop-1.4.7/lib/

cp /export/servers/hive-2.3.9/lib/derby-10.10.2.0.jar /export/servers/hadoop-2.7.4/share/hadoop/yarn/lib/

导入配置文件

由于Sqoop向Hive表中导入数据时需要获取Hive的配置信息，所以需要将Hive 的配置文件hive-site.xml导入到Sqoop的conf目录下，具体命令如下。

cp /export/servers/hive-2.3.9/conf/hive-site.xml /export/servers/sqoop-1.4.7/conf/

（二）数据转换过程

1. 实现数据迁移

（ 1 ）在虚拟机 hive 执行 Sqoop 命令将 MySQL 数据库的用户会话信息表

web_chat_ems_2019_07

中的数据迁移到 Hive 数据仓库源数据层的 web_chat_ems_ods 源数据表，具体命

令如下所示。

sqoop import \
 --connect jdbc:mysql://192.168.230.200:3306/nev \
 --username root \
 --password abc123 \
 --query "select id,create_date_time,session_id,sid,create_time,seo_source,seo_keywords,ip,area,country,province,city,origin_channel,user as user_match,manual_time,begin_time,end_time,last_customer_msg_time_stamp,last_agent_msg_time_stamp, reply_msg_count,msg_count,browser_name,os_info from web_chat_ems_2019_07 where 1=1 and \$CONDITIONS"\
 --hcatalog-database itcast_ods \
 --hcatalog-table web_chat_ems_ods \
 -m 10 \
 --split-by id

（ 2 ）在虚拟机 hive   执行 Sqoop 命令将 MySQL 数据库的用户会话信息附属表

web_chat_text_ems_2019_07 中 的 数 据 迁 移 到 Hive 数 据 仓 库 源 数 据 层 的

web_chat_text_ems_ods 源数据表，具体命令如下所示。

sqoop import \
--connect jdbc:mysql://192.168.230.200:3306/nev \
--username root \
--password abc123 \
--query "select id,referrer,from_url,landing_page_url,url_title,platform_description,
other_params,history from web_chat_text_ems_2019_07 where 1=1 and \$CONDITIONS" \
--hcatalog-database itcast_ods \
--hcatalog-table web_chat_text_ems_ods \
-m 10 \
--split-by id

（ 3 ）统计源数据表 web_chat_ems_ods 中包含的数据行数，

select count(*) from itcast_ods.web_chat_ems_ods;

（ 3 ）统计源数据表 web_chat_text_ems_ods 中包含的数据行数 , 具体命令如下。

据转换，具体命令如下。 

​​​​​​​select count(*) from itcast_ods.web_chat_text_ems_ods;

四、数据分析

（一）实现地区访问用户量统计

1. 需求是统计不同地区访问网站的用户量，公司决策者可以根据统计结果对不

同地区的业务进行调整 , 例如访问用户量比较多的地区可以增加客服人数 , 减少用

户等待时间；访问用户量比较少的地区可以加大推广，使更多用户了解并访问网

站。接下来，实现地区访问用户量统计，统计结果将存储在访问用户量宽表

visit_dws ，具体命令如下。 INSERT INTO itcast_dws.visit_dws PARTITION (yearinfo, monthinfo)

SELECT
COUNT(DISTINCT sid) sid_total,
COUNT(DISTINCT session_id) sessionid_total,
COUNT(DISTINCT ip) ip_total,
area,
'-1' origin_channel,
'-1' from_url,
'1' grouptype,
yearinfo,monthinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY area,yearinfo,monthinfo;

   SELECT sid_total,sessionid_total,ip_total,area,yearinfo,monthinfo FROM itcast_dws.visit_dws WHERE grouptype="1" limit 6;

（二）实现会话页面排行榜

1. 会话页面是指用户创建客服会话的页面 , 本需求是统计会话页面排行榜，公司

决策者可以根据统计结果了解网站中哪些页面对用户比较有吸引力 , 促使用户向

客服咨询。例如用户在浏览网站中的大数据学科相关课程介绍页面时，有大量用

户向客服进行咨询，那么从侧面可以反映出用户对于大数据学科学习的需求量比

较高。接下来，统计会话页面，统计结果存储在访问用户量宽表 visit_dws

INSERT INTO itcast_dws.visit_dws PARTITION (yearinfo, monthinfo)
SELECT
COUNT(DISTINCT sid) sid_total,
COUNT(DISTINCT session_id) sessionid_total,
COUNT(DISTINCT ip) ip_total,
'-1' area,
'-1' origin_channel,
from_url,
'3' grouptype,
yearinfo,monthinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY from_url,yearinfo,monthinfo;

    SELECT sessionid_total,from_url,yearinfo,monthinfo FROM itcast_dws.visit_dws WHERE grouptype="3" AND from_url!="" ORDER BY sessionid_total DESC LIMIT 1;

（三）实现访问用户量统计

1. 本需求是统计访问网站的总用户量，公司决策者可以根据统计结果了解网站

的访问情况，并根据实际情况对网站内容以及业务进行有效调整，同时访问用户

量统计结果也可以作为二次分析的底层数据，例如咨询率统计就需要访问用户量

统计结果 。接下来，统计访问用户量 , 统计结果存储在访问用户量宽表 visit_dws,

具体命令如下。

INSERT INTO itcast_dws.visit_dws PARTITION (yearinfo, monthinfo)
SELECT
COUNT( DISTINCT sid ) sid_total,
COUNT( DISTINCT session_id ) sessionid_total,
COUNT( DISTINCT ip ) ip_total,
'-1' area,
'-1' origin_channel,
'-1' from_url,
'4' grouptype,
yearinfo,monthinfo
FROM itcast_dwd.visit_consult_dwd
GROUP BY yearinfo,monthinfo;

   SELECT sid_total,sessionid_total,ip_total,yearinfo,monthinfo 
   FROM itcast_dws.visit_dws WHERE grouptype="4";

（四）实现来源渠道访问用户量统计

1. 本需求是统计通过不同来源渠道访问网站的用户量，公司决策者可以根据统

计结果了解不同渠道投放广告的效果，并根据实际情况进行调整，例如投放效果

好的渠道可以加大投资，投放效果不好的渠道可以舍弃等。接下来，统计来源渠

道访问用户量，统计结果将存储在访问用户量宽表 visit_dws 。

INSERT INTO itcast_dws.visit_dws PARTITION (yearinfo,monthinfo)
SELECT
COUNT(DISTINCT sid) sid_total,
COUNT(DISTINCT session_id) session_total,
COUNT(DISTINCT ip) ip_total,
'-1' area,
origin_channel,
'-1' from_url,
'2' grouptype,
yearinfo,monthinfo
FROM itcast_dwd.visit_consult_dwd
19GROUP BY origin_channel,yearinfo,monthinfo;

    SELECT sid_total,sessionid_total,ip_total,origin_channel,yearinfo,monthinfo FROM itcast_dws.visit_dws WHERE grouptype="2";

（五）实现咨询率统计

1. 实现咨询用户量统计的命令执行完成后，通过访问用户量和咨询用户量统计

结果实现咨询率统计，具体命令如下。

SELECT
CONCAT(ROUND(msgNumber.sid_total / totalNumber.sid_total,4) * 100, '%')
sid_rage,
CONCAT (ROUND (msgNumber.sessionid_total/ totalNumber.sessionid_total,4)*100,
'%') session_rage,
CONCAT (ROUND (msgNumber.ip_total / totalNumber.ip_total,4)* 100, '&')
ip_rage,
msgNumber.yearinfo,
msgNumber.monthinfo
FROM
(
SELECT
sid_total,
sessionid_total,
ip_total,
yearinfo,
monthinfo
FROM itcast_dws.consult_dws
) msgNumber,
(
SELECT
sid_total,
sessionid_total,
ip_total,
yearinfo,
monthinfo
FROM itcast_dws.visit_dws
where grouptype="4"
) totalNumber;

（六）数据可视化

1. 导出数据

在虚拟机 hive 中执行“ mysql -uroot -p ”命令以 root 身份登录 MySQL 数据库，

在弹出的“ Enter password: ”信息处输入 root 用户的密码，从而登录 MySQL 数

据库进入命令行交互界面。

在数据库 nev 中创建表 visit_dws 用于存储 Hive 数据仓库中访问用户量宽表

visit_dws 导出的数据，具体命令如下。

CREATE TABLE nev.visit_dws (
sid_total INT(11) COMMENT '根据用户 ID 去重统计',
sessionid_total INT(11) COMMENT '根据 SessionID 去重统计',
ip_total INT(11) COMMENT '根据 IP 地址去重统计',
area VARCHAR(32) COMMENT '地区', -- 添加单引号并在注释前后加上空格
origin_channel VARCHAR(32) COMMENT '来源渠道', -- 添加单引号并在注释前
后加上空格
from_url VARCHAR(100) COMMENT '会话来源页面', -- 添加单引号并在注释前
后加上空格
groupType VARCHAR(100) COMMENT '1.地区维度 2.来源渠道维度 3.会话页面
维度 4 总访问量维度',
yearinfo VARCHAR(32) COMMENT '年',
monthinfo VARCHAR(32) COMMENT '月'
);

在数据库 nev 中创建表 consult_dws 用于存储 Hive 数据仓库中咨询用户量宽表

consult_dws 导出的数据，具体命令如下。

CREATE TABLE nev.consult_dws (
sid_total INT(11) COMMENT '根据用户 ID 去重统计',
sessionid_total INT(11) COMMENT '根据 SessionID 去重统计',
21ip_total INT(11) COMMENT '根据 IP 地址去重统计',
yearinfo VARCHAR(32) COMMENT '年',
monthinfo VARCHAR(32) COMMENT '月'
);

查看数据库 nev 中是否成功创建表 visit_dws 和 consult_dws ，具体命令如下。

/* 切换到数据库 nev*/

use nev;

/* 查看数据库 nev 中包含的表 */

show tables;

在 MySQL 命令行交互界面执行“ exit ”命令登出 MySQL ，在虚拟机 Node02 执

行 Sqoop 命令将 Hive 数据仓库中访问用户量宽表 visit_dws 导出到关系型数据库

MySQL 中 nev 数据库的表 visit_dws ，具体命令如下所示。

sqoop export \
--connect "jdbc:mysql://192.168.230.222:3306/nev?useUnicode=true&character
Encoding=utf-8" \
--username root \
--password abc123 \
--table visit_dws \
22--hcatalog-database itcast_dws \
--hcatalog-table visit_dws

在 MySQL 命令行交互界面执行“ exit ”命令登出 MySQL ，在虚拟机 Node02 执

行 Sqoop 命令将 Hive 数据仓库中咨询用户量宽表 consult_dws 导出到关系型数据

库 MySQL 中 nev 数据库的表 consult_dws ，具体命令如下所示。

sqoop export \
--connect "jdbc:mysql://192.168.230.222:3306/nev?
useUnicode=true&characterEncoding=utf-8" \
--username root \
--password abc123 \
--table consult_dws \
--hcatalog-database itcast_dws \
--hcatalog-table consult_dws

2. 安装、启动与配置 FineBI

访问 FineBI 的官方网站通过注册的方式下载 Windows x64 位操作系统的 FineBI

安装包 windows-x64_FineBI5_1-CN.exe 。

双击 FineBI 安装包 windows-x64_FineBI5_1-CN.exe 进入“欢迎使用 FineBI 安装

程序向导“界面。

23 在“欢迎使用 FineBI 安装程序向导”界面，下一步”按钮进入“许可协议”界

面，在该界面勾选“我接受协议选项”

在“许可协议”界面，单击“下一步”按钮进入“选择安装目录”界面，在该界

面中配置 FineBI 的安装目录，这里配置 FineBI 的安装目录为“ D:\FineBI5.1 ”

在“选择安装目录”界面，单击“下一步”按钮进入“设置最大内存”界面，在

“最大 jvm 内存”输入框中输入最大 jvm 内存，默认为 2048 （ 2G ）。建议设置

最大 jvm 内存大于 2048 ，这里设置最大 jvm 内存为 4096 （ 4G ）。

在“设置最大内存”界面，单击“下一步”按钮进入“选择开始菜单文件夹”界

面，在该界面使用默认配置即可。

在“选择开始菜单文件夹”界面，单击“下一步”按钮进入“选择附加工作”界

面，在该界面使用默认配置即可。

在“选择附加工作”界面，单击“下一步”按钮进入“安装中”界面，在该界面

FineBI 会自动安装。

等待 FineBI 安装完成后会自动进入“完成 FineBI 安装程序”界面，在该界面使

用默认配置即可。

26 启动 FineBI

打开 FineBI 平台

等待 FineBI 启动完成后，系统默认配置的浏览器会打开 FineBI 平台。

初始化设置

首次使用 FineBI 时需要通过 FineBI 平台进行初始化设置，将用户名设置为“ root ”，

密码设置为“ 123456 ”

在”设置管理员账号界面”，单击“确认”按钮后，平台会显示“管理员账号设

置成功”信息，并且设置的管理员密码会以明文的方式显示

选择 FineBI 使用的数据库

在”管理员账号设置成功界面”，单击“下一步”按钮选择 FineBI 使用的数据

库。

FineBI 登录界面

在”选择 FineBI 使用的数据库”中，单击 “直接登录”选项，页面会自动跳转

到 FineBI 商业智能平台的登录界面。

28 登录 FineBI 商业智能平台

输入用户名“ root ”和密码“ 123456 ”，单击“登录”按钮登录 FineBI 商业智

能平台。

数据连接管理

在 FineBI 商业智能平台依次选择“管理系统→数据连接→数据连接管理”进入

数据连接管理界面。

选择 FineBI 使用的数据库

在”数据连接界面”，单击“新建数据连接”按钮选择 FineBI 使用的数据库

29 选择 FineBI 使用的数据库

在”选择 FineBI 使用的数据库界面”右侧的数据库列表中，选择“ MySQL ”选

项，配置连接 MySQL 的相关信息，完成 MySQL 相关信息配置。

选择 FineBI 使用的数据库

单击“测试连接”按钮，验证 FineBI 是否可以成功连接虚拟机 hive  中的 MySQL

数据库。

数据准备

在 FineBI 商业智能平台选择“数据准备”选项。

配置业务名称

在“数据准备”界面，单击“添加业务包”选项添加业务，并配置业务名称为“教

育大数据分析平台”。

配置业务使用的数据内容

在“配置业务名称”界面，单击业务“教育大数据平台”配置该业务使用的数据

内容。

选择数据库中需要使用的数据表

在“配置业务使用的数据内容”界面，依次单击“添加表→数据库表”选项选择

数据库中需要使用的数据表。

成功添加表

在“选择数据库中需要使用的数据表”界面，选中需要使用的访问用户量表

visit_dws 和咨询用户量表 consult_dws ，单击“确认”按钮，在教育大数据平台

业务中展示成功添加表。

业务包更新

单击“业务包更新”选项更新“教育大数据平台”业务中添加表 consult_dws 和

visit_dws 的数据，否则后续无法使用这两个表的数据进行可视化展示。

新建 FineBI 仪表板

在 FineBI 商业智能平台单击“仪表板”选项进入仪表板管理页面，在该页面单

击“新建仪表板”选项，在弹出窗口的“名称”文本框中输入“教育大数据平台”，

单击“确定”按钮新建仪表板。

完成 FineBI 仪表板配置

30 单击仪表板“教育大数据平台”，进入教育大数据平台仪表板的配置页面 , 显示

仪表板配置完成。

（三 ) 实现数据可视化

统计地区访问用户量

设 置 表 名 为 ” 根 据 sid 统 计 的 地 区 访 问 用 户 量 ” , 然 后 选 择 使 用 的 表

“ itcast_visit_dws ”，最后勾选使用的字段名称“根据用户 ID 去重统计”和“地

区”。

添加过滤条件

在”根据访问用户量”界面 , 单击“ + ”按钮添加过滤条件。

去除字段在过滤条件中去除字段“地区”中值为“ -1 ”的数据，并且指定数据开

头为“中国”。

退出自助数据集页面

在“根据访问用户量”配置过滤条件界面，单击“保存并更新”按钮保存过滤条

件的配置内容，然后单击“×”按钮退出“自助数据集”页面。

添加组件

在弹出的“添加组件”窗口依次选择“教育大数据平台→根据 sid 统计的地区访

问用户量”

配置经纬度

在组件添加完成后，需要将地区字段内的数据转换为经纬度数据，便于后续通过

中国地图的方式进行可视化展示。

手动调整未匹配字段数据

FineBI 会自动将“地区”字段中的数据转换为经纬度，不过此时“地区”字段中

的特殊数据， FineBI 并不会自动匹配并转换，因此需要对特殊字段进行手动调整。

完成经纬度配置

在“手动调整未匹配字段数据”界面，单击“确认”按钮，此时在“地区”字段

下方会多出“地区（经度）”和“地区（纬度）”两个字段。

地区访问用户量可视化展示

在“图标类型”一栏中选择区域地区，然后将“地区（经度）”字段拖动到“横

轴”，将“地区（纬度）”拖动到“纵轴”，最后将“根据用户 ID 去重统计”

字段拖动到“图形属性”一栏中的“标签”中，此时 FineBI 会自动实现根据用

户 ID 统计的地区访问用户量可视化展示。

地区用户可视化展示

在“根据用户 ID 统计的地区访问用户量可视化展示”界面，更改标题名称，然

后单击“进入仪表板”按钮即可。

配置过滤件

在 “ 根据 Session” 选字段界面，单击 “+” 按钮添加过滤条件，去除字段 “ 会话来源页

面 ” 中的空数据和值为 “-1” 的数据。

统计会话页面排行榜

在 “ 实现地区访问用户量可视化 ” 界面，单击 “ 组件 ” 按钮，在弹出的 “ 添加组件 ” 窗

口依次选择 “ 教育大数据平台 → 根据 Session” 。

配置组件页面

在添加组件 “Session” 界面，单击 “ 确认 ” 按钮进入配置组件页面。

进入仪表盘

在配置组件 “ 根据 SessionID 统计的会话页面排行榜 ” 界面，单击 “ 进入仪表板 ” 按

钮进入 “ 教育大数据平台 ” 仪表板。

选字段

在“在教育大数据平台业务中成功添加表”页面，依次选择“添加表→自助数据

集”进入自助数据集页面，在该页面中首先设置表名为“根据 ip 统计的来源渠

道访问用户量”，然后选择使用的表“ itcast_visit_dws ”，最后勾选使用的字段

名称“来源渠道”和“根据 IP 地址去重统计”。

去除字段

在“自助数据集“根据 sid 统计的地区访问用户量”选字段页面，单击“ + ”按

钮添加过滤条件，去除字段“来源渠道”中值为“ -1 ”的数据。

统计访问用户量

在“实现会话页面排行榜可视化”页面，单击“组件”按钮，在弹出的“添加组

件”窗口依次选择“教育大数据平台→根据 ip 统计的来源渠道访问用户量”。

退出自助数据集页面

在 “根据 Session ”页面，单击“确认”按钮进入配置组件页面。

成功添加组件

在“根据 ip 统计的来源渠道访问用户量”页面，单击“进入仪表板”按钮进入

“教育大数据平台”仪表板。