Los benditos paquetes

Asegúrate de instalar estos 5 paquetes:

• readr: Para leer el archivo delimitado por comas (csv) por tabulaciones o por el limite que sea
• DBI, RSQLite: Para comunicarnos con las bases de datos y usar SQLite
• dplyr y dbplyr: Para sacar información de la base de datos

library(readr)
library(DBI)
library(RSQLite)
library(dplyr)
library(dbplyr)

¿Que tan grande es mi base de datos? ¿Cuales son sus características?

Primero definimos la ruta de nuestra base de datos, le vamos a poner delim_file:

delim_file <- "C:/Censo/Microdato_Censo2017-Personas.csv"

Para ver sus características vamos a leer sus primeras 2 lineas, para eso vamos a usar la función readLines

readLines(delim_file, n=2)

## [1] "REGION;PROVINCIA;COMUNA;DC;AREA;ZC_LOC;ID_ZONA_LOC;NVIV;NHOGAR;PERSONAN;P07;P08;P09;P10;P10COMUNA;P10PAIS;P11;P11COMUNA;P11PAIS;P12;P12COMUNA;P12PAIS;P12A_LLEGADA;P12A_TRAMO;P13;P14;P15;P15A;P16;P16A;P16A_OTRO;P17;P18;P19;P20;P21M;P21A;P10PAIS_GRUPO;P11PAIS_GRUPO;P12PAIS_GRUPO;ESCOLARIDAD;P16A_GRUPO;REGION_15R;PROVINCIA_15R;COMUNA_15R;P10COMUNA_15R;P11COMUNA_15R;P12COMUNA_15R"
## [2] "15;152;15202;1;2;6;13225;1;1;1;1;1;73;1;98;998;3;15101;998;1;98;998;9998;98;2;4;6;2;1;2;98;7;98;98;98;98;9998;998;998;998;4;2;15;152;15202;98;15101;98"

Ya con esta información podemos ver los nombres de los encabezados y ver que el separador (delim) es un punto y coma (;)

Para obtener el número de filas de una base de datos grande R no nos va a servir, ya que no la vamos a poder cargar, así que utilizaremos un comando de Powershell (get content) en el caso de estar en Windows o un comando de sistema (wc) en el caso de estar en Linux.

Para saber cuantas filas tiene mi archivo (esto se demora un poco):

En Windows:

total_records <- system2("powershell", args = c("Get-content", delim_file, "|", "Measure-Object", "–Line"),
                         stdout = TRUE)
total_records <- as.numeric(gsub("[^0-9]", "", paste(total_records, collapse = ""))) - 1
total_records

## [1] 17574003

17574003 registros o lineas (dejando fuera el encabezado), ese el numero de personas que registró el censo del año 2017, así que estamos bien.

Nota: Si tienes Windows 10 no debería haber problemas, pero si tienes Windows 7 u 8 te recomiendo ver que versión de powershell tienes instalada estas instrucciones de Microsoft e instalar powershell 5.1 siguiendo este link tambien de Microsoft

En Linux sería algo así:

 total_records <- system2("wc", args = c("-l", delim_file), stdout = TRUE) %>%
 sub(normalizePath(delim_file), "", .) %>%
 as.integer() %>%
       {
         . - 1
       }

Crear la ruta a la base de datos

Para crear la base de datos vamos a generar una ruta a un futuro archivo de extensión .sqlite3,

Definimos la ruta y el nombre de la tabla dentro de la base de datos, necesitaremos este nombre para extraer datos en el futuro, otra gracia de estas bases de datos es que pueden contener varias tablas dentro de ellas.

table_name_censo <- "censo2017"
sqlite_file_censo <- "C:/Censo/censo2017.sqlite3"

La prometida funcion

Teniendo el numero total de filas del archivo, las rutas definidas y el delimitador (coma, punto y coma, espacio etc), podremos usar la siguiente función:

read_delim2sqlite <- function(delim_file, delim, sqlite_file, table_name, batch_size = 10000, OS = "Windows") {

  ## establish a connection to the database
  condb <- dbConnect(SQLite(), sqlite_file)

  ## get the total number of records in the file
  # in Unix
  if (OS == "Unix") {
    total_records <- system2("wc", args = c("-l", delim_file), stdout = TRUE) %>%
      sub(normalizePath(delim_file), "", .) %>%
      as.integer() %>%
      {
        . - 1
      }
  } else {
    # In windows
    total_records <- system2("powershell", args = c("Get-content", delim_file, "|", "Measure-Object", "–Line"), stdout = TRUE)
    total_records <- as.numeric(gsub("[^0-9]", "", paste(total_records, collapse = ""))) - 1
  }

  message("Total records: ", total_records)

  ## find the number of passes needed based on size of each batch
  passes <- total_records %/% batch_size
  remaining <- total_records %% batch_size

  message("Total Passes to complete: ", passes)

  ## first pass determines header and column types
  dat <- read_delim(delim_file, delim, n_max = batch_size, progress = FALSE) %>% as.data.frame()
  if (nrow(problems(dat)) > 0) print(problems(dat))
  col_names <- colnames(dat)
  col_types <- c(character = "c", numeric = "d", integer = "i", logical = "l", Date = "c") %>%
    .[sapply(dat, class)] %>%
    paste0(collapse = "")

  ## write to database table
  dbWriteTable(condb, table_name, dat, overwrite = TRUE)

  ## multiple passes
  for (p in 2:passes) {
    message("Pass number: ", p, ", Progress:", round(p / passes, 2) * 100, "%")
    read_delim(delim_file, delim,
      col_names = col_names, col_types = col_types,
      skip = (p - 1) * batch_size + 1, n_max = batch_size, progress = FALSE
    ) %>%
      as.data.frame() %>%
      dbWriteTable(condb, table_name, ., append = TRUE)
  }

  if (remaining) {
    read_delim(delim_file, delim,
      col_names = col_names, col_types = col_types,
      skip = p * batch_size + 1, n_max = remaining, progress = FALSE
    ) %>%
      as.data.frame() %>%
      dbWriteTable(condb, table_name, ., append = TRUE)
  }

  ## close the database connection
  dbDisconnect(condb)
}

Se ve algo intimidante la función, pero la verdad no es tan compleja, lo que hace es leer el archivo por partes de cierto tamaño (batch_size), utilizando la función read_delim del paquete “readr” para leer rápidamente el archivo, luego inserta la parte leída en la base de datos SQLite, y repite el proceso hasta la ultima linea del archivo.

Los argumentos de la función son los siguientes:

delim file: Ruta al archivo delimitado (en este caso la BD del censo). delim: Caracter delimitador, en este caso un punto y coma. sqlite_file: Ruta a la base de datos de SQLite, debe terminar con “.sqlite3”. table_name: Nombre de la tabla dentro de la base de datos de SQLite. batch_size: Numero de registros que va a leer en cada iteración. OS: Sistema operativo que estamos utilizando, puede ser “Windows” o “Unix” (Solo probado en Linux, no en Mac).

Mover la csv o txt a la base de datos SQLlite

Con lo que ya tenemos preparado podemos usar la función, en este caso, para asegurarme de no quedarme sin RAM, voy a pasar la base de datos en conjuntos de un millon de lineas (batch_size), por lo que debería terminarse en 18 iteraciones.

Advertencia, esto tardará unos cuantos minutos, vayan a cebarse un mate

read_delim2sqlite(delim_file, delim=";", sqlite_file = sqlite_file_censo, 
                  table_name = table_name_censo, batch_size=1000000)

## Total records: 17574003

## Total Passes to complete: 17

## Parsed with column specification:
## cols(
##   .default = col_integer(),
##   P18 = col_character()
## )

## See spec(...) for full column specifications.

## Pass number: 2, Progress:12%

## Pass number: 3, Progress:18%

## Pass number: 4, Progress:24%

## Pass number: 5, Progress:29%

## Pass number: 6, Progress:35%

## Pass number: 7, Progress:41%

## Pass number: 8, Progress:47%

## Pass number: 9, Progress:53%

## Pass number: 10, Progress:59%

## Pass number: 11, Progress:65%

## Pass number: 12, Progress:71%

## Pass number: 13, Progress:76%

## Pass number: 14, Progress:82%

## Pass number: 15, Progress:88%

## Pass number: 16, Progress:94%

## Pass number: 17, Progress:100%

Este proceso tan demoroso solo lo haremos una vez. Como podemos ver se crea un archivo .sqlite3, que incluso es un poco más pequeño que nuestro .csv

Archivo SQLite creado