24 Hours of PASS – Understanding and Visualizing Data Using R in SQL Server

2015-06-23 08_19_31-24 Hours of PASS_ Growing Our Community _ About

24 Hours of PASS is slowly closing in. My session “Understanding and Visualizing Data Using R in SQL Server” was scheduled as 21 and only three left, until the community hit exactly 24 hour or professional webcasts to deliver technical content on Microsoft Data Platform technologies.

During my session questions were asked, if and where the code can be shared or is available.

And of course, I always gladly share my code.

File is available and can be downloaded here: Code for Creating and using SP_statistics.

Slide deck are available here.

Session description is available here.

Speakers bio here.

I am preparing also a series of articled on this topic and will have extended version of procedure with more and more samples following.

Thanks to SQL Server community, Microsoft and PASS Organization for having me as presenter.

Code available for copy/paste:

USE [AdventureWorks2014]
GO

/****** Object:  StoredProcedure [dbo].[sp_getStatistics]    Script Date: 25.6.2015 8:24:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



------------------------------------------------------------------------
------------------------------------------------------------------------
-- MS SQL Server Get Statistics from Program R
-- Using Statistical Program R for running statistics in MS SQL Server
-- Configuring MS SQL Server, data preparation, execution of procedure 

-- Author: Tomaz Kastrun
-- Date: July 25, 2015; 
-- Version 1.2.2; Presented on PASS 24HOP 2015

-- E: tomaz.kastrun@gmail.com
-- T: @Tomaz_tsql
-- B: http://tsqljokes.tumblr.com
-- B: https://tomaztsql.wordpress.com/

------------------------------------------------------------------------
------------------------------------------------------------------------

/*

USAGE SAMPLE:

USE AdventureWorks2014;
EXECUTE AdventureWorks2012.dbo.sp_getStatistics 
                             @TargetTable = 'TK_vPersonDemographics'
                            ,@Variables = 'TotalChildren,NumberCarsOwned' -- order is applied, first variable will be used first, etc
                            ,@Statistics = '3' 
                            ,@ServerName = 'SAMSUNG'
                            ,@DatabaseName = 'AdventureWorks2014'
                            ,@WorkingDirectory = 'C:\DataTK'
                            ,@RPath = 'C:\Program Files\R\R-3.0.2\bin\x64';
GO


INSTALLATION: 
Check and enable SP_CONFIGURE!!!

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO 

*/


CREATE Procedure [dbo].[sp_getStatistics]
(
     @TargetTable            VARCHAR(200)    = NULL
    ,@Variables            VARCHAR(1000)   = NULL
    ,@Statistics             VARCHAR(200)   = NULL
    ,@ServerName             VARCHAR(50)    = NULL
    ,@DatabaseName             VARCHAR(20)    = NULL
    ,@WorkingDirectory         VARCHAR(200)   = NULL
    ,@RPath                      VARCHAR(200)   = NULL
)  
--RETURNS NVARCHAR(MAX)

WITH EXECUTE AS CALLER
-- WITH STATEMENT_COMPLETED 

AS    

BEGIN

/*
-- FUCKING DEBBUGING

DECLARE @TargetTable VARCHAR(200) = '[vStoreWithAddresses]'
DECLARE @Variables VARCHAR(1000) = 'Name'                        
DECLARE @Statistics VARCHAR(200)  = '8'
DECLARE @ServerName VARCHAR(50) = 'SICN-00031'
DECLARE @DatabaseName VARCHAR(20) =  'AdventureWorks2014'
DECLARE @WorkingDirectory VARCHAR(200) = 'C:\DataTK'
DECLARE @RPath VARCHAR(200) = 'C:\Program Files\R\R-3.0.3\bin'
*/


/*
-- USAGE:

EXECUTE AdventureWorks2014.dbo.sp_getStatistics
             @TargetTable = 'vPersonDemographics'
            ,@Variables = 'TotalChildren'                        
            ,@Statistics = '4'
            ,@ServerName = 'SRV-DEV'
            ,@DatabaseName = 'AdventureWorks2014'
            ,@WorkingDirectory = 'C:\DataTK'
            ,@RPath = 'C:\Program Files\R\R-3.0.3\bin';
GO 


*/

SET NOCOUNT ON;


-- *******************
--
--
-- LIST OF STATISTICS FOR R
--
--
-- ******************* 

BEGIN TRANSACTION

    IF EXISTS ( SELECT [object_id] FROM sys.objects  WHERE name = 'tableStatistics' )
    DROP TABLE dbo.tableStatistics

    CREATE TABLE dbo.tableStatistics 
        (
             id INT NOT NULL
            ,RStats NVARCHAR(1000) 
            ,R_Code NVARCHAR(MAX)
            ,NofVariables  INT 
        ) 

    INSERT INTO dbo.tableStatistics
    SELECT 1, 'Overview statistics','summary(fo)', NULL                                                   UNION ALL
    SELECT 2, 'Frequency table','table(fo$!!variable)',1                                                UNION ALL
    SELECT 3, 'Crosstabs','table(fo$!!variable1,fo$!!variable2)',2                                       UNION ALL
    SELECT 4, 'Chi Square test','chisq.test(fo$!!variable)',1                                           UNION ALL
    SELECT 5, 'Cross Tabs with cell %','round(prop.table(fo, ),2)',NULL                                   UNION ALL
    SELECT 6, 'Cross Tabs with row %','round(prop.table(fo,1),2)',NULL                                   UNION ALL
    SELECT 7, 'Cross Tabs with column %','round(prop.table(fo,2),2)',NULL                               UNION ALL
    SELECT 8, 'Word Cloud','', NULL                                                                       UNION ALL
    SELECT 9, 'Waffle graphs','', NULL

COMMIT TRANSACTION

-- ////////////////////////////////////
-- PRINT '1. Populating table with R Statistics'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


-- *******************
--
--
-- SPLIT VARIABLES
--
--
-- ******************* 

DECLARE @Variable_list TABLE 
    (
            ID INT IDENTITY(1,1) NOT NULL
        ,item VARCHAR(100) NOT NULL
    )

DECLARE @str NVARCHAR(4000) = @Variables
DECLARE @pos INT = 0
DECLARE @i INT = 1
DECLARE @nextPos INT
DECLARE @item NVARCHAR(4000)
DECLARE @maxLen INT = LEN(@str)
DECLARE @delimiter CHAR(1) = ','

    WHILE @maxLen >= @pos
    BEGIN  
           SET @nextPos = CHARINDEX(@delimiter, @str, @pos);

           IF (@nextPos = 0 OR @nextPos IS NULL) 
                 SELECT @nextPos = @maxLen + 1;

           SET @item = LTRIM(RTRIM(SUBSTRING(@str, @pos, @nextPos - @pos)));
       
           INSERT INTO @Variable_list
           SELECT @item
       
           SET @pos = @nextPos+LEN(@delimiter)      
           SET @i = @i + 1
    END


-- ***********************************************************
--
--
-- CLEANING ANY PREVIOUSLY EXISTING FILES IN WORKING DIRECTORY
--
--
-- *********************************************************** 

-- @WorkingDirectory
IF (@WorkingDirectory IS NOT NULL OR @WorkingDirectory <> '')
BEGIN

    DECLARE @test_dir INT
    DECLARE @SQL_DEL_DIR VARCHAR(100)
    SET @SQL_DEL_DIR = 'EXEC xp_cmdshell '' IF EXIST '+@WorkingDirectory+' DEL /Q '+@WorkingDirectory+' '' , NO_OUTPUT'; 
    EXEC (@SQL_DEL_DIR)

    IF (@test_dir = 0)
    BEGIN
        PRINT 'Directory not exist or no access'
        RETURN -1
    END

END;

-- ////////////////////////////////////
-- PRINT '2. Clean existing folders and files'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


-- ***********************************************************
--
--
--          CHECK INPUT VARIABLES
--
--
-- *********************************************************** 


-- @TargetTable
IF (@TargetTable = NULL OR @TargetTable = '')
BEGIN 
    PRINT 'Please Define variable @TargetTable'
    RETURN -1;
END;


-- @Statistics
IF (
    NOT EXISTS (SELECT * FROM dbo.tableStatistics where ID = @Statistics) 
    OR
        (@Statistics = NULL OR @Statistics = '')
    )
BEGIN 
    PRINT 'Please define statistics correctly. Check table of available statistics.'
    RETURN -1;
END;

-- @WorkingDirectory
IF (@WorkingDirectory = NULL OR @WorkingDirectory = '')
BEGIN
    PRINT 'Please define your working directory in variable @WorkingDirectory'
    RETURN -1;
END;

-- @RPath
IF (@RPath = NULL OR @RPath = '')
BEGIN
    PRINT 'Please define path to R program in variable @RPath'
    RETURN -1;
END;


IF (@ServerName = NULL OR LEN(@ServerName) = 0)
BEGIN
    PRINT 'Please define server name.'
    RETURN -1;
END

IF (@DatabaseName = NULL OR LEN(@DatabaseName) = 0)
BEGIN
    PRINT 'Please define database name.'
    RETURN -1;
END


-- @WorkingDirectory
IF (@WorkingDirectory <> NULL OR @WorkingDirectory <> '')
BEGIN

    DECLARE @test INT
    DECLARE @SQL_CMD VARCHAR(100)
    SET @SQL_CMD = 'EXEC xp_cmdshell '' IF NOT EXIST '+@WorkingDirectory+' MD '+@WorkingDirectory+' '' , NO_OUTPUT'; 
    EXEC (@SQL_CMD)

    IF (@test = 0)
        BEGIN
            PRINT 'Directory not exist or no access'
            RETURN -1
        END
END


-- @RPath
IF (@RPath <> NULL OR @RPath <> '')
BEGIN
    CREATE TABLE #temp_RPath (
                            Exist_File TINYINT
                           ,Exist_Dir TINYINT
                           ,Exist_RootDir TINYINT
                             )

    DECLARE @RPathINT VARCHAR(200)
    SELECT @RPathINT = REPLACE(@RPath,'"','')

    INSERT INTO #temp_RPath
    EXECUTE [master].[dbo].XP_FILEEXIST @RPathINT

    IF (SELECT Exist_Dir FROM #temp_RPath WHERE Exist_Dir = 1) IS NULL
        BEGIN
            PRINT 'R Path folder does not exist or folder name is wrong'
            RETURN -1
        END
    DROP TABLE #temp_RPath
END





-- Logical checks
-- 1) Variable Names must be in select list of SQL Statement
IF (@TargetTable IS NOT NULL AND @TargetTable <> '')
BEGIN

    IF (
        OBJECTPROPERTY ( object_id(@TargetTable),'ISTABLE') = 1
    OR
        OBJECTPROPERTY ( object_id(@TargetTable),'ISVIEW') = 1 
        )
       BEGIN
         DECLARE @varItem SMALLINT = 1
         WHILE (SELECT MAX(id) FROM @Variable_list) >= @varItem
         
                BEGIN
                        DECLARE @VariableInt VARCHAR(100)
                        SELECT @VariableInt = item FROM @Variable_list WHERE @varItem = id
                            IF COL_LENGTH(@TargetTable,@VariableInt) IS NULL
                            BEGIN
                            PRINT 'Column '+ @VariableInt +' does not exist or access denied to view the object'
                            RETURN -1
                            END
                    SET @varItem = @varItem + 1
                END
    
      END

END


IF (@ServerName <> NULL OR LEN(@ServerName) > 0) 
BEGIN
    DECLARE @server NVARCHAR(50)
    SELECT  @server = CONVERT(sysname, SERVERPROPERTY(N'servername'))
    IF @serverName <> @server
        BEGIN
            PRINT 'Bad server name or name does not exist'
            RETURN -1
        END
END


-- ////////////////////////////////////
-- PRINT '3. Checks validity of input parameters'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


-- ************************************    
-- *                                  *
-- *                                  *
-- *    MERGING SQL SERVER AND R      *
-- *                                  *
-- *                                  *
-- ************************************


-- Adding R Logic  
-- Based on selected statistics
    DECLARE @WorkingDirectoryR VARCHAR(50) = REPLACE(@WorkingDirectory, '\','/')
    DECLARE @ServerNameR VARCHAR(50) = REPLACE(@ServerName,'\','\\')
    DECLARE @SQL_EnvironmentR VARCHAR(2000)
    DECLARE @QueryStmt VARCHAR(8000) 
    SET @QueryStmt = 'SELECT ' +@Variables+ ' FROM '+ @TargetTable

IF @Statistics NOT IN ('8')
BEGIN
    SET @SQL_EnvironmentR = 'setwd("'+@WorkingDirectoryR+'")
    #install.packages("RODBC")
    #library("RODBC")
    if(!is.element("RODBC", installed.packages()))
    {install.packages("RODBC")
    }else{library("RODBC")}
    con <- odbcDriverConnect(''driver={SQL Server};server='+@ServerNameR+';database='+@DatabaseName+';trusted_connection=true'')
    fo <- sqlQuery(con, '''+@QueryStmt+''')
    out<-capture.output(';
END
IF @Statistics IN ('8')
BEGIN
    SET @SQL_EnvironmentR = 'setwd("'+@WorkingDirectoryR+'")
library(tm)
library(slam)
library(RODBC)
con <- odbcDriverConnect(''driver={SQL Server};server='+@ServerNameR+';database='+@DatabaseName+';trusted_connection=true'')
fo <- sqlQuery(con, '''+@QueryStmt+''')
myCorpus = Corpus(VectorSource(fo))
myCorpus = tm_map(myCorpus, tolower)
myCorpus = tm_map(myCorpus, removePunctuation)
myCorpus = tm_map(myCorpus, removeNumbers)
myCorpus = tm_map(myCorpus, removeWords, stopwords("english"))
myDTM = TermDocumentMatrix(myCorpus, control = list(minWordLength = 1))
m = as.matrix(myDTM)
v = sort(rowSums(m), decreasing = TRUE)
library(wordcloud)
set.seed(445)
png("C:\\DataTK\\WordCloud.jpg", width=12, height=8, units="in", res=300)
wordcloud(names(v), v, min.freq = 10)
dev.off()
close(con)';
END


IF @Statistics IN ('9')
BEGIN
    SET @SQL_EnvironmentR = 'setwd("'+@WorkingDirectoryR+'")
#if(!is.element("waffle", installed.packages()))
#    {install.packages("waffle")
#    }else{library("waffle")}
library(ggplot2)
library(waffle)
sample_data <- c(''Twitter (780h)''=780, ''Facebook (2402h)''=2402, ''Instagram (250h)''=250,''Linkedin (123h)''=123, ''Others (215h)''=215)
png("C:\\DataTK\\WordCloud.jpg", width=12, height=8, units="in", res=300)
waffle(sample_data/10, rows=8, size=0.5, 
       colors=c("#F1B2E1", "#B1DDF3", "#FFDE89", "#E3675C","#C2D985"), 
       title="Amount of days of Social Media", 
       xlab="1 Square = 1hour",
       flip = TRUE,
       reverse = TRUE)
dev.off()';
END


-- ////////////////////////////////////
-- PRINT '4. Concatenate R code 1. Part'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


-- ************************************    
-- *                                  *
-- *                                  *
-- * LIST OF ALL R STATISTICS /start/ *
-- *                                  *
-- *                                  *
-- ************************************

DECLARE @Stat VARCHAR(100)

-- summary(fo)
IF @Statistics = '1'
BEGIN
    SELECT @Stat = R_Code FROM  dbo.tableStatistics  WHERE id = @statistics
    SET @SQL_EnvironmentR = @SQL_EnvironmentR + @Stat;
END



IF @Statistics = '2'
BEGIN
    DECLARE @varItem_stat2 SMALLINT = 1
    DECLARE @var_stat2 VARCHAR(100)
    WHILE (SELECT NofVariables FROM dbo.tableStatistics WHERE id = @Statistics) >= @varItem_stat2
          BEGIN
            SELECT @var_stat2 = item FROM @Variable_list WHERE id = @varItem_stat2
            SELECT @Stat = REPLACE(R_Code,'!!Variable',@var_stat2) FROM  dbo.tableStatistics  WHERE id = @statistics
            SET @SQL_EnvironmentR = @SQL_EnvironmentR + + @Stat; 
            SET @varItem_stat2 = @varItem_stat2 + 1
          END
END


IF @Statistics = '3'
BEGIN
    DECLARE @varItem_stat3 SMALLINT = 1
    DECLARE @var_stat3 VARCHAR(100)
    DECLARE @Variable_list_INT TABLE  (item VARCHAR(100) NOT NULL)
    INSERT INTO @Variable_list_INT (item)
    SELECT R_Code FROM  dbo.tableStatistics  WHERE id = @statistics

    WHILE (SELECT NofVariables FROM dbo.tableStatistics WHERE id = @Statistics) >= @varItem_stat3
          BEGIN
            SELECT @var_stat3 = item FROM @Variable_list WHERE id = @varItem_stat3
            UPDATE @Variable_list_INT
                SET Item = REPLACE (item,'!!Variable'+CAST(@varItem_stat3 AS VARCHAR(2)), @var_stat3)
                
            SET @varItem_stat3 = @varItem_stat3 + 1
          END
    SET @Stat = (SELECT item FROM @Variable_list_INT)
    SET @SQL_EnvironmentR = @SQL_EnvironmentR + + @Stat; 
END


IF @Statistics = '4'

BEGIN
    DECLARE @varItem_stat4 SMALLINT = 1
    DECLARE @var_stat4 VARCHAR(100)
    WHILE (SELECT NofVariables FROM dbo.tableStatistics WHERE id = @Statistics) >= @varItem_stat4
          BEGIN
            SELECT @var_stat4 = item FROM @Variable_list WHERE id = @varItem_stat4
            SELECT @Stat = REPLACE(R_Code,'!!Variable',@var_stat4) FROM  dbo.tableStatistics  WHERE id = @statistics
            SET @SQL_EnvironmentR = @SQL_EnvironmentR + + @Stat; 
            SET @varItem_stat4 = @varItem_stat4 + 1
          END
END



IF @Statistics = '5'
BEGIN
  SELECT @Stat = R_Code FROM  dbo.tableStatistics  WHERE id = @statistics
  SET @SQL_EnvironmentR = @SQL_EnvironmentR + + @Stat;
END

IF @Statistics = '6'
BEGIN
  SELECT @Stat = R_Code FROM  dbo.tableStatistics  WHERE id = @statistics
  SET @SQL_EnvironmentR = @SQL_EnvironmentR + + @Stat;
END

IF @Statistics = '7'
BEGIN
  SELECT @Stat = R_Code FROM  dbo.tableStatistics  WHERE id = @statistics
  SET @SQL_EnvironmentR = @SQL_EnvironmentR + + @Stat;
END


-- ////////////////////////////////////
-- PRINT '5. Concatenate R code 2. Part'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

-- ************************************    
-- *                                  *
-- *                                  *
-- * LIST OF ALL R STATISTICS /end/   *
-- *                                  *
-- *                                  *
-- ************************************

IF @Statistics NOT IN ('8','9')
BEGIN
-- CLOSE RESULTS!
SET @SQL_EnvironmentR = @SQL_EnvironmentR + ')
cat(out,file="'+REPLACE(@workingDirectory,'\','/')+'/output.txt",sep="\n",append=TRUE)
close(con)
';
END




/*
cat(out,file="'+REPLACE(@workingDirectory,'\','/')+'/output.txt",sep="\n",append=TRUE)
close(con)
*/

/*
IF @Statistics = '8'
BEGIN
    SET @SQL_EnvironmentR8 = @SQL_EnvironmentR8 + 
    'png("'+REPLACE(@workingDirectory,'\','/')+'/WordCloud.jpg", width=12, height=8, units="in", res=300)
    wordcloud(names(v), v, min.freq = 10)
    dev.off()
    close(con)
    ';
END
*/

-- ////////////////////////////////////
-- PRINT '6. Concatenate R code 3. Part'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

IF EXISTS (SELECT [object_id] FROM sys.objects WHERE name = 'tableStmt')
DROP TABLE dbo.tableStmt

CREATE TABLE dbo.tableStmt 
        (
         id INT IDENTITY(1,1)
        ,SQLStmt NVARCHAR(MAX)
        ) 


INSERT INTO dbo.tableStmt
SELECT 'SELECT GETDATE()' UNION ALL
SELECT @SQL_EnvironmentR
-- PRINT @SQL_EnvironmentR

CREATE TABLE #temp_bcp (return_output VARCHAR(500))
DECLARE @sql VARCHAR(8000)
SET @sql = 'BCP "Select SQLStmt from '+@DatabaseName+'.dbo.tableStmt where id = (select max(id) from '+@DatabaseName+'.dbo.tableStmt)" queryout '+CAST(@WorkingDirectory AS VARCHAR(300))+'\mytest.R -c -t -T -S '+CAST(@ServerName AS VARCHAR(100))+' '
INSERT INTO #temp_bcp
EXEC XP_CMDSHELL @sql-- , NO_OUTPUT

IF (SELECT COUNT(*) FROM #temp_bcp WHERE return_output LIKE 'BCP copy out faile%') >= 1
    BEGIN
        PRINT 'BCP copy out failed!'
        RETURN -1 
    END

DROP TABLE #temp_bcp

-- ////////////////////////////////////
-- PRINT '7. Save R code in .R file'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


-- *******************
-- *                 *
-- * CALLING R DATA  *
-- *                 *
-- ******************* 

-- SET PATH TO R 
DECLARE @RPathINTEX VARCHAR(200)
SELECT @RpathINTEX = REPLACE(@RPath,'"','')
SELECT @RPathINTEX = @RPathINTEX + '\R"'
DECLARE @sqlCallR VARCHAR(8000)
CREATE TABLE #temp_sqlCallR (return_output VARCHAR(100))
SET @sqlCallR =  '"'+@RPathINTEX+' CMD BATCH --vanilla --slave '+@workingDirectory+'\mytest.R'

-- PRINT @sqlCallR

INSERT INTO #temp_sqlCallR
EXEC XP_CMDSHELL @sqlCallR --, NO_OUTPUT

IF (SELECT COUNT(*) FROM #temp_sqlCallR) > 1
    BEGIN
        PRINT 'Problems executing R batch from SQL Server XP_CMDSHELL'
        RETURN -1 
    END
DROP TABLE #temp_sqlCallR


-- ////////////////////////////////////
-- PRINT '8. Call R.exe and executes .R file'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


 
-- *********************
-- *                   *
-- * PRINTING RESULTS  *
-- *                   *
-- ********************* 

DECLARE @title VARCHAR(100)
SELECT @title = RStats FROM  dbo.tableStatistics
WHERE @statistics = id
PRINT 'Statistics: '+ @title 



DECLARE @FS INT, @FileId INT, @hr INT, @End INT
IF @statistics NOT IN ('8','9')
BEGIN
DECLARE @FileName VARCHAR(1000) = @workingDirectory + '\' + 'output.txt'
END
ELSE
BEGIN
 SET  @FileName  = @workingDirectory + '\' + 'WordCloud.jpg'
END
DECLARE @Chunk VARCHAR(8000)
DECLARE @String VARCHAR(MAX) =''


EXECUTE @hr = SP_OACreate 'Scripting.FileSystemObject', @FS OUT
EXECUTE @hr = SP_OAMethod @FS, 'OpenTextFile', @FileId OUT, @FileName, 1,false,0 


WHILE @hr=0
    BEGIN
        IF @HR=0 EXECUTE @hr = sp_OAGetProperty @FileId, 'AtEndOfStream', @End OUTPUT
        IF @End<>0 BREAK
        IF @HR=0 EXECUTE @hr = sp_OAMethod  @FileId, 'Read', @chunk OUTPUT,4000
        SELECT @String=@string+@chunk
    END

EXECUTE @hr = sp_OAMethod  @FileId, 'Close'
EXECUTE SP_OADestroy @FileId
PRINT @String

END

-- ////////////////////////////////////
-- PRINT '9. Returns R Results in MS SQL Environment'
-- \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


-- *********************
-- *                   *
-- *   DATA CLEANING   *
-- *                   *
-- ********************* 
IF EXISTS ( SELECT [object_id] FROM sys.objects  WHERE name = 'tableStatistics' )
DROP TABLE dbo.tableStatistics

IF EXISTS (SELECT [object_id] FROM sys.objects WHERE name = 'tableStmt')
DROP TABLE dbo.tableStmt



GO

Advertisements

2 thoughts on “24 Hours of PASS – Understanding and Visualizing Data Using R in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s