Azure Synapse无服务器SQL池查询JSON文件过滤日志及时间范围

你当前读取返回的doc字段是Blob中存储的完整JSON文本，需要通过内置JSON函数解析对应字段后做筛选，可直接运行以下查询：

SELECT
    -- 可按需调整返回字段，不需要解析单独字段的话直接替换为 * 或者 doc 即可
    JSON_VALUE(doc, '$.time') AS log_time,
    JSON_VALUE(doc, '$.operationName') AS operation_name,
    JSON_VALUE(doc, '$.properties.pod') AS source_pod,
    JSON_VALUE(doc, '$.properties.stream') AS log_stream,
    JSON_VALUE(doc, '$.properties.log') AS kube_audit_log
FROM
    OPENROWSET(
        BULK 'https://azdevogs.blob.core.windows.net/insights-logs-kube-audit/resourceId=/SUBSCRIPTIONS/533AEB/RESOURCEGROUPS/AZURE-TEST/PROVIDERS/MICROSOFT.CONTAINERSERVICE/MANAGEDCLUSTERS/AZURE-TEST/y=2022/m=05/d=23/h={13,14,15,16,17}/m=*/PT1H.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) AS raw_logs
WHERE
    -- 匹配指定时间范围，使用ISO8601格式转换避免时区/格式报错
    TRY_CONVERT(DATETIME2, JSON_VALUE(doc, '$.time'), 127) 
        BETWEEN '2022-05-23T13:45:13.0000000Z' AND '2022-05-23T17:45:13.0000000Z'
    -- 排除包含指定服务账号标识的日志
    AND JSON_VALUE(doc, '$.properties.log') NOT LIKE '%system:serviceaccount:internal-services:spinnaker%'
    AND JSON_VALUE(doc, '$.properties.log') NOT LIKE '%system:serviceaccounts:internal-services%'
GO

关键说明 #

• 路径通配符：BULK路径中使用{13,14,15,16,17}匹配13点到17点的小时分区、m=*匹配对应小时下所有分钟分片，覆盖要求的完整时间范围；如果仅需要查询原语句中的单小时分片，直接替换为原来的文件路径即可。
• 时间转换：使用TRY_CONVERT搭配127格式码（ISO8601标准时间格式）做转换，个别脏数据格式异常时会返回NULL而非直接中断查询。
• 日志匹配：你提到的logs字段实际对应JSON结构中properties.log嵌套字段，该字段本身是转义存储的K8s审计日志文本，不需要二次解析JSON，直接用字符串匹配判断排除内容即可，查询性能更高。
• 如果需要返回原始完整JSON内容，直接把SELECT后的字段列表替换为doc即可。

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