4-3 StarRocks 構建實時數倉的新探索.pdf

1、StarRocks構建實時數倉的新探索2022-03-24 王天宜|StarRocks 實時數倉戶案例錄|01020304實時數倉經典架構與特點StarRocks 實時數倉產品定位StarRocks 實時數倉建模式實時數倉經典架構與特點|實時數倉 1.0-Storm 架構SpoutBoltBoltBoltSpouttuplestuplestuplestuples|實時數倉 2.0-Lambda&Kappa 架構Kappa 架構批處理實時處理計算引擎數據服務服務層數據存儲結果數據歷史數據Lambda 架構應層實時 分析實時 屏實時 推薦實時 查詢應層實時 分析實時 屏實時 推薦實時 查詢數據存儲

2、結果數據歷史數據實時處理計算引擎數據服務服務層|實時數倉 3.0-Flink 架構數據采集戶為業務數據系統志爬數據數據源實時應實時分析實時屏實時查詢實時推薦消息隊列數據存儲KafkaPulsarCanalFlumeLogstashDebezium|StarRocks實時數倉定位與產品能實時數倉新挑戰數據采集戶為業務數據系統志爬數據數據源實時應實時分析實時屏實時查詢實時推薦消息隊列數據存儲KafkaPulsarCanalFlumeLogstashDebezium數據傳輸鏈路 鏈路冗，數據經過采集層，消息隊列，Flink 分析層存儲 技術棧復雜，多種數據存儲引擎配合功能性缺失 數據更新能弱，查詢并

3、發量低，去重困難|StarRocks 在實時數倉態定位實時應實時分析實時屏實時查詢實時推薦戶為業務數據系統志爬數據數據源數據采集MySQL維度數據FileStarRocks數據湖LogstashDebeziumCanalODSDWSDWDODSODSODSDWSDWSDWDDWDODSODS|StarRocks 實時數倉能實時數據攝 直接訂閱 Kafka 數據 提供 Flink-Connector，Exactly Once Flink CDC 直接捕獲 TP 數據變更實時數據更新 PK 模型提供 Delete and Insert 更新式 性能相 Merge on Read 提升 3-15 倍

4、實時數據建模 聚合模型提供實時聚合能 向量化引擎，實時 Join 能撐多種模型 提供物化視圖實現搶致性實時數據分析 產后的數據可以直接使 實時、離線數據統融合數據源實時應報表業務指標監控客群圈選智能分析MySQLFile維度數據ADSODSDWDDWSADSODSDWDDWSADSDWSStarRocksODSDWD數據湖數據采集CloudCanalFlumeLogstashBI 業務戶為業務數據志系統爬數據|StarRocks 實時數倉能實時數據攝 直接訂閱 Kafka 數據 提供 Flink-Connector，Exactly Once Flink CDC 直接捕獲 TP 數據變更實時數據

5、更新 PK 模型提供 Delete and Insert 更新式 性能相 Merge on Read 提升 3-15 倍實時數據建模 聚合模型提供實時聚合能 向量化引擎，實時 Join 能撐多種模型 提供物化視圖實現搶致性實時數據分析 產后的數據可以直接使 實時、離線數據統融合數據源實時應報表業務指標監控客群圈選智能分析MySQLFile維度數據ADSODSDWDDWSADSODSDWDDWSADSDWSStarRocksODSDWD數據湖數據采集CloudCanalFlumeLogstashBI 業務戶為業務數據志系統爬數據|StarRocks 實時數倉能實時數據攝 直接訂閱 Kafka 數

6、據 提供 Flink-Connector，Exactly Once Flink CDC 直接捕獲 TP 數據變更實時數據更新 PK 模型提供 Delete and Insert 更新式 性能相 Merge on Read 提升 3-15 倍實時數據建模 聚合模型提供實時聚合能 向量化引擎，實時 Join 能撐多種模型 提供物化視圖實現搶致性實時數據分析 產后的數據可以直接使 實時、離線數據統融合數據源實時應報表業務指標監控客群圈選智能分析MySQLFile維度數據ADSODSDWDDWSADSODSDWDDWSADSDWSStarRocksODSDWD數據湖數據采集CloudCanalFlum

7、eLogstashBI 業務戶為業務數據志系統爬數據|StarRocks 實時數倉能實時數據攝 直接訂閱 Kafka 數據 提供 Flink-Connector，Exactly Once Flink CDC 直接捕獲 TP 數據變更實時數據更新 PK 模型提供 Delete and Insert 更新式 性能相 Merge on Read 提升 3-15 倍實時數據建模 聚合模型提供實時聚合能 向量化引擎，實時 Join 能撐多種模型 提供物化視圖實現搶致性實時數據分析 產后的數據可以直接使 實時、離線數據統融合數據源實時應報表業務指標監控客群圈選智能分析MySQLFile維度數據ADSODS

8、DWDDWSADSODSDWDDWSADSDWSStarRocksODSDWD數據湖數據采集CloudCanalFlumeLogstashBI 業務戶為業務數據志系統爬數據|StarRocks 實時數倉能實時數據攝 直接訂閱 Kafka 數據 提供 Flink-Connector，Exactly Once Flink CDC 直接捕獲 TP 數據變更實時數據更新 PK 模型提供 Delete and Insert 更新式 性能相 Merge on Read 提升 3-15 倍實時數據建模 聚合模型提供實時聚合能 向量化引擎，實時 Join 能撐多種模型 提供物化視圖實現搶致性實時數據分析 產后

9、的數據可以直接使 實時、離線數據統融合數據源實時應報表業務指標監控客群圈選智能分析MySQLFile維度數據ADSODSDWDDWSADSODSDWDDWSADSDWSStarRocksODSDWD數據湖數據采集CloudCanalFlumeLogstashBI 業務戶為業務數據志系統爬數據|StarRocks 實時數據攝能ETLCDCQueryIngestionExternal TableMQ|StarRocks 實時數據攝能ETLCDCQueryIngestionExternal TableMQ|StarRocks 實時數據攝能ETLCDCQueryIngestionExternal Ta

10、bleMQ|StarRocks 實時數據攝能ETLCDCQueryIngestionExternal TableMQ|StarRocks 實時數據攝能ETLCDCQueryIngestionExternal TableMQ|StarRocks 實時數據建模能KEYVALUEidcitypv11001120022100322005KEYVALUEidcitypv110010210020插數據原表數據INSERT主鍵排序主鍵分組11001原表11001插12002原表21003插21003原表22005原表KEYVALUEidcitypv110011100101200221003210020220

11、0521003原表210020插11001原表110010插12002原表22005原表明細明細模型KEYVALUEidcitypv1100111200221002322005聚合模型11001+1021003+2022005聚合12002KEYVALUEidcitypv1100101200221002022005110010-112002210020-322005更新主鍵模型|StarRocks 實時數據建模能StarRocks數據建模AdHoc 分析固定報表OLAP 分析應ADSADSADSDWSDWSDWSODSODSODSDWDDWDDWDINSERT INTO SELECT視圖/物化

12、視圖建模視圖/物化視圖建模動構建透明使 持實時、批量構建 持靈活增加、刪除 持 Schema Change 使時只需指定原始表 CBO 動選擇最優物化視圖|StarRocks 實時分析加速能X86 CPU110010Row1210030Row2320040Row3420020Row4Traditional Memory Buffer123410010020020010304020IDCITYPVStarRocks Memory BufferID1234CITY100100200200PV10304020Row1Row2Row3Row4SELECT*FROM sr_vector WHERE id

13、=2+A0A1A2A3B0B1B2B3C0C1C2C3+MEMORYA0A1A2A3B0B1B2B3C0C1C2C3SIMD+MEMORY=通過改造數據結構和執邏輯，利 CPU 指令 AVX2，實現單條指令多條數據同時執的效果。向量化引擎向量化提升效果Filter：Group：Shuffle Join：Broadcast Join：5.1 倍14.9 倍3.7 倍3.5 倍更多優化段低基數優化：延遲物化：3.2 倍3.3 倍升|StarRocks 實時分析加速能|L1R1L1R1L1R1L1R1L1R1L1R1Colocation JointimeL1R1L1R1L1R1L1R1L1R1L1R

14、1Shuffle Jointimedatesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter2803231100Alex1203232200Dylan6603232200Justin6603233300Daniel1903241100Alex2803242100Tom6503243300Daniel2303243200May86datesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter28datesitecityuserpv03

15、231100Alex1203232200Dylan6603232200Justin6603233300Daniel19datesitecityuserpv03241100Alex2803242100Tom6503243300Daniel2303243200May86CREATE TABLE srtestdb.test_sr(date_id DATE,site_id INT DEFAULT 10,city_code SMALLINT,user_name VARCHAR(32)DEFAULT,pv BIGINT DEFAULT 0)DUPLICATE KEY(date_id,site_id,cit

16、y_code)PARTITION BY RANGE(date_id)(PARTITION p20200321 VALUES LESS THAN(2020-03-22),PARTITION p20200322 VALUES LESS THAN(2020-03-23),PARTITION p20200323 VALUES LESS THAN(2020-03-24),PARTITION p20200324 VALUES LESS THAN(2020-03-25)DISTRIBUTED BY HASH(site_id)BUCKETS 10 SELECT SUM(pv)FROM srtestdb.tes

17、t_sr WHERE date_id =2020-03-23 AND site_id =1 AND city_code=200;P20200323P20200324P2020032503231100Alex1203232200Dylan6603232200Justin6603233300Daniel19bucket_1bucket_2bucket_303241100Alex2803242100Tom6503243300Daniel2303243200May86bucket_1bucket_2bucket_303221100Jack3003221200May2803222100Tom760322

18、3200Peter28bucket_1bucket_2bucket_3Level 1 分區存儲Level 2 分桶存儲建表 并發查詢StarRocks 實時分析并發能|datesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter2803231100Alex1203232200Dylan6603232200Justin6603233300Daniel1903241100Alex2803242100Tom6503243300Daniel2303243200May86datesitecityuserpv03221100

19、Jack3003221200May2803222100Tom7603223200Peter28datesitecityuserpv03231100Alex1203232200Dylan6603232200Justin6603233300Daniel19datesitecityuserpv03241100Alex2803242100Tom6503243300Daniel2303243200May86CREATE TABLE srtestdb.test_sr(date_id DATE,site_id INT DEFAULT 10,city_code SMALLINT,user_name VARCH

20、AR(32)DEFAULT,pv BIGINT DEFAULT 0)DUPLICATE KEY(date_id,site_id,city_code)PARTITION BY RANGE(date_id)(PARTITION p20200321 VALUES LESS THAN(2020-03-22),PARTITION p20200322 VALUES LESS THAN(2020-03-23),PARTITION p20200323 VALUES LESS THAN(2020-03-24),PARTITION p20200324 VALUES LESS THAN(2020-03-25)DIS

21、TRIBUTED BY HASH(site_id)BUCKETS 10 SELECT SUM(pv)FROM srtestdb.test_sr WHERE date_id =2020-03-23 AND site_id =1 AND city_code=200;P20200323P20200324P2020032503231100Alex1203232200Dylan6603232200Justin6603233300Daniel19bucket_1bucket_2bucket_303241100Alex2803242100Tom6503243300Daniel2303243200May86b

22、ucket_1bucket_2bucket_303221100Jack3003221200May2803222100Tom7603223200Peter28bucket_1bucket_2bucket_3Level 1 分區存儲Level 2 分桶存儲建表 并發查詢StarRocks 實時分析并發能|datesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter2803231100Alex1203232200Dylan6603232200Justin6603233300Daniel1903241100Alex280

23、3242100Tom6503243300Daniel2303243200May86datesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter28datesitecityuserpv03231100Alex1203232200Dylan6603232200Justin6603233300Daniel19datesitecityuserpv03241100Alex2803242100Tom6503243300Daniel2303243200May86CREATE TABLE srtestdb.test_sr(dat

24、e_id DATE,site_id INT DEFAULT 10,city_code SMALLINT,user_name VARCHAR(32)DEFAULT,pv BIGINT DEFAULT 0)DUPLICATE KEY(date_id,site_id,city_code)PARTITION BY RANGE(date_id)(PARTITION p20200321 VALUES LESS THAN(2020-03-22),PARTITION p20200322 VALUES LESS THAN(2020-03-23),PARTITION p20200323 VALUES LESS T

25、HAN(2020-03-24),PARTITION p20200324 VALUES LESS THAN(2020-03-25)DISTRIBUTED BY HASH(site_id)BUCKETS 10 SELECT SUM(pv)FROM srtestdb.test_sr WHERE date_id =2020-03-23 AND site_id =1 AND city_code=200;P20200323P20200324P2020032503231100Alex1203232200Dylan6603232200Justin6603233300Daniel19bucket_1bucket

26、_2bucket_303241100Alex2803242100Tom6503243300Daniel2303243200May86bucket_1bucket_2bucket_303221100Jack3003221200May2803222100Tom7603223200Peter28bucket_1bucket_2bucket_3Level 1 分區存儲Level 2 分桶存儲建表 并發查詢StarRocks 實時分析并發能|datesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter2803231100A

27、lex1203232200Dylan6603232200Justin6603233300Daniel1903241100Alex2803242100Tom6503243300Daniel2303243200May86datesitecityuserpv03221100Jack3003221200May2803222100Tom7603223200Peter28datesitecityuserpv03231100Alex1203232200Dylan6603232200Justin6603233300Daniel19datesitecityuserpv03241100Alex2803242100

28、Tom6503243300Daniel2303243200May86CREATE TABLE srtestdb.test_sr(date_id DATE,site_id INT DEFAULT 10,city_code SMALLINT,user_name VARCHAR(32)DEFAULT,pv BIGINT DEFAULT 0)DUPLICATE KEY(date_id,site_id,city_code)PARTITION BY RANGE(date_id)(PARTITION p20200321 VALUES LESS THAN(2020-03-22),PARTITION p2020

29、0322 VALUES LESS THAN(2020-03-23),PARTITION p20200323 VALUES LESS THAN(2020-03-24),PARTITION p20200324 VALUES LESS THAN(2020-03-25)DISTRIBUTED BY HASH(site_id)BUCKETS 10 SELECT SUM(pv)FROM srtestdb.test_sr WHERE date_id =2020-03-23 AND site_id =1 AND city_code=200;P20200323P20200324P2020032503231100

30、Alex1203232200Dylan6603232200Justin6603233300Daniel19bucket_1bucket_2bucket_303241100Alex2803242100Tom6503243300Daniel2303243200May86bucket_1bucket_2bucket_303221100Jack3003221200May2803222100Tom7603223200Peter28bucket_1bucket_2bucket_3Level 1 分區存儲Level 2 分桶存儲建表 并發查詢StarRocks 實時分析并發能|StarRocks 實時更新能

31、|StarRocks 實時更新能|查詢數據量Primary Key（Delete and Insert）Unique Key（Merge on Read）性能提升導過程 SELECT COUNT(*)FROM orders;8000 萬0.24 sec1.15 sec6.29 x SELECT COUNT(*)FROM orders;1.6 億0.31 sec3.4 sec10.97 x SELECT COUNT(*),SUM(quantify)FROM orders WHERE revenue 2000;1000 萬0.23 sec3.49 sec15.17 x導后 SELECT COUNT

32、(*)FROM orders;2 億0.32 sec1.17 sec3.66 x SELECT COUNT(*),SUM(quantify)FROM orders WHERE revenue 2000;1200 萬0.34 sec 1.52 sec 4.47 xStarRocks實時數倉架構與數據建模|實時數倉新挑戰數據采集戶為業務數據系統志爬數據數據源實時應實時分析實時屏實時查詢實時推薦消息隊列數據存儲KafkaPulsarCanalFlumeLogstashDebezium數據傳輸鏈路 鏈路冗，數據經過采集層，消息隊列，Flink 分析層存儲 技術棧復雜，多種數據存儲引擎配合功能性缺失 數

33、據更新能弱，查詢并發量低，去重困難|Flink x StarRocks 實時數倉架構|采集層 Extract計算層 TransformDebeziumKafkaOLTP DBOLAP DB裝載層 Load計算層 Transform采集層 ExtractOLTP DB裝載層 LoadOLAP DBCDCDebeziumFlink CDC改造前改造后StarRocks 寬表實時數倉建模式數據源StarRocks數據湖數據存儲與分析實時應Flink CDC結構數據傳輸與計算ADSADSDWSDWSDWDDWDDIMDIMDIM結構化業務數據戶為系統志爬數據埋點數據結構化ADSADSADSDWSDWS

34、DWSDWDDWDDWD實時分析實時屏實時推薦實時查詢實時預警寬表實時數倉架構|StarRocks 單表查詢性能測評StarRocks vs Clickhouse vs Apache Druid SSB單表性能測試對報告https:/ 星型/雪花模型實時數倉架構星型/雪花模型實時數倉架構數據湖數據存儲與分析實時應實時分析實時屏實時推薦實時查詢實時預警數據源Flink CDC結構化數據傳輸與計算DIM結構化業務數據戶為系統志爬數據埋點數據ODSODSODSODSDIM結構化StarRocksODSODSODSDWDDWDDWDDWSDWSDWSADSADSADSDIMDIMDIMDIM|Star

35、Rocks 多表關聯性能測評StarRocks vs Trino TPCH 性能測試對報告https:/ 紅書案例告曝光點擊流告效果數據業務邏輯匯總邏輯業務邏輯匯總邏輯改造前告曝光點擊流ETL線上 MySQL 數據離線數倉數據改造后|紅書基于 StarRocks 構建告數據中實踐StarRocks 紅書案例告曝光點擊流線上 MySQL 數據離線數倉數據聚合模型主鍵模型明細模型MV-創意粒度MV-戶特征MV-告主粒度JOIN|紅書基于 StarRocks 構建告數據中實踐StarRocks 紅書案例ConsulFlink JobsPrimary StarRocksFlink JobsBackup StarRocksMyHub ClientADC Service|紅書基于 StarRocks 構建告數據中實踐StarRocks 紅書案例|紅書基于 StarRocks 構建告數據中實踐常感謝您的觀看|