U-SQL foreach?


  • The task at hand is to produce full resolution store transactions by retailer.
    We are inserting in the Data Lake the transactions using ASA and once a day we would like to have a daily view with all the transactions per retailer. This is not a report but needs to feed an interactive portal.
    The U-SQL script so far is capable to scrub and produce a daily file (or it can insert to the U-Database).
    How can I do this in U-SQL:
    //this runs daily

    @transactions =

    SELECT store, retailer, product, value FROM "stag/redeem/logs/{v_date:yyyy}/{v_date:MM}/{v_date:dd}/{*}.csv"
        USING Extractors.Text(delimiter : '|', skipFirstNRows : 1)

    @retailers = SELECT DISTINCT(retailer) FROM @transactions

    foreach(var curRetailer in @retailers)
     @retTransactions = SELECT * FROM @transactions
     WHERE retailer = curRetailer

     OUTPUT @retTransactions
     TO "output/{curRetailer}/transactions.csv"
     USING Outputters.Text(delimiter : '|', dateTimeFormat : "s");

    Please guide me with 2 issues:

    1) How to iterate the list of retailers?

    2) How do I partition the output to use curRetailer?



    • Edited by Uri Kluk Thursday, December 15, 2016 2:24 PM
    Thursday, December 15, 2016 2:21 PM

All replies