How to upload JSON file to Azure Data Lake table -
i newbie adl & json files. trying load json file adl table.
my json file structure is
{abcd:{time:"", date:"", processingtime:"", processname:""}}, {abcd:{date:"", processingtime:"", processname:""}}, {abcd:{processingtime:"", processname:""}}, {abcd:{time:"", date:"", processingtime:"", processname:""}},
my table has 4 columns (time, data, processingtime, & processname).
first, tried writing csv file using usql statements before writing table. but, csv output got generated blank records.
any appreciated. can through adf well? have scheduled job.
below usql code used write csv file.
create assembly if not exists [newtonsoft.json] "c:/test/assemblies/newtonsoft.json.dll"; create assembly if not exists [microsoft.analytics.samples.formats] "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 time string, date string, processingtime string, processname string @input using new jsonextractor(); output @jsonfile @to using outputters.csv();
cheers!
that file not contain valid json document. seems json object per line. adl can handle json files object per line each json object should written on new line without additional separators should remove ,
@ end of each line. this:
{"abcd":{"time":"", "date":"", "processingtime":"", "processname":""}} {"abcd":{"date":"", "processingtime":"", "processname":""}} {"abcd":{"processingtime":"", "processname":""}} {"abcd":{"time":"", "date":"", "processingtime":"", "processname":""}}
then cannot use jsonextractor directly, have use text extractor extract separate json lines , use jsontuple method convert json:
create assembly if not exists [newtonsoft.json] "c:/test/assemblies/newtonsoft.json.dll"; create assembly if not exists [microsoft.analytics.samples.formats] "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"; @rawextract = extract [rawstring] string @input using extractors.text(delimiter:'\b', quoting : false); @parsedjsonlines = select jsonfunctions.jsontuple([rawstring]) jsonline @rawextract; @jsonobjects = select jsonfunctions.jsontuple(jsonline["abcd"]) abcd @parsedjsonlines; @result = select abcd["time"] time, abcd["date"] date, abcd["processingtime"] processingtime, abcd["processname"] processname @jsonobjects; output @result @to using outputters.csv();
Comments
Post a Comment