But first, some context. Serverless functions are the most reusable software entities that ever existed. If you doubt that, just take one look at the Azure Functions Triggers and Bindings page. Functions can be called into service by everything from an HTTP request to a queue message to the passing of time. And yet, as versatile as all these triggers are, sometimes you might desire to invoke functions-in-the-cloud from your local enterprise. And in the local enterprise, databases are king.
So, let's imagine you have your local SQL Server database but you want to integrate that database data with something in the cloud: specifically, you'd like to invoke Azure Functions. Ideally, you want this to be as accessible as joining two tables in a SQL Query; in fact, SQL queries would be the perfect place to invoke your Azure Function.
The End Result: Azure Functions in Your SQL Queries
Can this be achieved? Yes, it can. Before we go into how, let's take a look at the finished result. By the end of this post, you'll know how to create an Azure Function for weather that can be invoked using queries like these:select 'Chicago weather: ', Result from weather('41.8781', '-87.6298', 'summary')
Chicago weather: Clear
select 'Chicago temperature', Result from weather('41.8781', '-87.6298', 'temperature')
Chicago temperature: 31.81
The SQL function weather(latitude, longitude, property) is in turn invoking an Azure Function. That function is retrieving the weather from weather service DarkSky. The data could just as easily have come from a cloud database using the same pattern.
At The Other End of the Rainbow: Azure Function to Retrieve Weather
Before we connect the dots, let's go all the way to the cloud and see how this weather data is being retrieved. I created an Azure Function named weather-city. This function accepts latitude, longitude, and a property name such as 'temperature' or 'summary' or 'icon'. It then looks up the weather and returns the requested value. It's written in JavaScript and can be accessed via HTTP/S.To return weather, you need a source for meteorological data. I'm using DarkSky.net, which has an approachable JSON API, a lot of good data, and a generous allocation of 1,000 free API calls per day.
All that's left then is to write our function, which will be making an https call to DarkSky.net to retrieve the weather. To repeat this yourself, you'll need to register with DarkSky and get your own API key.
Here's our Azure Function code:
var https = require("https");
module.exports = function (context, req) {
if (req.query.lat && req.query.long) {
var apikey = 'f9d0925b077...your-api-key...52cf42625a8';
var url = 'https://api.darksky.net/forecast/' + apikey + '/' + req.query.lat + ',' + req.query.long;
https.get(url, (resp) => {
var data = "";
resp.on("data", (chunk) => {
data += chunk;
});
resp.on("end", () => {
context.log(data);
var value = null;
if (req.query.prop=='summary') {
value = JSON.parse(data).currently.summary
}
else if (req.query.prop=='temperature') {
value = JSON.parse(data).currently.temperature
}
else if (req.query.prop=='icon') {
value = JSON.parse(data).currently.icon
}
context.res = {
// status: 200, /* Defaults to 200 */
body: value
};
context.done();
});
});
}
else {
context.res = {
status: 200,
body: "Missing parameters: lat, long, prop"
};
}
};
Azure Function weather-city
With the proper parameters in place, the Azure Function works and can be tested from a browser:
Azure Function for Weather tested in Browser
We can request three types of weather values:
- summary: values like 'Overcast' or 'Clear' or 'Mostly Cloudy'
- temperature: values like 59.5 or 17.
- icon: values like cloudy or rain or partly-cloudy-day.
There is in fact a great deal more data in the Dark Sky result set that we could enable, but this will suffice for our example.
Connecting the Database to the Azure Function: A CLR Function
Now that we have our Azure Function for weather, by what magic will we make it possible for a SQL Server query to invoke it? The answer to that lies in the ability to write CLR (.NET) functions for SQL Server. We'll get to the code in a moment, but what we'll be building adds a new function to a SQL Server database named weather. Weather takes three parameters: latitude, longitude, and property name. This will permit queries like the following from a local SQL Server database:
select Result from weather('41.8781', '-87.6298', 'temperature')
Here's a diagram of how the pieces connect. The CLR SQL Server function is invoked by SQL queries, and it invokes our Azure Function for weather via HTTP. The Azure Function in turn retrieves and returns weather data from the weather API.
Let's take a look at the code to our CLR function. To work on CLR user-defined functions, you'll need to get acquainted with SQL Server data types like SqlString, the use of yield to return record values, and Fill Method functions to package enumeration results. You can read up on that here.
Components Involved in our Weather Example
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using System.Net;
using System.IO;
using System.Security.Cryptography;
public partial class UserDefinedFunctions
{
private const int DEFAULT_TIMEOUT = 1000 * 60 * 15; // Default timeout for web communication
#region weather
// weather : invoke Azure Function to retrieve a weather value and return nvarchar(100) result
//
// Inputs: latitude ............ latitude
// longitude ........... longitude
// prop ................ property to return: summary | temperature | icon
//
// Outputs: Result .............. requested value
[Microsoft.SqlServer.Server.SqlFunction(
FillRowMethodName = "FillMethodString",
TableDefinition = "Result NVARCHAR(200)"
)]
public static IEnumerable weather(String latitude, String longitude, String prop = "temperature")
{
String reply = null;
try
{
using (WebClientEx client = new WebClientEx())
{
client.Timeout = DEFAULT_TIMEOUT;
client.Encoding = System.Text.Encoding.UTF8;
String url = "http://weather-city.azurewebsites.net/api/HttpTrigger1?code=...code-to-your-function....&lat=" + latitude + "&long=" + longitude + "&prop=" + prop;
reply = client.DownloadString(url);
}
}
catch (Exception ex)
{
reply = ex.Message;
}
yield return new object[]
{
(SqlString)reply
};
}
#endregion
#region Fill Methods
// FillMethodString - function will be called from SQL Server to get a string result
// for current element from enumerator
public static void FillMethodString(object current,
out SqlString Result
)
{
// set return information from current item
object[] data = (object[])current;
Result = (SqlString)data[0];
}
#endregion
#region Value Converters
private static SqlString StringValue(String text)
{
if (String.IsNullOrEmpty(text))
{
return SqlString.Null;
}
return text;
}
private static SqlBoolean BooleanValue(String text)
{
if (String.IsNullOrEmpty(text))
{
return SqlBoolean.Null;
}
switch (text.ToLower())
{
case "1":
case "true":
return SqlBoolean.True;
default:
return SqlBoolean.False;
}
}
private static SqlDecimal DecimalValue(String text)
{
if (String.IsNullOrEmpty(text))
{
return SqlDecimal.Null;
}
try
{
return (SqlDecimal)Convert.ToDecimal(text);
}
catch(Exception)
{
return SqlDecimal.Null;
}
}
private static SqlInt32 IntegerValue(String text)
{
if (String.IsNullOrEmpty(text))
{
return SqlInt32.Null;
}
try
{
return (SqlInt32)Convert.ToInt32(text);
}
catch (Exception)
{
return SqlInt32.Null;
}
}
private static SqlDateTime DateTimeValue(String text)
{
if (String.IsNullOrEmpty(text))
{
return SqlDateTime.Null;
}
try
{
return (SqlDateTime)Convert.ToDateTime(text);
}
catch (Exception)
{
return SqlDateTime.Null;
}
}
#endregion
#region Communication
// Perform a GET url and return the response string.
public static String GetUrl(String url, int timeout = DEFAULT_TIMEOUT)
{
HttpWebRequest request = HttpWebRequest.Create(url) as HttpWebRequest;
request.Method = "GET";
request.ContentType = "text/xml; encoding='utf-8'";
request.Timeout = timeout;
HttpWebResponse response = request.GetResponse() as HttpWebResponse;
StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.UTF8);
return reader.ReadToEnd();
}
#endregion
// Extension to WebClient that has a Timeout property.
public class WebClientEx : WebClient
{
public int Timeout { get; set; }
protected override WebRequest GetWebRequest(Uri address)
{
var request = base.GetWebRequest(address);
request.Timeout = Timeout;
return request;
}
}
}
CLR weather Function for SQL Server
Before publishing, there's a one-time setup needed on your SQL Server database: you must grant permissions for CLR functions (and get affirmation this is okay with your IT department). That's done as follows in SQL Server Management Studio:
sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
Publishing CLR Function to Database
With the function published to your database, you can now enjoy invoking your Azure Function right from a SQL query. Fire up SQL Server Management Studio and try queries like these:
With your Azure Function available in SQL, you're free to combine it with database table data. For example, if we define a table named Location with Name, Latitude, and Longitude, then we can do queries like this:
SELECT Name, Latitude, Longitude,
(select Result from weather(loc.Latitude, loc.Longitude, 'icon')) AS Icon,
(select Result from weather(loc.Latitude, loc.Longitude, 'summary')) AS Conditions,
(select Result from weather(loc.Latitude, loc.Longitude, 'temperature')) AS Temperature
FROM Location loc
Now, our example is a simple one: the Azure Function is only returning a single column of data per call, and that isn't particularly efficient. However, CLR SQL Server functions are capable of returning entire rowsets of data so it's entirely possible to be returning multiple columns and mulitple rows. The possibilites are endless.
Building your solution with functions in the processing chain gives you maximum flexibility. Down the road when requirements and technologies change, the use of functions will preserve your ability to quickly pivot should you need to without being disruptive.
Azure Functions make excellent gateways to cloud assets from the enterprise, but enterprise systems need natural ways to access these functions. The technique we looked at today allows SQL Server databases to query Azure functions in a natural way.
Invoking Azure Function in SQL Queries
With your Azure Function available in SQL, you're free to combine it with database table data. For example, if we define a table named Location with Name, Latitude, and Longitude, then we can do queries like this:
SELECT Name, Latitude, Longitude,
(select Result from weather(loc.Latitude, loc.Longitude, 'icon')) AS Icon,
(select Result from weather(loc.Latitude, loc.Longitude, 'summary')) AS Conditions,
(select Result from weather(loc.Latitude, loc.Longitude, 'temperature')) AS Temperature
FROM Location loc
Database data and Azure Function data integrated
Now, our example is a simple one: the Azure Function is only returning a single column of data per call, and that isn't particularly efficient. However, CLR SQL Server functions are capable of returning entire rowsets of data so it's entirely possible to be returning multiple columns and mulitple rows. The possibilites are endless.
Why Use a Function?
If you've been paying attention to this exercise, you may be asking yourself if the Azure Function was really a necessary component in this exercise. After all, the CLR SQL Server function is doing a web request, and the weather data is available in a web API. While it's true in this specific case that we could have bypassed the Azure Function, that would miss the broader point: functions are versatile, and they are gateways to access your assets in the cloud--such as a cloud database or other data repositories. You want functions in place wherever the pattern of processing or source of data might conceivably change. For exampe, maybe you want the weather data cached for a certain amount of time to avoid exceeding an API threshold; or perhaps you'll eventually change data providers. Your function should worry about those details and nothing else should have to.Building your solution with functions in the processing chain gives you maximum flexibility. Down the road when requirements and technologies change, the use of functions will preserve your ability to quickly pivot should you need to without being disruptive.
In Conclusion
It's gratifying to see how strongly cloud computing is now being embraced, but enterprises will continue to have local systems and assets for a good long time, along with the need to connect them to applications and data in the cloud.Azure Functions make excellent gateways to cloud assets from the enterprise, but enterprise systems need natural ways to access these functions. The technique we looked at today allows SQL Server databases to query Azure functions in a natural way.
No comments:
Post a Comment