csv_to_sql: Save a delimited text table into a single table sqlite...

In rOpenStats/COVID19AR: for analysing COVID-19 Argentina's outbreak

Description

The table can be a comma separated (csv) or a tab separated (tsv) or any other delimited text file. The file is read in chunks. Each chunk is copied in the same sqlite table database before the next chunk is loaded into memory. See the INBO tutorial Handling large files in R to learn more about.

Usage

csv_to_sql(
  csv_file,
  table_name,
  delim = ",",
  quote = "\"",
  pre_process_size = 1000,
  chunk_size = 50000,
  show_progress_bar = TRUE,
  con = dbConnect(SQLite(), dbname = sqlite_file),
  cols.spec = NULL,
  disconnect.after = TRUE,
  n_max = Inf,
  logger = lgr,
  ...
)

Arguments

csv_file	Name of the text file to convert.
table_name	Name of the table to store the data table in the sqlite database.
delim	Text file delimiter (default ",").
pre_process_size	Number of lines to check the data types of the individual columns (default 1000).
chunk_size	Number of lines to read for each chunk (default 50000).
show_progress_bar	Show progress bar (default TRUE).
con	= dbConnect(SQLite(), dbname = sqlite_file),
cols.spec	= NULL Columns specification for read_delim
disconnect.after	= TRUE
n_max	= Inf
logger	= lgr
...	Further arguments to be passed to read_delim.
sqlite_file	Name of the newly created sqlite file.

Details

Version modified by @kenarab

Value

a SQLite database

Remark

The callback argument in the read_delim_chunked function call refers to the custom written callback function 'append_to_sql' applied to each chunk.

Examples

## Not run: 
library(R.utils)
library(dplyr)
csv.name <- "2016-04-20-processed-logs-big-file-example.csv"
db.name <- "2016-04-20-processed-logs-big-file-example.db"
# download the CSV file example
csv.url <- paste("https://s3-eu-west-1.amazonaws.com/lw-birdtracking-data/",
  csv.name, ".gz",
  sep = ""
)
download.file(csv.url, destfile = paste0(csv.name, ".gz"))
gunzip(paste0(csv.name, ".gz"))
# Make a SQLite database
sqlite_file <- "example2.sqlite"
sqlite_conn <- dbConnect(SQLite(), dbname = sqlite_file)
table_name <- "birdtracks"
csv_to_sql(
  csv_file = csv.name,
  con = sqlite_conn,
  table_name = table_name
)
# Get access to SQLite database
my_db <- src_sqlite(sqlite_file, create = FALSE)
bird_tracking <- tbl(my_db, "birdtracks")
# Example query via dplyr
results <- bird_tracking %>%
  filter(device_info_serial == 860) %>%
  select(date_time, latitude, longitude, altitude) %>%
  filter(date_time < "2014-07-01") %>%
  filter(date_time > "2014-03-01") %>%
  as_tibble()
head(results)

## End(Not run)

rOpenStats/COVID19AR documentation built on Feb. 3, 2022, 10:23 p.m.