Quickstart: Use R with Machine Learning Services to query an Azure SQL database (preview)
This quickstart demonstrates how to use R with Machine Learning Services to connect to an Azure SQL database and use Transact-SQL statements to query data. Machine Learning Services is a feature of Azure SQL Database, used for executing in-database R scripts. For further information, see Azure SQL Database Machine Learning Services with R (preview).
To complete this quickstart, make sure you have the following:
- An Azure SQL database. You can use one of these quickstarts to create and then configure a database in Azure SQL Database:
|Configure||Server-level IP firewall rule|
|Load data||Adventure Works loaded per quickstart|
During the preview of Azure SQL Database Machine Learning Services with R, the managed instance deployment option is not supported.
Machine Learning Services (with R) enabled. During the public preview, Microsoft will onboard you and enable machine learning for your existing or new database. Follow the steps in Sign up for the preview.
The latest SQL Server Management Studio (SSMS). You can run R scripts using other database management or query tools, but in this quickstart you'll use SSMS.
Get SQL server connection information
Get the connection information you need to connect to the Azure SQL database. You'll need the fully qualified server name or host name, database name, and login information for the upcoming procedures.
Sign in to the Azure portal.
Navigate to the SQL databases or SQL managed instances page.
On the Overview page, review the fully qualified server name next to Server name for a single database or the fully qualified server name next to Host for a managed instance. To copy the server name or host name, hover over it and select the Copy icon.
Create code to query your SQL database
Open SQL Server Management Studio and connect to your SQL database.
If you need help connecting, see Quickstart: Use SQL Server Management Studio to connect and query an Azure SQL database.
Pass the complete R script to the sp_execute_external_script stored procedure.
The script is passed through the
@scriptargument. Everything inside the
@scriptargument must be valid R code.
The code in this example uses the sample AdventureWorksLT data, which you can choose as source when creating your database. If your database has different data, use tables from your own database in the SELECT query.
EXECUTE sp_execute_external_script @language = N'R' , @script = N'OutputDataSet <- InputDataSet;' , @input_data_1 = N'SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName FROM [SalesLT].[ProductCategory] pc JOIN [SalesLT].[Product] p ON pc.productcategoryid = p.productcategoryid'
If you get any errors, it might be because the public preview of Machine Learning Services (with R) is not enabled for your SQL database. See Prerequisites above.
Run the code
Execute the sp_execute_external_script stored procedure.
Verify that the top 20 Category/Product rows are returned in the Messages window.