电商数据仓库项目开发记录

小崔的技术博客

于 2023-03-10 11:52:10 发布

阅读量416

点赞数 1

分类专栏：大数据学习文章标签：数据仓库 spark 大数据

本文链接：https://blog.csdn.net/qq_40905284/article/details/129438866

版权

大数据学习专栏收录该内容

61 篇文章 20 订阅

订阅专栏

文章目录

（一）数据生成
- （1）客户端数据
- （2）服务端数据
（二）采集数据

（一）数据生成

（1）客户端数据

客户端数据，主要包含用户打开APP、点击、浏览等行为数据
用户行为数据：通过埋点上报，后端日志服务器(http)负责接收数据

{ 
"uid"：1001,  //用户ID 
"xaid"："ab25617-c38910-m2991",  //手机设备ID 
"platform"：2,  //设备类型, 1:Android-APP, 2:IOS-APP, 3:PC  
"ver"："3.5.10",  //大版本号 
"vercode"："35100083",  //子版本号 
"net"：1,  //网络类型, 0:未知, 1:WIFI, 2:2G , 3:3G, 4:4G, 5:5G 
"brand"："iPhone",  //手机品牌 
"model"："iPhone8",  //机型 
"display"："1334x750",  //分辨率 
"osver"："ios13.5",  //操作系统版本号 
"data"：[ //用户行为数据 
{"act"：1,"acttime"：1592486549819,"ad_status"：1,"loading_time":100},
{"act"：2,"acttime"：1592486549819,"goods_id"："2881992"} 
] 
}

注意：考虑到性能，一般数据上报都是批量上报，假设间隔10秒上报一次，这种数据延迟是可以接受的
各个字段解释：

属性        含义 
act       用户行为类型 
acttime     数据产生时间(时间戳) 
goods_id    商品ID 
location    商品展示顺序：在列表页中排第几位，从0开始 
stay_time    页面停留时长(单位毫秒) 
loading_time  页面加载耗时(单位毫秒)

最终的日志数据会保存在data_collect这个日志采集服务所在的机器上，通过log4j记录在/data/log目录下面。

（2）服务端数据

服务端：商品订单相关数据，这些数据都是存储在mysql中的。
相关的表名：

订单表：user_order  
商品信息表：goods_info 
订单商品表：order_item 
商品类目码表：category_code 
订单收货表：order_delivery  
支付流水表：payment_flow 
用户收货地址表：user_addr 
用户信息表：user 
用户扩展表：user_extend

表结构如下：
在这里插入图片描述
使用这个脚本进行初始化：init_mysql_tables.sql

接着将用户产生的订单数据导入数据库中。

（二）采集数据

（1）用户行为数据采集

数据接收到以后，需要使用flume采集数据，按照act值的不同，将数据分目录存储
flume Agent配置内容如下：

# agent的名称是a1 
# 指定source组件、channel组件和Sink组件的名称 
a1.sources = r1 
a1.channels = c1 
a1.sinks = k1 
  
# 配置source组件 
a1.sources.r1.type = exec 
a1.sources.r1.command = tail -F /data/log/user_action.log 
# 配置拦截器 
a1.sources.r1.interceptors = i1 
a1.sources.r1.interceptors.i1.type = regex_extractor 
a1.sources.r1.interceptors.i1.regex = "act":(\\d) 
a1.sources.r1.interceptors.i1.serializers = s1 
a1.sources.r1.interceptors.i1.serializers.s1.name = act 
# 配置channel组件 
a1.channels.c1.type = memory 
a1.channels.c1.capacity = 1000 
a1.channels.c1.transactionCapacity = 100 
# 配置sink组件 
a1.sinks.k1.type = hdfs 
a1.sinks.k1.hdfs.path = hdfs://bigdata01:9000/data/ods/user_action/%Y%m%d/%{a
a1.sinks.k1.hdfs.fileType = DataStream 
a1.sinks.k1.hdfs.writeFormat = Text 
a1.sinks.k1.hdfs.rollInterval = 3600 
a1.sinks.k1.hdfs.rollSize = 134217728 
a1.sinks.k1.hdfs.rollCount = 0 
a1.sinks.k1.hdfs.useLocalTimeStamp = true 
#增加文件前缀和后缀 
a1.sinks.k1.hdfs.filePrefix = data 
a1.sinks.k1.hdfs.fileSuffix = .log 
# 把组件连接起来 
a1.sources.r1.channels = c1 
a1.sinks.k1.channel = c1

（2）商品订单相关数据采集

下面我们需要将商品订单数据采集到HDFS里面，在这里针对关系型数据库数据的采集可以使用Sqoop
使用sqoop的导入功能，将MySQL中的数据导入到HDFS上面。

（1）Sqoop的安装

Sqoop目前有两大版本，Sqoop1和Sqoop2，这两个版本都是一直在维护者的，所以使用哪个版本都可以。

使用Sqoop1的时候可以将具体的命令全部都写到脚本中，这样看起来是比较清晰的，但是有一个弊端，就是在操作MySQL的时候，MySQL数据库的用户名和密码会明文暴露在这些脚本中，不过一般也没有什么问题，因为在访问生产环境下的MySQL的时候，是需要申请权限的，就算你知道了MySQL的用户名和密码，但是你压根无法访问MySQL的那台机器，所以这样也是安全的，只要运维那边权限控制到位了就没问题。
sqoop2中引入了sqoop server(服务)，集中管理connector(连接)，而sqoop1只是客户端工具。
相对来说，Sqoop1更加简洁，轻量级。

去官网下载压缩包：注意：最终下载的sqoop1.4.7的安装是这个 sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz
链接地址
这个安装包表示里面包含了hadoop-2.6.0的依赖，我们目前使用的是hadoop3.2.0，不过是可以兼容的，这样就没有必要重新编辑sqoop了。

因为Sqoop1只是一个客户端工具，直接解压，修改一下配置文件就行，不需要启动任何进程
Sqoop在执行的时候底层会生成MapReduce任务，所以Sqoop需要部署在Hadoop客户端机器上

修改配置文件的名称

[root@bigdata04 conf]# mv sqoop-env-template.sh  sqoop-env.sh

配置SQOOP_HOME环境变量: vi /etc/profile

export SQOOP_HOME=/data/soft/sqoop-1.4.7.bin__hadoop-2.6.0 
export PATH=.......$SQOOP_HOME/bin:$PATH

将MySQL 8的驱动jar包，添加到SQOOP_HOME的lib目录下，因为我们需要使用Sqoop操作MySQL
查看验证一下是否成功添加MySQL的驱动jar包.

注意：使用hadoop 3.2.0版本的时候，需要在SQOOP_HOME的lib目录下增加commons-lang-2.6.jar

（2）sqoop的使用

导入数据sqoop-import：从MySQL导入HDFS
导出数据sqoop-export：从HDFS导出MySQL
Sqoop的通用参数：

--connect <jdbc-uri>        指定JDBC连接字符串 
--connection-manager <class-name>  指定要使用的连接管理器类 
--driver <class-name>        指定要使用的JDBC驱动类 
--hadoop-mapred-home <dir>      指定$HADOOP_MAPRED_HOME路径 
--help                万能帮助 
--password-file           设置用于存放认证的密码信息文件的路径 
-P                  从控制台读取输入的密码 
--password <password>        设置认证密码 
--username <username>        设置认证用户名 
--verbose              打印详细的运行信息 
--connection-param-file <filename>  可选，指定存储数据库连接参数的属性文件

导入功能相关参数

--append              将数据追加到HDFS上一个已存在的数据集上 
--as-avrodatafile          将数据导入到Avro数据文件 
--as-sequencefile          将数据导入到SequenceFile 
--as-textfile            将数据导入到普通文本文件（默认） 
--boundary-query <statement>    边界查询，用于创建分片（InputSplit） 
--columns <col,col,col…>      从表中导出指定的一组列的数据 
--delete-target-dir         如果指定目录存在，则先删除掉 
--direct              使用直接导入模式（优化导入速度） 
--direct-split-size <n>       分割输入stream的字节大小（在直接导入模式下） 
--fetch-size <n>          从数据库中批量读取记录数 
--inline-lob-limit <n>        设置内联的LOB对象的大小 
-m,--num-mappers <n>        使用n个map任务并行导入数据 
-e,--query <statement>        导入的查询语句 
--split-by <column-name>      指定按照哪个列去分割数据 
--table <table-name>        导入的源表表名 
--target-dir <dir>          导入HDFS的目标路径 
--warehouse-dir <dir>        HDFS存放表的根路径 
--where <where clause>        指定导出时所使用的查询条件 
-z,--compress            启用压缩 
--compression-codec <c>       指定Hadoop的codec方式（默认gzip） 
--null-string <null-string>     如果指定列为字符串类型，使用指定字符串替换值为
--null-non-string <null-string>   如果指定列为非字符串类型，使用指定字符串替换值

导出功能相关参数

选项  含义说明 
--validate <class-name>         启用数据副本验证功能，仅支持单表拷贝，可以
--validation-threshold <class-name>   指定验证门限所使用的类 
--direct                使用直接导出模式（优化速度） 
--export-dir <dir>            导出过程中HDFS源路径 
--m,--num-mappers <n>          使用n个map任务并行导出 
--table <table-name>          导出的目的表名称 
--call <stored-proc-name>        导出数据调用的指定存储过程名 
--update-key <col-name>         更新参考的列名称，多个列名使用逗号分隔 
--update-mode <mode>          指定更新策略，包括：updateonly（默认）、
--input-null-string <null-string>    使用指定字符串，替换字符串类型值为null的列
--input-null-non-string <null-string>  使用指定字符串，替换非字符串类型值为null的
--staging-table <staging-table-name>  在数据导出到数据库之前，数据临时存放的表名
--clear-staging-table          清除工作区中临时存放的数据 
--batch                 使用批量模式导出

（3）Sqoop演示

1) mysql导入Hdfs

(1)使用Sqoop将mall/user表中的数据导入到HDFS中

sqoop import \ 
--connect jdbc:mysql://192.168.1.107:3306/mall?serverTimezone=UTC \ 
--username root \ 
--password root\ 
--table user \ 
--target-dir /out1 \ 
--delete-target-dir \ 
--num-mappers 1 \ 
--fields-terminated-by '\t'

注意：如果表中没有主键则会报错(因为mapper数默认是4，需要分4个Task。但是info表又没有主键，MapReduce不知道以哪个字段为准来分Task。
解决办法有三种：

可以选择在表中设置主键，默认根据主键字段分task
使用–num-mappers 1 ，表示将map任务个数设置为1，sqoop默认是4
使用–split-by ，后面跟上一个数字类型的列，会根据这个列分task

(2)查询导入：使用sql语句查询表中满足条件的数据导入到HDFS里面

sqoop import \ 
--connect jdbc:mysql://192.168.1.107:3306/mall?serverTimezone=UTC \ 
--username root \ 
--password root \ 
--target-dir /out2 \ 
--delete-target-dir \ 
--num-mappers 1 \ 
--fields-terminated-by '\t' \ 
--query 'select id,name from user where id >1 and $CONDITIONS;'

注意：–query和–table不能同时指定

问题：sqoop在导入数据的时候针对空值如何处理？
默认情况下MySQL中的null值(无论字段类型是字符串类型还是数字类型)，使用Sqoop导入到HDFS文件中之后，都会显示为字符串null。

针对字符串null类型：通过–null-string '*'来指定，单引号中指定一个字符即可，这个字符不能是–，因为–是保留关键字
针对非字符串的null类型：通过–null-non-string ‘=’ 来指定，单引号中指定一个字符即可，这个字符不能是–，因为–是保留关键字
这两个参数可以同时设置，这样在导入数据的时候，针对空值字段，会替换为指定的内容。

例如：可以使用\N，因为我们把数据导入到HDFS之后，最终是希望在Hive中查询的，Hive中针对NULL值在底层是使用\N存储的。

2) hdfs导出mysql

sqoop export \ 
--connect jdbc:mysql://192.168.182.1:3306/mall?serverTimezone=UTC \ 
--username root \ 
--password root\ 
--table user2 \ 
--export-dir /out2 \ 
--input-fields-terminated-by '\t'

注意：这里–table指定的表名需要提前创建，sqoop不会自动创建此表。

在导出的时候可以实现插入和更新功能。如果存在就更新，不存在就插入。注意：此时表中必须有一个主键字段

sqoop export \ 
--connect jdbc:mysql://192.168.182.1:3306/imooc?serverTimezone=UTC \ 
--username root \ 
--password admin \ 
--table user2 \ 
--export-dir /out2 \ 
--input-fields-terminated-by '\t' \ 
--update-key id \ 
--update-mode allowinsert

（3）数据采集方式

下面我们就需要使用Sqoop进行数据采集了。将服务端数据库中的表全部导入到HDFS里面。
但是在采集之前，我们需要分析一下，针对数据库中的那些表应该使用什么策略去采集数据采集策略大体上来说有两种

1) 全量采集

针对用户表、商品表等这些实体表，数据量不是特别大，通常可以每天做全量采集，就是每天保存一份完整的数据
如果针对一些维度表，例如：存储城市信息的表，这种表里面的数据一般是几十年都不变的，针对这种表在采集的时候只需要做一次全量采集就可以了，不需要每天都做。
如果表中的数据可能会变，那就只能每天做一次全量采集了。

2) 增量采集

针对订单表、订单商品表，流水表，这些表中的数据是比较多的，如果使用全量的方式，会造成大量的数据冗余，浪费磁盘空间。
所以这种表，一般使用增量的方式，每日采集新增的数据。
在这注意一点：针对订单表，如果单纯的按照订单产生时间增量采集数据，是有问题的，因为用户可能今天下单，明天才支付，但是Hive是不支持数据更新的，这样虽然MySQL中订单的状态改变了，但是Hive中订单的状态还是之前的状态。
想要比较好的解决这个问题，最好是使用拉链表的形式.

3) 总结

表名        说明      导入方式 
user      用户基本信息表    全量 
user_extend   用户信息扩展表    全量 
user_addr    用户收货地址表    全量 
goods_info    商品信息表      全量 
category_code  商品类目码表     全量 
user_order    订单表        增量 
order_item    订单商品表      增量 
order_delivery  订单收货表      增量 
payment_flow  支付流水表      增量

（4）数据采集脚本开发

开发一个通用的sqoop数据采集脚本
在bigdata04机器上创建录/data/soft/warehouse_shell_good_order，针对商品订单相关的脚本全部放在这里面。
创建脚本sqoop_collect_data_util.sh

#!/bin/bash 
# 采集MySQL中的数据导入到HDFS中 
if [ $# != 2 ] 
then  
echo "参数异常：sqoop_collect_data_util.sh <sql> <hdfs_path>" 
exit 100 
fi 
# 数据SQL 
# 例如：select id,name from user where id >1 
sql=$1 
# 导入到HDFS上的路径 
hdfs_path=$2 
sqoop import \ 
--connect jdbc:mysql://192.168.182.1:3306/mall?serverTimezone=UTC \ 
--username root \ 
--password admin \ 
--target-dir  "${hdfs_path}" \ 
--delete-target-dir \ 
--num-mappers 1 \ 
--fields-terminated-by "\t" \ 
--query "${sql}"' and $CONDITIONS;' \ 
--null-string '\\N' \

注意：如果在windows中使用notepad++开发shell脚本的时候，需要将此参数设置为UNIX。

(1)全量数据采集

脚本：collect_data_full.sh

#!/bin/bash 
# 全量数据采集 
# 每天凌晨执行一次 
# 默认获取昨天的日期，也支持传参指定一个日期 
if [ "z$1" = "z" ] 
then 
dt=`date +%Y%m%d --date="1 days ago"` 
else 
dt=$1 
fi 
# SQL语句 
user_sql="select user_id,user_name,user_gender,user_birthday,e_mail,concat(lef
user_extend_sql="select user_id,is_pregnant_woman,is_have_children,is_have_ca
user_addr_sql="select addr_id,user_id,addr_name,order_flag,user_name,concat(l
goods_info_sql="select goods_id,goods_no,goods_name,curr_price,third_category_
category_code_sql="select first_category_id,first_category_name,second_catego
# 路径前缀 
path_prefix="hdfs://bigdata01:9000/data/ods" 
# 输出路径 
user_path="${path_prefix}/user/${dt}"
user_extend_path="${path_prefix}/user_extend/${dt}" 
user_addr_path="${path_prefix}/user_addr/${dt}" 
goods_info_path="${path_prefix}/goods_info/${dt}" 
category_code_path="${path_prefix}/category_code/${dt}" 
# 采集数据 
echo "开始采集..." 
echo "采集表：user" 
sh sqoop_collect_data_util.sh "${user_sql}" "${user_path}" 
echo "采集表：user_extend" 
sh sqoop_collect_data_util.sh "${user_extend_sql}" "${user_extend_path}" 
echo "采集表：user_addr" 
sh sqoop_collect_data_util.sh "${user_addr_sql}" "${user_addr_path}" 
echo "采集表：goods_info" 
sh sqoop_collect_data_util.sh "${goods_info_sql}" "${goods_info_path}" 
echo "采集表：category_code" 
sh sqoop_collect_data_util.sh "${category_code_sql}" "${category_code_path}" 
echo "结束采集..."

(2)增量数据采集

脚本：collect_data_incr.sh


#!/bin/bash
# 增量数据采集
# 每天凌晨执行一次

# 默认获取昨天的日期，也支持传参指定一个日期
if [ "z$1" = "z" ]
then
dt=`date +%Y%m%d --date="1 days ago"`
else
dt=$1
fi

# 转换日期格式，20260101 改为 2026-01-01
dt_new=`date +%Y-%m-%d --date="${dt}"`


# SQL语句
user_order_sql="select order_id,order_date,user_id,order_money,order_type,order_status,pay_id,update_time from user_order where order_date >= '${dt_new} 00:00:00' and order_date <= '${dt_new} 23:59:59'"
order_item_sql="select order_id,goods_id,goods_amount,curr_price,create_time from order_item where create_time >= '${dt_new} 00:00:00' and create_time <= '${dt_new} 23:59:59'"
order_delivery_sql="select order_id,addr_id,user_id,carriage_money,create_time from order_delivery where create_time >= '${dt_new} 00:00:00' and create_time <= '${dt_new} 23:59:59'"
payment_flow_sql="select pay_id,order_id,trade_no,pay_money,pay_type,pay_time from payment_flow where pay_time >= '${dt_new} 00:00:00' and pay_time <= '${dt_new} 23:59:59'"

# 路径后缀
path_prefix="hdfs://bigdata01:9000/data/ods"

# 输出路径
user_order_path="${path_prefix}/user_order/${dt}"
order_item_path="${path_prefix}/order_item/${dt}"
order_delivery_path="${path_prefix}/order_delivery/${dt}"
payment_flow_path="${path_prefix}/payment_flow/${dt}"

# 采集数据
echo "开始采集..."
echo "采集表：user_order"
sh sqoop_collect_data_util.sh "${user_order_sql}" "${user_order_path}"
echo "采集表：order_item"
sh sqoop_collect_data_util.sh "${order_item_sql}" "${order_item_path}"
echo "采集表：order_delivery"
sh sqoop_collect_data_util.sh "${order_delivery_sql}" "${order_delivery_path}"
echo "采集表：payment_flow"
sh sqoop_collect_data_util.sh "${payment_flow_sql}" "${payment_flow_path}"
echo "结束采集..."