Monthly Archives: April 2017

Calling U-SQL Stored Procedures with C# Code Behind

So friends, some more lessons learnt when developing with U-SQL and Azure Data Lake. I’ll try and keep this short.

Problem

You have a U-SQL stored procedure written and working fine within your Azure Data Lake Analytics service. But we need to add some more business logic or something requiring a little C# magic. This is the main thing I love about U-SQL, having that C# code behind file where I can extend my normal SQL behaviour. So, being a happy little developer you write your class and method to support the U-SQL above and you recreate your stored procedure. Great!

Next, you try to run that stored procedure…

[ExampleDatabase].[dbo].[SimpleProc]();

But are hit with an error, similar to this:

E_CSC_USER_INVALIDCSHARP: C# error CS0103: The name ‘SomeNameSpaceForCodeBehind’ does not exist in the current context.


Why?

Submitting U-SQL queries containing C# code behind methods works fine normally. But once you wrap it up as a stored procedure within the ADL analytics database the complied C# is lost. Almost as if the U-SQL file/procedure no longer has its lovely code behind file at all!

Just to be explicit with the issue. Here is an example stored procedure that I’ve modified from the Visual Studio U-SQL Sample Application project. Note my GetHelloWord method that I’ve added just for demonstration purposes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP PROCEDURE IF EXISTS [dbo].[SimpleProc];
 
CREATE PROCEDURE [dbo].[SimpleProc]()
AS
BEGIN
 
    @searchlog =
        EXTRACT UserId INT,
                START DateTime,
                Region string,
                Query string,
                Duration INT?,
                Urls string,
                ClickedUrls string
        FROM "/Samples/Data/SearchLog.tsv"
        USING Extractors.Tsv();
 
    @WithCodeBehind =
        SELECT 
            *,
            SomeNameSpaceForCodeBehind.MyCodeBehind.GetHelloWorld() AS SomeText
        FROM @searchlog;
 
    OUTPUT @WithCodeBehind
    TO "/output/SearchLogResult1.csv"
    USING Outputters.Csv();
 
END;

This U-SQL file then has the following C#, with my totally original naming conventions. No trolls please, this is not the point of this post 🙂

1
2
3
4
5
6
7
8
9
10
11
namespace SomeNameSpaceForCodeBehind
{
    public class MyCodeBehind
    {
        static public string GetHelloWorld()
        {
            string text = "HelloWorld";
            return text;
        }
    }
}

So, this is what doesn’t work. Problem hopefully clearly defined.

Solution

To work around this problem, instead of using a C# code behind file for the procedure we need to move the class into its own assembly. This requires a little more effort and plumbing, but does solve this problem. Plus, this approach is probably more familiar to people that have ever worked with CLR functions in SQL Sever that they want to use within a stored procedure.

This is what we need to do.

  • Add a C# class library to your Visual Studio solution and move the U-SQL code behind into a library name space.

  • Build the library and use the DLL to create an assembly within the ADL analytics database. The DLL can live in your ADL store root, in line it or create it from Azure Blob Store. I have another post on that here if your interested.
CREATE ASSEMBLY IF NOT EXISTS [HelloWorld] FROM "assembly/ClassLibrary1.dll";
  • Finally, modify your stored procedure to use the assembly instead of the code behind name space. The new stored procedure should look like this.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DROP PROCEDURE IF EXISTS [dbo].[SimpleProc];
 
CREATE PROCEDURE [dbo].[SimpleProc]()
AS
BEGIN
 
    //Complied library:
    REFERENCE ASSEMBLY [HelloWorld];
 
    @searchlog =
        EXTRACT UserId INT,
                START DateTime,
                Region string,
                Query string,
                Duration INT?,
                Urls string,
                ClickedUrls string
        FROM "/Samples/Data/SearchLog.tsv"
        USING Extractors.Tsv();
 
    @WithCodeBehind =
        SELECT *,
               //Changed TO USE assembly:
               HelloWorld.ClassLibrary1.GetHelloWorld() AS SomeText
        FROM @searchlog;
 
    OUTPUT @WithCodeBehind
    TO "/output/SearchLogResult1.csv"
    USING Outputters.Csv();
 
END;

This new procedure executes without error and gets around the problem above.

I hope this helps and allows you to convert those complex U-SQL scripts to procedures, while retaining any valuable code behind functionality.

Many thanks for reading

Wearing Four Hats at SQLBits 2017

I know there are lots of great blog posts already out there from great people sharing their experiences from SQLBits. However, I hope none of them can offer an account quite as unique as mine from this years event. Why? Well, most people go to SQLBits as attendee’s to learn, network and socialise. This certainly applies to me. But ‘attendee’ was just 1 of my 4 hats (roles) at SQLBits 2017. In this post I’d like to share with you details of my other hats. Which alone poses a wardrobe problem… Orange with black writing or black with orange writing!

Firstly, let’s label my hats…

  1. Attendee
  2. Helper
  3. Speaker
  4. Exhibitor

Before I share any more information with you its worth saying from the start that this was not an easy task juggling these 4 different roles! Very long days with little sleep, little nourishment, and little time to pause. That said, I wouldn’t change it for the world and would gladly do it all again. The reward and enjoyable of SQLBits greatly outweighs any negativity.

Hat Number One – Attendee

I’ve already mentioned being an attendee is the main reason for going to SQLBits. But just to add some stats to that statement:

  • 88 expert speakers
  • 290 hours of content
  • 120 sessions
  • 13 tracks
  • 4 event days
  • 7500 square meters of conference space
  • 700 square meters of partying

SQLBits really is an awesome event! If you haven’t been before I strongly recommend it. Even if you just go for the Friday party, maybe not dressed as a Rubik’s cube though!… Sadly for a disco themed party this often takes some explaining (invented in 1974 + BI SSAS cube)  🙂

Hat Number Two – Helper

Helping at SQLBits is so much more than wearing a bright orange tshirt and telling people where the toilets are! Its about being part of something bigger. Being part of the #SQLFamily that makes the conference tick. Those early starts and late nights to ensure attendee’s have the best possible experience are returned by being part of this great team.

To give you an idea of what I mean by family, here is a picture we posed for (I’m in the middle), just to congratulate an absent helper and friend on the birth of his son just days before the conference. Congrats Terry aka @SQLShark.

I’m not going to waffle on about helping, instead I’m just going to direct you to the blog of my bearded friend Mr. Rob Sewell about why we volunteer at SQLBits. Great post. Great pictures!

Hat Number Three – Speaker

This was only my second time at SQLBits but I was honoured to get selected to speak on Saturday. I took along the Raspberry Pi’s and showed the audience how to build an IoT solution end to end within the Microsoft stack using Azure.

For those that asked me for my C# UWP app code that I ran on the Pi this is now available on GitHub here:

https://github.com/mrpaulandrew/IoTPi3FezHatUWPApp

If speaking on Saturday wasn’t enough I was also invited to talk in Chris Testa-O’Neill’s session on Friday about Azure Data Factory. I offered our real world experiences of the service from a recent customer project, building a complete business intelligence solution in Azure.

I’ll certainly treasure those 20mins in the limelight. Thanks again Chris.

Hat Number Four – Exhibitor

This year with SQLBits being held on the home turf of Purple Frog Systems we simply had to sponsor the event and make our mark. This was a very proud moment for the company having never sponsored Europe’s biggest data platform before.

I was charged with the setup and collapse of the stand as well as speaking to all the lovely attendee’s during breaks about what we do. The purple glow sticks and balloons were all my idea. You’re welcome!… Did I mention Purple Frog is also recruiting, please send your CV to Jobs@PurpleFrogSystems.com.

Another little known sponsor called Microsoft 🙂 also embraced the spirit of things by having a very purple exhibitor stand. Seen to the right. Total coincidence, but let’s claim it as a win for the frog massive! Ha.

If you won one of our Raspberry Pi starter kit prizes I’d be keen to hear from you about how you’ve used the device. Please reach out.

That’s it from SQLBits this year. Start counting down the days until next years event.

Many thanks for reading.

Paul’s Frog Blog

Paul is a Microsoft Data Platform MVP with 10+ years’ experience working with the complete on premises SQL Server stack in a variety of roles and industries. Now as the Business Intelligence Consultant at Purple Frog Systems has turned his keyboard to big data solutions in the Microsoft cloud. Specialising in Azure Data Lake Analytics, Azure Data Factory, Azure Stream Analytics, Event Hubs and IoT. Paul is also a STEM Ambassador for the networking education in schools’ programme, PASS chapter leader for the Microsoft Data Platform Group – Birmingham, SQL Bits, SQL Relay, SQL Saturday speaker and helper. Currently the Stack Overflow top user for Azure Data Factory. As well as very active member of the technical community.
Thanks for visiting.
@mrpaulandrew