如何在ClickHouse导入JSON时为数据指定未传入的partnerID字段值

我需要将合作伙伴发送的JSON数据（不含partnerID字段）导入ClickHouse的customers表，要求给每条数据统一分配一个固定的partnerID值（例如100），后续将通过该字段查询数据。以下是相关的建表、插入、查询语句及可行解决方案：

建表语句 #

CREATE TABLE IF NOT EXISTS customers (
    uuid UUID DEFAULT generateUUIDv4(),
    partnerID Int32,
    userID String,
    externalID String,
    fullName String,
    firstName String,
    lastName String,
    birthday Date,
    gender UInt8,
    phoneNumber Int64,
    email String,
    isEmailVerified UInt8,
    timestamp DateTime DEFAULT now(),
    firstInteractionDate Date,
    createdAt DateTime,
    updatedAt DateTime,
    defaultAddress String
) ENGINE = AggregatingMergeTree()
PARTITION BY partnerID
ORDER BY (phoneNumber, email, externalID, userID);

可行解决方法 #

方法1：插入时指定固定partnerID，配合JSONEachRow格式 #

通过明确指定插入字段列表，将partnerID设为固定值，其余字段从JSON中读取。使用JSONEachRow格式更适合处理多条JSON对象：

SET input_format_json_read_objects_as_strings = 1;

INSERT INTO customers (partnerID, userID, externalID, fullName, firstName, lastName, birthday, gender, phoneNumber, email, isEmailVerified, firstInteractionDate, createdAt, updatedAt, defaultAddress)
SELECT 
    100 AS partnerID,
    userID,
    externalID,
    fullName,
    firstName,
    lastName,
    birthday,
    gender,
    phoneNumber,
    email,
    isEmailVerified,
    firstInteractionDate,
    createdAt,
    updatedAt, -- 修正原JSON中的拼写错误（原updateAt改为updatedAt，与表字段一致）
    defaultAddress
FORMAT JSONEachRow
{"userID":"3252345664645454","externalID":"160768","fullName":"Крейг Газовский","firstName":"Крейг","lastName":"Газовский","birthday":"2001-12-20","gender":"1","phoneNumber":"79407496606","email":"79483376098@mail.net","isEmailVerified":true,"createdAt":"2020-01-01","firstInteractionDate":"2020-01-01","updatedAt":"2024-02-15","defaultAddress":{"city":"Воронеж","address":"Ленина, дом 5, квартира 10"}},
{"userID":"1265875664667599","externalID":"600090","fullName":"Лена Иванова","firstName":"Лена","lastName":"Иванова","birthday":"1975-10-13","gender":"2","phoneNumber":"79415436832","email":"79415436832@mail.net","isEmailVerified":true,"createdAt":"2023-12-24","firstInteractionDate":"2023-12-24","updatedAt":"2024-02-15","defaultAddress":{"city":"Москва","address":"Разина, дом 75, квартира 178"}};

方法2：临时设置partnerID默认值（适合单批次固定值导入） #

如果当前批次所有数据都使用同一个partnerID，可以临时修改表字段的默认值，导入完成后再恢复：

-- 修改partnerID默认值为100
ALTER TABLE customers MODIFY COLUMN partnerID Int32 DEFAULT 100;

-- 导入数据，无需手动指定partnerID
SET input_format_json_read_objects_as_strings = 1;
INSERT INTO customers FORMAT JSONEachRow
{"userID":"3252345664645454","externalID":"160768",...}; -- 替换为完整JSON数据

-- 恢复原字段设置（移除默认值）
ALTER TABLE customers MODIFY COLUMN partnerID Int32;

方法3：解析JSON字符串后插入 #

如果JSON是以字符串形式传入，可以通过JSONExtractArrayRaw拆分数据，再添加固定partnerID：

SET input_format_json_read_objects_as_strings = 1;

INSERT INTO customers
SELECT
    generateUUIDv4() AS uuid,
    100 AS partnerID,
    JSONExtractString(data, 'userID') AS userID,
    JSONExtractString(data, 'externalID') AS externalID,
    JSONExtractString(data, 'fullName') AS fullName,
    JSONExtractString(data, 'firstName') AS firstName,
    JSONExtractString(data, 'lastName') AS lastName,
    JSONExtractString(data, 'birthday') AS birthday,
    toUInt8(JSONExtractString(data, 'gender')) AS gender,
    toInt64(JSONExtractString(data, 'phoneNumber')) AS phoneNumber,
    JSONExtractString(data, 'email') AS email,
    toUInt8(JSONExtractBool(data, 'isEmailVerified')) AS isEmailVerified,
    now() AS timestamp,
    JSONExtractString(data, 'firstInteractionDate') AS firstInteractionDate,
    JSONExtractString(data, 'createdAt') AS createdAt,
    JSONExtractString(data, 'updatedAt') AS updatedAt,
    JSONExtractRaw(data, 'defaultAddress') AS defaultAddress
FROM
    (SELECT arrayJoin(JSONExtractArrayRaw('[{"userID":"3252345664645454",...}]')) AS data); -- 替换为完整JSON数组字符串

查询示例 #

-- 查询指定partnerID的所有数据
SELECT *
FROM customers 
WHERE partnerID = 100
-- 可选：筛选defaultAddress中的指定城市
-- WHERE partnerID = 100 AND JSONExtractString(defaultAddress, 'city') = 'Москва'
FORMAT Vertical;

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