none
Load Data Analytics table with JSON as source

    Question

  • Hi,

    I am a newbie to Azure Data Lake & Analytics and USQL. I am trying to load a table in Azure Data Lake with data from Json. I need to have this as a re-usable job as well.

    Have a Json file name Test which has the structure like

    Test.Json

    {"Order": {"ID":"ABC", "Date":"11/11/2016"}}

    {"Order": {"ID":"DEF", "Date":"11/11/2016"}}

    Now I need to load it into the Data Lake table with table structure as below:

    Table Name: Orders

    ID    Date

    ABC 11/11/2016

    DEF 11/11/2016

    Any guidance/suggestions/help is much appreciated.

    Cheers!

    Monday, August 14, 2017 3:12 AM

All replies

  • I would check out the github project. https://github.com/Azure/usql/tree/master/Examples/DataFormats/Microsoft.Analytics.Samples.Formats


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, August 14, 2017 1:52 PM
  • Thanks, Russel.

    Tried following that link and did write some <g class="gr_ gr_85 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="85" id="85">USQL</g>.

    I have comma between the objects like below

    {"Order": {"ID":"ABC", "Date":"11/11/2016"}}, {"Order": {"ID":"DEF", "Date":"11/11/2016"}}

    I wrote a USQL as shown below, however it generates an empty CSV file. 

    CREATE ASSEMBLY IF NOT EXISTS [Newtonsoft.Json] FROM 
    "C:/Test/Assemblies/Newtonsoft.Json.dll";
    CREATE ASSEMBLY IF NOT EXISTS [Microsoft.Analytics.Samples.Formats] FROM 
    "C:/ADL/Assemblies/Microsoft.Analytics.Samples.Formats.dll";
    
    REFERENCE ASSEMBLY [Newtonsoft.Json];
    REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];
    
    USING Microsoft.Analytics.Samples.Formats.Json;
    
    DECLARE @path string = @"C:\Test\";
    DECLARE @input string = @path + @"sample_data1.json";
    DECLARE @to string = @path + @"output.csv";
    
    @jsonFile =
     EXTRACT
    ID string,
    Date string,
    FROM @input
    USING new JsonExtractor();
    
    OUTPUT @jsonFile
    TO @to
    USING Outputters.Csv();


    Any help is much appreciated.

    Cheers!

    

    Tuesday, August 15, 2017 11:17 PM
  • The ID and Date fields are contained in the Order object. I have used only the JsonTuple UDF function, and not the JsonExtractor. But I believe that the trick is to pass the JSON selector as follows:

    USING new Microsoft.Analytics.Samples.Formats.Json.JsonExtractor("Order");


    Wednesday, September 6, 2017 7:32 PM