How read line separated json file from azure data lake and query using usql

Question

I have ioT data in azure datalake structure as {date}/{month}/{day}/abbs. Json Each file has multiple records separated by new line .. How to read this data using usql and load into table and query.

When I load it in usql table using ////.json will that load data into same table when new files added to files.

I have followed qzure docs but did not find any answer to line separated json file.

What azure docs have you read? And what is starting the u-sql query (so we can answer second part) — Peter Bons
taygan.co/blog/2018/01/06/… I have read all three parts.. social.msdn.microsoft.com/Forums/azure/en-US/… — Amjath Khan

Peter Bons Peter Bons · Accepted Answer · 2018-12-27T19:29:04

In this example we will create a table to store events:

CREATE TABLE dbo.Events  
(
     Event string
    ,INDEX cIX_EVENT CLUSTERED(Event ASC) DISTRIBUTED BY HASH(Event)
);

Then when it comes to extracting the json and inserting it into the database:

You first have to extract the lines using a simple text extractor, then you can parse it. For example, give a file with json objects separated with new lines

{ "Event": "One" }
{ "Event": "Tow" }
{ "Event":  "Three"}

then this script will extract the events:

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

USING Microsoft.Analytics.Samples.Formats.Json;

@RawExtract = EXTRACT [RawString] string
    FROM @input
    USING Extractors.Text(delimiter:'\b', quoting : false);

@ParsedJSONLines = SELECT JsonFunctions.JsonTuple([RawString]) AS JSONLine
    FROM @RawExtract;

INSERT INTO Events  
SELECT JSONLine["Event"] AS Event
FROM @ParsedJSONLines;

Later on you can read from the table like this:

@result =
    SELECT Event
    FROM Events;

OUTPUT @result
TO @output
USING Outputters.Csv(outputHeader : true, quoting : true);

Now, since it is an INSERT IMTO data will be appended to the table.

Resources:
GitHub Examples

More GitHub Examples

How read line separated json file from azure data lake and query using usql

1 Answers