Hadoop_19_综合案例2

数仓的设计

维度建模(dimensional modeling)是专门用于分析型数据库、数据仓库、数据集市建模的方法。

维度建模的基本概念
维度表：维度表示你要对数据进行分析时所用的一个量。

例：昨天下午我在星巴克花费200元喝了一杯卡布奇诺
可从这段信息中提取三个维度：
时间维度(昨天下午)，地点维度(星巴克), 商品维度(卡布奇诺)

通常来说维度表信息比较固定，且数据量小。

事实表：表示对分析主题的度量。事实表包含了与各维度表相关联的外键，并通过JOIN方式与维度表关联。

消费事实表：Prod_id(引用商品维度表), TimeKey(引用时间维度表), Place_id(引用地点维度表), Unit(销售量)。

维度建模

星型模式

星型模式(Star Schema)是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样。

• 维表只和事实表关联，维表之间没有关联；
• 每个维表主键为单列，且该主键放置在事实表中，作为两边连接的外键；
• 以事实表为核心，维表围绕核心呈星形分布；
  

雪花模式

雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维度表的。

星座模式

星座模式是星型模式延伸而来，星型模式是基于一张事实表的，而星座模式是基于多张事实表的，而且共享维度信息。
在业务发展后期，绝大部分维度建模都采用的是星座模式。

案例项目数仓设计

事实表设计


这里时间明细，可以拆分为：
daystr
timestr
month
day
hour

http_referer 表示是从哪个上级网址来的
网址也可以拆分为以下字段：
//地址拆开，方便分析流量从哪里来
ref_host www.baidu.com
ref_path /hello.action
ref_query username
ref_query_id zhangsan

维度表设计

ETL

ETL工作的实质就是从各个数据源提取数据，对数据进行转换，并最终加载填充数据到数据仓库维度建模后的表中。

建表

node03 打开hive后
create database weblog;
use weblog;
建立原始数据层表

create table ods_weblog_origin(
valid string,
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
http_referer string,
http_user_agent string)
partitioned by (datestr string)
row format delimited
fields terminated by '\001';

建点击流pageView表

create table ods_click_pageviews(
session string,
remote_addr string,
remote_user string,
time_local string,
request string,
visit_step string,
page_staylong string,
http_referer string,
http_user_agent string,
body_bytes_sent string,
status string)
partitioned by (datestr string)
row format delimited
fields terminated by '\001';

建立点击流visit表

create table ods_click_stream_visit(
session     string,
remote_addr string,
inTime      string,
outTime     string,
inPage      string,
outPage     string,
referal     string,
pageVisits  int)
partitioned by (datestr string)
row format delimited
fields terminated by '\001';

show tables;
会看到刚才建的三个表。
一般把ods层的表和dw层的表区分开，要么用不同数据库区分，要么用不同的标识区分，这里加前缀ods。

上传数据至hdfs

node03
cd /export/servers/
mkdir weblog
cd weblog/
将之前跑出的pageView，visit，weblog数据上传至文件夹。
设置hive本地模式运行，可以快一些：set hive.exec.mode.local.auto=true;

导入清洗结果数据到贴源数据表ods_weblog_origin

load data local inpath '/export/servers/weblog/weblog' overwrite into table ods_weblog_origin partition(datestr='20130918');
show partitions ods_weblog_origin;
select count(*) from ods_weblog_origin;

导入点击流模型pageviews数据到ods_click_pageviews表

load data local inpath '/export/servers/weblog/pageview' overwrite into table ods_click_pageviews partition(datestr='20130918');
select * from ods_click_pageviews limit 10;

导入点击流模型visit数据到ods_click_stream_visit表

load data local inpath '/export/servers/weblog/visit' overwrite into table ods_click_stream_visit partition(datestr='20130918');

原始表拆分

time_local : 字符串截取
http_referer ：正则表达式

先建一个空的 ods_weblog_detail表，然后将拆分后的数据灌入。

create table ods_weblog_detail(
valid           string, --有效标识
remote_addr     string, --来源IP
remote_user     string, --用户标识
time_local      string, --访问完整时间
daystr          string, --访问日期
timestr         string, --访问时间
month           string,