導讀：本次分享的是在國內某頭部體育用品企業的主品牌數據倉庫建設中積累的寶貴經驗，項目背景是真實的，作者作為技術經理全程參與數據倉庫設計、開發、測試和優化工作，由于身份是乙方，所以這里就不暴露企業名稱了。項目基于Hive on Spark搭建數據倉庫，完成數據的抽取、轉換，最后通過DataX將數據同步到ClickHouse用于BI報表、自助分析和移動Web查詢。

該企業在啟動主品牌數據倉庫前，分布在其它品牌數據倉庫上，在上面嘗試了多個技術方案，包括基于Greenplum+Oacle模式、Hive+Kylin模式、Hive+Doirs模式的方案，但是以上三個項目最后查詢的數據量都是千萬級別到億級，而主品牌則是十億級的。

根據已有項目經驗發現：

① 基于MPP架構的Greenplum雖然查詢高效，但不方便進行存算分離，并發查詢也不高；

② 基于Kylin模式的查詢雖然性能快，但模型構建不太靈活，針對零售BI場景，預先構建Cube的難度非常大；

③ 基于 Doirs 模式的查詢引擎雖然性能足夠快，但是集群節點要求比較多，項目選型的時候還不支持 DataX 插件 ( 2021年9月底才發布 DorisWriter 插件 )。

所以，我們最后決定選用業界最主流的 Hive + ClickHouse 架構。

01

系統架構

本次項目基于集團自研大數據平臺進行開發，由系統架構部提供大數據平臺支撐，我公司完成數據倉庫模型構建和集市層數據加工，基于Hive SQL on Spark完成數據加工，基于自研工具完成數據抽取，基于DataX完成數據從Hive到ClickHouse的同步。前端展現分為移動端和PC端，移動端采用自研平臺，進行模塊化開發，PC端使用集團采購的商業化軟件BI來展現。整體系統架構如下圖：本次項目對接的下游系統包括新零售、歐寶、EBI、P60、P61、VOS和MDM等系統，主要抽取銷售模型、庫存明細（包括在途）、店倉和商品主數據等。數據抽取有增量和全量兩種方式，采用大數據平臺提供的模板化功能完成數據的同步。零售倉庫采用標準分層，按照ODS、DWD、DWS、ADS劃分，數據的加工全部由Hive腳本來完成，通過git進行代碼版本管理，Hive SQL的執行由大數據平臺自動提交到Spark on Yarn上。本次項目的集市層采用ClickHouse（以下簡稱CK）作為查詢引擎，數據在Hive中完成加工后通過DataX同步到ClickHouse。項目按照模塊分為移動端和PC端，分別創建zy_mbi和zy_pcbi兩個數據庫，用于集市數據同步和查詢。ClickHouse集群由 4 個節點（320G內存84核CPU）組成物理集群，并劃分成 2 個邏輯集群，其中，Nginx或CHProxy用作數據讀寫時的負載均衡；ClickHouse 在數據副本的同步會依賴 Zookeeper 來存儲元信息和協調。CK集群的結構如下圖：兩個邏輯集群的設定如下：單分片集群 單分片集群由4個節點組成對等服務，互為備份。當某張表存儲在單分片集群，每個節點都存儲該表的全量數據，各節點之間的數據會相互同步，往任何一個節點寫入數據，都會同步到其它節點中。維表和事實表（10 億以內），建議存儲在單分片集群。多分片集群 多分片集群，將4個節點切分成2個主節點2個備份節點，每個節點存儲單表1/2的數據。當某張表存儲在多分片集群時，相當于將該表做 sharding，每個分片存儲該表的一部分數據，每個分片內的節點數據會自動同步。事實表（10 億以上）的數據可以存儲在多分片集群中。邏輯集群的劃分根據配置文件 metrika.xml 進行配置： <remote_servers> <cluster_single_shard> <shard> <internal_replication>true</internal_replication> <replica> <host>n1</host> <port>9000</port> </replica> <replica> <host>n2</host> <port>9000</port> </replica> <replica> <host>n3</host> <port>9000</port> </replica> <replica> <host>n4</host> <port>9000</port> </replica> </shard> </cluster_single_shard> <cluster_multiple_shard> <shard> <internal_replication>true</internal_replication> <replica> <host>n1</host> <port>9000</port> </replica> <replica> <host>n2</host> <port>9000</port> </replica> </shard> <shard> <internal_replication>true</internal_replication> <replica> <host>n3</host> <port>9000</port> </replica> <replica> <host>n4</host> <port>9000</port> </replica> </shard> </cluster_multiple_shard></remote_servers>

在實際的項目應用和性能測試中，我們發現多分片集群在10億以下數據量并且有關聯查詢的場景下比單分片集群慢很多（10倍以上的性能差異），因此本次項目的所有的表都部署在單分片集群上，并且由于單分片集群有4個節點，通過Nginx進行負載均衡，并發查詢可以達到四倍單節點的效果，在一定程度上緩解了CK并發能力不強的問題。

02建表規范

在實際項目中，我們在建表方面遵循以下要求：

①每張需要同步的表，我們都創建三張對應表，一張local表、一張local_tmp表、一張跨節點的視圖；

②表名和字段名均采用小寫；

③視圖命名以_v結尾；

④字段類型盡快簡單統一，項目主要采用Date、Timestamp、String、Int、Decimal(38,4) 五種數據類型，分別對應Hive的date、timestamp、string、int和decimal(38,4)類型；

⑤ local表和local_temp表采用ReplicatedMergeTree引擎，跨節點的視圖采用Distributed引擎。

⑥所有的表分為單分區表和多分區表兩種，多分區表采用按月分區或者按日分區，即toYYYYMM(order_dt)和toYYYYMMDD(inv_dt)兩種，根據數據量大小來確定；

⑦ORDER BY的字段必須包含該表的業務日期，第二個字段優先選擇CMS_CODE；

⑧index_granularity統一設置為8192。

基于以上共識，我們就有了標準建表模板，所有的新增表和修改表操作都參照該目標進行，可以極大的提高生產效率。

drop table zy_mbi.dm_zy_mbi_offline_sale_dtl_local ON CLUSTER cluster_single_shard sync;drop table zy_mbi.dm_zy_mbi_offline_sale_dtl_local_tmp ON cluster cluster_single_shard sync;drop table zy_mbi.dm_zy_mbi_offline_sale_dtl ON CLUSTER cluster_single_shard sync;CREATE TABLE zy_mbi.dm_zy_mbi_offline_sale_dtl_local ON CLUSTER cluster_single_shard( `order_num` String COMMENT '訂單號', `order_dt` Date COMMENT '訂單日期', `order_tm` Timestamp COMMENT '訂單時間', `order_mon` String COMMENT '訂單月份', `cms_code` String COMMENT 'CMS編碼', `store_code` String COMMENT '店鋪編碼', `art_no` String COMMENT '貨號', `member_card_id` String COMMENT '會員卡ID', `oper_staff_id` String COMMENT '營業員id', `oper_staff_name` String COMMENT '營業員姓名', `is_valid_store` String COMMENT '是否有效店', `is_same_store` String COMMENT '是否同店', `good_qty` Int COMMENT '商品數量', `retail_amt` Decimal(38,4) COMMENT '零售金額', `tag_amt` Decimal(38,4) COMMENT '吊牌金額', `sys_src` String COMMENT '系統來源') ENGINE = ReplicatedMergeTree('/clickhouse/{group}/tables/one/zy_mbi/dm_zy_mbi_offline_sale_dtl_local', '{replica}')PARTITION BY toYYYYMM(order_dt)ORDER BY (order_dt, cms_code)SETTINGS index_granularity=8192;CREATE TABLE zy_mbi.dm_zy_mbi_offline_sale_dtl_local_tmp ON CLUSTER cluster_single_shardAS zy_mbi.dm_zy_mbi_offline_sale_dtl_localENGINE = ReplicatedMergeTree('/clickhouse/{group}/tables/one/zy_mbi/dm_zy_mbi_offline_sale_dtl_local_tmp', '{replica}')PARTITION BY toYYYYMM(order_dt)ORDER BY (order_dt, cms_code)SETTINGS index_granularity=8192;CREATE TABLE zy_mbi.dm_zy_mbi_offline_sale_dtl ON CLUSTER cluster_single_shardAS zy_mbi.dm_zy_mbi_offline_sale_dtl_localENGINE = Distributed(cluster_single_shard, zy_mbi, dm_zy_mbi_offline_sale_dtl_local, cityHash64(order_num));03數據同步前面說到，我們的建表模板需要創建三張表，可能有人會好奇，為什么需要創建local_tmp表。看完這一部分，讀者就明白了。由于數據同步可能會失敗，并且有可能用戶正在使用相關報表，這個時候刪除數據再插入數據是有一個時間間隔的，為了避免用戶那邊出現查詢不到數據或者查詢的數據不完整的情況，我們就需要用到local_tmp表了。即先將Hive數據同步到local_tmp表然后在庫內將數據遷移到local表，而頁面查詢則是基于local表之上的跨節點視圖。庫存數據遷移還分為兩種情況，一種是全量替換，我們可以直接通過rename表來實現。前置和后置SQL如下：

平臺自動生成的DataX同步配置如下：

{ "job": { "name": "hive2ck_dm_zy_pcbi_cms_sale_sync", "setting": { "speed": { "channel": 1, "byte": }, "errorLimit": { "record": , "percentage": } }, "content": [ { "reader": { "name": "hdfsreader", "parameter": { "path": "/hive/warehouse/zy_bi.db/dm_zy_pcbi_cms_sale_sync/*", "column": [ { "index": , "type": "string", "name": "order_num" }, ...【省略若干字段】 { "index": 14, "type": "string", "name": "sys_src" } ], "fileType": "orc", "encoding": "UTF-8", "fieldDelimiter": "," } }, "writer": { "name": "clickhouse2writer", "parameter": { "dsDatasource": "clickhouse_zy_pcbi", "writeMode": "insert", "preSql": [ "truncate table zy_pcbi.dm_zy_pcbi_cms_sale_local_tmp ON CLUSTER cluster_single_shard" ], "postSql": [ "RENAME TABLE zy_pcbi.dm_zy_pcbi_cms_sale_local TO zy_pcbi.dm_zy_pcbi_cms_sale_local_tmp_tmp ON CLUSTER cluster_single_shard", "RENAME TABLE zy_pcbi.dm_zy_pcbi_cms_sale_local_tmp TO zy_pcbi.dm_zy_pcbi_cms_sale_local ON CLUSTER cluster_single_shard", "RENAME TABLE zy_pcbi.dm_zy_pcbi_cms_sale_local_tmp_tmp TO zy_pcbi.dm_zy_pcbi_cms_sale_local_tmp ON CLUSTER cluster_single_shard" ], "column": [ "order_num", "order_dt", "order_tm", "cms_code", "store_code", "art_no", "oper_staff_id", "oper_staff_name", "member_card_id", "is_valid_store", "is_same_store", "good_qty", "retail_amt", "tag_amt", "sys_src" ], "connection": [ { "table": [ "zy_pcbi.dm_zy_pcbi_cms_sale_local_tmp" ] } ] } } } ] }}第二種是部分數據替換，早期我們嘗試過采用先delete后插入的方式，后面統一調整成為分區替換的方式。我們利用ClickHouse支持分區的特性，將local_tmp表和local表創建成為多分區的表，按月或者按日創建分區，然后通過ClickHouse的分區替換功能交換兩張表的分區數據，這樣就實現了數據的快速更新。分區替換模式需要在前置SQL里面清空目標表。

然后通過其它程序實現分區替換。

實現分區替換的邏輯其實非常簡單，主要包括三個步驟：

第一步，查詢表的分區信息。

SELECT partition,name,part_type,active,rows,bytes_on_disk,data_compressed_bytesfrom system.parts where table='dm_zy_pcbi_offline_stock_sale_local_tmp';

第二步，檢查不同節點分區的數據是否一致。由于數據同步到ClickHouse以后，ClickHouse還需要進行節點間的數據復制，如果數據未復制完成就進行分區替換，結果會出現異常。這里就需要連上每一個實例節點，查詢對應表的分區記錄數。

select `partition`,sum(`rows`) from `system`.partswhere table ='dm_zy_pcbi_offline_stock_sale_local_tmp'--and `partition` ='202202'group by `partition`

第三步，從小到大執行分區替換。

ALTER TABLE dm_zy_pcbi_offline_stock_sale_local REPLACE PARTITION 202011 FROM dm_zy_pcbi_offline_stock_sale_local_tmp

總結一下，通過local_tmp表替換數據，有以下好處：

①清空local_tmp表對用戶使用數據無影響，數據同步過程無感知；

②重命名表或者分區替換操作屬于庫內操作，時間非常短，用戶無感知；

③數據同步失敗的情況下，不影響報表使用上一個版本的數據；

④通過分區替換的方式，支持增量同步數據。

04實時數據

利用ClickHouse來完成實時數據接入，有兩種方案分別對應ClickHouse提供的兩種功能。

方案一：直接通過Flink寫入ClickHouse。

從技術實現上也有兩種方法，第一種是改寫jdbc connector源碼，增加ck方言，可以參考阿里云的文檔：

https://help.aliyun.com/document_detail/175749.html。

第二種是直接引入flink-clickhouse-sink包，對應的github項目地址為：https://github.com/ivi-ru/flink-clickhouse-sink。

本次我們項目采用是FlinkSQL寫入ClickHouse，采用的是第一種方法。通過FlinkSQL讀取kafka數據，完成雙流jion后直接寫入ClickHouse。這里省略讀取kafka的過程和雙流join的代碼，只保留寫入ClickHouse的模板，簡化代碼如下：

DROP TABLE IF EXISTS zy_rt.ck_dwd_mbi_mxls_receipt_detail_rt;CREATE TABLE zy_rt.ck_dwd_mbi_mxls_receipt_detail_rt (receipt_id STRING, seq_num STRING, store_code STRING,....actual_retail_amt DOUBLE, settle_amt DOUBLE, update_tm STRING,PRIMARY KEY (receipt_id,seq_num) NOT ENFORCED) with ( 'connector' = 'clickhouse', 'jdbc-source' = 'clickhouse_zy_mbi', /* ClickHouse數據庫連接，復用平臺配置 */ 'table-name' = 'zy_mbi.dwd_ord_mxls_receipt_detail_rt_local', /* ClickHouse 數據表名 */ 'sink.batch-size' = '10000', /* batch 大小 */ 'sink.flush-interval' = '5000', /* flush 時間間隔 */ 'sink.max-retries' = '1', /* 最大重試次數 */ 'sink.delete-mode' = 'ignore' /* 刪除方式：alter | flag | ignore */);--數據寫入MBI的目標表表insert into zy_rt.ck_dwd_mbi_mxls_receipt_detail_rtselect * from zy_rt.kfk_dwd_ord_mxls_receipt_detail_rt_v;

由于Interval Join要數據庫支持update和detele，ClickHouse雖然支持但是語法不正常，所以這個功能沒能用起來，后面需要進一步優化。

方案二：直接通過ClickHouse讀取kafka數據到內部表。

第一步，創建Kafka引擎表。

CREATE TABLE zy_rt.kfk_ods_mxls_p_bl_sell_hd (Billid String,Selltime DateTime,Storeid String,Realamount Float64)ENGINE = KafkaSETTINGS kafka_broker_list = '192.168.9.226:9092',kafka_topic_list = 'kafka-reader',kafka_row_delimiter = '\n',kafka_group_name = 'kafka-reader-group',kafka_format = 'JSONEachRow'

第二步，創建Kafka引擎表。

CREATE TABLE zy_rt.ods_mxls_p_bl_sell_hd_local(Billid String,Selldate Date,Selltime DateTime,Storeid String,Realamount Decimal(38,4)) ENGINE = ReplicatedMergeTree('/clickhouse/{group}/tables/one/zy_rt/ods_mxls_p_bl_sell_hd_local', '{replica}')ORDER BY (Billid, Selldate)SETTINGS index_granularity=8192;

第三步，創建物化視圖，持續不斷地從 Kafka 收集數據并通過 SELECT 將數據轉換為所需要的格式寫入實體存儲表。

CREATE MATERIALIZED VIEW zy_rt.ods_mxls_p_bl_sell_hd_mv TO ods_mxls_p_bl_sell_hd_localAS SELECT billid, toDate(toDateTime(selltime)) as selldate, selltime, storeid, toFloat64OrZreo(realamount) as realamountFROM zy_rt.kfk_ods_mxls_p_bl_sell_hd;

數據接入以后，還需要對數據進行去重查詢。為什么數據接入的時候不去重呢，因為ClickHouse唯一支持數據去重的引擎ReplacingMergeTree是以分區為單位刪除重復數據的。只有在相同的數據分區內重復的數據才可以被刪除，而不同數據分區之間的重復數據依然不能被剔除。

這個時候就用到了ClickHouse一個比較好用的語法 order by limit 1 by ，等于在一個語句里面實現了row _number去重（row _numbe去重需要嵌套兩層查詢）。

根據實際的業務場景，我們還需要在ClickHouse里面進行一些維度表關聯，例如關聯從hive同步過來的店鋪和商品映射表，這些邏輯由于比較固定，并且維表數據量也比較大（在FlinkSQL中關聯出現過內存溢出），所以我們選擇創建ClickHouse視圖來封裝這些處理，舉例如下：

drop view zy_rt.dm_zy_sale_detail_curday_rt_v ON cluster cluster_single_shard sync;create view zy_rt.dm_zy_sale_detail_curday_rt_v ON cluster cluster_single_shardas select t.receipt_id as order_num,t.sale_dt as order_dt,concat(cast(t.sale_dt as String),' ',t.sale_tm) as order_tm,b.cms_code as cms_code,a.art_no as art_no,t.store_code as store_code,b.cms_code as shop_code,t.oper_staff_id as emp_code,sale_qty as good_qty,toFloat64(settle_amt) as retail_amt,'offline' as online_offline_flag,'AXLS' as sys_srcfrom (select * from zy_rt.dwd_ord_axls_receipt_detail_rt where sale_dt = Date(now()) order by update_tm limit 1 by receipt_id,seq_num,sku_id ) t left join (select * from zy_mrt.dwd_good_xls_sku where sys_part ='AXLS' ) a on t.sku_id = a.sku_id left join (select * from zy_rt.dwd_chan_xls_depot_store where sys_part ='AXLS' and vos_beg_dt <=cast(Date(now()) as String) and vos_end_dt >= cast(Date(now()) as String) ) b on t.store_code = b.store_code

目前系統按照方案一構建實時數據，且僅需當日的實時數據，頁面查詢結果的延時在1分鐘以內。但是由于不能支持Interval Join，數據沒辦法保證100%準確性。如果能實現Interval Join，然后保證數據在24小時內可以多次修改，那么數據的準確性會進一步提高。

另外，根據實際應用情況，在接入實時數據時，Doris的優勢比較明顯。首先Doris對FlinkSQL的支持比較好，可以刪除和修改數據；其次Doris的UNIQUE KEY可以自動完成數據去重；第三，Doirs對大表join支持更好，查詢速度比ClickHouse更快。

05數據查詢

至此，所有離線數據和實時數據都已經進入了ClickHouse，接下來就是BI報表和移動web查詢了。

在PCBI方面，我們是通過數據集的方式構建查詢結果集的。這里特別需要說到的一個場景是基于任意日期的本同期查詢。

針對這個場景，我們做了以下優化：

①本同期數據只保留一份，即按照日期插入目標表的明細數據，通過union all查詢+字段錯位來獲取本同期數據；

②在order_dt字段上增加分區和排序索引，并且order_dt作為order by字段的第一位；

③明細數據集字段盡可能精簡，減少數據存儲；

④維度表數據通過join來獲取，頁面會根據查詢要求自動裁剪字段。

SELECT formatDateTime(toDate(s.biz_dt),'%Y-%m') as `年月`, s.biz_dt as `業務日期`, s.bq_sale_qty as `本期銷售數量`, s.bq_sale_amt as `本期銷售金額`, s.bq_sale_tag_amt as `本期銷售吊牌金額`, s.tq_sale_qty as `同期銷售數量`, s.tq_sale_amt as `同期銷售金額`, s.tq_sale_tag_amt as `同期銷售吊牌金額`, c.cms_code as `CMS編碼`, c.cms_name as `CMS名稱`, 【此處省略80個店鋪維度】 a.art_no as `貨號`, 【此處省略80個商品維度】from --本期銷售(select s.order_dt as biz_dt, s.cms_code as shop_code, s.art_no as art_no, sale_qty as bq_sale_qty, sale_amt as bq_sale_amt, sale_tag_amt as bq_sale_tag_amt, as tq_sale_qty, as tq_sale_amt, as tq_sale_tag_amt from zy_pcbi.dm_zy_pcbi_chan_sales_daily s where s.order_dt >= [DYNAMIC_PARAMS.報表開始日期] and s.order_dt <= [DYNAMIC_PARAMS.報表結束日期] union all --同期銷售 select s.order_dt as biz_dt, s.cms_code as shop_code, s.art_no as art_no, as bq_sale_qty, as bq_sale_amt, as bq_sale_tag_amt, sale_qty as tq_sale_qty, sale_amt as tq_sale_amt, sale_tag_amt as tq_sale_tag_amt from zy_pcbi.dm_zy_pcbi_chan_sales_daily s where s.order_dt >= addMonths([DYNAMIC_PARAMS.報表開始日期],-12) and s.order_dt <= addMonths([DYNAMIC_PARAMS.報表結束日期],-12))s left join zy_pcbi.`dm_zy_pcbi_cms_store_info` c on s.shop_code=c.shop_codeleft join zy_pcbi.`dm_zy_pcbi_art_no_info` a on s.art_no=a.art_no

在移動BI方面主要是基于mybatis語法完成SQL語句的封裝和參數的替換。這里的特殊場景就是基于任意日期的日、周、月、年、累計查詢。

對此場景做的查詢優化有：

①本同期數據只保留一份，即按照日期插入目標表的明細數據，通過union all查詢+字段錯位來獲取本同期數據；

②通過mybatis的條件判斷確定數據的過濾條件，增加代碼復用率，并且保持都用biz_dt過濾；

③多個數據來源的數據拆分成不同的表，都保留相同的數據粒度，分別進行數據同步；

④所有底表的biz_dt字段上增加分區和排序索引，并且biz_dt作為order by字段的第一位；

⑤過濾條件也通過mybatis的判斷語句添加，實現多種篩選和下鉆復用同一個數據查詢服務。

select 【省略查詢維度和指標】 from zy_mbi.dm_zy_mbi_stock_sale_daily_sum_v t where 1=1 <if test="dayTime != null and '' != dayTime"> and t.biz_dt = toDate(${timeCondition}) </if> <if test="weekTime != null and '' != weekTime"> and t.biz_dt <![CDATA[ >= ]]> (select curr_week_begin_dt from zy_mbi.dm_zy_mbi_calendar_info where calendar_date = toDate(${timeCondition})) and t.biz_dt <![CDATA[ <= ]]> (select curr_week_end_dt from zy_mbi.dm_zy_mbi_calendar_info where calendar_date = toDate(${timeCondition})) </if> <if test="monthTime != null and '' != monthTime"> and t.biz_dt <![CDATA[ >= ]]> (select curr_month_begin_dt from zy_mbi.dm_zy_mbi_calendar_info where calendar_date = toDate(${timeCondition})) and t.biz_dt <![CDATA[ <= ]]> (select curr_month_end_dt from zy_mbi.dm_zy_mbi_calendar_info where calendar_date = toDate(${timeCondition})) </if> <if test="yearTime != null and '' != yearTime"> and t.biz_dt <![CDATA[ >= ]]> (select toStartOfYear(calendar_date) from zy_mbi.dm_zy_mbi_calendar_info where calendar_date = toDate(${timeCondition})) and t.biz_dt <![CDATA[ <= ]]> (select calendar_date from zy_mbi.dm_zy_mbi_calendar_info where calendar_date = toDate(${timeCondition})) </if> <if test="total != null and '' != total"> and 1=2 </if>

此外，還有實時數據和離線數據的聯合查詢，這個在PCBI和移動BI都有應用。

對此場景做的查詢優化有：

①通過視圖把離線數據和實時數據union all到一起；

②查詢實時的時候通過參數條件直接跳過離線數據，查詢離線數據的時候通過參數條件跳過實時數據；

③離線數據的開始日期、結束日期通過子查詢來獲取；

④在離線數據的order_dt字段上增加分區和排序索引，并且order_dt作為order by字段的第一位；

⑤在查詢的外圍嵌套維度表join，減少join的主表數據量。

--離線數據查詢select 【省略查詢維度和指標】from zy_pcbi.dm_zy_pcbi_cms_salewhere [DYNAMIC_PARAMS.報表_零售報表_周期選項] <> '實時'and order_dt >=(select case when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='日' then calendar_Date when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='周' then curr_week_beg_dt when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='月' then curr_mon_beg_dt when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='年' then Date(toStartOfYear(toDate([DYNAMIC_PARAMS.報表開始日期]))) end as bq_beg_dt from zy_pcbi.dm_zy_pcbi_calendar_info where calendar_Date =[DYNAMIC_PARAMS.報表開始日期])and order_dt <=(select case when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='日' then calendar_Date when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='周' then curr_week_end_dt when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='月' then curr_mon_end_dt when [DYNAMIC_PARAMS.報表_零售報表_周期選項] ='年' then Date([DYNAMIC_PARAMS.報表開始日期]) end as bq_end_dt from zy_pcbi.dm_zy_pcbi_calendar_info where calendar_Date =[DYNAMIC_PARAMS.報表開始日期])union all --實時數據查詢select 【省略查詢維度和指標】from zy_rt.dm_zy_sale_detail_curday_rt_vwhere [DYNAMIC_PARAMS.報表_零售報表_周期選項] = '實時'

在以上CK查詢優化的基礎上，目前80%的復雜查詢可以在1s內返回，95%的查詢在3s內完成，剩余少量需要count distinct的場景（無法提前預聚合）會稍慢一點。

經過半年的努力，項目完成各項開發目標，跑批和查詢性能都達到了項目預期目標，滿足上線條件并于2022年3月底正式上線。這篇“熱氣騰騰”的項目總結，既是對項目的一次復盤，也給想要使用Hive+ClickHouse構建數據倉庫的朋友一些經驗分享。

最后特別感謝Kenny.Wang和塵埃在項目過程中給予的指導和幫助，正是雙方的密切配合和集思廣益，才產出了這么多最佳實踐經驗，才有了項目的成功交付。

今天的分享就到這里，謝謝大家。