1. 从ClickHouse到Doris为什么需要迁移最近在做一个郑州港区的智慧交通项目涉及200多个路口和大量电警卡口设备。之前小项目用单机ClickHouse还能应付现在数据量直接翻了几十倍明显感觉力不从心了。特别是遇到数据更新和复杂联表查询时ClickHouse单点部署的瓶颈就暴露无遗。我们团队评估后决定迁移到Doris集群主要看中三点一是Doris的MPP架构天然适合海量数据二是对实时更新支持更好三是兼容MySQL协议开发成本低。但迁移过程中发现个棘手问题——原来在ClickHouse里大量使用的数组类型Array字段在Doris里需要重新设计。交通数据有个特点很多指标天然适合用数组表示。比如一个路口的四个方向东、南、西、北的违法数据用数组存储比拆成四个字段更合理。之前用ClickHouse的Array类型很方便现在要确保在Doris集群环境下这些功能还能正常用。2. Doris数组类型深度解析2.1 基础特性对比Doris的ARRAY类型和ClickHouse相比有几个关键区别需要注意存储模型Doris的数组在Duplicate模型表里可以直接用Unique模型需要2.0以上版本才能作为非主键列元素类型支持几乎所有基础类型从BOOLEAN到DATETIME都能作为数组元素函数支持虽然没有ClickHouse的arrayJoin但有explode、element_at等实用函数实测发现个细节Doris的数组元素如果是NULL值在聚合函数如array_sum里会被自动跳过。这个特性和ClickHouse一致对交通数据清洗特别有用——设备采集的原始数据经常会有缺失值。2.2 性能优化要点在集群环境下使用数组要注意分布列选择避免用数组列做分桶键会导致数据倾斜。我们项目用region_idintersection_number组合分桶元素大小控制单个数组元素建议不超过1MB过大的数组会影响BE节点内存预分配策略对于固定长度的指标数组如每小时车流量建表时就指定合理长度这里有个真实案例我们有个表存储每分钟车道流量最初设计成动态数组后来改成固定长度24的ARRAY 查询性能提升了40%。3. 交通数据建模实战3.1 表结构设计以路口安全指标表为例这是我们在生产环境使用的DDLCREATE TABLE signal.dwd_signal_securityindex_ri ( time_stamp DATETIME NOT NULL COMMENT 周期开始时间, region_id INT NOT NULL COMMENT 区域编号, intersection_number INT NOT NULL COMMENT 路口ID, safety_factor FLOAT DEFAULT 0 COMMENT 安全系数, approach_index ARRAYVARCHAR(128) COMMENT 进口指标数组 ) DUPLICATE KEY(time_stamp, region_id, intersection_number) DISTRIBUTED BY HASH(region_id, intersection_number) BUCKETS 8 PROPERTIES ( replication_num 3, storage_medium SSD );关键设计点使用DUPLICATE模型保留原始数据数组字段放在最后避免影响前缀索引分桶数按数据量预估每个桶约5GB设置SSD存储提升随机访问性能3.2 数据写入方案从ClickHouse迁移数据时数组字段需要特殊处理。我们开发了转换工具处理两种场景场景1直接迁移# ClickHouse原始数据格式[1,2,3] # 直接转为Doris兼容格式 def convert_array(ck_data): return ck_data.replace([, [).replace(], ])场景2多列合并# 将多个指标列合并为数组 def merge_columns(north, south, east, west): return f[{north},{south},{east},{west}]实际跑批处理时发现用JDBC的PreparedStatement.setArray()比拼接SQL字符串快3倍左右特别是处理百万级数据时更明显。4. 查询优化技巧4.1 基础查询Doris的数组查询语法很直观-- 查询数组长度 SELECT array_size(approach_index) FROM security_index; -- 获取特定位置元素 SELECT element_at(approach_index, 2) FROM security_index;但要注意element_at的position参数从1开始支持负数倒序索引-1表示最后一个元素。4.2 高级分析对于交通数据常见的分析场景推荐两种方案方案Aexplode子查询-- 分析各进口违法率 SELECT t.intersection_number, exploded.approach_data, split_part(exploded.approach_data, -, 2) AS illegal_rate FROM ( SELECT intersection_number, explode(approach_index) AS approach_data FROM security_index ) t WHERE date(time_stamp) 2023-07-01;方案BLateral Join-- 更高效的写法 SELECT s.intersection_number, app.data, split_part(app.data, -, 3) AS conflict_count FROM security_index s LATERAL VIEW explode(approach_index) app AS data WHERE s.region_id 5;实测发现在分析200路口的日数据时方案B比方案A快60%因为减少了临时表的创建。5. 避坑指南迁移过程中我们踩过几个坑分享给大家版本兼容问题早期Doris 1.2版本对ARRAY聚合函数支持不完善升级到2.0.3后解决内存控制一次查询涉及太多数组展开时需要设置exec_mem_limit参数防止OOM类型强校验Doris对数组元素类型检查比ClickHouse严格混用INT和STRING会直接报错空值处理array_contains(NULL, x)永远返回NULL要先用coalesce处理有个特别隐蔽的问题在Java代码中用JSON字符串设置数组字段时必须确保没有多余的转义字符。我们曾因为这个问题浪费了半天排查时间。6. 性能对比测试在同等硬件配置3台16核64GB服务器下我们对ClickHouse单机和Doris集群做了对比测试测试项ClickHouseDoris集群数据导入速度12万条/秒8万条/秒简单数组查询0.8秒1.2秒复杂分析查询4.5秒2.1秒并发查询稳定性15QPS时变慢50QPS稳定虽然单条导入和简单查询Doris稍慢但在真实业务场景下优势明显数据更新不用怕表锁复杂查询利用多节点并行计算支持高并发报表查询特别是做路口指标聚合分析时Doris的MPP架构能把计算任务分发到各节点200个路口的数据分析从原来的分钟级降到秒级。
Doris数组实战：从ClickHouse迁移到集群的交通数据建模指南
1. 从ClickHouse到Doris为什么需要迁移最近在做一个郑州港区的智慧交通项目涉及200多个路口和大量电警卡口设备。之前小项目用单机ClickHouse还能应付现在数据量直接翻了几十倍明显感觉力不从心了。特别是遇到数据更新和复杂联表查询时ClickHouse单点部署的瓶颈就暴露无遗。我们团队评估后决定迁移到Doris集群主要看中三点一是Doris的MPP架构天然适合海量数据二是对实时更新支持更好三是兼容MySQL协议开发成本低。但迁移过程中发现个棘手问题——原来在ClickHouse里大量使用的数组类型Array字段在Doris里需要重新设计。交通数据有个特点很多指标天然适合用数组表示。比如一个路口的四个方向东、南、西、北的违法数据用数组存储比拆成四个字段更合理。之前用ClickHouse的Array类型很方便现在要确保在Doris集群环境下这些功能还能正常用。2. Doris数组类型深度解析2.1 基础特性对比Doris的ARRAY类型和ClickHouse相比有几个关键区别需要注意存储模型Doris的数组在Duplicate模型表里可以直接用Unique模型需要2.0以上版本才能作为非主键列元素类型支持几乎所有基础类型从BOOLEAN到DATETIME都能作为数组元素函数支持虽然没有ClickHouse的arrayJoin但有explode、element_at等实用函数实测发现个细节Doris的数组元素如果是NULL值在聚合函数如array_sum里会被自动跳过。这个特性和ClickHouse一致对交通数据清洗特别有用——设备采集的原始数据经常会有缺失值。2.2 性能优化要点在集群环境下使用数组要注意分布列选择避免用数组列做分桶键会导致数据倾斜。我们项目用region_idintersection_number组合分桶元素大小控制单个数组元素建议不超过1MB过大的数组会影响BE节点内存预分配策略对于固定长度的指标数组如每小时车流量建表时就指定合理长度这里有个真实案例我们有个表存储每分钟车道流量最初设计成动态数组后来改成固定长度24的ARRAY 查询性能提升了40%。3. 交通数据建模实战3.1 表结构设计以路口安全指标表为例这是我们在生产环境使用的DDLCREATE TABLE signal.dwd_signal_securityindex_ri ( time_stamp DATETIME NOT NULL COMMENT 周期开始时间, region_id INT NOT NULL COMMENT 区域编号, intersection_number INT NOT NULL COMMENT 路口ID, safety_factor FLOAT DEFAULT 0 COMMENT 安全系数, approach_index ARRAYVARCHAR(128) COMMENT 进口指标数组 ) DUPLICATE KEY(time_stamp, region_id, intersection_number) DISTRIBUTED BY HASH(region_id, intersection_number) BUCKETS 8 PROPERTIES ( replication_num 3, storage_medium SSD );关键设计点使用DUPLICATE模型保留原始数据数组字段放在最后避免影响前缀索引分桶数按数据量预估每个桶约5GB设置SSD存储提升随机访问性能3.2 数据写入方案从ClickHouse迁移数据时数组字段需要特殊处理。我们开发了转换工具处理两种场景场景1直接迁移# ClickHouse原始数据格式[1,2,3] # 直接转为Doris兼容格式 def convert_array(ck_data): return ck_data.replace([, [).replace(], ])场景2多列合并# 将多个指标列合并为数组 def merge_columns(north, south, east, west): return f[{north},{south},{east},{west}]实际跑批处理时发现用JDBC的PreparedStatement.setArray()比拼接SQL字符串快3倍左右特别是处理百万级数据时更明显。4. 查询优化技巧4.1 基础查询Doris的数组查询语法很直观-- 查询数组长度 SELECT array_size(approach_index) FROM security_index; -- 获取特定位置元素 SELECT element_at(approach_index, 2) FROM security_index;但要注意element_at的position参数从1开始支持负数倒序索引-1表示最后一个元素。4.2 高级分析对于交通数据常见的分析场景推荐两种方案方案Aexplode子查询-- 分析各进口违法率 SELECT t.intersection_number, exploded.approach_data, split_part(exploded.approach_data, -, 2) AS illegal_rate FROM ( SELECT intersection_number, explode(approach_index) AS approach_data FROM security_index ) t WHERE date(time_stamp) 2023-07-01;方案BLateral Join-- 更高效的写法 SELECT s.intersection_number, app.data, split_part(app.data, -, 3) AS conflict_count FROM security_index s LATERAL VIEW explode(approach_index) app AS data WHERE s.region_id 5;实测发现在分析200路口的日数据时方案B比方案A快60%因为减少了临时表的创建。5. 避坑指南迁移过程中我们踩过几个坑分享给大家版本兼容问题早期Doris 1.2版本对ARRAY聚合函数支持不完善升级到2.0.3后解决内存控制一次查询涉及太多数组展开时需要设置exec_mem_limit参数防止OOM类型强校验Doris对数组元素类型检查比ClickHouse严格混用INT和STRING会直接报错空值处理array_contains(NULL, x)永远返回NULL要先用coalesce处理有个特别隐蔽的问题在Java代码中用JSON字符串设置数组字段时必须确保没有多余的转义字符。我们曾因为这个问题浪费了半天排查时间。6. 性能对比测试在同等硬件配置3台16核64GB服务器下我们对ClickHouse单机和Doris集群做了对比测试测试项ClickHouseDoris集群数据导入速度12万条/秒8万条/秒简单数组查询0.8秒1.2秒复杂分析查询4.5秒2.1秒并发查询稳定性15QPS时变慢50QPS稳定虽然单条导入和简单查询Doris稍慢但在真实业务场景下优势明显数据更新不用怕表锁复杂查询利用多节点并行计算支持高并发报表查询特别是做路口指标聚合分析时Doris的MPP架构能把计算任务分发到各节点200个路口的数据分析从原来的分钟级降到秒级。
