业务数据调研及ETL

一、系统介绍

整个用户画像（UserProfile）项目中，数据、业务及技术流程图如下所示：

其中数据源存储在业务系统数据库：MySQL 数据库中，采用SQOOP全量/增量将数据抽取到HDFS（Hive表中），通过转换为HFile文件加载到HBase表。

• 1）、编写MapReduce程序
• 2）、编写Spark程序（推荐使用Spark编程）

1）、为什么将【订单相关数据：订单数据和订单商品数据】存储到HBase表中？？？？

• 特点：数据量比较大
• 存储HBase：存储海量数据、查询检索

2）、实际项目来说【访问行为日志】数据存储到Hive表中

• 数据仓库分层：
  • ODS层、DW层和APP层

3）、特殊：模拟的所有业务数据存储在RDBMs表中，为了简化整个项目开发，重点在于标签开发，将所有数据迁移到HBase表中。

二、电商数据 2.1、MySQL数据库

在数据库【 tags_dat 】中包含四张表：

1）、用户表：tbl_users
2）、订单数据表：tbl_orders
3）、订单商品表：tbl_goods
4）、行为日志表：tbl_logs

2.2、Hive 数据仓库

将MySQL数据库中表的数据导入到Hive表中，以便加载到HBase表中。

2.2.1、创建表

创建Hive中数据库Database：

CREATE DATABASE tags_dat;

根据MySQL数据库表在Hive数据仓库中构建相应的表：

2.2.1.1、用户信息表： tbl_users

sqoop create-hive-table \
--connect jdbc:mysql://chb1:3306/tags_dat \
--table tbl_users \
--username root \
--password 123456 \
--hive-database tags_dat \
--hive-table tbl_users \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

2.2.1.2、创建hive表通用脚本

# 数据库配置  db.sh 
#!/bin/bash
export host="mysql-host"
export db="tags_dat"
export user="root"
export pwd="123456"


# 创建表脚本 createHiveTable.sh 
#!/bin/bash
source ./db.sh
# tableName="tbl_users"
tableName=$1
sqoop create-hive-table \
--connect jdbc:mysql://$host:3306/$db \
--table $tableName \
--username $user \
--password $pwd \
--hive-database $db \
--hive-table $tableName \
--fields-terminated-by '\t' \
--lines-terminated-by '\n'

执行
sh createHiveTable.sh tbl_users
sh createHiveTable.sh tbl_orders
sh createHiveTable.sh tbl_goods
sh createHiveTable.sh tbl_logs

2.2.2、导入数据至Hive表

使用Sqoop将MySQL数据库表中的数据导入到Hive表中（本质就是存储在HDFS上），具体命令如下：

2.2.2.1、用户信息表： tbl_users

/sqoop import \
--connect jdbc:mysql://chb1:3306/tags_dat \
--username root \
--password 123456 \
--table tbl_users \
--direct \
--hive-overwrite \
--delete-target-dir \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--hive-database tags_dat \
--hive-table tbl_users \
--hive-import \
--num-mappers 1

2.2.2.2、通用同步数据到hive脚本

导数据脚本  importMysqlToHive.sh 
#!/bin/bash
source ./db.sh
#tableName="tbl_users"
tableName=$1

sqoop import \
--connect jdbc:mysql://$host:3306/$db \
--username $user \
--password $pwd \
--table $tableName \
--direct \
--hive-overwrite \
--delete-target-dir \
--fields-terminated-by '\t' \
--lines-terminated-by '\n' \
--hive-database $db \
--hive-table $tableName \
--hive-import \
--num-mappers 10

执行
sh importMysqlToHive.sh tbl_goods
sh importMysqlToHive.sh tbl_logs
sh importMysqlToHive.sh tbl_orders
sh importMysqlToHive.sh tbl_users

2.3、数据导入到 Hbase

将MySQL表中业务数据导入大数据平台中如HBase表，方案如下所示：

2.3.1、HBase 表设计

电商网站中各类数据（用户信息数据、用户访问日志数据及用户订单数据）存储到HBase表中，便于检索和分析构建电商用户画像，有如下几张表：

create 'tbl_users','detail'
create 'tbl_orders','detail'
create 'tbl_goods','detail'

create 'tbl_logs', 'detail', SPLITS => ['189394']

2.3.2、Sqoop直接导入

可以使用sqoop将MySQL表的数据导入到HBase表中，指定 表的名称、列簇及RowKey ，范例如下所示：

sqoop import \
-D sqoop.hbase.add.row.key=true \
--connect jdbc:mysql://chb1:3306/tags_dat \
--username root \
--password 123456 \
--table tbl_users \
--hbase-create-table \
--hbase-table tbl_users \
--column-family detail \
--hbase-row-key id \
--num-mappers 2

参数含义解释：

• 1、-D sqoop.hbase.add.row.key=true
  • 是否将rowkey相关字段写入列族中，默认为false，默认情况下你将在列族中看不到任何rowkey中的字段。注意，该参数必须放在import之后。
• 2、–hbase-create-table 如果hbase中该表不存在则创建
• 3、–hbase-table 对应的hbase表名
• 4、–hbase-row-key hbase表中的rowkey,注意格式
• 5、–column-family hbase表的列族

知识拓展：如何使用SQOOP进行增量导入数据至HBase表，范例命令如下：

sqoop import \
-D sqoop.hbase.add.row.key=true \
--connect jdbc:mysql://chb1:3306/tags_dat \
--username root \
--password 123456 \
--table tbl_logs \
--hbase-create-table \
--hbase-table tag_logs \
--column-family detail \
--hbase-row-key id \
--num-mappers 20 \
--incremental lastmodified \
--check-column log_time \
--last-value '2019-08-13 00:00:00' \

相关增量导入参数说明：

• 1、–incremental lastmodified 增量导入支持两种模式 append 递增的列；lastmodified 时间戳。
• 2、–check-column 增量导入时参考的列
• 3、–last-value 最小值，这个例子中表示导入2019-08-13 00:00:00到今天的值

使用SQOOP导入数据到HBase表中，有一个限制：   需要指定RDBMs表中的某个字段作为HBase表的ROWKEY，如果HBase表的ROWKEY为多个字段组合，就无法指定，所以此种方式有时候不能使用。

2.3.3、importtsv

缺点

• 1）、ROWKEY不能是组合主键
  • 只能是某一个字段
• 2）、当表中列很多时，书写-Dimporttsv.columns值时很麻烦，容易出错

2.3.4、HBase Bulkload（推荐）

完整代码

2.3.4.1、注意

• 1、生成hfile， rowkey必须有序
• 2、hase-2.0.2之后，对列也要求有序
• 注意 LoadIncrementalHFiles 的包变化， HFileOutputFormat2· 的包变化

2.2.4.2、执行同步hive数据到hbase

[root@chb1 ~]# cat hive2Hbase.sh 
#!/bin/bash


jars=''
for file in `\ls  /opt/cloudera/parcels/CDH/jars/hbase*`
do
  jars+=$file","
done
echo $jars



tableName=$1

spark-submit \
--jars $jars \
--master yarn \
--class com.chb.tags.etl.hfile.HBaseBulkLoader \
tag-etl-1.0-SNAPSHOT.jar 1 $tableName detail /user/hive/warehouse/tags_dat.db/$tableName /datas/output_hfile/$tableName
[root@chb1 ~]# 
[root@chb1 ~]# 


sh hive2Hbase.sh tbl_logs
sh hive2Hbase.sh tbl_goods
sh hive2Hbase.sh tbl_orders
sh hive2Hbase.sh tbl_users