Permanence

To mimic the behavior of in-memory objects, when working with data in Andromeda the data is stored in a temporary location on the disk. You can modify the data as you can see fit, and when needed can save the data to a permanent location. Later this data can be loaded to a temporary location again and be read and modified, while keeping the saved data as is.

Technology

Andromeda heavily relies on RSQLite, an R wrapper around SQLite. SQLite is a low-weight but very powerful single-user SQL database that can run from a single file on the local file system. Although SQLite and therefore Andromeda can be queried using SQL, Andromeda favors using dbplyr, a dplyr implementation, to work with the data.

Closing Andromeda objects

Every Andromeda object will have a corresponding data file in a temporary location on your local file system. This file will be automatically deleted when the Andromeda object is no longer used. It is best practice not to rely on R to decide when to do this, but explicitly cause the file to be cleaned up by calling the close statement:

close(andr)
close(andr2)
close(andr3)

Once an Andromeda is closed the underlying file is deleted, and it can no longer be used. You can check whether an Andromeda object is still valid:

isValidAndromeda(andr)

## [1] FALSE

Temporary file location

By default Andromeda uses the default temporary file location of your operating system to store the Andromeda objects while your are working on them. You can override the location by setting the andromedaTempFolder option:

options(andromedaTempFolder = "c:/andromedaTemp")

This only applies to Andromeda objects that are created from that point onward. Prior objects will stay where they are.

Simple meta-data

If we wish to know the tables that exist in an Andromeda object, we can call the generic names function. Similarly, we can call colnames to get the names of the columns in a table:

names(andr)

## [1] "cars"     "fastCars"

colnames(andr$cars)

## [1] "speed" "dist"

Using SQL

In the end, an Andromeda is still an RSQLite database, and can be queried using SQL:

RSQLite::dbGetQuery(andr, "SELECT * FROM cars LIMIT 5;")

##   speed dist
## 1     4    2
## 2     4   10
## 3     7    4
## 4     7   22
## 5     8   16

However, for consistency it is recommended to use the dplyr functions instead.

Dates and times

One limitation of SQLite - the technology underlying Andromeda - is that it does not support date and time formats. Dates and times are automatically converted to numeric values when adding them to an Andromeda table. Converting them back needs to be done manually:

myData <- data.frame(someTime = as.POSIXct(c("2000-01-01 10:00", 
                                             "2001-01-31 11:00", 
                                             "2004-12-31 12:00")),
                     someDate = as.Date(c("2000-01-01", 
                                          "2001-01-31", 
                                          "2004-12-31")))
andr$myData <- myData
andr$myData %>% 
  collect() %>%
  mutate(someTime = restorePosixct(someTime),
         someDate = restoreDate(someDate))

## Warning: There were 2 warnings in `mutate()`.
## The first warning was:
## ℹ In argument: `someTime = restorePosixct(someTime)`.
## Caused by warning:
## ! Input to restorePosixct is already Posixct
## ℹ Run `dplyr::last_dplyr_warnings()` to see the 1 remaining warning.

## # A tibble: 3 × 2
##   someTime            someDate  
##   <dttm>              <date>    
## 1 2000-01-01 10:00:00 2000-01-01
## 2 2001-01-31 11:00:00 2001-01-31
## 3 2004-12-31 12:00:00 2004-12-31

Safe mode

For technical reasons it is not possible to write to the same Andromeda while reading from it. Writing to an Andromeda environment while inside a batchApply or groupApply on the that same Andromeda environment will therefore result in an error message. To avoid this, you can set the safe argument to TRUE. This will cause the table to first be copied to a temporary Andromeda before executing the function. However, this might not be very fast:

doSomething <- function(batch) {
    batch$speedSquared <- batch$speed^2
    if (is.null(andr$cars2)) {
      andr$cars2 <- batch
    } else {
      appendToTable(andr$cars2, batch)
    }
  }
  batchApply(andr$cars, doSomething, safe = TRUE)

Note that this only a restriction of batchApply and groupApply. We could have achieved the same task as the example above much faster using only dplyr:

andr$cars2 <-
  andr$cars %>%
  mutate(speedSquared = speed^2)

Import dplyr

Make sure dplyr is imported in the NAMESPACE. That way all dplyr functions can be used on the Andromeda objects in your functions.

Import .data from rlang

If we reference variables in our dplyr function calls we should precede them with .data$ to avoid R check warnings about using an unknown variable. For example, instead of

andr$cars %>%
  filter(speed > 10)

we should write

andr$cars %>%
  filter(.data$speed > 10)

to avoid R check warnings about speed being an unknown variable.

Beware of variable name confusion

dplyr sometimes confuses variable names, so we have to help. For example, this code:

speed <- 10
andr$cars %>%
  filter(.data$speed == speed)

will not actually filter anything, because the second speed variable in the filter statement is interpreted to refer to the speed field in the data, not the variable we defined earlier. One way to avoid this is by forcing early evaluation of the variable:

speed <- 10
andr$cars %>%
  filter(.data$speed == !!speed)