从ADLS路径读取嵌套JSON到SQL Server遇错求助

问题场景 #

尝试读取ADLS路径下的嵌套JSON数据，数据示例如下：

[{"id": 9, "name": "Apps Support", "email": "support@apps.com", "employee_id": "12345", "photo": "https://connect.com/ce/pulse/images/default_images/profile50-50.png", "presence_option_id": "2", "presence_string": "Offline", "active_at": "17277", "user_type": "S", "user_mention": "support", "human_mention": "Apps Support", "conversation_id": null, "has_default_photo": true, "custom_status": null, "title": "TAM", "user_status": {"status_message": null, "auto_respond_text": null, "emoji": null, "auto_respond": false, "do_not_disturb": false, "status_expire_time": null, "status_set_time": null, "clear_after": 0}, "additional_info": "TAM", "is_followee": false, "is_follower": false}]

最初使用CSV格式中转读取的代码出现查询超时异常，简化代码后仍报错：

Bulk load failed due to invalid column value in CSV data file 'path'

需求是将嵌套JSON展平后导入SQL Server作为表或视图。

解决方案 #

1. 直接使用JSON格式读取，避免CSV解析错误 #

之前通过CSV格式读取JSON文件，强制指定分隔符和引号符的方式容易触发格式错误。SQL Server的OPENROWSET支持直接读取JSON格式，无需中转：

SELECT *
FROM OPENROWSET(
    BULK 'your-adls-json-path',
    DATA_SOURCE = 'DataSource',
    FORMAT = 'JSON',
    -- 针对数组格式的JSON启用此选项
    WITH (FORMATFILE='N') 
) AS raw
CROSS APPLY OPENJSON(raw.BulkColumn)
WITH (
    id INT,
    name NVARCHAR(100),
    email NVARCHAR(100),
    employee_id NVARCHAR(50),
    photo NVARCHAR(200),
    presence_option_id NVARCHAR(10),
    presence_string NVARCHAR(50),
    active_at BIGINT,
    user_type NVARCHAR(10),
    user_mention NVARCHAR(100),
    human_mention NVARCHAR(100),
    conversation_id NVARCHAR(100),
    has_default_photo BIT,
    custom_status NVARCHAR(100),
    title NVARCHAR(100),
    additional_info NVARCHAR(100),
    is_followee BIT,
    is_follower BIT,
    -- 直接通过JSON路径解析嵌套字段，无需额外CROSS APPLY
    status_message NVARCHAR(100) '$.user_status.status_message',
    auto_respond_text NVARCHAR(100) '$.user_status.auto_respond_text',
    emoji NVARCHAR(50) '$.user_status.emoji',
    auto_respond BIT '$.user_status.auto_respond',
    do_not_disturb BIT '$.user_status.do_not_disturb',
    status_expire_time NVARCHAR(100) '$.user_status.status_expire_time',
    status_set_time NVARCHAR(100) '$.user_status.status_set_time',
    clear_after INT '$.user_status.clear_after'
) AS parsed_data

2. 优化超时问题 #

• 调整查询超时时间：执行查询前设置SET QUERY_TIMEOUT 300;（单位秒，根据文件大小调整）
• 分批读取大文件：使用OPENROWSET的RANGE参数分批加载数据，避免一次性读取全部内容
• 检查存储连接：确认ADLS存储账户与SQL Server处于同区域，保证网络传输稳定

3. 验证JSON格式完整性 #

若仍报错，先排查JSON文件本身的问题：

• 执行以下语句读取原始内容，检查是否存在语法错误：

  SELECT BulkColumn FROM OPENROWSET(BULK 'your-adls-json-path', DATA_SOURCE='DataSource', FORMAT='JSON') AS raw
  

• 确认文件中没有未转义的特殊字符（如未闭合的引号）

4. 持久化为表或视图 #

导入为表

CREATE TABLE EmployeeData (
    id INT,
    name NVARCHAR(100),
    email NVARCHAR(100),
    employee_id NVARCHAR(50),
    photo NVARCHAR(200),
    presence_option_id NVARCHAR(10),
    presence_string NVARCHAR(50),
    active_at BIGINT,
    user_type NVARCHAR(10),
    user_mention NVARCHAR(100),
    human_mention NVARCHAR(100),
    conversation_id NVARCHAR(100),
    has_default_photo BIT,
    custom_status NVARCHAR(100),
    title NVARCHAR(100),
    additional_info NVARCHAR(100),
    is_followee BIT,
    is_follower BIT,
    status_message NVARCHAR(100),
    auto_respond_text NVARCHAR(100),
    emoji NVARCHAR(50),
    auto_respond BIT,
    do_not_disturb BIT,
    status_expire_time NVARCHAR(100),
    status_set_time NVARCHAR(100),
    clear_after INT
)

INSERT INTO EmployeeData
SELECT *
FROM OPENROWSET(
    BULK 'your-adls-json-path',
    DATA_SOURCE = 'DataSource',
    FORMAT = 'JSON',
    WITH (FORMATFILE='N')
) AS raw
CROSS APPLY OPENJSON(raw.BulkColumn)
WITH (
    id INT,
    name NVARCHAR(100),
    email NVARCHAR(100),
    employee_id NVARCHAR(50),
    photo NVARCHAR(200),
    presence_option_id NVARCHAR(10),
    presence_string NVARCHAR(50),
    active_at BIGINT,
    user_type NVARCHAR(10),
    user_mention NVARCHAR(100),
    human_mention NVARCHAR(100),
    conversation_id NVARCHAR(100),
    has_default_photo BIT,
    custom_status NVARCHAR(100),
    title NVARCHAR(100),
    additional_info NVARCHAR(100),
    is_followee BIT,
    is_follower BIT,
    status_message NVARCHAR(100) '$.user_status.status_message',
    auto_respond_text NVARCHAR(100) '$.user_status.auto_respond_text',
    emoji NVARCHAR(50) '$.user_status.emoji',
    auto_respond BIT '$.user_status.auto_respond',
    do_not_disturb BIT '$.user_status.do_not_disturb',
    status_expire_time NVARCHAR(100) '$.user_status.status_expire_time',
    status_set_time NVARCHAR(100) '$.user_status.status_set_time',
    clear_after INT '$.user_status.clear_after'
) AS parsed_data

创建视图

CREATE VIEW vw_EmployeeData
AS
SELECT *
FROM OPENROWSET(
    BULK 'your-adls-json-path',
    DATA_SOURCE = 'DataSource',
    FORMAT = 'JSON',
    WITH (FORMATFILE='N')
) AS raw
CROSS APPLY OPENJSON(raw.BulkColumn)
WITH (
    id INT,
    name NVARCHAR(100),
    email NVARCHAR(100),
    employee_id NVARCHAR(50),
    photo NVARCHAR(200),
    presence_option_id NVARCHAR(10),
    presence_string NVARCHAR(50),
    active_at BIGINT,
    user_type NVARCHAR(10),
    user_mention NVARCHAR(100),
    human_mention NVARCHAR(100),
    conversation_id NVARCHAR(100),
    has_default_photo BIT,
    custom_status NVARCHAR(100),
    title NVARCHAR(100),
    additional_info NVARCHAR(100),
    is_followee BIT,
    is_follower BIT,
    status_message NVARCHAR(100) '$.user_status.status_message',
    auto_respond_text NVARCHAR(100) '$.user_status.auto_respond_text',
    emoji NVARCHAR(50) '$.user_status.emoji',
    auto_respond BIT '$.user_status.auto_respond',
    do_not_disturb BIT '$.user_status.do_not_disturb',
    status_expire_time NVARCHAR(100) '$.user_status.status_expire_time',
    status_set_time NVARCHAR(100) '$.user_status.status_set_time',
    clear_after INT '$.user_status.clear_after'
) AS parsed_data

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