第13周 · 教育大数据分析平台（一）

教育大数据分析平台（一）

1.1 Sqoop安装配置

由于提交的Sqoop命令最终会转换为MapReduce作业，所以安装Sqoop之前需要确保Hadoop集群正常运行。跟Hive类似，Sqoop客户端只需要选择一个节点安装即可，详细的安装步骤如下所示。

1.下载解压

到官网(地址:http://archive.apache.org/dist/sqoop/1.4.6/)下载Sqoop安装包sqoop-1.4.6.bin__hadoop-1.0.4-alpha.tar.gz，然后上传至 hadoop1节点的/home/hadoop/app目录下并解压，具体操作如下所示。

[hadoop@hadoop1 app]$tar -zxvf sqoop-1.4.7.bin__hadoop-1.6.0.tar.gz

[hadoop@hadoop1 app]$ ln -s sqoop-1.4.7.bin__hadoop-1.6.0 sqoop

1. 修改配置文件

进入Sqoop的conf目录下修改sqoop-env.sh配置文件，修改内容如下所示。

[hadoop@hadoop1 conf]$ cp sqoop-env-template.sh sqoop-env.sh

[hadoop@hadoop1 conf]$ vi sqoop-env.sh

export HADOOP_COMMON_HOME=/home/hadoop/app/hadoop

export HADOOP_MAPRED_HOME=/home/hadoop/app/hadoop

export HIVE_HOME=/home/hadoop/app/hive

export ZOOCFGDIR=/home/hadoop/app/zookeeper

export HCAT_HOME=/home/hadoop/app/hive/hcatalog

3.配置环境变量

在hadoop用户下，添加Sqoop的环境变量，添加内容如下所示。

[hadoop@hadoop1 conf]$ vi ~/.bashrc

export SQOOP_HOME=/home/hadoop/app/sqoop

Source生效

[hadoop@hadoop1 conf]$ source ~/.bashrc

4.添加MySQL驱动包

由于需要通过Sqoop实现Hadoop平台与MySQL之间的数据迁移，所以需要下载MySQL驱动包mysql-connector-j-8.0.31.jar，commons-lang-1.6.jar，hive-common-3.1.3.jar并上传至Sqoop的lib目录下。

5.测试运行

（1）查看Sqoop命令语法。在Sqoop安装目录下，使用help命令查看Sqoop的基本用法，具体操作如下所示。

[hadoop@hadoop1 sqoop]$ bin/sqoop help

（2）测试数据库连接。我们以Hive的元数据库MySQL为例，使用list-databases命令查看数据库名称列表，具体操作如下所示。

[hadoop@hadoop1 sqoop]$ bin/sqoop list-databases --connect jdbc:mysql://hadoop1 --username hive --password hive

如果能打印显示MySQL数据库名称列表，说明Sqoop可以连接上数据库。

1.2实现数据仓库分层

本项目的数据仓库分层设计，需要在Hive数据仓库中创建源数据层对应的源数据表，以及数据仓库层对应的明细表和宽表。在实现数据仓库分层之前，需要启动 Hadoop集群和Zookeeper集群， 在虚拟机hadoop1中使用Hive的 HiveServer2服务操作，用dadagrip操作Hive实现数据库分层，具体操作步骤如下。

1.1.1源数据层

源数据层通常用于存储Mysql数据库的原始数据，为了防止原始数据丢失无法找回，通常情况下元数据层的表需要创建外外部表，实现源数据层的步骤如下：

（1）在Hive中创建数据库hnsoft_ods，用于存储源数据层的源数据表，具体命令如下

CREATE DATABASE IF NOT EXISTS hnsoft_ods;

设置Hive的属性hive.exec.orc.compression.strategy的属性值为COMPRESSION，配置压缩生效，具体命令如下。

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

在Hive的数据库hnsoft_ods中创建源数据表web_chat_ems_ods用于存储MySQL数据库中用户会话信息表web_chat_ems_2019_07的数据，具体命令如下:

CREATE EXTERNAL TABLE IF NOT EXISTS hnsoft_ods.web_chat_ems_ods (

id INT COMMENT "主键ID",

create_date_time STRING COMMENT "数据创建时间",

session_id STRING COMMENT "会话ID",

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 "访问会话信息表"

ROW FORMAT DELIMITED

FIELDS TERMINATED BY "\t"

STORED AS ORC

LOCATION "/user/hive/warehouse/hnsoft_ods.db/web_chat_ems_ods"

TBLPROPERTIES ("orc.compress"="ZLIB");

在Hive的数据库hnsoft_ods中创建源数据表web_chat_text_ems_ods用于存储MySQL数据库中用户会话信息表web_chat_text_ems_2019_07的数据，具体命令如下。

CREATE EXTERNAL TABLE IF NOT EXISTS hnsoft_ods.web_chat_text_ems_ods (

id INT COMMENT "主键ID",

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/hnsoft_ods.db/web_chat_text_ems_ods"

TBLPROPERTIES ("orc.compress"="ZLIB");

1.1.2 数据仓库层

数据仓库层通常用于存储源数据层清洗或转换后的数据，根据业务需求的不同，清洗或转换后的数据也会有所不同。本项目的数据仓库层涉及明细层和业务层，其中明细层用于存储源数据层清洗转换后的数据；业务层用于存储访问用户量和咨询用户量这两个指标的数据，实现数据仓库层的步骤如下。

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

CREATE DATABASE IF NOT EXISTS hnsoft_dwd;

在Hive的数据库hnsoft_dwd中创建明细层的明细表visit_consult_dwd用于存储源数据表web_chat_ems_ods和web_chat_text_ems_ods清洗转换后的数据，命令如下。

CREATE TABLE IF NOT EXISTS hnsoft_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");

（3）在Hive中创建数据库hnsoft_dws用于存储业务层的数据，具体命令如下。

CREATE DATABASE IF NOT EXISTS hnsoft_dws;

（4）在Hive的数据库hnsoft_dws中创建业务层的宽表consult_dws，用于存储咨询用户量的数据，具体命令如下。

CREATE TABLE IF NOT EXISTS hnsoft_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");

（5）在Hive的数据库hnsoft_dws中创建业务层的宽表consult_dws，用于存储咨询用户量的数据，具体命令如下。

CREATE TABLE IF NOT EXISTS hnsoft_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");

II