CODEWITHSUNDEEP
u-sql
Bond
Bond

Reputation: 121

How to implement Loops in U-SQL

Is is possible to implement Loops (while/for) in U-SQL without using C#. If no, can anyone share the c# syntax to implement loops in u-sql.

I am extracting files from a particular date to a date, but right now I am extracting this by writing file path manually.

DROP VIEW IF EXISTS dbo.ReadingConsolidated;  
CREATE VIEW IF NOT EXISTS dbo.ReadingConsolidated 
AS

EXTRACT     
        ControllerID int?,          
        sensorID int?,
        MeasureDate DateTime,
        Value float

FROM  
"adl://datalake.azuredatalakestore.net/2015/7/1/Reading.csv",
"adl://datalake.azuredatalakestore.net/2015/7/2/Reading.csv",
"adl://datalake.azuredatalakestore.net/2015/7/3/Reading.csv",
"adl://datalake.azuredatalakestore.net/2015/7/4/Reading.csv",
"adl://datalake.azuredatalakestore.net/2015/7/5/Reading.csv",
"adl://datalake.azuredatalakestore.net/2015/7/6/Reading.csv",
"adl://datalake.azuredatalakestore.net/2015/7/7/Reading.csv"

Note: these files are present in different folder.

Can above thing is possible using loop?

Upvotes: 1

Views: 1860

Answers (1)

Alexandre Gattiker
Alexandre Gattiker

Reputation: 749

The proper way to do this is to use virtual columns, then rely on partition elimination so that only files matching predicates will actually be read (you can confirm that in the job graph).

CREATE VIEW IF NOT EXISTS dbo.ReadingConsolidated 
AS

EXTRACT     
        ControllerID int?,          
        ParameterID int?,
        MeasureDate DateTime,
        Value float,
        date DateTime
    FROM  
    "adl://datalake.azuredatalakestore.net/{date:yyyy}/{date:M}/{date:d}/Reading.csv";


@res =
    SELECT * FROM dbo.ReadingConsolidated
    WHERE date BETWEEN DateTime.Parse("2015/07/01") AND DateTime.Parse("2016/07/07");

Upvotes: 2

Related Questions