MaxCompute表設計最佳實踐

產生大量小檔案的操作

MaxCompute表的小檔案會影響儲存和計算效能，因此我們先介紹下什麼樣的操作會產生大量小檔案，從 而在做表設計的時候考慮避開此類操作。

• 使用MaxCompute Tunnel SDK上傳資料，上傳過程中，每commit一次就會產生一個檔案。這時每 個檔案過小(比如幾K)，並且頻繁上傳(比如5秒上傳)一次，則一小時就會產生720個小檔案，一 天就會產生17280個小檔案。

• 使用MaxCompute Tunnel SDK上傳資料，create了session但是沒有upload資料直接做了 commit，產生大量空目錄(服務側等同於小檔案)。

• 使用MaxCompute Console命令列工具Tunnel命令上傳時，將本地大檔案切分過小，導致上傳後產 生檔案數過多，檔案過小。

• 透過DataHub做資料歸檔，Datahub 的每個shard寫 MaxCompute 有兩個條件:資料總量達到 64MB，commit 一次到 MaxCompute，形成一個檔案。或者每隔 5 分鐘一次 commit，形成一個 檔案。那麼:開的shard數多(比如20個shard)，每個shard資料在5分鐘內都遠遠達不到64M，比 如就是幾百K，就會產生大量小檔案。那麼一天就會產生241220=5760個小檔案。

• 透過Dataworks等資料開發工具進行資料增量插入(insert into)到MaxCompute的表(或者表分 區)裡時，每個insert into都會產生一個檔案，若每次insert into 10條，每天累計insert insert 10000條記錄，則會產生1000個小檔案。

• 透過阿里雲DTS將資料從RDS等資料庫同步到MaxCompute，DTS進行資料同步時，會建立全量表和 增量表，增量表程式資料插入過程中會因為每次資料插入條數較少而commit比較完整一次資料同步， 從而在增量表中造成小檔案問題，比如每隔5分支執行一次同步，每次同步的資料量為10條，一天內的 增量為10000條，則會產生1000個小檔案。此種場景，需要在資料同步完成後進行全量極限表和增量 資料表的merge。

• 源資料採集客戶端太多，源資料透過T unnel直接進入到一個分割槽，每個源資料採集客戶端提交一次數 據，都會在同一分割槽下產生一個獨立的檔案導致產生大量小檔案。

• SLS 觸發 FunctionCompute持續高頻往MaxCompute中心接入檔案，小檔案流式資料進入 MaxCompute。

根據資料劃分專案空間

專案空間(Project)是MaxCompute最高層的物件，按專案空間進行資源的分配、隔離和管理，實現了 多租戶的管理能力。

• 如果多個應用需要共享“資料”，則推薦使用同一個專案空間。

• 如果多個應用所需“資料”是無關的，則推薦使用不同的專案空間。 專案空間間的表和分割槽可以透過Package授權的方式進行交換。

“維度表”設計的最佳實踐:

一般情況下描述屬性的表設計為維度表。維度表可以和任意表組的任意表進行關聯，並且建立時不需要配 置分割槽資訊，但是對單表資料量大小有所限制。維度表的設計和使用注意以下幾點:

• 一般要求維度表單表不超過1000萬。

• 維度表的資料不應該被大量更新。

• 維度表和其他表進行Join操作時可以使用mapjoin。

拉鍊表設計 – 極限儲存的應用

極限儲存功能待發布，在此介紹主要提供設計思想。 基於MaxCompute的拉鍊表設計背景 在資料倉儲的資料模型設計過程中，經常會遇到這樣的需求:

• 資料量比較大。 表中的部分欄位會被update,如使用者的地址，產品的描述資訊，訂單的狀態、手機號碼等等。

• 需要檢視某一個時間點或者時間段的歷史快照資訊。(比如，檢視某一個訂單在歷史某一個時間點的 狀態，比如，檢視某一個使用者在過去某一段時間內，更新過幾次等等)

• 變化的比例和頻率不是很大，比如，總共有1000萬的會員，每天新增和發生變化的有10萬左右，如果表每天都保留一份全量，那麼每次全量中會儲存很多不變的資訊，對儲存是極大的浪費。
  考慮極限儲存的使用 : MaxCompute提供了將不同錶轉化為極限儲存表的能力。極限儲存操作示例如下:

1. 建立源表。

create table src_tbl (key0 STRING, key1 STRING, col0 STRING, col1 STRING, col2 STRING) PARTITIO N (datestam p_x STRING, pt0 STRING);

1. 匯入資料。

2. 將src_tbl轉變為極限儲存的表。

set odps.exstore.primarykey=key0,key1;
[set odps.exstore.ignorekey=col0;]
EXSTO RE exstore_tbl PARTITIO N (datestam p_x='20140801'); EXSTO RE exstore_tbl PARTITIO N (datestam p_x='20140802');

拉鍊表設計更詳細介紹可以參考雲棲文章:

採集源表的設計

資料採集方式:流式資料寫入， 批次資料寫入，週期排程條式資料插入。
大資料量情況下，確保同一個業務單元的資料使用分割槽和表進行分;在資料量較小情況下，最佳化採集頻率。

• 流式資料寫入。

• 對於流式寫入的資料，一般採集的通道較多，相關採集通道應做有效區分，在單個資料通道寫入 量較大的情況下應該進行按照時間進行分割槽設計。

• 在採集通道資料量較小的情況下可以採取非分割槽表設計，對終端型別和採集時間設計成標準列字 段。

• 採用Datahub進行資料寫入時應該合理規劃shard數量，放置由於shard過多而造成採集通道流量 較小且通道較多的問題。

• 批次資料寫入。批次資料寫入重點關注寫入週期 週期排程條式資料插入。

• 避免週期資料插入，此種情況下需要建立分割槽表，在新分割槽進行插入操作， 減少對於原來分割槽影響。

日誌表的設計

日誌其實是個流水錶，不涉及記錄的更新，來一條採集一條，多條一起存放，日誌表設計的主要注意幾 點:

create table src_tbl (key0 STRING, key1 STRING, col0 STRING, col1 STRING, col2 STRING) PARTITIO N (datestam p_x STRING, pt0 STRING);
  set odps.exstore.primarykey=key0,key1;
[set odps.exstore.ignorekey=col0;]
EXSTO RE exstore_tbl PARTITIO N (datestam p_x='20140801'); EXSTO RE exstore_tbl PARTITIO N (datestam p_x='20140802');

• 考慮是否需要對日誌進行去重處理。

• 考慮是否需要擴充套件維度屬性。

• 是否需要關聯維表擴充套件維度屬性欄位考慮兩點:業務使用的頻次，關聯是否會造成的產出的延 遲。

• 需要仔細選擇是否對於維度表進行擴充套件

• 考慮區分終端型別。

• 日誌表由於量大，考慮在業務分析使用時通常會按PC端，APP端來統計分析，同時PC端、APP端 的採集是兩套體系，因此通常的做法會按終端設計多個明細DWD表。

• 如果終端較多，但資料量不大的情況下，如一個終端的資料小於1T 但是採集次數較多，可以考慮 不對終端進行分割槽，而設定終端資訊為普通列。

注意:

• 對於日誌表進行分割槽設計，可以按照日誌採集的時間按照天進行分割槽，在入資料前進行資料採集整 合，一批資料寫入提交一次(通常是64M)。

• 日誌資料很少有對原來分割槽的更新操作，可以採用insert 進行少量資料的插入，但一般需要限制插入 次數。

• 如果有大量的更新的操作，需要採用insert overwrite操作避免小檔案問題。

• 對日誌表設定合理的分割槽和對已經⻓久不訪問的冷熱資料配置歸檔操作。

互動明細表的設計

週期快照表，每天對收藏的所有記錄進行快照存放。
問題 :歷史累計的記錄非常多，每天生成快照要拿當天增量表與前一天的全量表merge，非常耗資源。統 計最近1天的新增收藏數，需要掃描全量表，如何降低資源?
建議的方案 :建立一個事務性事實表，在建立一個存放當前有效收藏的週期快照表，以滿足各種不同業務 的統計分析需要。
注意 :

• 設計互動明細表最重要的是要區分存量資料和增量資料之間的關係。 - 對於新分割槽的資料可以寫入，作為增量資料。

• 應儘量減少對於老的分割槽資料的修改和插入。

• 在資料插入和全表覆蓋寫種選擇時應儘量選用insert overwrite而並選擇insert into。

MaxCompute表資料更新與刪除操作

關係型資料庫支援的 delete/update/merge SQL ，在MaxCompute上的實現方式示例如下:
表準備

-- 上日全量表
table1(key1 string,key2 string,col1 string,col2 string);
-- 今日增量表
table2(key1 string,key2 string,col1 string,col2 string);
-- 今日增量表(刪除)
table3(key1 string,key2 string,col1 string,col2 string);

update(table2 表中的記錄的值，更新到table1表中)

insert overwrite table table1 select t1.key1
,t1.key2
,case when t2.key1 is not null then t2.col1 else t1.col1 end as col1 ,case when t2.key1 is not null then t2.col2 else t1.col2 end as col2from table1 t1
left outer join table2 t2 on t1.key1=t2.key1 and t1.key2 = t2.key2 ;

 delete(table2 表中的記錄，從table1表中刪除)

insert overwrite table table1 select t1.key1
,t1.key2 ,t1.col1 ,t1.col2from table1 t1
left outer join table2 t2 on t1.key1=t2.key1 and t1.key2 = t2.key2 where t2.key1 is null;

 merge(沒有del)

insert overwrite table table1 selectfrom (
-- 先把上日存在，今日也存在的記錄從上日表中排除。剩下的就是今日沒有更新的記錄 select t1.key1
,t1.key2 ,t1.col1 ,t1.col2from table1 t1
left outer join table2 t2 on t1.key1=t2.key1 and t1.key2 = t2.key2 where t2.key1 is nullunion all
-- 再合併上今日增量，就是今天的全量 select t2.key1
select t2.key1
  ,t2.key2
  ,t2.col1
  ,t2.col2from table2 t2)tt
;

merge(有del)
 insert overwrite table table1 select
from (
-- 先把上日存在，今日也存在的記錄從上日表中排除，再把今日刪除的記錄排除。剩下的就是今日沒有更 新的記錄

insert overwrite table table1 selectfrom (
-- 先把上日存在，今日也存在的記錄從上日表中排除，再把今日刪除的記錄排除。剩下的就是今日沒有更 新的記錄
select t1.key1
,t1.key2 ,t1.col1 ,t1.col2from table1 t1
left outer join table2 t2 on t1.key1=t2.key1 and t1.key2 = t2.key2 left outer join table3 t3 on t1.key1=t3.key1 and t1.key2 = t3.key2
where t2.key1 is null or t2.key1 is nullunion all
-- 再合併上今日增量，就是今天的全量 select t2.key1
,t2.key2 ,t2.col1 ,t2.col2from table2 t2)tt ;

表建立設計示例

場景:天氣情況資訊採集。

• 基本資訊: 資料資訊包括地名，關於此地的屬性數如面積，基本人口數量等資訊，天氣資訊。

• 屬性資料變化較小，天氣資訊數採用多個終端採集，且資料量較大

• 天氣資訊變化較大，終端數量穩定的情況下流量基本穩定。
  表設計指南:

• 建議對資料資訊劃分為基本屬性表，和天氣日誌表，區分變化小和變化大的資料。

• 因為資料量巨大，對天氣日誌表按照地域進行分割槽，也可以按照時間如天進行二級分割槽，此種分割槽方 式避免因某一地或某一個時間的天氣變化而造成其他無關資料變化。

• 採集終端上使用datahub進行資料匯聚，依據穩定的流量值選擇合適的shard通道數量，批次資料方式 寫入到天氣日誌表中，不使用Insert into。

MaxCompute表的特色功能

生命週期

MaxCompute表/分割槽提供資料生命週期管理。表(分割槽)資料從最後一次更新時間算起，在經過指定的 時間後沒有變動，則此表(分割槽)將被MaxCompute自動回收。這個指定的時間就是生命週期，生命週期 設定為表級別。

create table test_lifecycle(key string) lifecycle 100;/alter table test_l ifecycle set lifecycle 50;

MaxCompute會根據每張非分割槽表或者分割槽的的LastDataModifiedTime以及lifecycle的設定來判斷是 否要回收此非分割槽表或者分割槽表中的分割槽。 MaxCompute SQL提供touch操作用來修改分割槽的 LastDataModifiedTime。會將分割槽的LastDataModifiedTime修改為當前時間。修改 LastDataModifiedTime的值，MaxCompute會認為表或分割槽的資料有變動，生命週期的計算會重新開始。

ALTER TABLE table_nam e TO UCH PARTITIO N(partition_col='partition_col_valu e', ...);

注意:

• 合理規劃表的生命週期，在建立表時即設定生命週期，可有效減少儲存壓力。

• 對錶資料的任何變動都會影響生命週期回收資料的判斷時間，包括小檔案合併。

避免全表掃描

表設計:

• 建立分割槽表或者對掃描條件進行列設計。

• 對資料表進行合理分割槽。 對常用查詢條件設定成列名。

• 讀常用查詢條件進行hash clustering
  資料計算:

• 加分割槽過濾條件，或者減少掃描分割槽數,或者拆出中間小表然後再掃描小錶的歷史分割槽以減少資料掃描 量。

• 把全域性掃描表中間結果進行儲存形成中間表。

• 如果每天都去掃一年的分割槽，計算消耗是非常大的，建議拆出一張中間表，每天做一次彙總，然後再 去掃描這張中間表的一年分割槽，掃描資料量會減少很多。

避免小檔案

• Reduce計算過程產生的小檔案:只需要insert overwrite源表(或分割槽)即可，或者寫入到新表刪除 源表。

• Tunnel資料採集過程中產生的小檔案建議:

• 呼叫tunnelsdk時當buffer達到64M時提交一次;

• 使用console時避免頻繁上傳小檔案，建議積累較大時一次性上傳; 如果匯入的是分割槽表，建議給分割槽設定生命週期，過期不用的資料自動清理;

• 同第一種方案，insertoverwrite源表(或分割槽);

• ALTER合併模式，透過console命令進行合併。

• 使用臨時表建議建立時都加上生命週期，到期後垃圾回收自動回收。 - 申請過多的datahub shard將會產生小檔案問題，申請datahub shard數目的策略 :

• 預設吞吐量單個shard是1MB/s，可以按照這個分配實際的shard數目(可以在此基礎上多加幾 個);

• 同步odps的邏輯是每個shard有一個單獨的task(滿足5分鐘或者64MB會commit一次)，預設設定5分鐘是為了儘快能在odps查到資料。如果是按照小時建partition，那個一個shard每個小 時有12個檔案。

• 如果這個時候資料量很少，但是shard很多，在odps裡面就會很多小檔案(shard*12/hour)。

• 不要過多的分配shard，按需分配。

轉化Hash Clustering表

Hash Clustering表的優勢:最佳化Bucket Pruning/最佳化Aggregation/最佳化儲存。 在建立表時使用CLUSTERED BY指定Hash Key，MaxCompute將對指定列進行Hash運算，按照Hash 值分散到各個Bucket裡面。
Hash Key指選擇原則:

• 選擇重複鍵值少的列

• SORTED BY用於指定在Bucket內欄位的排序方式。
  如何轉化為HashClustering表:

ALTER TABLE table_nam e [CLUSTERED BY (col_nam e [, col_nam e, ...]) [SO RTED B Y (col_nam e [ASC | DESC] [, col_nam e [ASC | DESC] ...])] INTO num ber_of_buck ets BUCKETS]

ALTER TABLE語句適用於存量表，在增加了新的聚集屬性之後，新的分割槽將做hash cluster儲存。 建立 完HashClustering的表之後使用insert overwrite從另外一個源表進行轉化。
注意，Hash Clustering表有以下限制:

• 不支援insert into，只能透過insert overwrite來新增資料。

• 不支援tunnel直接upload到range cluster表，因為tunnel上傳資料是無序的。