Search code examples
azureazure-data-lakeu-sql

Can i use DateTime variable to create dynamic file name in USql Data Lake Analytics

In my Usql script, I have declared a variable as follows:

DECLARE @startTime DateTime;
DECLARE @endTime DateTime;

I have a rowset lets say @result and i want to generate the output as:

OUTPUT @result
TO "Output/{@startTime:yyyy}.{@startTime:MM}.{@startTime:DD}.RealUsage.{@endTime:yyyy}.{@endTime: MM}.{@endTime:DD}.csv"
USING Outputters.Csv(outputHeader : true, quoting : false);

How can i use a variable of DateTime Type to generate a file path?

Thanks In advance.

Solution

  • If you want to generate a single file dynamically, you can parameterize your script and create the output path statically in a variable (I added the CONST to mark that it needs to be a constant-foldable expression):

    DECLARE CONST @output = "/Output/" + @startTime.Year.ToString() + "/" + ... ;

    Then you can use it with

    OUTPUT @result
TO @output
USING ...;

    If you want to have several files generated based on data in your rowset, please wait a bit longer, or contact me at usql at microsoft dot com for access to an early private preview.