本地数仓项目(二)——搭建系统业务数仓详细流程

1 说明

本文基于《本地数据仓库项目(一)——本地数仓搭建详细流程》业务数据，在本地搭建系统业务数仓。
根据模拟sql脚本生成业务数据，依次执行生成业务数据即可。

sql脚本提供如下

链接：https://pan.baidu.com/s/1AhLIuTNIyJ_GBD7M0b2RoA 
提取码：1lm8 

生成的数据如下：

2 业务数据导入数仓

数仓整体框架如下，在前面的《本地数据仓库项目(一)——本地数仓搭建详细流程》已完成对数据采集及分析整体流程。这里的业务数仓数据需要用到sqoop完成从mysql导入数据到HDFS中。

2.1 安装sqoop

2.1.1 解压并重命名

tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz

mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha sqoop-1.4.6

2.1.2 配置SQOOP_HOME环境变量

SQOOP_HOME=/root/soft/sqoop-1.4.6
PATH=$PATH:$JAVA_HOME/bin:$SHELL_HOME:$FLUME_HOME/bin:$HIVE_HOME/bin:$KAFKA_HOME/bin:$ZOOKEEPER_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$SQOOP_HOME/bin

2.1.3 配置sqoop-env.sh

mv sqoop-env-template.sh sqoop-env.sh

export HADOOP_COMMON_HOME=/root/soft/hadoop-2.7.2
export HADOOP_MAPRED_HOME=/root/soft/hadoop-2.7.2
export HIVE_HOME=/root/soft/hive
export ZOOKEEPER_HOME=/root/soft/zookeeper-3.4.10
export ZOOCFGDIR=/root/soft/zookeeper-3.4.10

2.1.4拷贝mysql的jdbc驱动到sqoop的lib目录下

2.1.5 测试链接

bin/sqoop list-databases --connect jdbc:mysql://192.168.2.100:3306/ --username root --password 123456

出现如下页面表示sqoop安装成功

2.2 sqoop导入数据到HDFS

如下sqoop脚本，可实现定时自动导入数据到HDFS

#!/bin/bash

db_date=$2
echo $db_date
db_name=gmall

import_data() {
   
   
/root/soft/sqoop-1.4.6/bin/sqoop import \
--connect jdbc:mysql://192.168.2.100:3306/$db_name \
--username root \
--password 123456 \
--target-dir /origin_data/$db_name/db/$1/$db_date \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query "$2"' and $CONDITIONS;' \
--null-string '\\N' \
--null-non-string '\\N'
}

import_sku_info(){
   
   
  import_data "sku_info" "select 
id, spu_id, price, sku_name, sku_desc, weight, tm_id,
category3_id, create_time
  from sku_info where 1=1"
}

import_user_info(){
   
   
  import_data "user_info" "select 
id, name, birthday, gender, email, user_level, 
create_time 
from user_info where 1=1"
}

import_base_category1(){
   
   
  import_data "base_category1" "select 
id, name from base_category1 where 1=1"
}

import_base_category2(){
   
   
  import_data "base_category2" "select 
id, name, category1_id from base_category2 where 1=1"
}

import_base_category3(){
   
   
  import_data "base_category3" "select id, name, category2_id from base_category3 where 1=1"
}

import_order_detail(){
   
   
  import_data   "order_detail"   "select 
    od.id, 
    order_id, 
    user_id, 
    sku_id, 
    sku_name, 
    order_price, 
    sku_num, 
    o.create_time  
  from order_info o, order_detail od
  where o.id=od.order_id
  and DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date'"
}

import_payment_info(){
   
   
  import_data "payment_info"   "select 
    id,  
    out_trade_no, 
    order_id, 
    user_id, 
    alipay_trade_no, 
    total_amount,  
    subject, 
    payment_type, 
    payment_time 
  from payment_info 
  where DATE_FORMAT(payment_time,'%Y-%m-%d')='$db_date'"
}

import_order_info(){
   
   
  import_data   "order_info"   "select 
    id, 
    total_amount, 
    order_status, 
    user_id, 
    payment_way, 
    out_trade_no, 
    create_time, 
    operate_time  
  from order_info 
  where (DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date' or DATE_FORMAT(operate_time,'%Y-%m-%d')='$db_date')"
}

case $1 in
  "base_category1")
     import_base_category1
;;
  "base_category2")
     import_base_category2
;;
  "base_category3")
     import_base_category3
;;
  "order_info")
     import_order_info
;;
  "order_detail")
     import_order_detail
;;
  "sku_info")
     import_sku_info
;;
  "user_info")
     import_user_info
;;
  "payment_info")
     import_payment_info
;;
   "all")
   import_base_category1
   import_base_category2
   import_base_category3
   import_order_info
   import_order_detail
   import_sku_info
   import_user_info
   import_payment_info
;;
esac

注意：
①默认sqoop到import数据时，将Mysql的Null类型，转为’null’
②hive中使用\N代表NULL类型
③如果希望在import时，讲将Mysql的Null类型，转为自己期望的类型，
需要使用–null-string and --null-non-string

–null-string： 当mysql的string类型列为null时，导入到hive时，使用什么来代替！
–null-string a: 如果mysql中，当前列是字符串类型(varchar,char)，假如这列值为NULL，导入到hive时，使用a来代替！
–null-non-string： 当mysql的非string类型列为null时，导入到hive时，使用什么来代替！
–null-non-string b: 如果mysql中，当前列不是字符串类型(varchar,char)，假如这列值为NULL，导入到hive时，使用b来代替！

④如果到导出时，希望将指定的参数，导出为mysql的NULL类型，需要使用

–input-null-string and --input-null-non-string --input-null-string a： 在hive导出到mysql时，如果hive中string类型的列的值为a,导出到mysql中，使用NULL代替！
–input-null-non-string b:
在hive导出到mysql时，如果hive中非string类型的列的值为b,导出到mysql中，使用NULL代替！

执行脚本，导入数据

3 ODS层

3.1 创建ods表

3.1.1 创建订单表

drop table if exists ods_order_info;
create external table ods_order_info (
    `id` string COMMENT '订单编号',
    `total_amount` decimal(10,2) COMMENT '订单金额',
    `order_status` string COMMENT '订单状态',
    `user_id` string COMMENT '用户id',
    `payment_way` string COMMENT '支付方式',
    `out_trade_no` string COMMENT '支付流水号',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间'
) COMMENT '订单表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
location '/wavehouse/gmall/ods/ods_order_info/';

3.1.2 创建订单明细表

drop table if exists ods_order_detail;
create external table ods_order_detail( 
    `id` string COMMENT '订单详情编号',
    `order_id` string  COMMENT '订单号', 
    `user_id` string COMMENT '用户id',
    `sku_id` string COMMENT '商品id',
    `sku_name` string COMMENT '商品名称',
    `order_price` string COMMENT '商品单价',
    `sku_num` string COMMENT '商品数量',
    `create_time` string COMMENT '创建时间'
) COMMENT '订单明细表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t' 
location '/wavehouse/gmall/ods/ods_order_detail/';

3.1.3 创建商品信息表

drop table if exists ods_sku_info;
create external table ods_sku_info( 
    `id` string COMMENT 'skuId',
    `spu_id` string   COMMENT 'spuid', 
    `price` decimal(10,2) COMMENT '价格',
    `sku_name` string COMMENT '商品名称',
    `sku_desc` string COMMENT '商品描述',
    `weight` string COMMENT '重量',
    `tm_id` string COMMENT '品牌id',
    `category3_id` string COMMENT '品类id',
    `create_time` string COMMENT '创建时间'
) COMMENT '商品表'
PARTITIONED BY (`dt` string)
row format delimited fields terminated by '\t'
location '/wavehouse/gmall/ods/ods_sku_info/'