Quickstart: Use R 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:

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.

    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