如何构造String源数据并转换为ClickHouse的数组元组复杂类型？

问题描述 #

我需要将HDFS上Impala的Parquet数据导入ClickHouse，目标表包含两种复杂类型：

target_type1 Array(Tuple(  LowCardinality(String),
                      Int8,
                      Int32, 
                      Int32,
                      Int32
         ))

target_type2 Array(Tuple(  LowCardinality(String),
                      LowCardinality(String), 
                      LowCardinality(String)
         ))

源表对应字段的类型是String，请问该如何构造源表的String数据，并将其转换为目标类型？

附带示例场景：
目标表定义：

create table test (
    a String,
    b Array(String),
    c Array(Tuple(Int, String, String))
) engine = MergeTree order by tuple()

源数据构造示例如下：

with t as (
    select '123#def#aaa|456#xxx#aaa|789#bbbb#aaaa|3333#www#aaaa' as x, splitByChar('|', x) as y, splitByChar('#', y[1]) as z
) select * from t;

请问如何构造源字符串x并完成转换，才能导入到c Array(Tuple(Int, String, String))字段中？

解决方案 #

一、示例场景（c字段）的处理 #

1. 源字符串构造规则

用双层分隔符区分元组和元组内元素：

• 用|作为元组之间的分隔符，拆分多个(Int, String, String)元组
• 用#作为元组内元素的分隔符，拆分每个元组里的Int、String、String

示例里的x就是符合规则的：'123#def#aaa|456#xxx#aaa|789#bbbb#aaaa|3333#www#aaaa'，每段xxx#xxx#xxx对应一个完整元组。

2. 转换SQL逻辑

通过嵌套的字符串拆分+类型转换实现：

with t as (
    select '123#def#aaa|456#xxx#aaa|789#bbbb#aaaa|3333#www#aaaa' as x
)
select
    arrayMap(
        tuple_str -> (
            toInt64(splitByChar('#', tuple_str)[1]),
            splitByChar('#', tuple_str)[2],
            splitByChar('#', tuple_str)[3]
        ),
        splitByChar('|', x)
    ) as c
from t;

逻辑说明：

1. 先用splitByChar('|', x)把整个字符串拆分成单个元组的字符串数组
2. 再用arrayMap遍历每个元组字符串，用splitByChar('#', ...)拆分出每个元素，同时把第一个元素转为Int类型，最终组成目标的Array(Tuple(Int, String, String))类型。

二、实际场景（target_type1和target_type2）的处理 #

1. 源字符串构造规则

针对target_type1

同样采用双层分隔符：

• 外层用|分隔每个(LowCardinality(String), Int8, Int32, Int32, Int32)元组
• 内层用#分隔元组内的5个元素
  示例源字符串：'str1#1#100#200#300|str2#2#400#500#600'

针对target_type2

• 外层用|分隔每个(LowCardinality(String), LowCardinality(String), LowCardinality(String))元组
• 内层用#分隔元组内的3个字符串元素
  示例源字符串：'a#b#c|d#e#f|g#h#i'

2. 转换SQL逻辑

target_type1的转换

select
    arrayMap(
        tuple_str -> (
            toLowCardinality(splitByChar('#', tuple_str)[1]),
            toInt8(splitByChar('#', tuple_str)[2]),
            toInt32(splitByChar('#', tuple_str)[3]),
            toInt32(splitByChar('#', tuple_str)[4]),
            toInt32(splitByChar('#', tuple_str)[5])
        ),
        splitByChar('|', source_str)
    ) as target_type1
from your_source_table;

target_type2的转换

select
    arrayMap(
        tuple_str -> (
            toLowCardinality(splitByChar('#', tuple_str)[1]),
            toLowCardinality(splitByChar('#', tuple_str)[2]),
            toLowCardinality(splitByChar('#', tuple_str)[3])
        ),
        splitByChar('|', source_str)
    ) as target_type2
from your_source_table;

注意事项

• 确保源字符串的分隔符不会和字段内容冲突，如果字段本身包含#或|，需要换用其他不冲突的分隔符（比如^或&&）
• 转换时要严格匹配目标类型的元素顺序和类型，避免类型转换错误
• 导入时可以直接在INSERT语句中使用上述转换逻辑，或者在创建Materialized View时提前完成转换

内容的提问来源于stack exchange，提问作者Rick