大數據

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

2020-10-29

作者：景聞 阿里巴巴數據技術及產品部數據技術專家

一.業務背景

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

1.png

二.原技術方案

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

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

1）ADS層模型任務多

流計算和批處理任務都分別需要開發基於商品，賣家，品牌粒度的滿足應用層的三個ADS模型數據，三個數據同步任務，分別需要創建三個oneservice服務，滿足三個數據模塊應用。

2）計算過程數據膨脹

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

3）流批分離

當前產品上根據時間進行選擇讀取實時數據還是離線數據，三天之內的數據通過實時任務計算的數據，三天前的歷史數據是通過批處理任務計算的離線數據，存在兩套任務同時運行，增加了運維的複雜性。

4）產品搭建邏輯複雜

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

三.架構升級思考策略

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

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

3.png

四.產品技術選型

在產品技術選型之前，需要先梳理業務需要用到的表以及數據量，選取幾張最具代表性的表用於驗證實際業務場景中產品技術可行性，以及驗證關鍵指標性能問，包括查詢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後的架構如下圖所示。

4.png

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

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功能
  • dataphin設置：set tovsbi.dwd_intl_log_ipv_itm_visitor_ri.insertOrUpdate='true'
  • sdp原生的設置：創建sink表的時候，在with後面添加 insertOrUpdate

3.數據可視化展現

Hologres兼容PostgreSQL生態，提供JDBC/ODBC Driver，因此可以直接對接BI工具實現可視化展現，在實際業務中，主要用到數據服務（阿里內部叫oneservice）和FBI（阿里集團內的一款可視化報表工具）。

1）數據服務

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

5.png

但是在實際過程中，由於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的頁面佈局設計這個比較簡單，直接通過報表的模式展示即可，但是需要同營銷活動分析的級聯模式保持一致，尤其是涉及到行級關聯的一些地方還是需要一些技巧和設置的，否則初始化的性能不太好。最終的頁面和查詢如下：
6.png

七.業務價值

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

1.提升數據分析效率

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

2.節省數據回刷資源

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

八.未來規劃

1.節省存儲空間

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

2.繼續優化當前技術

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

3.數據回刷方案

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

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

由於當前的Hologres中存儲的大量的交易和流量的IPV明細數據，因此在很多的數據看板，數據分析中都存在可以直接複用當前數據，直接在數據集上進行自由的交互式分析，提升數據研發，數據分析，產品搭建等研發效率。

Leave a Reply

Your email address will not be published. Required fields are marked *