Fetching big SQL table in the web app session

Question

I'm quite new in web app so apologize if my question is abit basic. I'm developing a Web app with R shiny where the inputs are very large tables from Azure SQL server. They are 20 tables each in the order of hundred-thousand rows and hundreds of columns containing numbers, Characters and etc. I have no problem calling them, my main issue is that it takes so much time to fetch everything from Azure SQL server. It takes approximately 20 minutes. So the user of the web app needs to wait quite a long. I'm using DBI package as follows:

db_connect <- function(database_config_name){
  dbConfig <- config::get(database_config_name)
  connection <- DBI::dbConnect(odbc::odbc(),
                        Driver = dbConfig$driver,
                        Server = dbConfig$server,
                        UID    = dbConfig$uid,
                        PWD    = dbConfig$pwd,
                        Database = dbConfig$database,
                        encoding = "latin1"
  )

  return(connection)
}

and then fetching tables by :

connection <- db_connect(db_config_name)
table <- dplyr::tbl(con, dbplyr::in_schema(fetch_schema_name(db_config_name,table_name,data_source_type), fetch_table_name(db_config_name,table_name,data_source_type)))

I searched a lot but didn't come across a good solution, I appreciate any solutions can tackle this problem.

Answer 1

I work with R accessing SQL Server (not Azure) daily. For larger data (as in your example), I always revert to using the command-line tool sqlcmd, it is significantly faster. The only pain point for me was learning the arguments and working around the fact that it does not return proper CSV, there is post-query munging required. You may have an additional pain-point of having to adjust my example to connect to your Azure instance (I do not have an account).

In order to use this in a shiny environment and preserve its interactivity, I use the processx package to start the process in the background and then poll its exit status periodically to determine when it has completed.

Up front: this is mostly a "loose guide", I do not pretend that this is a fully-functional solution for you. There might be some rough-edges that you need to work through yourself. For instance, while I say you can do it asynchronously, it is up to you to work the polling process and delayed-data-availability into your shiny application. My answer here provides starting the process and reading the file once complete. And finally, if encoding= is an issue for you, I don't know if sqlcmd does non-latin correctly, and I don't know if or how to fix this with its very limited, even antiquated arguments.

Steps:

1. Save the query into a text file. Short queries can be provided on the command-line, but past some point (128 chars? I don't know that it's clearly defined, and have not looked enough recently) it just fails. Using a query-file is simple enough and always works, so I always use it.

   I always use temporary files for each query instead of hard-coding the filename; this just makes sense. For convenience (for me), I use the same tempfile base name and append .sql for the query and .csv for the returned data, that way it's much easier to match query-to-data in the temp files. It's a convention I use, nothing more.

   tf <- tempfile()
   # using the same tempfile base name for both the query and csv-output temp files
   querytf <- paste0(tf, ".sql")
   writeLines(query, querytf)
   csvtf <- paste0(tf, ".csv")
   # these may be useful in troubleshoot, but not always [^2]
   stdouttf <- paste0(tf, ".stdout")
   stderrtf <- paste0(tf, ".stderr")
   

2. Make the call. I suggest you see how fast this is in a synchronous way first to see if you need to add an async query and polling in your shiny interface.

   exe <- "/path/to/sqlcmd" # or "sqlcmd.exe"
   args <- c("-W", "b", "-s", "\037", "-i", querytf, "-o", csvtf,
             "-S", dbConfig$server, "-d", dbConfig$database,
             "-U", dbConfig$uid, "-P", dbConfig$pwd)
   ## as to why I use "\037", see [^1]
   ## note that the user id and password will be visible on the shiny server
   ## via a `ps -fax` command-line call
   proc <- processx::process$new(command = exe, args = args,
                                 stdout = stdouttf, stderr = stderrtf) # other args exist
   # this should return immediately, and should be TRUE until
   # data retrieval is done (or error)
   proc$is_alive()
   # this will hang (pause R) until retrieval is complete; if/when you
   # shift to asynchronous queries, do not do this
   proc$wait()
   

   One can use processx::run instead of process$new and proc$wait(), but I thought I'd start you down this path in case you want/need to go asynchronous.

3. If you go with an asynchronous operation, then periodically check (perhaps every 3 or 10 seconds) proc$is_alive(). Once that returns FALSE, you can start processing the file. During this time, shiny will continue to operate normally. (If you do not go async and therefore choose to proc$wait(), then shiny will hang until the query is complete.)

   If you make a mistake and do not proc$wait() and try to continue with reading the file, that's a mistake. The file may not exist, in which case it will err with No such file or directory. The file may exist, perhaps empty. It may exist and have incomplete data. So really, make a firm decision to stay synchronous and therefore call proc$wait(), or go asynchronous and poll periodically until proc$is_alive() returns FALSE.

4. Reading in the file. There are three "joys" of using sqlcmd that require special handling of the file.

   1. It does not do embedded quotes consistently, which is why I chose to use "\037" as a separator. (See [^1].)
   2. It adds a line of dashes under the column names, which will corrupt the auto-classing of data when R reads in the data. For this, we do a two-step read of the file.
   3. Nulls in the database are the literal NULL string in the data. For this, we update the na.strings= argument when reading the file.

   exitstat <- proc$get_exit_status()
   if (exitstat == 0) {
     ## read #1: get the column headers
     tmp1 <- read.csv(csvtf, nrows = 2, sep = "\037", header = FALSE)
     colnms <- unlist(tmp1[1,], use.names = FALSE)
     ## read #2: read the rest of the data
     out <- read.csv(csvtf, skip = 2, header = FALSE, sep = "\037",
                     na.strings = c("NA", "NULL"), quote = "")
     colnames(out) <- colnms
   } else {
     # you should check both stdout and stderr files, see [^2]
     stop("'sqlcmd' exit status: ", exitstat)
   }
   

Note:

1. After a lot of pain with several issues (some in sqlcmd.exe, some in data.table::fread and other readers, all dealing with CSV-format non-compliance), at one point I chose to stop working with comma-delimited returns, instead opting for the "\037" field Delimiter. It works fine with all CSV-reading tools and has fixed so many problems (some not mentioned here). If you're not concerned, feel free to change the args to "-s", "," (adjusting the read as well).

2. sqlcmd seems to use stdout or stderr in different ways when there are problems. I'm sure there's rationale somewhere, but the point is that if there is a problem, check both files.

   I added the use of both stdout= and stderr= because of a lot of troubleshooting I did, and continue to do if I munge a query. Using them is not strictly required, but you might be throwing caution to the wind if you omit those options.

3. By the way, if you choose to only use sqlcmd for all of your queries, there is no need to create a connection object in R. That is, db_connect may not be necessary. In my use, I tend to use "real" R DBI connections for known-small queries and the bulk sqlcmd for anything above around 10K rows. There is a tradeoff; I have not measured it sufficiently in my environment to know where the tipping point is, and it is likely different in your case.