在线教育项目——数仓实战（二）_在线教育数据仓库技术可行性

1. 教育项目的数仓分层

1.1 原有的基础分层

• ODS层: 源数据层

  • 作用: 对接数据源, 和数据源的数据保持相同的粒度(将数据源的数据完整的拷贝到ODS层中)
  • 注意:
    如果数据来源于文本文件, 可能会需要先对这些文本文件进行预处理(spark)操作, 将其中不规则的数据, 不完整的数据, 脏乱差的数据先过滤掉, 将其转换为一份结构化的数据, 然后灌入到ODS层

• DW层: 数据仓库层

  作用: 进行数据分析的操作

• DA层: 数据应用层

  作用: 存储DW层分析的结果, 用于对接后续的应用(图表, 推荐系统…)

1.2 教育数仓中

• ODS层: 源数据层

  • 作用:

    ​ 对接数据源, 和数据源的数据保持相同的粒度(将数据源的数据完整的拷贝到ODS层中)

  • 注意:
    如果数据来源于文本文件, 可能会需要先对这些文本文件进行预处理(spark)操作, 将其中不规则的数据, 不完整的数据, 脏乱差的数据先过滤掉, 将其转换为一份结构化的数据, 然后灌入到ODS层

  • 一般放置 事实表数据和少量的维度表数据

• DW层: 数据仓库层

  • DWD层: 明细层

    • 作用:

      ​ 用于对ODS层数据进行清洗转换工作 , 以及进行少量的维度退化操作

    • 少量:

      1. 多个事实表的数据合并为一个事实表操作
      2. 如果维度表放置在ODS层 一般也是在DWD层完成维度退化

  • DWM层: 中间层

    作用:

    1. 用于进行维度退化操作
    2. 用于进行提前聚合操作(周期快照事实表)

  • DWS层: 业务层

    作用: 进行细化维度统计分析操作

• DA层: 数据应用层

  • 作用: 存储基于DWS层再次分析的结果, 用于对接后续的应用(图表, 推荐系统…)

  • 例如

    ​ DWS层的数据表完成了基于订单表各项统计结果信息, 但是图表只需要其中销售额, 此时从DWS层将销售额的数据提取出来存储到DA层

• DIM层: 维度层

  作用: 存储维度表数据

维度退化

• 是为了减少维度表的关联工作

• 做法:

  ​ 将数据分析中可能在维度表中需要使用的字段, 将这些字段退化到事实表中；

  ​ 这样后续在基于维度统计的时候, 就不需要在关联维度表, 事实表中已经涵盖了维度数据了

• 例如:

  ​ 订单表, 原有订单表中只有用户id, 当我们需要根据用户维度进行统计分析的时候, 此时需要关联用户表, 找到用户的名称；

  ​ 那么如果我们提前将用户的名称放置到订单表中, 那么是不是就不需要关联用户表, 而这就是维度退化

2. 数仓工具的使用

2.1 HUE

• HUE: hadoop 用户体验
• 出现目的: 提升使用hadoop生态圈中相关软件的便利性
• 核心: 是将各类hadoop生态圈的软件的操作界面集成在一个软件中（大集成者）
• 进入HUE界面
  
  

2.2 HUE操作Oozie

• 什么是Oozie?

  • 一个用于管理Apache Hadoop作业的工作流调度程序系统
  • 由Cloudera公司贡献给Apache的基于工作流引擎的开源框架,是用于Hadoop平台的开源的工作流调度引擎,是用来管理Hadoop作业,属于web应用程序
  • 由Oozie client和Oozie Server两个组件构成,Oozie Server运行于Java Servlet容器（Tomcat）中的web程序。

• 什么是工作流?

  工作流（Workflow），指“业务过程的部分或整体在计算机应用环境下的自动化”。

• 能够使用工作流完成的业务一般具有什么特点?

  1. 整个业务流程需要周期性重复干
  2. 整个业务流程可以被划分为多个阶段
  3. 每一个阶段存在依赖关系,前序没有操作, 后续也无法执行

  如果发现实际生产中的某些业务满足了以上特征, 就可以尝试使用工作流来解决

• 请问, 大数据的工作流程是否可以使用工作流来解决呢? 完全可以的

  

• 请问: 如何实现一个工作流呢? 已经有爱心人士将工作流软件实现了, 只需要学习如何使用这些软件配置工作流程即可

  • azkaban:

    ​ 来源于领英公司, 配置工作流的方式是通过类似于properties文件的方式来配置, 只需要简单的几行即可配置

    ​ 提供了一个非常的好可视化界面, 通过界面可以对工作流进行监控管理

    ​ 号称 只要能够被shell所执行, azkaban都可以进行调度, 所以azkaban就是一个shell客户端软件

  • Oozie:

    ​ 来源于apache, 出现时间较早一款工作流调度工具, 整个工作流的配置主要采用XML方式进行配置, 整个XML配置是非常繁琐的, 如果配置一个MR, 相当于将MR重写一遍

    ​ 而且虽然提供了一个管理界面, 但是这个界面仅能查看, 无法进行操作, 而且界面异常卡顿

  • 总结:
    azkaban要比Oozie更加好用

• 如何和HUE结合使用:

  • azkaban由于不属于apache旗下, 所以无法和HUE集成

  • HUE是属于apache旗下的, 所以HUE像集成一款工作流的调度工具, 肯定优先集成自家产品

  • ooize也是属于apache旗下的, HUE对Oozie是可以直接集成的, 集成之后, 只需要用户通过鼠标的方式点一点即可实现工作流的配置

  • 总结:
    HUE加入后, Oozie要比azkaban更加好用

  Oozie本质是将工作流翻译为MR程序来运行

2.3 Sqoop相关的操作

Sqoop是隶属于Apache旗下的, 最早是属于cloudera公司的,是一个用户进行数据的导入导出的工具

主要是将关系型的数据库(MySQL, oracle…)导入到hadoop生态圈(HDFS,HIVE,Hbase…) , 以及将hadoop生态圈数据导出到关系型数据库中

将导入或导出命令翻译成mapreduce程序来实现。

• 通过Sqoop将数据导入到HIVE主要有两种方式: 原生API 和 hcatalog API

  • 数据格式支持:
    • 原生API——仅支持 textFile 格式
    • hcatalog API——支持多种hive的存储格式(textFil、ORC、sequenceFile、parquet…)
  • 数据覆盖:
    • 原生API——支持数据覆盖操作
    • hcatalog API——不支持数据覆盖,每一次都是追加操作
  • 字段名:
    • 原生API——字段名比较随意, 更多关注字段的顺序, 会将关系型数据库的第一个字段给hive表的第一个字段…
    • hcatalog API——按照字段名进行导入操作, 不关心顺序
    • 建议: 在导入的时候, 不管是顺序还是名字都保持一致

​ 目前主要采用 hcatalog 的方式

2.3.1 Sqoop的基本操作

注：在命令行中执行

• sqoop help查看命令帮助文档

  

• sqoop list-databases --help查看某一个命令帮助文档

• 查看mysql中有那些库

  [root@hadoop01 ~]# sqoop list-databases --connect jdbc:mysql://192.168.52.150:3306 --username root --password 123456
  1

  

• 查看mysql中hue数据库下所有的表

  [root@hadoop01 ~]# sqoop list-tables \
  > --connect jdbc:mysql://192.168.52.150:3306/hue \
  > --username root \
  > --password 123456 
  
  注意:
  	\ 表示当前命令没有写完, 换行书写
  1
  2
  3
  4
  5
  6
  7

2.3.2 Sqoop的数据导入操作

1. 数据准备工作

mysql 中执行 (可使用Navicat连接数据库来执行命令)

create database test default character set utf8mb4 collate utf8mb4_unicode_ci;
use test;

create table emp
(
    id     int  not null   primary key,
    name   varchar(32) null,
    deg    varchar(32) null,
    salary int         null,
    dept   varchar(32) null
);

INSERT INTO emp (id, name, deg, salary, dept) VALUES (1201, 'gopal', 'manager', 50000, 'TP');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1202, 'manisha', 'Proof reader', 50000, 'TP');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1203, 'khalil', 'php dev', 30000, 'AC');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1204, 'prasanth', 'php dev', 30000, 'AC');
INSERT INTO emp (id, name, deg, salary, dept) VALUES (1205, 'kranthi', 'admin', 20000, 'TP');

create table emp_add
(
    id     int         not null
        primary key,
    hno    varchar(32) null,
    street varchar(32) null,
    city   varchar(32) null
);

INSERT INTO emp_add (id, hno, street, city) VALUES (1201, '288A', 'vgiri', 'jublee');
INSERT INTO emp_add (id, hno, street, city) VALUES (1202, '108I', 'aoc', 'sec-bad');
INSERT INTO emp_add (id, hno, street, city) VALUES (1203, '144Z', 'pgutta', 'hyd');
INSERT INTO emp_add (id, hno, street, city) VALUES (1204, '78B', 'old city', 'sec-bad');
INSERT INTO emp_add (id, hno, street, city) VALUES (1205, '720X', 'hitec', 'sec-bad');

create table emp_conn
(
    id    int         not null
        primary key,
    phno  varchar(32) null,
    email varchar(32) null
);

INSERT INTO emp_conn (id, phno, email) VALUES (1201, '2356742', 'gopal@tp.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1202, '1661663', 'manisha@tp.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1203, '8887776', 'khalil@ac.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1204, '9988774', 'prasanth@ac.com');
INSERT INTO emp_conn (id, phno, email) VALUES (1205, '1231231', 'kranthi@tp.com');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

2. 如何将数据从mysql中导入到HDFS中 (全量)

以emp表为例:

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp
1
2
3
4
5
6
7

说明:
默认情况下, 会将数据导入到操作sqoop用户的HDFS的家目录下,在此目录下会创建一个以导入表的表名为名称文件夹, 在此文件夹下莫每一条数据会运行一个mapTask, 数据的默认分隔符号为 “,”

• 更改其默认的位置

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp \
> --delete-target-dir \
> --target-dir '/sqoop_works/emp_1'
1
2
3
4
5
6
7

• 调整map的数量

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp \
> --delete-target-dir \
> --target-dir '/sqoop_works/emp_2' \
> --split-by id \
> -m 2
1
2
3
4
5
6
7
8
9

• 调整默认分隔符号，比如调整为’\001’

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp \
> --fields-terminated-by '\001' \
> --delete-target-dir \
> --target-dir '/sqoop_works/emp_3' \
> -m 1
1
2
3
4
5
6
7
8
9

3. 从mysql导入数据到Hive中(全量)

• 第一步：在HIVE中创建一个目标表

create database hivesqoop;
use hivesqoop;
create table hivesqoop.emp_add_hive(
	id  int,
	hno string,
	street string,
	city string
) 
row format delimited fields terminated by '\t'
stored as orc ;
1
2
3
4
5
6
7
8
9
10

• 第二步: 通过sqoop完成数据导入操作

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp_add \
> --hcatalog-database hivesqoop \
> --hcatalog-table emp_add_hive \
> -m 1 
1
2
3
4
5
6
7
8

4. mysql条件导入到HDFS中

• 方式一: 通过–where的方式

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp \
> --where 'id > 1205' \
> --delete-target-dir \
> --target-dir '/sqoop_works/emp_2' \
> --split-by id \
> -m 2 
1
2
3
4
5
6
7
8
9
10

• 方式二: 通过SQL(–query)的方式

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --query 'select deg  from emp where 1=1 AND $CONDITIONS' \
> --delete-target-dir \
> --target-dir '/sqoop_works/emp_4' \
> --split-by id \
> -m 1 
1
2
3
4
5
6
7
8
9

注意:
如果SQL语句使用 双引号包裹, $CONDITIONS前面需要加一个\进行转义, 单引号是不需要的

5. mysql条件导入到hive中(可实现增量导入)

• where

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp_add \
> --where 'id > 1205' \
> --hcatalog-database hivesqoop \
> --hcatalog-table emp_add_hive \
> -m 1 
1
2
3
4
5
6
7
8
9

• query

[root@hadoop01 ~]# sqoop import \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --query 'select * from emp_add where id>1205 and $CONDITIONS'
> --hcatalog-database hivesqoop \
> --hcatalog-table emp_add_hive \
> -m 1 
1
2
3
4
5
6
7
8

2.3.3 Sqoop的数据导出操作

将hive中 emp_add_hive 表数据导出到MySQL中

1. 第一步: 在mysql中创建目标表(必须创建)

create table test.emp_add_mysql(
	id     INT  ,
    hno    VARCHAR(32) NULL,
    street VARCHAR(32) NULL,
    city   VARCHAR(32) NULL
);
1
2
3
4
5
6

2. 第二步: 执行sqoop命令导出数据

[root@hadoop01 ~]# sqoop export \
> --connect jdbc:mysql://192.168.52.150:3306/test \
> --username root \
> --password 123456 \
> --table emp_add_mysql \
> --hcatalog-database hivesqoop \
> --hcatalog-table emp_add_hive \
> -m 1 
1
2
3
4
5
6
7
8

存在问题: 如果hive中表数据存在中文, 通过上述sqoop命令, 会出现中文乱码的问题

2.3.4 sqoop相关常用参数