136.1 數(shù)據(jù)建模

• 維度建模
• 專門適用于OLAP的設計模式存在著兩種類型的表：事實表 維度表
  • 事實表：主題的客觀度量 能夠以記錄主題為準 信息多不精準
  • 維度表：看問題分析問題的角度 信息精但是不全 可跟事實表關系
• 維度建模三種常見模型
  • 星型模型 一個事實表帶多個維度表 維度之間沒關系 數(shù)倉發(fā)展建立初期（一個主題）
  • 雪花模型 一個事實表帶多個維度表 維度之間可以繼續(xù)關系維度 不利于維護 少用
  • 星座模型 多個事實表帶多個維度 有些維度可以共用 數(shù)倉發(fā)展后期（多個主題）
• 不管什么模型，在數(shù)倉中，一切有利于數(shù)據(jù)分析即可為，不用考慮數(shù)據(jù)冗余性和其他設計規(guī)范
• 模塊設計–維度建模
  • 在本項目中，因為分析主題只有一個（網(wǎng)站流量日志），所有采用星型模型
  • 事實表---->對應清洗完之后的數(shù)據(jù)
  • 維度表----->來自于提前通過工具生成 維度表范圍要橫跨事實表分析維度
  • 點擊流模型屬于業(yè)務模型數(shù)據(jù) 既不是事實表 也不是維度表 是為了后續(xù)計算某些業(yè)務指標方便而由業(yè)務指定
• 寬表：為了分析，把原來表中某些字段屬性提取出來，構成新的字段 也稱之為明細表
  • 窄表：沒有擴寬的表 原始表
  • 寬表數(shù)據(jù)來自于窄表 insert（寬）+select (窄)
  • 總結：hive中，有幾種方式可以創(chuàng)建出帶有數(shù)據(jù)的表？
    • create+load data 創(chuàng)建表加載數(shù)據(jù)（內(nèi)部表）
    • create +external +location 創(chuàng)建外部表指定數(shù)據(jù)路徑
    • create+insert+select 表的數(shù)據(jù)來自于后面查詢語句返回的結果
    • create+select 創(chuàng)建的表結構和數(shù)據(jù)來自于后面的查詢語句

 # -- hive內(nèi)置解析url的函數(shù)
 
 parse_url_tuple（url,host path,query,queryvalue）
 
 # -- 通常用于把后面的表掛接在左邊的表之上 返回成為一個新表
 
 a LATERAL VIEW b 
 LATERAL VIEW
 
 create table t_ods_tmp_referurl as SELECT a.*,b.* FROM ods_weblog_origin a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id; 

• group by 語法限制

select count(*) as pvs from ods_weblog_detail t where datestr='20130918' group by t.hour

select t.hour,count(*) as pvs from ods_weblog_detail t where datestr='20130918' group by t.hour

# -- 在有group by的語句中，出現(xiàn)在select后面的字段要么是分組的字段要么是被聚合函數(shù)包圍的字段。
解決：
select t.day,t.hour,count(*) as pvs from ods_weblog_detail t where datestr='20130918' group by t.day,t.hour;

136.2 ETL

1.寬表生成

• 生成ods+url解析表

create table t_ods_tmp_referurl as
SELECT a.*,b.*
FROM ods_weblog_origin a 
LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id;

• 生成ods+url+date解析表

create table t_ods_tmp_detail as 
select b.*,substring(time_local,0,10) as daystr,
substring(time_local,12) as tmstr,
substring(time_local,6,2) as month,
substring(time_local,9,2) as day,
substring(time_local,11,3) as hour
From t_ods_tmp_referurl b;

• 綜合

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, --傳輸字節(jié)數(shù)
http_referer    string, --來源url
ref_host        string, --來源的host
ref_path        string, --來源的路徑
ref_query       string, --來源參數(shù)query
ref_query_id    string, --來源參數(shù)query的值
http_user_agent string --客戶終端標識
)
partitioned by(datestr string);

insert into table  ods_weblog_detail partition(datestr='20130918') 
select c.valid,c.remote_addr,c.remote_user,c.time_local,
substring(c.time_local,0,10) as daystr,
substring(c.time_local,12) as tmstr,
substring(c.time_local,6,2) as month,
substring(c.time_local,9,2) as day,
substring(c.time_local,12,2) as hour,
c.request,c.status,c.body_bytes_sent,c.http_referer,c.ref_host,c.ref_path,c.ref_query,c.ref_query_id,c.http_user_agent
from 
(select a.*,b.*
from ods_weblog_origin a
LATERAL view 
parse_url_tuple(regexp_replace(a.http_referer,"\"",""),'HOST','PATH','QUERY','QUERY_ID')b as ref_host, ref_path, ref_query, ref_query_id) c;

2.DML分析

• 計算該處理批次（一天）中的各小時 pvs

select 
t.month,t.day,t.hour,count(*)
from ods_weblog_detail t
where t.datestr='20130918'
group by t.month,t.day,t.hour;

• 計算每天的pvs

select t.month,t.day,count(*) from ods_weblog_detail t where t.datestr='20130918' group by t.month,t.day;

select a.month,a.day,sum(a.pvs)
from 
(
 select 
 t.month as month,t.day as day,t.hour as hour,count(*)  as pvs
 from ods_weblog_detail t
 where t.datestr='20130918'
 group by t.month,t.day,t.hour
) a 
group by a.month,a.day;

• 統(tǒng)計每小時各來訪url產(chǎn)生的pvs

select 
t.day,t.hour,t.http_referer,t.ref_host,count(*)
from ods_weblog_detail t
where datestr='20130918'
group by t.day,t.hour,t.http_referer,t.ref_host
having t.ref_host is not null;

• 統(tǒng)計每小時各來訪host的產(chǎn)生的pv數(shù)并排序

select 
t.month,t.day,t.hour,t.ref_host,count(*) as pvs
from ods_weblog_detail t
where datestr='20130918'
group by t.month,t.day,t.hour,t.ref_host
having t.ref_host is not null
order by t.hour asc ,pvs desc;

• 按照時間維度，統(tǒng)計一天內(nèi)各小時產(chǎn)生最多pvs的來源（host）topN(分組Top)

select 
a.month,a.day,a.hour,a.host,a.pvs,a.rmp
from
(
 select 
 t.month as month,t.day as day,t.hour as hour,t.ref_host as host,count(*) as pvs,
 row_number()over(partition by concat(t.month,t.day,t.hour) order by pvs desc) rmp
 from ods_weblog_detail t
 where datestr='20130918'
 group by t.month,t.day,t.hour,t.ref_host
 having t.ref_host is not null
 order by hour asc ,pvs desc
)a 
where a.rmp < 4;

• 統(tǒng)計今日所有來訪者平均請求的頁面數(shù)。

select count(*)/count(distinct remote_addr) from ods_weblog_detail where datestr='20130918';

select
sum(a.pvs)/count(a.ip)
from
(
 select
 t.remote_addr as ip,count(*) as pvs
 from ods_weblog_detail t
 where t.datestr='20130918'
 group by t.remote_addr
) a;

• 統(tǒng)計每日最熱門的頁面 top10

select 
t.request,count(*) as counts
from ods_weblog_detail t
where datestr='20130918'
group by t.request
order by counts desc
limit 10;

• 每日新訪客

select 
today.ip
from 
(
 select distinct t.remote_addr as ip 
 from ods_weblog_detail t
) today 
left join history
on today.ip=history.ip
where  history.ip is null;

• 查詢今日所有回頭訪客及其訪問次數(shù)（session）

select
remote_addr,count(session) as cs
from ods_click_stream_visit
where datestr='20130918'
group by remote_addr
having cs >1;

• 人均訪問頻次

select 
count(session)/count(distinct remote_addr)
from ods_click_stream_visit
where datestr='20130918';

• 級聯(lián)查詢自join

select 
rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs  
from  dw_oute_numbs rn
inner join 
dw_oute_numbs rr;

# -- 絕對轉化

select 
a.rrstep,a.rrnumbs/a.rnnumbs
from 
(
 select 
 rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs  
 from  dw_oute_numbs rn
 inner join dw_oute_numbs rr
)a
where a.rnstep='step1';

# -- 相對轉化

select 
tmp.rrstep as step,tmp.rrnumbs/tmp.rnnumbs as leakage_rate
from
(
 select rn.step as rnstep,rn.numbs as rnnumbs,rr.step as rrstep,rr.numbs as rrnumbs from    dw_oute_numbs rn
 inner join 
 dw_oute_numbs rr
) tmp
where cast(substr(tmp.rnstep,5,1) as int)=cast(substr(tmp.rrstep,5,1) as int)-1;

大數(shù)據(jù)視頻推薦：
網(wǎng)易云課堂
CSDN
人工智能算法競賽實戰(zhàn)
AIops智能運維機器學習算法實戰(zhàn)
ELK7 stack開發(fā)運維實戰(zhàn)
PySpark機器學習從入門到精通
AIOps智能運維實戰(zhàn)
騰訊課堂
大數(shù)據(jù)語音推薦：
ELK7 stack開發(fā)運維
企業(yè)級大數(shù)據(jù)技術應用
大數(shù)據(jù)機器學習案例之推薦系統(tǒng)
自然語言處理
大數(shù)據(jù)基礎
人工智能：深度學習入門到精通