How can we improve Microsoft Azure Data Lake?

Allow rowset variables to be converted to scalar values in USQL

I'm looking to make an output path string out of dates that are in an event-level table, so that the aggregate results can be saved and partitioned into paths according to their date.

It appears that when I'm selecting a date from a rowset, it cannot be converted into a scalar string and used as an output path..

38 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Phil shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

7 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • sainath commented  ·   ·  Flag as inappropriate

    I am working on below logic and unable to get this could any one help on this
    @maxid=select max(id) from tablename;
    @ds=Select row_number() over (order by 1 desc)+@maxid, other columns FROM tablename2;

  • William Blakey commented  ·   ·  Flag as inappropriate

    I need to derive an @outfile variable to include a scalar value contained within the data file I am reading/extracting. I am aware of Fileset support and the ability to decompose names and include them in the dataset, however we have data files that I need to name to identify the set.
    Similar to this:
    @maxDt =
    SELECT MIN(OrderDate) AS minDt FROM @myInFile;

    DECLARE @minDtFilename string = "/output/orders/input_" + @maxDt + ".csv";
    ...
    OUTPUT ...
    TO @minDtFilename

    Thanks.

  • Anonymous commented  ·   ·  Flag as inappropriate

    I have a case where I want to have a cleanup script only run if a certain amount of rows are in need of cleanup, so it would benefit me to be able to turn an integer from count() into a scalar value to compare to a minimum threshold.

  • Harpalsinh Rana commented  ·   ·  Flag as inappropriate

    Yea, we have a case where we want to keep some commonly used data in single file and use it in multiple u-sql scripts.
    So while deploying on azure pipeline we do not need to maintain long parameter list but a single file path.

  • Lalit Rana commented  ·   ·  Flag as inappropriate

    Would be great to see scalar values from file or rowset, it would enable data driven decision scenarios at run time within U-SQL jobs .

    DECLARE @scalar string = SELECT TOP 1 <something> FROM [<file> USING Extractor.csv()] | [@rowset];

Feedback and Knowledge Base