Azure Data Lake Loop

Question

Does Azure Data Lake Analytics and U-SQL support use While or For to Loop and create multiple outputs? I want output to multiple files using one USQL execution.

This is what i want:

Foreach @day in @days
    @dataToSave = 
        SELECT    day AS day,
                  company AS Company,      
        FROM @data
        WHERE @day = @day

    @out = @day + ".txt"

    OUTPUT @dataToSave
    TO @out
    USING Outputters.Text();
Next

I know i can use a powershell, but i think that will cost performance prepairing the execution.

Answer 1

U-SQL does not support While or For loops. You can use WHERE statements to filter extracted data, and virtual columns to filter based on file paths/names (example).

To output to multiple files, you can write a unique rowset and WHERE clause for each output if its a reasonable number of files.

As you said, you could also script this with Powershell or U-SQL (example).

Dynamic output to multiple files is currently in a limited private preview. Please send an email to usql at microsoft dot com with your scenario if you're interested in this feature, as it could work for your scenario based on your description.

Hope this helps, and let me know if you have more questions about implementing any of these solutions.

Answer 2

You can try create a custom outputter and ignore the output file and write on your own file! public override void Output (IRow row, IUnstructuredWriter output)

Answer 3

Try this, using outputter too:

public override void Output(IRow input, IUnstructuredWriter output)
    {
       using (System.IO.StreamWriter streamWriter = new StreamWriter(address + _file, true))
    //Save on file!
    }