ClickHouse 剖析 | 結(jié)合 SQL 解析的 ClickHouse 表生命周期管理

1. 問題背景在我們?nèi)粘５臉I(yè)務(wù)中，數(shù)據(jù)往往以庫表的形式呈現(xiàn)，數(shù)據(jù)生產(chǎn)和數(shù)據(jù)消費(fèi)則分別對應(yīng)著庫表的創(chuàng)建和查詢。對于ClickHouse而言，數(shù)據(jù)的生成是上游庫表的同步導(dǎo)入，數(shù)據(jù)的消費(fèi)是用戶通過諸如BI平臺等服務(wù)對庫表進(jìn)行查詢。理論上，按照業(yè)務(wù)的需求，每個(gè)ClickHouse的表都應(yīng)該有一個(gè)相應(yīng)的生命周期，假設(shè)所有的表都以天粒度為分區(qū)，則某些表往往只需要保留一周或一個(gè)月的數(shù)據(jù)，其它有一些表可能需要保留三個(gè)月或半年，可見不同的表生命周期應(yīng)該是不一樣的。但問題在于如何為每個(gè)表設(shè)定合適的生命周期？過長的生命周期會造成存儲資源的浪費(fèi)，占滿ClickHouse集群的磁盤空間，而過短的生命周期可能不能滿足業(yè)務(wù)方的需求，導(dǎo)致查不出需要的數(shù)據(jù)。我們過往的做法是，在ClickHouse入庫前，讓用戶填寫生命周期。但這樣的做法并未能從根本上解決問題，究其原因總結(jié)下來有以下幾點(diǎn)：

• 隨著業(yè)務(wù)發(fā)展，業(yè)務(wù)方對于同一張表的生命周期的需求也在不斷地發(fā)生變化。一開始需要存儲一年的數(shù)據(jù)，經(jīng)過一段時(shí)間之后可能只需要一個(gè)月甚至已經(jīng)逐漸廢棄無人問津，但業(yè)務(wù)方可能并不會去修改這個(gè)生命周期。
• ?業(yè)務(wù)方有時(shí)候自己也并不知道生命周期要設(shè)多久才合適，這種情況下用戶一般會選擇偏大的生命周期，但最終使用的時(shí)候其實(shí)只用了最近幾天的數(shù)據(jù)，以致于造成ClickHouse集群磁盤空間的浪費(fèi)。
• ?最初建表的用戶并不一定是查詢數(shù)據(jù)的用戶，通常某一張表建立之后，會有多個(gè)用戶同時(shí)使用這一張表，真正使用該表的用戶往往對生命周期是無感的，他們不一定有義務(wù)或者并不一定能意識到表生命周期的問題。最初填寫生命周期的用戶可能在一段時(shí)間之后也離職了。

2.1 整體思路

• 從審計(jì)日志中篩選出近10天內(nèi)接受過的所有SQL請求。
• ?利用AST Parser對SQL進(jìn)行解析，找出每個(gè)SQL所涉及到的表及其分區(qū)范圍。
• ?統(tǒng)計(jì)所有的表所涉及到的最大分區(qū)范圍，將最大分區(qū)范圍設(shè)置為該表的生命周期。

2.2 基于AST Parser解析SQL的分區(qū)范圍

• 遍歷AST，遍歷過程中需要獲取到ClickHouse的元數(shù)據(jù)信息（包括表的分區(qū)字段名等），最后在遍歷AST的對比表達(dá)式節(jié)點(diǎn)時(shí)，解析出所有與分區(qū)字段名相關(guān)的過濾條件；
• 合并過濾條件得出最終的分區(qū)范圍。

SELECT
    *
FROM t1
WHERE
    (ftime >= '2021-09-01' AND ftime <= '2021-09-10')
    OR ftime IN ('2021-08-01', '2021-08-02')

2.3 ClickHouse AST Parser的使用?

• 給定SQL語句，找出與該SQL相關(guān)的表名?；谒梢詫?shí)現(xiàn)熱點(diǎn)表分析、緩存機(jī)制等功能。

String sql = "SELECT t1.id, count(1) as total_count FROM my_db1.table1 t1 LEFT JOIN my_db2.table2 t2 ON t1.id = t2.id GROUP BY t1.id";
AstParser astParser = new AstParser();
INode ast = (INode) astParser.parse(sql);
ReferredTablesDetector referredTablesDetector = new ReferredTablesDetector();
// tables should be ["my_db1.table1", "my_db2.table2"] in this caseList tables = referredTablesDetector.searchTables(ast);

• 給定SQL語句，找出該SQL所涉及到的分區(qū)范圍。

// we need to implement MetadataService first
MetadataService metadataService = new MetadataService() {
            @Override
            public String getPartitionColName(String tableFullName) {
                // TODO: implement this method
                return null;
            }


            @Override
            public List<String> getTables() {
                // TODO: implement this method
                return null;
            }
        };
String todayDate = "2022-01-01"; // for parsing UDF like today() and yesterday() in the SQL
String targetIP = "127.0.0.1"; // the node to get metadata
ReferredPartitionsDetector referredPartitionsDetector = new ReferredPartitionsDetector(todayDate, targetIp, metadataService);
List<String> partitionRangeList = referredPartitionsDetector.searchTablePartitions(ast);

public interface MetadataService {
    String getPartitionColName(String tableFullName);
    List getTables();
}

• 抽取Distributed引擎表的參數(shù)信息。ClickHouse的Distributed引擎給予了我們靈活的數(shù)據(jù)組織方式，但有時(shí)我們確實(shí)需要提取Distributed引擎表里的相關(guān)信息，比如所涉及到的cluster、database和table。單純使用正則表達(dá)式來提取很容易出錯(cuò)，尤其是當(dāng)CREATE TABLE建表語句中有復(fù)雜的注釋時(shí)，容易抽取出注釋的內(nèi)容。通過AST解析可以比較好地解決這個(gè)問題。

String sql = "CREATE TABLE my_db.my_tbl (date Date, name String) Engine = Distributed('my_cluster', 'my_db', 'my_tbl_local', rand())";
DistributedTableInfoDetector distributedTableInfoDetector = new DistributedTableInfoDetector();
// clusterName is "my_cluster"
String clusterName = distributedTableInfoDetector.searchCluster(sql);
// tableFullName is "my_db.my_tbl_local"
String tableFullName = distributedTableInfoDetector.searchLocalTableFullName(sql);

• 改寫SQL，優(yōu)化效率。目前只實(shí)現(xiàn)了對JOIN操作增加GLOBAL關(guān)鍵字，更多的改寫邏輯可以在日后更新，幫助提高SQL效率，在解析層屏蔽掉慢查詢SQL。

String sql = "SELECT t1.id, count(1) as total_count FROM my_db1.table1 t1 LEFT JOIN my_db2.table2 t2 ON t1.id = t2.id GROUP BY t1.id";
AstParser astParser = new AstParser(false);
SelectUnionQuery ast = (SelectUnionQuery) astParser.parse(sql);
GlobalJoinAstRewriter globalJoinAstRewriter = new GlobalJoinAstRewriter();
String rewrittenSql =  globalJoinAstRewriter.visit((INode) ast);
// the rewritten SQL should be:
// SELECT t1.id, count(1) as total_count FROM my_db1.table1 t1 GLOBAL LEFT JOIN my_db2.table2 t2 ON t1.id = t2.id GROUP BY t1.id

作者介紹：麥嘉銘，前后就職于阿里云和BIGO，目前在騰訊音樂參與大數(shù)據(jù)分析平臺建設(shè)，主要負(fù)責(zé)Clickhouse和Presto的運(yùn)維和開發(fā)