大数据第三次阶段考试题

一、简答部分(20分)

1.1 尽量详细写出数据仓库构建流程(20分)

1. 向需求方要需求
2. 找业务人员熟悉业务
3. 根据需求，确定需要的事实和维度，在原始业务库中筛选所需的表
4. 数据采集，包括采集业务数据和日志数据
5. 构建业务总线矩阵，一行对应一个事实，一列对应一个维度
   1. 一个事实可能对应原始业务库的若干张表，比如：下单业务过程，对应的原始表：order_info、order_detail、order_detail_coupon、order_detail_activity
   2. 一个维度可能对应原始业务库的若干张表，比如：商品维度，对应的原始表：sku_info、spu_info、base_trademark、base_category1_info、base_category2_info、base_category3_info、sku_attr_value、sku_sale_attr_value
6. 构建指标体系
   1. 指标的类别：原子指标、派生指标、衍生指标
   2. 原子指标 = 业务过程 + 度量 + 聚合逻辑
   3. 派生指标 = 统计周期 + 统计周期 + 原子指标 + 业务限定
   4. 衍生指标依赖多个派生指标
7. 创建ODS层的表，和业务系统保持一致
8. 创建DIM、DWD层的表，根据业务总线矩阵创建，视具体情况创建拉链表、累积型事实表、全量表、增量表
9. 根据指标体系分析出需要创建的DWS层的表，业务过程、统计周期、统计粒度相同的指标，可以共用一张DWS表，DWS表分为：1d、nd、td，根据需要创建不同的表。
10. 创建ADS层的表，直接根据指标创建即可，然后使用DIM、DWD、DWS层的表，进行统计分析
11. 将ADS表的数据，导出到MySQL
12. 使用supset读取MySQL数据，进行展示

二、绘图部分(20分)

2.1 手绘数仓整体架构图

（采集项目的技术框架，他们的数量和作用）(20分)

1. MySQL：业务数据库，2个
2. Maxwell：增量采集，1个
3. datax:全量采集，1个
4. kafka：中间件，存储数据，削峰填谷，（集群）
5. flume：流式采集，3个
6. HDFS：存储数据
7. hive：管理数仓的表
8. superset：展示
9. zookeeper：服务注册与发现，用于kafka选举，（集群）
10. DS：任务执行、调度

三、手写SQL部分(60分)

3.1 SQL1(20分)

1. 数据结构：

有一个线上服务器访问日志，数据结构如下：

表名：ip_info

字段名：time1（访问时间）、interface（访问接口）、ip（访问的IP地址）

2. 需求：

① 建表语句

② 求11月9号下午14点（14-15点），访问/api/user/login接口次数的top2的ip地址

3. 数据集：

[atguigu@hadoop102 ~]$ vim /opt/module/hive/datas/ip_info.txt
2016-11-09 14:22:05	/api/user/login	110.23.5.33
2016-11-09 11:23:10	/api/user/detail	57.3.2.16
2016-11-09 14:59:40	/api/user/login	200.6.5.166
2016-11-09 14:22:05	/api/user/login	110.23.5.34
2016-11-09 14:22:05	/api/user/login	110.23.5.34
2016-11-09 14:22:05	/api/user/login	110.23.5.34
2016-11-09 11:23:10	/api/user/detail	57.3.2.16
2016-11-09 23:59:40	/api/user/login	200.6.5.166
2016-11-09 14:22:05	/api/user/login	110.23.5.34
2016-11-09 11:23:10	/api/user/detail	57.3.2.16
2016-11-09 23:59:40	/api/user/login	200.6.5.166
2016-11-09 14:22:05	/api/user/login	110.23.5.35
2016-11-09 14:23:10	/api/user/detail	57.3.2.16
2016-11-09 23:59:40	/api/user/login	200.6.5.166
2016-11-09 14:59:40	/api/user/login	200.6.5.166
2016-11-09 14:59:40	/api/user/login	200.6.5.166

4. 建表语句：

hive(default)>
create table ip_info(
    time1 string,       -- 访问时间
    interface string,   -- 访问接口
    ip string           -- 访问的ip地址
)
row format delimited fields terminated by '\t';

5. 思路：

① 过滤符合要求的数据

② 对数据进行分组统计，分组字段ip，统计记录个数

③ 按照每个ip的统计记录个数排序，取前2个。

6. 最终SQL

hive(default)>
select
    ip, interface, count(ip) ip_times
from ip_info
where date_format(time1,"yyyy-MM-dd HH") >= "2016-11-09 14"
  and date_format(time1,"yyyy-MM-dd HH") <= "2016-11-09 15"
  and interface ="/api/user/login"
group by ip, interface
order by ip_times desc
limit 2;

3.2 SQL2(20分)

1. 数据结构：

有一个账号表，

表名：account

字段名：dist_id（区组id）、account（账号）、gold（金币）

2. 需求：

① 建表语句

② 查询各自区组的money排名前十的账号（分组取前10）

3. 数据集：

[atguigu@hadoop102 ~]$ vim /opt/module/hive/datas/account.txt
1003	11133	23000
1001	11113	43000
1003	11134	41000
1002	11123	33000
1003	11135	31001
1002	11121	22000
1002	11122	31000
1001	11114	21000
1001	11115	11001
1001	11111	25500
1001	11112	34300
1002	11124	11700
1002	11125	23501
1003	11131	45800
1003	11132	14900
1003	11133	23400
1001	11113	43200
1003	11134	41700
1002	11123	33800
1003	11135	31901
1002	11121	22100
1002	11122	31200
1001	11114	21400
1001	11115	11101

4. 建表语句：

hive(default)>
CREATE TABLE `account`
(
    `dist_id` int,    -- '区组id'
    `account` string, -- '账号'
    `gold`    string -- '金币'
)
row format delimited fields terminated by '\t';

5. 思路：

6. 最终sql

hive(default)>
select
    *
from account as a
where(
    select
        count(distinct(a1.gold))
    from account as a1 
    where 
        a1.dist_id=a.dist_id
        and a1.gold > a.gold
)<3;

3.3 SQL3(20分)

1. 数据结构：

① 会员表：member  字段名：memberid、credits（积分）

② 销售表：sale    字段名：memberid、MNAccount（购买金额）

③ 退货表：regoods 字段名：memberid、RMNAccount（退货金额）

2. 业务说明：

（1）销售表中的销售记录可以是会员购买，也可以是非会员购买。（即销售表中的memberid可以为空）；

（2）销售表中的一个会员可以有多条购买记录；

（3）退货表中的退货记录可以是会员，也可是非会员；

（4）一个会员可以有一条或多条退货记录。

3. 需求：

① 建表语句

② 分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits保留两位小数）

4. 数据集

[atguigu@hadoop102~]$ vim /opt/module/hive/datas/sale.txt
1001	50.3
1002	56.5
1003	235
1001	23.6
1005	56.2
    	25.6
    	33.5
[atguigu@hadoop102~]$ vim /opt/module/hive/datas/regoods.txt
1001	20.1
1002	23.6
1001	10.1
     	23.5
     	10.2
1005	0.8

5. 建表语句：

// member会员表
hive(default)>
create table member(
    memberid string,
    credits decimal(10,2)
) 
row format delimited fields terminated by '\t';

// sale销售表
hive(default)>
create table sale(
    memberid string,
    MNAccount double
) 
row format delimited fields terminated by '\t;

// regoods退货表
hive(default)>
create table regoods(
    memberid string,
    RMNAccount double
) 
row format delimited fields terminated by '\t';

6. 最终sql

hive(default)>
insert into table member
select
    t1.memberid,
    MNAccount-RMNAccount
from
    (select
        memberid,
        sum(MNAccount) MNAccount
    from
        sale
    where
        memberid!=''
    group by
        memberid
    )t1
join
    (select
        memberid,
        sum(RMNAccount) RMNAccount
    from
        regoods
    where
        memberid!=''
    group by
        memberid
    )t2
on
    t1.memberid=t2.memberid;