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

Important

Azure SQL Database Machine Learning Services is currently in public preview. This preview version is provided without a service level agreement, and it's not recommended for production workloads. Certain features might not be supported or might have constrained capabilities. For more information, see Supplemental Terms of Use for Microsoft Azure Previews.

Sign up for the preview

Prerequisites

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:
Single database
Create Portal
CLI
PowerShell
Configure Server-level IP firewall rule
Load data Adventure Works loaded per quickstart

Note

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.

  1. Sign in to the Azure portal.

  2. Navigate to the SQL databases or SQL managed instances page.

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

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

  2. Pass the complete R script to the sp_execute_external_script stored procedure.

    The script is passed through the @script argument. Everything inside the @script argument must be valid R code.

    Important

    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'
    

    Note

    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

  1. Execute the sp_execute_external_script stored procedure.

  2. Verify that the top 20 Category/Product rows are returned in the Messages window.

Next steps