ClickHouse 剖析 | 结合 SQL 解析的 ClickHouse 表生命周期管理

1. 问题背景在我们日常的业务中，数据往往以库表的形式呈现，数据生产和数据消费则分别对应着库表的创建和查询。对于ClickHouse而言，数据的生成是上游库表的同步导入，数据的消费是用户通过诸如BI平台等服务对库表进行查询。理论上，按照业务的需求，每个ClickHouse的表都应该有一个相应的生命周期，假设所有的表都以天粒度为分区，则某些表往往只需要保留一周或一个月的数据，其它有一些表可能需要保留三个月或半年，可见不同的表生命周期应该是不一样的。但问题在于如何为每个表设定合适的生命周期？过长的生命周期会造成存储资源的浪费，占满ClickHouse集群的磁盘空间，而过短的生命周期可能不能满足业务方的需求，导致查不出需要的数据。我们过往的做法是，在ClickHouse入库前，让用户填写生命周期。但这样的做法并未能从根本上解决问题，究其原因总结下来有以下几点：

• 随着业务发展，业务方对于同一张表的生命周期的需求也在不断地发生变化。一开始需要存储一年的数据，经过一段时间之后可能只需要一个月甚至已经逐渐废弃无人问津，但业务方可能并不会去修改这个生命周期。
•  业务方有时候自己也并不知道生命周期要设多久才合适，这种情况下用户一般会选择偏大的生命周期，但最终使用的时候其实只用了最近几天的数据，以致于造成ClickHouse集群磁盘空间的浪费。
•  最初建表的用户并不一定是查询数据的用户，通常某一张表建立之后，会有多个用户同时使用这一张表，真正使用该表的用户往往对生命周期是无感的，他们不一定有义务或者并不一定能意识到表生命周期的问题。最初填写生命周期的用户可能在一段时间之后也离职了。

2.1 整体思路

• 从审计日志中筛选出近10天内接受过的所有SQL请求。
•  利用AST Parser对SQL进行解析，找出每个SQL所涉及到的表及其分区范围。
•  统计所有的表所涉及到的最大分区范围，将最大分区范围设置为该表的生命周期。

2.2 基于AST Parser解析SQL的分区范围

• 遍历AST，遍历过程中需要获取到ClickHouse的元数据信息（包括表的分区字段名等），最后在遍历AST的对比表达式节点时，解析出所有与分区字段名相关的过滤条件；
• 合并过滤条件得出最终的分区范围。

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相关的表名。基于它可以实现热点表分析、缓存机制等功能。

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所涉及到的分区范围。

// 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引擎表的参数信息。ClickHouse的Distributed引擎给予了我们灵活的数据组织方式，但有时我们确实需要提取Distributed引擎表里的相关信息，比如所涉及到的cluster、database和table。单纯使用正则表达式来提取很容易出错，尤其是当CREATE TABLE建表语句中有复杂的注释时，容易抽取出注释的内容。通过AST解析可以比较好地解决这个问题。

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，优化效率。目前只实现了对JOIN操作增加GLOBAL关键字，更多的改写逻辑可以在日后更新，帮助提高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，目前在腾讯音乐参与大数据分析平台建设，主要负责Clickhouse和Presto的运维和开发