hadoopdataplatform_# Hadoop离线数据分析平台实战——230项目数据存储结构设计

Hadoop离线数据分析平台实战——230项目数据存储结构设计

数据存储设计

在本次项目中设计到数据存储的有三个地方：

第一个就是将原始的日志数据按天保存到hdfs文件系统中；

第二个就是将etl解析后的数据保存到hbase中；

第三个就是将分析结果保存到mysql数据库中。

其中存储到hbase和mysql的这两个过程需要设计具体的存储结构。

HBase表结构设计

由于我们需要按天进行数据分析，

所以我们的hbase的rowkey中必须包含时间戳或者我们一天的数据就建立一个表。

这里我们采用在rowkey中包含时间戳的方式来进行讲解；

另外hbase要求列簇一般尽量的少而且短，我们这里采用log来标示列簇。

所以最终我们创建一个单列簇的rowkey包含时间戳的eventlog表。

命令如下：

create 'eventlog', 'log'。rowkey设计规则为：timestamp+uuid.hashcode+random(4)

MySQL表结构设计

在mysql中我们采用维度信息表+统计分析结果表+分析辅助表三类表组成。

其中维度信息表用于存储维度相关信息，名称格式为:dimension_*；

统计分析结果表用户存储最终的统计分析结果，以dimension维度id做主健，名称格式为:stats_*；

分析辅助表主要用户在分析过程中使用到的其他辅助类型表。

MySql维度信息表设计

根据我们最终统计的维度信息来看，我们分别需要创建以下八个维度表：

平台维度(platform)，时间维度(date)，浏览器维度(browser)，地域维度(location)，支付方式维度(payment)，货币类型维度(currency_type)，event维度(event)，外链维度(inbound)。

另外还分别需要一个kpi维度和一个操作系统维度(os)表。

注意os表在本次项目中不会用到。

最终统计分析模块和维度表关系

分析模块

相关维度表

用户基本信息分析

platform、date

浏览器信息分析

platform、date、browser

地域信息分析

platform、date、location

用户浏览深度分析

platform、date、kpi

外链信息分析

platform、date、inbound

订单信息分析

platform、date、currency_type、payment

事件分析

platform、date、event

MySql统计分析结果表设计

我们最终需要进行七个模块的数据分析，

这七个模块的展示数据最终是从我们的mysql数据库中获取的，

那么接下来就分别从这七个模块来分析对应的mysql表结构设计。

用户基本信息分析模块表结构设计

用户基本信息分析模块中要求数据库对应表有以下几个维度的数据：

新增用户数、活跃用户数、总用户数、新增会员数、活跃会员数、总会员数、会话个数、会话长度这八个统计指标，

除此之外还需要platform和date这两个维度信息id以及created字段来标示修改时间。

其中通过platform和date这两个字段信息来唯一确定表数据，

设计表名为: stats_user。

除了这个表以外，由于我们还需要统计分时段的数据信息，故还要求有一个分时存储统计数据的表，设计表名为:stats_hourly。

stats_user表结构

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

active_users

int(11)

空。0

活跃用户数

new_install_users

int(11)

空。0

新增用户数

total_intall_users

int(11)

空。0

总用户数

sessions

int(11)

空。0

会话个数

sessions_length

int(11)

空。0

会话长度

total_members

int(11)

空。0

总会员数

active_members

int(11)

空。0

活跃会员数

new_members

int(11)

空。0

新增会员数

created

date

空。null

记录日期

浏览器信息分析模块表结构设计

浏览器信息分析和用户基本信息分析基本类型，

也包含以下几个维度的数据：

新增用户数、活跃用户数、总用户数、新增会员数、活跃会员数、总会员数、会话个数、会话长度这八个统计指标，

除此之外还需要一个pv计数的统计指标以及platform、date、browser这三个维度信息字段和create标示修改日期的字段。

其中通过platform、date和browser这三个字段信息来唯一确定表数据。

设计表名为: stats_device_browser

stats_device_browser表结构

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

browser_dimension_id

int(11)

非空，0

浏览器id，pkey

active_user

int(11)

空，0

活跃用户数

new_install_users

int(11)

空，0

新增用户数

total_intall_users

int(11)

空，0

总用户数

sessions

int(11)

空，0

会话个数

sessions_length

int(11)

空，0

会话长度

total_members

int(11)

空，0

总会员数

active_members

int(11)

空，0

活跃会员数

new_members

int(11)

空，0

新增会员数

pv

int(11)

空，0

pv数

created

date

空，null

最后修改日期

地域信息分析模块表结构设计

地域信息分析模块只分析活跃用户的地域分布情况以及跳出率相关分析，

故需要以下几个统计指标：

活跃用户数、会话个数、跳出会话个数。

除此之外需要platform、date、location这三个维度信息字段和create标示修改日期的字段。

其中通过platform、date和location这三个字段信息来唯一确定表数据。

设计表名为: stats_device_location

stats_device_location表结构

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

location_dimension_id

int(11)

非空，0

地域id，pkey

active_user

int(11)

空，0

活跃用户数

sessions

int(11)

空，0

会话个数

bounce_sessions

int(11)

空，0

跳出会话个数

created

date

空，null

最后修改日期

用户浏览深度分析模块表结构设计

用户浏览深度我们通过计算访问不同数量页面的用户数/会话数来表示，

在本次项目中我们分为8个不同数量级的指标，

分别为：访问1个pv、访问2个pv、访问3个pv、访问4个pv，访问5-10个pv(包含5但不包含10)，访问10-30个pv，访问30-60个pv，访问60+pv。

除此之外需要platform、date、kpi这三个维度信息字段和create标示修改日期的字段。

其中通过platform、date和kpi这三个字段信息来唯一确定表数据。

设计表名为: stats_view_depth

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

kpi_dimension_id

int(11)

非空，0

kpiid，pkey

pv1

int(11)

非空，0

访问1个页面

pv2

int(11)

非空，0

访问2个页面

pv3

int(11)

非空，0

访问3个页面

pv4

int(11)

非空，0

访问4个页面

pv5_10

int(11)

非空，0

访问5-10个页面

pv11_30

int(11)

非空，0

访问11-29个页面

pv30_60

int(11)

非空，0

访问30-59个页面

pv60plus

int(11)

非空，0

访问60+个页面

created

date

空，null

最后修改日期

外链信息分析模块表结构设计

外链信息分析主要包括外链构成(偏好)分析和跳出率分析。

其中外链构成(偏好)我们通过活跃用户数来标示，

需要我们需要几下几个统计指标：

活跃用户数、会话个数、跳出会话个数。

除此之外需要platform、date、inbound这三个维度信息字段和create标示修改日期的字段。

其中通过platform、date和inbound这三个字段信息来唯一确定表数据。

设计表名为: stats_inbound

stats_inbound表结构

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

inbound_dimension_id

int(11)

非空，0

外链id，pkey

active_user

int(11)

空，0

活跃用户数

sessions

int(11)

空，0

会话个数

bounce_sessions

int(11)

空，0

跳出会话个数

created

date

空，null

最后修改日期

订单信息分析模块表结构设计

订单信息分析需要分析订单数量和订单金额相关统计信息，

故需要以下几个指标的数据分析：

订单数量、成功支付的订单数量、退款订单数量、订单金额、成功支付金额、退款金额、总成功支付金额、总退款金额。

除此之外，我们还需要使用platform、date、currency_type和payment四个维度字段类表示确认唯一记录。

另外需要添加一个created字段来表示数据日期。

表名设计为:stats_order

stats_order表结构

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

currency_type_dimension_id

int(11)

非空，0

货币类型id，pkey

payment_type_dimension_id

int(11)

非空，0

支付类型id，pkey

orders

int(11)

空，0

订单数量

success_orders

int(11)

空，0

成功支付的订单数量

refund_orders

int(11)

空，0

退款的订单数量

order_amount

int(11)

空，0

订单金额

revenue_amount

int(11)

空，0

支付金额

refund_amount

int(11)

空，0

退款金额

total_revenue_amount

int(11)

空，0

总的支付金额

total_refund_amount

int(11)

空，0

总的退款金额

created

date

空，null

最后修改日期

事件分析模块表结构设计

在本次项目中，事件分析主要就是分析事件的触发次数，

故对于的数据存储结构为：times(触发次数)以及platform、date、event三个维度字段和created字段，

表名设计为:stats_event

stats_event表结构

列名

类型

默认值

描述

platform_dimension_id

int(11)

非空，0

平台id，pkey

date_dimension_id

int(11)

非空，0

日期id，pkey

event_dimension_id

int(11)

非空，0

event维度id，pkey

times

int(11)

空，0

触发次数

created

date

空，null

最后修改日期

MySql表结构设计总述

我们通过维度信息表和统计分析结果表的整合来保证我们数据展示的正常性，具体创建表结果的文件参考：

..\文档\report.sql ;

其中我们创建数据库的命令为：CREATE DATABASE report DEFAULT CHARACTER SET utf8 ;