azure-data-factory – U-SQL无法从JSON文件中提取数据

发布时间：2020-12-12 08:53:25 所属栏目：MsSql教程来源：网络整理

导读：我试图使用USQL从 JSON文件中提取数据.查询成功运行而不生成任何输出数据或导致“顶点失败快速错误”. JSON文件如下所示： { "results": [ { "name": "Sales/Account","id": "7367e3f2-e1a5-11e5-80e8-0933ecd4cd8c","deviceName": "HP","deviceModel": "g6-p

我试图使用USQL从 JSON文件中提取数据.查询成功运行而不生成任何输出数据或导致“顶点失败快速错误”.

JSON文件如下所示：

{
  "results": [
    {
      "name": "Sales/Account","id": "7367e3f2-e1a5-11e5-80e8-0933ecd4cd8c","deviceName": "HP","deviceModel": "g6-pavilion","clientip": "0.41.4.1"
    },{
      "name": "Sales/Account","id": "c01efba0-e0d5-11e5-ae20-af6dc1f2c036","deviceName": "acer","deviceModel": "veriton","clientip": "10.10.14.36"
    }
  ]
}

我的U-SQL脚本是

REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

DECLARE @in string="adl://xyz.azuredatalakestore.net/todelete.json";

DECLARE @out string="adl://xyz.azuredatalakestore.net/todelete.tsv";

@trail2=EXTRACT results string FROM @in USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();

@jsonify=SELECT Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(results,"name","id","deviceName","deviceModel","clientip") AS rec FROM @trail2;

@logSchema=SELECT rec["name"] AS sysName,rec["id"] AS sysId,rec["deviceName"] AS domainDeviceName,rec["deviceModel"] AS domainDeviceModel,rec["clientip"] AS domainClientIp 
       FROM @jsonify;

OUTPUT @logSchema TO @out USING Outputters.Tsv();

解决方法

实际上,JSONExtractor支持 JSONPath中表示的rowpath参数,使您能够识别要映射到行的JSON对象或JSON数组项.因此,您可以使用JSON文档中的单个语句提取数据：

@logSchema = 
    EXTRACT name string,id string,deviceName string,deviceModel string,clientip string
    FROM @input
   USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("results[*]");

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!