Using Azure Machine Learning with an on-premises Database
With Azure Machine Learning (AzureML) you have access to a cloud based, flexible and friendly method to perform machine learning tasks on your data. One disadvantage I frequently run into is that cloud based approach of AzureML since the data you are building your machine learning models on has to be in the cloud as well. Even though AzureML offers a variety of ways to access your data, from CSVs to Azure Blob Storage and Azure SQL Database, having to store data in the cloud is one of the major drawbacks me, and some of my clients, run into.
But there is good news! The Microsoft Data Management Gateway! The Data Management Gateway acts like a bridge between AzureML and your on-premises SQL Server databases allowing you to import data directly from a local database!
Let’s take a look at how you can use the Data Management Gateway to build a machine learning experiment using on-premises data inside AzureML!
Local SQL Server Configuration
Since we plan on using data stored inside SQL Server on-premises, I used the AdventureWorksDW database as the database we are importing data from inside AzureML. You can download the AdventureWorksDW database here: //msftdbprodsamples.codeplex.com/.
The way we will import data inside AzureML through the Data Management Gateway is based on a query. For this example I went with the query below that returns information from the AdventureWorksDW database about characteristics of people who bought bikes from the AdventureWorks company.
SELECT CustomerKey, GeographyKey, CustomerAlternateKey, MaritalStatus, Gender, CAST (YearlyIncome AS INT) AS SalaryYear, TotalChildren, NumberChildrenAtHome, EnglishEducation, EnglishOccupation, HouseOwnerFlag, NumberCarsOwned, CommuteDistance, Region, Age, BikeBuyer FROM vTargetMail
Running the query above on my local SQL Server databases returns the following results and returns a total of 18.484 rows:
Setting up the Data Management Gateway
The next step is to configure the Data Management Gateway so we can access the results of the query below inside AzureML. The Data Management Gateway configuration is stored inside AzureML Studio, underneath the “Settings” page.
To create a new Data Gateway simply click the “New Data Gateway” button at the bottom and a pop-up should appear (make sure you allow pop-ups from the AzureML Studio page else some of the configuration pages might get blocked).
Here we can specify a name for the Data Gateway and provide a description. In this case I went for “AzureML_On-Premises” as the Gateway name and clicked the next page button.
This configuration page is actually the most important one as it shows the Gateway Registration Key we need to tie the Data Gateway in AzureML together with the local Data Management Gateway. In the screenshot above I intentionally removed the registration key.
From this page we can directly download the Data Management Gateway software. We need to install the Data Management Gateway on an on-premises machine. This does not necessarily have to be the same machine we are importing our data from, but for this article I installed it on the same machine where my SQL Server Instance resides.
You can click the link in the Data Gateway page in AzureML or use the following link to download the Data Management Gateway software: //go.microsoft.com/fwlink/?LinkId=271260.
The installation of the Data Management Gateway is a pretty straightforward next, next, finish.
After installation finishes you will be presented the Microsoft Data Management Gateway Configuration Manager page:
This is where we enter the gateway key we received when adding the new Data Gateway inside AzureML. Copy/paste the code from AzureML inside the input box and click “Register”.
If all went well the Data Management Gateway will be linked to the Data Gateway in AzureML:
If we return to the Data Gateway page inside AzureML we should see an updated page as well, indicating that the connection between the Data Gateway and the on-premises Data Management Gateway has been registered.
We can now click the “V” button to save and close the configuration of the Data Gateway and our Data Gateway should be added:
Now that we have our gateway configured and connected we can access our on-premises database directly from AzureML!
Importing on-premises data from AzureML
Let’s start by creating a new experiment in AzureML Studio. The way we access our on-premises databases is through the Import Data module. Simply drag the Import Data module into the AzureML experiment canvas, the properties of the Import Data module will appear on the right.
To make use of the gateway we created we will have to configure some setting of the Import Data module.
First of all we will have to change the Data source to “On-Premises SQL Data” as show in the image below.
The next step is to select the Data Gateway we configured in the previous steps:
After that we need to provide the name of the SQL Server our database resides on, as well as providing the name of the database:
Then we need to provide the credentials AzureML will use when connecting to your on-premises databases. Simply click the “Enter values” button and the Credential Manager application will start.
In this case I selected “SQL Server Authentication” and provided a username and password of a SQL Server user I created that has read permissions on the AdventureWorksDW databases.
Click “OK” to save this information inside the AzureML experiment.
The final step is to provide a query that will be used to retrieve the data from your on-premises database. For this example I used the query at the top of this article.
In this case I left the “Use cached results” checkbox unchecked. This means that every time I run the experiment AzureML will access my on-premises database to retrieve the data. If you do check the checkbox the results are cached inside AzureML.
Right now the properties of the Import Data module should look something like this:
Basically we are all done now with the configuration, save and run the experiment!
After the experiment finished running we are able to see what data AzureML has imported, click on the Import Data module and select the “Visualize” option.
AzureML will return a result set of the data it has imported into the AzureML workspace:
As you can see from the figure above, all of the information I showed you at the top of this article by running the query locally is there and a total of 18.484 rows is returned, just like the locally executed query!
Now that we have our on-premises data in AzureML we can continue with using the data to build a machine learning experiment! And that’s basically how easy it is to configure AzureML to use data inside an on-premises SQL Server database.
Additional configuration
The steps above show the quick and easy method you can use to access on-premises database data inside AzureML. There are some optional settings for the Data Management Gateway you might want to consider changing if you plan on using this for your production data. For instance, by default a self-signed certificate is used to encrypt the communication between the gateways. For a more secure solution you might want to change it to a valid certificate. You can also choose to configure a proxy for the communication to AzureML if you want.
All of these settings can be configured inside the Microsoft Data Management Gateway Configuration Manager on the machine where you installed the Data Management Gateway.
Conclusion
Having the option to use data stored in on-premises databases inside AzureML is a very nice addition to AzureML for those people who do not have everything stored inside the cloud yet. It is easy and quick to configure and allows you to use the power of the cloud-based AzureML without having to move all the data to the cloud first!
20 comments to “Using Azure Machine Learning with an on-premises Database”
But, it’s a superb range between being extreme and being
fully a loose canon.
A very nice article; however, I would like to enquire about data privacy. How can I train my model locally on my machine using the power of Azure but without uploading my training data.
Thanks for the good writeup. It in truth was a entertainment account it.
Look complex to far delivered agreeable from you!
By the way, how could we keep in touch?
Hello to every one, the contents existing at this website are really remarkable for people experience, well,
keep up the nice work fellows.
I am no longer positive the place you’re getting your info, but great topic.
I must spend a while finding out more or figuring out more.
Thank you for magnificent info I used to be looking for this information for my mission.
Great beat ! I wish to apprentice while you amend your web site, how
can i subscribe for a blog web site? The account helped me a acceptable deal.
I were a little bit familiar of this your broadcast provided bright clear idea
It’s remarkable to visit this web site and reading the views of all colleagues on the topic of this post, while I am also keen of getting know-how.
Marvelous, what a webpage it is! This blog provides valuable
information to us, keep it up.
What i don’t realize is in fact how you’re not really a lot
more well-liked than you might be now. You’re very intelligent.
You know thus considerably on the subject of this matter,
made me for my part believe it from a lot of various angles.
Its like men and women don’t seem to be fascinated except it is one thing to accomplish with Girl gaga!
Your individual stuffs excellent. At all times
handle it up!
Because the admin of this website is working, no doubt very soon it will be famous, due to its feature contents.
Good answer back in return of this query with genuine arguments and telling the whole thing about that.
Thanks for a marvelous posting! I definitely enjoyed reading it,
you happen to be a great author.I will make certain to bookmark
your blog and will come back at some point. I want to encourage you continue your great posts, have a nice morning!
May I just say what a relief to discover a person that
actually understands what they are discussing on the internet.
You definitely understand how to bring a problem to light
and make it important. More people have to look at this
and understand this side of the story. It’s surprising
you’re not more popular given that you certainly
possess the gift.
The other day, while I was at work, my cousin stole my apple ipad and tested to see if it can survive a thirty foot drop,
just so she can be a youtube sensation. My iPad is now
destroyed and she has 83 views. I know this is entirely
off topic but I had to share it with someone!
Hello, I wish for to subscribe for this weblog to take
hottest updates, thus where can i do it please assist.
I think that is among the so much significant information for me.
And i am satisfied reading your article. However should
statement on some general things, The website taste is perfect, the articles is in reality great :
D. Just right job, cheers
Link exchange is nothing else but it is just placing the other person’s webpage link on your page
at suitable place and other person will also do similar for
you.
Some are run by Mafia associates or are aspect of larger criminal enterprises.
Sports betting was legalized in June 2019 with the passage of SB 690, which permits each retail and on line
sports betting.