数仓搭建（四）


文章目录

  • 数仓搭建（四）
  • 一、数仓理论
  • 1.1 表的分类
  • 1.1.1 实体表
  • 1.1.2 维度表
  • 1.1.3 事务型事实表
  • 1.1.4 周期型事实表
  • 1.2 同步策略
  • 1.2.1 实体表同步策略
  • 1.2.2 维度表同步策略
  • 1.2.3 事务型事实表同步策略
  • 1.2.4 周期型事实表同步策略
  • 1.3 范式理论
  • 1.3.1 范式概念
  • 1.3.2 函数依赖
  • 1.3.3 三范式
  • 第一范式：属性不可分割
  • 第二范式：不能存在部分函数依赖
  • 第三范式： 不能存在传递函数依赖
  • 1.4 关系建模和维度建模
  • 1.5 雪花模型、星型模型和星座模型
  • 二、Sqoop的安装
  • 2.1 解压
  • 2.2 修改配置文件
  • 2.3 拷贝JDBC驱动
  • 2.4 验证Sqoop
  • 2.5 测试Sqoop是否能够成功连接数据库
  • 2.6 创建导入脚本


一、数仓理论

1.1 表的分类

1.1.1 实体表

实体表，一般是指一个现实存在的业务对象，比如用户，商品，商家，销售员等等。

用户表：

用户id

姓名

生日

性别

邮箱

用户等级

创建时间

1

张三

2011-11-11

男

zs@163.com

2

2018-11-11

2

李四

2011-11-11

女

ls@163.com

3

2018-11-11

3

王五

2011-11-11

中性

ww@163.com

1

2018-11-11

…

…

…

…

…

…

…

1.1.2 维度表

维度表，一般是指对应一些业务状态，编号的解释表。也可以称之为码表。

比如地区表，订单状态，支付方式，审批状态，商品分类等等。

订单状态表：

订单状态编号

订单状态名称

1

未支付

2

支付

3

发货中

4

已发货

5

已完成

商品分类表：

商品分类编号

分类名称

1

服装

2

保健

3

电器

4

图书

1.1.3 事务型事实表

事务型事实表，一般指随着业务发生不断产生数据。特点是一旦发生不会再变化。

一般比如，交易流水，操作日志，出库入库记录等等。

交易流水表：

编号

对外业务编号

订单编号

用户编号

支付宝交易流水编号

支付金额

交易内容

支付类型

支付时间

1

7577697945

1

111

QEyF-63000323

223.00

海狗人参丸1

alipay

2019-02-10 00:50:02

2

0170099522

2

222

qdwV-25111279

589.00

海狗人参丸2

wechatpay

2019-02-10 00:50:02

3

1840931679

3

666

hSUS-65716585

485.00

海狗人参丸3

unionpay

2019-02-10 00:50:02

。。。

。。。

。。。

。。。

。。。

。。。

。。。

。。。

。。。

1.1.4 周期型事实表

周期型事实表，一般指随着业务发生不断产生变化(更新, 新增)的数据。

与事务型不同的是，数据会随着业务周期性的推进而变化。

比如订单，其中订单状态会周期性变化。再比如，请假、贷款申请，随着批复状态在周期性变化。

订单表：

订单编号

订单金额

订单状态

用户id

支付方式

支付流水号

创建时间

操作时间

1

223.00

2

111

alipay

QEyF-63000323

2019-02-10 00:01:29

2019-02-10 00:01:29

2

589.00

2

222

wechatpay

qdwV-25111279

2019-02-10 00:05:02

2019-02-10 00:05:02

3

485.00

1

666

unionpay

hSUS-65716585

2019-02-10 00:50:02

2019-02-10 00:50:02

。。。

。。。

。。。

。。。

。。。

。。。

。。。

。。。

1.2 同步策略

数据同步策略的类型包括：全量表、增量表、新增及变化表、拉链表

Ø 全量表：存储完整的数据。

Ø 增量表：存储新增加的数据。

Ø 新增及变化表：存储新增加的数据和变化的数据。

Ø 拉链表：对新增及变化表做定期合并。

1.2.1 实体表同步策略

实体表：比如用户，商品，商家，销售员等

实体表数据量比较小：通常可以做每日全量，就是每天存一份完整数据。即每日全量。

1.2.2 维度表同步策略

维度表：比如订单状态，审批状态，商品分类

维度表数据量比较小：通常可以做每日全量，就是每天存一份完整数据。即每日全量。

说明：

1）针对可能会有变化的状态数据可以存储每日全量。

2）没变化的客观世界的维度（比如性别，地区，民族，政治成分，鞋子尺码）可以只存一份固定值。

1.2.3 事务型事实表同步策略

事务型事实表：比如，交易流水，操作日志，出库入库记录等。

因为数据不会变化，而且数据量巨大，所以每天只同步新增数据即可，所以可以做成每日增量表，即每日创建一个分区存储。

1.2.4 周期型事实表同步策略

周期型事实表：比如，订单、请假、贷款申请等

这类表从数据量的角度，存每日全量的话，数据量太大，冗余也太大。如果用每日增量的话无法反应数据变化。

每日新增及变化量，包括了当日的新增和修改。一般来说这个表，足够计算大部分当日数据的。但是这种依然无法解决能够得到某一个历史时间点（时间切片）的切片数据。

所以要用利用每日新增和变化表，制作一张拉链表，以方便的取到某个时间切片的快照数据。所以我们需要得到每日新增及变化量。

拉链表：

name姓名

start新名字创建时间

end名字更改时间

张三

1990/1/1

2018/12/31

张小三

2019/1/1

2019/4/30

张大三

2019/5/1

9999-99-99

。。。

。。。

。。。

select * from user where start =<’2019-1-2’ and end>=’2019-1-2’

1.3 范式理论

1.3.1 范式概念

关系型数据库设计时，遵照一定的规范要求，目的在于降低数据的冗余性，目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。

范式可以理解为设计一张数据表的表结构，符合的标准级别。

使用范式的根本目的是：

1）减少数据冗余，尽量让每个数据只出现一次。

2）保证数据一致性

缺点是获取数据时，需要通过Join拼接出最后的数据。

1.3.2 函数依赖

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MdRgbHoF-1627222465884)(images/Screenshot%202021-07-23%20073442.jpg)]

1.3.3 三范式
第一范式：属性不可分割

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1xSVcIYM-1627222465887)(images/Screenshot%202021-07-23%20073927.jpg)]

第二范式：不能存在部分函数依赖

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TzGQIP7n-1627222465889)(images/Screenshot%202021-07-23%20074020.jpg)]

第三范式： 不能存在传递函数依赖

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9tCPjWJt-1627222465891)(images/Screenshot%202021-07-23%20074056.jpg)]

1.4 关系建模和维度建模

关系模型主要应用与OLTP系统中，为了保证数据的一致性以及避免冗余，所以大部分业务系统的表都是遵循第三范式的。

维度模型主要应用于OLAP系统中，因为关系模型虽然冗余少，但是在大规模数据，跨表分析统计查询过程中，会造成多表关联，这会大大降低执行效率。

所以把相关各种表整理成两种：事实表和维度表两种。所有维度表围绕着事实表进行解释。

对比属性

OLTP

OLAP

读特性

每次查询只返回少量记录

对大量记录进行汇总

写特性

随机、低延时写入用户的输入

批量导入

使用场景

用户，Java EE项目

内部分析师，为决策提供支持

数据表征

最新数据状态

随时间变化的历史状态

数据规模

GB

TP到PB

1.5 雪花模型、星型模型和星座模型

雪花模型与星型模型的区别主要在于维度的层级，标准的星型模型维度只有一层，而雪花模型可能会涉及多级。

星座模型与前两种情况的区别是事实表的数量，星座模型是基于多个事实表。

基本上是很多数据仓库的常态，因为很多数据仓库都是多个事实表的。所以星座不星座只反映是否有多个事实表，他们之间是否共享一些维度表。

所以星座模型并不和前两个模型冲突。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lsWYrPGZ-1627222465892)(images/Screenshot%202021-07-23%20074904.jpg)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ko6ztecm-1627222465893)(images/Screenshot%202021-07-23%20074919.jpg)]

二、Sqoop的安装

2.1 解压

2.2 修改配置文件

Sqoop的配置文件与大多数大数据框架类似，在sqoop根目录下的conf目录中。

1) 重命名配置文件

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

2) 修改配置文件

sqoop-env.sh

export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2

export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2

export HIVE_HOME=/opt/module/hive

export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10

export ZOOCFGDIR=/opt/module/zookeeper-3.4.10

export HBASE_HOME=/opt/module/hbase

2.3 拷贝JDBC驱动

拷贝jdbc驱动到sqoop的lib目录下，如：

$ cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/lib/

2.4 验证Sqoop

我们可以通过某一个command来验证sqoop配置是否正确：

$ bin/sqoop help

出现一些Warning警告（警告信息已省略），并伴随着帮助命令的输出：

Available commands:

 codegen      Generate code to interact with database records

 create-hive-table   Import a table definition into Hive

 eval        Evaluate a SQL statement and display the results

 export       Export an HDFS directory to a database table

 help        List available commands

 import       Import a table from a database to HDFS

 import-all-tables   Import tables from a database to HDFS

 import-mainframe  Import datasets from a mainframe server to HDFS

 job        Work with saved jobs

 list-databases    List available databases on a server

 list-tables      List available tables in a database

 merge       Merge results of incremental imports

 metastore      Run a standalone Sqoop metastore

 version      Display version information

2.5 测试Sqoop是否能够成功连接数据库

$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password 000000

出现如下输出：

information_schema
metastore
mysql
oozie
performance_schema

2.6 创建导入脚本

1）在/home/atguigu/bin目录下创建脚本sqoop_import.sh

[guochao@hadoop102 bin]$ vim sqoop_import.sh

在脚本中填写如下内容

#!/bin/bash

db_date=$2
echo $db_date
db_name=gmall

import_data() {
/opt/module/sqoop/bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/$db_name \
--username root \
--password 000000 \
--target-dir /origin_data/$db_name/db/$1/$db_date \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query "$2"' and $CONDITIONS;'
}

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

2）增加脚本执行权限

[guochao@hadoop102 bin]$ chmod 777 sqoop_import.sh

3）执行脚本导入数据

[atguigu@hadoop102 bin]$ sqoop_import.sh all 2019-02-10

4）在SQLyog中生成2019年2月11日数据

CALL init_data('2019-02-11',1000,200,300,TRUE);

5）执行脚本导入数据

[atguigu@hadoop102 bin]$ sqoop_import.sh all 2019-02-11