DEV Community: Darren Fuller

Managing LLM prompts in DotNet with DotPrompt

Darren Fuller — Sun, 27 Oct 2024 10:51:24 +0000

We've been working on some internal tooling lately which makes use of AI to automate a number of tasks and to help reduce the time to production for customer use cases. While building this one of the things that became increasingly annoying was that, every time we needed to modify the system or user prompt we would have to rebuild and re-package, deploy, and then test again. While not massively time-consuming, it's a pain and a few minutes each time adds up over a day.

So, easy solution, lets move the prompts to an external file so we can edit them without needing to rebuild. But...

What if we could make them template driven as well, and what if we could include configuration information, what if we could load them up-front and refer to them by name, and what if it was thread safe access by default (because this app is very Task oriented)?

And so, we built DotPrompt and, published it on GitHub, pushed the packages out to NuGet and made it available under an MIT license.

I started piecing it but wanted to see if someone else had done this already. Turns out there's a couple of efforts out there, like Firebase Genkit Dotprompt, but it's not for the DotNet ecosystem. There were a couple of others as well but they were for Python.

Nobody ever thinks of the DotNet devs 😭

One of the things I didn't want to do was to come up with a new format and parser for this. JSON would have been an obvious choice, but it's not exactly user friendly to write multi-line strings in. XML? No, screw that. So what about YAML? Well, it's structured, has great support for multi-line strings, and is pretty user-friendly to read and write.

So, what does a prompt file look like? Well, it looks like this.

name: Example
config:
  outputFormat: text
  temperature: 0.9
  maxTokens: 500
  input:
    parameters:
      topic: string
      style?: string
    default:
      topic: social media
prompts:
  system: |
    You are a helpful research assistant who will provide descriptive responses for a given topic and how it impacts society
  user: |
    Explain the impact of {{ topic }} on how we engage with technology as a society
    {% if style -%}
    Can you answer in the style of a {{ style }}
    {% endif -%}
fewShots:
  - user: What is Bluetooth
    response: Bluetooth is a short-range wireless technology standard that is used for exchanging data between fixed and mobile devices over short distances and building personal area networks.
  - user: How does machine learning differ from traditional programming?
    response: Machine learning allows algorithms to learn from data and improve over time without being explicitly programmed.
  - user: Can you provide an example of AI in everyday life?
    response: AI is used in virtual assistants like Siri and Alexa, which understand and respond to voice commands.

We have our configuration, and name we can refer to it as later. We've got parameters (with optional items denoted by the question mark ?), default values, the system prompt, the user prompt, and some few-shot prompts.

You might notice that the prompt has template instructions in there. To create this we used the Fluid library which is based on the Liquid template language from Shopify. It's got some great features in it and helps to make the prompt generation pretty powerful.

The library expects the prompt files (all with the file extension of .prompt) to be in a folder called prompts in the current working directory. This means you can run your app from different directories and use different versions of the prompt files, useful if you have different tenants to target.

So, how do you use it? Well, after installing it using NuGet you can either load a prompt file directly (see the repo), or use the prompt manager like this.

var promptManager = new PromptManager();
var promptFile = promptManager.GetPromptFile("example");

And now promptFile has all the information needed to generate the prompts. To generate the system prompt or user prompt you pass in a dictionary of values to fill in the template (or an empty dictionary, or null if there aren't any).

var systemPrompt = promptFile.GetSystemPrompt(null);
var userPrompt = promptFile.GetUserPrompt(new Dictionary<string, object>
{
    { "topic", "bluetooth" },
    { "style", "used car salesman" }
});

And we've now got our generated prompts 🎉

We even built out some extension methods for OpenAI as we're big users of Azure OpenAI, so you don't have to generate the prompts and use them in this way, you can swap it for a method call.

var promptValues = new Dictionary<string, object>
{
    { "topic", "bluetooth" },
    { "style", "used car salesman" }
};

var completion = await client.CompleteChatAsync(
    promptFile.ToOpenAiChatMessages(promptValues),
    promptFile.ToOpenAiChatCompletionOptions()
);

So a full example would look something like this.

using System.ClientModel;
using Azure.AI.OpenAI;
using DotPrompt;
using DotPrompt.Extensions.OpenAi;

var openAiClient = new(new Uri("https://endpoint"), new ApiKeyCredential("abc123"));
var client = openAiClient.GetChatClient("model");

var promptManager = new PromptManager();
var promptFile = promptManager.GetPromptFile("example");

var promptValues = new Dictionary<string, object>
{
    { "topic", "bluetooth" },
    { "style", "used car salesman" }
};

var completion = await client.CompleteChatAsync(
    promptFile.ToOpenAiChatMessages(promptValues),
    promptFile.ToOpenAiChatCompletionOptions()
);

var response = completion.Value;
Console.WriteLine(response.Content[0].Text);

The call to ToOpenAiChatMessages would also include the few-shot prompts if they were in the prompt file as well.

And that's it, call to the LLM is done, we can change the parameter values on each call, and we change the prompts without re-compiling. We integrated this into our internal app and just made the whole process of tweaking so much easier. And we'll continue to dog-food the library.

We did build in a few more things as well, because why not. So it also has.

Ability to parse a prompt file from a stream
parameter value validation at runtime
Interfaces to allow the file store and the prompt manager to be mocked out or used in dependency injection scenarios
Because the file store has an interface, the prompt manager takes an instance of that, so you can build your own stores in case you want to hold them in something like a database (see the repo for an example)

We should probably come up with a logo for it as well, right now it's the boring NuGet default.

Is there something you'd like to see it do? If so then feel free to raise it over on GitHub.

Kotlin and Azure Functions - Automating the deployment

Darren Fuller — Sat, 16 Mar 2024 10:09:45 +0000

Recently I needed to write an Azure Function app that uses the Apache POI library for getting the text from Microsoft Word 94 documents (and yes, I am fully aware that the year is currently 2024, but some people still have 30 year old documents kicking around!).

Being somewhat allergic to coding in Java (this is a personal thing, if you like Java then good for you) I decided to try out writing the code using Kotlin from JetBrains instead. I'm already using IntelliJ as I work with Apache Spark using Scala, so the tooling was already there and ready to go for this.

Microsoft, unexpectedly, have an article about creating Azure Function apps using Kotlin, so this was a great start and, pretty quickly, I had a version of my function app ready to try out.

Debugging

This isn't as straight forward as just running a debug configuration unfortunately, it would be nice if it was, but it's honestly not too bad either. The Microsoft article talks about how to do it, and once you get into the habit of running the app and then running a configuration to attach the debugger, it just starts to flow.

So, running locally I have an Azure Function app, written in Kotlin, using the Apache POI library, extracting text from a Wore 94 document (it kills me a little bit every time I write that part). So I opened up HTTPie and started firing sample documents at it

It works great, so next I want to deploy this thing, and that's where the smooth ride hits a few bumps.

Deployment

In the article it tells you to just run the azure-functions:deploy Maven target. It somewhat skips over the fact that you should probably first open up the pom.xml file and do things like change the resource group name, function app name, and the app region. These might seem trivial, but if you have issues with data leaving certain regions then they become really important.

So, first up, change the pom.xml file, specifically these values in the properties section.

functionAppName
functionAppRegion
functionResourceGroup

Having done that I tried to run the maven target again and... failed.

But it failed because it was complaining that it couldn't read the azureProfile.json or accessTokens.json files and that I should check that I'm logged in using the Azure CLI.

This is weird because I had just created the resource group and a few other things for my deployment using the CLI. But I logged in again and tried again and still the same error!

A bit of searching around the web and it turns out that the Azure CLI had been updated a while ago and changed how it was handling tokens, but the Maven Archtype hasn't been updated to reflect this! So, what next?

Well, most deployments to Azure Functions work by packaging up the function app, producing a zip, and then pushing this up. There are other ways, but this works well and is pretty robust. But it's all hidden away in the target, so how can we replicate what it does. We can do this fairly easily it turns out.

There are a few steps we want to follow.

Clean everything up
Package the app
Create the zip
Deploy the zip

Cleaning things up is easy, we can just run mvn clean and we're done.

Packaging we need to run a couple of things. First is the mvn package target to build our application, and then the next is the mvn azure-functions:package target which creates our directory structure that we need to be able to publish the app (this is the really important step).

Once both of these have been executed, if we look under the target directory we can see a new azure-functions directory which contains everything we need, all the jar files, the host.json file and more.

Now, we're just creating a zip to deploy, so in the command line we move to the target/azure-functions/<function app name> folder and run the following.



> zip -r ../../../deploy.zip *

That will create a deploy.zip file in the root of the source directory with all the contents of the packaged function app directory within it.

Then, we publish the zip to our function app (you need to create that first by the way, as a Java 8 function app) with the following command.



> az functionapp deployment source config-zip -g <resource group name> -n <function app name> --src ./deploy.zip

And that's it, the function app is deployed. You can then grab the URL from the Azure Portal and change the URL in HTTPie, send the document, and get the response.

What I ended up doing was putting all of the steps into a Makefile so I could just run make deploy whenever I wanted to push a new version



clean:
    mvn clean

package: clean
    mvn package
    mvn azure-functions:package

deploy: package
    cd ./target/azure-functions/<app name>; zip -r ../../../deploy.zip *
    az functionapp deployment source config-zip -g <resource group name> -n <func app name> --src ./deploy.zip
    rm deploy.zip

Putting in the dependencies meant that I only needed to execute the deploy step, instead of remembering to clean and package as well.

Wrapping up

Pretty quickly I ended up with a function app deployed, written in Kotlin, which could read and extract text from Word 94 (sob sob sob) file. Other than working out the deployment process it went really smoothly and it runs incredibly well.

I'm probably not quite ready to give up C# and dotnet to use this for everything, but this is a nice option when a Java based solution is the better option.

Querying SQL from Databricks without PyODBC

Darren Fuller — Sat, 06 May 2023 10:00:55 +0000

Okay, so this is probably a bit of a niche post but still.

Something I see a lot of is people asking questions on how to do things like run arbitrary SQL statements on SQL databases from Databricks, or how to execute stored procedures. Often this leads to the same follow-ups on how to install PyODBC along with Unix Drivers, adding Microsoft's packages repo, accepting EULA's etc...

It works, don't get me wrong, but it's a faff especially if you don't have permission to execute those things. Plus Microsoft's packages repo has had... issues in the past, and suddenly production jobs fail because they can't connect. I've posted about this before, so there are ways around it, but still, it's a faff.

So, what if you want to connect to SQL and installing PyODBC isn't an option?

Using what's already available

Yep, you can do this using only what is already available, and pretty easily, all thanks to Py4J. Py4J is important in Spark because Spark runs on the JVM, so how do all of those PySpark calls get executed? Well, it calls the Java/Scala methods under the hood through Py4J. And this works not just for Spark.

To make things like the Azure SQL Databricks connector work it ships with the Microsoft SQL JDBC library (along with others such as PostgreSQL), and we can access these in Python.

To keep things as secure as possible I'm going to show how to do this using a service principal for access, but this works just as well with SQL-based authentication as well.

How it works

So first I'm going to assume some things.

You have a SQL database you can connect to
A service principal (or SQL account) exists which has permissions
You have access to Databricks
The credentials are in a secret scope (if not then why not!)

The first thing we need is a reference to SQLServerDataSource.

SQLServerDataSource = spark._sc._gateway.jvm.com.microsoft.sqlserver.jdbc.SQLServerDataSource

And that's the magic line which lets us access something in the JVM from Python. So we now have a Python variable which references this object. Now we can use it.

client_id = dbutils.secrets.get(secret_scope, '<secret name>')
client_secret = dbutils.secrets.get(secret_scope, '<secret name>')

datasource = SQLServerDataSource()
datasource.setServerName(f'{sql_server}.database.windows.net')
datasource.setDatabaseName(database_name)
datasource.setAuthentication('ActiveDirectoryServicePrincipal')
datasource.setAADSecurePrincipalId(client_id)
datasource.setAADSecurePrincipalSecret(client_secret)

Here we are getting the service principal's application id and client secret from our secret scope. Then we're creating an instance of SQLServerDataSource and configuring it to connect to our database using AAD based authentication (for other options see the connection string settings documentation).

Now, we're read to execute something. So lets do a simple query to get the list of users.

connection = datasource.getConnection()
statement = connection.createStatement()

try:
  results = statement.executeQuery('SELECT name FROM sysusers')
  while results.next():
    print(results.getString('name'))
except:
  print('oops')

So, we get a connection from the data source, get a statement object, execute a query, and then iterate on the results.

And that's pretty much it!

There are other methods which allow you to prepare statements which take parameters in a secure way, and you can use the execute method instead if you're not expecting results (such as when calling a stored procedure). Or maybe you want to make sure a database schema exists before writing your DataFrame to SQL.

statement.execute("IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE [name] = 'MyCoolSchema') BEGIN EXEC('CREATE SCHEMA [MyCoolSchema]') END")

There are samples of how to do this in the data source sample documentation.

But we haven't had to install any new drivers or packages to make this happen.

Any issues?

Yes. Don't go crazy with this. Spark is a Big Data platform used for processing huge amounts of data, it's not intended for making lots of small queries, and this can reduce connection availability for the Spark JDBC operations. But as with anything, if you use the tools the way they are intended to be used then you shouldn't have any issues.

Azure Digital Twins - Fluent API v3.1 - MATCH the things!

Darren Fuller — Sun, 09 Oct 2022 11:07:13 +0000

Back in June of this year I posted about the Fluent API we built at Intelligent Spaces for Azure Digital Twins.

Since then we've been busy making extensive use of the API ourselves and looking at making improvements. This last week we've finished implementing one of the larger improvements to the API since introducing the Fluent API itself. And that's bringing in the MATCH clause.

The MATCH clause

Most of the time when writing queries against Azure Digital Twin you'll find yourself using the JOIN clause, which is pretty easy to use. You start with a twin on the left, and say you want to connect another twin on the right based on a relationship type, such as.

SELECT
    building, level
FROM
    digitaltwins building
    JOIN level RELATED building.isPartOf
WHERE
    building.$dtId = 'myBuilding'

These work great but have a few limitations. You can't join more than 5 deep, there are no OUTER JOIN semantics, and you can only query on the twins, so if there's something about the relationship (other than it's type) that you want to filter on, you're going to have to do this in the client code.

So, lets say we have a graph where we want to get from an owning organisation, down to the assets inside of rooms, but also want to get to the assets Building Information Modelling (BIM) data. In our ontology we have the following.

There's a couple of things here which are going to stop us from using the JOIN clause, the primary one being that the query goes 6 deep and we have a limit of 5. But we can write this using a MATCH clause pretty easily as follows.

SELECT
    asset, bimproperties
FROM
    digitaltwins
MATCH
    (company)-[:owns]->(building)-[:isPartOf]->(level)-[:isPartOf]->(room)-[:servedBy]->(asset)<-[:hasBimProperties]-(bimproperties)
WHERE
    company.$dtId = 'myOrg'

I say easily, but you can see that we've got arrows in there, relationships prefixed with colons, and it gets even more fun when we introduce hops -[query_variable:relationship_name*min_hops..max_hops]-.

This gets complex pretty quickly and becomes easy to really mess things up. Miss that : and suddenly our relationship name becomes a query variable, and our join goes wrong.

The Fluent API

The MATCH clause provides a really powerful way to navigate the digital twin graph, but it's syntax is not natural SQL and, as we've just seen, it's pretty easy to get it wrong. This was one of the primary reasons for why we wanted bring it in to our Fluent API. To do this we introduced a new method called Match, and it brings in all of the functionality of the MATCH clause. Importantly the API also performs validation at run time on what has been specified, as you can't mix the JOIN clause with the MATCH clause, so it guards against this.

We have also introduced the BaseGraphInstanceFluentRelationship type which can be used in one of 2 ways. The first is directly, so you can create a new instance and specify the relationship name. The second way is to derive new relationship types from it and set the relationship name as part of its default constructor.

Using directly means that, where there not existing C# models that describe the relationships you don't have to create entirely new ones to use the API. But they are pretty simple, so you can't do much with them other than use them for specifying the relationship name.

If you derive and create new types, then you can use these with the Where methods to also filter your queries based on relationship properties, and that gets a lot more powerful.


// Used directly
var owns = new BaseGraphInstanceFluentRelationship("owns")

// Or as an override
public class Owns : BaseGraphInstanceFluentRelationship
{
    [JsonPropertyName("start")]
    public DateTime StartOfLease { get; set; }

    [JsonPropertyName("end")]
    public DateTime EndOfLease { get; set; }

    public Owns()
    {
        RelationshipName = "owns"
    }
}

Using the JsonPropertyName attribute allows us to map POCO properties to field names in the graph.

So, lets re-build our query using the MATCH clause above using the Fluent API.

IEnumerable<(Room, Asset, BimProperties)> results = await new FluentGraph(graph)
    .WithFluent()
    .From<Company>(ModelVerificationType.IsOfModel)
    .Match<Building, Owns>()
    .Match<Room, IsPartOf>(modelVerificationType: ModelVerificationType.IsOfModel, minHops: 2)
    .Match<Asset, ServedBy>(modelVerificationType: ModelVerificationType.IsOfModel)
    .Match<BimProperties, HasBimProperties>(direction: MatchDirection.RightToLeft)
    .WhereId<Company>("myOrg")
    .Project<Room, Asset, BimProperties>();

You can see we're starting the same way using From method, but then jump into using Match which, using derived types, takes two type arguments, the first is the model we're connecting to, and the second is the relationship type.

The full method signature allows us to provide a query variable, minimum number of hops, maximum number, join direction, and model verification type. There are also overrides allowing us to specify multiple relationship types as the MATCH clause lets us union different relationship names such as -[:owns|leases|rents]- (that syntax just gets more complex right).

The above Fluent API builds and executes the following Digital Twin query.

SELECT
    room
    , asset
    , bimproperties
FROM
    digitaltwins
MATCH
    (company)-[:owns]->(building)-[:isPartOf*2]->(room)-[:servedBy]->(asset)<-[:hasBimProperties]-(bimproperties)
WHERE
    IS_OF_MODEL(company, 'dtmi:digitaltwins:rec_3_3:agents:Company;1')
    AND IS_OF_MODEL(room, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
    AND IS_OF_MODEL(asset, 'dtmi:digitaltwins:rec_3_3:core:Asset;1')  
    AND company.$dtId IN ['myOrg']

There's a slight change here compared to the original. We're setting a minimum number of hops from building to room. Because we've not specified a maximum number then it's a specific number of hops, so "there are 2 hops from building to room".

But we've now got type safety, compile time safety, and runtime validation of the query. If we specified right-to-left as the join direction then that's what we get, and there is always a : before the relationship name.

One of things we can also do (for matches which don't include a hop) is specify a query name and then use this in a WHERE clause.

// Change the building match statement as follows
.Match<Building, Owns>("o")

// Add the WHERE clause
.Where(
    WhereClause.Comparison<Owns, DateTime>(
        "o", 
        o => o.StartOfLease, 
        DateTime.UtcNow.Date.AddDays(-7), 
        ComparisonOperatorType.GreaterThan
    )
)

Here we specifying the query variable as o and then using it in the where clause to filter only those buildings whose lease has started within the last 7 days. This generates the Digital Twin query (parts of it) like this

-- Match
MATCH (company)-[o:owns]->(building)

-- Where
company.$dtId IN ['myOrg'] AND o.start > '2022-10-02T00:00:00.0000000Z'

You can see that the Fluent API has converted the POCO property name to start because of the JsonPropertyName attribute, and generated a correctly formatted date time value. In there Where method, because we said that the property is a DateTime we can only use the DateTime properties, and can only provide a DateTime value for comparison, otherwise it's a compile time error and we don't get to deploy the code.

Current state

With the introduction of the Match clause we can now use the Fluent API for all methods of querying the Digital Twin graph (at the time of writing), in a method which is interface driven and so lets us unit test code without connecting to a Digital Twin instance. With greater protection against misspellings, queries that update as the twin model version changes, and better compile time safety.

If you want to try it out yourself, we have our SDK published as a NuGet package over at nuget.org.

Don't forget to check out our other releases, such as AdxUtilities, or our Azure Digital Twin connector for Spark.

Or, come and check us out directly if you want to see what we can do for your spaces using Azure Digital Twins, over at our Intelligent Spaces website.

Azure Digital Twins - Building a Fluent API in .NET

Darren Fuller — Sat, 25 Jun 2022 09:05:53 +0000

Azure Digital Twins is an amazing platform service which allows you to model virtual representations of a real world space. In Intelligent Spaces we pull in data from BIM sources to create a digital twin and combine that with real world context from IoT sensors to optimize spaces for business use.

Digital Twins has a Query API through which we can query the twin using the Digital Twin Query Language which provides a SQL type language for querying what is a graph database. Microsoft also provides SDKs we can use instead of having to use the Query API directly.

Creating queries

So, what does a typical query look like? Well, lets says we want to query our digital twin to find all rooms in a building which have a maximum occupancy of 10 or greater, or the name contains the word "meeting". But we also want to make sure that the "room" twins we get back are definitely rooms.

SELECT building, level, room
FROM digitaltwins building
JOIN level RELATED building.isPartOf
JOIN room RELATED level.isPartOf
WHERE building.$dtId = 'HeadOffice'
AND IS_OF_MODEL(room, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND (
    room.maximumOccupancy >= 10
    OR CONTAINS(room.name, 'meeting')
)

Hand writing this means that we're likely to make a mistake. Plus, it means that wherever we use the model information we're going to have to remember to change it in all places we've used it if we update the version.

The Fluent API

To help improve on this situation we decided to create a Fluent API. This way we could have an easy to assembly query builder and compile time safety. But we also wanted to use the POCOs we built to hold the output to bring in the model information. That way, if we update the version later we only have to change it in one place. So, what does this actually look like (including some POCO objects to work with).

public class BuildingModel : BaseGraphInstanceFluentModel
{
    public override string TwinModelType => "dtmi:digitaltwins:rec_3_3:core:Building;1";
}

public class LevelModel : BaseGraphInstanceFluentModel
{
    public override string TwinModelType => "dtmi:digitaltwins:rec_3_3:core:Level;1";
}

public class RoomModel : BaseGraphInstanceFluentModel
{
    public override string TwinModelType => "dtmi:digitaltwins:rec_3_3:core:Room;1";

    [JsonPropertyName("name")]
    public string Name { get; set; }

    [JsonPropertyName("maximumOccupancy")]
    public int MaximumOccupancy { get; set; }
}

dt.WithFluent()
    .From<BuildingModel>()
    .Join<LevelModel, BuildingModel>("isPartOf")
    .Join<RoomModel, LevelModel>("isPartOf", ModelVerificationType.IsOfModel)
    .WhereId<BuildingModel>("HeadOffice")
    .Where(WhereClause.Or(
        WhereClause.Comparison<RoomModel, int>(r => r.MaximumOccupancy, 10, ComparisonOperatorType.GreaterThanOrEqual),
        WhereClause.Function<RoomModel>(r => r.Name, "meeting", FunctionType.Contains)
    ))
    .Project<BuildingModel, LevelModel, RoomModel>();

This produces the follwing Digital Twin SQL statement.

SELECT buildingmodel, levelmodel, roommodel
FROM digitaltwins buildingmodel
JOIN levelmodel RELATED buildingmodel.isPartOf
JOIN roommodel RELATED levelmodel.isPartOf
WHERE IS_OF_MODEL(roommodel, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND buildingmodel.$dtId IN ['HeadOffice']
AND (
    roommodel.maximumOccupancy >= 10
    OR CONTAINS(roommodel.name, 'meeting')
)

We have 3 POCOs in this example which inherit from a BaseGraphInstanceFluentModel class. That base class gives us a number of features and some common properties covering things like $dtId, $eTag, and $metadata. It also takes care of generating the model aliases. The JsonPropertyName attributes in the POCO classes are used to map the C# properties to the properties in the digital twins, the FluentAPI uses these to generate the correct property names for the query.

We also included a few helper methods such as WhereId as this is such a common query piece that it didn't make sense to keep writing it out in full every time.

As you can see, the API is making extensive use of generics and property accessors. This way when we say we want to query a property we get that property, and if we say we're doing a comparison on an integer then it's a compile time error to use a non-integer property or value.

The final part of the builder is the Project method. This is essentially the SELECT part of the query. In this case we're selecting all of the models we've used in the builder, but it can also be a subset of them. But we also wanted a little more.

Projecting to a different type

One of the things we often do with the output is to take only certain properties, so we added a version which lets us project an output to a different POCO.

public class ExampleOutput
{
    public string BuildingId { get; set; }
    public string LevelId { get; set; }
    public string RoomId { get; set; }
    public string RoomName { get; set; }   
    public int MaximumOccupancy { get; set; }
}

dt.WithFluent()
    .From<BuildingModel>()
    .Join<LevelModel, BuildingModel>("isPartOf")
    .Join<RoomModel, LevelModel>("isPartOf", ModelVerificationType.IsOfModel)
    .WhereId<BuildingModel>("HeadOffice")
    .Where(WhereClause.Or(
        WhereClause.Comparison<RoomModel, int>(r => r.MaximumOccupancy, 10, ComparisonOperatorType.GreaterThanOrEqual),
        WhereClause.Function<RoomModel>(r => r.Name, "meeting", FunctionType.Contains)
    ))
    .Project<BuildingModel, LevelModel, RoomModel, ExampleOutput>((b, l, r) => new ExampleOutput
    {
        BuildingId = b.ExternalId,
        LevelId = l.ExternalId,
        RoomId = r.ExternalId,
        RoomName = r.Name,
        MaximumOccupancy = r.MaximumOccupancy
    });

This time we get a different Digital Twin query statement generated which looks like this.

SELECT
    buildingmodel.$dtId AS BuildingId
    , levelmodel.$dtId AS LevelId
    , roommodel.$dtId AS RoomId
    , roommodel.name AS RoomName
    , roommodel.maximumOccupancy AS MaximumOccupancy
FROM digitaltwins buildingmodel
JOIN levelmodel RELATED buildingmodel.isPartOf
JOIN roommodel RELATED levelmodel.isPartOf
WHERE IS_OF_MODEL(roommodel, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND buildingmodel.$dtId IN ['HeadOffice']
AND (
    roommodel.maximumOccupancy >= 10
    OR CONTAINS(roommodel.name, 'meeting')
)

This time instead of querying for the full models, we're returning just the properties we want and aliasing them so that we can match them to the new output type. Again, if we try to project out a string field to an integer then it's a compile time error.

Generic types for when not everything is modelled

Last, but not least, we added in the capability to have "generic" types as well. These are models you can use when you want to join from one twin to another where you only care about the id, but you don't have a full POCO define. So if we didn't have a "level" model (weird, I know), then we could something like this instead.

var level = new GenericGraphInstanceModel("level");

dt.WithFluent()
    .From<BuildingModel>()
    .Join(level, "isPartOf")
    .Join<RoomModel>(level, "isPartOf", ModelVerificationType.IsOfModel)
    .WhereId<BuildingModel>("HeadOffice")
    .Where(WhereClause.Or(
        WhereClause.Comparison<RoomModel, int>(r => r.MaximumOccupancy, 10, ComparisonOperatorType.GreaterThanOrEqual),
        WhereClause.Function<RoomModel>(r => r.Name, "meeting", FunctionType.Contains)
    ))
    .Project<BuildingModel, RoomModel, GenericGraphInstanceModel>(level);

And from this code we get the following query.

SELECT buildingmodel, roommodel, level
FROM digitaltwins buildingmodel
JOIN level RELATED buildingmodel.isPartOf
JOIN roommodel RELATED level.isPartOf
WHERE IS_OF_MODEL(roommodel, 'dtmi:digitaltwins:rec_3_3:core:Room;1')
AND buildingmodel.$dtId IN ['HeadOffice']
AND (
    roommodel.maximumOccupancy >= 10
    OR CONTAINS(roommodel.name, 'meeting')
)

As you can see, it's almost exactly the same as the original. But this gives us the flexibility to use intermediate twins without having to model everything.

The results of all of these queries are Task<IEnumerable<>>, in the case where we're projecting Building, Level, and Room then we get an Task<IEnumerable<(BuildingModel, LevelModel, RoomModel)>> result, giving us typed access to all of the values returned. And naturally Task because the Project method is asynchronous.

Wrapping up

This is a pretty quick wrap up of the functionality. Built into the Fluent API is the capability to use all of the functions and operators available in the language specification, the capability to perform exact matches on the IS_OF_MODEL function.

This has been rolled out in our applications now, giving us more assurance over the queries we're executing, knowing that we haven't accidentally misspelt an alias or function name, and removing lots of code where we're simply creating strings to put into queries. And the API itself is extensively unit tested to give us further confidence still.

Coming up next is looking at bringing relationship information into the API to remove the manual "isPartOf" aspect of the query builder.

We've also made the package available as a NuGet package if you want to try it out yourself.

Nu Shell and Databricks

Darren Fuller — Sat, 15 Jan 2022 12:59:57 +0000

I'm a big fan of the command line. It's often something that can seem daunting to people at first, but with a little time and patience you can often speed up many tasks just by knowing some useful commands and how to chain them together.

Most of the time I'm in Powershell which, thanks to Powershell Core, is now cross-platform and incredibly powerful. But I'm finding myself also using Nu more and more. In both shells I also use the Databricks CLI a lot. Want to check the status of jobs? Use the CLI. Want to upload and download data? Use the CLI. And so on.

Whilst the Databricks CLI is useful, there's times where I want a little more power over it. Such as, using the CLI to find a Databricks runtime version which is under Long Term Support (LTS) and is Photon enabled. So, I can do this using, for instance, the Databricks CLI and some jq. But I'm also lazy and wanted something that's a bit easier to query, and displays nicer, and is easier to output to something like CSV afterwards.

Well, I can get all of that from Nushell. The only downside is that it's quite a few commands to get the data into the right shape to make querying it easy. So, instead, lets do the tedious bits and save them as a command aliases. So, lets fire up Nushell and give it a go.

First up, lets find our config file.

> config path
C:\Users\DarrenFuller\AppData\Roaming\nushell\nu\config\config.toml

Yours will look different to this, but this is the file we need to add our command aliases to.

Now, lets work out what our command looks like. I want to create a command that calls the Databricks CLI for the runtime versions and adds some useful information such as if it's an LTS version. So what does that look like?

>  databricks clusters spark-versions 
    | from json 
    | get versions 
    | insert isLTS { get name | str contains "LTS" } 
    | insert isML { get name | str contains "ML" } 
    | insert photonEnabled { get name | str contains -i "Photon" }
    | insert details { get name | parse "{runtime} (includes Apache Spark {spark},{remainder}" }
    | insert runtime { get details.runtime } 
    | insert spark { get details.spark }
    | reject details

I've put that over multiple lines to make it easier to read, but if you want to run it you'll need to have it all on the same line, like this.

> databricks clusters spark-versions | from json | get versions | insert isLTS { get name | str contains "LTS" } | insert isML { get name | str contains "ML" } | insert photonEnabled { get name | str contains -i "Photon" } | insert details { get name | parse "{runtime} (includes Apache Spark {spark},{remainder}" } | insert runtime { get details.runtime } | insert spark { get details.spark } | reject details

So what's it doing? Lets break it down a bit.

databricks clusters spark-versions

Run the Databricks CLI to get the available runtime information

from json

Parses the response from JSON as a table

get versions

Gets the "version" part of the response object

insert isLTS { get name | str contains "LTS" }

Adds a new "isLTS" column by looking for the term "LTS" in the runtime name

insert isML { get name | str contains "ML" }

Adds a new "isML" column by looking for the term "ML" in the runtime name

insert photonEnabled { get name | str contains -i "Photon"

Adds a new "photonEnabled" column by doing a case-insensitive search for "Photon" in the runtime name

insert details { get name | parse "{runtime} (includes Apache Spark {spark},{remainder}"

Adds a new "details" column by parsing the name name and extracting key information (in curly braces)

insert runtime { get details.runtime }

Adds a new "runtime" column by getting the runtime information from the details column

insert spark { get details.spark }

Adds a new "spark" column by getting the spark version information from the details column

reject detail

Removes the "details" column

That's a lot of commands to run each time, so lets instead save this as a command alias in our config file.

startup = [
    "alias dbx-runtimes = ( databricks clusters spark-versions | from json | get versions | insert isLTS { get name | str contains \"LTS\" } | insert isML { get name | str contains \"ML\" } | insert photonEnabled { get name | str contains -i \"Photon\" } | insert details { get name | parse \"{runtime} (includes Apache Spark {spark},{remainder}\" } | insert runtime { get details.runtime } | insert spark { get details.spark } | reject details )"
]

Here I've aliased the command with the name dbx-runtimes. I've also had to escape the double-quotation marks. But now that we have this we can run all of the above by simply calling the alias.

> dbx-runtimes

────┬──────────────────────────────────┬────────────────────────────────────────────────────────────────────┬───────┬───────┬───────────────┬────────────────────────────┬───────
 #  │               key                │                                name                                │ isLTS │ isML  │ photonEnabled │          runtime           │ spark
────┼──────────────────────────────────┼────────────────────────────────────────────────────────────────────┼───────┼───────┼───────────────┼────────────────────────────┼───────
  0 │ 6.4.x-esr-scala2.11              │ 6.4 Extended Support (includes Apache Spark 2.4.5, Scala 2.11)     │ false │ false │ false         │ 6.4 Extended Support       │ 2.4.5
  1 │ 7.3.x-cpu-ml-scala2.12           │ 7.3 LTS ML (includes Apache Spark 3.0.1, Scala 2.12)               │ true  │ true  │ false         │ 7.3 LTS ML                 │ 3.0.1
  2 │ 7.3.x-hls-scala2.12              │ 7.3 LTS Genomics (includes Apache Spark 3.0.1, Scala 2.12)         │ true  │ false │ false         │ 7.3 LTS Genomics           │ 3.0.1
  3 │ 10.2.x-gpu-ml-scala2.12          │ 10.2 ML (includes Apache Spark 3.2.0, GPU, Scala 2.12)             │ false │ true  │ false         │ 10.2 ML                    │ 3.2.0
  4 │ 7.3.x-gpu-ml-scala2.12           │ 7.3 LTS ML (includes Apache Spark 3.0.1, GPU, Scala 2.12)          │ true  │ true  │ false         │ 7.3 LTS ML                 │ 3.0.1
  5 │ 8.4.x-photon-scala2.12           │ 8.4 Photon (includes Apache Spark 3.1.2, Scala 2.12)               │ false │ false │ true          │ 8.4 Photon                 │ 3.1.2
  6 │ 10.1.x-photon-scala2.12          │ 10.1 Photon (includes Apache Spark 3.2.0, Scala 2.12)              │ false │ false │ true          │ 10.1 Photon                │ 3.2.0
  7 │ 9.1.x-photon-scala2.12           │ 9.1 LTS Photon (includes Apache Spark 3.1.2, Scala 2.12)           │ true  │ false │ true          │ 9.1 LTS Photon             │ 3.1.2
  8 │ 10.2.x-photon-scala2.12          │ 10.2 Photon (includes Apache Spark 3.2.0, Scala 2.12)              │ false │ false │ true          │ 10.2 Photon                │ 3.2.0
  9 │ 8.3.x-scala2.12                  │ 8.3 (includes Apache Spark 3.1.1, Scala 2.12)                      │ false │ false │ false         │ 8.3                        │ 3.1.1
 10 │ 9.0.x-photon-scala2.12           │ 9.0 Photon (includes Apache Spark 3.1.2, Scala 2.12)               │ false │ false │ true          │ 9.0 Photon                 │ 3.1.2
 11 │ 8.4.x-cpu-ml-scala2.12           │ 8.4 ML (includes Apache Spark 3.1.2, Scala 2.12)                   │ false │ true  │ false         │ 8.4 ML                     │ 3.1.2
 12 │ 10.1.x-gpu-ml-scala2.12          │ 10.1 ML (includes Apache Spark 3.2.0, GPU, Scala 2.12)             │ false │ true  │ false         │ 10.1 ML                    │ 3.2.0
 13 │ 9.1.x-scala2.12                  │ 9.1 LTS (includes Apache Spark 3.1.2, Scala 2.12)                  │ true  │ false │ false         │ 9.1 LTS                    │ 3.1.2
 14 │ 10.0.x-cpu-ml-scala2.12          │ 10.0 ML (includes Apache Spark 3.2.0, Scala 2.12)                  │ false │ true  │ false         │ 10.0 ML                    │ 3.2.0
 15 │ 9.0.x-gpu-ml-scala2.12           │ 9.0 ML (includes Apache Spark 3.1.2, GPU, Scala 2.12)              │ false │ true  │ false         │ 9.0 ML                     │ 3.1.2
 16 │ 9.0.x-scala2.12                  │ 9.0 (includes Apache Spark 3.1.2, Scala 2.12)                      │ false │ false │ false         │ 9.0                        │ 3.1.2
 17 │ 8.3.x-cpu-ml-scala2.12           │ 8.3 ML (includes Apache Spark 3.1.1, Scala 2.12)                   │ false │ true  │ false         │ 8.3 ML                     │ 3.1.1
 18 │ 10.1.x-cpu-ml-scala2.12          │ 10.1 ML (includes Apache Spark 3.2.0, Scala 2.12)                  │ false │ true  │ false         │ 10.1 ML                    │ 3.2.0
 19 │ 10.0.x-scala2.12                 │ 10.0 (includes Apache Spark 3.2.0, Scala 2.12)                     │ false │ false │ false         │ 10.0                       │ 3.2.0
 20 │ apache-spark-2.4.x-esr-scala2.11 │ Light 2.4 Extended Support (includes Apache Spark 2.4, Scala 2.11) │ false │ false │ false         │ Light 2.4 Extended Support │ 2.4
 21 │ 10.1.x-scala2.12                 │ 10.1 (includes Apache Spark 3.2.0, Scala 2.12)                     │ false │ false │ false         │ 10.1                       │ 3.2.0
 22 │ 9.1.x-cpu-ml-scala2.12           │ 9.1 LTS ML (includes Apache Spark 3.1.2, Scala 2.12)               │ true  │ true  │ false         │ 9.1 LTS ML                 │ 3.1.2
 23 │ 10.2.x-scala2.12                 │ 10.2 (includes Apache Spark 3.2.0, Scala 2.12)                     │ false │ false │ false         │ 10.2                       │ 3.2.0
 24 │ 10.2.x-cpu-ml-scala2.12          │ 10.2 ML (includes Apache Spark 3.2.0, Scala 2.12)                  │ false │ true  │ false         │ 10.2 ML                    │ 3.2.0
 25 │ 8.3.x-photon-scala2.12           │ 8.3 Photon (includes Apache Spark 3.1.1, Scala 2.12)               │ false │ false │ true          │ 8.3 Photon                 │ 3.1.1
 26 │ 10.0.x-photon-scala2.12          │ 10.0 Photon (includes Apache Spark 3.2.0, Scala 2.12)              │ false │ false │ true          │ 10.0 Photon                │ 3.2.0
 27 │ 10.0.x-gpu-ml-scala2.12          │ 10.0 ML (includes Apache Spark 3.2.0, GPU, Scala 2.12)             │ false │ true  │ false         │ 10.0 ML                    │ 3.2.0
 28 │ 8.4.x-scala2.12                  │ 8.4 (includes Apache Spark 3.1.2, Scala 2.12)                      │ false │ false │ false         │ 8.4                        │ 3.1.2
 29 │ 9.1.x-gpu-ml-scala2.12           │ 9.1 LTS ML (includes Apache Spark 3.1.2, GPU, Scala 2.12)          │ true  │ true  │ false         │ 9.1 LTS ML                 │ 3.1.2
 30 │ apache-spark-2.4.x-scala2.11     │ Light 2.4 (includes Apache Spark 2.4, Scala 2.11)                  │ false │ false │ false         │ Light 2.4                  │ 2.4
 31 │ 7.3.x-scala2.12                  │ 7.3 LTS (includes Apache Spark 3.0.1, Scala 2.12)                  │ true  │ false │ false         │ 7.3 LTS                    │ 3.0.1
 32 │ 8.4.x-gpu-ml-scala2.12           │ 8.4 ML (includes Apache Spark 3.1.2, GPU, Scala 2.12)              │ false │ true  │ false         │ 8.4 ML                     │ 3.1.2
 33 │ 9.0.x-cpu-ml-scala2.12           │ 9.0 ML (includes Apache Spark 3.1.2, Scala 2.12)                   │ false │ true  │ false         │ 9.0 ML                     │ 3.1.2
 34 │ 8.3.x-gpu-ml-scala2.12           │ 8.3 ML (includes Apache Spark 3.1.1, GPU, Scala 2.12)              │ false │ true  │ false         │ 8.3 ML                     │ 3.1.1
────┴──────────────────────────────────┴────────────────────────────────────────────────────────────────────┴───────┴───────┴───────────────┴────────────────────────────┴───────

Your output might look different depending on when you run the command.

But from this we can now start adding in some filters to get to the records we want. So if I want to find all of the runtimes which are Long Term Support but aren't ML instances I can do the following.

> dbx-runtimes | where isLTS | where isML == $false | sort-by key

───┬────────────────────────┬────────────────────────────────────────────────────────────┬───────┬───────┬───────────────┬──────────────────┬───────
 # │          key           │                            name                            │ isLTS │ isML  │ photonEnabled │     runtime      │ spark
───┼────────────────────────┼────────────────────────────────────────────────────────────┼───────┼───────┼───────────────┼──────────────────┼───────
 0 │ 7.3.x-hls-scala2.12    │ 7.3 LTS Genomics (includes Apache Spark 3.0.1, Scala 2.12) │ true  │ false │ false         │ 7.3 LTS Genomics │ 3.0.1
 1 │ 7.3.x-scala2.12        │ 7.3 LTS (includes Apache Spark 3.0.1, Scala 2.12)          │ true  │ false │ false         │ 7.3 LTS          │ 3.0.1
 2 │ 9.1.x-photon-scala2.12 │ 9.1 LTS Photon (includes Apache Spark 3.1.2, Scala 2.12)   │ true  │ false │ true          │ 9.1 LTS Photon   │ 3.1.2
 3 │ 9.1.x-scala2.12        │ 9.1 LTS (includes Apache Spark 3.1.2, Scala 2.12)          │ true  │ false │ false         │ 9.1 LTS          │ 3.1.2
───┴────────────────────────┴────────────────────────────────────────────────────────────┴───────┴───────┴───────────────┴──────────────────┴───────

A lot simpler to read, and very easy to now work with. And if I want to save the results I could just add | save runtimes.csv and I'll have a csv with the same data in it.

I've done the same with the Databricks cluster node types as well, though that is a lot less complex than the above one, but it makes being able to query for the information a lot simpler. And with Nushell providing great features for filtering, displaying, and getting data, it's a smooth and easy workflow.

Azure Bicep - Deploy Function Apps with KeyVault references

Darren Fuller — Fri, 31 Dec 2021 15:55:00 +0000

One of the things you often yourself doing when starting a new project, module, or component, is having to define the infrastructure that it is going to run on. In the world of Azure often what you find is that many will just drop into the portal and create the items that they need directly, this obviously isn't good as a repeatable pattern but works for throw-away tests. Others might be braver and fire up the console to use the Azure CLI tool, much better for repeating steps if you remember to script it.

Better yet are those who take that little extra time and generate the environment using templates. Repeatable, consistent, and they give the option of parametrising them so that you can change the scale of components as they're deployed to different environments.

What I want to do in this post is show doing exactly this, by deploying the following components, but also by setting up managed identities, adding RBAC permissions in, and hooking them up all from the same template. By putting in that little bit extra you can go from zero to fully configured platform in minutes, in a consistent and repeatable way.

I'm going to do this using Bicep which is Microsoft's DSL for deploying resources to Azure. Don't be fooled by the 0.4 version number, this is a more than capable language which should take centre stage of your deployment scripts. Unlike ARM templates before it also supports modules, allowing you to break apart your templates without the need for a public end-point to bring them all back together again. But for this post we're only going to need a single template.

The infrastructure

So what are we deploying? Well for this post the core of the platform is going to be the Azure Function app, but it's going to need some support. We're also going to assume that the platform is going to collect data from an external service, so the secret we want will be an API key passed in to the template. So the full platform will be:

Azure Function App with System Assigned managed identity and app settings for:
- API Key from KeyVault using KeyVault references
- Storage account name
- Container name
- Key from Application Insights
Azure Storage Account with container for future use
- Also with data contributor permissions assigned to the Function App at the storage account level
Azure KeyVault
- Deployed using RBAC for authentication instead of using Access Control Lists
- With secret for the API key
- Secret User permissions assigned to the Function App
Application Insights
Azure Log Analytics to back the Application Insights instance

And whilst we're at it lets make sure we add some basic security steps like HTTPS only traffic and setting TLS 1.2 as the minimum permitted TLS version.

Creating the template

The best way to get started with Azure Bicep is to use Visual Studio Code with the Bicep extension which includes language server support and some other nice features. But the language server support giving us highlighting, template outlines, auto-complete etc... is the real nice to have feature.

The template is available in full on Github.

Parameters

For this demo template I want to make 3 things configurable.

The prefix for all of the resources (but defaulted to "demo")
The Storage Account SKU (defaulted to Standard LRS)
The API key for the external service

In this we have defined the prefix as a string and restrict it's length to a minimum of 3 and a maximum of 6 and default it to "demo". We give the storage account SKU a set of values it needs to be restricted to and default it to "Standard_LRS". Finally we define the API key as a string, but annotate the template to make it a secure parameter. Making this a secure parameter means that we're not going to accidentally leak the value to anyone who can see the template deployment history.

Variable

Next up are the variables. The bulk of these are defining things like locations and names. I'm using a naming convention of <prefix>-<service name>-<unique id>. This lets multiple instances be deployed without (hopefully) hitting any naming conflict issues. The only variation in this is the storage account name as we can't use hyphens, only alpha-numeric characters.

The storageConnectionString is used later on in the template and is only defined here so I don't repeat myself later.

The storageBlobDataContributorRole and keyVaultSecretsUserRole are the role definition values for the built in Storage Blob Data Contributor and Key Vault Secrets User built-in roles which you can find in the Azure documentation.

Resources

Right, on to the fun stuff. We've got our parameters and variables, now it's time to put them to good use.

Storage account

Azure Storage is typically the beating heart of any platform, and so it's normally the first resource I start with.



resource storage 'Microsoft.Storage/storageAccounts@2021-06-01' = {
  name: storageAccountName
  location: location
  sku: {
    name: storageSkuName
  }
  kind: 'StorageV2'
  properties: {
    supportsHttpsTrafficOnly: true
    minimumTlsVersion: 'TLS1_2'
    encryption: {
      keySource: 'Microsoft.Storage'
      services: {
        blob: {
          enabled: true
        }
        file: {
          enabled: true
        }
        queue: {
          enabled: true
        }
        table: {
          enabled: true
        }
      }
    }
  }

  resource blobService 'blobServices' = {
    name: 'default'

    resource content 'containers' = {
      name: 'content'
    }
  }
}

Out of the box here we're setting the minimum TLS version to 1.2 and enabling HTTPS only traffic. There really should be very very few reasons as to why these are not in every template we produce. We're also turning on encryption of data in all storage services by default.

We have a couple of nested resources which are how we create our containers. In this case I'm creating a container called "content".

Log Analytics and Application Insights

Next up we want to deploy an Application Insights instance which will be used by our Azure Function app. It's a couple of years away at the time of writing, but Microsoft will be requiring that all instances are backed by a Log Analytics workspace. This makes a huge amount of sense really as it gives a single place to monitor your application and generate alerts based on health conditions.



resource logAnalytics 'Microsoft.OperationalInsights/workspaces@2021-06-01' = {
  name: logAnalyticsName
  location: location
  properties: {
    sku: {
      name: 'PerGB2018'
    }
    retentionInDays: 30
    features: {
      enableLogAccessUsingOnlyResourcePermissions: true
    }
    workspaceCapping: {
      dailyQuotaGb: 1
    }
    publicNetworkAccessForIngestion: 'Enabled'
    publicNetworkAccessForQuery: 'Enabled'
  }
}

resource appInsights 'Microsoft.Insights/components@2020-02-02' = {
  name: appInsightsName
  location: location
  kind: 'web'
  properties: {
    Application_Type: 'web'
    publicNetworkAccessForIngestion: 'Enabled'
    publicNetworkAccessForQuery: 'Enabled'
    WorkspaceResourceId: logAnalytics.id
  }
}

If you're familiar with ARM templates you might be noticing the lack of dependsOn entries. This is because in Bicep, where we use a resource reference to get a value (e.g. logAnalytics.id) this adds an automatic dependency in for us. The use of the id at this point sets the workspace resource instance backing the Application Insights instance. This is a pretty simple setup of the services and I won't dive in any further at this point.

KeyVault

Okay, next up, KeyVault. This is core to accessing secrets on the platform in a secure manner. One of the things we're going to do here is enable the RBAC permissions instead of using Access Control Lists (ACLs). Normally when using ACLs we would give dependent services Get and List permissions over Secrets, with RBAC we can instead use the Key Vault Secret User built-in role. This makes the management of secrets a lot easier and means we can view permissions in a single place.



resource kv 'Microsoft.KeyVault/vaults@2021-06-01-preview' = {
  name: keyVaultName
  location: location
  properties: {
    sku: {
      family: 'A'
      name: 'standard'
    }
    tenantId: subscription().tenantId
    enableRbacAuthorization: true
    enabledForDeployment: false
    enabledForDiskEncryption: true
    enabledForTemplateDeployment: false
  }

  resource storageNameSecret 'secrets' = {
    name: 'ExternalServiceApiKey'
    properties: {
      contentType: 'text/plain'
      value: apiKey
    }
  }
}

The enableRbacAuthorization setting is key for us to enable RBAC permissions. Otherwise we're setting up a fairly standard instance. We're also create a new secret as a nested resource using our secure API key parameter. Maybe pick a better secret name than I have, but it works for a demo.

Function app

Last up for resources is the Azure Function application.



resource plan 'Microsoft.Web/serverfarms@2021-02-01' = {
  name: appServicePlanName
  location: location
  kind: 'functionapp'
  sku: {
    name: 'Y1'
  }
  properties: {
  }
}

resource funcApp 'Microsoft.Web/sites@2021-02-01' = {
  name: functionAppName
  location: location
  kind: 'functionapp'
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    httpsOnly: true
    serverFarmId: plan.id
    siteConfig: {
      ftpsState: 'Disabled'
      minTlsVersion: '1.2'
      netFrameworkVersion: 'v6.0'
      appSettings: [
        {
          name: 'AzureWebJobsStorage'
          value: storageConnectionString
        }
        {
          name: 'WEBSITE_CONTENTAZUREFILECONNECTIONSTRING'
          value: storageConnectionString
        }
        {
          name: 'APPINSIGHTS_INSTRUMENTATIONKEY'
          value: appInsights.properties.InstrumentationKey
        }
        {
          name: 'FUNCTIONS_WORKER_RUNTIME'
          value: 'dotnet'
        }
        {
          name: 'FUNCTIONS_EXTENSION_VERSION'
          value: '~4'
        }
        {
          name: 'ApiKey'
          value: '@Microsoft.KeyVault(VaultName=${kv.name};SecretName=${kv::storageNameSecret.name})'
        }
        {
          name: 'ContentStorageAccount'
          value: storage.name
        }
        {
          name: 'ContentContainer'
          value: storage::blobService::content.name
        }
      ]
    }
  }
}

This is deployed under a consumption plan (mostly to keep the template a bit simpler), but it's the appSettings where things start to get more interesting.

In here we're creating the AzureWebJobsStorage and WEBSITE_CONTENTAZUREFILECONNECTIONSTRING settings using the connection string we defined earlier in the variables section.

The APPINSIGHTS_INSTRUMENTATIONKEY setting is being set using the instrumentation key of the yet-to-be-deployed Application Insights resource which we're accessing just using dot notation.

For ApiKey we're creating our KeyVault reference. We could do this using the URL format, but I'm using this format as I think it's easier to read. I'm also not using the secret version as I want to get the latest key each time. Because the secret itself is a nest resource we have to use the double-semi-colon notation :: to access it's values.

Setting up permissions

Having the KeyVault reference is great, but if we deploy this now it will fail. This is because the Function App doesn't have permissions to read secrets, so lets set that up as well.



resource storageFunctionAppPermissions 'Microsoft.Authorization/roleAssignments@2020-04-01-preview' = {
  name: guid(storage.id, funcApp.name, storageBlobDataContributorRole)
  scope: storage
  properties: {
    principalId: funcApp.identity.principalId
    principalType: 'ServicePrincipal'
    roleDefinitionId: storageBlobDataContributorRole
  }
}

resource kvFunctionAppPermissions 'Microsoft.Authorization/roleAssignments@2020-04-01-preview' = {
  name: guid(kv.id, funcApp.name, keyVaultSecretsUserRole)
  scope: kv
  properties: {
    principalId: funcApp.identity.principalId
    principalType: 'ServicePrincipal'
    roleDefinitionId: keyVaultSecretsUserRole
  }
}

There's a lot happening here, so lets break it down a bit.

We have 2 roleAssignments resources here. The first is assigning permissions to the storage account so that later on (if we want) the function app can read and write data to the storage account. This isn't needed for our example, but I added it for demonstration purposes. The second role assignment assigns secret user permissions to the Azure Function app.

Inside of both we're creating a name by generating a guid. This has to be something which is known at deployment time so we can't use values like the principal id as that's only known after the resource deployment. Which values you provide is largely up to you, but I like the format of target/source/role as it makes sense when reading it back later.

In the properties we have to say which principal needs access, what role we're giving it, and that it's type is ServicePrincipal (for our managed identities). This last point is important, the deployment will work without it but might occasionally throw a PrincipalNotFound error as the identity might not have been created before we try assigning permissions. By adding this property we should be waiting for the identity to be created first.

Deploying

This is the easy part of the process. Assuming you've installed the Azure CLI and have bicep installed you can simply run the following (changing the location and resource group name to suit you)



az group create --name func-app-demo --location northeurope

az deployment group create --resource-group func-app-demo --template-file function-app.bicep --parameters apiKey=abc123

The above command skips the storage account SKU and resource prefix so the defaults are used.

You can check that permissions have been assigned by looking in the access control section of the KeyVault instance.

And we can see the KeyVault reference in the Azure Function configuration.

And that's it. We can now deploy our functions and make use of the app settings already created. And when we're ready we can take this template and deploy out to all of our other environments.

If you get intrigued you can always see what the ARM equivalent of the template might have looked like by running the following.



az bicep build -f function-app.bicep

This will generate a function-app.json ARM template file in which you will see of the dependencies added in, and what the shortcuts like listKeys() have done for us. I'm sure you'll agree that the bicep is much nicer to read and maintain.

Databricks and PyODBC - Avoiding another MS repo outage

Darren Fuller — Sat, 10 Jul 2021 10:21:32 +0000

Not so long ago Microsoft suffered an outage with their package repo over at https://packages.microsoft.com. The short version is that it looks as though the pools directory was removed, forcing Microsoft to synchronize everything back out. Of course the outage started pushing traffic to other end-points which added bottlenecks and it was just a lot of pain for around 26 hours.

Whilst this affected a lot of devs for a variety of projects, the one I was more affected by was the impact on Databricks environments which were pulling in the Linux ODBC driver for Microsoft SQL Server. The standard init script for doing this relies on installing the packages at the point the cluster is created. If the repo isn't available then the init script takes longer to run, fails, but the cluster still runs; it just breaks anything which needs the driver.

I wanted to come up with a way to try and prevent this happening again. Whilst it's unlikely to happen again, adding some resiliency is never a bad thing.

So I created a Databricks notebook which downloads the package and it's dependencies from the Microsoft repo to the cluster. It does this by downloading each required package, when they have all successfully completed it swaps out the current set of files with the new ones. If any of the files can't be downloaded for any reason then the existing files are left intact. The init script uses these downloaded files instead to install the driver. In the event of another outage the local files are not replaced and the cluster will start up as normal with the previously downloaded packages.

Each time the packages are successfully downloaded the script updates the init script, just in case any more packages have been added or the file locations have been changed.

try:
    # Download packages
    if previous.exists():
        shutil.rmtree(previous)

    shutil.move(current, previous)
    shutil.move(new, current)
except:
    # Remove any newly downloaded files
    shutil.rmtree(next)

The notebook is available on GitHub as both an ipynb and dbc file. Hopefully someone else finds it useful.

Scratching an itch with Spark

Darren Fuller — Sun, 27 Jun 2021 10:59:18 +0000

One of the things I love about Spark is how easy it makes working with various data sources. Once you've got the data out of the source and into a Dataset or DataFrame then you can manipulate that data without thought to the original format. Most of the times this is in CSV, Parquet, JSON, or a few others. But occasionally (or quite a lot of the time it seems if you're me) people will have the bulk of their data in Excel.

I'm not going to go into why Excel is a bad format for data storage and distribution, you can find plenty of reasons on-line, or you've likely encountered it yourself (Excel interpreting dates anyone?).

I've been working on a custom data source for Spark recently using the newer DatasourceV2 APIs, which make it easier to write data sources without having to consider RDDs, whilst bringing along a lot of new functionality like predicate push-down.

At the moment I'm still working through a few kinks, and ensuring that I've got as much code and branch coverage as I can, at which point I'll do a deeper dive into the how as I can't see a lot about how to write your own data sources on-line or off-line. But at the moment the data source supports the following features.

Handling Excel 97-2003, 2010, and OOXML files (thanks to Apache POI)
Multi-line headers
Reading data from multiple sheets matching a given regular expression
Handle glob patterns for reading multiple files
Outputting the sheet name in the result set (set via an option)
Schema inference
Cleaning up column names (removing invalid characters)
Handle merged cells (repeats data to all cells in the merged region)
Formula evaluation (for those supported by Apache POI)
Long name, short name, and helper method for loading data
Works in Scala, PySpark, and Spark SQL

This screenshot is querying a sample workbook I put together which contains formula data, I wanted to do this to make sure that the data was extracted as expected. And thanks to the Apache POI library, most formulae people use should be covered.

Code coverage is looking pretty good at the moment, but I want to make sure all of the key parts of the data source are covered, as there's a few "quirks" of Excel which I need to make sure are working as expected.

Hopefully I can jump onto the deeper dive soon, just in case there's anyone else out there crazy enough to want to write their own data source.

Unit testing your PySpark library

Darren Fuller — Sun, 28 Mar 2021 10:39:49 +0000

In software development we often unit test our code (hopefully). And code written for Spark is no different. So here I want to run through an example of building a small library using PySpark and unit testing it. I'm using Visual Studio Code as my editor here, mostly because I think it's brilliant, but other editors are available.

Building the demo library

I really wanted to avoid the usual "lets add two numbers together" example and use something a little more like something you might come across day-to-day. So, I'm going to create a library with some methods around creating a date dimension.

A date dimension is something you will tend to come across in any Data Warehouse or Data LakeHouse environment where you're working with dates. And really that's pretty much everywhere! This kind of dimension is typically created to contain information about days of a year which users might want to slice-and-dice data with later on. If you can join your fact tables to a date dimension based on a date key, and then want to see all data in a given month, quarter, last week, over weekends, it gets much easier.

I'm going to stick with a fairly simple date dimension for brevity, so it will just include the key, basic information about the day, month, and year, and the ISO week number.

The ISO week number is the kicker here, and it's better explained over on Wikipedia but it gets interesting because, contrary to popular belief, there aren't always 52 weeks in a year (dates are always so much fun). This isn't something for which a function in Spark exists, but fortunately this is a piece of functionality we can get straight out of python, as the datetime class has an isocalendar function which gives us the ISO values back as a tuple.

But... We can't just call Python code directly from PySpark (well, we can do, but we shouldn't because it's really bad for performance). So we're going to need to create a user defined function.

PySpark has gotten a lot easier to work with in recent years, and that's especially true of UDFs with the arrival of Pandas UDFs. If you want to know why then I suggest you read the docs as it's a bit long winded for here, but it means we can write User Defined Functions in Python which are significantly more performant. Still not quite as good as Scala, but when you think about what it needs to do under the hood, they're very impressive.

The ISO week number function

Write, lets dive into the code for the User Defined Function then.



def get_iso_weeknumber(dt: Union[date, datetime]) -> int:
    if type(dt) is not datetime and type(dt) is not date:
        return None

    return dt.isocalendar()[1]


@pandas_udf(IntegerType())
def iso_week_of_year(dt: pd.Series) -> pd.Series:
    return dt.apply(lambda x: get_iso_weeknumber(x) if not pd.isnull(x) else pd.NA)

I've deliberately broken it into 2 functions here, and I probably could have used some better names but meh. The reason for breaking it down is because of testing. Where possible we want to test custom logic outside of Spark so we don't need to spin up a new session, this means we can write a lot of tests and execute them quickly to check it's working, and that's the purpose of the get_iso_weeknumber function.

In the iso_week_of_year function we're getting a Pandas Series as our input, and we're returning another Pandas Series. This is a Series-to-Series function and the output must be the same length as the input. The code in this function is deliberately brief as we want to keep as much logic out of here as possible, it should really just care about applying the get_iso_weeknumber function to the input. And that's all it's doing, as long as the input value isn't null.

The date dimension

Now that we're able to create our ISO week number we can create a date dimension on demand. There's a few different ways of doing this, but my preferred method is using the SparkSession.range function using UNIX timestamps.



def create_date_dimension(spark: SparkSession, start: datetime, end: datetime) -> DataFrame:
    if spark is None or type(spark) is not SparkSession:
        raise ValueError("A valid SparkSession instance must be provided")

    if type(start) is not datetime:
        raise ValueError("Start date must be a datetime.datetime object")

    if type(end) is not datetime:
        raise ValueError("End date must be a datetime.datetime object")

    if start >= end:
        raise ValueError("Start date must be before the end date")

    if start.tzinfo is None:
        start = datetime.combine(start.date(), time(0, 0, 0), tzinfo=timezone.utc)

    if end.tzinfo is None:
        end = datetime.combine(end.date(), time(0, 0, 0), tzinfo=timezone.utc)

    end = end + timedelta(days=1)

    return (
        spark.range(start=start.timestamp(), end=end.timestamp(), step=24 * 60 * 60)
             .withColumn("date", to_date(from_unixtime("id")))
             .withColumn("date_key", date_format("date", "yyyyMMdd").cast("int"))
             .withColumn("day", dayofmonth("date"))
             .withColumn("day_name", date_format("date", "EEEE"))
             .withColumn("day_short_name", date_format("date", "EEE"))
             .withColumn("month", month("date"))
             .withColumn("month_name", date_format("date", "MMMM"))
             .withColumn("month_short_name", date_format("date", "MMM"))
             .withColumn("year", year("date"))
             .withColumn("week_number", iso_week_of_year("date"))
    )

There's a lot of setup in this function, just to make sure we're giving all the right information to create the dimension, but the actual generation is pretty straight forward. You can see on the last line we're using our iso_week_of_year UDF to get the week number and calling it just like any other Spark function.

Using the SparkSession.range function with a step of 86,400 means we're incrementing by 1 day at a time, regardless of any time component we're passing in to the function in the first place.

Running this gives us a table looking like the following:



>>> start = datetime(2021, 1, 1)
>>> end = datetime(2021, 1, 10)
>>> create_date_dimension(spark, start, end).show()
+----------+----------+--------+---+---------+--------------+-----+----------+----------------+----+-----------+
|        id|      date|date_key|day| day_name|day_short_name|month|month_name|month_short_name|year|week_number|
+----------+----------+--------+---+---------+--------------+-----+----------+----------------+----+-----------+
|1609459200|2021-01-01|20210101|  1|   Friday|           Fri|    1|   January|             Jan|2021|         53|
|1609545600|2021-01-02|20210102|  2| Saturday|           Sat|    1|   January|             Jan|2021|         53|
|1609632000|2021-01-03|20210103|  3|   Sunday|           Sun|    1|   January|             Jan|2021|         53|
|1609718400|2021-01-04|20210104|  4|   Monday|           Mon|    1|   January|             Jan|2021|          1|
|1609804800|2021-01-05|20210105|  5|  Tuesday|           Tue|    1|   January|             Jan|2021|          1|
|1609891200|2021-01-06|20210106|  6|Wednesday|           Wed|    1|   January|             Jan|2021|          1|
|1609977600|2021-01-07|20210107|  7| Thursday|           Thu|    1|   January|             Jan|2021|          1|
|1610064000|2021-01-08|20210108|  8|   Friday|           Fri|    1|   January|             Jan|2021|          1|
|1610150400|2021-01-09|20210109|  9| Saturday|           Sat|    1|   January|             Jan|2021|          1|
|1610236800|2021-01-10|20210110| 10|   Sunday|           Sun|    1|   January|             Jan|2021|          1|
+----------+----------+--------+---+---------+--------------+-----+----------+----------------+----+-----------+

You can see that the first few days of 2021 were part of week 53, and week 1 didn't actually start until the 4th January.

Unit testing the code

But the title of this post is about unit testing the code, so how is that done?

Well we've seen that the ISO week number functions are broken out, so unit testing get_iso_weeknumber is pretty straight forward and can be tested just like any other Python code.

Whilst we can test the iso_week_of_year function we lose the ability to capture any coverage information because of the way Spark calls the function. If you're using Coverage.py then you can add patterns to your .coveragerc file to exclude these functions like this from your coverage results such as the following.



[report]
exclude_lines =
    def .* -> pd.Series

We're going to test our create_date_dimension function which will call the iso_week_of_year function.

We'll need a Spark Session to test the code with, and the most obvious way of doing that would be like this (I'm using the Python unittest library here).



class DateDimensionTests(unittest.TestCase):
        def test_simple_creation(self):
        spark = SparkSession.builder.appName("unittest").master("local[*]").getOrCreate()

        df = create_date_dimension(spark, datetime(2021, 1, 1), datetime(2021, 1, 5))

But there's a problem with this. For every single test we're going to be creating a brand new Spark Session, which is going to get expensive. And if we need to change any of the spark configuration, we're going to need to do it in every single test!

So what's a better way? Well, we can create our own test class based on unittest.TestCase which does all of the set up for us.



class PySparkTestCase(unittest.TestCase):

    @classmethod
    def setUpClass(cls) -> None:
        conf = SparkConf()
        conf.setAppName("demo-unittests")
        conf.setMaster("local[*]")
        conf.set("spark.sql.session.timezone", "UTC")

        cls.spark = SparkSession.builder.config(conf=conf).getOrCreate()

    @classmethod
    def tearDownClass(cls) -> None:
        cls.spark.stop()

    def tearDown(self) -> None:
        self.spark.catalog.clearCache()

    def assert_dataframes_equal(self, expected: DataFrame, actual: DataFrame, display_differences: bool = False) -> None:
        diff: DataFrame = actual.subtract(expected).cache()
        diff_count: int = diff.count()

        try:
            if diff_count != 0 and display_differences:
                diff.show()
            self.assertEqual(0, diff_count)
        finally:
            diff.unpersist

What have we got now? Well, we have our setUpClass method which is doing the work of setting up our Spark Session for us; and we have our tearDownClass method which clears up the Spark Session once we're done.

We've also added in a tearDown method which clears up after each test, in this case it's clearing out the cache, just in case. And there's a new method for comparing data frames which abstracts a lot of repetitive code we might write in our unit tests.

One of the other things that's different in this setup is the line conf.set("spark.sql.session.timezone", "UTC"). Timestamps are evil. It's that simple. A date is sort of okay, it represents a point in the calendar so they're nice to work with. But the range we're creating is using seconds from the epoch, and so we're in the world of Timestamps, and that world has Daylight Savings Times and Timezones! And how they work varies based on your OS of choice.

Running the following on my Windows 10 laptop using Python 3 gives an error of OSError: [Errno 22] Invalid argument



from datetime import datetime
datetime(1066, 10, 14).timestamp()

But running it in Ubuntu 20.04 on WSL gives a result of -28502755125.0. Now, running it again in both, but with timezone information provided.



from datetime import datetime, timezone
datetime(1066, 10, 14, tzinfo=timezone.utc).timestamp()

Well, now they both give an answer of -28502755200.0. So, setting the spark session timezone to UTC means that it's going to be handling timestamps consistently, regardless of where in the world I am or on what OS I'm running.

Writing the tests themselves

Now that we have our test case class, we can write some unit tests, and by putting a number of them under the same test class we're going to use the same Spark Session, which means that we're not going to incur a performance penalty for constantly re-creating it.



class DateDimensionTests(PySparkTestCase):
    def test_simple_creation(self):
        expected = self.spark.read.csv(
            self.get_input_file("simple_date_dim.csv"),
            header=True,
            schema=DateDimensionTests.get_dimension_schema())

        result = create_date_dimension(self.spark, datetime(2021, 1, 1), datetime(2021, 1, 5))

        self.assert_dataframes_equal(expected, result, display_differences=True)

    def test_simple_range_week53(self):
        week_53_day_count = (
            create_date_dimension(self.spark, datetime(2020, 12, 1), datetime(2021, 1, 31))
            .filter(col("week_number") == 53)
            .count()
        )

        self.assertEqual(7, week_53_day_count)

These can now be executed using the Visual Studio Code test runner...

... or from the command line.



python -m unittest discover -v -s ./tests -p test_*.py

They're still not as performant as tests which don't require a Spark Session and so, where possible, make sure business logic is tested outside of a Spark Session. But if you need one, then this is definitely a way to go.

Summary

Phew. So this is my first post to Dev.to. I've been sat reading content from others for a while and thought it was time I contributed something of my own. Hopefully it helps someone out, or gives someone a few ideas for doing something else.

If you can think of anything which could be improved, or you have a different way of doing things then let me know, things only improve with collaboration.

If you want to have a look at the code, or use it in any way (it's MIT licensed), then you can find it over on Github.