Implements a 'DBI' compliant interface to Presto. Presto is an open source distributed SQL query engine for running interactive analytic queries against data sources of all sizes ranging from gigabytes to petabytes: < https://prestodb.io/>.
RPresto is a DBI-based adapter for the open source distributed SQL query engine Presto for running interactive analytic queries.
RPresto is both on CRAN and github. For the CRAN version, you can use
install.packages('RPresto')You can install the github development version via
devtools::install_github('prestodb/RPresto')The standard DBI approach works with RPresto:
library('DBI') con <- dbConnect( RPresto::Presto(), host='http://localhost', port=7777, user=Sys.getenv('USER'), schema='<schema>', catalog='<catalog>', source='<source>') res <- dbSendQuery(con, 'SELECT 1')# dbFetch without arguments only returns the current chunk, so we need to# loop until the query completes.while (!dbHasCompleted(res)) { chunk <- dbFetch(res) print(chunk)} res <- dbSendQuery(con, 'SELECT CAST(NULL AS VARCHAR)')# Due to the unpredictability of chunk sizes with presto, we do not support# custom number of rows# testthat::expect_error(dbFetch(res, 5)) # To get all rows using dbFetch, pass in a -1 argumentprint(dbFetch(res, -1)) # An alternative is to use dbGetQuery directly # `source` for iris.sql()source(system.file('tests', 'testthat', 'utilities.R', package='RPresto')) iris <- dbGetQuery(con, paste("SELECT * FROM", iris.sql())) dbDisconnect(con)We also include dplyr integration.
library(dplyr) db <- src_presto( host='http://localhost', port=7777, user=Sys.getenv('USER'), schema='<schema>', catalog='<catalog>', source='<source>') # Assuming you have a table like iris in the databaseiris <- tbl(db, 'iris') iris %>% group_by(species) %>% summarise(mean_sepal_length = mean(as(sepal_length, 0.0))) %>% arrange(species) %>% collect()Presto exposes its interface via a REST based API1. We utilize the
httr package to make the API calls and
use jsonlite to reshape the
data into a data.frame. Note that as of now, only read operations are
supported.
RPresto has been tested on Presto 0.100.
RPresto is BSD-licensed. We also provide an additional patent grant.
[1] See https://github.com/prestodb/presto/wiki/HTTP-Protocol for a description of the API.
SET/RESET SESSION queries are now correctly respected when used under dbGetQuery.
PrestoConnection no longer has parameters slot but dbConnect remains backward
compatible. Manual change to parameter is still possible via [email protected]$setParameter() (#77)dbplyr. This should be backwards compatible
back to dplyr 0.4.3 (#76)json to data.frame construction from pure R to Rcpp for 10x
speed gains! (thanks @saurfang) (#57, #58)session.timezone parameter to dbConnect and src_presto which
defaults to UTC. This affects the timestamps returned for Presto data types
"TIMESTAMP". We handle the ambiguity by assigning a time zone to every
POSIXct column returned. Note that if you are doing as.character() directly
on these columns, the values you obtain will be different from what happened
before.LIMIT 0 queries specifically.src_presto.