天天看點
傳回

天貓國際通過Hologres進行排行榜的實時互動式分析

作者：景聞 阿裡巴巴資料技術及産品部資料技術專家

一.業務背景

天貓國際營銷活動分析實時排行榜是在大促中幫助業務快速的分析商家或者品牌的交易和流量的資料情況，給下一步大促的銷售目标，流量蓄水等等做出營運決策；尤其是在活動當天當發現行業的問題之後，僅僅靠子行業的拆分不足以确定具體的問題，也不一定有具體的業務抓手，是以需要有到商家、品牌和商品粒度的資料來快速定位問題。

天貓國際通過Hologres進行排行榜的實時互動式分析

二.原技術方案

原始技術方案的架構如下圖所示，可以看到是非常典型的Lambda架構，實時和離線分别是兩套系統，離線資料通過MaxCompute（原MaxCompute）輕度彙總同步至MySQL，實時增量資料通過Blink清洗後同步至HBase，最後在資料服務裡面以View的形式将實時和離線資料合并，提供對外服務。

天貓國際通過Hologres進行排行榜的實時互動式分析

整個架構在實際業務執行中會有非常多的痛點，典型的有以下幾個：

1）ADS層模型任務多

流計算和批處理任務都分别需要開發基于商品，賣家，品牌粒度的滿足應用層的三個ADS模型資料，三個資料同步任務，分别需要建立三個oneservice服務，滿足三個資料子產品應用。

2）計算過程資料膨脹

在營銷活動分析的場景下，看資料都是基于天貓國際業務類型和行業為大前提，是以通常在離線和實時的計算任務中，我們都是并行同時計算好不同的bu類型和所有的行業粒度的資料，這就導緻了計算的過程中的資料的大量膨脹。

3）流批分離

目前産品上根據時間進行選擇讀取實時資料還是離線資料，三天之内的資料通過實時任務計算的資料，三天前的曆史資料是通過批處理任務計算的離線資料，存在兩套任務同時運作，增加了運維的複雜性。

4）産品搭建邏輯複雜

每一個産品展示的報表子產品都需要通過實時資料提供的os接口和離線資料提供的os來進行，産品搭建的工作量比較大，通過時間來判斷什麼時候來讀取離線os的資料，什麼時候來讀取實時os的資料，邏輯比較繁雜。

三.架構更新思考政策

思考：為了提升研發效率和産品搭建的效率，我們隻需要開發到DWD層的明細資料，明細資料隻需要存儲葉子類目，在互動式分層層面按需去關聯行業維表，來提供對外随機的查詢服務，進而節省了建構ADS層的多個模型資料以及資料服務接口的時間，在産品搭建上基于一個資料接口就能滿足多個不同的應用場景的資料服務，提升産品搭建層面的效率，降低了産品搭建時的邏輯代碼的複雜性。

政策：我們希望能夠有一款産品，能統一計算寫入任務，做到流批統一，對外提供自由的互動式查詢服務。

天貓國際通過Hologres進行排行榜的實時互動式分析

四.産品技術選型

在産品技術選型之前，需要先梳理業務需要用到的表以及資料量，選取幾張最具代表性的表用于驗證明際業務場景中産品技術可行性，以及驗證關鍵名額性能問，包括查詢QPS、寫入TPS等。主要選取的表以及資料量如下：

（1） 交易明細資料表

| buyer_id + item_id +order_id |
|  20191111   |    4800W       |
|  20200618   |    600W        |
|  20200721   |    300W        |

（2）流量IPV明細資料表

| visitor_id + item_id           |
|  20191111   |    2.1億         |
|  20200618   |    7000W         |
|  20200721   |    2600W         |

在技術選型方面，我們鎖定了兩款産品，一款是AnalyticDB for MySQL，一款是Hologres。ADB是阿裡雲資料庫事業部團隊提供的雲原生資料倉庫AnalyticDB MySQL版，是阿裡巴巴自主研發的海量資料實時高并發線上分析雲計算服務。Hologres是阿裡雲計算平台事業部提供的一款全面相容PostgreSQL協定并與大資料生态無縫打通的實時互動式分析産品。從實際業務場景出發，兩者的主要差別有以下幾點：

1）與MaxCompute的打通性

Hologres：與MaxCompute打通，可以直接通過外部表讀取MaxCompute資料進行查詢分析，無需存儲就能查詢。

ADB：能加速查詢MaxCompute，提供複雜互動式分析、實時混合資料倉庫等多種場景。

2）成本方面

從我們每年ADB和Hologres的的單價上對比，Hologres成本相比ADB略微低。

3）靈活度

均能滿足OLAP場景，Hologres相容相容PostgreSQL生态，ADB堅相容MySQL協定，均能滿足實時和離線批量的資料導入和分析。

4）性能

以下是Hologers的測試性能，資料量和大小均以MaxCompute的存儲格式為準，沒有進行一些特殊的索引和優化處理。

|  資料量              |   測試項                    |   響應時間
|  4600W(20.64GB)     |    SUM                      |   2.7s
|  2300W(5.04GB)      |    SUM                      |   1.1s
|  4600W(20.64GB)     |    COUNT                    |   2.5s
|  2300W(5.04GB)      |    COUNT                    |   1.0s
|  4600W(5.04GB)      |    COUNT(distinct)          |   2.8s
|  2300W(5.04GB)      |    COUNT(distinct)          |   1.6s
|  4600W(20.64GB)     |    AVG                      |   1.7s
|  2300W(5.04GB)      |    AVG                      |   0.9s
|  4600W(20.64GB)     |    ROW_NUMBER               |   2.6s
|  2300W(5.04GB)      |    AVG                      |   2.2s

結論：綜合很多種種因素，我們最終選擇Hologres作為互動式分析的查詢引擎

五.技術架構更新

使用Hologres後的架構如下圖所示。

天貓國際通過Hologres進行排行榜的實時互動式分析

技術架構更新後，主要有以下幾個優勢：

1. 計算統一

商品和賣家以及品牌粒度頁面的資料統一通過一個實時計算任務來統一計算，計算過程中不關聯行業和業務BU類型的維表，在互動式分析端按需統一通過商品ID和葉子類目ID進行關聯維表查詢。提升了研發的效率和降低了運維的難度，也沒有做到計算任務的膨脹，節省了計算資源。

2. 存儲統一

統一通過Hologres的内部分區表的形式存儲實時寫入的交易和流量明細資料，提供的統一的資料管理和維護，将存儲統一即可實作同庫間的任意互動式分析查詢服務。

3. 服務統一

商品和賣家以及品牌粒度的頁面均可以通過一份通用的具有占位符的FBI的資料集來實作，通過FBI的報表模式實作資料可視化，提升了産品搭建的效率。

六.Hologres實戰

下面将會講述Hologres在實時排行榜中的具體實踐過程：

1.建表模型設計

首先第一個是表設計，合理的表設計能夠大大提升查詢效率，尤其是當資料量變大的時候，相關索引的選擇和配置對性能優化有着非常大的作用。是以在Hologres中建立表之前，一定要想清楚表的使用場景，查詢邏輯。

在該方案中，由于需要涉及到交易的明細資料按照商品,賣家，品牌粒度彙總和流量資料進行join，同時還需要按照行業以及bu類型進行檢索，主要用到的表DDL如下：

sql
CREATE TABLE IF NOT EXISTS public.dwd_intl_trd_pay_itm_buyer_ri( 
stat_date text not null 
,stat_hour text not null 
,bu_id text not null
,bu_id_level1 text not null
,bu_id_level2 text not null
,item_id text not null
,item_title text
,item_tier text
,seller_id text
,seller_nick text
,seller_level text
,brand_id text
,brand_name text
,cate_id text not null
,pay_time text
,buyer_id text not null 
,div_idx_actual_total_fee float8
,is_wap text
,is_jhs text
,biz_order_id text not null
,buy_amount int8
,PRIMARY KEY (stat_date,stat_hour,item_id,buyer_id,biz_order_id)
)
PARTITION BY LIST(stat_date);
CALL SET_TABLE_PROPERTY('public.dwd_intl_trd_pay_itm_buyer_ri', 'orientation', 'column');
CALL set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'storage_format', 'segment');
CALL set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'segment_key', 'stat_date,stat_hour,bu_id,bu_id_level1,bu_id_level2,cate_id');
call set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'distribution_key', 'stat_date,stat_hour');
CALL set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'shard_count’, ‘30');


CREATE TABLE IF NOT EXISTS public.dwd_intl_log_ipv_itm_visitor_ri( 
stat_date text not null 
,stat_hour text not null 
,bu_id text not null
,bu_id_level1 text not null
,bu_id_level2 text not null
,item_id text not null
,item_title text
,seller_id text
,seller_nick text
,brand_id text
,brand_name text
,cate_id text not null
,visitor_id text not null 
,ipv int8 
,PRIMARY KEY (stat_date,stat_hour,item_id,visitor_id)
)
PARTITION BY LIST(stat_date);
CALL SET_TABLE_PROPERTY('public.dwd_intl_log_ipv_itm_visitor_ri', 'orientation', 'column');
CALL set_table_property('public.dwd_intl_log_ipv_itm_visitor_ri', 'storage_format', 'segment');
CALL set_table_property('public.dwd_intl_log_ipv_itm_visitor_ri', 'segment_key', 'stat_date,stat_hour,bu_id,bu_id_level1,bu_id_level2,cate_id');
call set_table_property('public.dwd_intl_log_ipv_itm_visitor_ri', 'distribution_key', 'stat_date,stat_hour');
CALL set_table_property('public.dwd_intl_log_ipv_itm_visitor_ri', 'shard_count', ‘50');

同時借助Hologres的表屬性設定，根據業務場景針對性優化，主要有以下幾點：

（1）基礎屬性的設定

distribution_key

指定分布列，資料将按照指定列，shuffle到各個shard

set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'distribution_key', 'stat_date,stat_hour,brand_id,seller_id');

clustering_key

指定一些列作為聚簇索引

set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri‘, 'clustering_key‘，'stat_date,stat_hour,cate_id');

segement_key

檔案索引，資料按該索引劃分檔案，可以通過segement_key快速索引到某一檔案

set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'segment_key', 'stat_date,stat_hour,bu_id,bu_id_level1,bu_id_level2,cate_id');

（2）進階屬性的設定

設定合理的TableGroup

Table Group非常關鍵的作用就是做local join，進而大大提升join效率，尤其是多表和比較大資料量join的時候

call set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'colocate_with', 'public.dwd_intl_log_ipv_itm_visitor_ri');

設定Shard_count

資料量：7億/230GB的資料量，設定在2Kcore左右，交易30和流量50

執行個體資源：1個shard至少需要1個core來負責計算

寫入性能：根據交易和流量的RPS來指定

Join需求：有奪标join的查詢case時，需要考慮TableGroup

sql
CALL set_table_property('public.dwd_intl_trd_pay_itm_buyer_ri', 'shard_count', '30');
CALL set_table_property('public.dwd_intl_log_ipv_itm_visitor_ri', 'shard_count', '30');

通過一系列的優化，在實際業務場景中達到的優化效果如下：

1）使用者增加五端通子產品從90s-->8s

2）淘寶直播子產品查詢從9s-->2s

3）目前實時查詢1.8s

2.Hologres與實時Blink的融合

目前流計算任務的開發是基于dataphin平台上采用BlinkSql進行的，這個平台讓實時任務的開發變得相當的容易，這裡主要記錄下當Hologres作為實時任務的sink的時候的一些需要注意的點：

1）sink表建立
  • 在實時任務開發的時候，任務的sink operator需要寫到Hologres的時候，需要在dataphin平台上建立源表，這個表需要同Hologres中的表字段和類型都要保持一緻，否則就會出現寫入成功，完全查詢不出來資料的情況發生。
2）分區路由設定
  • 當Hologres中的表是分區表的時候，實時任務是寫父表，資料會根據分區字段進行資料的路由動态寫入子表中(就是分區表)，但是這個子表需要提前建立好，否則實時任務會Failouver。分區子表的建立一般是通過dataworks的離線排程進行建立，天/周/月的建立都行。
  • dataphin設定：set tovsbi.dwd_intl_log_ipv_itm_visitor_ri.

    partitionRouter

     ='true'
  • sdp原生的設定：建立sink表的時候，在with後面添加

    partitionRouter

     = 'true'
3）資料寫入政策
  • 對于Hologres中有設定主鍵的表，這個時候需要考慮實時任務寫入Hologres的資料寫入政策。insertOrUpdate這個屬性進行控制，Holo導入時預設會丢棄掉重複的pk，配置為true将開啟Update功能

  • insertOrUpdate

  • insertOrUpdate

3.資料可視化展現

Hologres相容PostgreSQL生态，提供JDBC/ODBC Driver，是以可以直接對接BI工具實作可視化展現，在實際業務中，主要用到資料服務（阿裡内部叫oneservice）和FBI（阿裡集團内的一款可視化報表工具）。

1）資料服務

在資料中台的資料建設和資料服務的鍊路上，我們常常需要按照生産者和消費者的模式一樣，通過oneservice将我們開發好的持久化的資料提供的統一的數服務。最初原生的方案設計是按照如下鍊路[1]進行的

天貓國際通過Hologres進行排行榜的實時互動式分析

但是在實際過程中，由于oneservice對于互動分析這樣的場景支援的很弱，尤其是涉及到查詢邏輯涉及到join的實時，就會出現timeout以及一些其他的問題。其次oneservice将查詢query下推到Hologres查詢的時候，oneservice很多的pg生态的文法都不支援。最終我們通過上圖的鍊路[2]也就是通過FBI内置的Postgresql引擎來直接連Hologres實作資料查詢服務的。

2）FBI可視化

資料集動态配置

FBI層面通過建立資料集，将商品，賣家，品牌的三個頁面統一分析，力争通過一個資料集來動态實作查詢服務，這裡需要涉及到資料集中應用到的占位符和基于vilocity文法來實作動态的配置。

python
    from    dwd_intl_trd_pay_itm_buyer_ri
    where
        stat_date = '${bizdate:20200722}' 
        #if ((! ${bu_level_two} ) and (${bu_level_one} == "111"))
              and bu_id = '${bu_level_one}' 
        #elseif ((! ${bu_level_two}  ) and (${bu_level_one} != "111"))
              and bu_id_level2 = '${bu_level_two}' 
        #elseif (( ${bu_level_two}  ) and (${bu_level_one} == "111"))
              and bu_id = '${bu_level_one}' 
        #elseif (( ${bu_level_two}  ) and (${bu_level_one} != "111"))
              and bu_id_level1 = '${bu_level_one}' 
        #else
              and bu_id_level1 = '${bu_level_one}'
        #end
   group  by  
            '${dims}'

頁面布局設計

FBI的頁面布局設計這個比較簡單，直接通過報表的模式展示即可，但是需要同營銷活動分析的級聯模式保持一緻，尤其是涉及到行級關聯的一些地方還是需要一些技巧和設定的，否則初始化的性能不太好。最終的頁面和查詢如下：

天貓國際通過Hologres進行排行榜的實時互動式分析

七.業務價值

總結來說，使用Hologres之後，除了上面講訴的做到了計算、存儲、服務統一的優勢之外，從業務上來說還有以下幾個突出價值：

1.提升資料分析效率

将公共層的明細資料存入Hologres，提供給業務方能夠進行随意互動式分析，相比于以前的cube模型的需要關聯多個模型的取數方式，極大的提升了資料分析的效率。

2.節省資料回刷資源

整個公共層明細資料在行業的次元隻包含葉子類目資料，在每年的行業類目調整中，不會因為類目維表的變更，導緻進行回刷資料，極大的節省了回刷的資料資源。

八.未來規劃

1.節省存儲空間

由于需要看曆史資料，是以需要儲存最近400天的資料，明細資料太過于龐大，是以需要将曆史資料進行彙總，有成交的商品是27W X 400 = 1億+(大促會比較大) 降低存儲壓力，同時也會同Hologres技術團隊一起引入高壓縮比的存儲格式。

2.繼續優化目前技術

目前通過Hologres的互動式分析技術和FBI的可視化工具能夠做到查詢均在3s左右，但還是有很多的一些可以化的細節的地方，比如查詢的優化，FBI可視化工具搭建以及使用的一些小問題上，需要結合Hologres技術團隊，FBI的技術團隊一起來共建，反哺技術鍊的建設更加完善。

3.資料回刷方案

目前的技術方案不存在因為行業的頻繁調整而帶來的大量曆史資料回刷的問題，但是如果存在一些邏輯的調整或者一些不可控的因素導緻需要回刷曆史資料，是以需要設計實時的資料回刷方案。

4.複用到更多互動式分析場景

由于目前的Hologres中存儲的大量的交易和流量的IPV明細資料，是以在很多的資料看闆，資料分析中都存在可以直接複用目前資料，直接在資料集上進行自由的互動式分析，提升資料研發，資料分析，産品搭建等研發效率。

存儲 分布式計算 資料可視化 關系型資料庫 資料挖掘 bi MaxCompute 流計算 postgresql 索引 MaxCompute存儲計算 MaxCompute資料存儲計算 MaxCompute存儲oss MaxCompute存儲壓縮 MaxCompute存儲文檔
上一篇: 2020雙十一雲大使推廣攻略最全指南，月入十萬不是夢！
下一篇: 螞蟻集團IPO折讓定價68.8元！社保基金追投，為全國人民賺養老金

繼續閱讀