保姆级教程：用Doris的SPLIT_BY_STRING和EXPLODE，把‘1-2-3-4’这种字符串拆成多行明细表

数据清洗实战：用Doris高效拆分分隔符字符串的完整指南

在数据分析工作中，我们经常会遇到这样的数据格式："1-2-3-4"、"A,B,C,D"或"2023|08|15"。这些用特定分隔符连接的字符串，虽然存储紧凑，却给分析带来了诸多不便。本文将带你深入掌握Doris中SPLIT_BY_STRING和EXPLODE的组合用法，实现从"脏数据"到结构化明细表的华丽转变。

1. 理解字符串拆分的核心需求

在日常ETL流程中，分隔符字符串的拆分需求无处不在。交通信号数据中的"路口ID-进口道编号-指标1-指标2"格式、电商订单中的"SKU1,SKU2,SKU3"列表、日志中的"IP|时间|操作"记录，都需要我们将其拆解为规范化的明细表。

传统做法是在应用层预处理，但这会导致：

• 数据冗余：原始字符串和拆分结果需要同时存储
• 流程复杂：需要额外的处理脚本
• 时效性差：无法实时响应数据变化

Doris提供的表函数组合方案，能够在数据库层面高效解决这些问题。我们来看一个典型场景：某交通管理系统将每个路口的各个进口道指标存储为"1-0.85-0.12-3"这样的字符串，需要拆分为结构化表格进行分析。

2. 关键函数深度解析

2.1 SPLIT_BY_STRING：字符串转数组

SPLIT_BY_STRING函数是处理分隔符字符串的第一把钥匙。它的基本语法如下：

SPLIT_BY_STRING(字符串, 分隔符)

这个函数的核心特点包括：

• 多字符分隔符支持：不仅支持单字符如"-"，也支持多字符如"||"
• 空值处理：连续分隔符会产生空字符串元素
• 性能优化：向量化执行引擎下效率极高

实际使用时，我们常需要处理各种边界情况：

-- 基础拆分示例 SELECT SPLIT_BY_STRING('1-2-3-4', '-'); -- 结果: ['1','2','3','4'] -- 含空元素的处理 SELECT SPLIT_BY_STRING('A,,C,D', ','); -- 结果: ['A','','C','D'] -- 多字符分隔符 SELECT SPLIT_BY_STRING('数据1||数据2||数据3', '||'); -- 结果: ['数据1','数据2','数据3']

2.2 EXPLODE：数组转多行

得到数组后，EXPLODE函数负责将其"炸开"成多行记录。这是Doris实现列转行的核心函数，必须配合LATERAL VIEW使用：

SELECT 原始字段, 炸开后的值 FROM 表名 LATERAL VIEW EXPLODE(数组字段) 虚拟表名 AS 炸开后的值

关键注意事项：

1. NULL值处理：原始数组为NULL时，EXPLODE不产生行
2. 性能影响：每行数组元素数量决定最终行数，大数组需谨慎
3. 字段保留：EXPLODE只输出炸开后的值，需同时选择其他需要保留的字段

3. 完整解决方案实战

让我们通过一个交通指标分析的完整案例，演示如何组合使用这些函数。

3.1 原始数据准备

假设有表traffic_metrics存储路口级指标，结构如下：

CREATE TABLE traffic_metrics ( record_time DATETIME, intersection_id INT, approach_data VARCHAR(200) -- 格式"进口道编号-行人守法率-冲突次数" ); -- 示例数据 INSERT INTO traffic_metrics VALUES ('2023-08-01 08:00', 101, '1-0.85-3'), ('2023-08-01 08:00', 101, '2-0.92-1'), ('2023-08-01 09:00', 101, '1-0.82-5');

3.2 分步拆解流程

第一步：字符串转数组

SELECT record_time, intersection_id, SPLIT_BY_STRING(approach_data, '-') AS metric_array FROM traffic_metrics;

第二步：数组转多行

SELECT t.record_time, t.intersection_id, e.metric_value FROM traffic_metrics t LATERAL VIEW EXPLODE(SPLIT_BY_STRING(approach_data, '-')) e AS metric_value;

第三步：结构化提取

SELECT record_time, intersection_id, CAST(element_at(metric_array, 1) AS INT) AS approach_no, CAST(element_at(metric_array, 2) AS DECIMAL(3,2)) AS compliance_rate, CAST(element_at(metric_array, 3) AS INT) AS conflict_count FROM ( SELECT record_time, intersection_id, SPLIT_BY_STRING(approach_data, '-') AS metric_array FROM traffic_metrics ) t;

3.3 创建可复用视图

对于频繁使用的拆分逻辑，建议创建视图：

CREATE VIEW traffic_metrics_detail AS SELECT record_time, intersection_id, CAST(element_at(SPLIT_BY_STRING(approach_data, '-'), 1) AS INT) AS approach_no, CAST(element_at(SPLIT_BY_STRING(approach_data, '-'), 2) AS DECIMAL(3,2)) AS compliance_rate, CAST(element_at(SPLIT_BY_STRING(approach_data, '-'), 3) AS INT) AS conflict_count FROM traffic_metrics;

4. 高级技巧与性能优化

4.1 复杂分隔符处理

遇到非常规分隔符时，可以采用以下策略：

-- 处理混合分隔符 SELECT SPLIT_BY_STRING( REPLACE(REPLACE(original_str, ' ', ''), '|', ','), ',' ); -- JSON字符串提取 SELECT SPLIT_BY_STRING( REGEXP_EXTRACT(json_str, '"items":\\["(.*?)"\\]', 1), '","' );

4.2 大规模数据性能优化

当处理海量数据时，需注意：

1. 向量化引擎：确保开启

   SET enable_vectorized_engine = true;

2. 分区裁剪：确保查询能利用分区过滤

3. 内存控制：大数组可能导致内存压力，可通过以下方式缓解：

   -- 分批处理 SELECT ... FROM large_table WHERE dt = '2023-08-01' -- 按分区过滤 LIMIT 10000;

4. 物化视图：对频繁查询的模式建立物化视图

4.3 类型转换最佳实践

从字符串拆分出的元素默认仍是字符串，需合理转换：

-- 安全的类型转换示例 SELECT CASE WHEN element_at(arr,1) = '' THEN NULL ELSE CAST(element_at(arr,1) AS INT) END AS safe_int FROM ...

5. 常见问题解决方案

5.1 不规则数据处理

实际数据往往不完美，我们需要处理各种异常情况：

问题1：不一致的分隔符

解决方案：

-- 统一分隔符 SELECT SPLIT_BY_STRING( REGEXP_REPLACE(raw_str, '[-;,]', '|'), -- 统一替换为| '|' );

问题2：元素数量不一致

解决方案：

-- 确保访问安全 SELECT record_time, intersection_id, -- 使用COALESCE提供默认值 COALESCE(CAST(element_at(arr,1) AS INT), -1) AS approach_no, -- 检查数组长度 CASE WHEN array_length(arr) >= 2 THEN CAST(element_at(arr,2) AS DECIMAL(3,2)) ELSE NULL END AS compliance_rate FROM ( SELECT record_time, intersection_id, SPLIT_BY_STRING(approach_data, '-') AS arr FROM traffic_metrics ) t;

5.2 多级拆分场景

有时需要处理嵌套的分隔符结构，如"1:80,2:90"表示不同指标的键值对：

SELECT t.record_time, split_pair[1] AS metric_type, split_pair[2] AS metric_value FROM ( SELECT record_time, EXPLODE(SPLIT_BY_STRING(metrics, ',')) AS metric_pair FROM complex_metrics ) t LATERAL VIEW EXPLODE(SPLIT_BY_STRING(metric_pair, ':')) e AS split_pair;

5.3 与其他ETL工具对比

在Doris集群资源充足的情况下，优先使用数据库内方案能大幅简化架构。我曾在一个交通流量分析项目中，将原本需要Spark集群处理的ETL流程迁移到Doris函数实现，使数据处理延迟从分钟级降低到秒级，同时节省了50%的计算资源。