利用SQL语句实现分析需求(1)

数仓的设计

维度建模的基本概念：

维度表：

 用于描述环境的维度表，单一主键。维度表的属性是所有查询约束和报表标示的来源。维度提供数据的入口点，提供所有DW/BI分析的最终标识和分组。
 所以维度建模表示每个业务过程包含的事实表，事实表里面存储事件的数值化度量，围绕事实表的是多个维度表，维度表包含事件发生的实际存在的文本环境。

事实表：

  用于度量的事实表，事实表一般会有两个或者多个外健与维度表的主键进行关联。事实表的主键一般是组合健，表达多对多的关系。

维度建模的三种方式：
星型模型：以事实表为依据，周围很多维度表

  例如：    订单的分析：用户   货运id   商品id  

雪花模型：以事实表为依据 ，很多维度表围绕其中，然后维度表还可能有很多子维度

星座模式：多个事实表有可能会公用一些维度表，最常见的就是我们的省市区的公用

实际中大部分时候使用星座表

原始数据的拆分

以原始数据表ods_weblog_origin（对应MR清洗后的数据表）为例，需要建立数据宽表

所需要的信息字段：

    valid	string	是否有效
    remote_addr	string	访客ip
   remote_user	string	访客用户信息
  time_local	string	请求时间  2018-11-20 15:23:25
  request	string	请求url
  status	string	响应码
  body_bytes_sent	string	响应字节数
   http_referer	string	来源url
  http_user_agent	string	访客终端信息 

首先建立ods_weblog_origin表

drop table if  exists ods_weblog_origin;
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表：

drop table if  exists ods_click_pageviews;
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表

drop table if exists ods_click_stream_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';

将清洗后的数据导入表中：

 设置hive本地模式运行
set hive.exec.mode.local.auto=true;

---

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

load data local inpath '/export/servers/weblog/webout' 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/views' overwrite into table ods_click_pageviews partition(datestr='20130918');
select count(*) from ods_click_pageviews;

---

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

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

建立明细宽表：
明细宽表 ods_weblog_detail

drop table 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, --访问月
day             string, --访问日
hour            string, --访问时
request         string, --请求的url
status          string, --响应码
body_bytes_sent string, --传输字节数
http_referer    string, --来源url
ref_host        string, --来源的host
ref_path        string, --来源的路径
ref_query       string, --来源参数query
ref_query_id    string