none
Storing Result from Code Behind in Variable

    Question

  • Hi,

    I'm trying to make a dynamic input file name by using a code behind file. The file path consists of : "folder/year/month/day_gibberish". I would like to run the U-SQL Script every day such that it operates on the file from the previous day. However, when I try to store the result from my code behind function in a variable, it says that the name is not defined in the current context. Here is the snippet from the script:

    DECLARE @in string = Paths.PathMaker.PrevDatePath("ProtoType");

    Here is the code behind file:

    using Microsoft.Analytics.Interfaces;
    using Microsoft.Analytics.Types.Sql;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace Paths
    {
        public class PathMaker
        {
            static public string PrevDatePath(string FilePath)
            {
                DateTime yesterDayDate = DateTime.UtcNow.AddDays(-1);
                string yesterDayDay = "/" + yesterDayDate.ToString("dd");
                string yesterDayYear = "/" + yesterDayDate.ToString("yyyy");
                string yesterDayMonth = "/" + yesterDayDate.ToString("MM");

                string output = FilePath + yesterDayYear + yesterDayMonth + yesterDayDay + "{*}.json";
                return output;
            }
        }
    }
       

    Is this functionality not supported? If so, how can I accomplish this task?

    Thank you.

    Thursday, July 21, 2016 6:05 PM

Answers

  • Input paths for EXTRACT statements have to be known at compile time. since your code-behind function is not constant foldable, you cannot do it in this way.

    The good news is that you should be able to use file set patterns with a predicate to achieve your goal (note this is MSDN Forum code and has not been tested):

    DECLARE @in string = @"/ProtoType/{date:yyyy}/{date:MM}/{date:dd}_{*}.json";

    @data = EXTRACT ... , date DateTime FROM @in USING ...;

    @data = SELECT * FROM @data WHERE date == DateTime.UtcNow.AddDays(-1); // you may need to restrict it to day resolution

    This should give you only the files for the specified date.


    Michael Rys

    • Marked as answer by Pfav Friday, July 22, 2016 5:20 PM
    Thursday, July 21, 2016 10:45 PM
    Moderator