Stairway to U-SQL Level 13: SQL.MAP

• Mike McQuillan

  SSCertifiable

  Points: 5997

  More actions

  March 1, 2017 at 12:01 am

  #341087

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

• naugolnyh

  SSC Veteran

  Points: 287

  More actions

  March 1, 2017 at 7:24 am

  #1930957

  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?

• Mike McQuillan

  SSCertifiable

  Points: 5997

  More actions

  March 1, 2017 at 7:34 am

  #1930963

  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.

  Regards,
  Mike.

• kalho13

  Newbie

  Points: 4

  More actions

  January 4, 2019 at 9:48 am

  #2018010

  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"},
       {"IJK","100-23+(54-12)"}
  };

  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.

• Mike McQuillan

  SSCertifiable

  Points: 5997

  More actions

  January 13, 2019 at 3:34 am

  #2018784

  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
   CROSS APPLY
     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!

  Regards,
  Mike.