Quickstart: "Hello world" R script in SQL Server

APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

In this quickstart, you learn key concepts by running a "Hello World" R script inT-SQL, with an introduction to the sp_execute_external_script system stored procedure.

Prerequisites

A previous quickstart, Verify R exists in SQL Server, provides information and links for setting up the R environment required for this quickstart.

Basic R interaction

There are two ways you can run R code in SQL Database:

  • Add R script as an argument of the system stored procedure, sp_execute_external_script.
  • From a remote R client, connect to your SQL database, and execute code using the SQL Database as the compute context.

The following exercise is focused on the first interaction model: how to pass R code to a stored procedure.

  1. Run a simple script to see how an R script executes in your SQL database.

    EXECUTE sp_execute_external_script
    @language = N'R',
    @script = N'
    a <- 1
    b <- 2
    c <- a/b
    d <- a*b
    print(c(c, d))
    '
    
  2. Assuming that you have everything set up correctly the correct result is calculated, and the R print function returns the result to the Messages window.

    Results

    STDOUT message(s) from external script: 
    0.5 2
    

    While getting stdout messages is useful when testing your code, more often you need to return the results in tabular format, so that you can use it in an application or write it to a table. See Quickstart: Handle inputs and outputs using R in SQL Server for more information.

Remember, everything inside the @script argument must be valid R code.

Run a Hello World script

The following exercise runs another simple R scripts.

EXEC sp_execute_external_script
  @language =N'R',
  @script=N'OutputDataSet<-InputDataSet',
  @input_data_1 =N'SELECT 1 AS hello'
  WITH RESULT SETS (([Hello World] int));
GO

Inputs to this stored procedure include:

  • *@language* parameter defines the language extension to call, in this case, R.
  • *@script* parameter defines the commands passed to the R runtime. Your entire R script must be enclosed in this argument, as Unicode text. You could also add the text to a variable of type nvarchar and then call the variable.
  • *@input_data_1* is data returned by the query, passed to the R runtime, which returns the data to SQL Server as a data frame.
  • WITH RESULT SETS clause defines the schema of the returned data table for SQL Server, adding "Hello World" as the column name, int for the data type.

Results

Hello World
1

Next steps

Now that you have run a couple of simple R scripts, take a closer look at structuring inputs and outputs.