{"id":71671,"date":"2017-07-17T12:34:58","date_gmt":"2017-07-17T12:34:58","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71671"},"modified":"2021-09-29T16:21:10","modified_gmt":"2021-09-29T16:21:10","slug":"sql-server-r-services-digging-r-language","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/sql-server-r-services-digging-r-language\/","title":{"rendered":"SQL Server R Services: Digging into the R Language"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. SQL Server R Services: The Basics<\/a><\/li>\n
  2. SQL Server R Services: Digging into the R Language<\/a><\/li>\n
  3. SQL Server R Services: Working with ggplot2 Statistical Graphics<\/a><\/li>\n
  4. SQL Server R Services: Working with Data Frames<\/a><\/li>\n
  5. SQL Server R Services: Generating Sparklines and Other Types of Spark Graphs<\/a><\/li>\n
  6. SQL Server R Services: Working with Multiple Data Sets<\/a><\/li>\n<\/ol>\n\n

    In SQL Server 2016, Microsoft added support for the R language in two different forms: SQL Server R Server, a stand-alone product that provides parallel processing and other performance enhancements, and SQL Server R Services, an integrated service that lets you run R scripts directly within the SQL Server environment and incorporate SQL Server data within those scripts.<\/p>\n

    This article is the second in a series about SQL Server R Services. In the first article, I explained how to use R Services to run R scripts within the SQL Server environment. To do so, you must use the sp_execute_external_script<\/code> system stored procedure to run the script, including it as a parameter value when calling the procedure. The first article provides a number of examples of how to go about doing this.<\/p>\n

    In this article, I focus on the R script itself, using a single example that demonstrates how to generate a bar chart (also known as a bar plot<\/em> in R lingo). The article walks you through the script one element at a time so you can better understand the various elements that make up the script, while gaining a foundation in many important concepts related to R scripting.<\/p>\n

    When calling the sp_execute_external_script<\/code> stored procedure, we\u2019ll take the same approach as in the first article; that is, we\u2019ll save the R script and T-SQL SELECT<\/code> statement to variables (@rscript<\/code> and @sqlscript<\/code>, respectively). We\u2019ll then use those variables as parameter values when calling the procedure. We\u2019ll also be running the procedure within the context of the AdventureWorks2014<\/code> database because it will provide our source data.<\/p>\n

    With that in mind, take a look at the following T-SQL code, which includes all the elements necessary to generate a bar chart based on data extracted from the Sales.SalesOrderHeader<\/code> and Sales.Territory<\/code> tables in the AdventureWorks2014<\/code> database:<\/p>\n

    USE AdventureWorks2014;\r\nGO\r\n\r\nDECLARE @rscript NVARCHAR(MAX);\r\nSET @rscript = N'\r\n\r\n  # import scales and ggplot2 packages\r\n  library(scales)\r\n  library(ggplot2)\r\n\r\n  # set up report file for chart\r\n  reportfile <- \"C:\\\\DataFiles\\\\SalesReport.png\"\r\n  png(filename=reportfile, width=1000, height=600)\r\n\r\n  # construct data frame\r\n  sales <- InputDataSet\r\n  c1 <- levels(sales$SalesTerritories)\r\n  c2 <- round(tapply(sales$Subtotal, sales$SalesTerritories, sum))\r\n  salesdf <- data.frame(c1, c2)\r\n  names(salesdf) <- c(\"Territories\", \"Sales\")\r\n\r\n  # generate bar chart\r\n  barchart <- ggplot(salesdf, aes(x=Territories, y=Sales)) + \r\n    labs(title=\"Total Sales per Territory\", x=\"Sales Territories \\n (with country code)\", y=\"Sales Amounts\") +\r\n    geom_bar(stat=\"identity\", color=\"green\", size=1, fill=\"lightgreen\") + \r\n    coord_flip() + xlim(rev(levels(sales$SalesTerritories))) +\r\n    scale_y_continuous(labels=function(x) format(x, big.mark=\",\", scientific=FALSE)) +\r\n    geom_text(aes(label=comma(Sales), ymax=100, ymin=0), size=4, hjust=0, position=position_fill())\r\n  print(barchart)\r\n  dev.off()';\r\n\r\nDECLARE @sqlscript NVARCHAR(MAX);\r\nSET @sqlscript = N'\r\n  SELECT h.Subtotal, \r\n    t.Name + '' ('' + t.CountryRegionCode + '')'' AS SalesTerritories\r\n  FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t\r\n    ON h.TerritoryID = t.TerritoryID;';\r\n\r\nEXEC sp_execute_external_script\r\n  @language = N'R',\r\n  @script = @rscript,\r\n  @input_data_1 = @sqlscript\r\n  WITH RESULT SETS NONE; \r\nGO\r\n<\/pre>\n
    If you read the first article in this series, the components of the procedure call should be familiar to you. We start by defining the R script and assigning it to the @rscript<\/code> variable. Next, we define the SELECT<\/code> statement and assign it to the @sqlscript<\/code> variable. Finally, we call the sp_execute_external_script<\/code> stored procedure, specifying the @language<\/code> value as R<\/code>, the @rscript<\/code> value as @rscript<\/code>, and the @input_date_1<\/code> value as @sqlscript<\/code>. We also specify that no result set be returned. Instead, we\u2019ll be generating a .png file that contains our bar chart.<\/p>\n
    The remainder of the article walks you through each section of the R script: importing R packages, setting up the report file, constructing a data frame, and generating the bar chart. If you have any questions about the non-R elements in the example, be sure to refer back to the first article.<\/p>\n
    Importing R packages<\/h2>\n
    The first part of our code imports two R packages from the local SQL Server library into the scripting environment. Packages are collections of functions or data sets in the SQL Server R Services library. When you install R Services, SQL Server setup adds a number of common R packages; however, you can install additional packages at any time.<\/p>\n
    Once a package has been installed in the library, you can import it into your scripting environment in order to use its functions or data sets within your R script.<\/p>\n
    In our example, we\u2019ll be importing the scales<\/code> and ggplot2<\/code> packages. Before we can do that, however, we must install them into the SQL Server R Services library. Any package we import must exist in the library. Fortunately, we can get both packages by installing the ggplot2<\/code> package. When you install this package, R Services installs several additional packages, including scales<\/code>.<\/p>\n
    If the machine on which SQL Server is running can connect to the Internet, one of the simplest ways to install a package is to use the RGui utility available in the following folder (on a default installation):<\/p>\n
    C:\\Program Files\\MSSQL13.MSSQLSERVER\\R_SERVICES\\bin\\<\/pre>\n
    If you\u2019re working with a named SQL Server instance, simply replace MSSQLSERVER<\/code> with the name of that instance.<\/p>\n
    From this folder, launch the RGui utility as an administrator and run the following two commands (again replacing the default instance with the named instance, if applicable):<\/p>\n
    lib.SQL <- \"C:\\\\Program Files\\\\Microsoft SQL Server\\\\MSSQL13.MSSQLSERVER\\\\R_SERVICES\\\\library\"\r\n  \r\n  install.packages(\"ggplot2\", lib = lib.SQL)<\/pre>\n
    All you\u2019re doing here is specifying where the target SQL Server library is located and then using that location when running the install.packages<\/code> function. R Services takes care of the rest, checking for an available download server, pulling the files from that server, and installing the package into the library. The following figure shows the RGui utility after running the install.packages<\/code> function. Your results might be different, depending on the download server used.<\/p>\n
    \"rgui.png\"<\/p>\n
    That\u2019s all there is to installing a package in the SQL Server library, assuming you can connect to the Internet. If you can\u2019t, you\u2019ll have to take a different approach. For more information, see the Microsoft article Install Additional R Packages on SQL Server<\/a>.<\/p>\n
    With the packages in place, we can import them into the scripting environment. To do so, you need only run the library<\/code> function for each package, specifying the package name when calling the function, as shown in the following code:<\/p>\n
    library(scales)\r\nlibrary(ggplot2)<\/pre>\n
    Once we\u2019ve imported the packages, we can use their functions in our script, which we\u2019ll be doing later in the script, when we define the bar chart.<\/p>\n
    Setting up the image file<\/h2>\n
    The next step is to set up the image file that will hold our bar chart. We do this by including the following two lines of code:<\/p>\n
    reportfile <- \"C:\\\\DataFiles\\\\SalesReport.png\"\r\npng(filename=reportfile, width=1000, height=600)<\/pre>\n
    First, we define a string variable to hold the folder and file name of the target image file and then use the back-arrow assignment operator (<-<\/code>) to assign the value to the reportfile<\/code> variable. Notice that we must escape the backslashes in the file path by doubling them. (The folder and file names specified here are just for the example. You can specify any folder or .png file name you want. Just substitute them within the code.)<\/p>\n
    Next, we call the png<\/code> device, which is used to create the .png file for our bar chart. The R language provides devices such as png<\/code>, bmp<\/code>, and tiff<\/code> for creating plots and image files in various formats. In this case, when calling the png<\/code> device, we specify the reportfile<\/code> variable for the folder and file names and then provide a width and height for the file, in pixels.<\/p>\n
    That\u2019s all there is to setting up our graphic file. After we add the bar chart, we\u2019ll need to close the png<\/code> device, using the dev.off()<\/code> function, but we\u2019ll get to that later in the article, after we define our bar chart.<\/p>\n
    Constructing the data frame<\/h2>\n
    Before we can create the bar chart, we must get the data we want to visualize into a data frame. A data frame is similar to a database table. Strictly speaking, it is a list of vectors of equal length that are assigned the data.frame<\/code> type, but to keep in simple, we can think of the data set in terms of rows and columns.<\/p>\n
    To construct the data frame, we need to retrieve the SQL Server data, transform the data, and build the data frame, as shown in the following code:<\/p>\n
    sales <- InputDataSet\r\nc1 <- levels(sales$SalesTerritories)\r\nc2 <- round(tapply(sales$Subtotal, sales$SalesTerritories, sum))\r\nsalesdf <- data.frame(c1, c2)\r\nnames(salesdf) <- c(\"Territories\", \"Sales\")<\/pre>\n
    First, we use the following statement to assign the SQL Server data to the sales<\/code> variable:<\/p>\n
    sales <- InputDataSet<\/pre>\n
    The SQL Server data is represented by the InputDataSet<\/code> variable, which is the default variable used to reference the data returned by the SELECT<\/code> statement. We must use the InputDataSet<\/code> variable to pull the SQL Server data into our R script, unless we specify a different name for the data set variable. (We would do this in the stored procedure call. If you\u2019re not sure how this works, refer to the first article in this series.)<\/p>\n
    Once we have the data set stored in the sales<\/code> variable, we can start working with that data. Our final data frame will contain two columns. The first will include the list of territories, and the second will include the aggregated sales for each territory, as defined in the following code:<\/p>\n
    c1 <- levels(sales$SalesTerritories)\r\nc2 <- round(tapply(sales$Subtotal, sales$SalesTerritories, sum))<\/pre>\n
    We start by assigning the values for the first column to the c1<\/code> variable. To get the values, we use the levels<\/code> function to retrieve a distinct list of values from the SalesTerritories<\/code> column in the sales<\/code> data set. Notice that we must first specify the sales<\/code> variable, then a dollar sign ($<\/code>), and finally the SalesTerritories<\/code> column.<\/p>\n
    Next, we assign the values for the second column to the c2<\/code> variable. This time, we use the tapply<\/code> function to return the aggregated subtotals for each territory. The function takes three arguments. The first is the Subtotal<\/code> column in the sales<\/code> data set.<\/p>\n
    The second argument, sales$SalesTerritories<\/code>, contains the factors used for aggregating the subtotals specified in the first argument. In other words, the second column provides the basis (territories) for how the values in the first column (subtotals) will be grouped together and aggregated.<\/p>\n
    The third argument, sum<\/code>, is an aggregate function that adds together the subtotals in each territory to produce a total for each group. Notice that we also use the round<\/code> function to round the aggregated totals to integers.<\/p>\n
    Once we\u2019ve defined the two columns, we can put them together into a data frame and assign names to the columns, as shown the following code:<\/p>\n
    salesdf <- data.frame(c1, c2)\r\nnames(salesdf) <- c(\"Territories\", \"Sales\")<\/pre>\n
    In the first line of code, we use the data.frame<\/code> function to merge the two columns into a data frame, which we assign to the salesdf<\/code> variable.<\/p>\n
    In the second line, we use the names<\/code> function to assign names to the salesdf<\/code> data frame. To do so, we use the c<\/code> function to concatenate the column names, Territories<\/code> and Sales<\/code>, and then assign the results to the data frame.<\/p>\n
    That\u2019s all there is to creating our data frame. As you become more adept at the R language, you\u2019ll be able to perform far more sophisticated calculations. But for now, let\u2019s see what we can do with the data.<\/p>\n
    Generating the bar chart<\/h2>\n
    One of the most powerful aspects of the R language is its ability to plot data and generate meaningful visualizations. In this case, we\u2019re using the following code to create a bar chart based on the data in the salesdf<\/code> data frame:<\/p>\n
    barchart <- ggplot(salesdf, aes(x=Territories, y=Sales)) + \r\n  labs(title=\"Total Sales per Territory\", x=\"Sales Territories \\n (with country code)\", y=\"Sales Amounts\") +\r\n  geom_bar(stat=\"identity\", color=\"green\", size=1, fill=\"lightgreen\") + \r\n  coord_flip() + xlim(rev(levels(sales$SalesTerritories))) +\r\n  scale_y_continuous(labels=function(x) format(x, big.mark=\",\", scientific=FALSE)) +\r\n  geom_text(aes(label=comma(Sales), ymax=100, ymin=0), size=4, hjust=0, position=position_fill())\r\nprint(barchart)\r\ndev.off()';\r\n<\/pre>\n
    In this section of code, we use several functions from the scales<\/code> and ggplot2<\/code> packages. (Prior to this section, all the functions we used were part of default packages included with the R Services installation.)<\/p>\n
    The bulk of the code in this section is related the bar chart definition, which we assign to the barchart<\/code> variable. The definition is made up of seven elements, connected together with plus (+<\/code>) signs. In the first element, we use the ggplot<\/code> function to create the base layer for our bar chart:<\/p>\n
    ggplot(salesdf, aes(x=Territories, y=Sales))<\/pre>\n
    The function\u2019s first argument, salesdf<\/code>, is the data frame that provides the data for the bar chart. The second argument uses the aes<\/code> function to define the default aesthetics used by all layers in the chart, unless specifically overridden within a layer. In this case, the aes<\/code> function merely defines the chart\u2019s X-axis and Y-axis (Territories<\/code> and Sales<\/code>, respectively), which coincide with the columns in the salesdf<\/code> data set.<\/p>\n
    In the next element in the bar chart definition, we use the labs<\/code> function to provide labels for the title and each axis:<\/p>\n
    labs(title=\"Total Sales per Territory\", x=\"Sales Territories \\n (with country code)\", y=\"Sales Amounts\")<\/pre>\n
    All we\u2019re doing here is passing three arguments into the function, one for each label. One thing worth noting, however, is the \\n<\/code> in the X-axis label. This inserts a line break into the text. By using the labs<\/code> function, we can override the default labels that would normally be used, as specified in the ggplot<\/code> function.<\/p>\n
    Now we get to the third element, which uses the geom_bar<\/code> function to specify that a bar plot be created, as opposed to another type of visualization:<\/p>\n
    geom_bar(stat=\"identity\", color=\"green\", size=1, fill=\"lightgreen\")<\/pre>\n
    The function takes four arguments. The stat=\"identity\"<\/code> argument ensures that data values map correctly to the plot points. The color<\/code> argument sets the bar outlines to green, the size<\/code> argument sets the bar outlines to 1 point, and the fill<\/code> argument sets the main color of the bars to light green.<\/p>\n
    Next, we call the coord_flip<\/code> function to flip how the X-axis and Y-axis are displayed in order to make it easier to read the axis labels:<\/p>\n
    coord_flip()<\/pre>\n
    After we rotate the chart, we need to modify the order of the territory names to ensure that they\u2019re listed alphabetically, starting at the top:<\/p>\n
    xlim(rev(levels(sales$SalesTerritories)))<\/pre>\n
    We\u2019re taking this approach because flipping the X-axis and Y-axis resulted in the territory names being listed in reverse order (from down to up). To fix this, we first use the levels<\/code> function to retrieve a distinct list of the territory names, and then use the rev<\/code> function to reverse their order. We then wrap all this within the xlim<\/code> function to ensure that the values are correctly mapped to the reversed labels.<\/p>\n
    Next, we use the scale_y_continuous<\/code> function to ensure that the numeric labels use integers rather than scientific notation:<\/p>\n
    scale_y_continuous(labels=function(x) format(x, big.mark=\",\", scientific=FALSE))<\/pre>\n
    The scale_y_continuous<\/code> function lets us refine the labels used for the Y-axis aesthetics. In this case, we\u2019re creating the x<\/code> function as the value for the labels<\/code> argument, and then using the format<\/code> function to modify the x<\/code> function, adding commas to the numerical values and ensuring they\u2019re not rendered as scientific notation.<\/p>\n
    The format<\/code> function takes three arguments. The first specifies the x<\/code> function as the object being formatted. The second argument (big.mark<\/code>) specifies that a comma be used for large numerical numbers. The third argument, scientific<\/code>, specifies FALSE<\/code> to prevent scientific notation from being displayed.<\/p>\n
    The next step is to add the sales totals to the bars themselves so they appear on top of the bars. For this, we must use the geom_text<\/code> function:<\/p>\n
    geom_text(aes(label=comma(Sales), ymax=100, ymin=0), size=4, hjust=0, position=position_fill()))<\/pre>\n
    The function takes four arguments. The first argument, label<\/code>, uses the aes<\/code> function, which itself takes three arguments. The first specifies that the Sales<\/code> values be displayed, using the comma<\/code> function to add commas to the numeric vales. The ymax<\/code> and ymin<\/code> settings specify the upper and lower pointwise limits of the displayed value. You must include these two arguments, but you can experiment with their settings, particularly ymax<\/code>.<\/p>\n
    The size<\/code> argument of the geom_text<\/code> function specifies 4<\/code>, which sets the font size to 4 points, and the hjust<\/code> argument specifies 0<\/code>, which left-justifies the labels. Finally, the position<\/code> argument uses the position_fill<\/code> function to ensure that the labels appear on top of the bar, rather than off to the right.<\/p>\n
    This completes our bar chart definition, we can now use the print<\/code> function to send the bar plot to the .png file and the dev.off<\/code> function to close the png<\/code> device:<\/p>\n
    print(barchart)\r\ndev.off()<\/pre>\n
    If you were to now run the script, calling the sp_execute_external_script<\/code> stored procedure as defined in our example, the SalesReport.png<\/code> file will be created in the designated folder. When you open file, you should see an image similar to the one shown in the following figure.<\/p>\n
    \"SalesReport.png\"<\/p>\n
    As you can see, the SQL Server data has been aggregated and rendered in the chart, providing us with the total sales for each territory. Notice that all the numerical values are rounded and include commas to make them more readable.<\/p>\n
    Working with R Services<\/h2>\n
    Not surprisingly, we can do a lot more with both the data and the visualization in our example. We can modify the bar chart to change how data is displayed, or we can try different types of visualizations. Because it\u2019s so easy to generate a graphic file, we can play around with the code as much as we want to see what we can come up with. In fact, this is often the best approach to learn R because much of the documentation is very unclear. Sometimes the only way to understand how a language element works is to try it out.<\/p>\n
    Although the example we\u2019ve been working here is very basic, it should help you better understand some of the language elements that go into analyzing and visualizing data. In future articles in this series, we\u2019ll dig deeper into both the analytics side and the visualization side. Until then, you have plenty here you can play with, so dive in and start having some fun.<\/p>\n","protected":false},"excerpt":{"rendered":"
    It is not just the analytic power of R that you get from using SQL Server R Services, but also the great range of packages that can be run in R that provide a daunting range of graphing and plotting facilities. Robert Sheldon shows how you can take data held in SQL Server and, via SQL Server R Services, use an R package called ggPlot that offers a powerful graphics language for creating elegant and complex plots.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,143531],"tags":[5134],"coauthors":[6779],"class_list":["post-71671","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71671"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71671\/revisions"}],"predecessor-version":[{"id":71723,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71671\/revisions\/71723"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71671"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}