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..
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
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:
SELECT MIN(OrderDate) AS minDt FROM @myInFile;
DECLARE @minDtFilename string = "/output/orders/input_" + @maxDt + ".csv";
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
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
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];
Mike R commented
Dear Phil, we are working on addressing this scenario with the feature requested here: https://feedback.azure.com/forums/327234-data-lake/suggestions/10550388-support-dynamic-output-file-names-in-adla
I will leave this request as a general request for scalar (sub) queries for now.
Sakthivel Nachimuthu commented
No Scalar value functions in USQL (Force to use rowset to join with other rowset)