In this vignette, we introduce how primitive Presto data types are
translated into R types in the
You can check your
RPresto version by running the
packageVersion() function. You need version 1.3.9
or later to have a more comprehensive and robust primitive types
Here primitive types refer to basic data types that are
atomic and non-nested (as opposed to structural types such as
ROW). Refer to
documentation for a complete list of Presto data types.
We don’t currently support all of Presto’s primitive types. Here we summarize what’s supported and what those supported types map to in R.
|Category||Presto data type||R type|
|Date and Time||DATE||Date (S3 class)|
|Date and Time||TIME||difftime (S3 class)|
|Date and Time||TIME WITH TIME ZONE||difftime (S3 class)|
|Date and Time||TIMESTAMP||POSIXct (S3 class)|
|Date and Time||TIMESTAMP WITH TIME ZONE||POSIXct (S3 class)|
|Date and Time||INTERVAL YEAR TO MONTH||Duration (S4 class)|
|Date and Time||INTERVAL DAY TO SECOND||Duration (S4 class)|
We assume that the user already have a Presto server with a memory connector set up. If you don’t have such a server set up, refer to the Presto documentation for instructions if you want to follow along.
We first create a
PrestoConnection which will serve as
the bridge between the Presto server and R.
We first issue a simple query to see if the Presto connection is working properly.
To illustrate how those primitive types are mapped to R types in bulk, we first create a made-up table with 3 rows and 17 fields (i.e., one column for each supported Presto type).
We create the table using an auxiliary
create_primitive_types_table() function included in the
We can check if the table now exists in Presto.
We can list the fields in the table. They are named after the Presto types they represent.
DBI::dbListFields(con, "presto_primitive_types") #>  "boolean" "tinyint" "smallint" #>  "integer" "bigint" "real" #>  "double" "decimal" "varchar" #>  "char" "varbinary" "date" #>  "time" "time_with_tz" "timestamp" #>  "timestamp_with_tz" "interval_year_to_month" "interval_day_to_second"
Translating boolean values from Presto to R is fairly
false values are
FALSE in R and
null is mapped to
NA which is by default a
logical (i.e., boolean) type in R.
We can verify that all three values in R are
Presto has 4 integer data types.
is 8-bit and ranges from
is 16-bit and ranges from
is 32-bit and ranges from
is 64-bit and ranges from
|Presto data type||Bits||Minimum value||Maximum value|
In comparison, base R only ships with one 32-bit integer type, but
the range is slightly different from Presto. R’s integer type ranges
-2^31+1 = -2,147,483,647 to
2^31-1 = 2,147,483,647. The one number (out of ~4.3 billion
32-bit integer numbers) that’s a valid
INTEGER in Presto
but not in R is the range lower bound
INTEGER numbers (except
-2,147,483,648) can be
fit into R’s
integer type, we translate all of them to
We can verify that all three columns in R are
We can also show that mapping the one exception
-2,147,483,648 to R results in an error while the query
runs correctly in Presto. Users whose data contains the
exceptional integer should consider using the
INTEGER to encode the data in
Since the Presto
BIGINT type is 64-bit, its range is
clearly much wider than base R’s
However, by default we still map
BIGINT integers from
integer in R, for two reasons.
R doesn’t have native 64-bit integer support yet. Mapping
BIGINT to a 64-bit integer in R will require additional
package and add extra dependencies.
The 32-bit integer range covers 2+ billion positive integers and 4.3 billion integers in total. Most integer values used in data analysis and modeling can be well covered in that range.
Besides the default mapping to
integer (albeit 32-bit)
type, we offer three other options when it comes
Firstly, using the
bigint = "character" argument
RPresto to cast the
BIGINT value to
character type. This is particularly useful when
BIGINT is used to store long IDs rather than large numbers
(i.e., the numbers are not used in arithmetic computations).
bigint = "integer64" makes
BIGINT values to a value of
integer64 S3 class from the
bigint = "numeric" makes
BIGINT value in a
numeric ( i.e.,
Whenever we map an integer to another numeric type, we always need to
consider the precision of the mapping, that is, whether there’s any
precision loss during the translation. Among the three translation
options above, the first one (i.e., casting
character) doesn’t involve any precision translation, so we
will focus the precision discussion on the other two translations (
On the receiving end, the
bit64::integer64 type has a
-2^63+1 = -9,223,372,036,854,775,807 to
2^63-1 = 9,223,372,036,854,775,807.
Comparing the range to Presto’s
BIGINT range, it seems
that in theory the
bit64::integer64 range is only
infinitesimally smaller than the
BIGINT range by 1 number
(again, the lower bound number). However, in practice, the range of
BIGINT values that can be translated into
bit64::integer64 without precision loss is
The limitation comes from how the Presto
REST API communicates data with R. It uses the JSON format to encode
the query result data and sends it to R for
JSON by default encodes integers as
double numbers, so
is limited to
+/-(2^53-1) = +/-9,007,199,254,740,991.
Any integers outside of this range will lose precision during the
Since the translation limitation is caused by the JSON format
encoding integers using
double, the same limitation applies
BIGINT values are mapped to
|bigint =||R type||Range without precision loss||Range with possible precision loss|
The table created by the
function has the values from the no-precision-lost range. Below we show
bigint arguments change the output R
bigint = "character"
bigint = "integer64"
bigint = "numeric"
If you attempt to translate integers outside of the no-precision-loss range, a warning message will show up.
dbGetQuery( con, " SELECT SIGN(bigint) * (ABS(bigint) + 1) AS bigint_precision_loss FROM presto_primitive_types ", bigint = "numeric" ) #> Warning in as.double.integer64(x): integer precision lost while converting to #> double #> # A tibble: 3 × 1 #> bigint_precision_loss #> <dbl> #> 1 -9007199254740992 #> 2 9007199254740992 #> 3 NA
R only has one floating point data type,
numeric). All floating numbers are stored in
double precision format (i.e., 64-bit). This matches Presto’s
type, so translation between Presto and R is straightforward.
We can verify that both floating point types are translated to
Presto offers a
DECIMAL data type that has fixed
precision up to 38 digits. This means that it can be used to represent a
very large integer which is obviously outside of the precision limits
mentioned above. We can’t decide one precise R class to translate the
DECIMAL data type into, so RPresto currently translates the
type into a string (i.e.,
character) in R.
We can verify the resulting R types to be
type stores string data in raw bytes. It can be nicely mapped to R’s
We can verify all elements in the resulting column are of
raw data type.
We can also convert the bytes data back to the string values.
In R, there are three types of date/time data that refer to an instant in time.
A date. Tibbles print this as
A time within a day. Tibbles print this as
A date-time is a date plus a time: it uniquely
identifies an instant in time (typically to the nearest second). Tibbles
print this as
<dttm>. Elsewhere in R these are called
We prefer to use the
lubridate package to handle date
and date-time objects. Time objects are not very commonly used and R
lacks a strong native support for it. In
RPresto, we uses
hms package to handle time objects.
type is by far the most used date-and-time types. Note that a
DATE value isn’t tied to any particular time zone, so it
isn’t associated with a unique point in time (i.e., seconds or
microseconds since epoch).
We use base R’s
Date S3 class to translate Presto’s
We can verify the R type of the column.
POSIXct type values, on the other hand, are
associated with a unique point in time. That is, they can be translated
to a unique numeric value that refers to the number of time units
(usually seconds, milliseconds, or microseconds) elapsed since epoch
(i.e., the beginning of time). This is why the mode of a
POSIXct value in R is numeric and you can call the
as.integer() function on it.
In this case, the integer value refers to the number of seconds elapsed since 1970-01-01 00:00:00 in the UTC time zone.
TIMESTAMP WITH TIME ZONE
types follow the same logic and thus can be mapped to the
POSIXct type. To ensure consistency,
always translates the timestamp to match the Presto session’s
You can check the session time zone by printing the
session.timezone slot of the
Here we get the
TIMESTAMP WITH TIME ZONE values from the table.
We can check the R types of the two columns.
We can also verify that the time zone attributes match the session time zone.
( df.duration <- dbGetQuery( con, " SELECT interval_year_to_month, interval_day_to_second FROM presto_primitive_types " ) ) #> # A tibble: 3 × 2 #> interval_year_to_month interval_day_to_second #> <Duration> <Duration> #> 1 36817200s (~1.17 years) 187506.5s (~2.17 days) #> 2 73634400s (~2.33 years) 284889.6s (~3.3 days) #> 3 NA NA