bigint Data Type - Aster R

The base R environment uses finite precision arithmetic and numbers are accurately represented only up to 15 or 16 decimal places. Hence, users need to be careful when working with bigint values that are larger than the values that can be accurately represented. To transfer such numbers between Aster Database and the R environment, users can use a character representation as shown in this example:

frame<-try(read.table(IN,header=FALSE,sep="\t",quote="",nrows=1,colClasses=c("character")),silent=TRUE)

Users can then use packages like int64 and gmp to convert the numbers back to 64-bit integers in the R program.

For example, using the int64 package (http://cran.r-project.org/web/packages/int64/), users can convert a bigint represented as a character back to a 64-bit integer value as shown here:

y<-as.int64(c("-3940427841425010000","-4236711481380030000"))

Also, here is an example that shows how users can perform arithmetic with a BIGINT value:

# head -20 *
==> bigints.csv <==
1,4699533205482374612
2,-3526377826377322350
3,1
4,-1
5,0
==> bigints.R <==
#!/usr/bin/Rscript
library(int64)
IN = file(description="stdin",open="r")
while(1)
{
  # Create a frame to hold input Rows , 
  # without HEADER and also to deal with end of stream
  # read.table() should be inside try block
	frame<-try(read.table(IN,header=FALSE,sep="\t",quote="",nrows=1,colClasses=c("character")),silent=TRUE)
	if(inherits(frame,"try-error"))
	   	break
	# instantiate a int64 object based on BIGINT in table and add 1 to it
	value<-as.int64(frame$V1[1])
	value = value + 1
	# create a new frame with the original BIGINT value
	frame2<-cbind(frame, value)
	write.table(frame2,stdout(),col.names=FALSE,row.names=FALSE,quote=FALSE,sep="\t")
}
==> bigints.sql <==
DROP TABLE IF EXISTS bigints;
CREATE TABLE bigints(seq INT, num BIGINT) DISTRIBUTE BY HASH(seq);
# ncluster_loader -c -w beehive -B bigints.sql bigints bigints.csv
Trying to connect to the loader '192.80.170.44'.
Loading tuples using node '192.80.170.44'.
5 tuples were successfully loaded into table 'bigints'.
# act -w beehive
Welcome to act 05.10.00.00, the Aster nCluster Terminal.
Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with act commands
\g or terminate with semicolon to execute query
\q to quit
beehive=> \install bigints.R
beehive=> SELECT * FROM STREAM( ON (SELECT num FROM bigints) SCRIPT('Rexec --vanilla bigints.R') OUTPUTS ('*', 'sum BIGINT'));
num          |         sum
----------------------+----------------------
1 |                    2
4699533205482374612 |  4699533205482374613
-1 |                    0
-3526377826377322350 | -3526377826377322349
0 |                    1
(5 rows)