1、磁區表和分桶表的應用場景
- 注意事項：磁區和分桶都會產生資料傾斜
1.1、磁區表應用場景
- 作用：提高查詢效率
①在SQL中會按照某個欄位進行過濾，建表的時候要按照這個欄位作為磁區欄位
②資料基本上只會分析增量資料，不會分析全量資料，則應該按照時間來磁區
1.2、分桶表應用場景
- 作用：提高查詢效率+進行抽樣
①如果一個表的欄位經常被Join查詢條件，就應該建分桶表進行優化
select a.*,b.* from a join b on a.guid=b.guid
-
注意事項：a表和b表都要分桶，而且分桶規則要一樣（分桶欄位一致，分桶個數成倍數關系），
-
分析分桶join資料快的原因：hash散列提高關聯效率
2、全域排序和區域排序
2.1、Order By
- 含義：全域排序，最終使用一個reduceTask來完成排序，就算你設定了多個也沒用
- 注意事項：如果資料量很大，使用Order By來排序效率非常低
select * from table order by guid
2.2、Sort By
-
含義：區域排序，使用多個ReduceTask來運行，那么每個ReduceTask輸出的結果是有序的，但整體結果無序，
-
應用場景：資料量很大但是需要進行全域排序
select * from table sort by guid
- 實作方案：
①使用一個Reducer，只輸出一個Reduce檔案
#進行相關引數設定
set mapreduce.job.reduces=1;
#SQL實作
select * from table order by guid desc
②使用多個Reducer
- 實作原理：范圍磁區（分桶distribute by）＋區域排序（每個桶內進行sort by）
#進行相關引數設定
set mapreduce.job.reduces=4;
#SQL實作
select * from table distribute by(case when guid >= 0 then A
when guid >=10 then B
when guid >=20 then C
else D) sort by guid
- 分桶規則指定：通過采樣確定資料分布規律
①第一步：模糊采樣
#采樣方式：通過亂數的方式將資料打散分布,之后取前100條
select * from table sort by rand() - 0.5 limit 100;
②第二步：確定范圍
- 將采樣后的資料進行排序，之后切分10個磁區來做
- 第一個磁區取最后一條資料，其余磁區同理
2.3、distribute by
- 含義：分桶查詢
- 應用場景：
- ①必須設定reduceTask的個數
- ②查詢中，必須設定distribute by來設定分桶規則：hash散列
set mapreduce.job.reduce=3
select * from table distribute by guid
2.4、cluster by
- 含義：如果sort by的欄位和distribute by的欄位一樣，就可以簡寫
cluster by guid = distribute by guid order by guid
- 運行規則：資料分成n段，每一段的ID都是按照hash散列的規則得到的結果都是一樣，即hash(id)%n=余數
3、常用函式
3.1、str_to_map()
- 作用：使用兩個分隔符將文本拆分為鍵值對， Delimiter1將文本分成K-V對，Delimiter2分割每個K-V對，對于delimiter1默認分隔符是’，’，對于delimiter2默認分隔符是’=’，
3.2、explode
- 作用：將一行轉換成多行
select explode(str_to_map("a:1,b:2,c:3",",",":"));
#運行結果
a 1
b 2
c 3
3.3、split()[]
- 作用：通過固定字符對字串進行切割，可通過索引獲得切割后的結果
4、高級聚合函式
- 高級聚合函式：相當于group by加強
4.1、Grouping sets
- 作用：為自定義維度，根據需要分組即可
grouping sets((device_id),(os_id),(device_id,os_id),())<=>
group by device_id union all group by os_id union all device_id,os_id
select
platform
,action
,count(guid)
,GROUPING__ID
,rpad(reverse(bin(cast(GROUPING__ID AS bigint))),3,'0')
from mtt_search.t_od_mtt_search_three_ecological_novel
where ds = 11111111
group BY
platform
,action
grouping sets((platform),(action),(platform,action),())
- 運行結果
4.2、cube函式
- 作用：分組組合最全，是各個維度值的笛卡爾（包含null）組合，
group by a,b,c with cube <=> grouping sets((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())
select
platform
,action
,count(guid)
,GROUPING_ID
from mtt_search.t_od_mtt_search_three_ecological_novel
where ds = 11111111
group BY
platform
,action
with cube
- 運行結果：
4.3、rollup函式
- 含義：從右到做遞減多級的統計
group by a,b,c with rollup <=> grouping sets((a,b,c),(a,b),(a),())
select
platform
,action
,count(guid)
,GROUPING_ID
from mtt_search.t_od_mtt_search_three_ecological_novel
where ds = 11111111
group BY
platform
,action
with rollup
- 運行結果
5、Hive處理Json資料
（1）原始資料如下
{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}
{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}
{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}
{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}
{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}
.....
{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}
{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}
{"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}
{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}
（2）要求匯入結果如下
| movie | rate | timestamp | uid |
|---|---|---|---|
| 1193 | 5 | 978300760 | 1 |
（3）解決方案
- 方案：可用內置 get_json_object 或者 自定義函式完成
-- 第一步：先放入中間表
create database if not exists nx_de_json_db;
use nx_de_json_db;
drop table if exists rate_json;
create table rate_json(line string) row format delimited;
load data local inpath '/home/bigdata/rating.json' into table rate_json;
-- 第二步：創建結果表
create table rate(movie int, rate int, unixtime int, userid int) row format
delimited fields terminated by '\t';
-- 第三步：寫決議陳述句進行插入
insert into table rate select
get_json_object(line,'$.movie') as moive,
get_json_object(line,'$.rate') as rate,
get_json_object(line,'$.timeStamp') as unixtime,
get_json_object(line,'$.uid') as userid
from rate_json;
6、企業三大面試題
6.1、最大值&累計值
（1）需求描述
- 原始表
#用戶名，月份，訪問次數
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11
- 報表產出
#每個用戶截止到每月為止的最大單月訪問次數和累計到該月的總訪問次數
A 1 20 20 20
A 2 10 30 20
A 3 30 60 30
A 4 50 110 50
.....
B 1 2 2
B 2 10 12
B 3 20 32
B 4 50 82
（2）代碼實作
- 第一步：先統計每個人的每個月的PV
- 第二步：之后通過分析函式每個用戶截止到每月為止的最大單月訪問次數和累計到該月的總訪問次數，平均單月訪問量，最小單月訪問量
select
a.name,
a.month,
a.pv,
sum(a.pv) over (partition by a.name order by a.month rows between unbounded preceding and current row) as sumpv,
max(a.pv) over (partition by a.name order by a.month rows between unbounded preceding and current row) as maxpv,
min(a.pv) over (partition by a.name order by a.month rows between unbounded preceding and current row) as minpv,
avg(a.pv) over (partition by a.name order by a.month rows between unbounded preceding and current row) as avgpv
from
(select b.name as name, b.month as month, sum(b.pv) as pv from exercise_pv b
group by b.name, b.month) a;
6.2、行列轉換
（1）行轉列
- 作用：本身回傳一張虛擬表，使用later view可直接關聯兩張表，保持原有映射關系，
# Map資料進行行轉列
select
t1.uid
,t2.key
,t2.value
from t1
later view explode(map(
'c1',c1,
'c2',c2,
'c3',c3
)) t2 as key,value
# 字串資料進行行轉列
select s.name,temp.x from s later view explode(split('score',',')) temp as x
（2）列轉行
- 作用：將多行資料融為一行
str_to_map(concat_ws(','collect_set(':',key_clm,value_clm))))
select
uid
,kv['c1'] as c1
,kv['c2'] as c2
,kv['c3'] as c3
from(
select
uid
,str_to_map(concat_ws(','collect_set(':',key_clm,value_clm)))) kv
from vtable
group by uid
) temp
6.3、TopN實作
（1）需求描述
- 源表資料
#id,name,age,favors
#id,姓名,年齡,愛好
1,huangxiaoming,45,a-c-d-f
2,huangzitao,36,b-c-d-e
3,huanglei,41,c-d-e
4,liushishi,22,a-d-e
5,liudehua,39,e-f-d
6,liuyifei,35,a-d-e
- 報表產出
#求出每種愛好中，年齡最大的兩個人（愛好，年齡，姓名）注意思考一個問題：如果某個愛好中的第二大年齡有多個相同的怎么辦？
a huangxiaoming 45
a liuyifei 35
b huangzitao 36
c huangixaoming 45
c huanglei 41
（2）代碼實作
- 思路總結:
- 1、explode() + lateral view
- 2、求TopN + row_number()
from
(
select b.id, b.name, b.age, b.favor,
row_number() over (partition by b.favor order by b.age desc) as rank
from
(
select a.id as id, a.name as name, a.age as age, favor_view.favor
from exercise_topn a
LATERAL VIEW explode(split(a.favors, "-")) favor_view as favor
) b
) c
where c.rank <= 2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/436401.html
標籤：其他