0

I have a series of large zipped files that I have been unzipping to load directly into a MySQL database for querying from R.

I'll continue with this example (on x86_64 GNU/Linux):

> write.csv(iris, file = "iris.csv", row.names = FALSE, quote = FALSE)
> system("gzip iris.csv")
> list.files(pattern = "iris")
[1] "iris.csv"    "iris.csv.gz"

I currently load the unzipped file in the following way:

> library(RSQLite)
> con <- dbConnect(RSQLite::SQLite(), dbname = "test_db")
> dbWriteTable(con, name = "iris", value = "iris.csv", field.types = list(Sepal.Length = "decimal(6, 2)", Sepal.Width = "decimal(6, 2)", Petal.Length = "decimal(6, 2)", Petal.Width = "decimal(6, 2)", Species = "varchar(15)"), row.names = FALSE)
[1] TRUE

I am wondering if it is possible to do a direct table write to the DB using the zipped file iris.csv.gz?

EDIT:

I am aware of gzfile but to my understanding its usage would necessitate bringing the file into memory before writing to the MySQL DB, something I am looking to avoid (correct me if I'm misunderstanding)

mlegge
  • 6,763
  • 3
  • 40
  • 67
  • mysql has no clue what zipped data is. you HAVE to unzip the data first before mysql can do anything with the raw csv. – Marc B Aug 14 '15 at 19:45
  • check out this may be of interest http://stackoverflow.com/a/16125858/1816093 – Drew Aug 14 '15 at 19:46
  • @MarcB ok, thanks -- if you want to make an answer I'll accept. I was hoping there was an R package that I could use in tandem with RSQLite, not necessarily that MySQL handle the zipped data. – mlegge Aug 14 '15 at 19:48
  • well clearly mysql can use zip files. The task is to find R libraries that will – Drew Aug 14 '15 at 19:55
  • here is a popular R / zip link http://stackoverflow.com/a/3053883/1816093 – Drew Aug 14 '15 at 19:58

0 Answers0