none
Parameterizing u-sql scripts

    Question

  • I was looking at the documentation for running u-sql scripts from data factory (https://azure.microsoft.com/en-us/documentation/articles/data-factory-usql-activity/)

    In there, it shows that you can pass parameters to a script.  How would you access those parameters inside the script? I haven't seen any documentation on this.

    Wednesday, November 18, 2015 6:23 PM

Answers

  • Best check with the ADF team, since they are ultimately designing how they pass the parameters. But I would expect that you should not have the DECLARE for the parameter variables.

    One suggestion I have is that you package the script into Table-valued functions and Procedures. That way you can get the types specified in the Procedure and then the script is just the invocation (plus DDL statements if any).

    For example, you define a procedure

    CREATE PROCEDURE MyProc(@param1 string, @param2 string) AS BEGIN

    @result = SELECT * FROM MyTable WHERE column == @param1;

    OUPUT @result TO @param2 USING Outputters.Csv();

    END;

    Then you can for example just write a script as

    MyProc(@p1, @p2);

    And have ADF pass the parameters. Maybe (again, please check with ADF), ADF may allow you to reference and call a procedure directly.


    Michael Rys

    • Marked as answer by blueawning Saturday, November 21, 2015 3:33 AM
    Friday, November 20, 2015 12:32 AM
    Moderator

All replies

  • U-SQL itself doesn't have a formal notion of "parameters". In this context though, if the ADF activity is putting the value of those parameters into the U-SQL script as U-SQL variables (via the DECLARE keyword) then you can access those values like any other U-SQL variable.
    Wednesday, November 18, 2015 10:09 PM
    Moderator
  • U-SQL itself doesn't have a formal notion of "parameters". In this context though, if the ADF activity is putting the value of those parameters into the U-SQL script as U-SQL variables (via the DECLARE keyword) then you can access those values like any other U-SQL variable.

    To add to this:

    If you decide to use a parameter called @myparam, you would write the script like:

    ...

    @result = SELECT * FROM MyTable WHERE column == @myparam;

    ...

    The DECLARE statement will be added by ADF for you.


    Michael Rys

    Wednesday, November 18, 2015 11:13 PM
    Moderator
  • Ok cool.

    While developing and testing the script, I would have those variables declared.

    Do I need to remove the DECLARE statements from the script when I attempt to pass them in to the script via ADF? Or will ADF "overwrite" my DECLARE statements.

    I'm guessing I would have to remove them from the script.


    Thursday, November 19, 2015 12:30 PM
  • Best check with the ADF team, since they are ultimately designing how they pass the parameters. But I would expect that you should not have the DECLARE for the parameter variables.

    One suggestion I have is that you package the script into Table-valued functions and Procedures. That way you can get the types specified in the Procedure and then the script is just the invocation (plus DDL statements if any).

    For example, you define a procedure

    CREATE PROCEDURE MyProc(@param1 string, @param2 string) AS BEGIN

    @result = SELECT * FROM MyTable WHERE column == @param1;

    OUPUT @result TO @param2 USING Outputters.Csv();

    END;

    Then you can for example just write a script as

    MyProc(@p1, @p2);

    And have ADF pass the parameters. Maybe (again, please check with ADF), ADF may allow you to reference and call a procedure directly.


    Michael Rys

    • Marked as answer by blueawning Saturday, November 21, 2015 3:33 AM
    Friday, November 20, 2015 12:32 AM
    Moderator