ClickHouse读取Minio中Parquet文件时NULL值插入非空列报错求助

错误CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN的核心原因是：ClickHouse读取Parquet文件时，将accepted_at列解析为Nullable(UInt32)类型，但插入过程中试图将其转换为非Nullable的UInt32（而非表定义的Nullable(DateTime64(3))），这大概率是列顺序不匹配或类型自动推断偏差导致的，而非表结构本身的问题。

1. 显式指定列映射与类型转换 #

避免使用SELECT *，手动对应Parquet列与目标表列，并对日期类列做显式类型转换，同时保留Nullable属性：

-- 开启按列名匹配（可选但推荐）
SET input_format_parquet_use_column_names = 1;

INSERT INTO general_marts.gm_order (
    order_id,
    user_id,
    vendor_id,
    total_price,
    total_price_after_discount,
    payable_price,
    customer_name,
    vendor_name,
    preparation_time,
    delivery_type,
    accepted_at,
    nfc_reason,
    rejected_at,
    paid_at,
    created_at,
    list_order_status,
    list_order_status_datetime,
    list_payment_status,
    list_payment_status_datetime,
    list_delivery_status,
    list_delivery_status_datetime,
    voucher_id,
    voucher_name,
    voucher_code,
    voucher_value,
    ofood_share_delivery_price,
    customer_share_delivery_price,
    vendor_share_delivery_price,
    delivery_order_id,
    packing_price,
    vendor_tax,
    refund_at,
    refund_amount,
    city_name,
    list_product_id,
    list_product_name,
    list_product_variation_id,
    list_product_variation_name,
    list_option_ids,
    list_options,
    list_option_label,
    list_has_option,
    list_price,
    list_price_after_discount,
    list_quantity,
    list_stock,
    list_capacity
)
SELECT
    order_id,
    user_id,
    vendor_id,
    total_price,
    total_price_after_discount,
    payable_price,
    customer_name,
    vendor_name,
    preparation_time,
    delivery_type,
    -- 将Nullable(UInt32)转换为表定义的Nullable(DateTime64(3))
    toNullable(toDateTime64(accepted_at, 3)),
    nfc_reason,
    toNullable(toDateTime64(rejected_at, 3)),
    toNullable(toDateTime64(paid_at, 3)),
    toDateTime64(created_at, 3),
    list_order_status,
    list_order_status_datetime,
    list_payment_status,
    list_payment_status_datetime,
    list_delivery_status,
    list_delivery_status_datetime,
    voucher_id,
    voucher_name,
    voucher_code,
    voucher_value,
    ofood_share_delivery_price,
    customer_share_delivery_price,
    vendor_share_delivery_price,
    delivery_order_id,
    packing_price,
    vendor_tax,
    toNullable(toDateTime64(refund_at, 3)),
    refund_amount,
    city_name,
    list_product_id,
    list_product_name,
    list_product_variation_id,
    list_product_variation_name,
    list_option_ids,
    list_options,
    list_option_label,
    list_has_option,
    list_price,
    list_price_after_discount,
    list_quantity,
    list_stock,
    list_capacity
FROM s3(
'http://host:9000/general-marts/general_mart_order/year=2024/month=02/day=12/*.snappy.parquet',
'aws_access_key_id',
'aws_secret_access_key','Parquet');

2. 调整ClickHouse的Parquet读取参数 #

通过配置参数让ClickHouse严格按列名匹配而非位置匹配，避免列顺序错位导致的类型错误：

-- 开启按列名匹配Parquet列
SET input_format_parquet_use_column_names = 1;
-- 允许Parquet与表之间存在缺失列（如果有需要）
SET input_format_parquet_allow_missing_columns = 1;

-- 再执行原INSERT语句
INSERT INTO general_marts.gm_order
SELECT *
FROM s3(
'http://host:9000/general-marts/general_mart_order/year=2024/month=02/day=12/*.snappy.parquet',
'aws_access_key_id',
'aws_secret_access_key','Parquet');

3. 修正Spark写入Parquet的类型映射 #

从根源上确保Spark写出的Parquet类型与ClickHouse表结构一致：

• 对于日期类型，调整Spark写入格式，让ClickHouse能正确识别：

df.write
  .option("parquet.writeLegacyFormat", false) // 禁用旧格式，使用标准Parquet类型
  .option("timestampFormat", "yyyy-MM-dd'T'HH:mm:ss.SSS")
  .partitionBy("year", "month", "day")
  .parquet("s3a://general-marts/general_mart_order/")

• 确保Spark中的Nullable列正确标记，避免写出时丢失Nullability信息。

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