Stairway to U-SQL Level 13: SQL.MAP

  • Comments posted to this topic are about the item Stairway to U-SQL Level 13: SQL.MAP

  • Mike, thanks for the great chapter (as always)!

    Quick question - I don't see "Preview" menu item when right clicking the table in my Visual Studio, only "Preview by Running a Job", which is definitely another one. Do you use beta or preview version of VS / Azure tools?

  • Hi naugolnyh

    Glad you liked the article!

    I'm one minor version behind the tools at the moment - I'm running Azure Data Lake Tools for Visual Studio 2.2.4000.0, the latest version is 2.2.5000.0, which was released on Feb 8 2017. I'm using VS2015 (Enterprise). I do have Community installed as well, and that behaves as per the article.

    Could be worth upgrading your ADL Tools, that will hopefully sort your problem out.


  • Thanks for the great information on the U SQL Maps.  I have 1 question.  use a SQL Map that looks something like this:

    @expressions = 
    new SQL.MAP<string, string>{
         {"ABC", "1=3+5/7"}, 
         {"CDE", "(10/4.3)-12 +(8 *17)"},
         {"FGH", "14+87-234"},

    Is it possible to pass one of the items in the SQL Map as an argument to a c# method call based on the key name?

    For example if I wanted to pass the expression with the key of "CDE" (using obviously incorrect syntax) to the c# such as

    SELECT namespace.class.method(expressions("CDE"))  as CDE,

    how would I make the proper reference to the the CDE expression? 

    I do not want to break out the expression from the map as the map is dynamic as I have many expressions which vary based on other criteria and there are many calculations to perform.  I know I can calculate the expression in C# and return the calculation but I cannot figure out how to pass a reference to a single element of the SQL Map

    Your input is appreciated.

  • Hi Kahlo13

    Sorry it's taken me a while to come back to you. C# dictionaries do support retrieving values from keys as you illustrated in your post, but I haven't been able to replicate this behaviour with a SQL.MAP. However, all is not lost. It is possible to EXPLODE the SQL.MAP and then use a WHERE clause to filter on the required key, then pass this to a C# function.
    I added this C# function to a script's code-behind file:

    namespace UkPostcodes
      public static class SqlMapTestFunctions
       public static string ReadSqlMapValue(string targetValue)
        var newValue = string.Format("Hello there, {0}!", targetValue);

        return newValue;

    I can then call this from the parent U-SQL script - the comments should explain everything.

    // Declare the manual values here
    @basePeople = SELECT * FROM (VALUES
    ("MMQ1", "Mike McQuillan"), ("BMQ1", "Bertie McQuillan"), ("DMQ1", "Dolly McQuillan"))
    AS p(PersonId, PersonName);

    // Convert them into a SQL.MAP
    @persons = SELECT new SQL.MAP<string, string>{{PersonId, PersonName}} AS PersonList
    FROM @basePeople;

    // Pass required key from the SQL.MAP to the C# function - the WHERE clause is used to do this.
    @outputData =
      SELECT UkPostcodes.SqlMapTestFunctions.ReadSqlMapValue(pData.PersonName) AS ConvertedPersonName
      FROM @persons
         EXPLODE(PersonList) AS pData(PersonId, PersonName)
      WHERE pData.PersonId == "MMQ1";

    // Write out the data to file
    OUTPUT @outputData TO "/outputs/people.tsv" USING Outputters.Tsv();

    Hope this helps!


Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply