Call Azure REST API from SQL Server
Microsoft Azure platform can be managed in a number of ways. You can use PowerShell, you can use the Management Portal or you can use the http REST interface. If you happen to manage a lot of Azure items (containers, blobs, etc…) you might have to do some serious PowerShelling. But would not be wonderful to be able to control Azure using our beloved TSQL? In this post I will show you how easy is to wrap the Azure REST API in a SQLCLR dll in order to query Azure using TSQL.
First thing first: let me show you what we want to achieve in this sample (bear in mind this is just sample code to get you started). Suppose we have an Azure storage account; we want to find all the unused blobs in all its containers.
Ideally – as DBAs – we want to have a function that would query Azure for us and give us the results in a table. Something like:
SELECT * FROM Azure.ListContainersTVF('storageaccount', 'yoursharedkey');
With another function we want to query the blobs in a container:
SELECT * FROM Azure.ListBlobsTVF('storageaccount', 'yoursharedkey', 'container');
Cool. With these two functions alone we can solve our problem. The CROSS APPLY statement is just for that:
DECLARE @storageAccount NVARCHAR(255) = 'storageaccount';
DECLARE @sharedKey NVARCHAR(800) = 'yoursharedkey';
SELECT * FROM Azure.ListContainersTVF(@storageAccount, @sharedKey) C
CROSS APPLY Azure.ListBlobsTVF(@storageAccount, @sharedKey, C.Name) B
WHERE B.LastModified > DATEADD(day, -30, GETDATE())
ORDER BY B.ContentLength DESC;
We expect to find all the blobs not modified in the last 30 days, sorted by size:
It’s a dream you might say :). In fact it’s just CLR table-valued function built around a C# DLL. Obviously, our assembly will need the external access security level to call the REST API (http/https). We can do it in two ways: using a asymmetric key bound login (preferred way) or setting the TRUSTWORTHY attribute on the database that will load the assembly.
Let’s say we choose the first approach. This comes down to:
USE [master];
GO
CREATE ASYMMETRIC KEY SampleAzureKey FROM EXECUTABLE FILE ='C:\SampleAzure.dll';
CREATE LOGIN SampleAzureLogin FROM ASYMMETRIC KEY SampleAzureKey;
GRANT EXTERNAL ACCESS ASSEMBLY TO SampleAzureLogin;
GO
USE [YourCLRDb];
GO
CREATE ASSEMBLY SampleAzure FROM 'C:\SampleAzure.dll' WITH PERMISSION_SET=EXTERNAL_ACCESS;
GO
Notice the GRANT EXTERNAL ACCESS ASSEMBLY privilege granted to our asymmetric key login.
Once we have the assembly loaded we just need to wrap the C# functions to a TVF. It’s just a matter of standard T-SQL stuff (some return nodes of the List Blobs REST API are left off for brevity):
CREATE FUNCTION Azure.ListBlobsTVF (@AccountName NVARCHAR(255), @SharedKey NVARCHAR(255), @Container NVARCHAR(255))
RETURNS TABLE(
Name NVARCHAR(MAX), Url NVARCHAR(MAX), LastModified DATETIME, Etag NVARCHAR(255), ContentLength BIGINT,
ContentType NVARCHAR(255), ContentEncoding NVARCHAR(255), ContentLanguage NVARCHAR(255), ContentMD5 NVARCHAR(255),
BlobType NVARCHAR(255), LeaseStatus NVARCHAR(255), LeaseState NVARCHAR(255), CopyId UNIQUEIDENTIFIER,
CopySource NVARCHAR(MAX), CopyStatus NVARCHAR(MAX))
AS EXTERNAL NAME [SampleAzure].[SampleAzure.Sample].ListBlobsTVF;
GO
CREATE FUNCTION Azure.ListContainersTVF (@AccountName NVARCHAR(255), @SharedKey NVARCHAR(255))
RETURNS TABLE(
Name NVARCHAR(MAX), Url NVARCHAR(MAX), LastModified DATETIME, Etag NVARCHAR(255), LeaseStatus NVARCHAR(255), LeaseState NVARCHAR(255), Metadata NVARCHAR(MAX))
AS EXTERNAL NAME [SampleAzure].[SampleAzure.Sample].ListContainersTVF;
GO
Note: you might need to create the Azure schema first with:
CREATE SCHEMA Azure;
GO
That’s just about it. Oh, wait, you still need the DLL :). I won’t be discussing all the code here because it’s too long, I’ll just attach the sourcecode. But let me show a TVF here (credit for the Azure helper class here: http://code.msdn.microsoft.com/windowsazure/CSAzureStorageRESTAPI-2b9055fb/sourcecode?fileId=21643&pathId=1953904287).
[SqlFunction(
TableDefinition = " Name NVARCHAR(MAX), Url NVARCHAR(MAX), LastModified DATETIME, Etag NVARCHAR(255), ContentLength BIGINT, ContentType NVARCHAR(255), ContentEncoding NVARCHAR(255), ContentLanguage NVARCHAR(255), ContentMD5 NVARCHAR(255), BlobType NVARCHAR(255), LeaseStatus NVARCHAR(255), LeaseState NVARCHAR(255), CopyId UNIQUEIDENTIFIER, CopySource NVARCHAR(MAX), CopyStatus NVARCHAR(MAX)",
Name = "ListBlobsTVF",
FillRowMethodName = "FillRowListBlobsTVF")]
public static System.Collections.IEnumerable ListBlobsTVF(string accountName, string sharedKey, string container)
{
System.Xml.XmlNode n = null;
List<AzureBlob> lAzureBlobs = new List<AzureBlob>();
string strMarker = null;
while (true)
{
SqlXml list = ListBlobs(accountName, sharedKey, container, strMarker);
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
using (System.Xml.XmlReader reader = list.CreateReader())
{
doc.Load(reader);
}
#region Add blobs
System.Xml.XmlNodeList nList = doc.SelectNodes("EnumerationResults/Blobs/Blob");
Helper.Log("nList.Count == " + nList.Count);
foreach (System.Xml.XmlNode nContainer in nList)
{
AzureBlob ab = null;
lAzureBlobs.Add(ab = new AzureBlob()
{
Name = nContainer.SelectSingleNode("Name").InnerText,
Url = nContainer.SelectSingleNode("Url").InnerText,
LastModified = DateTime.Parse(nContainer.SelectSingleNode("Properties/Last-Modified").InnerText),
Etag = nContainer.SelectSingleNode("Properties/Etag").InnerText,
ContentLength = long.Parse(nContainer.SelectSingleNode("Properties/Content-Length").InnerText),
ContentType = nContainer.SelectSingleNode("Properties/Content-Type").InnerText,
ContentEncoding = nContainer.SelectSingleNode("Properties/Content-Encoding").InnerText,
ContentLanguage = nContainer.SelectSingleNode("Properties/Content-Language").InnerText,
ContentMD5 = nContainer.SelectSingleNode("Properties/Content-MD5").InnerText,
BlobType = nContainer.SelectSingleNode("Properties/BlobType").InnerText,
LeaseStatus = nContainer.SelectSingleNode("Properties/LeaseStatus").InnerText,
LeaseState = nContainer.SelectSingleNode("Properties/LeaseState").InnerText,
CopySource = nContainer.SelectSingleNode("Properties/CopySource").InnerText,
CopyStatus = nContainer.SelectSingleNode("Properties/CopyStatus").InnerText
});
string strCopyId = nContainer.SelectSingleNode("Properties/CopyId").InnerText;
if (string.IsNullOrEmpty(strCopyId))
ab.CopyId = Guid.Empty;
else
ab.CopyId = Guid.Parse(strCopyId);
}
#endregion
n = doc.ChildNodes[0];
n = doc.SelectSingleNode("EnumerationResults/NextMarker");
strMarker = n.InnerText;
if (string.IsNullOrEmpty(strMarker))
break;
}
return lAzureBlobs;
}
Here there are three things worth noting. First, many Azure REST methods might return you a subset of the result. In that case you are required to call the method again adding a marker. This way the next recordset will be the continuation of the previous one. In our sample code we just keep calling the same method until the EnumerationResults/NextMarker node is empty. You can read more about it here: http://msdn.microsoft.com/en-us/library/windowsazure/dd135734.aspx. The documentation says:
Note that the value of NextMarker should be treated as opaque.
That is why we should not expose it to the callers.
Second is the decoration needed to instruct the SQLCLR how to compose the TVF. You need to specify the FillRowMethodName attribute. It must point to a static method that will be called for each item in your IEnumerable result. Here is the – simplified – sequence diagram:
Notice how your DLL gets called for each row. The “Populate output parameters” in our case is very simple:
public static void FillRowListBlobsTVF(Object obj,
out SqlString name,
out SqlString url,
out SqlDateTime lastModified,
out SqlString etag,
out System.Data.SqlTypes.SqlInt64 contentLength,
out SqlString ContentType,
out SqlString ContentEncoding,
out SqlString ContentLanguage,
out SqlString ContentMD5,
out SqlString BlobType,
out SqlString LeaseStatus,
out SqlString LeaseState,
out SqlGuid CopyId,
out SqlString CopySource,
out SqlString CopyStatus
)
{
AzureBlob ab = (AzureBlob)obj;
name = ab.Name;
url = ab.Url;
lastModified = ab.LastModified;
contentLength = ab.ContentLength;
etag = ab.Etag;
ContentType = (string.IsNullOrEmpty(ab.ContentType)) ? null : ab.ContentType;
ContentEncoding = (string.IsNullOrEmpty(ab.ContentEncoding)) ? null : ab.ContentEncoding;
ContentLanguage = (string.IsNullOrEmpty(ab.ContentLanguage)) ? null : ab.ContentLanguage;
ContentMD5 = (string.IsNullOrEmpty(ab.ContentMD5)) ? null : ab.ContentMD5;
BlobType = ab.BlobType;
LeaseStatus = ab.LeaseStatus;
LeaseState = ab.LeaseState;
CopyId = ab.CopyId;
CopySource = (string.IsNullOrEmpty(ab.CopySource)) ? null : ab.CopySource;
CopyStatus = (string.IsNullOrEmpty(ab.CopyStatus)) ? null : ab.CopyStatus;
}
Third point to note is the CopyStatus field. In Azure you can start an asynchronous blob copy job: it’s very efficient (read more here: http://msdn.microsoft.com/en-us/library/windowsazure/dd894037.aspx). By querying this field you can check the progress of an asynchronous copy operation easily.
The code calls this method responsible of the Azure REST API call:
[SqlFunction]
public static System.Data.SqlTypes.SqlXml ListBlobs(string accountName, string sharedKey, string container, string marker)
{
string Address = string.Format("http://{0:S}.blob.core.windows.net", accountName);
string QueryString = "?restype=container&comp=list&include=metadata,snapshots,uncommittedblobs,copy";
if (!string.IsNullOrEmpty(marker))
{
QueryString += "&marker=" + marker;
}
Uri requesturi = new Uri(Address + "/" + container + QueryString);
string ret = _CallGETMethod(accountName, sharedKey, requesturi.ToString());
using (System.Xml.XmlTextReader XmlReader = new System.Xml.XmlTextReader(new System.IO.StringReader(ret)))
{
return new SqlXml(XmlReader);
}
}
As you can see it’s just a procedure (and a SqlFunction) that composes the Azure REST get request along with its required headers. Being decorated we can also call it in SQL Server:
CREATE FUNCTION Azure.ListBlobs (@AccountName NVARCHAR(255), @SharedKey NVARCHAR(255), @Container NVARCHAR(255), @Marker NVARCHAR(400))
RETURNS XML EXTERNAL NAME [SampleAzure].[SampleAzure.Sample].ListBlobs;
GO
This is a scalar function so we call it like this:
SELECT Azure.ListBlobs('storage', 'sharedkey', 'container', NULL);
And it gives you the XML in return:
Below you will find the C# source code to get you started. Remember, this code is just a sample written for educational purposes. Of course there are many methods in the Azure REST API but with little effort you can automate most tasks using your easier-to-understand T-SQL Code.
Happy Coding,