5 Replies Latest reply on Dec 14, 2017 3:46 PM by Brian Wang

    Using R scripts to write back to SQL databases

    Brandon Ohlaug

      I am trying to have an end user look at the results, apply their business knowledge and then adjust a value in a SQL table.  To adjust the value, I am having the end user select how much they want to adjust the value by (Adjustment parameter) and what category they would like to adjust for (Category parameter).  I am trying to get these selections to kick off a R script in a calculated field and write their values back to a table in SQL. 

       

      The datasource is a SQL table called ForecastTable.  For this simple example there are two fields, Quantity and Class, and could look something like below.

      Quantity      Class

      500               Category 1

      750               Category 2

       

      Here is my R script I am using to try to write this back to SQL.  Access is not an issue and the connection is also not the issue.  I am able to run this code in R directly and it works fine if I plug in values for the variables.

       

      SCRIPT_REAL("library(RODBC);

      library(Rserve);

      dbhandle <- odbcDriverConnect('driver={SQL Server};server=TEST;database=Test1;trusted_connection=true');

      Quantity2 <- .arg1[1]

      Category2 <- .arg2[2]

      sqlQuery(dbhandle, "UPDATE [Test].[dbo].[ForecastTable]

                  set [Quantity] = Quantity2

                  where [Class] = Category2)",

      [Adjustment], [Category])

       

       

      In the end, once the data is written back to the SQL table, the end user would refresh the datasource and see the changes they have made.

        • 1. Re: Using R scripts to write back to SQL databases
          Patrick A Van Der Hyde

          Hello Brandon,

           

          What is the error that is being encountered?  In straight up Tableau, we prevent writing/altering sql entirely so this is an interesting work around if you are getting it to function as desired.

           

          Patrick

          • 2. Re: Using R scripts to write back to SQL databases
            Brandon Ohlaug

            Hi,

            My code works in R when I run it, so I know this is correct R code.  Below is the error code I am getting in my calculated field.  I believe it is erroring out because of the quotation marks in the SQL update statement, when I change those to single quotes the error msg goes away, however, it is not valid R code so it will not execute correctly.

             

            If you have any other suggestions to updating SQL, even going outside of straight up Tableau, I am open to trying that as well.  I have looked into the Shiny package in R as well, but am running into security issues with that so I am not sure that will work for me.  I have also just begun looking into the JavaScript API so will see where that takes me.

             

            Thanks

            Brandon

            • 3. Re: Using R scripts to write back to SQL databases
              Jonathan Drummey

              The simplest workaround I can think of is for you to turn your R code for the update into a library with the desired values as arguments and then you can load that library either when starting Rserve (better performance but leaving a security hole) or in the Tableau call to R.

               

              One note on using Tableau's R integration to do write back is that Tableau calls R for many types of view updates. Refreshes, changing filters, and changing parameters are the big three. This will lead to the SQL update being executed again and again unless you do something fancy to add some sort of checking for current/prior state. The JS API could theoretically let you avoid this issue, but that has its own complexities.

               

              Jonathan

               

              Sent from my iPhone

              • 4. Re: Using R scripts to write back to SQL databases
                Brandon Ohlaug

                Thank you, I will give this a try or going with the API.  Thank you for your responses

                • 5. Re: Using R scripts to write back to SQL databases
                  Brian Wang

                  Hi Brandon,

                  This discussion is long time ago, and I am not sure if you still remember this case.

                  I also kind of run into similar problem that users request to write comments back into SQL databases. Can you share with me how you design the dashboard and how to use the R codes in Tableau?

                  Appreciate your help!

                   

                  -Brian