Reading from Database
Now that we have data in our database let's read from it. This code will be similar than what we used to write to the database by using the SqlConnection class again. In fact, the only differences is the query we send to it and how we read in the data.
We do need a variable to add each row to, though, so we can create a new List of WineData objects.
var data = new List<WineData>();
Within the SqlConnection we can create a select statement that will return all of the columns and execute it with the ExecuteReader function. This returns a SqlDataReader object and we can use that to extract out the data.
In a while loop, which checks that the reader can read the next row, use the List variable created earlier to add a new instance of the WineData object to it and we can map from the reader to the object using the reader.GetValue method. The GetValue parameter will be the column position and then we'll do a ToString on it. Note that we need the Parse method from above again here to parse the strings into a float.
using (var conn = new SqlConnection(_sqlConectionString))
{
conn.Open();
var selectCmd = "SELECT * FROM dbo.WineData";
var sqlCommand = new SqlCommand(selectCmd, conn);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
data.Add(new WineData
{
Type = reader.GetValue(0).ToString(),
FixedAcidity = Parse(reader.GetValue(1).ToString()),
VolatileAcidity = Parse(reader.GetValue(2).ToString()),
CitricAcid = Parse(reader.GetValue(3).ToString()),
ResidualSugar = Parse(reader.GetValue(4).ToString()),
Chlorides = Parse(reader.GetValue(5).ToString()),
FreeSulfurDioxide = Parse(reader.GetValue(6).ToString()),
TotalSulfurDioxide = Parse(reader.GetValue(7).ToString()),
Density = Parse(reader.GetValue(8).ToString()),
Ph = Parse(reader.GetValue(9).ToString()),
Sulphates = Parse(reader.GetValue(10).ToString()),
Alcohol = Parse(reader.GetValue(11).ToString()),
Quality = Parse(reader.GetValue(12).ToString())
});
}
}
Creating the Model
Now that we have our data from the database, let's use it to create an ML.NET model.
First thing, though, let's create an instance of the MLContext.
var context = new MLContext();
We can use the LoadFromEnumerable helper method to load the IEnumerable data that we have into the IDataView that ML.NET uses. In previous versions of ML.NET this used to be called ReadFromEnumerable.
var mlData = context.Data.LoadFromEnumerable(data);
Now that we have the IDataView we can use that to split the data into a training set and test set. In previous versions of ML.NET this returned a tuple and it could be deconstructed into two variables (var (trainSet, testSet) = ...), but now it returns an object.
var testTrainSplit = context.Regression.TrainTestSplit(mlData);
With the data set up, we can create the pipeline. The two main things to do here is to set up the Type feature, which denotes if the wine is red or white, as one hot encoded. Then we concatenate each of the other features into a feature array. We'll use the FastTree trainer and since our label column isn't named "Label", we set the labelColumnName parameter to the name of the label we want to predict, which is "Quality".
var pipeline = context.Transforms.Categorical.OneHotEncoding("TypeOneHot", "Type")
.Append(context.Transforms.Concatenate("Features", "FixedAcidity", "VolatileAcidity", "CitricAcid",
"ResidualSugar", "Chlorides", "FreeSulfurDioxide", "TotalSulfurDioxide", "Density", "Ph", "Sulphates", "Alcohol"))
.Append(context.Regression.Trainers.FastTree(labelColumnName: "Quality"));
With the pipeline created, we can now call the Fit method on it with our training data.
var model = pipeline.Fit(testTrainSplit.TrainSet);
Save Model
With our new model, let's save it to Azure Blob Storage so we can retrieve it to build an API around the model.
To start, we'll use the connection string that we put in the config earlier. We then pass that into the Parse method of the CloudStorageAccount class.
var storageAccount = CloudStorageAccount.Parse(configuration["blobConnectionString"]);
With a reference to the storage account, we can now use that to create a client and use the client to create a reference to the container that we will call "models". This container will need to be created in the storage account, as well.
var client = storageAccount.CreateCloudBlobClient();
var container = client.GetContainerReference("models");
With the container reference, we can create a blob reference to a file, which we created earlier as a field.
var blob = container.GetBlockBlobReference(fileName);
To save the model to a file, we can create a file stream using File.Create and inside the stream we can call the context.Model.Save method.
using (var stream = File.Create(fileName))
{
context.Model.Save(model, stream);
}
And to upload the file to blob storage, just call the UploadFromFileAsync method. Note that this method is async, so we need to mark the containing method as async and add await in front of this method.
await blob.UploadFromFileAsync(fileName);
After running this, there should now be a file added to blob storage.
