MaxCompute表設(shè)計(jì)方法是什么

這篇文章主要介紹“MaxCompute表設(shè)計(jì)方法是什么”，在日常操作中，相信很多人在MaxCompute表設(shè)計(jì)方法是什么問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MaxCompute表設(shè)計(jì)方法是什么”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

MaxCompute表設(shè)計(jì)最佳實(shí)踐

產(chǎn)生大量小文件的操作

MaxCompute表的小文件會(huì)影響存儲(chǔ)和計(jì)算性能，因此我們先介紹下什么樣的操作會(huì)產(chǎn)生大量小文件，從 而在做表設(shè)計(jì)的時(shí)候考慮避開此類操作。

• 使用MaxCompute Tunnel SDK上傳數(shù)據(jù)，上傳過程中，每commit一次就會(huì)產(chǎn)生一個(gè)文件。這時(shí)每 個(gè)文件過小(比如幾K)，并且頻繁上傳(比如5秒上傳)一次，則一小時(shí)就會(huì)產(chǎn)生720個(gè)小文件，一 天就會(huì)產(chǎn)生17280個(gè)小文件。

• 使用MaxCompute Tunnel SDK上傳數(shù)據(jù)，create了session但是沒有upload數(shù)據(jù)直接做了 commit，產(chǎn)生大量空目錄(服務(wù)側(cè)等同于小文件)。

• 使用MaxCompute Console命令行工具Tunnel命令上傳時(shí)，將本地大文件切分過小，導(dǎo)致上傳后產(chǎn) 生文件數(shù)過多，文件過小。

• 通過DataHub做數(shù)據(jù)歸檔，Datahub 的每個(gè)shard寫 MaxCompute 有兩個(gè)條件:數(shù)據(jù)總量達(dá)到 64MB，commit 一次到 MaxCompute，形成一個(gè)文件。或者每隔 5 分鐘一次 commit，形成一個(gè) 文件。那么:開的shard數(shù)多(比如20個(gè)shard)，每個(gè)shard數(shù)據(jù)在5分鐘內(nèi)都遠(yuǎn)遠(yuǎn)達(dá)不到64M，比 如就是幾百K，就會(huì)產(chǎn)生大量小文件。那么一天就會(huì)產(chǎn)生241220=5760個(gè)小文件。

• 通過Dataworks等數(shù)據(jù)開發(fā)工具進(jìn)行數(shù)據(jù)增量插入(insert into)到MaxCompute的表(或者表分 區(qū))里時(shí)，每個(gè)insert into都會(huì)產(chǎn)生一個(gè)文件，若每次insert into 10條，每天累計(jì)insert insert 10000條記錄，則會(huì)產(chǎn)生1000個(gè)小文件。

• 通過阿里云DTS將數(shù)據(jù)從RDS等數(shù)據(jù)庫同步到MaxCompute，DTS進(jìn)行數(shù)據(jù)同步時(shí)，會(huì)創(chuàng)建全量表和 增量表，增量表進(jìn)程數(shù)據(jù)插入過程中會(huì)因?yàn)槊看螖?shù)據(jù)插入條數(shù)較少而commit比較完整一次數(shù)據(jù)同步， 從而在增量表中造成小文件問題，比如每隔5分支執(zhí)行一次同步，每次同步的數(shù)據(jù)量為10條，一天內(nèi)的 增量為10000條，則會(huì)產(chǎn)生1000個(gè)小文件。此種場(chǎng)景，需要在數(shù)據(jù)同步完成后進(jìn)行全量極限表和增量 數(shù)據(jù)表的merge。

• 源數(shù)據(jù)采集客戶端太多，源數(shù)據(jù)通過T unnel直接進(jìn)入到一個(gè)分區(qū)，每個(gè)源數(shù)據(jù)采集客戶端提交一次數(shù) 據(jù)，都會(huì)在同一分區(qū)下產(chǎn)生一個(gè)獨(dú)立的文件導(dǎo)致產(chǎn)生大量小文件。

• SLS 觸發(fā) FunctionCompute持續(xù)高頻往MaxCompute中心接入文件，小文件流式數(shù)據(jù)進(jìn)入 MaxCompute。

根據(jù)數(shù)據(jù)劃分項(xiàng)目空間

項(xiàng)目空間(Project)是MaxCompute最高層的對(duì)象，按項(xiàng)目空間進(jìn)行資源的分配、隔離和管理，實(shí)現(xiàn)了 多租戶的管理能力。

• 如果多個(gè)應(yīng)用需要共享“數(shù)據(jù)”，則推薦使用同一個(gè)項(xiàng)目空間。

• 如果多個(gè)應(yīng)用所需“數(shù)據(jù)”是無關(guān)的，則推薦使用不同的項(xiàng)目空間。 項(xiàng)目空間間的表和分區(qū)可以通過Package授權(quán)的方式進(jìn)行交換。

“維度表”設(shè)計(jì)的最佳實(shí)踐:

一般情況下描述屬性的表設(shè)計(jì)為維度表。維度表可以和任意表組的任意表進(jìn)行關(guān)聯(lián)，并且創(chuàng)建時(shí)不需要配 置分區(qū)信息，但是對(duì)單表數(shù)據(jù)量大小有所限制。維度表的設(shè)計(jì)和使用注意以下幾點(diǎn):

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

• 維度表的數(shù)據(jù)不應(yīng)該被大量更新。

• 維度表和其他表進(jìn)行Join操作時(shí)可以使用mapjoin。

拉鏈表設(shè)計(jì) – 極限存儲(chǔ)的應(yīng)用

極限存儲(chǔ)功能待發(fā)布，在此介紹主要提供設(shè)計(jì)思想。 基于MaxCompute的拉鏈表設(shè)計(jì)背景 在數(shù)據(jù)倉庫的數(shù)據(jù)模型設(shè)計(jì)過程中，經(jīng)常會(huì)遇到這樣的需求:

• 數(shù)據(jù)量比較大。 表中的部分字段會(huì)被update,如用戶的地址，產(chǎn)品的描述信息，訂單的狀態(tài)、手機(jī)號(hào)碼等等。

• 需要查看某一個(gè)時(shí)間點(diǎn)或者時(shí)間段的歷史快照信息。(比如，查看某一個(gè)訂單在歷史某一個(gè)時(shí)間點(diǎn)的 狀態(tài)，比如，查看某一個(gè)用戶在過去某一段時(shí)間內(nèi)，更新過幾次等等)

• 變化的比例和頻率不是很大，比如，總共有1000萬的會(huì)員，每天新增和發(fā)生變化的有10萬左右，如果表每天都保留一份全量，那么每次全量中會(huì)保存很多不變的信息，對(duì)存儲(chǔ)是極大的浪費(fèi)。
  考慮極限存儲(chǔ)的使用: MaxCompute提供了將不同表轉(zhuǎn)化為極限存儲(chǔ)表的能力。極限存儲(chǔ)操作示例如下:

1. 創(chuàng)建源表。

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

1. 導(dǎo)入數(shù)據(jù)。

2. 將src_tbl轉(zhuǎn)變?yōu)闃O限存儲(chǔ)的表。

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');

采集源表的設(shè)計(jì)

數(shù)據(jù)采集方式:流式數(shù)據(jù)寫入， 批量數(shù)據(jù)寫入，周期調(diào)度條式數(shù)據(jù)插入。
大數(shù)據(jù)量情況下，確保同一個(gè)業(yè)務(wù)單元的數(shù)據(jù)使用分區(qū)和表進(jìn)行分;在數(shù)據(jù)量較小情況下，優(yōu)化采集頻率。

• 流式數(shù)據(jù)寫入。

• 對(duì)于流式寫入的數(shù)據(jù)，一般采集的通道較多，相關(guān)采集通道應(yīng)做有效區(qū)分，在單個(gè)數(shù)據(jù)通道寫入 量較大的情況下應(yīng)該進(jìn)行按照時(shí)間進(jìn)行分區(qū)設(shè)計(jì)。

• 在采集通道數(shù)據(jù)量較小的情況下可以采取非分區(qū)表設(shè)計(jì)，對(duì)終端類型和采集時(shí)間設(shè)計(jì)成標(biāo)準(zhǔn)列字 段。

• 采用Datahub進(jìn)行數(shù)據(jù)寫入時(shí)應(yīng)該合理規(guī)劃shard數(shù)量，放置由于shard過多而造成采集通道流量 較小且通道較多的問題。

• 批量數(shù)據(jù)寫入。批量數(shù)據(jù)寫入重點(diǎn)關(guān)注寫入周期 周期調(diào)度條式數(shù)據(jù)插入。

• 避免周期數(shù)據(jù)插入，此種情況下需要建立分區(qū)表，在新分區(qū)進(jìn)行插入操作， 減少對(duì)于原來分區(qū)影響。

日志表的設(shè)計(jì)

日志其實(shí)是個(gè)流水表，不涉及記錄的更新，來一條采集一條，多條一起存放，日志表設(shè)計(jì)的主要注意幾 點(diǎn):

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');

• 考慮是否需要對(duì)日志進(jìn)行去重處理。

• 考慮是否需要擴(kuò)展維度屬性。

• 是否需要關(guān)聯(lián)維表擴(kuò)展維度屬性字段考慮兩點(diǎn):業(yè)務(wù)使用的頻次，關(guān)聯(lián)是否會(huì)造成的產(chǎn)出的延 遲。

• 需要仔細(xì)選擇是否對(duì)于維度表進(jìn)行擴(kuò)展

• 考慮區(qū)分終端類型。

• 日志表由于量大，考慮在業(yè)務(wù)分析使用時(shí)通常會(huì)按PC端，APP端來統(tǒng)計(jì)分析，同時(shí)PC端、APP端 的采集是兩套體系，因此通常的做法會(huì)按終端設(shè)計(jì)多個(gè)明細(xì)DWD表。

• 如果終端較多，但數(shù)據(jù)量不大的情況下，如一個(gè)終端的數(shù)據(jù)小于1T 但是采集次數(shù)較多，可以考慮 不對(duì)終端進(jìn)行分區(qū)，而設(shè)置終端信息為普通列。

注意:

• 對(duì)于日志表進(jìn)行分區(qū)設(shè)計(jì)，可以按照日志采集的時(shí)間按照天進(jìn)行分區(qū)，在入數(shù)據(jù)前進(jìn)行數(shù)據(jù)采集整 合，一批數(shù)據(jù)寫入提交一次(通常是64M)。

• 日志數(shù)據(jù)很少有對(duì)原來分區(qū)的更新操作，可以采用insert 進(jìn)行少量數(shù)據(jù)的插入，但一般需要限制插入 次數(shù)。

• 如果有大量的更新的操作，需要采用insert overwrite操作避免小文件問題。

• 對(duì)日志表設(shè)置合理的分區(qū)和對(duì)已經(jīng)?久不訪問的冷熱數(shù)據(jù)配置歸檔操作。

互動(dòng)明細(xì)表的設(shè)計(jì)

周期快照表，每天對(duì)收藏的所有記錄進(jìn)行快照存放。
問題:歷史累計(jì)的記錄非常多，每天生成快照要拿當(dāng)天增量表與前一天的全量表merge，非常耗資源。統(tǒng) 計(jì)最近1天的新增收藏?cái)?shù)，需要掃描全量表，如何降低資源?
建議的方案:建立一個(gè)事務(wù)性事實(shí)表，在建立一個(gè)存放當(dāng)前有效收藏的周期快照表，以滿足各種不同業(yè)務(wù) 的統(tǒng)計(jì)分析需要。
注意:

• 設(shè)計(jì)互動(dòng)明細(xì)表最重要的是要區(qū)分存量數(shù)據(jù)和增量數(shù)據(jù)之間的關(guān)系。 - 對(duì)于新分區(qū)的數(shù)據(jù)可以寫入，作為增量數(shù)據(jù)。

• 應(yīng)盡量減少對(duì)于老的分區(qū)數(shù)據(jù)的修改和插入。

• 在數(shù)據(jù)插入和全表覆蓋寫種選擇時(shí)應(yīng)盡量選用insert overwrite而并選擇insert into。

MaxCompute表數(shù)據(jù)更新與刪除操作

關(guān)系型數(shù)據(jù)庫支持的 delete/update/merge SQL ，在MaxCompute上的實(shí)現(xiàn)方式示例如下:
表準(zhǔn)備

-- 上日全量表
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 ;

表創(chuàng)建設(shè)計(jì)示例

場(chǎng)景:天氣情況信息采集。

• 基本信息: 數(shù)據(jù)信息包括地名，關(guān)于此地的屬性數(shù)如面積，基本人口數(shù)量等信息，天氣信息。

• 屬性數(shù)據(jù)變化較小，天氣信息數(shù)采用多個(gè)終端采集，且數(shù)據(jù)量較大

• 天氣信息變化較大，終端數(shù)量穩(wěn)定的情況下流量基本穩(wěn)定。
  表設(shè)計(jì)指南:

• 建議對(duì)數(shù)據(jù)信息劃分為基本屬性表，和天氣日志表，區(qū)分變化小和變化大的數(shù)據(jù)。

• 因?yàn)閿?shù)據(jù)量巨大，對(duì)天氣日志表按照地域進(jìn)行分區(qū)，也可以按照時(shí)間如天進(jìn)行二級(jí)分區(qū)，此種分區(qū)方 式避免因某一地或某一個(gè)時(shí)間的天氣變化而造成其他無關(guān)數(shù)據(jù)變化。

• 采集終端上使用datahub進(jìn)行數(shù)據(jù)匯聚，依據(jù)穩(wěn)定的流量值選擇合適的shard通道數(shù)量，批量數(shù)據(jù)方式 寫入到天氣日志表中，不使用Insert into。

MaxCompute表的特色功能

生命周期

MaxCompute表/分區(qū)提供數(shù)據(jù)生命周期管理。表(分區(qū))數(shù)據(jù)從最后一次更新時(shí)間算起，在經(jīng)過指定的 時(shí)間后沒有變動(dòng)，則此表(分區(qū))將被MaxCompute自動(dòng)回收。這個(gè)指定的時(shí)間就是生命周期，生命周期 設(shè)置為表級(jí)別。

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

MaxCompute會(huì)根據(jù)每張非分區(qū)表或者分區(qū)的的LastDataModifiedTime以及l(fā)ifecycle的設(shè)置來判斷是 否要回收此非分區(qū)表或者分區(qū)表中的分區(qū)。 MaxCompute SQL提供touch操作用來修改分區(qū)的 LastDataModifiedTime。會(huì)將分區(qū)的LastDataModifiedTime修改為當(dāng)前時(shí)間。修改 LastDataModifiedTime的值，MaxCompute會(huì)認(rèn)為表或分區(qū)的數(shù)據(jù)有變動(dòng)，生命周期的計(jì)算會(huì)重新開始。

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

注意:

• 合理規(guī)劃表的生命周期，在創(chuàng)建表時(shí)即設(shè)置生命周期，可有效減少存儲(chǔ)壓力。

• 對(duì)表數(shù)據(jù)的任何變動(dòng)都會(huì)影響生命周期回收數(shù)據(jù)的判斷時(shí)間，包括小文件合并。

避免全表掃描

表設(shè)計(jì):

• 建立分區(qū)表或者對(duì)掃描條件進(jìn)行列設(shè)計(jì)。

• 對(duì)數(shù)據(jù)表進(jìn)行合理分區(qū)。 對(duì)常用查詢條件設(shè)置成列名。

• 讀常用查詢條件進(jìn)行hash clustering
  數(shù)據(jù)計(jì)算:

• 加分區(qū)過濾條件，或者減少掃描分區(qū)數(shù),或者拆出中間小表然后再掃描小表的歷史分區(qū)以減少數(shù)據(jù)掃描 量。

• 把全局掃描表中間結(jié)果進(jìn)行存儲(chǔ)形成中間表。

• 如果每天都去掃一年的分區(qū)，計(jì)算消耗是非常大的，建議拆出一張中間表，每天做一次匯總，然后再 去掃描這張中間表的一年分區(qū)，掃描數(shù)據(jù)量會(huì)減少很多。

避免小文件

• Reduce計(jì)算過程產(chǎn)生的小文件:只需要insert overwrite源表(或分區(qū))即可，或者寫入到新表刪除 源表。

• Tunnel數(shù)據(jù)采集過程中產(chǎn)生的小文件建議:

• 調(diào)用tunnelsdk時(shí)當(dāng)buffer達(dá)到64M時(shí)提交一次;

• 使用console時(shí)避免頻繁上傳小文件，建議積累較大時(shí)一次性上傳; 如果導(dǎo)入的是分區(qū)表，建議給分區(qū)設(shè)置生命周期，過期不用的數(shù)據(jù)自動(dòng)清理;

• 同第一種方案，insertoverwrite源表(或分區(qū));

• ALTER合并模式，通過console命令進(jìn)行合并。

• 使用臨時(shí)表建議創(chuàng)建時(shí)都加上生命周期，到期后垃圾回收自動(dòng)回收。 - 申請(qǐng)過多的datahub shard將會(huì)產(chǎn)生小文件問題，申請(qǐng)datahub shard數(shù)目的策略 :

• 默認(rèn)吞吐量單個(gè)shard是1MB/s，可以按照這個(gè)分配實(shí)際的shard數(shù)目(可以在此基礎(chǔ)上多加幾 個(gè));

• 同步odps的邏輯是每個(gè)shard有一個(gè)單獨(dú)的task(滿足5分鐘或者64MB會(huì)commit一次)，默認(rèn)設(shè)置5分鐘是為了盡快能在odps查到數(shù)據(jù)。如果是按照小時(shí)建partition，那個(gè)一個(gè)shard每個(gè)小 時(shí)有12個(gè)文件。

• 如果這個(gè)時(shí)候數(shù)據(jù)量很少，但是shard很多，在odps里面就會(huì)很多小文件(shard*12/hour)。

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

轉(zhuǎn)化Hash Clustering表

Hash Clustering表的優(yōu)勢(shì):優(yōu)化Bucket Pruning/優(yōu)化Aggregation/優(yōu)化存儲(chǔ)。 在創(chuàng)建表時(shí)使用CLUSTERED BY指定Hash Key，MaxCompute將對(duì)指定列進(jìn)行Hash運(yùn)算，按照Hash 值分散到各個(gè)Bucket里面。
Hash Key指選擇原則:

• 選擇重復(fù)鍵值少的列

• SORTED BY用于指定在Bucket內(nèi)字段的排序方式。
  如何轉(zhuǎn)化為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語句適用于存量表，在增加了新的聚集屬性之后，新的分區(qū)將做hash cluster存儲(chǔ)。 創(chuàng)建 完HashClustering的表之后使用insert overwrite從另外一個(gè)源表進(jìn)行轉(zhuǎn)化。
注意，Hash Clustering表有以下限制:

• 不支持insert into，只能通過insert overwrite來添加數(shù)據(jù)。

• 不支持tunnel直接upload到range cluster表，因?yàn)閠unnel上傳數(shù)據(jù)是無序的。

到此，關(guān)于“MaxCompute表設(shè)計(jì)方法是什么”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！