![\"Importpng\"](\"https:\/\/raw.githubusercontent.com\/kywe665\/MLServicesTutorials\/master\/images\/RemoteCompute.png\" "\\"New")
<\/p>\n
Introduction<\/h1>\n
Did you know that you can execute R and Python code remotely in SQL Server from Jupyter Notebooks or any IDE? Machine Learning Services<\/a> in SQL Server eliminates the need to move data around. Instead of transferring large and sensitive data over the network or losing accuracy on ML training with sample csv files, you can have your R\/Python code execute within your database. You can work in Jupyter Notebooks, RStudio, PyCharm, VSCode, Visual Studio, wherever you want, and then send function execution to SQL Server bringing intelligence to where your data lives.<\/p>\n This tutorial will show you an example of how you can send your python code from Juptyter notebooks to execute within SQL Server. The same principles apply to R and any other IDE as well. If you prefer to learn through videos, this tutorial is also published on YouTube.<\/p>\n https:\/\/youtu.be\/D5erljpJDjE<\/a><\/p>\n In order for R or Python to execute within SQL, you first need the Machine Learning Services feature installed and configured. See this how-to guide<\/a>.<\/p>\n In order to send Python execution to SQL from Jupyter Notebooks, you need to use Microsoft’s RevoscalePy package. To get RevoscalePy, download and install Microsoft’s ML Services Python Client. Documentation Page<\/a> or Direct Download Link<\/a> (for Windows).<\/p>\n After downloading, open powershell as an administrator and navigate to the download folder. Start the installation with this command (feel free to customize the install folder):<\/p>\n Be patient while the installation can take a little while. Once installed navigate to the new path you installed in. Let’s make an empty folder and open Jupyter Notebooks:<\/p>\n Create a new notebook with the Python 3 interpreter:<\/p>\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0 To test if everything is setup, import revoscalepy in the first cell and execute. If there are no error messages you are ready to move forward.<\/p>\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 For the rest of the tutorial you can clone this Jupyter Notebook from Github<\/a> if you don’t want to copy paste all of the code. This database setup is a one time step to ensure you have the same data as this tutorial. You don’t need to perform any of these setup steps to use your own data.<\/p>\n Modify the connection string for your server and use pyodbc to create a new database.<\/p>\n Iris is a popular dataset for beginner data science tutorials. It is included by default in sklearn package.<\/p>\n (You can also do this with pyodbc, sqlalchemy or other packages)<\/em><\/p>\n Write any python code you want to execute in SQL. In this example we are creating a scatter matrix on the iris dataset and only returning the bytestream of the .png back to Jupyter Notebooks to render on our client.<\/p>\n Now that we are finally set up, check out how easy sending remote execution really is! First, While this example is trivial with the Iris dataset, imagine the additional scale, performance, and security capabilities that you now unlocked. You can use any of the latest open source R\/Python packages to build Deep Learning and AI applications on large amounts of data in SQL Server. We also offer leading edge<\/a>, high-performance algorithms in Microsoft’s RevoScaleR<\/a> and RevoScalePy<\/a> APIs. Using these with the latest innovations in the open source world allows you to bring unparalleled selection, performance, and scale to your applications.<\/p>\n Check out SQL Machine Learning Services Documentation<\/a> to learn how you can easily deploy your R\/Python code with SQL stored procedures making them accessible in your ETL processes or to any application. Train and store machine learning models in your database bringing intelligence to where your data lives.<\/p>\n Basic R and Python Execution in SQL Server: <\/a>https:\/\/aka.ms\/BasicMLServicesExecution<\/a><\/p>\n Set up Machine Learning Services in SQL Server: <\/a>https:\/\/aka.ms\/SetupMLServices<\/a><\/p>\n End-to-end tutorial solutions on Github: <\/a>https:\/\/microsoft.github.io\/sql-ml-tutorials\/<\/a><\/p>\n<\/p>\n Other YouTube Tutorials:<\/p>\n How to Install SQL Server Machine Learning Services: <\/a>https:\/\/aka.ms\/InstallMLServices<\/a><\/p>\n How to Enable SQL Server Machine Learning Services: <\/a>https:\/\/aka.ms\/EnableMLServices<\/a><\/p>\n Basics of R and Python Execution in SQL: <\/a>https:\/\/aka.ms\/ExecuteMLServices<\/a><\/p>\n<\/p>\n Keywords: #MachineLearning #DataScience #Python #R #Jupyter #JupyterNotebook #JupyterNotebooks #RStudio #SQL #SQLServer #BigData #DataAnalytics #ScikitLearn #Data #AI #ArtificialIntelligence<\/p>\n<\/div>\n Go to Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"![\"httpsyoutubeD5erljpJDjE\"](\"http:\/\/img.youtube.com\/vi\/D5erljpJDjE\/0.jpg\")
<\/a><\/p>\nEnvironment Setup Prerequisites<\/h1>\n
1. Install ML Services on SQL Server<\/h4>\n
2. Install RevoscalePy via Microsoft’s Python Client<\/h4>\n
.Install-PyForMLS.ps1 -InstallFolder \"C:Program FilesMicrosoftPythonClient\"<\/em><\/code><\/p>\nmkdir JupyterNotebooks; cd JupyterNotebooks; ..Scriptsjupyter-notebook<\/em><\/code><\/p>\n![\"NewNotebookpng\"](\"https:\/\/raw.githubusercontent.com\/kywe665\/MLServicesTutorials\/master\/images\/JupyterNew.png\" "\\"New")
<\/p>\n![\"Importpng\"](\"https:\/\/raw.githubusercontent.com\/kywe665\/MLServicesTutorials\/master\/images\/importrevopy.png\" "\\"New")
<\/p>\nDatabase Setup (Required for this tutorial only)<\/h1>\n
1. Create a database<\/h4>\n
import<\/span> pyodbc<\/span>
# creating a new db to load Iris sample in
<\/span>new_db_name = \"MLRemoteExec\"<\/span> connection_string = \"Driver=SQL Server;Server=localhostMSSQLSERVER2017;Database={0};Trusted_Connection=Yes;\"<\/span>
\ncnxn = pyodbc.connect(connection_string.format(\"master\"<\/span>), autocommit=True<\/span>)
\ncnxn.cursor().execute(\"IF EXISTS(SELECT * FROM sys.databases WHERE [name] = '{0}') DROP DATABASE {0}\"<\/span>.format(new_db_name))
\ncnxn.cursor().execute(\"CREATE DATABASE \"<\/span> + new_db_name)
\ncnxn.close()
print<\/span>(\"Database created\"<\/span>) <\/pre>\n<\/div>\n2. Import Iris sample from SkLearn<\/h4>\n
from<\/span> sklearn<\/span> import<\/span> datasetsimport<\/span> pandas<\/span> as<\/span> pd
<\/span># SkLearn has the Iris sample dataset built in to the package<\/span>iris = datasets.load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)<\/pre>\n<\/div>\n3. Use RecoscalePy APIs to create a table and load the Iris data<\/h4>\n
from<\/span> revoscalepy<\/span> import<\/span> RxSqlServerData, rx_data_step
# Example of using RX APIs to load data into SQL table. You can also do this with pyodbc
<\/span>table_ref = RxSqlServerData(connection_string=connection_string.format(new_db_name), table=\"Iris\"<\/span>)rx_data_step(input_data = df, output_file = table_ref, overwrite = True<\/span>)print<\/span>(\"New Table Created: Iris\"<\/span>)\nprint<\/span>(\"Sklearn Iris sample loaded into Iris table\"<\/span>)
<\/pre>\n<\/div>\nDefine a Function to Send to SQL Server<\/h1>\n
def<\/span> send_this_func_to_sql<\/span>():
from<\/span> revoscalepy<\/span> import<\/span> RxSqlServerData, rx_import
from<\/span> pandas.tools.plotting<\/span> import<\/span> scatter_matrix
import<\/span> matplotlib.pyplot<\/span> as<\/span> plt<\/span> import<\/span> io<\/span> # remember the scope of the variables in this func are within our SQL Server Python Runtime<\/span>
\n connection_string = \"Driver=SQL Server;Server=localhostMSSQLSERVER2017;Database=MLRemoteExec;Trusted_Connection=Yes;\"
<\/span> # specify a query and load into pandas dataframe df<\/span>
\n sql_query = RxSqlServerData(connection_string=connection_string, sql_query = \"select * from Iris\"<\/span>)
\ndf = rx_import(sql_query)
\nscatter_matrix(df)
# return bytestream of image created by scatter_matrix<\/span>
\nbuf = io.BytesIO()
\nplt.savefig(buf, format=\"png\"<\/span>)
\nbuf.seek(0<\/span>)
return<\/span> buf.getvalue()
<\/pre>\n<\/div>\nSend execution to SQL<\/h1>\n
import revoscalepy<\/code>. Create a sql_compute_context<\/code>, and then send the execution of any function seamlessly to SQL Server with RxExec<\/code>. No raw data had to be transferred from SQL to the Jupyter Notebook. All computation happened within the database and only the image file was returned to be displayed.<\/p>\nfrom<\/span> IPython<\/span> import<\/span> display
import<\/span> matplotlib.pyplot<\/span> as<\/span> plt<\/span>
from<\/span> revoscalepy<\/span> import<\/span> RxInSqlServer, rx_exec# create a remote compute context with connection to SQL Server<\/span>
\nsql_compute_context = RxInSqlServer(connection_string=connection_string.format(new_db_name))
# use rx_exec to send the function execution to SQL Server<\/span>
\nimage = rx_exec(send_this_func_to_sql, compute_context=sql_compute_context)[0<\/span>]
# only an image was returned to my jupyter client. All data remained secure and was manipulated in my db.<\/span>
\ndisplay.Image(data=image)<\/pre>\n<\/div>\nLearn More<\/h1>\n