Are data tables with more than 2^31 rows supported in R with the data table package yet?

Question

I am trying to do a cross join (from the original question here), and I have 500GB of ram. The problem is that the final data.table has more than 2^31 rows, so I get this error:

Error in vecseq(f__, len__, if (allow.cartesian || notjoin || !anyDuplicated(f__,  : 
  Join results in more than 2^31 rows (internal vecseq reached physical limit). Very likely misspecified join. Check for duplicate key values in i each of which join to the same group in x over and over again. If that's ok, try by=.EACHI to run j for each group to avoid the large allocation. Otherwise, please search for this error message in the FAQ, Wiki, Stack Overflow and data.table issue tracker for advice.

Is there a way to override this? When I add by=.EACHI, I get the error:

  'by' or 'keyby' is supplied but not j

I know this question is not in ideal reproducible format (my apologies!), but I am not sure that is strictly necessary for an answer. Maybe I am just missing something or data.table is limited in this way?

I am aware only of this question from 2013, which seems to suggest data.table could not do this back then.

This is the below code that causes the error:

  pfill=q[, k:=t+1][q2[, k:=tprm], on=.(k), nomatch=0L,allow.cartesian=TRUE][,k:=NULL]

Answer 1

As data.table seems to be still limited to 2^31 rows, you could as a workaround use arrow combined with dplyr to overcome this limit:

library(arrow)
library(dplyr)

# Create 3 * 2^30 rows feathers
dt <-data.frame(val=rep(1.0,2^30))
write_feather(dt, "test/data1.feather")
write_feather(dt, "test/data2.feather")
write_feather(dt, "test/data3.feather")

# Read the 3 files in a common dataset
dset <- open_dataset('test',format = 'feather')

# Get number of rows
(nrows <- dset %>% summarize(n=n()) %>% collect() %>% pull)
#integer64
#[1] 3221225472

# Check that we're above 2^31 rows
nrows / 2^31
#[1] 1.5

Answer 2

If you just want to know Yes or No, I guess we cannot have a data.table object with more than 2^31 rows if you stick to be within data.table only. However, if you jump out of data.table, the answer by @Waldi is a fabulous workaround for this issue.

The explanation below is just an example to somewhat "prove" the infeasibility, which may provide you with some hints, hopefully.

---

Let's think about it in the other way around. Assuming we have a data.table dt with more then 2^31 rows, what will happen when indexing the rows? It should be noted that we use integers to index the rows, that means we need to support integers larger than 2^31 in your case. Unfortunately, if you type ?.Machine in the console, you will see that

The algorithm is based on Cody's (1988) subroutine MACHAR. As all current implementations of R use 32-bit integers and use IEC 60559 floating-point (double precision) arithmetic, the "integer" and "double" related values are the same for almost all R builds.

and

integer.max the largest integer which can be represented. Always 2^31 - 1 = 2147483647.

If the assumption is true, then we come to indexing issues, i.e., invalid indexing. Thus the assumption does not hold.

---

A Simple Test

Given a long vector v of length 2^31 (which is larger than 2^31-1), let's see what will happen if we use it to initialize a data.table:

> v <- seq_len(2^31)

> d <- data.table(v)
Error in vapply(X = x, FUN = fun, ..., FUN.VALUE = NA_integer_, USE.NAMES = use.names) : 
  values must be type 'integer',
 but FUN(X[[1]]) result is type 'double'

As we can see, there is no issue when create a vector of length 2^31, but we have troubles when initializing a data.table d. When we look into the source code of data.table, we see there are several places using length, which is applicable when the vector is not longer than 2^31-1

The default method for length currently returns a non-negative integer of length 1, except for vectors of more than 2^{31}-1 elements, when it returns a double. and we can check that

> class(length(v))
[1] "numeric"

which means the output is not integer as required when calling data.table.