Alex Alex - 26 days ago 6
MySQL Question

How to get all data from Tables in SQL with similar name in R

i am new to R and i want to merge multiple Tables from my SQL DB with R.

The problem is not to merge them, but to get the query. I have a lot of tables with similar name(just numbers at the end are different) in the DB.

Now i want the data in these tables to work with them in R. I already tried to import these table names from a .txt document and this works. But i can´t use the function

dbGetQuery(connection, "SELECT * FROM...")


right with with method because it allows only the real table name and no variable with multiple names.

Over that i tried

dbGetQuery(connection,
"SELECT * FROM INFORMATION_SCHEMA.tables WHERE TABLE_NAME LIKE '..._%'")


but this gives just table names and not the data in there.

I hope anyone can understand my problem.

Answer Source

Import all tables to list and merge in R. (Included LIMIT 100 for test purposes)

tables <- dbGetQuery(con, {"
  SELECT concat(TABLE_SCHEMA,'.',TABLE_NAME) tab
  FROM INFORMATION_SCHEMA.tables 
  WHERE TABLE_NAME LIKE 'pattern%'"})$tab


data <- 
  lapply(tables, function(table){
    dbGetQuery(con, sprintf({"
      SELECT * 
      FROM %s
      LIMIT 100
      "}, table)
    )
})

library(magrittr);library(dplyr)
data %<>% bind_rows() 

Or if one wants to keep it in MySQL, and is sure that tables have same structure, function below returns query which union all tables matching pattern:

union_all_mysql <- function(pattern){
  tables <- dbGetQuery(con, sprintf({"
  SELECT concat(TABLE_SCHEMA,'.',TABLE_NAME) tab
    FROM INFORMATION_SCHEMA.tables
    WHERE TABLE_NAME LIKE '%s'"}, pattern)
  )$tab

  query <- 
    lapply(tables, function(table) 
      sprintf("select * from %s", table) ) %>%
    unlist


  paste(query,collapse=" union ")

}


union_all_mysql("users_%")