Unarchive a list of compressed tsv files into a database

Unarchive a list of compressed tsv files into a database

Usage

unark(
  files,
  db_con,
  streamable_table = NULL,
  lines = 50000L,
  overwrite = "ask",
  encoding = Sys.getenv("encoding", "UTF-8"),
  tablenames = NULL,
  try_native = TRUE,
  ...
)

Arguments

files

vector of filenames to be read in. Must be tsv format, optionally compressed using bzip2, gzip, zip, or xz format at present.

db_con

a database src (src_dbi object from dplyr)

streamable_table

interface for serializing/deserializing in chunks

lines

number of lines to read in a chunk.

overwrite

should any existing text files of the same name be overwritten? default is "ask", which will ask for confirmation in an interactive session, and overwrite in a non-interactive script. TRUE will always overwrite, FALSE will always skip such tables.

encoding

encoding to be assumed for input files.

tablenames

vector of tablenames to be used for corresponding files. By default, tables will be named using lowercase names from file basename with special characters replaced with underscores (for SQL compatibility).

try_native

logical, default TRUE. Should we try to use a native bulk import method for the database connection? This can substantially speed up read times and will fall back on the DBI method for any table that fails to import. Currently only MonetDBLite connections support this.

...

additional arguments to streamable_table$read method.

Value

the database connection (invisibly)

Details

unark will read in a files in chunks and write them into a database. This is essential for processing large compressed tables which may be too large to read into memory before writing into a database. In general, increasing the lines parameter will result in a faster total transfer but require more free memory for working with these larger chunks.

If using readr-based streamable-table, you can suppress the progress bar by using options(readr.show_progress = FALSE) when reading in large files.

Examples

# \donttest{
## Setup: create an archive.
library(dplyr)
dir <- tempdir()
db <- dbplyr::nycflights13_sqlite(tempdir())

## database -> .tsv.bz2
ark(db, dir)
#> Warning: overwriting airlines.tsv.bz2
#> Exporting airlines in 50000 line chunks:
#> 	...Done! (in 0.002443314 secs)
#> Warning: overwriting airports.tsv.bz2
#> Exporting airports in 50000 line chunks:
#> 	...Done! (in 0.01579881 secs)
#> Warning: overwriting flights.tsv.bz2
#> Exporting flights in 50000 line chunks:
#> 	...Done! (in 8.193941 secs)
#> Warning: overwriting planes.tsv.bz2
#> Exporting planes in 50000 line chunks:
#> 	...Done! (in 0.0235095 secs)
#> Warning: overwriting weather.tsv.bz2
#> Exporting weather in 50000 line chunks:
#> 	...Done! (in 0.5831811 secs)

## list all files in archive (full paths)
files <- list.files(dir, "bz2$", full.names = TRUE)

## Read archived files into a new database (another sqlite in this case)
new_db <- DBI::dbConnect(RSQLite::SQLite())
unark(files, new_db)
#> Importing /tmp/Rtmpu4Y6Rz/airlines.tsv.bz2 in 50000 line chunks:
#> 	...Done! (in 0.009640694 secs)
#> Importing /tmp/Rtmpu4Y6Rz/airports.tsv.bz2 in 50000 line chunks:
#> 	...Done! (in 0.01828122 secs)
#> Importing /tmp/Rtmpu4Y6Rz/flights.tsv.bz2 in 50000 line chunks:
#> 	...Done! (in 5.677197 secs)
#> Importing /tmp/Rtmpu4Y6Rz/planes.tsv.bz2 in 50000 line chunks:
#> 	...Done! (in 0.02773046 secs)
#> Importing /tmp/Rtmpu4Y6Rz/weather.tsv.bz2 in 50000 line chunks:
#> 	...Done! (in 0.1742442 secs)

## Prove table is returned successfully.
tbl(new_db, "flights")
#> # Source:   table<flights> [?? x 19]
#> # Database: sqlite 3.40.1 []
#>     year month   day dep_time sched_de…¹ dep_d…² arr_t…³ sched…⁴ arr_d…⁵ carrier
#>    <int> <int> <int>    <int>      <int>   <int>   <int>   <int>   <int> <chr>  
#>  1  2013     1     1      517        515       2     830     819      11 UA     
#>  2  2013     1     1      533        529       4     850     830      20 UA     
#>  3  2013     1     1      542        540       2     923     850      33 AA     
#>  4  2013     1     1      544        545      -1    1004    1022     -18 B6     
#>  5  2013     1     1      554        600      -6     812     837     -25 DL     
#>  6  2013     1     1      554        558      -4     740     728      12 UA     
#>  7  2013     1     1      555        600      -5     913     854      19 B6     
#>  8  2013     1     1      557        600      -3     709     723     -14 EV     
#>  9  2013     1     1      557        600      -3     838     846      -8 B6     
#> 10  2013     1     1      558        600      -2     753     745       8 AA     
#> # … with more rows, 9 more variables: flight <int>, tailnum <chr>,
#> #   origin <chr>, dest <chr>, air_time <int>, distance <int>, hour <int>,
#> #   minute <int>, time_hour <dbl>, and abbreviated variable names
#> #   ¹​sched_dep_time, ²​dep_delay, ³​arr_time, ⁴​sched_arr_time, ⁵​arr_delay
# }