Lv游网电商离线数据分析平台--4.数据存储设计

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

• 将原始的日志数据按天保存到hdfs文件系统中；(lv_track)
• 将etl解析后的数据保存到hbase中；(lv_transformer)
• 将分析结果保存到mysql数据库中。(lv_dataapi)

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

---

1、HBase表结构设计

  需要按天进行数据分析，所以 hbase的rowkey中必须包含时间戳/一天的数据就建立一个表。这里采用第一种方式；

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

  最终创建一个单列簇的rowkey包含时间戳的eventlog表。命令：create 'eventlog', 'log'。rowkey设计规则为：timestamp + uuid.hashcode + random(4)
 

2、MySQL表结构设计

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

• 其中维度信息表用于存储维度相关信息，名称格式为：dimension_*；
• 统计分析结果表用户存储最终的统计分析结果，以维度id做主健，名称格式为：stats_*；
• 分析辅助表主要用户在分析过程中使用到的其他辅助类型表。
   

a) MySQL维度信息表设计

  根据最终统计的维度信息来看，分别需要创建以下八个维度表：平台维度(platform)，时间维度(date)，浏览器维度(browser)，地域维度(location)，支付方式维度(payment)，货币类型维度(currency_type)，event维度(event)，外链维度(inbound)。另外还分别需要一个kpi维度和一个操作系统维度(os)表。

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

统计分析模块	相关维度表
用户基本信息分析	platform、date
浏览器信息分析	platform、date、browser
地域信息分析	platform、date、location
用户浏览深度分析	platform、date、kpi
外链信息分析	platform、date、inbound
订单信息分析	platform、date、currency_type、payment
事件分析	platform、date、event

b) MySQL统计分析结果表设计

  需要进行七个模块的数据分析，这些展示数据最终是从MySQL中获取的，那么接下来就分别从这七个模块来分析对应的mysql表结构设计。

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

  要求数据库对应表有以下几个维度的数据：新增用户数、活跃用户数、总用户数、新增会员数、活跃会员数、总会员数、会话个数、会话长度这8个统计指标，除此之外还需要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	ing(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表结构

列名	类型	默认值	描述
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	ing(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标示修改日期的字段。

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<=pv<10)，访问10-30个pv，访问30-60个pv，访问60+pv。此外需要platform、date、kpi（主键）这三个维度信息字段和create标示修改日期的字段。

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	只访问一个页面的数量
pv2	int(11)	空，0	访问两个页面
pv3	int(11)	空，0	访问三个页面
pv4	int(11)	空，0	访问四个页面
pv5_10	int(11)	空，0	访问[5,10)个页面
pv10_30	int(11)	空，0	访问[10,30)个页面
pv30_60	int(11)	空，0	访问[30,60)个页面
pv60+	int(11)	空，0	访问[60,..)个页面
created	date	空，null	最后修改日期

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

  主要包括外链构成(偏好)分析和跳出率分析。其中外链构成(偏好)我们通过活跃用户数来标示，需要几个统计指标：活跃用户数、会话个数、跳出会话个数。除此之外需要platform、date、inbound（主键）这三个维度信息字段和create 修改日期的字段。

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表结构

列名	类型	默认值	描述
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表结构

列名	类型	默认值	描述
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表结构设计总述

  通过维度信息表和统计分析结果表的整合来保证数据展示的正常性：CREATE DATABASE `report` DEFAULT CHARACTER SET utf8 ;