locked
U-SQL script parameters RRS feed

  • Question

  • i asked a question a few days ago over in the azure data lake forum and need some clarification.

    When using ADF with U-SQL actions, does it overwrite any DECLARE statements in the specified u-sql script?

    Here's the question I asked:

    https://social.msdn.microsoft.com/Forums/azure/en-US/1cbae39c-7a1c-45ac-a66f-adeb3ac66403/parameterizing-usql-scripts?forum=AzureDataLake

    thanks,

    matt

    Saturday, November 21, 2015 3:39 AM

Answers

  • While Gaurav's answer does point to the documentation, it doesn't answer the actual question posed.

    The question is whether data factory will simply add the parameters as a DECLARE statement or will data factory update an existing DECLARE statement in the script.

    The answer is that data factory will simply add the parameter as a DECLARE statement.  

    If you already have a DECLARE statement with the same name as the parameter already in the script, your script will not compile when run in ADLA.

    • Marked as answer by blueawning Thursday, March 10, 2016 12:32 AM
    Wednesday, March 9, 2016 2:13 PM

All replies

  • As far as I can tell it does not overwrite any declares but instead adds to the front of your u-sql script. It will add (amongst over stuff) one declare statement for each input parameter.

    I think you need to make sure that this additional parameter does not conflict with anything already in your script and that it is "syntactically correct" i.e. does not cause an error (the one that got me was an empty string)

    I am trying to workout how to get parameters from the pipeline into a u-sql script but at the moment the Declare that is added appears to only handle static value i.e. it is not expanded in any way... hope I am wrong...?

    Simon

    Tuesday, March 8, 2016 4:43 PM
  • We allow customers to pass parameters to U-SQL scripts..

     

    See here: https://azure.microsoft.com/en-us/documentation/articles/data-factory-usql-activity/

     

    Search for ‘parameters’ in this article on how we are passing parameters to a U-SQL script.

     

    Customers can also use ‘Text.Format’ environment variables and customize these variables for a particular date time in the variable values..

    Tuesday, March 8, 2016 8:00 PM
  • If @in and @out are declared in the script, they will be overwritten with the values and won’t be declared twice and cause an error.  Have a look at the below script. The @in and @out will just be replaced with the values that I will be passing in the ‘parameters’ section and I don’t need to declare those variables. For your case, just use @date in your script and pass the value that you want it to take in the ‘parameters’ section. ADF will automatically replace the parameter with its value..

    @searchlog =

        EXTRACT UserId          int,

                Start           DateTime,

                Region          string,

                Query           string,

                Duration        int?,

                Urls            string,

                ClickedUrls     string

        FROM @in

        USING Extractors.Tsv(nullEscape:"#NULL#");

    @rs1 =

        SELECT Start, Region, Duration

        FROM @searchlog

    WHERE Region == "en-gb";

    @rs1 =

        SELECT Start, Region, Duration

        FROM @rs1

        WHERE Start <= DateTime.Parse("2012/02/19");

    OUTPUT @rs1  

        TO @out

          USING Outputters.Tsv(quoting:false, dateTimeFormat:null);

    Tuesday, March 8, 2016 8:01 PM
  • My query is slightly different.

    I get all the parameter passing, but can the value being passed be formed dynamically from the activity.

    For example can I do

              "parameters": {

                       "in": "{SliceStart}"

              }

    or something similar to get information on the slice being process to be passed into the u-sql script...

    Thanks

    Wednesday, March 9, 2016 1:47 AM
  • Yes, you can. I'm passing in SliceStart like this for one of my scripts:

    "parameters": {
       "date": "$$Text.Format('{0:yyyy}-{0:MM}-{0:dd} {0:HH}:00:00',SliceStart)"
     }

    For example, when my script ran yesterday, I see this the top of my script:

    DECLARE @date string = "2016-03-08 13:00:00";

    Wednesday, March 9, 2016 2:06 PM
  • While Gaurav's answer does point to the documentation, it doesn't answer the actual question posed.

    The question is whether data factory will simply add the parameters as a DECLARE statement or will data factory update an existing DECLARE statement in the script.

    The answer is that data factory will simply add the parameter as a DECLARE statement.  

    If you already have a DECLARE statement with the same name as the parameter already in the script, your script will not compile when run in ADLA.

    • Marked as answer by blueawning Thursday, March 10, 2016 12:32 AM
    Wednesday, March 9, 2016 2:13 PM