如何通过USQL自定义输出器将Data Lake的JSON数据写入Azure SQL数据库？

嘿，我来帮你梳理下这个场景的解决方案～其实U-SQL完全支持直接将解析后的JSON数据写入外部SQL Server数据库，大部分常规场景下不需要自定义输出器，下面是具体的步骤和注意事项：

1. 先在U-SQL中解析JSON文件 #

首先你需要把Data Lake里的JSON文本转换成结构化数据，U-SQL内置了JsonFunctions工具类来处理这个需求，分两种常见情况：

情况1：每行一个JSON对象（最常见的格式） #

如果你的JSON文件是每行存储一个独立的JSON对象，比如：

{"id":1,"product":"Laptop","price":999}
{"id":2,"product":"Phone","price":699}

可以用如下代码解析：

-- 先读取原始JSON文本行
@rawJson =
    EXTRACT rawLine string
    FROM "/your-data-lake-path/your-file.json"
    USING Extractors.Text(delimiter: '\n');

-- 解析成结构化数据
@structuredData =
    SELECT
        JsonFunctions.JsonTuple(rawLine)["id"] AS Id,
        JsonFunctions.JsonTuple(rawLine)["product"] AS ProductName,
        Convert.ToInt32(JsonFunctions.JsonTuple(rawLine)["price"]) AS Price
    FROM @rawJson;

情况2：JSON数组格式（整个文件是一个大数组） #

如果你的JSON是数组格式，比如：

[{"id":1,"product":"Laptop"},{"id":2,"product":"Phone"}]

可以直接用Extractors.Json指定JSON路径来解析：

@structuredData =
    EXTRACT Id int, ProductName string, Price int
    FROM "/your-data-lake-path/array-file.json"
    USING Extractors.Json(jsonPath: "$[*]"); // $[*]表示数组中的每个元素

2. 连接SQL Server并写入数据 #

U-SQL内置了Outputters.SqlServer输出器，可以直接将结构化数据写入外部SQL Server，步骤如下：

第一步：创建访问SQL Server的凭据 #

你需要先在ADLA账户中创建存储SQL Server账号密码的凭据，避免明文暴露：

CREATE CREDENTIAL IF NOT EXISTS SqlServerAuth
WITH IDENTITY = 'your-sql-username',
SECRET = 'your-sql-password';

第二步：执行写入操作 #

用OUTPUT语句把解析好的数据写入目标表：

OUTPUT @structuredData
TO "sqlserver://your-sql-server-name.database.windows.net;database=your-db-name;table=TargetTable"
USING Outputters.SqlServer(
    credentialName: "SqlServerAuth",
    batchSize: 2000, -- 可选，调整批量写入的大小提升性能
    tableOption: Outputters.SqlServer.TableOption.CreateIfNotExist -- 可选，自动创建表（如果不存在）
);

3. 什么时候需要自定义输出器？ #

只有当你遇到以下特殊场景时，才需要考虑开发自定义U-SQL输出器：

• 处理极度复杂的嵌套JSON结构，内置JsonFunctions无法满足解析需求，需要自定义逻辑处理数据
• 对写入SQL Server的过程有高度定制化需求（比如特殊的字段映射、复杂的数据转换规则、自定义错误处理逻辑）
• 内置SqlServer输出器不支持的特殊数据类型转换

额外注意事项 #

• 确保你的SQL Server防火墙允许ADLA账户的IP访问，或者开启"允许Azure服务和资源访问此服务器"选项
• 测试阶段建议先用小批量数据验证逻辑，避免因数据格式问题导致整个作业失败
• 确保ADLA账户有足够的权限访问Data Lake Store和目标SQL Server

内容的提问来源于stack exchange，提问作者Alejandro B.