一、需求場景分析
在實際的資料平台營運管理過程中，資料表的規模往往随着更多業務資料的接入以及資料應用的建設而逐漸增長到非常大的規模，資料管理人員往往希望能夠利用中繼資料的分析來更好地掌握不同資料表的使用情況，進而優化資料模型。
一個MaxCompute項目中經常使用的表簡稱為熱門表，使用次數較少或者很長時間不使用的表簡稱為冷門表，本文将介紹如何去通過MaxCompute中繼資料資訊去分析熱門表和冷門表。
二、方案設計思路
MaxCompute Information_Schema提供了項目中全量的表中繼資料資訊Tables以及包含通路表的作業明細資料tasks_history，通過彙總各個表被作業通路的次數可以獲知不同表被作業使用的頻度。
詳細步驟如下：
1、熱門資料通過擷取tasks_history表裡的input_tables字段的詳細資訊，然後通過count統計一定時間分區内的各個表使用次數
2、冷門資料通過tables和tasks_history裡的input_tables表的作業彙總數量進行關聯、排序，進而統計出各張表在規定時間内的使用次數，正序排列
三、方案實作方法
1、擷取tasks_history表裡的input_tables字段的詳細資訊。如下圖所示：
select
inst_id ,
input_tables,
output_tables,
start_time,
end_time
from information_schema.tasks_history
where ds='20190902'limit 100;
查詢資料的結果如下圖所示：
發現在tasks_history表中input_tables字段格式為
["lightning.customer","lightning.orders_delta"]
是以在統計的時候需要對字段進行按逗号分割
注意：案例中的時間分區可以根據需求去調整範圍，區間根據實際場景去做相應的調整
例如：Ds>='20190902' and Ds<='20190905'
函數處理如下：
select
--去掉input_tables 字段中開始和結尾的[]
trans_array(1,",",inst_id,replace(replace(input_tables,"[",""),"]","")) as (inst_id,input_table)
from information_schema.tasks_history
--日期可以根據實際需求去限定，這裡以20190902為例
where ds='20190902' limit 100;
處理結果如下圖:
2、統計熱門表資料SQL編寫:
--按表名進行統計計算
input_table
,count(distinct inst_id) table_read_num
from
(
trans_array(1,",",inst_id,replace(replace(input_tables,"[",""),"]","")) as (inst_id,input_table)
where ds='20190902'
) t
group by input_table
order by table_read_num desc
limit 1000;
結果如下圖所示:
3、統計冷門表資料SQL編寫:
通過tables和tasks_history裡的input_tables表的作業彙總數量進行關聯、排序，進而統計出各張表在規定時間内的使用次數，正序排列。
t1.table_schema,
t1.table_name,
--兩表關聯
if(t2.table_read_num is null,0,table_read_num) as table_read_num
FROM information_schema.tables t1
left join(
--去掉表名前後的”符号
regexp_replace(t.input_table,""","") as input_table
,count(distinct t.inst_id) table_read_num
from
where ds='20190902' )t
)t2
--關聯條件比對
on concat('your_project_name.',t1.table_name)=t2.input_table
結果如下所示：
所有的表按照使用次數進行排序
即可得到各個表的使用次數排序資訊。進而去進行合理化的管理資料表。
注意：SQL中的” your_project_name.”為表名字首，客戶需要參照自己的實際資料去做相應的修改調整。
歡迎加入“MaxCompute開發者社群2群”,點選連結申請加入或掃描二維碼
https://h5.dingtalk.com/invite-page/index.html?bizSource=____source____&corpId=dingb682fb31ec15e09f35c2f4657eb6378f&inviterUid=E3F28CD2308408A8&encodeDeptId=0054DC2B53AFE745