如何将含逗号分隔数字的CSV导入ClickHouse的Array(Int16)列？

场景复现 #

CSV文件内容 #

cat > test.csv << EOF
a,b
x,"1,2,42"
EOF

ClickHouse表结构 #

DROP TABLE test_csv;
CREATE TABLE test_csv
(
    `a` LowCardinality(String),
    `c` Array(Int16)
)
ENGINE = MergeTree
ORDER BY a

插入报错语句及信息 #

执行以下插入语句：

INSERT INTO test_csv SELECT * FROM
(
  SELECT a, CAST(splitByChar(',', b) AS Array(Int16)) AS c
  FROM
  (
    SELECT *
    FROM file('test.csv', 'CSVWithNames', 'auto')
  )
  SETTINGS schema_inference_make_columns_nullable = 0
)

得到报错：

Received exception from server (version 25.5.1):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown expression or function identifier b in scope SELECT a, CAST(splitByChar(',', b), 'Array(Int16)') AS c FROM (SELECT * FROM file('test.csv', 'CSVWithNames', 'auto')) SETTINGS schema_inference_make_columns_nullable = 0. Maybe you meant: ['a']. (UNKNOWN_IDENTIFIER)

补充说明 #

单独执行查询语句可得到预期结果：

SELECT
    a,
    CAST(splitByChar(',', b), 'Array(Int16)') AS c
FROM
(
    SELECT *
    FROM file('test.csv', 'CSVWithNames', 'auto')
)
SETTINGS schema_inference_make_columns_nullable = 0

查询结果：

┌─a─┬─c────────┐
1. │ x │ [1,2,42] │
   └───┴──────────┘

原因分析 #

问题出在SETTINGS schema_inference_make_columns_nullable = 0的作用范围。单独查询时，该设置作用于整个查询逻辑，使得file函数能正确推断出b列；但插入语句中，设置被放在内层子查询的末尾，没有正确作用于file函数的schema推断过程，导致内层子查询仅识别到a列，无法找到b列。

可行的导入方案 #

方案1：显式指定CSV列类型 #

直接在file函数中指定列类型，避免依赖自动推断，这是最稳定的方式：

INSERT INTO test_csv 
SELECT a, CAST(splitByChar(',', b) AS Array(Int16)) AS c
FROM file('test.csv', 'CSVWithNames', 'a String, b String')
SETTINGS schema_inference_make_columns_nullable = 0;

方案2：调整SETTINGS的位置 #

将SETTINGS移到file函数所在的子查询内部，确保设置作用于schema推断：

INSERT INTO test_csv 
SELECT * FROM
(
  SELECT a, CAST(splitByChar(',', b) AS Array(Int16)) AS c
  FROM
  (
    SELECT *
    FROM file('test.csv', 'CSVWithNames', 'auto')
    SETTINGS schema_inference_make_columns_nullable = 0
  )
);

两种方案都能成功将CSV中的逗号分隔字符串转换为Array(Int16)类型并插入目标表。

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