Load Data Analytics table with JSON as source


  • 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


    {"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.


    Monday, August 14, 2017 3:12 AM

All replies

  • I would check out the github project.

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

    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 [Microsoft.Analytics.Samples.Formats] FROM 
    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 =
    ID string,
    Date string,
    FROM @input
    USING new JsonExtractor();
    OUTPUT @jsonFile
    TO @to
    USING Outputters.Csv();

    Any help is much appreciated.



    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