Home » Azure Machine Learning » Using Azure Machine Learning with an on-premises Database
14
May 2017

Using Azure Machine Learning with an on-premises Database

Enrico
Azure Machine Learning
41 comments

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:

AML_DMG_140517_01

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.

AML_DMG_140517_02

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).

AML_DMG_140517_03

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.

AML_DMG_140517_04

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.

AML_DMG_140517_05

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:

AML_DMG_140517_06

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:

AML_DMG_140517_07

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.

AML_DMG_140517_08

We can now click the “V” button to save and close the configuration of the Data Gateway and our Data Gateway should be added:

AML_DMG_140517_09

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.

AML_DMG_140517_10

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.

AML_DMG_140517_11

The next step is to select the Data Gateway we configured in the previous steps:

AML_DMG_140517_12

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:

AML_DMG_140517_13

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.

AML_DMG_140517_14

AML_DMG_140517_15

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.

AML_DMG_140517_16

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:

AML_DMG_140517_17

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.

AML_DMG_140517_18

AzureML will return a result set of the data it has imported into the AzureML workspace:

AML_DMG_140517_19

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.

AML_DMG_140517_20

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!

41 comments to “Using Azure Machine Learning with an on-premises Database”

You can leave a reply or Trackback this post.
  2. Moustafa says: January 1, 2019 at 16:02

    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.

    Reply
  3. hackaday.io says: March 25, 2021 at 05:12

    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?

    Reply
  5. www.newlivecode.info says: March 25, 2021 at 19:32

    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.

    Reply
  6. www.wanelo.com says: March 25, 2021 at 20:34

    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

    Reply
  9. //lesliewelling.contently.com/ says: March 29, 2021 at 22:11

    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!

    Reply
  12. review says: March 31, 2021 at 19:23

    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!

    Reply
  13. adultf friend finder says: April 1, 2021 at 05:39

    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.

    Reply
  14. 먹튀 says: April 5, 2021 at 05:25

    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!

    Reply
  16. his response says: April 11, 2021 at 16:47

    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

    Reply
  17. check my site says: April 12, 2021 at 02:55

    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.

    Reply
  20. คาสิโนออนไลน์ says: May 14, 2021 at 14:39

    Definitely consider that that you stated. Your favorite justification appeared to be at the net the easiest thing to keep in mind of.
    I say to you, I certainly get irked whilst other folks think about concerns that
    they just do not recognize about. You managed to hit the nail upon the top and also outlined
    out the entire thing with no need side effect , people can take a signal.
    Will likely be again to get more. Thanks //ufabaccat.com

    Reply
  24. kèo nhà cái says: May 28, 2021 at 19:16

    Aw, tһіs was a vｅry gоod post. Τaking thе time and actual
    effort to ｃreate a good article… Ƅut what can I ѕay… I procrastinate a lot ɑnd neveг manage to get anything dоne.

    Reply
  26. Jarrett says: June 8, 2021 at 16:38

    You really make itt seem really easy together with your presentation however I to find this topic to be really something
    that I feel I’d never understand. It seems too complex and very wide for me.
    I’m looking ahead on your subsequent post, I will attempt too get the cling of
    it!

    Reply
  28. Clarke says: June 20, 2021 at 00:26

    I havce seen lots of useful factors on your web-site about persona computers.
    However, I have ggot the opinion that lap tops are still less than powerful enough to be a good option if you generally do
    projects that require plenty oof power, for example vdeo enhancing.
    But for world-wide-web surfing, microsoft word processing, and most other typical computer work they are just great,
    provided you do not mind the screen size. Many thanks sharig your thinking.

    Reply
  31. Vay vốn tín chấp TPBank says: August 1, 2021 at 04:05

    naturally like your web-site however you have to test the spelling on quite a few of your posts.
    A number of them are rife with spelling problems and I to find it very
    bothersome to tell the reality however I will definitely come again again.

    Reply
  34. git.qt.io says: September 12, 2021 at 12:13

    Great post. I was checking constantly this blog and I’m inspired!
    Very useful info specially the closing phase :) I maintain such info a lot.

    I was looking for this particular info for a very lengthy time.
    Thanks and best of luck.

    Reply

Write a Reply or Comment

Your email address will not be published.