R/utils.R

In zambujo/sqlclone: Clone SQL Tables and Databases in R with DBI

#' Get list of tables in schema
#'
#' @param schema DB schema
#' @param connection DBI connection
#'
#' @return A vector with the names of the tables in the schema.
#' @export
#'
#' @examples
get_tbls <- function(schema, connection) {
  sql_query <- glue::glue(
    "SELECT table_name ",
    "FROM information_schema.tables ",
    "WHERE table_schema = '{ schema }'"
  )
  tables <- DBI::dbGetQuery(connection, sql_query)
  return(tables)
}


#' Copy a table from one connection to another by chuncks
#'
#' @param tbl_name The table to copy
#' @param schema The schema
#' @param from_conn Origin DBI connection
#' @param to_conn Destination DBI connection
#' @param n Chunck size. Useful for very large tables.
copy_tbl <- function(tbl_name, schema, from_conn, to_conn, n = 1000000L) {
  usethis::ui_info(glue::glue("quering { tbl_name } ....."))
  sql_query <- glue::glue("SELECT * FROM { schema }.{ tbl_name } LIMIT { n }")
  df_chunk <- DBI::dbGetQuery(from_conn, sql_query)
  DBI::dbWriteTable(to_conn, tbl_name, df_chunk)
  i <- 1L
  while (nrow(df_chunk) == n) {
    offset <- i * n
    usethis::ui_info(glue::glue("{ offset } rows ....."))
    i <- 1L + i
    sql_query <- glue::glue("SELECT * FROM { schema }.{ tbl_name } ",
                            "LIMIT { n } ",
                            "OFFSET { offset }")
    df_chunk <- DBI::dbGetQuery(from_conn, sql_query)
    DBI::dbWriteTable(to_conn, tbl_name, df_chunk,
                      append = TRUE, row.names = FALSE)
  }
  usethis::ui_done(glue::glue("....... { schema }.{ tbl_name } saved"))
}


#' Copy a table from one connection to a CSV file by chuncks
#'
#' @param tbl_name The table to save
#' @param schema The schema
#' @param from_conn Origin DBI connection
#' @param to_file Destination file
#' @param n Chunck size. Useful for very large tables.
save_tbl <- function(schema, tbl_name, from_conn, to_file, n = 1000000L) {
  usethis::ui_info(glue::glue("quering { tbl_name } ....."))
  sql_query <- glue::glue("SELECT * FROM { schema }.{ tbl_name } LIMIT { n }")
  df_chunk <- DBI::dbGetQuery(from_conn, sql_query)
  vroom::vroom_write(df_chunk, to_file, delim = ",", append = FALSE)
  i <- 1L
  while (nrow(df_chunk) == n) {
    offset <- i * n
    i <- 1L + i
    usethis::ui_info(glue::glue("{ offset } rows ....."))
    sql_query <- glue::glue("SELECT * FROM { schema }.{ tbl_name } ",
                            "LIMIT { nrow_exp } ",
                            "OFFSET { offset }")
    df_chunk <- DBI::dbGetQuery(from_conn, sql_query)
    vroom::vroom_write(df_chunk, to_file, delim = ",", append = TRUE)
  }
  usethis::ui_done(glue::glue("....... { schema }.{ tbl_name } ",
                              "saved to { to_file }"))
}