Is there any way to pass a U-SQL script a parameter from a C# program?

Question

I'm using U-SQL with a table in Azure Data Lake Analytics. Is there any way to pass a list of partition keys generated in a C# program to the U-SQL script then have the script return all the elements in those partitions?

Michael Rys Michael Rys · Accepted Answer · 2017-02-03T01:42:47

Do you want to run the C# code on your dev box and pass values to a U-SQL script or run C# code inside your U-SQL Script? Your description is not clear. Based on your question title, I will answer your first question.

Passing values as parameters from a C# program: The ADLA SDK (unlike Azure Data Factory) does not yet provide a parameter model for U-SQL scripts (please file a request at http://aka.ms/adlfeedback, although I know it is on our backlog already, having external customer demand helps in prioritization).

However it is fairly easy to add your parameter values by prepending DECLARE statements like the following in the beginning of the script and have the script refer to them as variables.

DECLARE @param = new SqlArray<int>( 1, 2, 3, 4); // 1,2,3,4 were calculated in your C# code (I assume you have int partition keys).

Then you should be able to use the array in a predicate (e.g., @param.Contains(partition_col)). That will not (yet, we have a workitem for it) trigger partition elimination though.

If you want partition elimination, you will have to have a fixed set of parameter values and use them in an IN clause. E.g., you want to check up to 3 months, you would write the query predicate as:

WHERE partition_col IN (@p1, @p2, @p3);

And you prepend definitions for @p1, @p2 and @p3, possibly duplicating values for the parameters you do not need.

Is there any way to pass a U-SQL script a parameter from a C# program?

1 Answers