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提供了将不同表轉化為極限存儲表的能力。極限存儲操作示例如下:

建立源表。

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

導入資料。
将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');

拉連結清單設計更詳細介紹可以參考雲栖文章:

https://yq.aliyun.com/articles/542146#? spm=a2c41.11181499.0.0

采集源表的設計

資料采集方式:流式資料寫入，批量資料寫入，周期排程條式資料插入。

大資料量情況下，確定同一個業務單元的資料使用分區和表進行分;在資料量較小情況下，優化采集頻率。

流式資料寫入。
- 對于流式寫入的資料，一般采集的通道較多，相關采集通道應做有效區分，在單個資料通道寫入量較大的情況下應該進行按照時間進行分區設計。
- 在采集通道資料量較小的情況下可以采取非分區表設計，對終端類型和采集時間設計成标準列字段。
- 采用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 col2
from 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.col2
from 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 select
from (
-- 先把上日存在，今日也存在的記錄從上日表中排除。剩下的就是今日沒有更新的記錄 select t1.key1
,t1.key2 ,t1.col1 ,t1.col2
from table1 t1
left outer join table2 t2 on t1.key1=t2.key1 and t1.key2 = t2.key2 where t2.key1 is null
union all
-- 再合并上今日增量，就是今天的全量 select t2.key1
select t2.key1
  ,t2.key2
  ,t2.col1
  ,t2.col2
from table2 t2)tt
;

merge(有del)

insert overwrite table table1 select

from (

-- 先把上日存在，今日也存在的記錄從上日表中排除，再把今日删除的記錄排除。剩下的就是今日沒有更新的記錄

insert overwrite table table1 select
from (
-- 先把上日存在，今日也存在的記錄從上日表中排除，再把今日删除的記錄排除。剩下的就是今日沒有更 新的記錄
select t1.key1
,t1.key2 ,t1.col1 ,t1.col2
from 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 null
union all
-- 再合并上今日增量，就是今天的全量 select t2.key1
,t2.key2 ,t2.col1 ,t2.col2
from 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上傳資料是無序的。

MaxCompute表設計最佳實踐MaxCompute表設計最佳實踐MaxCompute表的特色功能

MaxCompute表設計最佳實踐MaxCompute表設計最佳實踐MaxCompute表的特色功能

MaxCompute表設計最佳實踐

産生大量小檔案的操作

根據資料劃分項目空間

“次元表”設計的最佳實踐:

拉連結清單設計 – 極限存儲的應用

采集源表的設計

日志表的設計

互動明細表的設計

MaxCompute表資料更新與删除操作

表建立設計示例

MaxCompute表的特色功能

生命周期

避免全表掃描

避免小檔案

轉化Hash Clustering表

繼續閱讀

【51CTO學院三周年】自學路上的伴侶

線上教育巨頭多鄰國Duolingo入華一周年，中國市場馬力全開

【分類算法】什麼是分類算法定義分類與聚類分類過程方法

申請評分模型拒絕推斷（RI）方法申請評分模型拒絕推斷（RI）方法

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

Sql優化一：sql語句優化

Nacos 2.0 更新前後性能對比壓測

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

Storm編譯打包過程中遇到的一些問題及解決方法

MapReduce的幾個企業級經典面試案例MapReduce的幾個企業級經典面試案例

9.spark Core 進階2--Cashe

淺談企業活動中進行資料分析的重要性

Ambari介紹和架構原理

NOSQL安全攻擊

win10本地scala和spark安裝安裝scala安裝spark