架构

项目框架

数仓架构

存储压缩

Snappy与LZO

LZO安装：

读取LZO文件时，需要先创建索引，才可以进行切片。

框架版本选型

Apache：运维麻烦，需要自己调研兼容性。
CDH：国内使用最多，不开源，已开始收费。老版本不再进行组件后续兼容性的更新。
HDP：开源，不稳定，已被CDH收购，合并为CDP。

CDH 的Cloudera Manager免费与收费版的对比表

服务器选型

企业选择

有钱或与阿里无冲突的公司，选择阿里云。
中小公司，为了融资上市，选择阿里云，之后用物理机。
中大型构思，长期规划，资金充足，使用物理机。

集群规模

项目经验

HDFS

配置多目录

Hadoop

参数调优

Hive

可以用Tez引擎替换MR。Tez是基于内存的计算引擎，可以大大提升计算效率。数据量巨大时不适用。

Sqoop

Hbase

www.bilibili.com/video/bv1Y4…

HBase是一个结构化数据的分布式存储系统，利用HBase技术可在廉价PC Server上搭建起大规模结构化存储集群。

数仓理论

数仓分层

分层

本文根据美团的数仓进行阐述，美团的数仓理论依然是源自于Ralph Kimball的维度模型。每个公司在实践Kimball理论时会产生不同的输舱模型。

ODS 原始数据层：存放原始数据，直接加载原始日志、数据，数据保持原貌不作处理。
DWD 明细数据层：对ODS进行清洗，去除空值、脏数据、超过极限范围的数据等，且结构与粒度与ODS保持一致。ETL工具：HQL\MR\SparkSQL\Python\kettle
DWS 服务数据层：以DWD为基础，按天进行汇总。
DWT 数据主体层：以DWS为基础，按主题进行汇总。
ADS 数据应用层：以DWD和DWS为基础。执行SQL，为各种统计报表提供数据。

数据集市与数据仓库

数据集市 Data Market：微型的数据仓库，提供部门级的数据服务。数据更少，主题区域更少，历史数据更少。

数据仓库：企业级数据。

好处

简化问题，方便定位
减少重复开发
将真实数据与统计数据解耦

命名规范

表命名

ods_name

dwd_dim/fact_name

dws_name

dwt_name

ads_name

临时表

name_tmp

用户行为表

name.log

脚本命名

source_to_destination_db/log.sh
用户行为脚本用log结尾，业务数据脚本用db结尾

数仓理论

范式理论

概念

定义：表结构符合规范和要求

优点：降低冗余

缺点：读取时需要通过join拼接，效率低。

函数依赖

完全函数依赖、部分函数依赖、传递函数依赖。

三范式区分

属性不可切割
不能存在部分函数依赖
不能存在传递函数依赖

关系建模与维度建模

关系建模

满足三范式，为了减少冗余，查询时需要使用大量join

维度建模

不满足三范式，大量冗余，将表分为事实表和维度表两类。可以分为三类：星型模型、星座模型、雪花模型。

本节课所使用的电商领域的数仓建模*****

维度表和事实表*****

维度表（关于一个名词）

对事实的描述信息，每一张维表对应现实中一个对象或者概念。例如：用户、商品等。

特征

宽表，数据量较小，相对固定。

事实表（关于一个动词）

对业务事件的（可累加的）度量。例如：次数、个数、金额等。每一个事实表对应一个业务事件。例如：下单、支付、退款等。

每个事实表的行包括

可加的度量值
外键（通常有两个或以上）

特征

窄，大，经常变化。

分类

事务事实表

以每个事务或事件为单位，例如：一个销售订单记录。不可更改，插入更新，增量。

快照事实表

不保留所有数据，只保留固定时间间隔的数据，例如：每天、每月的销售额。

累计事实表

用于跟踪业务事实的变化（生命周期），在业务过程进行时，需要不断进行更新。

数仓建模*****

ODS

保持数据原貌，起到备份作用
数据采用压缩，减少存储空间（例如：100G -> 10G，受实际情况影响）
创建分区表，防止后续的全表扫描（例如：按照日期分区）

DWD

需构建维度模型，一般采用星型模型，呈现的状态一般为星座模型。

步骤：选择业务过程->声明粒度->确认维度->确认事实

选择业务过程

挑选感兴趣的业务线，一条业务线对应一张事实表。例如：下单业务，支付业务，退款业务，物流业务。

声明粒度

粒度定义了数据的细化程度或综合程度的级别
声明粒度意味着精确定义事实表中一行的含义，应尽可能选择最小粒度。

确认维度

维度是描述业务的事实，一般关于6W。

确认事实

度量值，例如：件数、金额。

DWS

主题宽表。主题是指观察问题的角度，选一个维度，对事实表进行归纳分析。DWS统计各个主体对象的当天行为，服务于DWT的主题宽表，以及一些业务明细数据。

DWT

主题宽表。主题是指观察问题的角度，选一个维度，对事实表进行归纳分析。DWT统计各个主体对象的全量宽表。

对各大主题指标进行统计。向DWS和DWT写SQL。

ODS

启动日志

drop table if exists ods_start_log;
CREATE EXTERNAL TABLE ods_start_log 
(
'line' string
)
PARTITIONED BY ('dt' string)
STORED AS 
    INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION '/warehouse/db_name_table/ods/ods_start_log';


#导入
load data inpath ... OVERWRITE into table ... partition(dt='')；


#创建索引,便于后续切片处理
hadoop jar ../hadoop-lzo-x.x.xx.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/db_name_table/ods/ods_start_log/dt=yyyy-MM-d

事件日志

步骤、代码同上。

脚本导入

#!/bin/bash


#定义变量
#hive路径
hive=/opt/module/hive/bin/hive
#APP名称，作为表名
APP=gmall


#获取时间
if [ -n "$2" ] ; then
  do_date=$1
else
  do_date=`date -d '-1 day' +%F`
fi


#确定SQL
sql="
load data ... overwrite into table ${APP}.table_name partition(dt='$do_date');
"


#执行SQL
$hive -e "$sql"


#创建LZO索引(若导入时已创建则可省略)
hadoop jar ../hadoop-lzo-x.x.xx.jar com.hadoop.compression.lzo.DistributedLzoIndexer /warehouse/db_name_table/ods/ods_start_log/dt=$do_

其中

[ -n 变量值 ]

判断的是输入值是否为非空：

变量非空，返回true
变量为空，返回false

可以通过case来区分第一次和之后运行的脚本版本（减少不必要的加载）。

case $1 in
"first"){
  $hive -e "sql1"
  $hive -e "sql2"
};;
"all"){
  $hive -e "sql1"
};;
esac

执行时输入两个脚本参数

脚本.sh first/all date

DWD

Json String的解析过程。

步骤：

建表
get_json_object
导入，采用parquet（列存储），支持切片，且不需要创建索引。

#建表
drop table if exists dwd_start_log;
CREATE EXTERNAL TABLE dwd_start_log(
'mid_id' string,
'user_id' string...
)
PARTITIONED BY (dt string)
stored as parquet
location ...
TBLPROPERTIES('parquet.compression'='lzo');


#导入
insert overwrite table dwd_start_log
partition(dt='yyyy-MM-dd')
select
  get_json_object(line, "$.mid") mid_id,
  get_json_object(line, "$.uid") user_id,
  ...
from ods_start_log
where dt='yyyy-MM-dd';

使用脚本导入

#!/bin/bash


APP=gmall
hive=/hivepath


if [-n "$1"];then
  do_date=$1
else
  do_date=`date -d '-1 day' +%F`
fi
sql="
insert overwrite table ${APP}.dwd_start_log
partition(dt='$do_date')
select
  ....
from ${APP}.ods_start_log
where dt='$do_date';
"
$hive -e "$sql

用户行为表解析*****

自定义函数

UDF：一进一出
UDAF：多进一出，如Count/max
UDTF：一/多进多出，如lateral view explore()

需要使用Maven项目来实现。

hive中的lateral view 与 explode函数的使用

UDF一进一出解析

UDTF一进多出解析

编译打包Maven项目后上传jar包至hdfs，然后创建永久函数与开发好的java class关联。

使用自定义函数时，要在脚本中注明所使用的数据库，因为自定义函数是创建在指定数据库下的。

业务数据（事务事实）

事实表的构成：外键+度量值。

生成维度表以及执行维度退化的工作。这部分内容加载需要执行大量的JOIN操作。在取数时最好有可以直观看到各个表之间关联信息的图，方便形成一个整体概念。

加购事实（周期型事实）

由于数据可能发生变化，导入增量并不合适，所以采用周期性的全量导入。结果是数据量较大，可以删除太久的历史数据节省空间。

优惠券领用（累积型事实）*****

因为Hive不支持单行数据的更新，所以选择对整个表进行重写。
使用事件开始的时间（dt）作为分区。
使用full outer join，并在合并时用if判断最新的表是否为空。
写入时同样采用事件开始的时间（dt）进行分区（动态分区），即可覆盖历史的数据表。

逻辑

实现

注意日期格式，date_format

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd表
partition(dt)
select
  if(new.id is null, old.id, new.id),
  ...,
  if(new.事件开始日期 is null, old.事件开始日期, new.事件开始日期)
from
(
  select
    id,
    ...
  from dwd表
  where dt in
  (
    select
      date_format(事件开始日期)
    from ods_coupon_use
    where dt=今天日期
  )
)old
join
(
  select
    id,
    ...
  from ods表
  where dt=今天日期
)new
on old.id=new.id;

订单事实表（累积型事实）*****

实用函数

concat

连接字符串，只要有一个为NULL，返回NULL

concat_ws

连接字符串，只要有一个不为NULL，就不返回NULL
需要指定分隔符

STR_TO_MAP

将字符串转为map
先指定列分隔符，再指定KV分隔符

str_to_map('10=2020-03-10,11=2020-03-10', ',','=')
----
{"10":"2020-03-10","11":"2020-03-10"}

collect_set

与group by联用，将多行合并成一行

逻辑/实现

对表格进行行转列，使用str_to_map

用户维度表（拉链表）

用于存储缓慢变化维度，如记录每条信息的生命周期。

逻辑

实现

建表，初始化
制作当日变动数据，每日更新

如果表内没有记录变动时间，可以使用canal对MySQL进行监控。
canal是阿里巴巴旗下的一款开源项目，纯Java开发。基于数据库增量日志解析，提供增量数据订阅&消费，目前主要支持了MySQL（也支持mariaDB）。

先合并变动信息，再追加新增信息，整合插入临时表中

drop table if exists dwd_his_tmp;
create external table dwd_his_tmp(
  ...
) COMMENT '临时表'
stored as parquet
location ...
tblproperties ("parquet.compression"="lzo");


insert overwrite table dwd_his_tmp
select * from
(
  select
    ...
  from ods where dt=当前日期
  
  union all
  select
    ...
    if(t2.id is not null and t1.有效期为最大值, date.add(t2.dt,-1), t1.有效期) 有效期
  from dwd_his t1 left join
  (
    select
      *
    from ods
     where dt=当前日期
  ) t2 on t1.id=t2.id
)t3
order by t3.id, 事件开始日期;

DWS

业务术语

用户

移动统计中，每个独立设备认为是一个独立用户
Android系统根据IMEI号
IOS系统根据OpenUDID

新增用户

首次打开某APP，会被视为新增
卸载再安装不会被视为新增

活跃用户

打开APP的用户即为活跃用户，不考虑使用情况
每天打开多次也只记为一个活跃用户

周/月活跃用户
月活跃率

月活/截止该约累计总用户

沉默用户

一段时间后不再启动/登陆的用户
反映新增用户质量，以及用户与APP的匹配程度

版本分布

不同版本的各种指标，用于区分APP各版本间的优劣和用户行为习惯

本周回流用户

上周未启动，本周启动了APP的用户

连续n周活跃用户

n周内每周都有登陆

忠诚用户

连续x周活跃用户，x>N

近期流逝用户

至今为止连续n周没有登陆用户

留存用户

新鲜用户在一段时间后的留存比例

用户新鲜度

新增用户占活跃用户的比例

单次使用时长
日使用时长
启动次数计算标准

IOS应用只要进入后台，就算一次启动
Android一般按照30秒计算，两次启动间隔小于30秒算作一次。

实用函数

NVL（formula1, formula2）

如果formula1为空值，在返回formula2的值，否则返回formula1的值。
formula1和formula2可以是数字型、字符型和日期型，二者类型需要保持一致。
可看做是if的一种简化。

日期

date_format
date_add

date_sub
next_day

last_day

设备（用户）行为

在group by后面可以使用多重的关键词，取出想要的聚合数据。

业务

DWS的宽表字段，是站在不同维度的视角去看事实表，重点关注事实表的度量值。

每日会员行为

查询涉及的表过多时可以使用子查询with语句

with 
tmp_1 as
(
  select *  from
),
tmp_2 as()


insert overwrite table xxx
partition()
select
  *,
  sum()...
from
(
  select * from tmp_1 union all
  select * from tmp_2 union all
  ...
)

每日商品行为

每日购买行为

DWT

设备主题宽表（累积型事实）

会员主题宽表

宽表的字段来自于维度关联的事实表度量值+开头/结尾+累积+累积一个时间段。

不进行列存储、存储压缩以及分区。因此也不用insert overwrite

设备主题

本主题主要都跟设备ID以及流量相关。可通过DWT/DWS/DWD层逐层下钻获取。

活跃设备数

每日新增设备

沉默用户

会员主题

会员信息统计

任务调度

本课程使用Azkaban作为任务调度工具。

由三个部分组成：

web
executor
数据库：目前只支持MySQL

可视化

使用场景

Apache Superset可对接大数据分析工具，如Hive、Kylin、Druid等。

环境配置

需要conda安装。superset使用的是flask，flask是一个python web框架。

启停

启停时需要gunicorn，gunicorn是一个web容器（python web server），可类比于java中的TomCat。

使用批量关闭脚本

ps -ef | awk '/gunicorn/ && !/awk/{print $2}' | xargs kill -9

使用

对接MySQL

需要安装mysqlclient。

制作仪表盘

可通过web界面配置。

即席查询

概念

与固话查询相对，查询逻辑无法提前预设，而是接受由用户即时生成的查询语句，并返回查询结果。

Presto

简介

概念

Presto是一个开源的分布式SQL查询引擎，支持GB到PB字节，用于处理秒级查询的场景。虽然可以解析SQL，但不是一个数据库，不能作为MySQL的替代，也不能用于OLTP。基于内存计算。

架构

可以实现跨数据源连表查询。

优点

内存计算，无磁盘I/O限制。
能连接多个数据源。

相比于Impala，性能上稍逊，但是在数据源的支持上更为丰富，包括Hive、图数据库、传统关系型数据库、Redis等。

缺点

数据块需要能放进内存。虽然能够处理PB级数据，但是并不会把全部数据都放入内存中计算。但处理聚合运算（COUNT\AVG等）时，一边计算一边清理内存，所耗内存并不大；但当处理连表查询，则会产生大量临时数据，导致速度变慢。这种现象普遍存在。

数据存储优化

合理设置分区

Presto会根据分区读取，合理的分区可以减少Presto的读取量。

使用列式存储

Presto对ORC文件做了特定优化，因此建议在Hive创建Presto使用的表时，采用ORC格式存储。

使用压缩

数据压缩可以减少对IO带宽的压力，建议使用Snappy。

查询SQL优化

只选择使用的字段

避免*的使用。

过滤条件必须加上分区字段

能用分区过滤，一定使用分区。

Groupby语句优化

合理安排groupby中字段的顺序，按照distinct数据进行降序排列。

OrderBy优化

要使用limit。如果进行全局有序，单个worker需要大量内存。使用limit可以减少排序计算和内存压力。

Join优化

将大表放在左侧。Presto默认join使用broadcast join，即将join左边的表分割到多个worker，然后将join右边的表数据整个复制一份发送到worker进行计算。若右边的表数据量太大，可能会导致内存溢出。

注意事项

字段名引用

避免和关键词的冲突。冲突时，MySQL使用的是反引号`，Presto使用的是双引号。

时间函数

presto中，比较时间时，需要在时间的数据前加 timestamp。

不支持INSERT OVERWRITE

需要先delete再insert into。

PARQUET格式

支持parquet查询，但不支持insert。

Druid

简介

概念

Druid是一个快速的列式分布式的支持实时分析的数据存储系统。在处理PB级数据、毫米级查询、数据实时处理方面，比传统的OLAP系统有了显著的性能改进。

特点

列式存储
可扩展的分布式系统
大规模的并行处理
实时或批量摄取
自愈、自平衡、易操作：易维护
预聚合、预计算
运用了Bitmap压缩算法

应用场景

数据需要提前清洗好，不需要再进行更新
适用于支持宽表，不用join的方式（即一张单表）
适合统计OLAP指标
适用于实时性要求高的场景
适用于对数据质量敏感度不高的场景

对比******

Druid是一个实时处理时序数据的OLAP数据库，索引首先按照时间分片，查询时也按照时间线去路由索引。
Kylin的核心是Cube，Cube是一种预计算技术，基本思路是预先对数据作多维索引，查询时只扫描索引而不访问原始数据。
Presto不使用MR，大部分场景下比Hive快一个数量级，其关键是在内存中完成计算。
Impala也基于内存运算，但是支持的数据源不如Presto多。
SparkSQL是基于Spark平台的一个OLAP框架，基本思路是通过整合机器、并行计算提高查询速度。
ES最大特点是使用倒排索引解决索引问题。

技术选型******

从超大数据的查询效率来看

Druid>Kylin>Presto>SparkQL

从支持的数据源种类来看

Presto>SparkQL>Kylin>Druid

框架原理

底层

时序数据库，底层是LSM-Tree（日志结构合并树），与HBase一样。用写入新数据的形式代替修改或删除操作，以此支持快速的随机写入。读取时，需要扫描所有文件中的记录。定期对数据进行合并维护，并删除过时的数据。

在写入时，先写入内存中进行（时间）排序，以保证查询的速度。Hbase为了保证数据的稳定性（宕机可恢复），在写入内存之前会先按顺序落盘。但是Druid没有这一步，所以有丢失数据的风险。

重点

data--MiddleManagerNodes--达到阈值--flush到Deep Storage
可能在MiddleManagerNodes中丢失

访问BrokerNodes--MiddleManagerNodes/DeepStorage

数据结构

DataSource+Segment=高性能

DataSource

逻辑概念，相当于RDBMS中的table，结构包括

时间列：表明每行数据的时间值，默认UTC，精确到毫秒
维度列：通常来自于OLAP的概念，用于标识数据行
指标列：用于聚合和计算的列，通常是数值。

聚合时，可以对时间列和维度列进行聚合，默认时间列以一分钟为一组。

Segment

数据的实际物理存储格式。Druid将不同时间范围内的数据存储在不同的Segment数据块中，称之为数据横向切割。同时采用了面向列数据压缩存储（Bitmap），称之为数据纵向切割。

Kylin

简介

概念

Apache Kylin是一个开源的分布式分析引擎，提供Hadoop/Spark之上的SQL查询接口及多维分析（OLAP）能力，以支持超大规模数据。用于亚秒级查询巨大的Hive表。基于预计算。

将多维数据预处理成data cube/cuboid，并进行存储。可以直接接入星型模型（star schema），结构分为dimension（维度）和measure（度量）。

架构

选择HBase作为存储数据的库，因为Hbase支持高速的实时查询。

特点

标准SQL接口

作为MOLAP工具，难得的提供了SQL接口

支持超大数据集
亚秒级响应

目前业界唯一

可伸缩性和高吞吐率

单节点高性能，支持集群

BI工具集成

tableau、Excel、powerbi……
BIRT……
JavaScript……

使用

通过web界面进行配置。

配置计算规则

数据来源
指定维度和度量

设计查询

需要使用在定义cube时设定的join方式（inner/left），事实表需要写在join的左边
需要使用在定义cube时设定的aggregation方式

定时调度

将命令写入脚本（借助restful api），通过任务调度软件定时调度。可查看官网。

Query

curl -X POST -H "Authorization:Basic XX" \ #验证，XX用BASE64加密的“用户名:密码”替换
-H "Content-Type:application/json" \
-d '{ "sql":"select count(*) from TEST_KYLIN_FACT","project":"learn_kylin"}' \ #数据体
http://localhost:7070/kylin/api/query #api地址

结果会以JSON的格式返回。

Build Cube

curl -X PUT -H "Authorization: Basic XXXXXXXXX" -H 'Content-Type: application/json' -d '{"startTime":'1423526400000', "endTime":'1423612800000', "buildType":"BUILD"}' http://<host>:<port>/kylin/api/cubes/{cubeName}/build

13位的时间戳可通过以下代码实现

#!/bin/bash
do_date='date -d '-1 day' +%F'


#0点
#kylin默认为UTC时间，需要加上时差
start_date_unix='date -d "do_date 08:00:00" +%s'
#转为毫秒
start_date=$(($start_date_unix*1000))
#24点
stop_date=$(($start_date+86400000))


curl ....

Cube构建原理

存储原理

维度字典表：kylin为每一个维度创建字典表。

HBase K-V与RowKey

cuboid ID表示字段是否聚合，维度值则是字段对应的值。

构建算法

逐层构建算法

多层MR，中间结果都需要落盘，效率较低。

快速构建算法（inmem内存计算）

一层MR，将map的结果都存储在内存中。稳定性低。

两种算法不需要进行额外配置，kylin会根据数据量自动进行选择。

Cube构建优化

使用衍生维度（derived dimension）

根据join用的各表外键构建cube，可以大量节省非外键字段间排列组合产生的cuboid。牺牲了查询时的延迟，换取构建cube时的速度。尽量不要使用。

使用聚合组（aggregation group）

强大的剪枝工具，维护的是无用的cuboid。

强制维度

根据某个必须出现的维度，去除无用的cuboid

层级维度

根据层级，去除不合理的cuboid

联合维度

合并某些维度，将它们视为一个整体

使用方法

RowKey优化

可以优化cuboid ID中维度的顺序，遵循以下原则：

被用作where过滤条件的放在前面

便于查询

基数大的放在基数小的前面

基数大==字典表长
便于计算

并发粒度优化

Segment即为Hbase中的一张表，并发粒度主要是修改hbase中的region相关参数。

kelin.hbase.region.cut

default=5G
覆盖hbase中分裂的阈值

kylin.hbase.region.count.min

default=1

kylin.hbase.region.count.max

default=500

BI工具集成

JDBC

因为可以使用JDBC，kylin可以直接与使用JDBC的工具联用。如JavaEE项目，不需要中转导入MySQL，而是直接存在HBase中，由web项目自行调取。

结果：

Zepplin

数据治理

分为

元数据管理

atlas

数据质量

griffin（版本较低）
自写脚本

权限管理

ranger
sentry
Kerbero认证

《大数据治理与安全从理论到开源实践》

本课程集中介绍Atlas

Atlas架构

帮助组织进行元数据的治理。可以体现表与表、字段与字段的血缘关系。

笔记-尚硅谷大数据项目数据仓库-电商数仓V1.2新版

架构

项目框架

数仓架构

存储压缩

框架版本选型

服务器选型

分类

企业选择

集群规模

项目经验

HDFS

配置多目录

Hadoop

参数调优

Hive

Sqoop

Hbase

数仓理论

数仓分层

分层

数据集市与数据仓库

好处

命名规范

表命名

脚本命名

数仓理论

范式理论

概念

函数依赖

三范式区分

关系建模与维度建模

关系建模

维度建模

本节课所使用的电商领域的数仓建模*****

维度表和事实表*****

维度表（关于一个名词）

事实表（关于一个动词）

数仓建模*****

ODS

DWD

DWS

DWT

ADS

ODS

启动日志

事件日志

脚本导入

DWD

用户行为表解析*****

自定义函数

UDF一进一出解析

UDTF一进多出解析

业务数据（事务事实）

加购事实（周期型事实）

优惠券领用（累积型事实）*****

逻辑

实现

订单事实表（累积型事实）*****

实用函数

逻辑/实现

用户维度表（拉链表）

逻辑

实现

DWS

业务术语

实用函数

设备（用户）行为

业务

每日会员行为

每日商品行为

每日购买行为

DWT

设备主题宽表（累积型事实）

会员主题宽表

ADS

设备主题

活跃设备数

每日新增设备

沉默用户