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

user_2690949 0
Viewed 453

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]
data.table apache-arrow cross-join r merge
2 Answers

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
user_135133280

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.

user_121587570
Related