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, Dec 27 '18 at 12:49
https://www.google.com/amp/s/www.sqlchick.com/entries/2017/9/4/querying-multi-structured-json-files-with-u-sql-in-azure-data-lake%3fformat=amp. — Amjath Khan, Dec 27 '18 at 12:57
https://www.taygan.co/blog/2018/01/06/azure-data-lake-series-working-with-json-part-1 I have read all three parts.. https://social.msdn.microsoft.com/Forums/azure/en-US/f52be907-e5f9-4163-b400-0849480f9ff5/is-there-a-way-to-query-json-files-directly-from-data-lake?forum=AzureDataLake — Amjath Khan, Dec 27 '18 at 13:02

Peter Bons · Answer 1 · 2018-12-27T20:32:29.137

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 Answers1