U- SQL无法从JSON文件提取数据 [英] U- SQL Unable to extract data from JSON file

问题描述

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

I was trying to extract data from a JSON file using USQL. Either the query runs successfully without producing any output data or results in "vertex failed fast error".

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支持

Actually the JSONExtractor supports the rowpath parameter expressed in JSONPath that gives you the ability to identify the JSON object or JSON array items that you want to map into rows. So you can extract your data with a single statement from your JSON document:

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

这篇关于U- SQL无法从JSON文件提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！