Parsing JSON file using U-SQL

Question

Parsing JSON file using U-SQL

Can anyone assist with parsing this Json file using USQL? I keep encountering errors.

Json file@

{"dimBetType_SKey":1,"BetType_BKey":1,"BetTypeName":"Test1"}
{"dimBetType_SKey":2,"BetType_BKey":2,"BetTypeName":"Test2"}
{"dimBetType_SKey":3,"BetType_BKey":3,"BetTypeName":"Test3"}

Here is the USQL script I'm using to extract data from the above file.

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

DECLARE @Full_Path string =
"adl://xxxx.azuredatalakestore.net/2017/03/28/00_0_66ffdd26541742fab57139e95080e704.json";

DECLARE @Output_Path = "adl://xxxx.azuredatalakestore.net/Output/Output.csv";

@logSchema =
EXTRACT dimBetType_SKey int
FROM @Full_Path
USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor();

OUTPUT @logSchema
TO @Output_Path 
USING Outputters.Csv();

The USQL script keeps failing with a Vertex error.

Any assistance would be greatly appreciated!

json azure azure-data-factory azure-data-lake u-sql

Answer 1

Answer №1

If you're encountering issues with parsing JSON blocks on each new line of a file, it may require a different approach than treating it as a standard JSON file.

One way to handle this is by using a text extractor initially to extract each JSON element with a newline delimiter. For example, you could do something like this...

DECLARE @Full_Path string = "etc"

@RawExtract = 
    EXTRACT 
        [RawString] string, 
        [FileName] string //optional, see below
    FROM
        @Full_Path
    USING 
        Extractors.Text(delimiter:'\b', quoting : false);

Once you have extracted the raw JSON strings, you can then parse them using the appropriate assembly and JSON tuple method. Here's an example...

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

@ParsedJSONLines = 
    SELECT 
        Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple([RawString]) AS JSONLine,
        [FileName]
    FROM 
        @RawExtract

After parsing the JSON lines, you can proceed to retrieve specific values. Here's how you can do it...

@StagedData =
    SELECT 
        JSONLine["dimBetType_SKey"] AS dimBetType_SKey,
        JSONLine["BetType_BKey"] AS BetType_BKey,
        JSONLine["BetTypeName"] AS BetTypeName
        [FileName]
    FROM 
        @ParsedJSONLines;

Finally, once you have processed the data, you can output it to CSV or any other desired format.

DECLARE @Output_Path string = "etc"

OUTPUT @StagedData
TO @Output_Path 
USING Outputters.Csv();

Additionally, you may not need to specify the complete data lake store path. The analytics engine can determine the root storage location, so you can simplify your variables like this...

DECLARE @Full_Path string = "/2017/03/28/{FileName}";

I hope these suggestions help resolve your issue.

Answer 2

If you're encountering issues with parsing JSON blocks on each new line of a file, it may require a different approach than treating it as a standard JSON file.

One way to handle this is by using a text extractor initially to extract each JSON element with a newline delimiter. For example, you could do something like this...

DECLARE @Full_Path string = "etc"

@RawExtract = 
    EXTRACT 
        [RawString] string, 
        [FileName] string //optional, see below
    FROM
        @Full_Path
    USING 
        Extractors.Text(delimiter:'\b', quoting : false);

Once you have extracted the raw JSON strings, you can then parse them using the appropriate assembly and JSON tuple method. Here's an example...

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

@ParsedJSONLines = 
    SELECT 
        Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple([RawString]) AS JSONLine,
        [FileName]
    FROM 
        @RawExtract

After parsing the JSON lines, you can proceed to retrieve specific values. Here's how you can do it...

@StagedData =
    SELECT 
        JSONLine["dimBetType_SKey"] AS dimBetType_SKey,
        JSONLine["BetType_BKey"] AS BetType_BKey,
        JSONLine["BetTypeName"] AS BetTypeName
        [FileName]
    FROM 
        @ParsedJSONLines;

Finally, once you have processed the data, you can output it to CSV or any other desired format.

DECLARE @Output_Path string = "etc"

OUTPUT @StagedData
TO @Output_Path 
USING Outputters.Csv();

Additionally, you may not need to specify the complete data lake store path. The analytics engine can determine the root storage location, so you can simplify your variables like this...

DECLARE @Full_Path string = "/2017/03/28/{FileName}";

I hope these suggestions help resolve your issue.

Answer 3

Answer №2

If you're looking to convert JSON (JSON Format) to CSV (Text Format), ADF can make the process easy for you. Detailed instructions can be found at: https://learn.microsoft.com/en-us/azure/data-factory/data-factory-faq#specifying-formats

The copy wizard in ADF allows you to preview your data and set up pipelines using a user-friendly interface. https://learn.microsoft.com/en-us/azure/data-factory/data-factory-copy-data-wizard-tutorial

Answer 4

If you're looking to convert JSON (JSON Format) to CSV (Text Format), ADF can make the process easy for you. Detailed instructions can be found at: https://learn.microsoft.com/en-us/azure/data-factory/data-factory-faq#specifying-formats

The copy wizard in ADF allows you to preview your data and set up pipelines using a user-friendly interface. https://learn.microsoft.com/en-us/azure/data-factory/data-factory-copy-data-wizard-tutorial

Parsing JSON file using U-SQL

Answer №1

Answer №2

Similar questions

JSONP is unable to utilize data fetched from an external API

using variables as identifiers in nested JSON objects

executing jQuery toggle on freshly generated content

A guide on looping through data in a JSON-serialized System.Data.DataTable with the help of jQuery

Ways to insert user data into a hidden input field

Convert HTML code into a customized JSON structure

The jQuery call to a web service is returning XML data, but the success function is receiving a

What could be the reason for my post jQuery Ajax request sending JSON data?

What could be causing my for loop to become unresponsive?

Retrieving information via RESTful API using jQuery

Using jQuery to incorporate a variable into JSON schema markup

Extracting information from Python Bottle with the help of AJAX jQuery

How can you modify the starting point of data in jQuery flot?

Is there a way to use getJSON to append divs and animate them one by one as they are displayed?

Implementing Bootstrap 4 validation within a modal using JSON data

Error encountered while using Google Translate with XMLHttpRequest (Missing 'Access-Control-Allow-Origin' header)

Performing two API calls using Jquery to fetch distinct dynamic elements within two nested $.each loops

Is there a recent problem with the flickrAPI where the photo description is showing as undefined

Struggling to properly render JSON data

Tips for interpreting JSON information and showcasing it with the assistance of Ajax in JQuery?