Skip to content

Latest commit

 

History

History
116 lines (83 loc) · 5.28 KB

deepdive-create-and-run-r-scripts.md

File metadata and controls

116 lines (83 loc) · 5.28 KB
titledescriptionauthorms.authorms.datems.servicems.subservicems.topicmonikerRange
Summary statistics in RevoScaleR
RevoScaleR tutorial 5: How to compute statistical summary statistics using the R language on SQL Server.
VanMSFT
vanto
11/27/2018
sql
machine-learning-services
tutorial
>=sql-server-2016||>=sql-server-linux-ver15

Compute summary statistics in R (SQL Server and RevoScaleR tutorial)

[!INCLUDE SQL Server 2016 and later]

This is tutorial 5 of the RevoScaleR tutorial series on how to use RevoScaleR functions with SQL Server.

This tutorial uses the established data sources and compute contexts created in previous tutorials to run high-powered R scripts. In this tutorial, you will use local and remote server compute contexts for the following tasks:

[!div class="checklist"]

  • Switch the compute context to SQL Server
  • Obtain summary statistics on remote data objects
  • Compute a local summary

If you completed the previous tutorials, you should have these remote compute contexts: sqlCompute and sqlComputeTrace. Moving forward, you use will sqlCompute and the local compute context in subsequent tutorials.

Use an R IDE or Rgui to run the R script in this tutorial.

Compute summary statistics on remote data

Before you can run any R code remotely, you need to specify the remote compute context. All subsequent computations take place on the [!INCLUDEssNoVersion] computer specified in the sqlCompute parameter.

A compute context remains active until you change it. However, any R scripts that cannot run in a remote server context will automatically run locally.

To see how a compute context works, generate summary statistics on the sqlFraudDS data source on the remote SQL Server. This data source object was created in tutorial two and represents the ccFraudSmall table in the RevoDeepDive database.

  1. Switch the compute context to sqlCompute created in the previous tutorial:

    rxSetComputeContext(sqlCompute)

  2. Call the rxSummary function and pass required arguments, such as the formula and the data source, and assign the results to the variable sumOut.

    sumOut<- rxSummary(formula=~gender+balance+numTrans+numIntlTrans+creditLine, data=sqlFraudDS)

    The R language provides many summary functions, but rxSummary in RevoScaleR supports execution on various remote compute contexts, including [!INCLUDEssNoVersion]. For information about similar functions, see Data summaries using RevoScaleR.

  3. Print the contents of sumOut to the console.

    sumOut

    [!NOTE] If you get an error, wait a few minutes for execution to finish before retrying the command.

Results

SummaryStatisticsResultsfor:~gender+balance+numTrans+numIntlTrans+creditLineData: sqlFraudDS (RxSqlServerDataDataSource) Numberofvalidobservations:10000NameMeanStdDevMinMaxValidObsMissingObsbalance4075.03183926.558714025626100000numTrans29.106126.6199230100100000100000numIntlTrans4.08688.726757060100000100000creditLine9.18569.870364175100000100000CategoryCountsforgenderNumberofcategories:2Numberofvalidobservations:10000Numberofmissingobservations:0genderCountsMale6154Female3846

Create a local summary

  1. Change the compute context to do all your work locally.

    rxSetComputeContext ("local")

  2. When extracting data from SQL Server, you can often get better performance by increasing the number of rows extracted for each read, assuming the increased block size can be accommodated in memory. Run the following command to increase the value for the rowsPerRead parameter on the data source. Previously, the value of rowsPerRead was set to 5000.

    sqlServerDS1<- RxSqlServerData( connectionString=sqlConnString, table=sqlFraudTable, colInfo=ccColInfo, rowsPerRead=10000)

  3. Call rxSummary on the new data source.

    rxSummary(formula=~gender+balance+numTrans+numIntlTrans+creditLine, data=sqlServerDS1)

    The actual results should be the same as when you run rxSummary in the context of the [!INCLUDEssNoVersion] computer. However, the operation might be faster or slower. Much depends on the connection to your database, because the data is being transferred to your local computer for analysis.

  4. Switch back to the remote compute context for the next several tutorials.

    rxSetComputeContext(sqlCompute)

Next steps

[!div class="nextstepaction"] Visualize SQL Server data using R

close